MySQL组合索引(多列索引)使用与优化案例详解

目录

1、多列索引

2、测试案例及过程

2.1 创建一个测试数据库和数据表

2.2 添加两个单列索引

2.3 查询一条数据利用到两个列的索引

2.4 查看执行计划

2.5 然后删除以上索引,添加多列索引

2.6 再次查询

3、多列索引的使用顺序

3.1 怎么选择建立组合索引时,列的顺序

3.2 组合索引的使用规则

1、多列索引

我们经常听到一些人说"把WHERE条件里的列都加上索引",其实这个建议非常错误。

在多个列上建立单独的索引大部分情况下并不能提高MySQL的查询性能。MySQL 在5.0之后引入了一种叫“索引合并”(index merge)的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。但是当服务器对多个索引做联合操作时,通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上,特别是当其中有些索引的选择性不高,需要合并扫描大量的数据的时候。这个时候,我们需要一个多列索引。

2、测试案例及过程 2.1 创建一个测试数据库和数据表 CREATE DATABASE IF NOT EXISTS db_test default charset utf8 COLLATE utf8_general_ci; use db_test; CREATE TABLE payment ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, staff_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

利用存储过程插入1000w 行随机数据(表引擎可以先设置为 MyISAM,然后改为 InnoDB)

DROP PROCEDURE IF EXISTS add_payment; DELIMITER // create PROCEDURE add_payment(in num INT) BEGIN DECLARE rowid INT DEFAULT 0; SET @exesql = 'INSERT INTO payment(staff_id, customer_id) values (?, ?)'; WHILE rowid < num DO SET @staff_id = (1 + FLOOR(5000*RAND()) ); SET @customer_id = (1 + FLOOR(500000*RAND())); SET rowid = rowid + 1; prepare stmt FROM @exesql; EXECUTE stmt USING @staff_id, @customer_id; END WHILE; END // DELIMITER ; 2.2 添加两个单列索引

(执行过程要花点时间,建议分开一句一句执行):

ALTER TABLE `payment` ADD INDEX idx_customer_id(`customer_id`); ALTER TABLE `payment` ADD INDEX idx_staff_id(`staff_id`); 2.3 查询一条数据利用到两个列的索引 select count(*) from payment where staff_id = 2205 AND customer_id = 93112; 2.4 查看执行计划 mysql> explain select count(*) from payment where staff_id = 2205 AND customer_id = 93112; +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ | 1 | SIMPLE | payment | index_merge | idx_customer_id,idx_staff_id | idx_staff_id,idx_customer_id | 4,4 | NULL | 11711 | Using intersect(idx_staff_id,idx_customer_id); Using where; Using index | +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec)

可以看到 type 是 index_merge,Extra 中提示 Using intersect(idx_staff_id,idx_customer_id);这便是索引合并,利用两个索引,然后合并两个结果(取交集或者并集或者两者都有)

查询结果:

mysql> select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112 ;
+----------+
| count(*) |
+----------+
|   178770 |
+----------+
1 row in set (0.12 sec)

2.5 然后删除以上索引,添加多列索引 ALTER TABLE payment DROP INDEX idx_customer_id; ALTER TABLE payment DROP INDEX idx_staff_id; ALTER TABLE `payment` ADD INDEX idx_customer_id_staff_id(`customer_id`, `staff_id`);

注意,多列索引很关注索引列的顺序(因为 customer_id 的选择性更大,所以把它放前面)。

2.6 再次查询 mysql> select count(*) from payment where staff_id = 2205 AND customer_id = 93112; +----------+ | count(*) | +----------+ | 178770 | +----------+ 1 row in set (0.05 sec)

发现多列索引加快的查询(这里数据量还是较小,更大的时候比较更明显)。

3、多列索引的使用顺序 3.1 怎么选择建立组合索引时,列的顺序

多列索引的列顺序至关重要,如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列(但是不是绝对的)。经验法则考虑全局的基数和选择性,而不是某个具体的查询:

