乐趣区

关于mysql:为什么我建议在复杂但是性能关键的表上所有查询都加上-force-index

jiezi

最近，又遇到了慢 SQL，简略的看了下，又是因为 MySQL 自身优化器还有查问打算预计不准的问题。SQL 如下：

select * from t_pay_record
WHERE
((
    user_id = 'user_id1' 
    AND is_del = 0 
)) 
ORDER BY
    id DESC 
    LIMIT 20

这个 SQL 执行了 20 分钟才有后果。然而咱们 换一个 user_id，执行就很快 。 从线上业务体现来看，大部分用户的体现都失常 。 咱们又用一个数据分布与这个用户类似的用户去查，还是比拟快。

咱们先来 EXPLAIN 下这个原始 SQL，后果是：

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | PRIMARY | 8       | NULL | 22593 |     0.01 | Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------+---------+------+-------+----------+-------------+

而后咱们换一些散布差不多的用户然而响应工夫失常的用户，EXPLAIN 后果有的是：

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key                                                     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id_trade_code_status_amount_create_time_is_del | 195     | NULL | 107561|     10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------+-------+----------+-------------+

有的是：

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key         | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id | 195     | NULL |  87514|     10.00| Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+

其实依据这个体现就能够推断出，是走错索引了。为啥会用错索引呢？这个是因为多方面的起因导致的，本篇文章将针对这个 SQL 来剖析下这个多方面的起因，并给出最初的解决办法。

对于 MySQL 慢 SQL 的剖析

在之前的文章，我提到过 SQL 调优个别通过上面三个工具：

  1. EXPLAIN：这个是比拟通俗的剖析，并不会真正执行 SQL，剖析进去的可能不够精确具体。然而能发现一些关键问题。
  2. PROFILING：通过 set profiling = 1 开启的 SQL 执行采样。能够剖析 SQL 执行分为哪些阶段，并且每阶段的耗时如何。须要执行并且执行胜利 SQL，并且剖析进去的阶段不够具体，个别只能通过某些阶段是否存在如何防止这些阶段的呈现进行优化（例如防止内存排序的呈现等等）。
  3. OPTIMIZER TRACE：具体展现优化器的每一步，须要执行并且执行胜利 SQL。MySQL 的优化器因为思考的因素太多，迭代太多，配置相当简单，默认的配置在大部分状况没问题，然而在某些非凡状况会有问题，须要咱们进行人为干涉。

这里再说一下在不同的 MySQL 版本，EXPLAIN 和 OPTIMIZER TRACE 后果可能不同，这是 MySQL 自身设计上的有余导致的，EXPLAIN 更贴近最初的执行后果，OPTIMIZER TRACE 相当于在每一步埋点采集，在 MySQL 一直迭代开发的时候，难免会有疏漏

对于下面这个 SQL，咱们其实 EXPLAIN 就能晓得它的起因是走错索引了。然而不能直观的看进去为啥会走错索引，须要通过 OPTIMIZER TRACE 进行进一步定位。然而在进一步定位之前，我想先说一下 MySQL 的 InnoDB 查问优化器数据配置。

MySQL InnoDB 查问优化器数据配置（MySQL InnoDB Optimizer Statistics）

官网文档地址：https://dev.mysql.com/doc/ref…

为了优化用户的 SQL 查问，MySQL 会对所有 SQL 查问进行 SQL 解析、改写和查问打算优化。针对 InnoDB 引擎，制订查问打算的时候要剖析：

  1. 全表扫描耗费是多大
  2. 走索引能够走哪些索引？会思考 where 条件，以及 order 条件，通过外面的条件找有这些条件的索引
  3. 每个索引的查问耗费是多大
  4. 选出耗费最小的那个查问打算并执行

每个索引查问耗费，须要通过 InnoDB 查问优化器数据。这个数据是通过采集表以及索引数据得出的，并且并不是全量采集，而是抽样采集。与以下配置相干：

  1. innodb_stats_persistent 全局变量管制全局默认的数据是否长久化，默认为 ON 即长久化，咱们个别不会能承受在内存中保留，这样万一数据库重启，表就要从新剖析，这样减慢启动工夫。管制单个表的配置是 STATS_PERSISTENT（在 CREATE TABLE 以及 ALTER TABLE 中应用）。
  2. innodb_stats_auto_recalc 全局变量全局默认是否自动更新，默认为 ON 即在表中有 10% 以上的行更新后触发后盾异步更新采集数据，。管制单个表的配置是 STATS_AUTO_RECALC（在 CREATE TABLE 以及 ALTER TABLE 中应用）。
  3. innodb_stats_persistent_sample_pages 全局变量管制全局默认的采集页的数量，默认为 20. 即每次更新，随机采集表以及表中的每个索引的 20 页数据，用于 估算每个索引的查问耗费是多大以及全表扫描耗费是多大，管制单个表的配置是 STATS_SAMPLE_PAGES（在 CREATE TABLE 以及 ALTER TABLE 中应用）。

执行工夫最慢的 SQL 起因定位

