likes
comments
collection
share

技巧解锁：应对优化器选错索引的智慧之道

作者站长头像
站长
· 阅读数 47

本文为《MySQL归纳学习》专栏的第十一篇文章，同时也是关于《MySQL索引》知识点的第三篇文章。

相关文章：

MySQL索引大揭秘：优化查询的秘籍（上）

MySQL索引大揭秘：优化查询的秘籍（下）

在数据库优化的道路上，索引统计的更新机制和优化器选错索引是不可忽视的关键问题。本文将深入探讨这两个议题，揭示它们背后的奥秘。首先，详解索引统计的作用和更新机制，为你解决查询性能问题提供关键线索。其次，我们将剖析优化器选错索引的潜在风险，并阐述导致误导的原因。更重要的是，文章将为你呈现应对选错索引的实用方案，助你优化数据库查询，提升应用性能。

首先来看一下这张思维导图，对本文内容有个直观的认识。

技巧解锁：应对优化器选错索引的智慧之道

接下来进入正文。

首先我们基于如下表结构进行讲解：

CREATE TABLE tt (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) DEFAULT NULL,
  b int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY a (a),
  KEY b (b)
) ENGINE=InnoDB;

接下来我们使用存储过程来新增数据，如下所示：

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
START TRANSACTION;
  while(i<=10000)do
    insert into tt(a,b) values(i, i);
    set i=i+1;
  end while;
commit;
end;;
delimiter ;
call idata();

我们分析下面这条 SQL 语句：

explain SELECT * from tt where a BETWEEN 1000 and 1500;

因为字段 a 有索引，所以肯定是用索引 a；

我们对上述案例做出修改，

start transaction with consistent snapshot;
commit;

delete from tt;
call idata();

explain SELECT * from tt where a BETWEEN 1000 and 1500;

技巧解锁：应对优化器选错索引的智慧之道

session A 就是开启了一个事务。随后，session B 把数据都删除后，又调用了 idata 这个存储过程，插入了 1 万行数据。 由于 MVCC 机制，之前的 1万行数据并没有删除，只是对 B 来说不可见了。通过自增 Id 可以看到数据表中仍然是之前的旧数据。

这里解释一下为什么说由于 MVCC 机制下，之前的 1万行数据并没有删除，只是对 B 来说不可见了？

session A 创建了一致性视图，在 commit 之前，所有的更新都是记录在 undo log 中，delete 语句没有将其删除。 该数据页(二级索引和聚簇索引)上的记录不会被物理删除，是被标记删除。只有当该事务（session A）不会用于 mvcc 了，才可以被 merge 线程把之前标记删除的数据真正删除掉。

但是即便数据物理删除了，磁盘空间也不会返回给操作系统。可以通过 show table status like 't';观察 data_free 来预估该表的碎片。如果过大，可以用 alter table t engine=innodb 来清除。

延伸问题：在 MySQL 中使用 delete 语句删除数据之后，监控视图中可用的磁盘空间没有增加，磁盘使用率没有下降等等。

当 session A 执行 commit 之后，数据表中则更新为新的数据。

下面的三条 SQL 语句，就是这个实验过程。

set long_query_time=0;
explain SELECT * from tt where a between 5000 and 10000; /*Q1*/
explain select * from tt force index(a) where a between 5000 and 10000;/*Q2*/
  • 第一句，是将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；
  • 第二句，Q1 是 session B 原来的查询；
  • 第三句，Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比，使用 force index(a) 来让优化器强制使用索引 a 。

Q1 语句执行结果如下：

技巧解锁：应对优化器选错索引的智慧之道

Q2 执行结果如下：

技巧解锁：应对优化器选错索引的智慧之道

从上述结果可知，MySQL 在没有使用 force index 的情况下，用错了索引，进行的是全表扫描。

优化器的逻辑

最开始的文章提到过，选择索引是优化器的工作。

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行

扫描行数是怎么判断的？

扫描行数是根据索引的区分度来判断的。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

可以使用 show index 方法，看到一个索引的基数。

技巧解锁：应对优化器选错索引的智慧之道

MySQL 通过采样统计来得到索引的基数。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

假设 N=3，另外有 10个索引数据页； page1：10个不同值； page2：20个不同值； page3：15 个不同值； 索引基数=(10+20+15)/3*10=150； 即 N 个数据页的平均不同值乘以总的页面数得到索引基数，其实就是一种随机取样统计，因为选择全部数据页取平均值代价太高不可取，被取得数据页就是样本。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

  • 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
  • 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

