深入学习理解MySQL高级特性系列（三） -- 创建高性能的索引

一、索引基础

• 索引/键（key）：存储引擎用于快速找到记录的一种数据结构
• 索引是否合理：按响应时间对查询进行分析（找出消耗时间长的查询，检查分析）

1. 索引的类型

a. B-Tree索引

按顺序存储数据

• MyISAM(B+Tree)：前缀压缩技术、根据数据的物理位置引用被索引的行
• InnoDB（B+Tree）：按原数据格式存储、根据主键引用被索引的行
• 查询类型：
  • 全值匹配：和索引中的所有列进行匹配
  • 匹配最左前缀：使用索引从左开始的列（联合索引）
  • 匹配列前缀：只匹配列的值的开头部分
  • 匹配范围值
  • 精确匹配某一列并范围匹配另外一列（联合索引）
  • 只访问索引的查询：查询只用访问索引，不用访问数据行（覆盖索引）
• 失效：
  • 不是按照索引的最左列开始查找
  • 跳过索引中的列（联合索引）
  • 查询某个列的范围查询，该列右边所有列无法使用索引

b. 哈希索引

基于哈希表实现，将所有的哈希码存储在索引中，并保存指向每个数据行的指针

• 只有Menory引擎支持（默认索引）
• 限制：
  • 只存储哈希码和行指针，不存储字段值，需要读取行
  • 没有按照顺序存储，无法排序
  • 不支持部分索引列查询，必须使用全部索引列查询
  • 只支持等值比较查询，不支持范围查询
  • 出现哈希冲突时，必须遍历链表的行指针，哈希冲突多（选择性低）的时候，索引维护代价高
• 自适应哈希索引：InnoDB功能（当某些索引值使用频繁时，会在内存中基于B-Tree索引之上自动创建哈希索引）

c. 空间数据索引（R-Tree）

MyISAM支持（存储地理数据）

d. 全文索引

MyISAM支持（查找文本中的关键词），5.6之后InnoDB也支持

二、索引的优点

• 索引大大减少了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机IO变为顺序IO。
• 三星系统（索引是否适合某个查询）：
  • 一星：索引将相关的记录放到一起
  • 二星：索引中的数据顺序和查找中的排列顺序一致
  • 三星：索引中的列包含了查询中需要的全部列
• 三星系统对应三个原则：
  • 选择合适的索引避免单行查找（读取的块中包含尽可能多所需要的行）
  • 使用数据原生顺序从而避免额外的排序操作
  • 使用索引覆盖查询

三、高性能的索引策略

1. 独立的列

• 查询中的列需要是独立的：不能是表达式的一部分或是函数的参数
• 简化 WHERE 条件将索引列单独放在比较符号的一侧

2. 前缀索引和索引选择性

• 索引选择性：不重复的索引值（基数）和记录总数（T）的比值（范围：1/T～1）
• 选择性越高，基数越大，不重复的值越多，查询效率越高（唯一索引：选择性 = 1）
• BLOB、TEXT、很长的VARCHAR
  • 必须使用前缀索引
  • 前缀保证高选择性，（足够长）不能太长（节约空间）
  • 通过增加前缀长度，直到前缀选择性接近完整列的选择性（基数近似相等）
  • 缺点：前缀索引无法排序（order by）和分组（group by）以及覆盖扫描

3. 多列索引（联合索引）

4. 选择合适的索引列顺序

将选择性最高的列放到索引最前列

5. 聚簇索引

• 定义：数据存储方式，在同一个结构中保存了B-Tree索引和数据行（数据行存储在叶子节点）
• 聚簇：数据行和相邻的键值紧凑地存储在一起
• 一个表只能有一个聚餐索引
• MySQL只有InnoDB引擎支持
  • 主键索引
  • 若没有定义主键，InnoDB选择一个唯一的非空索引
  • 没有唯一非空索引，InnoDB会隐式定义一个主键（6字节的row_id）作为聚簇索引
• 优点：
  • 将相关数据保存在一起
  • 数据访问更快
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
  • 同一页中有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了
• 缺点：
  • 数据全都保存在内存中时，聚簇索引没有优势
  • 插入速度严重依赖于插入顺序，按照主键的顺序插入，速度会很快
  • 更新聚簇索引的代价很高
  • 表在插入新行，或主键被更新导致需要移动行的时候，可能面临“页分裂”问题，导致表占用更多磁盘空间
  • 导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续时
  • 主键比较大时，二级索引可能比想象的要更大（叶子结点包含了主键列）
  • 二级索引访问需要两次索引查找（先通过二级索引找到主键，在通过主键索引找到数据行）
