释放双眼，带上耳机，听听看~！
MySQL索引及优化
-
摘要
-
1、索引介绍
-
1.1 索引的底层结构
- 1.2 索引的分类
-
1.2.1 单值索引
* 1.2.2 主键索引
* 1.2.3 唯一索引
* 1.2.4 组合索引 -
2、索引的优缺点
-
3、索引的应用场景
-
4、explain分析索引
-
5、查询语句的索引优化
-
5.1 全值匹配我最爱，最左前缀要遵守
- 5.2 带头大哥不能死，中间兄弟不能断
- 5.3 索引列上少计算，范围之后全失效
- 5.4 Like百分写最右，覆盖索引不写星
- 5.5 不等空值还有or，索引失效要少用
- 5.6 varchar数引不能丢，SQL高级也不难
-
6、排序语句的索引优化
-
6.1 where使用有效索引，oder by能使用索引最左前缀
- 6.2 不能使用索引进行排序
摘要
本文主要介绍MySQL索引的概念、索引的底层数据结构，然后介绍了索引的四种分类，使读者可以理解索引的基本知识；并通过讲解索引的优缺点以及应用场景，以解决索引如何应用；最后通过介绍explain工具，以及详细阐述了SQL中索引的优化方法，以便在实际开发中掌握并优化索引的使用。
本人还写了MySQL相关博文，有兴趣的研友可以点击如下链接，请各位研友指正并留言。
MySQL事务与隔离级别
MySQL的锁及其MVCC
索引为什么选择B+Tree
1、索引介绍
1.1 索引的底层结构
索引其实是一种数据结构，它是一种排好序的便于查找的数据结构，用Btree或B+tree实现。
三层Btree如图所示：
三层B+tree如图所示：
1.2 索引的分类
1.2.1 单值索引
单值索引是指一个索引只包含表中一个字段。一张表可以有多个单值索引。
在表test中name、age字段分别建一个单值索引
2
3
4
2mysql> create index idx_age on test(age);
3
4
查看表test的表信息
2
3
4
5
6
7
8
9
10
11
12
2+---------+-------------+------+-----+---------+-------+
3| Field | Type | Null | Key | Default | Extra |
4+---------+-------------+------+-----+---------+-------+
5| id | int(11) | YES | | NULL | |
6| name | varchar(10) | YES | MUL | NULL | |
7| age | int(11) | YES | MUL | NULL | |
8| addr | varchar(10) | YES | | NULL | |
9| collage | varchar(10) | YES | | NULL | |
10+---------+-------------+------+-----+---------+-------+
11
12
查看表test的索引信息
2
3
4
5
6
7
8
9
2+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5| test | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
6| test | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
7+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8
9
从索引信息中可知，索引idx_age、idx_name 的Seq_in_index项都为1，也就表示了两个索引没有关联关系。
删除表test的索引
2
3
4
2mysql> drop index idx_name on test;
3
4
1.2.2 主键索引
如果表中设置了主键，Innodb就会自动为主键建立索引。主键索引列的值因为自身就是主键，所以具有值唯一、不允许为NULL。主键索引属于单值索引。
注意：主键索引是InnoDB存储引擎会自动创建的索引。若有主键，则直接创建主键索引；若没有主键，则选取唯一键来创建；若没有唯一键，则自动添加6位row_id来创建主键索引。同时，主键索引是聚集类索引。
例如：为表test中的字段id添加主键
2
3
2
3
查看表信息
2
3
4
5
6
7
8
9
10
11
12
2+---------+-------------+------+-----+---------+-------+
3| Field | Type | Null | Key | Default | Extra |
4+---------+-------------+------+-----+---------+-------+
5| id | int(11) | NO | PRI | 0 | |
6| name | varchar(10) | YES | | NULL | |
7| age | int(11) | YES | | NULL | |
8| addr | varchar(10) | YES | | NULL | |
9| collage | varchar(10) | YES | | NULL | |
10+---------+-------------+------+-----+---------+-------+
11
12
查看表test的索引信息
2
3
4
5
6
7
8
2+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
6+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7
8
删除表test的主键
2
3
2
3
1.2.3 唯一索引
唯一索引是指索引列的值唯一，但允许为Null，如身份证信息、卡号等。与主键索引的区别就是唯一索引允许为Null。唯一索引属于单值索引。
为表test的字段id添加唯一性约束
2
3
2
3
查看表test的表信息
2
3
4
5
6
7
8
9
10
11
12
2+---------+-------------+------+-----+---------+-------+
3| Field | Type | Null | Key | Default | Extra |
4+---------+-------------+------+-----+---------+-------+
5| id | int(11) | NO | PRI | 0 | |
6| name | varchar(10) | YES | | NULL | |
7| age | int(11) | YES | | NULL | |
8| addr | varchar(10) | YES | | NULL | |
9| collage | varchar(10) | YES | | NULL | |
10+---------+-------------+------+-----+---------+-------+
11
12
查看表test的索引信息
2
3
4
5
6
7
8
2+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5| test | 0 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
6+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7
8
删除表test的唯一约束
2
3
2
3
1.2.4 组合索引
组合索引就是包含了多个字段的索引。
例如：为表test的字段name, age, addr创建组合索引：
2
3
2
3
查看表test的表信息
2
3
4
5
6
7
8
9
10
11
12
2+---------+-------------+------+-----+---------+-------+
3| Field | Type | Null | Key | Default | Extra |
4+---------+-------------+------+-----+---------+-------+
5| id | int(11) | NO | | 0 | |
6| name | varchar(10) | YES | MUL | NULL | |
7| age | int(11) | YES | | NULL | |
8| addr | varchar(10) | YES | | NULL | |
9| collage | varchar(10) | YES | | NULL | |
10+---------+-------------+------+-----+---------+-------+
11
12
查看表test的索引信息
2
3
4
5
6
7
8
9
10
2+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5| test | 1 | idx_name_age_addr | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
6| test | 1 | idx_name_age_addr | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | |
7| test | 1 | idx_name_age_addr | 3 | addr | A | 0 | NULL | NULL | YES | BTREE | | |
8+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9
10
从索引信息中可以知道字段name、age、addr的Key_name项都标记了组合索引idx_name_age_addr ，同时Seq_in_index 也标记了组合索引中字段的顺序。
2、索引的优缺点
优点：
- 类似字典目录，提高数据检索的效率，从而减少了IO访问次数，使性能得到提升；
- 使用索引进行数据排序，可以降低数据排序的开销。
缺点：
- 索引是一种数据结构，需要额外的占用空间；
- 索引降低了数据的更新效率，如insert、update、delete操作，因为MySQL除了要维护新的数据外，还要维护索引；
- 建表的同时要综合考虑如何建高效的索引。
3、索引的应用场景
需要建索引的场景：
- 主键自动建立主键索引；
- 查询条件中频繁出现的字段应该创建索引；
- 查询时与其他表关联的字段，外键关系建立索引；
- 查询中排序的字段应该建立索引；
- 查询中分组的字段应该建立索引。
不需要建索引的场景
- 频繁更新的字段不适合建索引；
- where中用不到的字段不适合建索引；
- 表中记录少不适合建索引；
- 经常增删改的表不适合建索引；
- 表中字段重复值太多，不适合建索引。
4、explain分析索引
explain是mysql提供的一种分析SQL执行情况的工具，所以使用explain可以看出SQL执行时，索引是否用到，是否失效。
2
3
4
5
6
7
8
2+----+-------------+-------+------+---------------+------+---------+------+------+-------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+---------------+------+---------+------+------+-------+
5| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
6+----+-------------+-------+------+---------------+------+---------+------+------+-------+
7
8
首先介绍explain中各列所代表的意思：
- id 代表SQL语句的执行顺序，当有嵌套SQL时，可以根据id知道子SQL语句的执行顺序，id号越大越先执行。
- select_type 用于标记查询类型，如普通查询、联合查询、子查询等。
SIMPLE
简单查询，不包含子查询或union
PRIMARY
SQL包含子查询，最外层SQL标识为PRIMARY
SUBQUERY
SQL包含子查询，子查询被标记为SUBQUERY
DERIVED
在From列表中包含的子查询被标记为DERIVED
UNION
带UNION的SQL语句中，第二条select语句被标记为UNION
UNION RESULT
带UNION的SQL语句的执行结果被标记为UNION RESULT
- type 代表SQL扫描范围的类型。
system
表中只有一行记录，等于系统表
const
通过索引一次就找到了
eq_ref
唯一性索引扫描，如主键索引、唯一索引
ref
非唯一性索引扫描
range
只检索给定范围的行，使用一个索引来选择行
index
遍历索引树
all
遍历全表
- possible_keys 表示预计使用到的索引键；
- key 表示实际使用到的索引键；
- key_len 索引中使用的字节数，长度越短越好
- ref 显示索引的哪一列被使用了
- rows 表示找到目标记录所需要读取的行数
- extra 额外信息
Using filesort
使用了一个外部索引排序，而不是已有的内部索引排序
Using temporary
使用临时表保存中间结果，常见于排序（order by）和分组（group by）
Using index
使用了覆盖索引，避免访问表的数据行，效率不错。 如果同时出现了Using where，表明索引被用来执行主键的查找，再进行了回表操作； 如果没有同时出现Using where，表明索引用来读取数据而非查找。
Using where
表明使用了where过滤
Using join buffer
使用了连接缓存
impossible where
where子句的值总是false，不能用来获取任何元素
5、查询语句的索引优化
建立表test，并填充值
2
3
4
5
6
7
8
9
2 `id` int,
3 `name` varchar(10),
4 `age` int ,
5 `addr` varchar(10) ,
6 `collage` varchar(10) ,
7)
8
9
2
3
4
5
6
7
2insert test values (2,'tujiu',18,'bbb222','qwer');
3insert test values (3,'xiezi',20,'ccc333','hjkl');
4insert test values (4,'shanyin',19,'ddd444','uio');
5insert test values (5,'haishi',18,'eee555','bmn');
6
7
建组合索引idx_name_age_addr
2
3
2
3
5.1 全值匹配我最爱，最左前缀要遵守
-
select * from test;
2
3
4
5
6
7
8
2+----+-------------+-------+------+---------------+------+---------+------+------+-------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+---------------+------+---------+------+------+-------+
5| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
6+----+-------------+-------+------+---------------+------+---------+------+------+-------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)。
-
select * from test where name=‘xuebao’ and age=19 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 31 | const,const,const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了三个索引键(ref为三个const)，在索引树中遍历(Using index)。
-
select * from test where name=‘xuebao’ and age=19;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 18 | const,const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了两个索引键(ref为两个const)，在索引树中遍历(Using index)。
-
select * from test where name=‘xuebao’ ;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 13 | const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了一个索引键(ref为一个const)，在索引树中遍历(Using index)。
总结：在具有组合索引时，根据组合索引创建的顺序，where语句中包含索引键个数越多，查询效率越高。即全值匹配我最爱，最左前缀要遵守。
5.2 带头大哥不能死，中间兄弟不能断
-
select * from test where age=19 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
5| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
6+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。
-
select * from test where name=‘xuebao’ and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 13 | const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了一个索引键(ref为一个const)，在索引树中遍历(Using index)，但在where语句中却使用了两个索引键，可见最后一个索引键addr失效。
总结：在使用组合索引时，可以使用部分索引键，但必须按照组合索引创建的顺序，按从左到右的顺序，使用部分索引键，如该例中组合索引为idx_name_age_addr，顺序为name、age、addr，所以使用部分组合索引键时，首先满足name带头大哥的存在，否则索引失效；然后选择使用name或name+age，最好不要使用name+addr，否则只有带头大哥name索引键有效。即带头大哥不能死，中间兄弟不能断。
5.3 索引列上少计算，范围之后全失效
-
select * from test where left(name,3)=‘xue’ and age=19 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
5| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
6+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。
-
select * from test where name=‘name’ and ABS(age)=18 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 13 | const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了一个索引键(ref为一个const)，在索引树中遍历(Using index)，索引键age用于索引扫描，索引键addr失效。
3.select * from test where name=‘name’ and age>18 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
5| 1 | SIMPLE | test | range | idx_name_age_addr | idx_name_age_addr | 18 | NULL | 1 | Using index condition |
6+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(key为idx_name_age_addr)，在给定范围类查询(type为range)，在索引树中遍历(Using index)，这种情况下只有索引键name被使用，addr索引键失效，而age索引键用于索引扫描。
-
select * from test where name=‘xuebao’ and age=19 and left(addr,3)=‘aaa’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 18 | const,const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了两个索引键(ref为两个const)，在索引树中遍历(Using index)，索引键addr用于索引扫描。
总结：索引键上不要使用函数、计算、对比，否则会使该索引用于排序，且该索引之后的索引全失效。即索引列上少计算，范围之后全失效。
5.4 Like百分写最右，覆盖索引不写星
-
select * from test where name like “%aa” and age=19 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
5| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
6+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。
-
select * from test where name like “aa%” and age=19 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
5| 1 | SIMPLE | test | range | idx_name_age_addr | idx_name_age_addr | 31 | NULL | 1 | Using index condition |
6+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为range、key为idx_name_age_addr )，索引键name用于索引扫描，在索引树中遍历(Using index)。
-
select name from test where name like “%aa” and age=19 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
5| 1 | SIMPLE | test | index | NULL | idx_name_age_addr | 31 | NULL | 5 | Using where; Using index |
6+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
7
8
2
3
4
5
6
7
8
2+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
5| 1 | SIMPLE | test | index | NULL | idx_name_age_addr | 31 | NULL | 5 | Using where; Using index |
6+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
7
8
2
3
4
5
6
7
8
2+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
5| 1 | SIMPLE | test | index | NULL | idx_name_age_addr | 31 | NULL | 5 | Using where; Using index |
6+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
7
8
从上表可知，该SQL使用到了索引(type为index、key为idx_name_age_addr )，使用的是覆盖索引(Using where; Using index)。
-
select name,addr from test where name like “%aa” and age=19 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
5| 1 | SIMPLE | test | index | NULL | idx_name_age_addr | 31 | NULL | 5 | Using where; Using index |
6+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
7
8
2
3
4
5
6
7
8
2+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
5| 1 | SIMPLE | test | index | NULL | idx_name_age_addr | 31 | NULL | 5 | Using where; Using index |
6+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
7
8
从上表可知，该SQL使用到了索引(type为index、key为idx_name_age_addr )，使用的是覆盖索引(Using where; Using index)。同时从上面例子可以得知，覆盖索引只要覆盖到组合索引中任何一个或多个索引键即可。
总结：在使用like语句时，‘%’尽量写在右边，where语句中不能使用到索引时，尽可能在select中选择索引键字段，从而使用到覆盖索引，覆盖索引意味着在索引树中就可以查询到所需数据，而不用回表操作。即Like百分写最右，覆盖索引不写星。
5.5 不等空值还有or，索引失效要少用
-
select * from test where name is not null and age=18 and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
5| 1 | SIMPLE | test | ALL | idx_name_age_addr | NULL | NULL | NULL | 5 | Using where |
6+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。由此可见is not null用于带头大哥name会使组合索引全失效。
-
select * from test where name=‘name’ and age is not null and addr=‘aaa111’;
2
3
4
5
6
7
8
2+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
5| 1 | SIMPLE | test | range | idx_name_age_addr | idx_name_age_addr | 18 | NULL | 1 | Using index condition |
6+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为range、key为idx_name_age_addr )，索引键age用于索引扫描，在索引树中遍历(Using index)。
-
select * from test where name=‘name’ and age=18 and addr is not null;
2
3
4
5
6
7
8
2+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
5| 1 | SIMPLE | test | range | idx_name_age_addr | idx_name_age_addr | 31 | NULL | 1 | Using index condition |
6+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为range、key为idx_name_age_addr )，索引键addr用于索引扫描，在索引树中遍历(Using index)。
5.6 varchar数引不能丢，SQL高级也不难
-
select * from test where name=‘xuebao’ and age=18 and addr=111; 111没有引号
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 18 | const,const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref 、key为idx_name_age_addr )，索引键(ref为两个const)，索引键addr用于索引扫描，在索引树中遍历(Using index)。
-
select * from test where name=‘xuebao’ and age=18 and addr=‘111’; 111带引号
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 31 | const,const,const | 1 | Using index condition |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
7
8
从上表可知，该SQL使用到了索引(type为ref 、key为idx_name_age_addr )，索引键(ref为三个const)，全值匹配型。
总结：由此可见，varchar型字段在匹配数字字符时一定要带引号，否则该索引键及后续索引键都会失效。即varchar数引不能丢，SQL高级也不难。
6、排序语句的索引优化
6.1 where使用有效索引，oder by能使用索引最左前缀
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 13 | const | 1 | Using index condition; Using where |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
7
8
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+------------------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+------------------------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 18 | const,const | 1 | Using index condition; Using where |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+------------------------------------+
7
8
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 13 | const | 1 | Using index condition; Using where |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
7
8
2
3
4
5
6
7
8
2+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
5| 1 | SIMPLE | test | range | idx_name_age_addr | idx_name_age_addr | 18 | NULL | 1 | Using index condition |
6+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
7
8
总结：order by中可以使用索引的前提是where语句中有有效索引，同时order by 在有效索引的基础上继续满足最左前缀规则即可使用索引。
6.2 不能使用索引进行排序
-
select * from test order by name ASC,age DESC, addr DESC;
2
3
4
5
6
7
8
2+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
5| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
6+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，因为order by中索引键的升降序不一致。同时extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。
-
select * from test where id=2 order by age, addr;
2
3
4
5
6
7
8
2+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
5| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using where; Using filesort |
6+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，因为where中没有有效索引。同时extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。
-
select * from test where name=‘asdf’ order by addr;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 13 | const | 1 | Using index condition; Using where; Using filesort |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
7
8
从上表可知，该SQL使用了索引(type为ref ，possible_keys 、key为idx_name_age_addr )，但extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。因为order by没有基于where的有效索引键满足最左前缀原则。
-
select * from test where name=‘asdf’ order by age,collage;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
5| 1 | SIMPLE | test | ref | idx_name_age_addr | idx_name_age_addr | 13 | const | 1 | Using index condition; Using where; Using filesort |
6+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
7
8
从上表可知，该SQL使用了索引(type为ref ，possible_keys 、key为idx_name_age_addr )，但extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。因为order by中collage不是索引的一部分。
-
select * from test where name in (‘xuebao’, ‘tujiu’, ‘sanyin’) order by age,addr;
2
3
4
5
6
7
8
2+----+-------------+-------+------+-------------------+------+---------+------+------+-----------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+----+-------------+-------+------+-------------------+------+---------+------+------+-----------------------------+
5| 1 | SIMPLE | test | ALL | idx_name_age_addr | NULL | NULL | NULL | 5 | Using where; Using filesort |
6+----+-------------+-------+------+-------------------+------+---------+------+------+-----------------------------+
7
8
从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，同时extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。因为对排序来说，多个相等条件也是范围查询。
