[大数据]mysql_性能优化三_高性能的索引使用策略

4.2.8.1. 不在索引列上做任何操作

我们通常会看到一些查询不当地使用索引，或者使得 MySQL 无法使用已有的索引。如果查询中的列不是独立的，则 MySQL 就不会使用索引。“独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数。
例如，我们假设 order_status 上有索引，但是下面这个查询无法使用 order_status 列的索引:

mysql> SELECT * FROM order_exp WHERE order_status + 1 = 1 limit 2;
+----+----------+------------------------------------------------------+---------------------+-----------------+---------------------+--------------+
| id | order_no | order_note                                           | insert_time         | expire_duration | expire_time         | order_status |
+----+----------+------------------------------------------------------+---------------------+-----------------+---------------------+--------------+
| 16 | DD00_15S | 你好，李焕英。7排15号,过期时长:DD00_15S              | 2021-03-22 18:23:42 |              15 | 2021-03-22 18:23:57 |            0 |
| 21 | DD00_10S | 你好，李焕英。7排10号,过期时长:DD00_10S              | 2021-03-22 18:28:18 |              10 | 2021-03-22 18:28:28 |            0 |
+----+----------+------------------------------------------------------+---------------------+-----------------+---------------------+--------------+
2 rows in set (0.00 sec)

凭肉眼很容易看出 WHERE 中的表达式其实等价于 order_status = 0，但是 MySQL 无法自动解析这个方程式。这完全是用户行为。我们应该养成简化 WHERE 条件的习惯，始终将索引列单独放在比较符号的一侧。
下面是另一个常见的错误:
mysql>SELECT … WHERE TO_DAYS(insert_time) - TO_DAYS(expire_time) <= 10; 在索引列上使用函数，也是无法利用索引的。

4.2.8.2. 尽量全值匹配

建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种 情况就称为全值匹配，比方说下边这个查找语句:

select * from order_exp where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time='2021-03-22 18:35:14';

我们建立的 u_idx_day_statusr 索引包含的 3 个列在这个查询语句中都展现出来了，联合索引中的三个列都可能被用到。
有的同学也许有个疑问，WHERE 子句中的几个搜索条件的顺序对查询结果有啥影响么?也就是说如果我们调换insert_time, order_status, expire_time这几个搜索列的顺序对查询的执行过程有影响么?比方说写成下边这样:

Select * from order_exp where order_status=1 and expire_time='2021-03-22 18:35:14' and insert_time='2021-03-22 18:34:55';

放心，MySQL 没这么蠢，查询优化器会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件。
所以，当建立了联合索引列后，能在 where 条件中使用索引的尽量使用。

4.2.8.3. 最佳左前缀法则

建立了联合索引列，如果搜索条件不够全值匹配怎么办?在我们的搜索语句中也可以不用包含全部联合索引中的列，但要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
比如: select * from order_exp where insert_time=‘2021-03-22 18:23:42’ and order_status=1;
或
select * from order_exp where insert_time=‘2021-03-2218:23:42’ ;
那为什么搜索条件中必须出现左边的列才可以使用到这个B+树索引呢?比如下边的语句就用不到这个 B+树索引么?
SELECT * FROM order_exp WHERE order_status=1;
或
Select * from order_exp where order_status=1 and expire_time=‘2021-03-22 18:35:14’;

思考一下，因为 B+树的数据页和记录先是按照 insert_time 列的值排序的， 在 insert_time 列的值相同的情况下才使用 order_status 列进行排序，也就是说 insert_time 列的值不同的记录中 order_status 的值可能是无序的。而现在你跳过 insert_time 列直接根据 order_status 的值去查找，怎么可能呢?expire_time 也是 一样的道理，那如果我就想在只使用 expire_time 的值去通过 B+树索引进行查找咋办呢?这好办，你再对 expire_time 列建一个 B+树索引就行了。
但是需要特别注意的一点是，如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。比方说联合索引 u_idx_day_status 中列的定义顺序是insert_time, order_status, expire_time，如果我们的搜索条件中只有 insert_time 和 expire_time，而没有中间的 order_status，
select * from order_exp where insert_time=‘2021-03-22 18:23:42’ and expire_time=‘2021-03-22 18:35:14’;
只能用到 insert_time 列的索引，order_status 和 expire_time 的索引就用不上了。

