MySQL-InnDB索引学习

莱戈拉斯
161 阅读13分钟

本文主要围绕 InnoDB 引擎进行讲解。
磁盘IO作为性能消耗大户，自然优化的重点也就是尽可能减少读取次数，InnoDB 将数据划分为若干页，以页作为磁盘与内存交互的基本单位，一般页的大小为16KB。
可以通过此配置进行查看：SHOW VARIABLES LIKE 'innodb_page_size'

常见索引失效场景

索引失效的常见场景 baijiahao.baidu.com/s?id=172157… 

众所周知，拥有几年开发经验的人都能总结出几条索引失效场景，如下：

  1. 错误使用 select *
  2. 索引列上有计算
  3. 索引列使用了函数
  4. 字段类型不同
  5. like左边包含%
  6. 列对比（同一个表）
  7. or关键字
  8. not in 和 not exists
  9. 错误使用 in（详见《高性能MySQL（第三版）》电子工业出版社 223页内容）
  10. order by

那么为什么会导致索引失效，底层原因是什么？我们就来探讨一下。

索引数据类型比较

  • 哈希表，近乎O(1)的时间效率在查询上优势非常大，对数据的hash算法也可以节省空间，不支持模糊，不支持范围。
  • 二叉树，数据离散，节约不了查询时间，增加数据维护成本。
  • 平衡二叉树、红黑树，顺序插入，解决了范围查找、模糊搜索，但是耗费的空间巨大。
  • BTree，在平衡二叉树、红黑树的基础上，减少了空间的浪费，一个节点上存储多条数据，但是耗费的空间依旧很大。 比如一个节点16KB，数据字段规划的好，控制在1KB，则一个节点可以存储16条数据，三层树高度的情况下，仅能存储272（256 + 16）条数据
  • B+Tree，目前主流的mysql索引结构，非叶子节点不再存储具体数据，大大节省了空间，但是不支持全文索引。

索引类型

B+Tree

B+Tree是BTree的演变而来，B+Tree的主要特点：

  • 非叶子节点只存储key，主要由 Key + pointer 组成
  • 叶子节点存储实际数据，主要由Pointer + 实际数据组成

存储大小

在InnoDB中，一个非叶子节点主要由 Key + pointer 组成，如果 Key 是 bigint （占用8byte），pointer（占用6byte），那么一个页中16KB下，约可以存储【1170】条数据。如果一个三层的B+Tree，则可以存储【1368900（1170 * 1170 ）】条数据

哈希索引

InnoDB用户无法手动创建哈希索引。

InnoDB会自调优（self-tuning），如果判定建立自适应哈希索引（Adaptive Hash Index, AHI），能够提升查询效率，InnoDB 自己会建立相关哈希索引。

全文搜索

InnoDB并不支持全文检索技术。

全文检索解决左右模糊匹配的问题，通常使用倒排索引（inverted index）解决。

B+Tree索引分类

聚集索引

image.png

聚集索引（clustered index，也称聚簇索引）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

每张表只能拥有一个聚集索引。聚集索引的建立分为以下几种情况

主键

聚集索引会优先选择主键进行创建，我们常用的是在建表的时候就会创建一个自增ID（int或者bigint）作用主键使用：