通过之前的 EXPLAIN 的后果，咱们晓得最初的查问用的索引是 PRIMARY 主键索引，这样的话整个 SQL 的执行过程就是：通过主键倒序遍历表中的每一条数据，直到筛选出 20 条。通过执行耗时咱们晓得，这个遍历了很多数据才凑满 20 条，效率极其低下。为啥会这样呢？

通过 SQL 语句咱们晓得，在后面提到的第二步中，思考的索引包含 where 条件中的 user_id，is_del 相干的索引（通过 EXPLAIN 咱们晓得有这些索引：idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del），以及 order by 条件中的 id 索引，也就是主键索引。假如本次随机采集中采集的页数据是这个样子的：

图中蓝色的代表抽样到的页，同一个表内每个索引都会抽样默认 20 页。假如本次采集的后果就是图中所示，其余索引采集的比拟平衡，通过其余索引判断用户都要扫描几万行的后果。然而主键采集的最初一页，正好开端全是这个用户的记录。因为语句最初有 limit 20，如果开端正好有 20 条记录（并且都合乎 where 条件），那么就会认为依照主键倒着找 20 条记录就能够了。这样就会造成优化器认为走主键扫描耗费起码。然而实际上并不是这样，因为这是采样的，没准前面有很多很多不是这个用户的记录，对大表尤其如此。

如果咱们把 limit 去掉，EXPLAIN 就会发现索引走对了，因为不限度 limit，主键索引就要全副扫描一遍，耗费怎么也不可能比 user_id 相干的索引低了。

执行工夫失常的 SQL 为啥 user_id 不同也会走剖析出奔不同索引的起因

同样的，因为所有索引的优化器数据是随机采样的，随着表的一直变大以及索引的一直收缩，还有就是可能加更简单的索引，这样会加剧应用不同参数剖析索引耗费的差异性（这里就是应用不同的 user_id）。

这也引出了一个新的可能大家也会遇到的问题，我在原有索引的根底上，加了一个复合索引（举个例子就是原来只有 idx_user_id，起初加了 idx_user_status_pay），那么原来的只依照 user_id 去查数据的 SQL，有的可能会应用
idx_user_id，有的可能会应用 idx_user_status_pay，应用 idx_user_status_pay 大概率比应用 idx_user_id，慢。所以， 增加新的复合索引，可能会导致原来的不是这个复合索引要优化的 SQL 的其余业务 SQL 变慢，所以须要谨慎增加

这种设计，在数据量一直增大表越变越简单的时候，会带来哪些问题

  1. 因为统计数据不是实时更新，而是更新的行数超过肯定比例才会开始更新。并且统计数据不是全量统计，是抽样统计。所以在表的数据量很大的时候，这个统计数据很难十分精确。
  2. 因为统计数据原本就不够精确，表设计如果也比较复杂，存储的数据类型比拟多，字段也很多，并且最要害的是有各种复合索引，索引也越来越简单，这样更加加剧了这个统计数据的不准确性。
  3. 顺便说一下：MySQL 表数据量不能很大，须要做好程度拆分，同时字段不能太多，所以须要做好垂直拆分。并且索引不能轻易加，想加多少加多少，也有以上说的这两个起因，这样会加剧统计数据的不准确性，导致用错索引。
  4. 手动 Analyze Table，会在表上加读锁，会阻塞表上的更新以及事务。所以不能在这种在线业务要害表下面应用。能够思考在业务低峰的时候，定时 Analyze 业务要害 Table
  5. 依附表自身主动刷新数据机制，参数比拟难以调整（次要是 STATS_SAMPLE_PAGES 这个参数，STATS_PERSISTENT 咱们个别不会改，咱们不会能承受在内存中保留，这样万一数据库重启，表就要从新剖析，这样减慢启动工夫，STATS_AUTO_RECALC 咱们也不会敞开，这样会导致优化器剖析的越来越不精确），很难预测出到底调整到什么数值最合适。并且业务的增长，用户的行为导致的数据的歪斜，也是很难预测的。通过 Alter Table 批改某个表的 STATS_SAMPLE_PAGES 的时候，会导致和 Analyze 这个 Table 一样的成果，会在表上加读锁，会阻塞表上的更新以及事务。所以不能在这种在线业务要害表下面应用。所以最好一开始就能预计出大表的量级，然而这个很难。

论断和倡议

综上所述，我倡议线上对于数据量比拟大的表，最好能提前通过分库分表管制每个表的数据量，然而业务增长与产品需要都是一直在迭代并且变简单的。很难保障不会呈现大并且索引比较复杂的表。这种状况下须要咱们，在适当调高 STATS_SAMPLE_PAGES 的前提下，对于一些用户触发的要害查问 SQL，应用 force index 疏导它走正确的索引，这样就不会呈现本文中说的因为 MySQL 优化器表采集数据的不精确导致的某些用户 id 查问走错索引的状况。

微信搜寻“我的编程喵”关注公众号，加作者微信，每日一刷，轻松晋升技术，斩获各种 offer：

我会常常发一些很好的各种框架的官网社区的新闻视频材料并加上集体翻译字幕到如下地址（也包含下面的公众号），欢送关注：

  • 知乎：https://www.zhihu.com/people/…
  • B 站：https://space.bilibili.com/31…
退出移动版