• InnoDB和MyISAM的数据分布对比：
  • MyISAM：
    • 按照数据插入的顺序存储在磁盘
    • 在行的旁边显示行号，从0开始递增
    • 索引中每个叶子节点包含“行号”（数据行地址）
    • 主键索引和其他索引在结构上一样
      
  • InnoDB：
    • 支持聚簇索引和事务
    • 每一个叶子节点包含了主键值、事务ID、用于事务和MVCC的回滚指针、剩余列（数据行）
    • 即使主键是列前缀索引，也会包含完整的主键列
    • 二级索引的叶子节点存储主键值（移动行或者页分裂时，减少二级索引的维护工作）
• 在InnoDB表中按主键顺序插入行
  • 使用AUTO-INCREMENT自增列作为主键（表没有数据需要聚集）
  • 避免随机（不连续且值的分布范围非常大）的聚簇索引（UUID）
  • 页的最大填充因子：页大小的15/16（当达到最大填充因子，下一条记录写入到新的页中）
• 向聚簇索引中插入无序的值（UUID）：
  • 写入的目标页可能已经刷到磁盘上并从缓存中移除，InnoDB在插入前不得不先找到目标页并从磁盘中读取页到内存中，这将导致大量的随机IO
  • 乱序写入，InnoDB不得不频繁地做页分裂操作（导致移动大量数据）
  • 频繁的页分裂，页会变得稀疏并被不规则地填充，最终数据会有碎片
    

6. 覆盖索引

• 定义：索引包含（覆盖）所有需要查询的字段值
• 好处：
  • 减少数据访问量，更容易全部放入内存中（索引比数据更小）
  • 范围查询的 IO 次数变少（索引按照列值顺序存储）
  • MyISAM在内存中缓存索引，数据依赖操作系统缓存，访问数据需要操作系统，开销大（覆盖索引开销小）
  • InnoDB的二级索引在叶子节点保存了行的主键值，若二级索引能够覆盖查询，避免对主键索引二次查询（回表）
• 索引覆盖查询：发起一个被索引覆盖的查询（EXPLAIN的Extra列中显示：Using index）
• 条件：
  • 覆盖索引必须存储索引列的值（哈希索引、空间索引、全文索引不支持）
  • SELECT * 会选择所有列，即使WHERE中字段满足索引覆盖，也不行
  • 无法执行LIKE操作
• 延迟关联：延迟对列的访问，在查询的第一阶段使用覆盖索引查询到主键值（子查询中），通过筛选主键值，在聚簇（主键）索引中获取数据行（减少回表次数）

select * from table where xxx limit a,b;
select * from table where id in (select id from table where xxx limit a,b); //延迟关联

7. 使用索引扫描来做排序

• 按索引顺序读取数据（没有覆盖查询时，需要回表，随机IO）的速度比顺序地全表扫描（文件排序）慢
• 设计索引：既满足查找行，又满足排序
• 使用索引排序条件：
  • 索引的列顺序和ORDER BY子句的顺序完全一致
  • 关联多张表时，ORDER BY子句引用的字段全部为第一个表
  • 需要满足最左前缀要求（除非前面的列为常量）

WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id

8. 压缩（前缀压缩）索引

• MyISAM使用前缀压缩来减少索引的大小（让更多的索引放入内存中）
• 默认只压缩字符串，通过参数设置可以压缩整数
• 压缩索引块的方法：行指针（数据行地址）也是前缀压缩方式
  • 先完全保存索引块中的第一个值
  • 将其他值和第一个值进行比较得倒相同前缀的字节数和剩余不同后缀部分
  • 将这部分存储起来
  • 举例：第一个值（peform），第二个值（performance），第二个值压缩为“7，ance”
• 压缩索引：更少的空间、更慢的查找

9. 冗余和重复索引

• 重复索引：在相同的列上按照相同的顺序创建相同类型的索引（类型不同，不属于重复索引）
• 冗余索引：
  • 创建索引 (A，B) ，再创建索引 (A) ，索引 (A) 就是冗余索引（索引(B)，(B，A)不属于冗余）
  • 为主键拓展索引（主键列已经包含在二级索引中）
