索引优化分析

MySQL学习笔记

索引优化分析

SQL性能下降原因

• 执行时间长、等待时间长
• 四个原因：查询语句繁琐，索引失效，关联查询太多join（设计缺陷或或不得已的要求），服务器调优及各个参数设置（缓冲、线程数等）

Join查询

• 机读指令顺序：

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DESTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

• 七种join理论：假设表A与表B：

1. C=A∩B

select * from tableA A INNER join TableB B on A.Key=B.Key;

2. C=A∪(A∩B)=A

select * from TableA A LEFT join TableB B on A.Key=B.Key;

3. C=B∪(A∩B)=B

select * from TableA A RIGHT join TableB B on A.Key=B.Key;

4. C=A∩(A∩B)

select * from TableA A LEFT join TableB B on A.Key=B.Key where B.Key is NULL;

5. C=B∩(A∩B)

select * from TableA A RIGHT join TableB B no A.Key=B.Key where B.Key is NULL;

6. C=A∪B

select * from TableA A FULL OUTER join TableB B on A.Key=B.Key;

7. C=(A∩(A∩B))∪(B∩(A∩B)) = C(A∩B)

select * from TableA A FULL OUTER join TableB B on A.Key=B.Key where A.Key is NULL or B.Key is NULL;

• 下面进行实战练习

create table tbl_dept(
id int(11) not null auto_increment,
deptName varchar(30) default null,
locAdd varchar(40) default null,
primary key(id)
)engine=INNODB auto_increment=1 default charset=utf8;

CREATE TABLE tbl_emp(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY(id),
KEY fk_dept_id(deptId)
#CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES tbl_dept(id) 
)ENGINE=INNODB AUTO_increment=1 DEFAULT CHARSET=utf8;

insert into tbl_dept(deptName,locAdd) values('RD',11);
insert into tbl_dept(deptName,locAdd) values('HR',12);
insert into tbl_dept(deptName,locAdd) values('MK',13);
insert into tbl_dept(deptName,locAdd) values('MIS',14);
insert into tbl_dept(deptName,locAdd) values('FD',15);

insert into tbl_emp(name,deptId) values('z3',1);
insert into tbl_emp(name,deptId) values('z4',1);
insert into tbl_emp(name,deptId) values('z5',1);

insert into tbl_emp(name,deptId) values('w5',2);
insert into tbl_emp(name,deptId) values('w6',2);

insert into tbl_emp(name,deptId) values('s7',3);

insert into tbl_emp(name,deptId) values('s8',4);

insert into tbl_emp(name,deptId) values('s9',51);

• 我们使用select * from tbl_emp可以获得八行，使用select * from tbl_dept可以获得五行，使用select * from tbl_emp,tbl_dept有40行。

• 然后分别验证七种join。例如mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

• 如果使用left等，以left为例，如果左表满足、右表不满足，则右边的部分补null。

mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.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   |
+----+------+--------+------+----------+--------+

• 第四种，A独有的部分：

mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+

• 第六种的mysql语句会出现问题，事实上mysql不支持这种语法格式，使用union关键字：合并并去重。

mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
    -> union
    -> select * from tbl_emp a right join tbl_dept b on a.deptId = b.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)

索引

• 索引是帮助MySQL高效获取数据的数据结构。
• 有两大基本功能：排序和查找。可以简单理解为：排好序的快速查找数据结构。
• 数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
• 我们平常说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引，其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。
• 优势：类似大学图书馆建立书目索引，提高数据检索的效率，降低数据库的IO成本。通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
• 劣势：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update、delete操作时，MySQL不仅要保存数据，还要保存下一索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花更多时间研究建立最优秀的索引，或优化查询。
• 索引分类：单值索引：一个索引只包含单个列，一个表可以有多个单列索引。唯一索引：索引列的值必须唯一，但允许空值。复合索引：即一个索引包含多个列。
• 索引基本语法：

# 创建
create [unique] index indexName ON mytable(columnname(length));
alter mytable ADD [unique] index [indexName] ON (columnname(length));

# 删除
drop index [indexName] ON mytable;

# 查看
show index from table_name;

• mysql索引结构：BTree索引、hash索引、full-text全文索引、R-Tree索引。主要研究BTree索引。
• 适合创建索引的情况：
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引
  • where条件里用不到的字段不创建索引。
  • 单键/组合索引的选择问题。高并发下倾向于创建组合索引
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
  • 查询中统计或者分组字段
