索引

索引基础

B-TREE

• MYSIAM

  • MYSIAM索引物理位置
  • 使用前缀索引

• INNODB

  • 索引行主键
  • 按照原数据索引

• 适用范围：全键值、键值范围或键前缀査找

• 限制

  • 如果不是按最左列开始查找，则无法使用索引

  • 不能跳过索引中的列，如3个键的联合索引，查询如果只按第一列和第三列进行WHERE过滤，则无法使用索引

  • 如果某列按范围查询，则右边剩余列均无法使用索引

    • 举个例子：含有3个列的复合索引A B C，对于A=‘a’ ,B>=3,C=4 则索引只能用到A B

HASH索引

• 仅MEMORY引擎支持HASH索引（默认为HASH，也可改为BTREE）

• 使用链表记录多个HASH相同的条目

• 特点

  • HASH不保存具体字段值，只记录行指针
  • 无法应用于排序
  • 不支持部分匹配列查找
  • 只支持等值查找

• INNODB上的改进

  • 系统内部：自适应哈希索引。当某条索引值被访问非常频繁时，系统自动针对该索引值在BTREE基础上建立HASH索引
  • 系统外部：创建自定义哈希索引。如查找条件中含有较长的URL字段，可将URL字段进行CRC运算，用额外的字段保存，并在CRC列上建立索引，业务查找应用时，进行WHERE url=‘XXX’ AND crc=‘12345’，如此可以使用索引进行加速

空间数据索引(R-Tree)

• 仅MYSIAM支持，由于GIS支持不完善，大部分人采用PostgreSQL的PostGIS

全文索引

其它索引

• 如TokuDB使用了分形树索引

索引的优点

1. 索引大大减少了服务器需要扫描的数据量。

2. 索引可以帮助服务器避免排序和临时表。

3. 索引可以将随机I/O变为顺序I/O。

高性能索引策略

独立的列

• 在WHERE条件中使用基于函数、运算结果的判断条件会不使用索引

前缀索引

• 对于长的字符串字段，可以选择前缀N个字符作为索引，太短缺少区分度，太长又消耗空间
• 如果通过对某列的COUNT(DISTINCT LEFT(city, 3))/C0UNT() AS sel3,COUNT(DISTINCT LEFT(city, 4))/C0UNT() AS sel4…的方式计算区分度，如果区分度在某个值以后随着位数增多上升不明显了，则可以考虑用这个值作为前缀的位数
• 缺点：MYSQL无法用前缀索引作GROUP BY 和ORDER BY，也不能做覆盖索引

多列索引

• 多个单列索引

  • 较早版本的MYSQL中，不支持索引合并，假设表中A和B列建立了各自的索引，如果WHERE中含有A=1 OR B=1，只会走全表扫描

  • 而5.0之后的版本，加入了索引合并，以上场景会同时用到A列与B列的索引

  • 存在问题

    • 当执行计划中出现了多个单列索引的组合时，通常意味着索引建立不合理
    • 当查询中需要对多个索引做UNION时，会出现大量的缓存、排序和合并操作消耗大量CPU与内存
    • 优化器并不会把以上计算代价计算在内，优化器只关心随机页面存取，这会造成执行时所采用的执行计划可能还不如全表扫描

选择合适的索引列顺序

• 经验法则：将选择性最高的列放到索引最前列

聚簇索引

• INNODB

  • 叶子页包含了行的全部数据

  • 优点

    • 可以把相关数据放在一起，实现读取少量页即可全部读取完毕
    • 数据访问快。聚簇索引与数据同时保存在一棵BTREE中
    • 使用覆盖索引时可以直接使用叶子节点的主键值

  • 缺点

    • 如果数据全部放在内存中，BTREE的优势不明显
    • 插入速度依赖于插入顺序，当插入顺序是主键顺序时，插入速度最快；不是主键顺序时，最好在加载完成后执行optimaize table
    • 更新数据的代价很大。每次更新INNODB会强制将行移动到新的位置（文中这里应该是指包含主键值的更新，非主键列的更新是原地更新）
    • 可能出现的页分裂问题会有性能损耗
    • 当值稀疏或是由于页分裂导致记录不连续性时可能会使全表扫描变慢
    • 二级索引有可能会使比想象大
    • 通过二级索引访问有可能需要两次查询（回表）

• MYSIAM与INNODB的区别

  • 聚簇索引

    • MYSIAM：由于数据文件单独存放（按行存储），对于定长行的表，聚簇索引的叶子节点只保存行号
    • INNODB：叶子节点保存表数据。包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列

  • 非聚簇索引

    • MYSIAM：叶子节点保存行号
    • INNODB：叶子节点保存主键值

  • 顺序的主键什么时候会造成更坏的结果

    • 在高并发插入场景中，不同请求会争用主键的上限而导致间隙锁竞争(模拟场景：不同的连接插入时带主键)

覆盖索引

• 定义：索引包含要查询的值

• 特点

  • 数据量相比原数据小，因此如果只需要索引，将极大减少IO
  • 索引是按值顺序排列的（至少单个页内如此），因此如果查询条件是范围查询，比从磁盘读取每一行数据要小的多
  • 一些存储引擎只在内存中缓存索引（如MYSIAM），而数据的缓存由系统控制，因此访问数据需要一次系统调用，这可能会有性能问题

• HASH索引，全文索引，空间索引暂不支持，而且MEMORY引擎也不支持覆盖索引（该书籍出版时）