4.2.8.4. 范围条件放最后

这一点，也是针对联合索引来说的，所有记录都是按照索引列的值从小到大的顺序排好序的，而联合索引则是按创建索引时的顺序进行分组排序。
比如:

mysql> explain select * from order_exp_cut where insert_time>'2021-03-22 18:23:42' and insert_time<'2021-03-22 18:35:00';
+----+-------------+---------------+------------+------+------------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | ALL  | u_idx_day_status | NULL | NULL    | NULL |   30 |    96.67 | Using where |
+----+-------------+---------------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

由于 B+树中的数据页和记录是先按 insert_time 列排序的，所以我们上边的查询过程其实是这样的:
找到 insert_time 值为’2021-03-22 18:23:42’ 的记录。
找到 insert_timee 值为’2021-03-22 18:35:00’的记录。
由于所有记录都是由链表连起来的，所以他们之间的记录都可以很容易的取出来，找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。
但是如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+树索引:

mysql> explain select * from order_exp where insert_time>'2021-03-22 18:23:42' and insert_time<'2021-03-22 18:35:00' and order_status > -1;    
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | u_idx_day_status | u_idx_day_status | 5       | NULL |   63 |    33.33 | Using index condition |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

上边这个查询可以分成两个部分:
通过条件 insert_time>‘2021-03-22 18:23:42’ and insert_time<‘2021-03-22 18:35:00’ 来对 insert_time 进行范围，查找的结果可能有多条 insert_time 值不同 的记录，
对这些 insert_time 值不同的记录继续通过 order_status > -1 条件继续过滤。
这样子对于联合索引 u_idx_day_status 来说，只能用到 insert_time 列的部分， 而用不到 order_status 列的部分，因为只有 insert_time 值相同的情况下才能用 order_status 列的值进行排序，而这个查询中通过 insert_time 进行范围查找的记录中可能并不是按照 order_status 列进行排序的，所以在搜索条件中继续以 order_status 列进行查找时是用不到这个 B+树索引的。
所以对于一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左 边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找:

mysql> explain select * from order_exp where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time>'2021-03-22 18:23:57' and expire_time<'2021-03-22 18:35:00' ;    
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | u_idx_day_status,idx_expire_time | u_idx_day_status | 12      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

中间有范围查询会导致后面的列全部失效，无法充分利用这个联合索引:

mysql> explain select * from order_exp where insert_time='2021-03-22 18:23:42' and order_status>-1 and expire_time='2021-03-22 18:35:14';    
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | u_idx_day_status,idx_expire_time | u_idx_day_status | 7       | NULL |    1 |     5.00 | Using index condition |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

4.2.8.5. 覆盖索引尽量用

覆盖索引是非常有用的工具，能够极大的高性能，三星索引里最重要的那颗星就是宽索引星。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处:
索引条目通常远小于数据行大小，所以如果只需要读取索引，那 MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于 I/O 密集型的应用也有帮助，因为索引比数据更小,更容易全部放入内存中。
因为索引是按照列值顺序存储的，所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。
由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，不是必要的情况下减少 select *，除非是需要将表中的全部列检索后，进行缓存。

mysql> explain select * from order_exp where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time>'2021-03-22 18:23:57' and expire_time<'2021-03-22 18:35:00' ;    
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | u_idx_day_status,idx_expire_time | u_idx_day_status | 12      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select expire_time,id from order_exp where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time>'2021-03-22 18:23:57' and expire_time<'2021-03-22 18:35:00' ;
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys                    | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | u_idx_day_status,idx_expire_time | u_idx_day_status | 12      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+----------------------------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

以上两个查询表现肯定是不一样的。

4.2.8.6. 不等于要慎用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