• 不要创建索引的情况：
  • 表记录太少（理论上三百万条数据性能会开始下降）
  • 经常增删改的表
  • 数据重复且分布平均的表字段，因此应该只为最经常查询的和最经常排序的数据列建立索引。**索引的选择性是指索引列中不同值的数目与表中记录数的比。**如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99，一个索引的选择性越接近1，这个索引的效率就越高。

性能分析

• MySQL中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的不一定是DBA认为的最优的，所以这部分最耗费时间）
• 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，去别处是select并转发给Mysql Query Optimizer时，它会首先对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
• MySQL常见瓶颈：
  • CPU：CPU在饱和时候一般发生在数据装入内存或从磁盘上读取数据的时候。
  • IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态。

explain

• 是什么：查看执行计划。使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL如何处理SQL语句。分析查询语句或是表结构的性能瓶颈。
• 能干啥：
  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询
• 怎么用：exlpain+SQL语句，执行计划包含的信息。

mysql> explain select * from  tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 表头各字段解释：

  • id：select查询到序列号，包含一组数字，表示查询中执行的select字句或操作表的顺序。有三种情况：id相同执行顺序由上到下；id不同：子查询id值越大越先被执行，而后执行主查询；id相同不同同时存在：子查询里，id越大越先被执行，第n个id被加载的叫做衍生，derivedN。id越大越先执行，id相同从上往下顺序执行。
  • select_type：有六种：simple,primary,subquery,derived,union,union result。查询的类型。
    • simple：简单的select查询，查询中不包含子查询或者union
    • primary：查询中若包含任何子查询，最外层的就是primary
    • subquery：在select或where列表中包含子查询
    • derived：在from列表中包含的子查询被标记为derived，MySQL会递归执行这些子查询，把结果放在临时表里。
    • union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为dericed。
    • union result：从union表获取结果的select
  • table：显示这一行数据是关于哪张表的
  • type：显示查询使用了何种类型，从最好到最差依次是system>const>eq_ref>ref>range>index>ALL。一般来说得保证查询至少达到range级别，最好能达到ref。
    • system：表只有一行记录（等与系统表），这是const类型的特例，平常不会出现。
    • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
    • eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好。
    • index：只遍历索引树，通常比全表扫描ALL快，因为索引文件通常比数据文件要小。
    • ALL：全表扫描 lj。

• possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

• key：实际使用的索引，如果为null则表示没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中（也就是possible_keys是null，但是key仍然有值），select查询字段和个数刚好吻合，从索引找到答案。

• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值是索引字段的最大可能长度，并非实际长度。即key_len是根据表定义计算而得的，不是通过表内检索出的。

• ref：显示索引的哪一列被使用了，如果可能的话是一个常数。哪些列或常量被用于查找索引的值。

• rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

索引优化

案例一：单表优化

mysql> create table if not exists `article`(
    -> `id` int(10) unsigned not null primary key auto_increment,
    -> `author_id` int(10) unsigned not null,
    -> `category_id` int(10) unsigned not null,
    -> `views` int(10) unsigned not null,
    -> `comments` int(10) unsigned not null,
    -> `title` varbinary(255) not null,
    -> `content` text not null);

mysql> insert into `article` ( `author_id`,`category_id`,`views`,`comments`,`title`,`content`) values (1,1,1,1,'1','1'),
    -> (2,2,2,2,'2','2'),
    -> (1,1,3,3,'3','3');
Query OK, 3 rows affected (0.00 sec)

• 查询category_id为1且comments大于1的情况下，views最多的article_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> explain select id,author_id from article where caregory_id = 1 and comments > 1 order by views DESC limit 1;
ERROR 1054 (42S22): Unknown column 'caregory_id' in 'where clause'
mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views DESC limit 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• 可以发现，extra里出现Using where; Using filesort，type是ALL，即最坏的情况。优化是必须的。

mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

• 尝试创建索引：前面我们使用到了category_id，comments，views三个列，那么就可以先用这三个列尝试创建索引。

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

mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

• 创建完复合索引之后，我们可以使用explain来试一试。

mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views DESC limit 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

• 可以看到，type变为了range，使用的索引为idx_article_ccv，但是，Using filesort任然没有解决。如果我们尝试性的把select语句中的comments > 1改为comments = 1，会发现