-- 指定id作为主键 
CREATE TABLE `student` ( 
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `id_card` bigint(20) NOT NULL, 
    `number` bigint(20) NOT NULL, 
    `name` varchar(32), 
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

以int为例，当这个值达到阈值后，int在InnoDB中占用大小为4个字节，最大值为 4,294,967,295 ，当再进行更新操作的时候，计数器发放的下一个 ID 也是当前这个 Max ID ，执行语句则会出现错误：

Duplicate entry ‘4294967295’ for key ‘PRIMARY’

所以在建表的时候合理选择主键并估计数据体量很重要。

非空唯一

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替（严格地选取第一个创建的唯一索引作为它的引用）。

-- 此时会选择 number 作为主键索引 
CREATE TABLE `student` ( 
    `id_card` bigint(20) NOT NULL, 
    `number` bigint(20) NOT NULL, 
    `name` varchar(32), 
    UNIQUE KEY(number), 
    UNIQUE KEY(id_card) 
) ENGINE=InnoDB;

隐式主键

如果没有这样的索引，InnoDB则会自动生成一个隐性的6字节的 row_id 作为主键来构建聚集索引。这个隐式主键在使用上会有隐患。隐式主键row_id 是由一个全局的 dict_sys.row_id 参数进行维护的，所有没有主键的表都会用上它（并不是每一个表单独占一份），这个row_id实际上有8个字节，但row_id只会存储6个字节，如下图：

image.png

当row_id达到这个值后，row_id又会从1开始，并且插入新的数据不产生错误，还会造成数据的覆盖写，把原本row_id为1的值给覆盖掉。

MySQL中的隐藏列的具体查看 www.jb51.net/article/221… 

辅助索引

对于辅助索引( Secondary Index，也称非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录（这个通过辅助索引的主键查询聚集索引的行为叫做 回表）。举例来说：

image.png

联合索引

联合索引是指对表上的多个列进行索引。前面讨论的情况都是只对表上的一个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。

image.png

  • 执行 select * from table where a = 3 and b < 2 ，此时 b 的值是有顺序的，所以联合索引是有效的。
  • 执行 select * from table where a in (2, 3) and b < 2，此时 2，3 下的 b 值是没有顺序的，所以这种情况是没有办法高效利用联合索引的。

覆盖索引

InnoDB存储引擎支持覆盖索引（covering index,或称索引覆盖），即从辅助索引中InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的I0操作。

辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，所以在覆盖索引的情况下是可能会走辅助索引的。

EXPLAIN SELECT id,phone FROM table WHERE phone LIKE '%2321%'

详解EXPLAIN

在了解完索引及底层实现后，我们就可以使用 EXPLAIN 关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 EXPLAIN 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息。

image.png

id列

id列的编号是 select 的序列号，表示语句的执行顺序。id列越大执行优先级越高，id相同则从上往下执行。

select_type列

select_type 表示查询类型。

  • simple：单表查询。
  • primary：复杂查询中最外层的 select。
  • subquery：包含在 select 中的子查询（不在 from 子句中）。
  • derived：包含在 from 子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表。
  • union：在 union 中的第二个 select 开始后的所有 select。

table列

这一列表示这行分析语句正在访问哪个表。

type列

这一列表示关联类型或访问类型。

依次从最优到最差分别为：null > system > const > eq_ref > ref > range > index > ALL

  • NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值
  • const, system：MySQL能对查询的某部分进行优化并将其转化成一个常量。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元素匹配时为system。
  • eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。
  • ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
  • range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。
  • index：扫描全索引就能拿到结果，一般是扫描某个非聚集索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引。
  • ALL：即全表扫描，扫描你的聚集索引的所有叶子节点。
-- 相关查询语句示例 
-- NULL(此处表中建立了number索引) 
select max(number) from t_order; 
-- const(通过唯一索引条件进行筛选) 
select * from t_car where car_number = '川ASSXXX' and color = 1; 
-- eq_ref(通过主键进行关联查询) 
SELECT * FROM t_order WHERE id IN ( 
    SELECT tab.id FROM ( 
        SELECT MAX( id ) AS id FROM t_order 
        WHERE company_code IN ( 'XX', 'XXX', 'XXXX' ) GROUP BY company_code 
    ) tab 
) 
-- ref(通过索引列进行关联查询) 
SELECT * FROM t_company_config WHERE company_code = ( 
    SELECT company_code FROM t_company WHERE id = ( 100 ) 
) 
-- range(范围条件筛选) 
select * from t_company where company_code like '151%' 
-- index(走了索引) 
select company_code,count(1) from t_order GROUP BY company_code limit 10 
-- ALL
select * from t_car where car_number like '%9527%'

prossible_keys列

这一列显示查询可能使用哪些索引来查找。在一个表索引列过多的情况下，此处扫描及分析也会导致 explain 结果变慢。

key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用某个索引，可以在查询中使用 force index。

key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以推算数据量对应的树高度。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

filtered列

表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。

Extra列

这一列展示的是额外信息。

  • Using index：使用覆盖索引
  • Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
  • Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
  • Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
  • Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
  • Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

案例分析

案例一

需求：发布货源获取地址经纬度异步化处理，在此基础上对货源地址中经纬度为空的数据再次进行查询更新。

  1. 先对货源地址表分析，经纬度并没有建立索引，并且新建经纬度索引也是不合理的设计，没有查询会这样使用
  2. 发现创建时间含有索引，于是从创建时间开始下手
-- 于是写出以下SQL 
SELECT id, 
       source_number 
  FROM t_cargo_address 
 WHERE 1 = 1 
     <if test="queryAll == null or queryAll != 1"> 
         AND DATE_SUB(CURDATE(), INTERVAL 8 HOUR) <= create_time 
     </if> 
     AND id > #{id} 
     AND (longitude is null OR latitude is null) 
     AND address_detail is not null 
   ORDER BY id LIMIT #{pageSize}
-- 解决思路： 
-- 1.把数据分为两种情况，过于8小时以内的数据，考虑之前的数据肯定已经补偿过了，所以尝试隔离可能已经补偿过的数据只查询8个小时内的数据 
-- 2.对于极其特殊的情况，我们有过去8个小时以上的数据都有异常数据，所以做了一个参数传入，去掉即可进行全表扫描补偿

这个SQL上线后，结果发现了这个SQL时快时慢，很不稳定，于是开始优化。通过对执行计划的查看发现优化器的 prossible_keys 列豁然展示 PRIMARY,idx_create_time 两个索引，看来并没有和预期一样，只单纯走了 创建时间索引。看了看我们的SQL，看来还是条件有问题，使用 id 列过多，导致优化器尽然在考虑主键。还有一个SQL居然使用了 filesort 说明还使用了额外空间去排序，这个也不太合理，所以作出如下优化。

-- 改版后 
SELECT create_time, 
       cargo_source_number 
  FROM t_cargo_address 
 WHERE 1 = 1 
     <if test="queryAll == null or queryAll != 1"> 
       AND create_time >= #{startCreateTime} 
     </if> 
       AND create_time < #{endCreateTime} 
       AND (longitude IS NULL OR latitude IS NULL) 
       AND address_detail IS NOT NULL 
     ORDER BY create_time DESC LIMIT #{pageSize}

改版后，去除了 id 列使用，主要依赖 create_time 列，order by create_time desc 也避免使用了 filesort 多余空间去进行排序，大大降低了 SQL 执行时间。

image.png

案例二

需求：根据一批司机编码获取最新且有效的订单号

这个是线上慢SQL日志看到的。

image.png

这个SQL尽然需要4秒的时间，通过查看表结构得知，这个表有 driver_code 列的索引，经过前面的学习，我们知道这种 MAX(id) 走了辅助索引应用了覆盖索引应该是非常非常快的，怎么会发生这种事情？

经过仔细翻阅相关书籍，得知原来是我们理想化了。MySQL的子查询实现得非常糟糕。举个例子：

查询 actor_id 为 1 的演员参与过的所有的影片信息：

SELECT * FROM film WHERE film_id IN (SELECT film_id FROM film_actor WHERE actor_id = 1);

我们以为SQL会这样执行：

1.SELECT film_id FROM film_actor WHERE actor_id = 1; -- Result: 442,...,34533 
2.SELECT * FROM film WHERE film_id IN (442,...,34533);

但是实际上MySQL的自身实现并不是，MySQL会将相关的外层表压到子查询中，它认为这样可以更高效地查找数据行。于是在MySQL眼里会改成这样：

SELECT * FROM film WHERE EXISTS (
    SELECT * FROM film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id
)

所以导致最后我们的SQL变慢了，解决方案也很简单，避免MySQL的优化，我们可以通过 临时表 的方式进行处理。

-- 方案一：临时表 tab 
SELECT number,driver_code FROM `t_order` WHERE id IN ( 
    SELECT tab.id FROM ( SELECT MAX( id ) AS id FROM`t_order` WHERE driver_code IN (
        'D994XXXXX','D007XXXXX','D061XXXXX','D198XXXXX','D295XXXXX', 
        'D361XXXXX','D362XXXXX','D391XXXXX','D522XXXXX','D662XXXXX', 
        'D884XXXXX','D051XXXXX','D151XXXXX','D301XXXXX','D351XXXXX') 
        AND state IN ( 2, 3, 4, 5 ) GROUP BY driver_code ) 
    tab ) 
    AND driver_code IS NOT NULL; 
-- 方案二：inner join 
SELECT number,driver_code FROM `t_order` inner join ( 
    SELECT MAX( id ) AS id FROM`t_order` WHERE driver_code IN (
        'D994XXXXX','D007XXXXX','D061XXXXX','D198XXXXX','D295XXXXX', 
        'D361XXXXX','D362XXXXX','D391XXXXX','D522XXXXX','D662XXXXX', 
        'D884XXXXX','D051XXXXX','D151XXXXX','D301XXXXX','D351XXXXX') 
    AND state IN ( 2, 3, 4, 5 ) GROUP BY driver_code 
    )t1 on o.id = t1.id;

小结

当然随着产品的迭代，功能也会越来越丰富，实现也会越来越复杂。在针对明显产生慢SQL的情景，需要的是加深业务的理解，了解适用场景，适当加上加快查询的条件，这样就会好很多。通过基础+实践，能够完成这样的优化工作，还是会非常让人开心的 (^_^)2333~~~

书籍推荐

当然我这里只是针对 MySQL 的索引进行了讲解，mysql还有更多需要我们去探索与发现并得以应用的点，给大家推荐两本书，也只我在写这个笔记的时候最大的仰仗，书籍确确实实比网络上的知识更加完整，也有更多细节点的说明。

  • 姜承尧.《MySQL技术内幕 InnoDB存储引擎（第2版）》.机械工业出版社.2013.6
  • Baron Schwartz.《高性能MySQL（第3版）》.电子工业出版社.2013.5
avatar
文章
阅读
粉丝