关于数据库:慢SQL原因分析之索引失效-京东物流技术团队

最近收到一个慢 sql 工单, 慢 sql 大略是这样：“select xxx from tabel where type = 1”。

咦，type 字段明明有索引啊，为啥是慢 sql 呢？

通过执行 explain，发现实际上数据库执行了全表扫描，从而被零碎断定为慢 sql。这时有肯定开发教训的共事会说：“字段区分度不够，这种字段作独自索引是没有意义的”。那么为什么会产生索引生效这种状况呢？索引生效都有哪些状况呢？

上面总结了若干索引生效的状况：

如果表中有个组合索引，idx\_start\_org\_code\_start\_province\_id\_trans\_type, 它的索引程序如下：

start\_org\_code,

start\_province\_id,

trans_type

当咱们从第二个索引字段开始查问时就不会走索引：

因为索引是 BTree 构造的，不能跳过第一个索引间接走第二个索引

当咱们用主键做条件时，走索引了：

而当 id 列下面有计算，比方：

能够看到走了全表扫描

有时候咱们在某条 sql 语句的查问条件中，须要应用函数，比方：截取某个字段的长度：

你有没有发现，在应用该函数之后，该 sql 语句居然走了全表扫描，索引生效了

在 sql 语句中因为字段类型不同，而导致索引生效的问题，很容易遇到，可能是咱们日常工作中最容易疏忽的问题。

到底怎么回事呢？

咱们看下表里的 start\_org\_code 字段，它是 varchar 字符类型的

在 sql 语句查问数据时，查问条件咱们能够写成这样：

从上图中看到，该字段走了索引

但如果在写 sql 时，不小心把引号丢了：

咦，该 sql 语句竟然变成全表扫描了，为什么索引生效了？

答：因为这个索引列是 varchar 类型，而传参的类型是 int，mysql 在比拟两种不同类型的字段时会尝试把这两个转化为同一种类型，再进行比拟。这样就能够了解为在字段上加了函数，依据下面剖析，索引列加了函数会索引生效。

比拟有意思的是，如果 int 类型的 id 字段，在查问时加了引号条件，却还能够走索引：

从图中看出该 sql 语句的确走了索引。int 类型的参数，不论在查问时加没加引号，都能走索引。

这还迷信吗？有没有王法了？

答：MySQL 发现如果是 int 类型字段作为查问条件时，它会主动将该字段的传参进行隐式转换，把字符串转换成 int 类型。

MySQL 会把下面列子中的字符串 12348，转换成数字 12348，所以依然能走索引。

事实上，索引列上对字段做任何操作都会导致索引生效，因为 mysq 认为任何计算或者函数都会扭转索引的实际效果，如果持续应用索引可能会造成后果不精确。

这个置信有点编程教训的同学都晓得, 这里就不举例说明了。然而为什么索引会生效呢？

答：其实很好了解，索引就像字典中的目录。个别目录是按字母或者拼音从小到大，从左到右排序，是有程序的。

咱们在查目录时，通常会先从右边第一个字母进行匹对，如果雷同，再匹对右边第二个字母，如果再雷同匹对其余的字母，以此类推。

通过这种形式咱们能疾速锁定一个具体的目录，或者放大目录的范畴。

但如果你硬要跟目录的设计反着来，先从字典目录左边匹配第一个字母，这画面你能够自行脑补一下，你眼中可能只剩下失望了，哈哈

如果咱们当初有这样一个需要：过滤出表中某两列值雷同的记录。例如：

索引生效了吧？惊不惊喜？

答：表里 create\_time 和 update\_time 都建了索引，独自查问某一字段时都会走索引。但如果把两个独自建了索引的列，用来做列比照时索引会生效。这其实和在索引列上加函数一个原理，MySQL 认为索引无奈满足需要。

这几个有殊途同归之处，就放一起说了。这里就不像下面几种状况 100% 不走索引了，而是有时候会走索引，有时候不走索引。到底走不走？成本计算说了算。

查问优化器是 MySQL 的外围子系统之一，成本计算又是查问优化器的外围逻辑。

全表扫描老本作为参照物，用于和表的其它拜访形式的老本做比照。任何一种拜访形式，只有老本超过了全表扫描老本，就不会被应用。

基于全表扫描老本的重要位置，要讲清楚 MySQL 的成本计算逻辑，从全表扫描成本计算开始是个不错的抉择。

定义

咱们先来看一下 Mysql 源码里成本计算的定义：

class Cost_estimate {
 private:
  // cost of I/O operations
  double io_cost;
  // cost of CPU operations
  double cpu_cost;
  // cost of remote operations
  double import_cost;
  // memory used (bytes)
  double mem_cost;
  ......
}

