Mysql不用多介绍,很好很强大,这篇文章主要记录innodb中复合索引的引用, 本文中的缩影指的都是二级索引.
概述 ¶
索引在innodb中对缩短查询时间起到至关重要的作用, 有了索引我们可以不进行全表扫描而是对一小部分的数据进行扫描.随着业务的复杂性的增加, 单一的索引已经无法满足日常的需求.因此, 我们需要建立复合索引满足需求, 当然所有的优化必然有开销, 建立索引我们会消耗很多的磁盘空间, 同样也影响插入的速度.
innodb的索引是将索引的列和主键关联的, 所以每个索引都会拷贝一份主键!!!
场景 ¶
先来看看如下场景
create table testexplain (
f0 varchar(10) primary key,
f1 varchar(10),
f2 varchar(10),
f3 varchar(10)
);
假使我们常用的查询sql如下
1. select * from testexplain f0 = 'xxx';
2. select * from testexplain f1 = 'xxx' and f2 = 'xxxx';
3. select * from testexplain f2 = 'xxx' and f3 = 'xxxx';
4. select * from testexplain f1 = 'xxx' or f2 = 'xxxx';
5. select * from testexplain f1 = 'xxx';
6. select * from testexplain f2 = 'xxx';
那么我们建立如下索引能满足要求
create index index_testexplain_f1_f2 on testexplain(f1,f2,f3);
分析 ¶
事实是否如此呢?我们需要mysql告诉我们.说到索引分析,我们必须要用到explain:
一 ¶
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | testexplain | const | PRIMARY | PRIMARY | 32 | const | 1 | |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
很明显走了主键索引, 和预期相同.
二 ¶
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | testexplain | ref | index_testexplain_f1_f2 | index_testexplain_f1_f2 | 66 | const,const | 1 | Using where |
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------------+------+-------------+
同样也符合预期.
三 ¶
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | testexplain | index | NULL | index_testexplain_f1_f2_f3 | 99 | NULL | 481495 | Using where; Using index |
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+
虽然mysql选择扫描了使用index, 但是依然遍历了481495行, 并没有起到加速的作用.因为mysql虽然没遍历全表但是遍历了全索引.
四 ¶
+----+-------------+-------------+-------+----------------------------+----------------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+----------------------------+----------------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | testexplain | index | index_testexplain_f1_f2_f3 | index_testexplain_f1_f2_f3 | 99 | NULL | 481495 | Using where; Using index |
+----+-------------+-------------+-------+----------------------------+----------------------------+---------+------+--------+--------------------------+
mysql选择扫描了全索引.
五 ¶
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | testexplain | ref | index_testexplain_f1_f2 | index_testexplain_f1_f2 | 33 | const | 1 | Using where |
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
mysql选择了索引.
六 ¶
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | testexplain | index | NULL | index_testexplain_f1_f2_f3 | 99 | NULL | 481495 | Using where; Using index |
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+
mysql选择扫描了全索引.
总结 ¶
由上面的测试结果我们可以看出原先的结论是不成立的, 也就是说要想使用复合索引必须按照一定的规则.查看mysql官方文档: 想要利用符合索引, sql语句的where条件必须是符合从左最匹配索引原则.这样翻译确实太抽象.
假使我们有上面的表结构, 要想利用复合索引, where语句的条件必须是f1,f2,f3|f1,f2|f1|f1,f3的形式(从而满足索引的顺序f1,f2,f3), 并且使用and, 条件的顺序可以乱序.or的话代表是两个不同的查询条件, mysql会使用index merge去优化查询, 简单说明就是两个不同的查询过程, 然后做merge.