高性能索引

发表于 | 分类于 |

1 基础

MySQL只能高效地使用索引的最左前缀列。

ORM生成的查询只是合法查询，需要检查是否适合索引查询。

MySQL索引是由存储引擎实现。

类型

1) B-Tree索引

实际上是B+树结构，顺序存储，适合范围查找。

InnoDB、MyISAM、Memory等存储引擎支持。

InnoDB使用原数据格式存储，根据主键引用被索引的行；MyISAM使用前缀压缩技术，使用物理地址引用被索引的行。

NDB集群使用T-Tree结构（名为BTree）。

限制：

  • 只能最左匹配，不能跳过中间索引列
  • 范围索引右侧的列无法索引

2) 哈希索引

只能精确匹配索引的所有列

MySQL中，只有Memory存储引擎支持哈希索引（含非唯一哈希索引），NDB存储引擎有使用到唯一哈希索引。

限制：

  • 因为索引中不包含列值，不能避免读取行
  • 不能排序和范围查询
  • 不支持使用索引列的真子集
  • 为了避免大量哈希冲突，建议采用合适的哈希算法，应用到合适的列中。

自适应哈希索引：InnoDB自动在内部为频繁使用的索引值建立哈希索引。

自定义哈希索引：

添加并在数据变化时维护哈希索引列。

可以使用CRC32()或FNV64()作为哈希函数避免冲突。前者32位，存储空间需求小；后者64位，哈希冲突少。

不建议使用SHA1()或MD5()作为哈希函数，因为其哈希值是非常长的字符串，存储空间需求过大。

遇到哈希冲突时，可以使用字段值过滤。

3) 空间数据索引

R-Tree

如MySQL的MBRCONTAINS()等GIS函数

通常使用PostGIS解决方案

4) 全文索引

查找文本中的关键词

与B-Tree索引不冲突

使用MATCH AGAINST匹配，而不是WHERE

5) 其他索引

如TokuDB的分形树索引

2 优点和三星系统

(1) 优点

  • 避免全表扫描
  • 避免排序和临时表
  • 将随机I/O转变为顺序I/O

(2) 三星系统

  • 索引将记录集中到一起
  • 数据顺序与查询中的排列顺序一致
  • 索引包含了查询需要的所有列-> 覆盖索引

3 策略

(1) 独立的列

索引列是直接值，不能是表达式或函数

(2) 前缀索引和索引选择性

1) 前缀索引

MySQL无法使用前缀索引ORDER BY或GROUP BY，无法覆盖扫描。即无法使用部分索引排序。

MySQL不支持反向索引，需要单独建列。

2) 索引选择性

选择性：索引值数量与记录数量的比值

选择性不能只看平均情况，需要分析具体的索引分布情况。

(3) 多列索引

包含多列的索引，用于多个列间相交或联合。

版本>=5.0，MySQL使用“索引合并”策略，将多个单列索引合并为多列索引。

使用optimizer_switch关闭索引合并功能。

使用IGNORE INDEX忽略某些索引。

(4) 索引顺序

适用于记录顺序的B树索引。

通常，将选择性最高的索引放在前面。

性能提升与查询条件和具体的值有关。可以使用pt-query-digest工具和EXPLAIN指令查找慢查询。

(5) 聚簇索引

将数据行存储到索引叶子页中。

部分存储引擎支持，如InnoDB。

一张表只能有一个聚簇索引。

InnoDB：

  • 没有主键时，选择一个非空唯一索引；没有非空唯一索引时，定义一个隐式的主键。
  • 只聚集同一页中的数据行，不同页中可能分离存储。

聚簇索引优点：

  • 减少I/O，避免回表查询
  • 查询效率高

聚簇索引缺点：

  • 同时加载索引和数据到内存中，资源占用大
  • 不按主键顺序时，插入效率低。建议使用OPTIMIZE TABLE重新组织表
  • 更新操作会强制移动数据行
  • 容易页分裂，额外占用磁盘空间。InnoDB页容量达到15/16时页分裂。
  • 页分裂导致数据存储不连续，全表扫描慢
  • 二级索引（即非聚簇索引）可能更大，因为二级索引叶子节点包含了引用行的主键列？
  • 二级索引需要两次索引查找。查找数据行的主键和根据主键查找数据行

高并发场景下的顺序主键：

可能造成间隙锁争用和AUTO_INCREMENT锁机制问题

  • 重新设计表或应用，如分库分表和分段自增等
  • 更改innodb_autonic_lock_mode配置

(6) 覆盖索引

索引包含查询所需的所有字段。

因为需要包含索引列的所有值，所以只能是B树索引。

优点:

  • 减少访问数据量
  • 范围查询使用完全顺序的索引访问
  • 避免访问数据引起的系统调用，对于只缓存索引的存储引，如MyISAM
  • 避免对主键索引的二次查询，对于InnoDB聚簇索引的二级索引（其中包含了主键）

延迟关联：内层使用覆盖索引查询所需结果主键，外层与全表连接。

(7) 索引排序

MySQL生成有序结果的方法：

  • 索引排序
  • 排序

索引排序要求：ORDER BY列顺序与索引中列顺序一致，且排序方向一致。若排在前面的列为常量时，可以不遵守最左前缀限制。

(8) 前缀压缩

默认值压缩字符串。

完整保存第一个索引值，后续索引值保存为与前一索引值相同前缀的字节长度+不同的后缀。

查找依赖前面的值，无法二分查找

创建表时使用PACK KEYS设置压缩方式