从下面代码能够看到，MySQL 成本计算模型定义了四种老本：

IO 老本：从磁盘或内存读取数据页的老本。
CPU 老本：拜访记录须要耗费的 CPU 老本。
导入老本：这一项始终没被应用，先疏忽。
内存老本：这一项指的是占用内存字节数，计算 MRR（Multi Range Read）形式读取数据的老本时才会用到，也先疏忽。

全表扫描的老本就只剩 IO 老本、CPU 老本这两项了

计算成本

咱们先从整体计算公式开始，而后逐渐拆解。

全表扫描老本 =io\_cost+ 1.1 +cpu\_cost+ 1。

io\_cost 前面的 1.1 是硬编码间接加到 IO 老本上的；cpu\_cost 前面的 1 也是硬编码的，间接加到 CPU 老本上。代码里长这样：

int test_quick_select(...) {
  ......
  double scan_time =
    cost_model->row_evaluate_cost(static_cast<double>(records)) 
    + 1 /* cpu_cost 前面的 + 1 */; 
  Cost_estimate cost_est = table->file->table_scan_cost();
  
  // io_cost 前面的 + 1.1
  cost_est.add_io(1.1);
  ......
}

对于这两个硬编码的值，代码里没有正文为什么要加，不过它们是个固定值，不影响咱们了解成本计算逻辑，先疏忽它们。

io\_cost =cluster\_page\_count*avg\_single\_page\_cost。

cluster\_page\_count 是主键索引数据页数量，从表的统计信息中失去，在统计信息大节会介绍。

avg\_single\_page_cost 是读取一个数据页的均匀老本，通过计算失去，公式如下：

avg\_single\_page\_cost =pages\_in\_memory\_percent 0.25 +pages\_on\_disk_percent 1.0。

pages\_in\_memory_percent 是主键索引曾经加载到 Buffer Pool 中的叶结点占所有叶结点的比例，用小数示意（取值范畴 0.0 ~ 1.0），例如：80% 示意为 0.8。数据页在内存中的比例大节会介绍具体计算逻辑。

pages\_on\_disk\_percent 是主键索引在磁盘文件中的叶结点占所有叶结点的比例，通过 1 – pages\_in\_memory\_percent 计算失去。

0.25 是老本常数 memory\_block\_read_cost 的默认值，示意从 Buffer Pool 中的一个数据页读取数据的老本。

1.0 是老本常数 io\_block\_read_cost 的默认值，示意把磁盘文件中的一个数据页加载到 Buffer Pool 的老本，加上从 Buffer Pool 中的该数据页读取数据的老本。

cpu\_cost = n\_rows * 0.1。

n_rows 是表中记录的数量，从表的统计信息中失去，在统计信息大节会介绍。

0.1 是老本常数 row\_evaluate\_cost 的默认值，示意拜访一条记录的 CPU 老本。

有了下面这些公式，咱们通过一个具体例子走一遍全表扫描成本计算的过程。

假如一个表有 15228 条记录，主键索引数据页的数量为 739，主键索引数据页曾经全副加载到 Buffer Pool（pages\_in\_memory_percent = 1.0），上面咱们开始计算过程：

pages\_on\_disk\_percent = 1 -pages\_in\_memory\_percent(1.0) =0.0。
avg\_single\_page\_cost =pages\_in\_memory\_percent(1.0) *0.25+pages\_on\_disk_percent(0.0) *1.0=0.25。
io\_cost =cluster\_page\_count(739) *avg\_single\_page\_cost(0.25) =184.75。
cpu\_cost =n\_rows(15228) * 0.1 =1522.8。
全表扫描老本 =io\_cost(184.75) +1.1+cpu\_cost(1522.8) +1=1709.55

统计信息

全表扫描成本计算过程中，用到了主键索引数据页数量、表中记录数量，这两个数据都起源 InnoDB 的表统计信息。

clustered\_index\_size 就是主键索引数据页数量，n_rows 是表中记录数量。

数据页在内存中的比例

avg\_single\_page\_cost =pages\_in\_memory\_percent 0.25 +pages\_on\_disk_percent 1.0。

下面的公式用于计算读取一个数据页的均匀老本，pages\_in\_memory_percent 是主键索引曾经加载到 Buffer Pool 中的叶结点占所有叶结点的比例。

计算代码如下：

inline double index_pct_cached(const dict_index_t *index) {
  // 索引叶结点数量
  const ulint n_leaf = index->stat_n_leaf_pages;
  ......
  // 曾经加载到 Buffer Pool 中的叶结点数量
  const uint64_t n_in_mem =
      buf_stat_per_index->get(index_id_t(index->space, index->id));
  // 已加载到 Buffer Pool 中的叶结点 [除以] 索引叶结点数量
  const double ratio = static_cast<double>(n_in_mem) / n_leaf;
  // 取值只能在 0.0 ~ 1.0 之间
  return (std::max(std::min(ratio, 1.0), 0.0));
}

