MySQL优化2之索引和explain

1、sql性能下降的原因

• 查询语句写的烂
• 索引失效
• 连接查询（join）太多
• 服务器调优和各个参数设置（缓冲、线程数等）

2、sql执行顺序

SELECT DISTINCT                      5
    <select_list>
FROM                                 1
    <left_table> <join_type>
JOIN                                 1
    <right_table> 
ON                                   1
    <join_condition>
WHERE                                2
    <where_condition>
GROUP BY                             3
    <group_by_list>
HAVING                               4
    <having_condition>
ORDER BY                             6          
    <order_by_condition>
LIMIT                                7
    <limit_number>

3、七种JOIN理论

3.1、前提

数据库里面有员工表和部门表，数据如下：

mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)

3.2、inner join

mysql> select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)

3.2、left join

mysql> select * from tbl_emp e left join tbl_dept d on e.deptid=d.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)

3.3、right join

mysql> select * from tbl_emp e right join tbl_dept d on e.deptid=d.id;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | HR       | 12     |
|    5 | w6   |      2 |  2 | HR       | 12     |
|    6 | s7   |      3 |  3 | MK       | 13     |
|    7 | s8   |      4 |  4 | MIS      | 14     |
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)

3.4、left join 2

mysql> select * from tbl_emp e left join tbl_dept d on e.deptid=d.id where d.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)

3.5、right join 2

mysql> select * from tbl_emp e right join tbl_dept d on e.deptid=d.id where e.deptid is null;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)

3.6、full join

MySQL不支持full join，但可以使用union表示

mysql> select * from tbl_emp e left join tbl_dept d on e.deptid = d.id union select * from tbl_emp e right join tbl_dept d on e.deptid = d.id;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)

3.7、full join 2

mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)

4、索引

4.1、索引是什么

定义：索引是帮助MySQL高效获取数据的数据结构，可以简单理解为“排好序的快速查找数据结构”。

详解：

左边是数据表，一共两列7条记录，最左边的是数据记录的物理地址。

右边是二叉查找树（索引），每个节点包含索引键值和指向对于数据记录的物理地址的指针。利用二叉查找树可以快速检索出符合条件的数据。

补充：一般来说索引本身很大，往往以索引文件的形式存储到磁盘上。

4.2、索引的优缺点

• 优点

  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

• 缺点

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引占用空间

  • 降低更新表的速度。因为更新表时，MySQL不仅要更新数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

总结：索引用空间换时间

4.3、索引分类

4.3.1、按字段数量分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
• 复合索引：即一个索引包含多个列。

4.3.2、按字段值特点分类

• Primary Key（聚集索引）

  InnoDB存储引擎的表会存在主键，如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

• Unique（唯一索引）

  索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

• Key（普通索引）

  MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

• FULLTEXT（全文索引）

  在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

• SPATIAL（空间索引）

  空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL。

4.4、建索引的sql

4.4.1、创建索引

CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); # 主键索引
ALTER TABLE tbl name ADD UNIQUE index_name (column_list); # 唯一索引
ALTER TABLE tbl_name ADD INDEX index_name (column_list); # 普通索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); # 全文索引

4.4.2、删除索引

DROP INDEX [indexName] ON mytable;

4.4.3、查看索引

SHOW INDEX FROM tableName;

4.5、哪些情况适合建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

4.6、哪些情况不适合建索引

• 表记录太少

• 经常增删改的表，频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引

• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。如性别字段不适合建索引。

• where条件里用不到的字段不创建索引

5、性能分析

5.1、前提知识

5.1.1、优化器

MySQL中有专门负责优化SELECT语句的优化器，优化器会使用他认为最好的方式来优化sql，并生成一条条执行计划。

5.1.2、MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

5.2、explain

作用：分析查询语句性能，查看执行计划

用法：explain select语句

演示：

5.2.1、explain之id介绍

说明：id表示查询中执行select子句或操作表的顺序

id的情况：

• id相同：执行顺序从上至下
• id不同：id值越大优先级越高，越先被执行

演示：

5.2.2、explain之select_type介绍

说明：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

select_type的分类：

种类	说明
SIMPLE	简单的select查询,查询中不包含子查询或者UNION
PRIMARY	复杂查询中最外层的select被标记为primary
SUBQUERY	在select 或 where列表中包含了子查询，子查询不在 from 子句中
DERIUED	from中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
UNION	若第二个select出现在union之后，则被标记为UNION；若UNION包含在from子句的子查询中外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

