Mysql高性能的秘密 - 深入理解索引

Mysql索引

引言

众所周知，Mysql的高性能与索引有着密不可分的关系：

• 正确的创建和使用索引是实现高性能查询的基础。
• 索引是对查询性能优化最有效的手段。

那么正确创建和使用索引的姿势什么样的呢，以及如何优化查询性能呢，本文将进行系统性讲解。

一、创建高性能的索引

• 1.1 索引类型

  • B-Tree

    • 特点
      • 所有值按顺序存放。
      • 每一个叶子页到根的距离相同
      • 叶子节点保存了指向数据的指针
      • 叶子节点与叶子节点通过指针链接，方便范围查询
      • 树的深度与表的大小直接相关
      • 索引对多个列排序的依据是，create table语句中定义索引时列的顺序
    • 适用场景
      • 全值匹配
      • 列前缀查找
        • 即xx*，注意与最左前缀不同
      • 最左前缀
        • 注意：跳列无效，只能匹配第一列
      • 只访问索引列的查询
        • 又叫“覆盖索引”
      • 键值范围
      • 精确匹配某一列并范围匹配另外一列
        • 注意：此时范围查询右边的列无法使用索引
    • 最佳实践
      • 如果范围查询列值有限，可以通过使用多个等于条件代替范围条件，以提高效率。
      • 可以使用相同的列但顺序不同的索引来满足不同类型的查询需求。

  • Hash index

    • 特点
      • 基于哈希表实现，只有精确匹配索引所有列的查询才有效。
      • 速度非常快：对每一行数据，存储引擎都会对所以列计算一个哈希码（哈希码是一个较小的值，尽可能少的重复）。
      • 行读取：哈希索引仅存哈希值和数据行的指针，不存储字段值，故无法避免读取行。
      • 不支持排序：哈希表按照槽（哈希码)顺序存储，不以值（数据行）的顺序存储。
      • 缺点
        • 仅仅支持等值比较查询（=、IN、<=>），不支持范围查询。
        • 冲突较多时维护成本较高。
    • 适用场景
      • Memory引擎表的默认存储引擎
        • 与众不同的是，Memory支持非唯一的哈希索引，如果哈希码相同，索引会以列表方式存放多个记录指针到同一个哈希条目中。
      • InnoDB的“自适应哈希索引”
        • 当某些索引使用很频繁时，会在内存中基于B-Tree索引之上在创建一个哈希索引。
      • 如存储URL这种长度较长，可辨别度较高的数据时。
    • 最佳实践
      • 如果存储引擎不支持哈希索引，则可以创建自定义哈希索引
      • 哈希函数不要使用SHA1()和MD5()，因为其哈希结果很长，浪费大量空间，且较慢
      • 可使用简单哈希函数crc32()，冲突范围可接受同时又能提供更好的性能。
        • 数据量非常大时，crc32()会出现大量哈希冲突，可考虑自实现64位哈希函数。简单的办法可以使用MD5()函数返回值的一部分作为自定义哈希函数
      • 根据“生日悖论”，出现哈希冲突的概率增长速度可能比想象快的多。查询时，为避免冲突问题，where条件中可带入哈希值和对应列值。

  • R-Tree空间索引

    • MyISAM

  • 全文索引

• 1.2 索引的优点

  • 索引有以下几个方面的好吃：

    • 1.因为索引可以快速定位到表中指定位置，故大大减少了服务器需要扫描的数据量。
    • 2.索引按顺序存储，故可以帮助服务器避免排序和临时表。
    • 3.索引可以将随机I/O变为顺序I/O。

  • 《Relational Database Index Design and the Optimizers》--作者：Leach/Lahdenmaki

  • “三星系统”：

    • 衡量一个索引是否适合某个查询
      • 1.索引将相关记录放在一起(数据页)，则获得一星
      • 2.索引中的数据顺序与查询的排列顺序一致，则获得二星
      • 3.索引中的列包含了查询中所需的全部列则，则获得三星

  • 索引的限制：

    • 只有索引的好处大于其带来的额外工作时，索引再有必要。
      • 1.对于非常小的表，简单的全表扫描更高校。
      • 2.对于中到大型的表，索引非常有效。
      • 3.对于特大型的表，建立和使用索引的代价也较大。此时不能一条一条记录匹配，而需要区分出直接查询需要的一组数据，例如使用分区技术。

