【高性能MySQL】第五章 创建高性能的索引

¶引言

• 索引（also called “键（key） in MySQL”）是存储引擎用于快速找到记录的一种数据结构

  索引的基本功能

• 索引对于良好的性能非常关键

  • 尤其是当表中的数据量越来越大，索引对性能的影响越发重要
  • 在数据量较小且负载较低时→不恰当的索引对性能的影响可能还不明显
  • 数据量逐渐增大时，性能则急剧下降

¶5.1 索引基础

¶存储引擎使用索引的方法

• 先在索引中找到对应值

• 然后根据匹配的索引记录找到对应的数据行

• 举例：

¶5.1.1 索引的类型

• 在MySQL中，索引是在存储引擎层而不是服务器层实现的，所以没有统一的索引标准
• 不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引

¶B-Tree索引

• 使用B-Tree数据结构来存储数据

• 大多数MySQL引擎都支持这种索引

  Archive引擎是一个例外

• 底层的存储引擎可能使用不同的存储结构

  例如：NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引

  InnoDB则使用B+Tree

• 存储引擎以不同的方式使用B-Tree索引，性能有所不同，各有优劣

  • MyISAM
    • MyISAM使用前缀压缩技术使得索引更小
    • MyISAM索引通过数据的物理位置引用被索引的行
  • InnoDB
    • InnoDB则按照原数据格式进行存储
    • InnoDB根据主键引用被索引的行

• B-Tree索引能够加快访问数据的速度

  • ∵存储引擎不再需要进行全表扫描来获取需要的数据，而是从索引的根结点开始进行搜索
  • 根结点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找
  • 通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点
  • 这些指针实际上定义了子节点页中值的上限和下限
  • 最终存储引擎要么找到对应值，要么该记录不存在

• 叶子节点比较特别

  • 指针指向的是被索引的数据，而不是其他节点页（不同引擎的“指针”类型不同）
  • 在根节点和叶子节点之间可能有很多层节点页
  • 树的深度和表的大小直接相关

• B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据

• 举例1：

  在一个基于文本域的索引树上，按字母顺序传递连读的值进行查找是非常合适的

  “找出所有以I到K开头的名字”→查找效率会非常高

¶可以使用B-Tree索引的查询类型

• B-Tree索引适用于全键值、键值范围或键前缀查找

  • 其中：键前缀查找只适用于根据最左前缀的查找

• 索引树对如下类型的查询有效：

  • 全值查询

    指的是和索引中的所有列进行匹配

    例如：查找姓名为Cuba Allen，出生于1960-01-01的人

  • 匹配最左前缀

    只使用索引的第一列

    例如：查找所有姓为Allen的人

  • 匹配列前缀

    只匹配某一列的值的开头部分，也只使用了索引的第一列

    例如：查找所有以J开头的姓的人

  • 匹配范围值

    只使用了索引的第一列

    例如：查找姓在Allen和Barrymore之间的人

  • 精确匹配某一列并范围匹配另外一列

    例如：查找所有姓为Allen，并且名字是字母K开头

    即第一列last_name全匹配，第二列first_name范围匹配

  • 只访问索引的查询

    B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行

    这种“覆盖索引”的优化

¶B-Tree索引的其他使用方式

• ∵索引树中的节点是有序的
• 所以除了按值查找外，索引还可以用于查询中的ORDER BY操作（按顺序查找）

¶关于B-Tree索引的限制

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

• 不能跳过索引中的列

• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

• 小总结：
  • 以上限制都和索引列的顺序有关
  • 在优化性能时，需要使用相同的列但顺序不同的索引来满足不同类型的查询需求

¶哈希索引（hash index）

• 哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效

• 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code）

  • 哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样

• 哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针

• 在MySQL中，只有Memory引擎显式支持哈希索引

  哈希索引也是Memory引擎表的默认索引类型

• Memory引擎同时也支持B-Tree索引

• 值得一提：

  • Memory引擎支持非唯一哈希索引
  • 如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中

• 举例：

• 因为索引本身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快

¶哈希索引的限制

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行

  • 不过访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显

• 哈希索引数据并不是按照索引值顺序存储，所以也就无法用于排序

• 哈希索引也不支持部分索引列匹配查找

  • ∵哈希索引始终是使用索引列的全部内容来计算哈希值的

    例如：在数据列（A,B ）上建立哈希索引，如果查询只有数据列A，则无法使用该索引

• 哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）

• 哈希索引也不支持任何范围查询

  例如WHERE price > 100

• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）

• 当出现哈希冲突的时候，存储引擎必须遍历链表中的所有的行指针，逐行进行比较，直到找到所有符合条件的行

• 如果哈希冲突很多，一些索引维护操作的代价也会很高

  例如：

  如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用

  冲突越多，代价越大

• 因为这些限制，哈希索引只适用于某些特定的场合

  • 一旦适合哈希索引，它带来的性能提升将非常显著

  例如：

  在数据仓库应用中有一种经典的“星型”schema，需要关联很多查找表

  哈希索引就非常适合查找表的需求

• 除了Memory引擎外，NDB集群引擎也支持唯一哈希索引，且在NDB集群引擎中作用非常特殊

¶自适应哈希引擎