MySQL-SQL调优-引擎选错索引或者不使用索引分析 和 字符串加索引的方式思考
优化器生成最优执行计划需要考虑的因素
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调优时，要根据当时环境的实际执行时间进行调试，然后决定是否应该强制使用索引。
注：这一节中，最后会写一些关于字符串加索引的思考，因为这部分知识不足以构成一个小节，所以把它添加到了这里。
字符串加索引的方式
给字符串字段加索引有几种方式：
- 直接创建字符串字段的完整索引，支持范围和等值查询
- 创建前缀索引，可能会增加扫描行数，会导致覆盖索引失效
- 如果前缀区分度不高，可以使用倒序存储，再根据倒叙存储的字段创建前缀索引
- 通过加入一个新的字段，这个字段的值为hash计算过的字段值，有额外的计算和存储消耗
第234种方式，考虑的更多的是节省存储空间，但是都增加了维护的成本。比如：
- 第三种方式，存储的时候就需要业务或者sql保证倒叙存储，查询的时候也需要相应的利用业务或者sql倒叙函数查询，如果一旦在业务或者sql语句上忘记使用倒叙，那么在实际存储的时候也不会报错，但是会影响业务。
- 第四种方式，除了有索引的消耗以外，还多了一个存储字段，如果有多个字符串字段需要设计搜因，那么需要增加N个hash索引字段。另外，存储和查询的时候，也多了hash计算的消耗。
在目前硬件越来越便宜的趋势下，直接使用1或者2方式创建完整或者前缀索引是完全可以的，2虽然会增加扫描行数和回表成本，但在目前的硬件下这些损耗几乎是可以忽略不计。