mysql> select count(DISTINCT staff_id) / count(*) AS staff_id_selectivity, count(DISTINCT customer_id) / count(*) AS customer_id_selectivity, count(*) from payment\G; *************************** 1. row *************************** staff_id_selectivity: 0.0005 customer_id_selectivity: 0.0500 count(*): 10000000 1 row in set (6.29 sec)

customer_id 的选择性更高,所以将它作为索引列的第一位。

3.2 组合索引的使用规则

索引可以理解成排好序的数据结构。组合索引可以这样理解,比如(a,b,c),abc 都是排好序的,在任意一段 a 的下面 b 都是排好序的,任何一段 b 下面  c都是排好序的;

生效的规则是:从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用; 

比如:

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点,全部发挥作用; where a=3 and c=5... 这种情况下b就是断点,a发挥了效果,c没有效果 where b=3 and c=4... 这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果; where b=45 and a=3 and c=5 .... 这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关

(a,b,c)多列索引使用的示例,说明:(a,b,c)组合索引和(a,c,b)是不一样的

(0) select * from mytable where a=3 and b=5 and c=4; abc三个索引都在where条件里面用到了,而且都发挥了作用 (1) select * from mytable where c=4 and b=6 and a=3; 这条语句列出来只想说明 mysql没有那么笨,where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样 (2) select * from mytable where a=3 and c=7; a用到索引,b没有用,所以c是没有用到索引效果的 (3) select * from mytable where a=3 and b>7 and c=3; a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引 (4) select * from mytable where b=3 and c=4; 因为a索引没有使用,所以这里 bc都没有用上索引效果 (5) select * from mytable where a>4 and b=7 and c=9; a用到了 b没有使用,c没有使用 (6) select * from mytable where a=3 order by b; a用到了索引,b在结果排序中也用到了索引的效果,前面说了,a下面任意一段的b是排好序的 (7) select * from mytable where a=3 order by c; a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort (8) select * from mytable where b=3 order by a; b没有用到索引,排序中a也没有发挥索引效果

到此这篇关于MySQL组合索引(多列索引)使用与优化的文章就介绍到这了,更多相关mysql组合索引使用内容请搜索易知道(ezd.cc)以前的文章或继续浏览下面的相关文章希望大家以后多多支持易知道(ezd.cc)!

推荐阅读

    3500元超额值学生娱乐结构的优化配置

    3500元超额值学生娱乐结构的优化配置,,作为一个DIY的主流用户领域的学生,每个用户51学生攒机的高峰。因为学生用户没有稳定的收入来源,攒机

    优化PostgreSQL中的批量更新性能

    优化PostgreSQL中的批量更新性能,数据,表格,在Ubuntu 12.04上使用PG 9.1. 我们目前需要24小时才能运行大量UPDATE数据库上的语句,其形式

    512内存的电脑优化|笔记本内存512

    512内存的电脑优化|笔记本内存512,,1. 笔记本内存512够用,因为运行非常流畅,苹果笔记本 16g512的运行内存是16g内存,机身内存是512g内存,运行

    Windows7下固态硬盘的优化技术

    Windows7下固态硬盘的优化技术,,当微软开发Windows Vista时,固态硬盘没有那么热,所以没有进行优化。Windows 7是不同的。微软从一开始就把SS

    word组合快捷键|word快速组合

    word组合快捷键|word快速组合,,word快速组合在word中如何将两张图片合在一起只需一键【组合】即可完成。以下是详细介绍:在word中如何将两

    记一次服务端系统性能优化

    记一次服务端系统性能优化,在线,设备, 首先简单介绍一下业务场景,物联网设备,关注公众号,免费领取环保袋。12月8号,也就是昨天上午,突然接

    幻灯片放映慢优化可以加快速度

    幻灯片放映慢优化可以加快速度,,用于制作幻灯片的一些技术更复杂,这些幻灯片在一些旧机器上运行缓慢或缓慢。在这种情况下,我们应该如何优化

    取消组合的快捷键|取消组合的快捷键ps

    取消组合的快捷键|取消组合的快捷键ps,,取消组合的快捷键ps1,首先,在右下方的图层栏里,选中要打散的图形所在的图层。2,选好图层之后,在左侧工