• 1.3 高性能的索引策略

  • 1.必须是“独立的列”，否则不会使用索引

    • 如果是表达式中的一部分，则不是独立的列。
    • 如果是函数的参数，则怒视独立的列。

  • 2.前缀索引

    • 概念：有时候索引很长的字符列会让索引变得大且满。通常可以索引开始的部分字符，在提高效率的同时可大大节约索引空间。
    • 前缀索引会降低索引的选择性。
    • 对于BLOB、TEXT或者很长VARCHAR类型的列，必须使用前缀索引！
    • 合适的长度的诀窍：选择足够长的前缀以保证较高的选择性，同时又不能太长以便节约空间。
    • 计算合适的长度：
      • 使前缀的选择性接近于完整列的可选择性。
    • 前缀函数：LEFT（calume，len）

  • 3.多列索引

    • 反面教材：在所有字段上创建单列索引
      • 5.0版本前：性能不如全表扫描
      • 5.0及其后：会在单列索引基础上执行合并策略，但浪费cpu、mem等资源，尤其大数据量时
    • 当explain的Extra出现索引合并时，说明此时需要一个多列索引

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

    • 1.正确的索引顺序依赖于使用该索引的查询的顺序，并且需要同时考虑如何更好的满足排序和分组的需要。
    • 2 在多列b-tree索引中，索引的顺序：先按照最左边的列排序，然后是第二列，等等。
    • 3 性能不仅与索引列的可选择性(基数)有关，也和值的分布有关。
    • 4 最佳实践：
      • 不考虑排序和分组情况下，可将选择性最高的列放到索引最前列。
      • 根据运行频率最高的查询来调整索引列的顺序，这种情况下索引的可选择性更高。
        • 索引列顺序：sarg查询预测
    • 5 使用“三星索引”衡量

  • 5.clustered聚簇索引

    • 并不是一种单独的索引类型，而是一种数据存储方式。
    • InnoDB的聚簇索引在同一个结构中同时保存了B-Tree索引和数据行。即： 在InnoDB中聚簇索引就相当于“表”。
    • 术语”聚簇“表示数据行和相临的键值紧凑地存储在一起。
      • 节点页只包含索引列
      • 叶子页包含了行的全部数据
    • 一个表只能有一个聚簇索引。
      • InnoDB通过主键聚集数据，即在主键上建立聚簇索引。如果没有定义主键，则会选择一个唯一的非空索引代替，如果没有这样的索引，则会隐式的定一个主键来作为聚簇索引。
    • 优点：
      • 减少了磁盘I/O：将相关数据保存在了一起，仅需查询少量数据页，减少了磁盘I/O。
      • 数据访问更快：聚簇索引将索引和数据保存在同一个b-tree结构中，故查询速度比非聚簇索引要快！
    • 缺点：
      • 插入速度严重依赖于插入顺序：按照主键的顺序插入是最快的方式。
      • 行移动：更新的代价较高，innodb强制将被更新的行移动到新的位置。
      • 页分裂：向已经满的数据页插入数据，或主键被更新导致需要移动行时，可能有“页分裂”问题。
      • 二级索引（非聚簇索引）可能比想象中的要更大！
        • 二级索引叶子结点保存的不是指向行的物理位置的指针，而是行的主键值。
        • 使用主键值当作指针比使用物理行指针占用更大的空间，但避免了行移动和页分裂时的维护工作！
      • 二级索引访问需要两次索引查找。
        • 第一次查找到主键值。第二次根据主键值查询聚簇索引！
      • “间隙锁”问题：高并发插入场景下，在innoDB中按主键顺序插入，主键的上界会成为“热点”，故并发插入时可能会出现“间隙锁”问题！

  • 6.覆盖索引

    • 如果一个索引包含（或覆盖）所有查询需要的字段的值，就成为覆盖索引。
    • 覆盖索引只需要扫描索引，不需要回表查找，能极大的提升性能。
    • 好处：
      • 1.极大的减少数据访问量。
      • 2.索引按照列值顺序存储，io密集型的范围查询比随机磁盘IO读取小的多。
      • 3.由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。
    • 注：当使用覆盖索引时，从的EXPLAIN 的extra列可以看到“Using index”的信息。

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

    • Mysql有两种方式可以生成有序的结果，通过排序操作；或者按照索引顺序扫描。
    • EXPLAIN的type列为“index”时，说明mysql使用列索引扫描来做了排序。
    • 只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（正序/倒序）相同时，才能使用索引对结果进行排序。
    • 同样需要满足“最左前缀要求”。
      例外：前导列为常量，可以不满足“最左前缀要求”：如果where子句或order by子句对最左列指定为常量，则可以使用索引排序。

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

    • MyISAM使用前缀压缩来减少索引大小，从而让更多索引可以放入到内存中，在某些情况下能极大提高性能。
      • 默认只压缩字符串，可通过参数设置调整。
    • MyISAM压缩方法：先完全保存索引块中的第一个值，然后将其他值与第一个值比较得到相同前缀的字节数和不同后缀部分，然后把这部分存储起来即可。
      • eg：索引块中的第一个值为“perform”，第二个值为“performance”，压缩后的存储是为“7，ance”这种形式。
    • 压缩块使用更少的空间，代价是某些操作可能更慢
    • 适用于I/O密集型应用，不适用于CPU密集型应用

  • 9.冗余和重复索引

    • 重复索引是指在相同的列上，按照相同的顺序，创建相同类型的索引。
      • 应避免，发现后应该立即移除。
    • 冗余索引与重复索引有一些不同。如果创建列索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。但再创建索引（B，A）就不是冗余索引。
    • 大多数情况下应该扩展已有的索引而不是创建新索引，但也有时候出于性能方面考虑需要冗余索引！

  • 10.索引和列

    • InnoDB只有在访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。
    • 进而减少锁定行，可以降低额外的开销，即使很小；锁定超过需要的行会增加锁征用并减少并发行。

• 1.4 拓展：

  • 【InnoDB和MyISAM的数据分别对比】
  • 【在InnoDB表中按主键顺序插入行】

• 1.5 最佳实践

  • 创建索引时，一需要考虑列的可选择行(高的)，二考虑列的使用频率。

  • 设计索引时，不仅只为现有查询考虑需要哪些索引，还要考虑对查询进行优化。应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车设计最完美的索引。

  • 为了是查询走索引，可以使用IN（）语法强制查询走索引。

    • 但是IN（）也不能滥用，因为每额外增加一个IN条件，优化器需要做的组合都是以指数形式增加，最终可能会极大的降低查询性能。

  • 尽可能将需要做范围查询的列放在索引的后面，以便优化器能尽可能多的使用索引列。

  • 避免多个范围条件：对于范围条件查询，Mysql无法在使用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这个限制。

二、查询性能优化

待续