「这是我参与2022首次更文挑战的第3天，活动详情查看：2022首次更文挑战」
主要是总结一下索引失效的一些场景,我的数据库是MySql5.7.34，下面是测试用的数据库字段和索引，数据量有五千，数据量太小有时候不会触发索引，因为这种情况下全表扫描会比索引的效率更高。
1. 使用了select *
mysql> explain select * from user where name='qiuye';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
在该sql中用了select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率低，如果查询的时候，只查我们真正需要的列，而不查所有列
mysql> explain select code,name from user where name='qiuye';
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | user | NULL | index | NULL | idx_code_age_name | 211 | NULL | 3 | 33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
从结果来看，该sql语句这次走了全索引扫描，比全表扫描效率更高。
如果select语句中的查询列，都是索引列，不回表，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。要强调的是：并不是只要用了Select * ，sql语句就不使用索引了，只是Select *这部分没使用索引，后面的情况会看到的。
2. 不满足最左匹配
建表时建立好的组合索引idx_code_age_name，该索引字段的顺序是：code、age、name
2.1 哪些情况索引有效？
mysql> explain select * from user where code='101';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 83 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where code='101' and age=21;
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 88 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where code='101' and age=21 and name='周星驰';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 211 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where code = '101' and name='周星驰';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 83 | const | 1 | 33.33 | Using index condition |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
查询条件原本的顺序是：code、age、name，但这里只有code和name中间断层了，掉了age字段，这种情况也能走code字段上的索引。这4条sql中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。
2.2 哪些情况索引失效
mysql> explain select * from user where age=21;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> explain select * from user where age=21 and name='周星驰';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> explain select * from user where name='周星驰';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
说明以上3种情况不满足最左匹配原则，说白了是因为查询条件中，没有包含给定字段最左边的索引字段，即字段code。
3. 索引列参与计算
mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where id=1+1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from user where id+1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
从第一个结果可以看出，由于id字段是主键，该sql语句用到了主键索引。第二个结果是id未参与运算，第三个是id参与了运算，结果索引失效。
4. 索引列用了函数
有时候我们在某条sql语句的查询条件中，需要使用函数，比如：截取某个字段的长度。
假如现在有个需求：想查出所有身高是一米七几的人，如果sql语句写成这样：
mysql> explain select * from user where height=17;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_height | idx_height | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
虽然用了索引，但sql语句肯定是有问题的，因为它只能查出身高正好等于17的，根本不是查看一米七几的。
为了满足需求，把sql语句稍稍改造了一下，这时需要用到SUBSTR函数，用它截取了height字段的前面两位字符，从第一个字符开始。
mysql> explain select * from user where SUBSTR(height,1,2)=17;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
在使用该函数之后，该sql语句走了全表扫描，索引失效了。
5. 字段类型不同
在sql语句中因为字段类型不同，而导致索引失效的问题。以code字段为例，它是varchar字符类型的。在sql语句可以写成：
mysql> explain select * from user where code="101";
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 83 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
从上图中看到，该code字段走了索引。但如果把引号弄掉了，把sql语句变成了下面这样：
mysql> explain select * from user where code=101;
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | idx_code_age_name | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
mysql> select * from user where code=101; -- 可以查出结果，但不走索引
+----+------+------+-----------+--------+---------+
| id | code | age | name | height | address |
+----+------+------+-----------+--------+---------+
| 1 | 101 | 21 | 周星驰 | 175 | 香港 |
+----+------+------+-----------+--------+---------+
该sql语句竟然变成了全表扫描。因为code字段的类型是varchar，而传参的类型是int，两种类型不同。
如果int类型的height字段，在查询时加了引号条件，却还可以走索引：
mysql> explain select * from user where height='175';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_height | idx_height | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
mysql> explain select * from user where height=175;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_height | idx_height | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
int类型的参数，不管在查询时加没加引号，都能走索引。mysql发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。mysql会把上面列子中的字符串175，转换成数字175，所以仍然能走索引。select 1 + '1'的结果是2; 拼接字符串用concat
它的执行结果是2，还是11呢？ 为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？
根据mysql官网上解释，字符串'1'、' 1 '、'1a'（第一个“1”无空格，第二个“1”有）都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，mysql怎么知道该把int类型的1转换成哪种字符串来进行查询呢？用哪个索引快速查值呢？很显然这是行不通的。
6. like（数据量）
举个例子：想查出所有code是10开头的用户。
mysql> explain select * from user where code like '10%'; -- 数据库里只有三条记录，不走索引
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | idx_code_age_name | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+
数据量有4980条，走索引
但是如果百分号在左边呢？或者左右都有？
mysql> explain select * from user where code like '%1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> explain select * from user where code like '%0%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
很显然，这样也是不会使用索引的。
7. 列对比
比如user表中id字段和height字段，查询出这两个字段中值相同的记录。
mysql> explain select * from user where id=height;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。但如果把两个单独建了索引的列，用来做列对比时索引会失效。
8. 使用or关键字
mysql> explain select * from user where id=1 or height=175; ##数据量只有三条，没走索引
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | PRIMARY,idx_height | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
explain select * from user where id=1 or height=175; ##数据量为4980
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+------------------------------------
----------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+------------------------------------
----------+
| 1 | SIMPLE | user | NULL | index_merge | PRIMARY,idx_height | PRIMARY,idx_height | 4,5 | NULL | 39 | 100.00 | Using union(PRIMARY,idx_height); Us
ing where |
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+------------------------------------
----------+
mysql> explain select * from user where id=1 or height='175' or address='成都';
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | PRIMARY,idx_height | NULL | NULL | NULL | 4980 | 19.02 | Using where |
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。
9. not in和not exists
在我们日常工作中用得也比较多的，还有范围查询，常见的有：
9.1 in关键字
假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：
mysql> explain select * from user where height in (173,174,175,176); ##三条数据
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | idx_height | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
##4980条数据
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | range | idx_height | idx_height | 5 | NULL | 180 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
从图中可以看出，sql语句中用in关键字是走了索引的，但还是得看数据量大小，MySql会自己优化选择效率高的方式。
9.2 exists关键字
有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果：
mysql> explain select * from user t1 where exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id);
+----+--------------------+-------+------------+--------+--------------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+--------------------+---------+---------+---------------+------+----------+-------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | eq_ref | PRIMARY,idx_height | PRIMARY | 4 | demo_01.t1.id | 1 | 33.33 | Using where |
+----+--------------------+-------+------------+--------+--------------------+---------+---------+---------------+------+----------+-------------+
从图中可以看出，用exists关键字同样走了索引。
9.3 not in关键字
mysql> explain select * from user where height not in (173,174,175,176);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | idx_height | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
not in是不会使用索引的，切记，不要使用！！！想查一下id不等于1、2、3的用户有哪些，这时sql语句可以改成这样：
explain select * from user where id not in (1,2,3);
mysql> explain select * from user where id not in (1,2,3);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
9.4 not exists关键字
除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：
mysql> explain select * from user t1 where not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id);
+----+--------------------+-------+------------+--------+--------------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+--------------------+---------+---------+---------------+------+----------+-------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4980 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | eq_ref | PRIMARY,idx_height | PRIMARY | 4 | demo_01.t1.id | 1 | 5.00 | Using where |
+----+--------------------+-------+------------+--------+--------------------+---------+---------+---------------+------+----------+-------------+
从图中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。
10. order by的坑
10.1 哪些情况走索引？
在code、age和name这3个字段上建了联合索引：idx_code_age_name。
10.1.1 满足最左匹配原则
order by后面的条件，也要遵循联合索引的最左匹配原则。具体有以下sql：
单独使用order by走索引，必须满足覆盖索引的条件，并且遵守最左匹配原则，可以看看下面的例子。
mysql> explain select * from user order by code;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 4980 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
mysql> explain select code,age,name from user order by code;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | idx_code_age_name | 211 | NULL | 4980 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
mysql> explain select code,age,name from user order by age;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | user | NULL | index | NULL | idx_code_age_name | 211 | NULL | 4980 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
---+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
mysql> explain select * from user order by code limit 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 4980 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
mysql> explain select * from user order by code,age limit 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 4980 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
mysql> explain select * from user order by code,age,name limit 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 4980 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
10.1.2 配合where一起使用
order by还能配合where一起遵循最左匹配原则。
mysql> explain select * from user where code='101' order by age;
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 83 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
code是联合索引的第一个字段，在where中使用了，而age是联合索引的第二个字段，在order by中接着使用。
假如中间断层了，sql语句变成这样，
mysql> explain select * from user where code='101' order by name;
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 83 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。
10.1.3 相同的排序
order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。
具体sql如下：
mysql> explain select code,age,name from user order by code desc,age desc limit 100;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | idx_code_age_name | 211 | NULL | 100 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
该示例中order by后面的code和age字段都用了降序，所以依然走了索引。
10.1.4 两者都有
如果某个联合索引字段，在where和order by中都有，结果会怎么样？
explain select * from user where code='101' order by code, name;
mysql> explain select * from user where code='101' order by code, name;
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | user | NULL | ref | idx_code_age_name | idx_code_age_name | 83 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
code字段在where和order by中都有，对于这种情况，还是能走了索引的。
10.2 哪些情况不走索引？
好了，接下来，重点聊聊order by的哪些情况下不走索引？
10.2.1 没满足覆盖索引
explain select * from user order by code, name;
mysql> explain select * from user order by code, name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
10.2.2 对不同的索引做order by
前面介绍的基本都是联合索引，这一个索引的情况。但如果对多个索引进行order by，结果会怎么样呢？
mysql> explain select * from user order by code, height limit 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 4980 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
从结果中看出索引也失效了。
10.2.3 不满足最左匹配原则
前面已经介绍过，order by如果满足最左匹配原则，还是会走索引。下面看看，不满足最左匹配原则的情况：
explain select * from user order by name limit 100;
mysql> explain select * from user order by name limit 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
name字段是联合索引的第三个字段，从图中看出如果order by不满足最左匹配原则，确实不会走索引。
10.2.4 不同的排序
前面已经介绍过，如果order by后面有一个联合索引的多个字段，它们具有相同排序规则，那么会走索引。
但如果它们有不同的排序规则呢？
mysql> explain select code,age,name from user order by code asc,age desc limit 100;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | user | NULL | index | NULL | idx_code_age_name | 211 | NULL | 4980 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
从结果中看出，尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效，但是SQL语句满足了覆盖索引。