mysql> explain select id,author_id from article where category_id = 1 and comments = 1 order by views DESC limit 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_ccv | idx_article_ccv | 8       | const,const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 我们可以发现，范围性的索引会导致性能下降。当前的索引，效果并不是特别好，需要重新建立索引。

mysql> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> show index from article;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_cv |            1 | category_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_cv |            2 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows 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;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_cv | idx_article_cv | 4       | const |    2 |    33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 检索加排序，同时用到索引，结果非常理想。

案例二：双表优化

mysql> create table if not exists `class`(
    -> `id` int(10) unsigned not null auto_increment,
    -> `card` int(10) unsigned not null,
    -> primary key(`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists `book`( `bookid` int(10) unsigned not null auto_increment, `card` int(10) unsigned not null, primary key(`bookid`));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into class(card) values(FLOOR(1+(RAND()*20)));
Query OK, 1 row affected (0.00 sec)
...

mysql> insert into book(card) values(FLOOR(1+(RAND()*20)));
Query OK, 1 row affected (0.00 sec)
...

mysql> select * from book inner join class on book.card=class.card;
+--------+------+----+------+
| bookid | card | id | card |
+--------+------+----+------+
|     12 |    8 |  1 |    8 |
|      4 |   12 |  2 |   12 |
|     12 |    8 |  4 |    8 |
|      5 |    9 |  5 |    9 |
|      6 |    9 |  5 |    9 |
|      9 |    2 |  6 |    2 |
|     16 |    2 |  6 |    2 |
|     10 |   20 |  7 |   20 |
|      7 |   18 |  9 |   18 |
|     13 |   18 |  9 |   18 |
|      9 |    2 | 10 |    2 |
|     16 |    2 | 10 |    2 |
|      2 |   15 | 12 |   15 |
|     19 |   13 | 15 |   13 |
|     19 |   13 | 16 |   13 |
|     14 |    7 | 17 |    7 |
|     17 |    7 | 17 |    7 |
|     18 |    7 | 17 |    7 |
|     19 |   13 | 18 |   13 |
+--------+------+----+------+
19 rows in set (0.00 sec)

• 下面开始explain分析：

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 |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

• type有all。添加索引优化：

mysql> alter table `book` add index Y (`card`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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            |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | db26.class.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

• 换用另一种索引试一试

mysql> drop index Y on book;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table `class` add index Y (`card`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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       | index | NULL          | Y    | 4       | NULL |   20 |   100.00 | Using index                                        |
|  1 | SIMPLE      | book  | NULL       | ALL   | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

• 显然，同样的索引，加在左表和右表是不一样的。这是由左连接特性决定的。left jion 条件用于确定如何从右表搜索行，左边一定都有。

• 记住左连接建在右表，右连接建在左表，反着建就好了。

案例三：三表优化

mysql> create table if not exists `phone`( `phoneid` int(10) unsigned not null auto_increment, `card` int(10) unsigned not null, primary key (`phoneid`) )engine=INNODB;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into phone(card) values(floor(1+(rand()*20)));
Query OK, 1 row affected (0.00 sec)
...

mysql> select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
+----+------+--------+------+---------+------+
| id | card | bookid | card | phoneid | card |
+----+------+--------+------+---------+------+
|  2 |   12 |      4 |   12 |       2 |   12 |
| 15 |   13 |     19 |   13 |       6 |   13 |
| 16 |   13 |     19 |   13 |       6 |   13 |
| 18 |   13 |     19 |   13 |       6 |   13 |
| 12 |   15 |      2 |   15 |       7 |   15 |
| 17 |    7 |     14 |    7 |       9 |    7 |
| 17 |    7 |     17 |    7 |       9 |    7 |
| 17 |    7 |     18 |    7 |       9 |    7 |
|  2 |   12 |      4 |   12 |      10 |   12 |
| 12 |   15 |      2 |   15 |      16 |   15 |
|  9 |   18 |      7 |   18 |      17 |   18 |
|  9 |   18 |     13 |   18 |      17 |   18 |
|  5 |    9 |      5 |    9 |    NULL | NULL |
|  5 |    9 |      6 |    9 |    NULL | NULL |
|  6 |    2 |      9 |    2 |    NULL | NULL |
| 10 |    2 |      9 |    2 |    NULL | NULL |
|  7 |   20 |     10 |   20 |    NULL | NULL |
|  1 |    8 |     12 |    8 |    NULL | NULL |
|  4 |    8 |     12 |    8 |    NULL | NULL |
|  6 |    2 |     16 |    2 |    NULL | NULL |
| 10 |    2 |     16 |    2 |    NULL | NULL |
|  3 |   17 |   NULL | NULL |    NULL | NULL |
|  8 |   16 |   NULL | NULL |    NULL | NULL |
| 11 |   16 |   NULL | NULL |    NULL | NULL |
| 13 |    5 |   NULL | NULL |    NULL | NULL |
| 14 |   17 |   NULL | NULL |    NULL | NULL |
| 19 |    5 |   NULL | NULL |    NULL | NULL |
| 20 |    5 |   NULL | NULL |    NULL | NULL |
+----+------+--------+------+---------+------+

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 |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

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

mysql> alter table `book` add index Y (`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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            |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | db26.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | z             | z    | 4       | db26.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

• 优化效果已经达到了。索引最好设置在需要经常查询的字段中。
• **结论：**要尽量减少join语句中的NestedLoop的循环总次数；永远用小结果集驱动大的结果集。优先优化NestedLoop的内层循环。保证Join语句中被驱动表上Join条件字段已经被索引。当无法保证驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

索引失效

• 是我们应该避免的情况。案例建表如下：

mysql> create table staffs( id int primary key auto_increment, `NAME` varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位', add_time timestamp not null default current_timestamp comment '入职时间' )charset utf8 comment '员工记录表';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into staffs(name,age,pos,add_time)values('z3',22,'manager',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> insert into staffs(name,age,pos,add_time)values('July',23,'dev',NOW());
Query OK, 1 row affected (0.01 sec)

mysql> insert into staffs(name,age,pos,add_time)values('2000',23,'dev',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | NAME | age | pos     | add_time            |
+----+------+-----+---------+---------------------+
|  1 | z3   |  22 | manager | 2021-04-25 15:59:49 |
|  2 | July |  23 | dev     | 2021-04-25 16:00:06 |
|  3 | 2000 |  23 | dev     | 2021-04-25 16:00:14 |
+----+------+-----+---------+---------------------+
3 rows in set (0.00 sec)

mysql> alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

• 我们建立了一个索引，使用这个索引：

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  | idx_staffs_nameAgePos | idx_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  | idx_staffs_nameAgePos | idx_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  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 可以发现，key_len从74变到78又变到140。
• 但是如果搜索的顺序发生改变，我们就会发现，索引不好使了。

mysql> explain select * from staffs where age = 23 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)

• 上面违背了最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

• 不要在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描：

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)

• 存储引擎不能使用范围条件右边的列：

mysql> explain select * from staffs where name='July' and age > 25 and pos = 'manager';
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 尽量使用覆盖引擎（只访问索引的查询（索引列与查询列一致）），减少select *。Using index 效果好。

mysql> explain select name,age,pos from staffs where name='July' and age=25 and pos='manager';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
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  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

• like以通配符开头的mysql索引失效，会变成全表扫描。一边而言使用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 | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
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       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 如果一定要使用两边都有%，即解决两边都使用%时索引失效的问题：

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 (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('1aa1',21,'b@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('2aa2',222,'a@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('3aa3',265,'c@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('4aa4',21,'d@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('aa',121,'e@163.com');
Query OK, 1 row affected (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       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id 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 |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
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       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
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       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
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       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
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       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
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       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 创建索引：

mysql> create index idx_user_nameAge on tbl_user(name,age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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 |    5 |    20.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
...

• 可以发现，单独id、name、age索引都不会失效，id,name、name,age、id,name,age也都不会失效，但是，发现*失效了。使用like时，百分号写在右边，如果非要两边百分号，那么就使用覆盖索引。
• 字符串不加单引号会导致索引失效。varchar类型不能失去单引号。

mysql> select * from staffs where name='2000';
+----+------+-----+-----+---------------------+
| id | NAME | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | 2000 |  23 | dev | 2021-04-25 16:00:14 |
+----+------+-----+-----+---------------------+
1 row in set (0.00 sec)

mysql> select * from staffs where name=2000;
+----+------+-----+-----+---------------------+
| id | NAME | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | 2000 |  23 | dev | 2021-04-25 16:00:14 |
+----+------+-----+-----+---------------------+
1 row in set, 1 warning (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  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (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       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

• 少用or，用它来连接时也会导致索引失效。