mysql> explain SELECT * FROM order_exp WHERE order_no <> 'DD00_6S';
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | ALL  | idx_order_no  | NULL | NULL    | NULL | 10367 |    55.90 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

为什么?道理是明显的，这种情况下，扫描区间是[第一条记录,‘DD00_6S’] 和[‘DD00_6S’,最后一条记录]，加上回表，还不如直接进行全表扫描。

4.2.8.7. Null/Not 有影响

需要注意 null/not null 对索引的可能影响：

mysql>  explain SELECT * FROM order_exp WHERE order_no 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 order_exp WHERE order_no is not null;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | order_exp | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10367 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

order_no 为索引列，同时不允许为 null，

mysql> show create table order_exp \G;
*************************** 1. row ***************************
       Table: order_exp
Create Table: CREATE TABLE `order_exp` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

可以看见，order_no is null 的情况下，MySQL 直接表示 Impossible WHERE(未过滤)， 对于 is not null 直接走的全表扫描。
当order_no允许为空时：

mysql>  explain SELECT * FROM order_exp_cut WHERE order_no is null;      
+----+-------------+---------------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table         | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | ref  | idx_order_no  | idx_order_no | 153     | const |    1 |   100.00 | Using index condition |
+----+-------------+---------------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM order_exp_cut WHERE order_no is not null;  
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | ALL  | idx_order_no  | NULL | NULL    | NULL |   30 |   100.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

is null 会走 ref 类型的索引访问，is not null;依然是全表扫描。所以总结起来:
is not null 容易导致索引失效，is null 则会区分被检索的列是否为 null，如果是 null 则会走 ref 类型的索引访问，如果不为 null，也是全表扫描。

但是当联合索引上使用时覆盖索引时，情况又不同了:

mysql> explain SELECT order_status,expire_time FROM order_exp WHERE insert_time is null;
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | ref  | u_idx_day_status | u_idx_day_status | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT order_status,expire_time FROM order_exp WHERE insert_time is not null;
+----+-------------+-----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key              | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | index | NULL          | u_idx_day_status | 12      | NULL | 10367 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time is null;
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table         | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | ref  | u_idx_day_status | u_idx_day_status | 6       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time is not null;
+----+-------------+---------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table         | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | index | u_idx_day_status | u_idx_day_status | 13      | NULL |   30 |   100.00 | Using where; Using index |
+----+-------------+---------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

所以总的来说，在设计表时列尽可能的不要声明为 null ;

4.2.8.8. Like 查询要当心

like 以通配符开头(’%abc…’)，mysql 索引失效会变成全表扫描的操作

mysql> explain SELECT * FROM order_exp WHERE order_no like '%_6S';
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10367 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

原因前面已经讲过了。此时如果使用覆盖索引可以改善这个问题

mysql> explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time like '%18:35:09';       
+----+-------------+---------------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table         | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | index | NULL          | u_idx_day_status | 13      | NULL |   30 |    11.11 | Using where; Using index |
+----+-------------+---------------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

4.2.8.9. 字符类型加引号

字符串不加单引号索引失效

mysql> explain SELECT * FROM order_exp WHERE order_no = 6;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | ALL  | idx_order_no  | NULL | NULL    | NULL | 10367 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> explain SELECT * FROM order_exp WHERE order_no = '6';
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order_exp | NULL       | ref  | idx_order_no  | idx_order_no | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL 的查询优化器，会自动的进行类型转换，比如上个语句里会尝试将 order_no 转换为数字后和 6 进行比较，自然造成索引失效。

4.2.8.10. 使用 or 关键字时要注意

mysql> explain SELECT * FROM order_exp WHERE order_no = 'DD00_6S' OR order_no = 'DD00_9S';
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | idx_order_no  | idx_order_no | 152     | NULL |   36 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' OR order_note = 'abc';
+----+-------------+-----------+------------+------+-----------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys   | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | ALL  | idx_expire_time | NULL | NULL    | NULL | 10367 |    10.01 | Using where |
+----+-------------+-----------+------------+------+-----------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

