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字段分别建一个单值索引

1
2
3
4
1mysql> create index idx_name on test(name);

2mysql> create index idx_age on test(age);  

3

4

查看表test的表信息

1
2
3
4
5
6
7
8
9
10
11
12
1mysql> desc test;

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的索引信息

1
2
3
4
5
6
7
8
9
1mysql> show index from test;

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的索引

1
2
3
4
1mysql> drop index idx_age on test;        

2mysql> drop index idx_name on test;

3

4

1.2.2 主键索引

如果表中设置了主键，Innodb就会自动为主键建立索引。主键索引列的值因为自身就是主键，所以具有值唯一、不允许为NULL。主键索引属于单值索引。
注意：主键索引是InnoDB存储引擎会自动创建的索引。若有主键，则直接创建主键索引；若没有主键，则选取唯一键来创建；若没有唯一键，则自动添加6位row_id来创建主键索引。同时，主键索引是聚集类索引。
例如：为表test中的字段id添加主键

1
2
3
1mysql>alter table test add primary key (id); 

2

3

查看表信息

1
2
3
4
5
6
7
8
9
10
11
12
1mysql> desc test;

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的索引信息

1
2
3
4
5
6
7
8
1mysql> show index from test;

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的主键

1
2
3
1mysql> alter table test drop primary key;

2

3

1.2.3 唯一索引

唯一索引是指索引列的值唯一，但允许为Null，如身份证信息、卡号等。与主键索引的区别就是唯一索引允许为Null。唯一索引属于单值索引。
为表test的字段id添加唯一性约束

1
2
3
1mysql> alter table test add unique (id);

2

3

查看表test的表信息

1
2
3
4
5
6
7
8
9
10
11
12
1mysql> desc test;

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的索引信息

1
2
3
4
5
6
7
8
1mysql> show index from test;

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的唯一约束

1
2
3
1mysql> alter table test drop key id;

2

3

1.2.4 组合索引

组合索引就是包含了多个字段的索引。
例如：为表test的字段name, age, addr创建组合索引：

1
2
3
1mysql> create index idx_name_age_addr on test(name, age, addr);

2

3

查看表test的表信息

1
2
3
4
5
6
7
8
9
10
11
12
1mysql> desc test;

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的索引信息

1
2
3
4
5
6
7
8
9
10
1mysql> show index from test;

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、索引的优缺点

优点：

1. 类似字典目录，提高数据检索的效率，从而减少了IO访问次数，使性能得到提升；
2. 使用索引进行数据排序，可以降低数据排序的开销。

缺点：

1. 索引是一种数据结构，需要额外的占用空间；
2. 索引降低了数据的更新效率，如insert、update、delete操作，因为MySQL除了要维护新的数据外，还要维护索引；
3. 建表的同时要综合考虑如何建高效的索引。

3、索引的应用场景

需要建索引的场景：

1. 主键自动建立主键索引；
2. 查询条件中频繁出现的字段应该创建索引；
3. 查询时与其他表关联的字段，外键关系建立索引；
4. 查询中排序的字段应该建立索引；
5. 查询中分组的字段应该建立索引。

不需要建索引的场景

1. 频繁更新的字段不适合建索引；
2. where中用不到的字段不适合建索引；
3. 表中记录少不适合建索引；
4. 经常增删改的表不适合建索引；
5. 表中字段重复值太多，不适合建索引。

4、explain分析索引

explain是mysql提供的一种分析SQL执行情况的工具，所以使用explain可以看出SQL执行时，索引是否用到，是否失效。

1
2
3
4
5
6
7
8
1mysql> explain select * from test;

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中各列所代表的意思：

1. id 代表SQL语句的执行顺序，当有嵌套SQL时，可以根据id知道子SQL语句的执行顺序，id号越大越先执行。
2. 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

1. type 代表SQL扫描范围的类型。

system
表中只有一行记录，等于系统表
const
通过索引一次就找到了
eq_ref
唯一性索引扫描，如主键索引、唯一索引
ref
非唯一性索引扫描
range
只检索给定范围的行，使用一个索引来选择行
index
遍历索引树
all
遍历全表

1. possible_keys 表示预计使用到的索引键；
2. key 表示实际使用到的索引键；
3. key_len 索引中使用的字节数，长度越短越好
4. ref 显示索引的哪一列被使用了
5. rows 表示找到目标记录所需要读取的行数
6. 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，并填充值

1
2
3
4
5
6
7
8
9
1CREATE TABLE `test` (

2  `id` int,

3  `name` varchar(10),

4  `age` int ,

5  `addr` varchar(10) ,

6  `collage` varchar(10) ,

7) 