(9) 冗余和重复索引

重复索引：相同的列、顺序和类型的索引，应当避免

冗余索引：已有索引的前缀列，处于性能考量，可能需要，如覆盖索引

Sholmi Noach的common_schema和Percona Toolkit的pt-duplicate-key-checker可用于查找重复读音和冗余索引

(10) 未使用的索引

不使用的索引，建议删除

检测方式：

  • Percona Server或MariaDB开启userstates服务器变量，运行一段时间后查询INFORMATION_SCHEMA.INDEX_STATISTICS查询索引使用频率
  • Percona Toolkit的pt-index-usage查询索引使用频率

(11) 索引和锁

InnoDB只有在访问数据行时加锁。

版本>=5.1，服务器在条件过滤后会释放筛出数据的锁。

InnoDB二级索引使用共享（读）锁，而主键索引使用排他（写）锁。这消除了使用覆盖索引的可能性，并且使SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询慢很多。

(12) 大型表

  • 分区

  • 元数据信息表

    使用块级别源数据技术替代索引

4 案例

(1) 多种过滤条件支持

为了使用前缀索引，使用IN枚举前缀列值。

由于MySQL无法使用范围列后的其他索引列，尽量将范围列滞后。

(2) 避免多个范围查询

为了使用索引，在考虑索引维护和空间占用平衡的前提下，尽量将范围查询转换为简单值比较。

(3) 优化排序

针对选择性很低的列，可以使用以下方式优化：

  • 反范式化
  • 预先计算
  • 缓存
  • 限制用户翻页数量
  • 延迟关联：减少扫描需要丢弃的行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 数据列sex选择性很低，数据总量上百万行
# 以下使用限制用户翻页数量和延迟关联优化
SELECT
	<cols>
FROM
	 profiles
INNER JOIN
	(SELECT
     	<primary key cols>
     FROM
     	profiles
     WHERE
     	x.sex='M'
     ORDER BY rating
     LIMIT 100000, 10
    ) AS x
USING (<primary key cols>);

5 维护

(1) 检查并修复

表损坏原因：硬件问题、系统崩溃和MySQL缺陷

以下命令，部分存储引擎不支持：

检查：CHECK TABLE

修复：

  • REPAIR TABLE
  • 不做操作的ALTER TABLE重建表
1
ALTER TABLE innodb_tbl ENGINE=INNODB;

  • 离线工具

    myisamchk

  • 导出并重新导入

数据恢复：

  • 设置innodb_force_recovery参数进入强制恢复模式
  • 开源InnoDB数据恢复工具箱InnoDB Data Recovery Toolkit

(2) 更新索引统计信息

使用ANALYZE TABLE重新生成统计信息。

MySQL查询优化器使用两个API了解索引值的分布信息：

  • records_in_range()

    统计索引范围内记录数量。InnoDB返回估计值，MyISAM返回精确值。

  • info()

    索引基数（一个索引值对应的记录数量）等信息。

MySQL优化器使用基于成本的模型，衡量成本的主要指标是一次查询扫描的数据行数。

不同存储引擎实现索引统计信息的方式不同：

  • InnoDB

    通过随机索引访问（采样）评估，存储在内存中

  • MyISAM

    锁表+全索引扫描，存储在磁盘中

  • Memory

    不存储

查询索引基数等信息：

  • SHOW INDEX FROM \中的Carfinality
  • INFORMATION_SCHEMA.STATISTICS表

信息更新时机：

  • 首次打开表
  • ANALYZE TABLE
  • 表大小变化超过1/16或新插入20亿行
  • 打开某些INFORMATION_SCHEMA表
  • 使用SHOW TABLE STATUS或SHOW INDEX
  • 客户端开启自动补全

问题：当服务器有大量数据时，更新统计信息将造成较大的服务器压力，可以关闭innodb_status_on_metadata关闭。但之后只能 通过ANALYZE TABLE手动更新。

(3) 减少索引和数据碎片

理论上，数据在叶子页上顺序且紧密地排列，查询性能更好。

三种碎片：

  • 行碎片

    数据行被分散存储在不同位置的多个片段中。单行查询也受影响。

    InnoDB不存在这种问题，会将分散的行数据聚拢。

  • 行间碎片

    逻辑上顺序的页或行，在物理上不是顺序存储的。影响全表扫描和聚簇索引等依赖行间顺序的功能。

  • 剩余空间碎片

    数据页中有大量空闲空间。页分裂引起？

解决方案：

  • OPTIMIZE TABLE

  • 导出再导入

  • 重建索引

    如InnoDB可以使用“在线”添加和删除索引重建，MyISAM使用排序算法重建

  • ALTER TABLE

    不支持OPTIMIZE TABLE的存储引擎可以使用不带操作的ALTER TABLE重建

碎片测量：

Percona的XtraBackup的–status参数以非备份方式运行，只会打印索引和表的统计情况，其中包含页中的数据量和剩余空间。

6 总结

(1) 索引选择、编写三原则：

  • 单行读取很慢

    尽可能多地读取所需数据

    使用索引可以创建位置引用，提升效率

  • 顺序访问很快

    无需多次磁盘寻道

    无需额外排序

  • 覆盖查询很快

    无需回表查询

(2) 慢查询处理

  • 找出消耗时间或资源最多的查询

  • 检查schema、SQL和索引结构

  • 判断是否扫描过多的行、额外的排序、临时表、随机I/O、过多回表查询等

  • pt-query-digest的查询审查功能review分析潜在问题索引

参考资料

《高性能MySQL》