优化器生成最优执行计划需要考虑的因素

MySQL有一个优化器，专门负责生成最优的查询计划，生成最优查询计划可能考虑的因素有：

• 扫描行数
• 是否排序
• 是否需要回表
• 是否需要临时表 等等

在不同的因素作用下，生成的查询计划可能和我们预想的不同。

具体实例

实验前
先准备好表

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB；

使用存储过程插入10万条数据

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

1. 范围查询某普通索引字段，引擎选择了全表扫描，没有使用索引

调用下列语句：

explain select * from t  where a between 20000 and 40000;

执行计划查询结果如下：

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | a             | NULL | NULL    | NULL | 100448 |    37.37 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

发现mysql使用了全表扫描，没有使用a列上的普通索引。

原因如下：
如果使用普通索引查询，还需要回表操作。当回表次数占总数据行数达到一定比例时，做随机IO查询的效率较低，并且当磁盘是机械硬盘时，多次随机IO查询一定比顺序查询的全表扫描要慢的多。
但是如果使用的是固态硬盘，随机读操作的性能很高，可以强制或者引导MySQL优化器使用普通索引来查询。

2. 查询语句中含有order by 可能会促使mysql选择排序字段对应的索引

调用下列语句：

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

执行计划查询结果如下：

| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | a,b           | b    | 5       | NULL | 50224 |     1.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

发现MySQL选择了b列上的索引。

我们分析一下，如果使用a列上的索引，搜索的行数为1000行，回表次数为1000次，因为使用a列索引，所以排序b列时，会在排序消耗一些时间；如果使用b列的索引，搜索行数变多，回表也变多，但是不需要排序。显然，mysql在这里更加注重了排序的影响，所以选择了b列的索引。

我们执行一下强制使用a列索引的相同sql语句，和不使用force index的sql语句都执行三次，对比一下执行时间，可以查看到如果使用a索引，平均执行时间要比使用b列索引快的多。

mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.05 sec)

mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.04 sec)

mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.05 sec)

mysql> select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.01 sec)

mysql> select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.00 sec)

mysql> select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.00 sec)

总结

相同的sql语句，在不同的计算机下，执行时间也会变得不同，上面的实验结果只代表在作者的计算机上运行得到的结果，在进行sql调优时，要根据当时环境的实际执行时间进行调试，然后决定是否应该强制使用索引。

---

注：这一节中，最后会写一些关于字符串加索引的思考，因为这部分知识不足以构成一个小节，所以把它添加到了这里。

字符串加索引的方式

给字符串字段加索引有几种方式：

1. 直接创建字符串字段的完整索引，支持范围和等值查询
2. 创建前缀索引，可能会增加扫描行数，会导致覆盖索引失效
3. 如果前缀区分度不高，可以使用倒序存储，再根据倒叙存储的字段创建前缀索引
4. 通过加入一个新的字段，这个字段的值为hash计算过的字段值，有额外的计算和存储消耗

第234种方式，考虑的更多的是节省存储空间，但是都增加了维护的成本。比如：

• 第三种方式，存储的时候就需要业务或者sql保证倒叙存储，查询的时候也需要相应的利用业务或者sql倒叙函数查询，如果一旦在业务或者sql语句上忘记使用倒叙，那么在实际存储的时候也不会报错，但是会影响业务。
• 第四种方式，除了有索引的消耗以外，还多了一个存储字段，如果有多个字符串字段需要设计搜因，那么需要增加N个hash索引字段。另外，存储和查询的时候，也多了hash计算的消耗。

在目前硬件越来越便宜的趋势下，直接使用1或者2方式创建完整或者前缀索引是完全可以的，2虽然会增加扫描行数和回表成本，但在目前的硬件下这些损耗几乎是可以忽略不计。