• 可以走索引的WHERE条件：等于 不等于 大于，前缀

• 5.6支持谓词下推，因此为了使用覆盖索引而将SQL查询改造为子查询的场景将不再需要

使用索引扫描做排序

• 使用条件

  • 对于多列索引，只有当多列ORDER BY的顺序与索引顺序一致，且ORDER BY里指定的顺序均同时为ASC或DESC时，才可使用索引作为排序
  • 如果使用了多张关联表，则只有当ORDER BY使用的是第1张表才能使用索引作排序

• 案例：

  • 可以使用索引的WHERE：
  • 不能使用索引的WHERE：

• filesort排序

  • 可进一步展开

压缩索引

• 原理

  • 先保存索引块的第一个值，对其他和第一个值进行比较得到相同的字节数和剩余部分

• 场景

  • IO密集型应用，且查询时只能正序扫描（从索引块的第一个开始扫）

冗余和重复索引

• 冗余索引：如有（A，B）索引，如果又建立（A）索引，则A是冗余的索引
• 重复索引：如对于主键列，又对其建立了一般索引与唯一键索引

未使用的索引

• 建议删除

索引和锁

• 1NNODB具有行锁，在5.1之前，事务中的WHERE条件会给WHERE筛选的记录均加上行锁，5.1之后在服务器端过滤掉行后就释放锁

• 查询语句：SELECT actor_id FROM sakila.actor
  WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE。假设数据返回actor_id 分别为2 3 4 的3条记录，实际上actor_id为1的记录也会被上锁（这是由于<>不能走索引，即不在扫描索引的过程中充当条件，因此存储引擎在扫描索引时，会将符合actor_id<5的全部记录均加上行锁，通过EXPLAIN看到EXTRA列出现了Using WHERE

• 执行计划中Extra列出现Using where表明数据从服务器的数据引擎返回后再应用WHERE过滤

• 如何证明actor_id为1的行被锁？在执行上述SQL语句但不提交事务时执行SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE会发现执行被阻塞

• INNODB上的细节

  • 二级索引使用共享锁
  • 访问主键索引使用排他锁

案例

案例中的用户信息表对用户信息中的(sex,country)建立前缀索引(即sex,country为实际索引的前2列)，按一般建立索引的原则应该在区分度高的字段建立。此案例认为几乎所有查询都会用到sex列，因此为了避免回表，建立sex列索引。对于某些查询WHERE条件中包含country=‘xxx’ AND bbb=‘yyy’(bbb为索引列）的情况，为了用到索引，应当在条件中加入sex IN (‘male’,‘female’)的条件

对于一些生僻的查询，如针对has_ pictures,eye_color的选择性高但又不频繁的查询，文中建议:

• 1.不需要加索引，让MYSQL多扫描一些行即可
• 2.对于形如(sex,country, region,age)的索引，可在age前加has_pictures, eye_color加索引

尽可能将需要做范围査询的列放到索引的后面以用到列多的索引列

为了优化排序速度，对于ORDERBY列也要加入索引

对于大分页

• 可能仅有策略：反范式化、预先计算和缓存
• 更好的方法是限制用户翻页数量

维护索引和表

目的

• 找到并修复损坏的表

  • MYSIAM

    • 对于MYSIAM表损坏通常是系统崩溃造成，索引损坏会出现查询出现错误结果或主键冲突

    • 运行CHECK TABLE检查是否发生了表损坏

      • 示例

    • REPAIR TABLE修复表

  • INNODB

    • 通常不会出现损坏，如果发生损坏可以设置innodb_force_recovery参数进入recovery模式

• 维护准确 的索引统计信息

  • records_in_range()函数估计WHERE条件中所可能扫描的行数，MYSIAM为精确值，INNODB为估算值

  • 如果存储引擎向优化器返回的数据是不准确的数据（如何知道是不精确？），或者执行计划太复杂无法获取各阶段所匹配的行数，此时MYSQL会使用索引的统计信息估算行数，如果信息不准优化器可能做出错误的决定

  • 通过ANALYZE TABLE可以得到索引的统计信息

    • MEMORY引擎不存储统计信息

    • MYSIAM在磁盘中保存统计信息，过程需要锁表

    • 直到MYSQL5.5 INNODB不在磁盘上保存统计信息处理，而是随机索引访问在内存在保存统计信息

      • 估算方法

        • 随机读取少量的页，并基本采样到的数据估算索引统计信息。通过设置innodb_stats_sample_pages参数可设置采样样本页的数量

          • 注：可与REDIS估计AVG_TTL的策略可以类比

      • 触发时机

        • 表首次打开
        • 执行ANALYZE TABLE
        • 表大小变化超过1/16或插入了20亿行
        • Percona数据库可通过参数设置关闭自动采样

      • 触发采样时会出现锁表影响正常业务

  • SHOW INDEX FROM可显示出索引信息

    • Cardinality列为MYSQL所估计该列有多少不同的取值

• 减少碎片

  • 碎片的种类

    • 行碎片

      • 一行内数据不连续

    • 行间碎片

      • 逻辑上连续的页物理上不连续，连续的行在物理上不连续

    • 剩余空间碎片

      • 页中有大量空余空间

  • 解决碎片

    • OPTIMIZE TABLE命令
    • 对于INNODB，可删除索引再添加索引
    • 删除数据再重新导入