表现是不一样的，具体原因在“深入思考索引在查询中的使用”中 已经说明过原因了。

当然如果两个条件都是索引列，情况会有变化:

mysql> explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' OR order_no = 'DD00_6S';
+----+-------------+-----------+------------+-------------+------------------------------+------------------------------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table     | partitions | type        | possible_keys                | key                          | key_len | ref  | rows | filtered | Extra                                                  |
+----+-------------+-----------+------------+-------------+------------------------------+------------------------------+---------+------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | order_exp | NULL       | index_merge | idx_order_no,idx_expire_time | idx_expire_time,idx_order_no | 5,152   | NULL |   25 |   100.00 | Using union(idx_expire_time,idx_order_no); Using where |
+----+-------------+-----------+------------+-------------+------------------------------+------------------------------+---------+------+------+----------+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

（这里我们看到了索引合并。将在后面讲到）
这也给了我们提示，如果我们将

SELECT*FROMorder_expWHERE expire_time= '2021-03-22 18:35:09' OR order_note = 'abc';

改为:

mysql> explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' union all SELECT * FROM order_exp WHERE order_note = 'abc';
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys   | key             | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+-------+----------+-------------+
|  1 | PRIMARY     | order_exp | NULL       | ref  | idx_expire_time | idx_expire_time | 5       | const |     6 |   100.00 | NULL        |
|  2 | UNION       | order_exp | NULL       | ALL  | NULL            | NULL            | NULL    | NULL  | 10367 |    10.00 | Using where |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

可以改善语句的表现。
使用覆盖扫描也可以改善这个问题:

mysql> explain SELECT order_status,id FROM order_exp_cut WHERE insert_time='2021-03-22 18:34:55' or expire_time='2021-03-22 18:28:28';
+----+-------------+---------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table         | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | index | u_idx_day_status | u_idx_day_status | 13      | NULL |   30 |    17.50 | Using where; Using index |
+----+-------------+---------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

4.2.8.11. 使用索引扫描来做排序和分组

MySQL 有两种方式可以生成有序的结果;通过排序操作;或者按索引顺序扫描;如果 EXPLAIN 出来的 type 列的值为“index”，则说明 MySQL 使用了索引扫描来做排序(不要和 Extra 列的“Using index”搞混淆了)。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条 记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机 I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在 IO 密集型的工作负载时。
MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。
只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向(倒序或正序)都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 0RDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。

4.1.8.12. 排序要当心

ASC、DESC 别混用对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是 ASC 规则排序，要么都是 DESC 规则排序。联合索引为何是这样，前面已经说过很多次了。
排序列包含非同一个索引的列
用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序

mysql> explain SELECT * FROM order_exp order by order_no,insert_time;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | order_exp | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10367 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select insert_time,order_status from order_exp order by insert_time,order_status;  
+----+-------------+-----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key              | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | index | NULL          | u_idx_day_status | 12      | NULL | 10367 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4.2.8.13. 尽可能按主键顺序插入行

最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于 I/O 密集型的应用。例如，从性能的角度考虑，使用 UUID 来作为聚簇索引则会很糟糕，它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
最简单的方法是使用 AUTO_INCREMENT 自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
注意到向 UUID 主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长;另一方面毫无疑问是由于页分裂和碎片导致的。
因为主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB 默认的最大填充因子是页大小的 15/16， 留出部分空间用于以后修改)，下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满,这也正是所期望的结果。
如果新行的主键值不一定比之前插入的大，所以 InnoDB 无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置-—通常是已有数据的中间位置——并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点:
写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机 IO。
因为写入是乱序的，InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
所以使用 InnoDB 时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

4.1.8.14. 优化 Count 查询

首先要注意，COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某个列值的数量，也可以统计行数。
在统计列值时要求列值是非空的(不统计 NULL)。
COUNT()的另一个作用是统计结果集的行数。常用的就是就是当我们使用 COUNT(*)。实际上，它会忽略所有的列而直接统计所有的行数。
通常来说，COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果，因此是很难优化的。在 MySQL 层面能做的基本只有索引覆盖扫描了。如果这还不够,就需要考虑修改应用的架构，可以用估算值取代精确值， 可以增加汇总表，或者增加类似 Redis 这样的外部缓存系统。