5.2.3、explain之table介绍

说明：显示这一行的数据是关于哪张表的

注意：当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表示参与 union 的 select行id。

5.2.4、explain之type介绍

说明：type显示的是访问类型，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

详细说明：

• system ：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

• const ：表示通过索引一次就找到了，const用于primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

  

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

  

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。

  

• range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  

• index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。

  

• all：Full Table Scan，将遍历全表以找到匹配的行。

  

5.2.5、explain之possible_keys和key介绍

possible_keys

理论上这张表上能用到的索引，但不一定被查询实际使用。

key

实际上使用的索引。如果为NULL，则没有使用索引。
演示：

查询中若使用了覆盖索引，则该索引仅出现在key列表中。

5.2.6、explain之key_len介绍

说明：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

注意：key_len显示的值为索引字段的最大可能长度，并非实际使用长度

演示：

5.2.7、explain之ref介绍

说明：表示索引的哪些列或常量被用于查找数据了。

演示：

t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’

5.2.8、explain之rows介绍

说明：根据表统计信息及索引选用情况，大致估算出找到满足条件的记录需要读取的行数。

5.2.9、explain之Extra介绍

常见的Extra值：

• Using filesort：MySQL中无法利用索引完成的排序操作称为"文件排序"，出现这个信息表示性能很差。

  

• Using temporary：MysQL在对查询结果使用临时表，常见于排序order by和分组查询group by。出现这个信息表示性能差透了。

  

• Using index：表示相应的select操作中使用了覆盖索引，效率不错！

  • 如果同时出现using where，表明索引被用来执行索引键值的查找；

    

  • 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

    

• Using where：表明使用了where过滤。

• Using join buffer：使用了连接缓存。

• impossible where：where子句的值总是false，不能用来获取任何元组。

• select tables optimized away：在没有group by子句的情况下，基于索引优化min/max操作，或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

• distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

5.3、explain案例

第一行（执行顺序4）：select_type列的primary表示该查询为外层查询，table列被标记为<derived3>，表示查询结果来自一个临时表，3代表该查询衍生自第三个select查询，即id为3的select。select d1.name…

第二行（执行顺序2）：因查询包含在from中，所以为derived。select id,namefrom t1 where other_column=’’

第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。select id from t3

第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行select name,id from t2

第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。两个结果union操作

5.4、索引覆盖（覆盖索引）

理解方式一

select的数据列只用从索引中就能够取得，不必读取数据行。MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

5.5、索引单表优化案例

mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  1 |         1 |           1 |     1 |        1 | 1     | 1       |
|  2 |         2 |           2 |     2 |        2 | 2     | 2       |
|  3 |         1 |           1 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)

案例：查询category_id为1且comments 大于1的情况下，views最多的author_id。

mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  3 |         1 |
+----+-----------+
1 row in set (0.00 sec)

mysql> show index from article\G
*************************** 1. row ***************************
        Table: article
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.01 sec)

优化：

mysql> create index idx_article_cv on article(category_id, views);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: idx_article_cv
          key: idx_article_cv
      key_len: 4
          ref: const
         rows: 2
     filtered: 33.33
        Extra: Using where; Backward index scan
1 row in set, 1 warning (0.00 sec)

思考：创建如下索引发现Extra还有Using filesort，说明该索引作用很小，为什么？

create index idx_article_ccv on article(category_id,comments,views);

回答：因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

5.6、索引两表优化案例

mysql> select * from class;
+----+------+
| id | card |
+----+------+
|  1 |   11 |
......

mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      1 |   10 |
......

mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   21 |   100.00 | NULL                                       |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

优化：左连接优化就在右表建立索引；右连接优化就在左表建立索引。

mysql> ALTER TABLE `book` ADD INDEX Y(`card`);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL           |   21 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | db1.class.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

5.7、索引三表优化案例

mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
|       1 |   20 |
|       2 |   10 |
......

mysql> select * from class;
+----+------+
| id | card |
+----+------+
|  1 |   11 |
......

mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      1 |   10 |
......

mysql> alter table `phone` add index z(`card`);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table `book` add index y(`card`);

mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL           |   21 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | db1.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | z             | z    | 4       | db1.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

索引最好设置在需要经常查询的字段中

