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调优时,要根据当时环境的实际执行时间进行调试,然后决定是否应该强制使用索引。


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

字符串加索引的方式

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

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

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

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

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

posted @ 2020-08-10 09:27  Ging  阅读(152)  评论(0编辑  收藏  举报