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

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组合索引使用内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！