当我们在执行 Q1 语句时进行了全表扫描，从结果上来看，优化器认为直接扫描主键索引更快，避免回表查询，但是从执行时间上并不是最优的。

这里总结一下上述基数出错的背后原理：session A 开启的事务是 RR 级别，会创建一个一致性读视图； session B 的删除会产生记录的新版本（空记录），同时会产生新的 Undo Log； 一致性读视图需要的 Undo Log 是不会删除的，意味着 session B 产生的删除记录的 Undo Log 是不能删除的，意味着记录的当前版本（空记录）不能真的从磁盘上被删掉，因为如果记录都从磁盘上删掉了，那么空有 Undo Log 也无法回到 session A 应该看到的那个版本；就好比链表总得有个头结点，如果失去了对头结点的引用，就失去了访问整个链表的起点； 不删除的情况下，记录还在数据页上占着空间，session B 又把数据加回来，索引 a 上的数据其实就有两份，导致索引的数据页出现大量的分裂； 大量的页分裂，导致了 cardinality 的不准；

题外话：索引 a 上的数据有两份，那么主键上的数据也不能删，那没有使用 force index 的语句，使用 explain 命令看到的扫描行数为什么没有翻倍呢？原因在于全表扫描时依靠主键，而主键是按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。

所以归根到底还是因为得到错误的扫描行数。既然统计信息不对，那么可以通过执行 analyze table tt 命令，重新统计索引信息。

前面我们提到过，优化器可不止看扫描行数。

查看如下 SQL 语句：

explain select * from tt where (a between 1 and 1000) and (b between 7000 and 9000) order by b limit 1;

技巧解锁：应对优化器选错索引的智慧之道

从结果上可以看到，优化器选择的是索引 b，我们知道如果选择索引 a，执行速度会快一些。

是否排序

我们构建如下表结构：

create table buy_log(
  id int  not null,
  buy date
)ENGINE=INNODB;

INSERT into buy_log values('1','2009-01-01');
INSERT into buy_log values('2','2009-01-01');
INSERT into buy_log values('3','2009-01-01');
INSERT into buy_log values('1','2009-02-01');
INSERT into buy_log values('3','2009-02-01');
INSERT into buy_log values('1','2009-03-01');
INSERT into buy_log values('1','2009-04-01');

alter table buy_log add key(id);
alter table buy_log add key(id,buy_date);

执行下述语句：

EXPLAIN select * from buy_log where id=2;

查看执行结果为：

技巧解锁：应对优化器选错索引的智慧之道

possible keys 在这里有两个索引可供使用，分别是单个的 id 索引和（id，buy_date）的联合索引。优化器选择使用普通索引 id。

当查询语句需要排序时，比如下方这条语句：

EXPLAIN select * from buy_log where id=1 ORDER BY buy_date desc limit 3;

技巧解锁：应对优化器选错索引的智慧之道

根据上图结果可知，优化器选择使用联合索引，因为在这个联合索引中 buy_date 已经排序好了。根据该联合索引取出数据，无须再对 buy_date 做一次额外的排序操作。若强制使用 userid 索引，则执行结果为：

技巧解锁：应对优化器选错索引的智慧之道

上述通过联合索引避免额外排序操作的案例，我们在之前的文章中讲过一遍，这里再次提及主要是为了说明当同时存在普通索引 id 和联合索引（id，buy_data)时，优化器会判断是否需要额外排序操作而选择正确的索引。

索引选择异常和处理

原本可以执行得很快的 SQL 语句，执行速度却比你预期的慢很多，你应该怎么办呢？

使用 force index

如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

explain select * from tt force index(a) where a between 5000 and 10000;

修改SQL，引导优化器选择我们期望的索引

explain select * from tt where (a between 1 and 1000) and (b between 7000 and 9000) order by b,a limit 1;

在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

技巧解锁：应对优化器选错索引的智慧之道

新建更合适的索引或删掉误用的索引

比如说前面 buy_log 表中创建的 id 索引和 (id,buy_date)索引，假设 id 不是主键，可以删除 id 索引，利用最左前缀原则来减少索引。

转载自:https://juejin.cn/post/7262731516849143866
评论
请登录