关于 Null 的特别说明
对于 Null 到底算什么，存在着分歧:

• 1、有的认为 NULL 值代表一个未确定的值，MySQL 认为任何和 NULL 值做比较的表达式的值都为 NULL，包括 select null=null 和 select null!=null;

mysql> select null = null;
+-------------+
| null = null |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select null != null;
+--------------+
| null != null |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

所以每一个 NULL 值都是独一无二的。

• 2、有的认为其实 NULL 值在业务上就是代表没有，所有的 NULL 值合起来算一份;
• 3、有的认为这 NULL 完全没有意义，所以在统计数量时压根儿不能把它们算进来。
  假设一个表中某个列 c1 的记录为(2,1000,null,null)，在第一种情况下，表中 c1 的记录数为 4，第二种表中 c1 的记录数为 3，第三种表中 c1 的记录数为 2。

MySQL 会统计索引中不重复值的数量，专门用于语句的执行计划分析。在对统计索引列不重复值的数量时如何对待 NULL 值，MySQL 专门 供 了一个 innodb_stats_method 的系统变量，
这个系统变量有三个候选值:

1. nulls_equal:认为所有 NULL 值都是相等的。这个值也是 innodb_stats_method 的默认值。
   如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个 列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
2. nulls_unequal:认为所有 NULL 值都是不相等的。
   如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个 列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
3. nulls_ignored:直接把 NULL 值忽略掉。
   而且有迹象表明，在 MySQL5.7.22 以后的版本，对这个 innodb_stats_method 的修改不起作用，MySQL 把这个值在代码里写死为 nulls_equal。也就是说 MySQL 在进行索引列的数据统计行为又把 null 视为第二种情况(NULL 值在业务上就是 代表没有，所有的 NULL 值和起来算一份)，看起来，MySQL 中对 Null 值的处理 也很分裂。所以总的来说，对于列的声明尽可能的不要允许为 null。

4.1.8.15. 优化 limit 分页

在系统中需要进行分页操作的时候，我们通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的 ORDER BY 子句。
一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是

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

这样的查询，这时 MySQL 需要查询 10010 条记录然后只返回最后 10 条，前面 10000 条记录都将被抛弃，这样的代价非常高。
优化此类分页查询的一个最简单的办法是

mysql> explain SELECT * FROM (select id from order_exp limit 10000,10) b,order_exp a where a.id = b.id;
+----+-------------+------------+------------+--------+---------------+-----------------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key             | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+-----------------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL            | NULL    | NULL | 10010 |   100.00 | NULL        |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY       | PRIMARY         | 8       | b.id |     1 |   100.00 | NULL        |
|  2 | DERIVED     | order_exp  | NULL       | index  | NULL          | idx_expire_time | 5       | NULL | 10367 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------------+---------+------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

会先查询翻页中需要的 N 条数据的主键值，然后根据主键值回表查询所需 要的 N 条数据，在此过程中查询 N 条数据的主键 id 在索引中完成，所以效率会高一些。
从执行计划中可以看出，首先执行子查询中的 order_exp 表，根据主键做索引全表扫描，然后与 a 表通过 id 做主键关联查询，相比传统写法中的全表扫描效率会高一些。
从两种写法上能看出性能有一定的差距，虽然并不明显，但是随着数据量的增大，两者执行的效率便会体现出来。
上面的写法虽然可以达到一定程度的优化，但还是存在性能问题。最佳的方式是在业务上进行配合修改为以下语句:

mysql> explain select * from order_exp where id > 67 order by id limit 10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | order_exp | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 5183 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

采用这种写法，需要前端通过点击 More 来获得更多数据，而不是纯粹的翻页，因此，每次查询只需要使用上次查询出的数据中的 id 来获取接下来的数据即可，但这种写法需要业务配合。

4.1.8.16 记忆总结

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