InnoDB 在内存中保护了一个哈希表（buf\_stat\_per\_index->m\_store），key 是表名，value 是表的主键索引曾经加载到 Buffer Pool 中的叶子结点数量。

每次从磁盘加载某个表的主键索引的一个叶子结点数据页到 Buffer Pool 中，该表在 buf\_stat\_per\_index->m\_store 中对应的 value 值就加一。

从 Buffer Pool 的 LRU 链表淘汰某个表的主键索引叶子结点时，该表在 buf\_stat\_per\_index->m\_store 中对应的 value 值就减一。

还有其它场景，buf\_stat\_per\_index->m\_store 中的 value 值也会发生变化，不开展了。

老本常数

memory\_block\_read\_cost 和 io\_block\_read\_cost 这两个老本常数从零碎表 mysql.engine_cost 中读取，默认别离是 1.0 和 0.25

以如下 sql 为例

列 resource_type 的搜寻条件是 BETWEEN 1 AND 2，造成的扫描区间就是 [1,2]。** 优化器规定，读取二级索引的一个扫描区间的 IO 老本，和读取一个页面的 IO 老本雷同，无论它占用多少页面。(这个是规定，大家记住就好了) 因而二级索引页的 IO 老本就是 1.0。

接下来就是估算二级索引过滤后的记录数量了，也就是满足 resource_type BETWEEN 1 AND 2 的记录数量。MySQL 是这样预估的：

找到索引树中 resource_type= 1 的第一条记录，称为该区间的最左记录，这个过程是极快的。
找到索引树中 resource_type= 2 的最初一条记录，称为该区间的最右记录，这个过程也是极快的。
从最左记录向右最多读 10 个页面，如果读到了最右记录，则准确计算区间的记录数。
如果读不到最右记录，阐明两头记录比拟多，则采纳预估法。对 10 个页面中的记录数取平均值，用平均值乘以区间的页面数量即可。

索引页的 Page Header 局部有 PAGE\_N\_RECS 属性记录了页中的记录数，因而不必遍历每个页里的记录

又带来一个新的问题，如何计算这个区间的页面数量呢？还记得 B + 树的构造吗？该区间的第 0 层的叶子节点数尽管很多，难以统计，然而咱们能够看它们的父节点啊，这两个索引页的目录项大概率是会在同一个父节点页中的，在父节点页中统计区间内有多少页面就非常容易了，其实就是统计两个目录项之间隔了多少个目录项记录。

这里，咱们假如满足 resource_type BETWEEN 1 AND 2 的记录数是 15000 个，则二级索引记录的 CPU 老本是 15000 * 0.2 + 0.01 = 3000.01

接下来就是这 15000 条记录回表的 IO 老本了，MySQL 规定，每次回表的 IO 老本相当于读取一个页面的 IO 老本，二级索引过滤出的记录数量就是回表的次数。因而，回表的 IO 老本是 15000 * 1.0 = 15000.0。

综上所述，应用索引的执行老本是：

IO 老本：15000.0+1.0= 15001.0
CPU 老本：3000.01
总索引老本：15001.0+3000.01=18001.01

通过和全表扫描比照，孰优孰劣高深莫测。这下是不是分明多了？

MySQL 执行一条查问语句的流程是这样的，先找到所有可能用到的索引，而后计算全表扫描的老本，而后别离计算应用不同索引的老本，最终抉择老本最低的计划来执行查问。这里说的老本其实是由 IO 老本和 CPU 老本组成的，对于 InnoDB 引擎来说，读取一个页的 IO 老本是 1.0，读取一条记录并检测是否合乎搜寻条件的 CPU 老本是 0.2。全表扫描的成本计算非常简单，依据表的统计数据即可预估出聚簇索引占用的页面数和表的总记录数。对于二级索引的辅助查问，除了过滤二级索引自身的 IO 老本 +CPU 老本，还有回表的 IO 老本 +CPU 老本，

作者：京东物流刘海运

起源：京东云开发者社区自猿其说 Tech 转载请注明起源

关于数据库:慢SQL原因分析之索引失效-京东物流技术团队

景象

起因

索引生效概括

不满足最左匹配

索引列上有计算

索引列上有函数

字段类型不同

like 右边蕴含 %

列比照

or 和 in 和 exist 和 not in 和 not exist

成本计算

全表扫描成本计算

定义

计算成本

统计信息

数据页在内存中的比例

老本常数

索引成本计算

小结