• 尽量拓展索引，而不是创建新索引
• 解决：
  • 找到冗余和重复索引（通过写复杂的查询语句、使用某些工具或commom_schema中的视图来定位）
  • 删除索引
  • 检查索引变更

10. 未使用的索引

• 永远不用的索引（累赘），建议删除
• 通过工具定位未使用的索引

11. 索引和锁

• InnoDB只有在访问行的时候才会对行加锁，索引能够减少InnoDB访问的行数
  • 条件：只有当InnoDB在存储引擎层过滤掉所有不需要的行
  • 若存储引擎无法过滤掉无效行，在InnoDB检测到数据并返回给服务器层后，MySQL会锁定行，应用WHERE语句
  • 二级索引：共享读锁
  • 主键索引：排他写锁
• 即使使用了索引，InnoDB也可能锁住一些不需要的数据（下面例子：InnoDB会获取1～4行的排他锁）

mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id < 5
-> AND actor_id <> 1 FOR UPDATE;

四、索引案例学习

1. 支持多种过滤条件

• 将sex列作为联合索引的前缀：虽然sex列选择性低
  • 几乎所有的查询都会用到sex列
  • 即使查询没有使用sex列也可以绕过（若查询不限制性别，可以在查询条件中新增AND SEX IN（‘m’，‘f’），让MySQL选择该索引）
• 如果想尽可能重用索引而不是建立大量的联合索引，可以使用IN（）技巧避免同时需要(sex， country，age )和(sex，country，region，age )的索引（定义一个全部国家列表）
• 生僻的搜索条件，这些列选择性高，可以放在sex列的前面，再通过IN（）技术来处理搜索时没有指定这些列的场景
• 将age列放在最后（查询时，大多是范围查询），将范围查询列放在最后，可以尽可能使用更多的索引列
• IN（）技巧不能滥用（每额外增加一个IN（）条件，优化器需要做的组合都将以指数形式增加，降低查询性能）

2. 避免多个范围条件

MySQL无法使用范围列后面的其他索引列（联合索引），对于多个等值条件查询（IN（）列表），没有限制

3. 优化排序

• 小数据集：文件排序（全表扫描）
• 对于LIMIT（10000，10）这种需要翻到最后页的查询：延迟关联（使用覆盖索引查询，返回需要的主键，再根据这10个主键关联原表获得需要的行），减少扫描行数

五、维护索引和表

1. 找到并修复损坏的表

• 运行CHECK TABLE 来检查是否发生了表损坏，使用REPAIR TABLE修复损坏的表
• 不支持REPAIR命令：
  • 可以使用ALTER操作来重建表，修改表的引擎为当前引擎
  • 使用离线工具（myisamchk）
  • 将数据导出再重新导入
  • 从备份中恢复表
• InnoDB一般不会出现损坏，若有则一般是硬件问题（内存/磁盘），或是数据库管理员问题

2. 更新索引统计信息

• MySQL的查询优化器通过两个API：了解存储引擎的索引值的分布信息，决定如何使用索引
  • record_in_range（）：向存储引擎传入两个边界值获取这个范围有多少记录
    • MyISAM：接口返回精确值
    • InnoDB：接口返回估算值
  • info（）：返回各种类型的数据（索引的基数：每个键值有多少记录）
• 优化器：基于成本（衡量成本的主要指标：查询需要扫描多少行）
• 通过ANALYZE TABLE：生成索引统计信息
  • Memory：不存储索引统计信息
  • MyISAM：在磁盘中存储索引统计信息，ANALYZE TABLE需要进行一次全索引扫描（锁表）
  • InnoDB：通过随机（抽样）的索引访问进行评估，并存储在内存中

3. 减少索引和数据的碎片

• 数据碎片：MyISAM（三类碎片都有）、InnoDB（没有行碎片：会移动短小的行并重写到一个片段中）
  • 行碎片：数据行被存储为多个地方的多个片段中
  • 行间碎片：逻辑上顺序的页，或者行在磁盘上不是顺序存储的（全表扫描和聚簇索引不能从顺序存储的数据中获益）
  • 剩余空间碎片：数据页中有大量的空余空间（服务器读取大量不需要的数据）
• 通过OPTIMIZE TABLE 或者导出再导入的方式，重新整理数据
• 消除索引碎片：
  • MyISAM：通过排序算法重建索引
  • InnoDB：通过删除索引再重新创建索引
• 消除数据和索引的碎片化：删除索引，重建表，再重新创建索引