join语句优化结论：

• 永远用小结果集驱动大的结果集

• 保证join语句中被驱动表上join条件字段已经被索引。

• 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

5.8、索引失效

• 最佳左前缀法则：对于复合索引，要遵守最左前缀法则。即查询从索引的最左前列开始并且不跳过复合索引中间列。
• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
• 存储引擎不能使用索引中范围条件右边的列。
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
• mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
• is null, is not null 也无法使用索引。
• like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
• 字符串不加单引号索引失效。
• 少用or，用它来连接时会索引失效。

索引失效1-跳过复合索引中间列

以下sql索引没有失效：

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

以下sql索引失效了：

mysql> EXPLAIN SELECT * FROM staffs WHERE age=25 AND pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效2-索引列上做额外操作

mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4)='July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效3-限制复合索引某列的范围

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

由age=25变成age>25后，type从ref变成range。

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND pos='dev' and age > 25;
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

索引失效4-select *

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效5-!=或者<>

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME!='July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME<>'July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效6-is null或者is not null

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is not null;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效7-like以通配符%开头字符串

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

问题：解决like '%字符串%'时索引不被使用的方法

使用主键字段或者覆盖索引来解决

mysql> CREATE TABLE `tbl_user`(
    -> `id` INT(11) NOT NULL AUTO_INCREMENT,
    -> `name` VARCHAR(20) DEFAULT NULL,
    -> `age`INT(11) DEFAULT NULL,
    -> `email` VARCHAR(20) DEFAULT NULL,
    -> PRIMARY KEY(`id`)
    -> )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 3 warnings (0.02 sec)

mysql> CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id from tbl_user where name like '%a%';  # id是主键，索引有效
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';  # 覆盖索引，索引有效
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

如果是以下的sql，索引会失效

mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效8-数目字符串不加单引号

本质上属于索引字段值的类型自动转换，从字符串类型自动转换成数字类型，从而导致索引失效

mysql> explain SELECT * FROM staffs WHERE NAME=2000;   # varchar类型数据没有加单引号，索引失效
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> explain SELECT * FROM staffs WHERE NAME='2000';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

索引失效9-用关键字OR

mysql> explain SELECT * FROM staffs WHERE NAME='July' or name='z3';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效小结

假设index(a,b,c)

where语句	索引是否被使用
where a = 3	Y，使用到a
where a = 3 and b = 5	Y，使用到a，b
where a = 3 and b = 5 and c = 4	Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b，c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c = 4	Y，使用到a，b，c

5.9、索引案例分析

create table test03(
    id int primary key not null auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

mysql> show index from test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test03 |          0 | PRIMARY          |            1 | id          | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test03 |          1 | idx_test03_c1234 |            1 | c1          | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test03 |          1 | idx_test03_c1234 |            2 | c2          | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test03 |          1 | idx_test03_c1234 |            3 | c3          | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test03 |          1 | idx_test03_c1234 |            4 | c4          | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)

案例一

explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

以上sql都用上了索引。

案例二

explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';

以上sql都用上了索引。因为优化器会使用他认为最好的方式来优化sql，将c1='a1' and c2='a2' and c3='a3' and c4='a4'优化成c1='a1' and c2='a2' and c3='a3' and c4='a4'。

案例三

explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

mysql> explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 123     | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 164     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

案例四

mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

c3的作用在于排序，不在于查找。

mysql> explain select * from test03 where c1='a1' and c2='a2' order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2='a2' order by c4;   # 出现了Using filesort
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; # 只用c1一个字段索引，但是c2、c3用于排序，无filesort
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;  # 我们建的索引是1234，它没有按照顺序来，3，2颠倒了,出现了Using filesort
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

案例五

mysql> explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                                  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

案例六

mysql> explain select * from test03 where c1='a1' and c2 like 'kk%' and c3='a3';     #用到了c1,c2,c3索引
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 123     | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2 like '%kk' and c3='a3';   #用到了c1索引
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2 like '%kk%' and c3='a3';  #用到了c1索引
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test03 where c1='a1' and c2 like 'k%kk%' and c3='a3';   #用到了c1,c2,c3索引
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 123     | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

一般性建议

• 对于单键索引，尽量选择针对当前查询过滤性更好的索引。
• 在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前查询中的where字句中更多字段的索引。
• 尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的。

5.10、优化总结口诀

全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR，索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。