8

9

1
2
3
4
5
6
7
1insert test values (1,'xuebao',19,'aaa111','asd');      

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

1
2
3
1create index idx_name_age_addr on test;

2

3

5.1 全值匹配我最爱，最左前缀要遵守

1. select * from test;

1
2
3
4
5
6
7
8
1mysql> explain select * from test;

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)。

1. select * from test where name=‘xuebao’ and age=19 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='xuebao' and age=19 and addr='aaa111';

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)。

1. select * from test where name=‘xuebao’ and age=19;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='xuebao' and age=19;

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)。

1. select * from test where name=‘xuebao’ ;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='xuebao';

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 带头大哥不能死，中间兄弟不能断

1. select * from test where age=19 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where age=19 and addr='aaa111';                  

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)，所以组合索引失效。

1. select * from test where name=‘xuebao’ and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='xuebao' and addr='aaa111';           

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 索引列上少计算，范围之后全失效

1. select * from test where left(name,3)=‘xue’ and age=19 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where left(name,3)='xue' and age=19 and addr='aaa111';                      

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)，所以组合索引失效。

1. select * from test where name=‘name’ and ABS(age)=18 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='name' and ABS(age)=18 and addr='aaa111'; 

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’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='name' and age>18 and addr='aaa111';                

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索引键用于索引扫描。

1. select * from test where name=‘xuebao’ and age=19 and left(addr,3)=‘aaa’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='xuebao' and age=19 and left(addr,3)='aaa';

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百分写最右，覆盖索引不写星

1. select * from test where name like “%aa” and age=19 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name like "%aa" and age=19 and addr='aaa111';

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)，所以组合索引失效。

1. select * from test where name like “aa%” and age=19 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name like "aa%" and age=19 and addr='aaa111';  

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)。

1. select name from test where name like “%aa” and age=19 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select name from test where name like "%aa" and age=19 and addr='aaa111';           

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

1
2
3
4
5
6
7
8
1mysql> explain select age from test where name like "%aa" and age=19 and addr='aaa111';    

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

1
2
3
4
5
6
7
8
1mysql> explain select addr from test where name like "%aa" and age=19 and addr='aaa111';   

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)。

1. select name,addr from test where name like “%aa” and age=19 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select name,addr from test where name like "%aa" and age=19 and addr='aaa111';

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

1
2
3
4
5
6
7
8
1mysql> explain select age,addr from test where name like "%aa" and age=19 and addr='aaa111';    

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，索引失效要少用

1. select * from test where name is not null and age=18 and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name is not null and age=18 and addr='aaa111';

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会使组合索引全失效。

1. select * from test where name=‘name’ and age is not null and addr=‘aaa111’;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='name' and age is not null and addr='aaa111';        

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)。

1. select * from test where name=‘name’ and age=18 and addr is not null;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='name' and age=18 and addr is not null;

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高级也不难

1. select * from test where name=‘xuebao’ and age=18 and addr=111; 111没有引号

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='xuebao' and age=18 and addr=111;

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)。

1. select * from test where name=‘xuebao’ and age=18 and addr=‘111’; 111带引号

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='xuebao' and age=18 and addr='111';

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能使用索引最左前缀

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='asdf' order by age;

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

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='asdf' and age=19 order by addr;

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

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='asdf' order by age, addr; 

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

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='asdf' and age>18 order by age, addr;

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 不能使用索引进行排序

1. select * from test order by name ASC,age DESC, addr DESC;

1
2
3
4
5
6
7
8
1mysql> explain select * from test order by name ASC,age DESC, addr DESC;      

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自己建了一套索引再进行查询动作，由此可见效率低，耗资源。

1. select * from test where id=2 order by age, addr;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where id=2 order by age, addr;                      

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自己建了一套索引再进行查询动作，由此可见效率低，耗资源。

1. select * from test where name=‘asdf’ order by addr;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='asdf' order by addr;  

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的有效索引键满足最左前缀原则。

1. select * from test where name=‘asdf’ order by age,collage;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name='asdf' order by age,collage;

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不是索引的一部分。

1. select * from test where name in (‘xuebao’, ‘tujiu’, ‘sanyin’) order by age,addr;

1
2
3
4
5
6
7
8
1mysql> explain select * from test where name in ('xuebao', 'tujiu', 'sanyin') order by age,addr;

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自己建了一套索引再进行查询动作，由此可见效率低，耗资源。因为对排序来说，多个相等条件也是范围查询。