深入理解MySQL——MySQL选错索引怎么办

最近线上的一个张表频繁的报慢查询告警，看了下日志发现一条语句执行要几秒钟，这还是在用了LIMIT的前提下，导致相关接口超时，一个功能对数据量大的用户完全不可用。COUNT了下这张表，一共有400多万的数据，EXPLAIN了慢日志SQL，发现走的索引并不是按照预期的更快的那一个，并且由于分段查询产生了filesort。那么MySQL选错索引的原因是什么呢？这个SQL有没有优化的空间呢？优化的效果如何呢？

问题重现

为了脱敏，我们先建一张与线上数据结构相同的表t，这张表有两个联合索引，其中一个为唯一索引，另一个为普通索引。

CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `group_id` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_t_user_id_group_id_a` (`user_id`,`group_id`,`a`),
  KEY `idx_t_user_id_group_id_b` (`user_id`,`group_id`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

然后我们执行如下存储过程，这里构造了300万条数据。

create
    definer = root@`%` procedure idata()
begin
    declare user_id int;
    declare group_id int;
    declare a int;
    declare b int;
    set group_id=50;
    start transaction;
        while(group_id>0)do   # 这边调换插入顺序为了增加回聚簇索引的随机IO成本，让走错索引耗时更长，尽量模拟线上环境
            set user_id = 1;
            while(user_id<=20)do
                set a=1;
                while(a<=3000)do
                    set b = a % 30;
                    insert into t(user_id, group_id, a, b) values(user_id, group_id, a, b);
                    set a=a+1;
                end while ;
                set user_id=user_id+1;
            end while ;
            set group_id=group_id-1;
        end while ;
    commit;
end;

call idata();

然后模拟线上的查询语句，这个语句在业务中的功能大致是：找到指定用户所有分组下不同的a对应的b的最大值，同时使用了分段查询，根据id排序，每次迭代1000条到内存中筛选最大值。

root@localhost:test> select id, user_id, group_id, a, b from t where id>0 and user_id=3 and group_id in (1, 2, ..., 50) and b >= 16 order by id asc limit 1000 offset 0

1000 rows in set
Time: 7.887s

我们看到这里一条语句耗时8s左右。在查看执行计划之前，我猜测这条语句会走idx_t_user_id_group_id_b索引，因为可以利用索引下推，将b>=16这个条件判断交给引擎层，这样可以返回更少的id记录给服务层，就可以减少回聚簇索引的次数。

下面我们看下这条语句的执行计划，发现并没有按照我们的预期，而是走了唯一索引，这是为什么呢？

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t
type          | range
possible_keys | PRIMARY,uk_t_user_id_group_id_a,idx_t_user_id_group_id_b
key           | uk_t_user_id_group_id_a
key_len       | 12
ref           | <null>
rows          | 13650
Extra         | Using index condition; Using where; Using MRR; Using filesort

根据执行计划信息推断数据库查询过程

我们先不分析为什么MySQL没有选择idx_t_user_id_group_id_b索引，先来根据执行计划推断下MySQL内部是怎么执行这条SQL的

根据user_id和group_id查询条件，到引擎层根据uk_t_user_id_group_id_a索引查询满足条件的行，命中索引的前两个字段，key_len为12也对的上
引擎层每查询到一行满足条件的索引记录，就返回一条给服务层，服务层根据剩下的查询条件，也就是b>=16，做数据过滤
服务层根据过滤后记录中的主键id，到聚簇索引中查询完整数据。

注意：这里存在一个优化，根据执行计划，Extra中有个叫MRR的策略，为什么会出现这个呢？因为我们在插入数据时故意让group_id递减，这导致根据uk_t_user_id_group_id_a索引查到的id不是顺序的，这样回表就会出现随机访问，性能相对较差。

MRR全称Multi-Range Read，这个优化的目的是尽量使用顺序读盘。由于使用了MRR，所以服务层不会拿到一个id就回表查询，而是将id值放入read_rnd_buffer中，直到buffer放满然后对id进行递增排序，排序后的id数组依次到聚簇索引中查询，取出结果。

上一步取出的结果还不能直接返回给客户端，还要对记录根据id排序。排序分以下两种，显然MySQL选择了第一种
1. 全字段排序：将select的字段以及order by的字段存入sort_buffer，对sort_buffer中的数据按照id进行快速排序，如果sort_buffer无法一次性存下所有数据，则需要借助临时文件辅助归并排序，sort_buffer的大小由sort_buffer_size控制
2. rowid排序：如果一条待排序数据字段数太多，就有可能造成sort_buffer中同时能够存放的行数很少，需要临时文件就更多，排序性能会很差。所以如果单行很大（根据max_length_for_sort_data判断，默认1024字节），只会将order by字段以及主键放入sort_buffer，排序后取前1000行，然后按照id的值回表取出select的字段

MySQL优化器选择索引的原则

上面我们分析了走uk_t_user_id_group_id_a索引的查询过程，不难发现这样会导致回表的次数比较多，因为b>=16这个条件是在服务层判断的，而如果走idx_t_user_id_group_id_b索引，是可以直接在第一步就把不满足这个条件的数据过滤掉的，少了很多不必要的回表查询。那么为什么优化器放着更高效的索引不走，而选择了另外的索引呢？

优化器的逻辑

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。我们这个查询不管走哪个索引都难免要排序，所以 MySQL 选错索引肯定是在判断扫描行数的时候出问题了。那么，问题就是：扫描行数是怎么判断的？

索引的基数怎么算的

索引的基数，通俗来讲就是索引的区分度，一个索引上不同的值越多，这个索引的区分度就越好。

准确的计算公式为 COUNT(DISTINCT(field)) / COUNT(field)，当然MySQL不可能通过这种方式统计基数，因为这样要扫描所有的数据页代价太高了。所以只能选择采样统计，InnoDB默认会选择N个数据也，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而由于是采样统计，所以这个基数是很容易出现偏差的。

EXPLAIN中的扫描行数rows如何估算的

首先找到第一个记录所在的page，记为page_left，统计page_left里的记录数，记为records_page_left，然后找到最后一个记录所在的page，记为page_right，统计page_right里的记录数，记为records_page_right，除了两个边界外，还沿着page_left往右连续查找8个page，最后按照如下公式计算

rows = (records_page_left + records_page_1 + records_page_2 + ... + records_page_8 + records_page_right) / 10 * page_number

根据公式我们可以得出以下结论

如果满足条件的总的page数目<=10，那么预估的rows和真实的rows一致
索引的非顺序插入、频繁删除等操作会导致页分裂和数据不均，就会导致统计信息比实际数据少很多

选错索引了怎么办

其实大多数时候优化器都能找到正确的索引，但是偶尔还是会碰到扫描行数估算错误导致原本可以执行很快的语句，却比预想的慢得多。那此时应该怎么办呢？

由于索引统计信息不准确导致的问题，可以用analyze table来解决，注意这个命令是不需要MDL锁的
采用force index强制选择一个索引。但是很多人不喜欢用这种语法，一来这种hard code不太优美，二来如果索引改了名字或被删除了，容易忘记更改代码
新建一个更合适的索引或删除误用的索引，帮助优化器作出正确的选择

SQL优化方向

上面几节我们分析了MySQL为什么没有走我们预期的索引。那么针对文章开始提到的SQL，我们有哪些优化手段呢？

强制走预期索引：强制走idx_t_user_id_group_id_b索引，这样可以利用索引下推将b >= 16这个条件下推到引擎层来判断，减少回表的次数
只查询需要的字段：只select a, b两个字段，可以减少单行占用空间，sort_buffer_size不变的情况下单次可以排序更多的数据行，减少了文件排序的次数
降低filesort成本，或去除filesort：代码中为了防止一次查询太多数据把pod的内存打爆，所以采用了根据id排序然后分段取的机制。但是仔细分析下来，这个场景是不需要担心这个问题的，原因如下
- MySQL取数据和发数据的流程如下，所以如果客户端没有及时的读取数据，服务器是会暂停数据查询及发送的
  1. 获取一行，写到net_buffer中，这块内存的大小是由参数net_buffer_length定义的，默认是16k
  2. 重复获取行，制动net_buffer写满，调用网络接口发出去
  3. 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer
  4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈(socket send buffer)写满了，进入等待。知道网络栈重新可写，再继续发送
- 一些ORM会缓存查询的返回数据，为了更高效的进行多次迭代、index等操作，但是应该也提供了可以关闭缓存的查询函数或配置(例如笔者使用的peewee)
- 业务场景上，我们只需要计算每个a对应的最大的b值，所以只会遍历所有数据，用一个字典存储a和b，不会占用太多pod内存

peewee相关采坑

如果你刚好用的python+peewee技术栈，那么这节有我实践的一些采坑记录，可以供你参考。

如何指定索引：peewee不支持USE INDEX语法，只能利用model.alias + from_

alias_name = "tbl"
alias_model = Model.alias(alias_name)
table_name = Model._meta.table_name
index_name = "xxx"
from_source = "%s AS %s USE INDEX (%s)" % (table_name, alias_name, index_name)
for row in alias_model.select(xxx).from_(peewee.SQL(from_source)).where(xxx).iterator():
    pass

MySQLdb或pymysql这些driver是不缓存数据行，但是peewee默认会缓存读取的数据：如果用for row in query这种遍历方法，会缓存每行数据，如果确认只会遍历一遍查询，可以使用for row in query.iterator()关闭缓存
使用model_alias查询，导致构造Model对象耗时太久：Model.select会使用ModelObjectCursorWrapper将返回的每行数据构造成model对象，默认情况下是不会在构造时调用get_default_dict方法的，但是由于使用了alias导致在__init__时调用了该方法（具体原因可以看源码），如果字段的default属性有耗时的操作（例如globalid生成），那么会有大量的时间浪费对象构造上。

实际效果

这里我们直接用python代码实现上述需求，找到指定用户所有分组下不同的a对应的b的最大值

基本模型

class T(peewee.Model):
    user_id = peewee.BigIntegerField()
    group_id = peewee.IntegerField()
    a = peewee.IntegerField()
    b = peewee.IntegerField()

    class Meta:
        database = db

优化前

def iter_model(model, conditions):
    start_id = 0
    while True:
        query = (
            model.select()
            .where(model.id > start_id, *conditions)
            .order_by(model.id.asc())
            .limit(1000)
        )
        if not list(query):
            break
        for v in query:
            if v.id > start_id:
                start_id = v.id
            yield v


def get_mapping():
    mapping = defaultdict(int)
    for row in iter_model(
        T, [T.user_id == 3, T.group_id.in_([i for i in range(1, 51)]), T.b >= 16]
    ):
        if row.b >= mapping[row.a]:
            mapping[row.a] = row.b
    return mapping


if __name__ == "__main__":
    start = time.time()
    rv = get_mapping_perf()
    print("cost time: %s" % (time.time() - start))

为避免buffer_pool等缓存影响结果，执行前重启了server。执行三次，结果分别为 11.72s, 10.11s, 10.16s。

优化后

def get_mapping_perf():
    mapping = defaultdict(int)
    alias_name = "tbl"
    alias_model = T.alias(alias_name)
    table_name = T._meta.table_name
    index_name = "idx_t_user_id_group_id_b"
    from_source = "%s AS %s USE INDEX (%s)" % (table_name, alias_name, index_name)
    for row in (
        alias_model.select(alias_model.a, alias_model.b)
        .from_(peewee.SQL(from_source))
        .where(
            alias_model.user_id == 3,
            alias_model.group_id.in_([i for i in range(1, 51)]),
            alias_model.b >= 16,
        )
        .tuples()
        .iterator()
    ):
        a, b = row[0], row[1]
        if b >= mapping[a]:
            mapping[a] = b
    return mapping

执行三次，结果分别为 1.70s, 1.44s, 1.45s。