MySQL索引

索引的分类

  • B-Tree索引

B-Tree 索引适合全键值、键值范围、键前缀查找(只适用于根据最左前缀的查找)。

  • 全值匹配
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精准匹配某一列并范围匹配另外一列
  • 只访问索引的查询

B-Tree索引的限制

  • 若不是按照索引的最左列开始查找,则无法使用索引。
  • 不能跳过索引中的列
  • 如果查询中某个列有范围查询, 则其右边的所有列都无法使用索引优化查找。
  • 哈希索引

定义:对每一行数据,存储引擎会对索引列计算一个哈希码。哈希索引是将所有的哈希码存储在索引中, 同时在哈希表中保存指向每个数据行的指针。
在MySQL中,只有Memory引擎显示支持哈希索引,也是Memory的默认索引类型, 同时也支持B-Tree索引。

哈希索引的限制:

  • 哈希索引值包含哈希值和行指针,不存储字段值,所以不能用索引的值来避免读取行
  • 哈希索引不是按照索引顺序存储的, 不能排序。
  • 哈希索引页不支持部分索引列匹配查找
  • 哈希索引只支持等值比较查询,如=、IN()、<>操作。不支持任何范围查询。
  • 访问哈希索引的数据非常快,除非有很多哈希冲突。
  • 如果哈希冲突很多, 一些索引维护操作的代价会很高。
  • 空间数据索引(R-Tree)

空间索引会从所有维度来索引数据。查询时,可以有效利用任意维度组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等维护数据。

  • 全文索引
  • 其他索引类型,如TokuDB使用分形树索引(fractal tree index)。InnoDB包括聚簇索引,覆盖索引等。

索引的优点

  • 索引大大减少了服务器需要扫描的数据量
  • 索引大大帮助了服务器避免排序和临时表
  • 索引可以将随机I/O变为顺序I/O

高性能的索引策略

  • 独立的列: 指索引列不能是表达式的一部分,也不能是函数的参数。
  • 前缀索引和索引选择性

索引的选择性指不重复的索引值和数据表的记录总数的比值,索引的选择性越高则查询的效率就越高。

  • 多列索引

”把WHERE条件里的列都建索引“是错误的。这样最好的情况也只是一星索引,比起真正最优的索引可能差几个数量级。如果无法设计一个三星索引, 不如忽略WHERE子句, 集中精力优化索引顺序,或者创建一个全覆盖索引
在多个列上单独建立索引大部分情况不能提高MySQL的查询效率。MySQL在5.0和更新版本引入一种“索引合并“的策略,一定程度上可以使用多个单个列索引来定位指定的行。

  • 选择合适的索引列顺序

正确的顺序依赖该列索引的查询,并同时考虑如何更好的满足排序和分组的需要。

在一个多列的B-Tree索引中, 索引列顺序意味着索引首先安装最左列进行顺序,其次是第二列等。故索引可以按照升序或降序扫描,满足精确符合列的顺序ORDER BY、GROUP BY和DISTINCT等子句查询。

如何选择索引的列顺序的经验法则: 将选择性最高的列放在索引的最前列。在某些场景(当不考虑排序和分组时)可能有帮助, 但是通常不如避免随机IO和排序那么重要, 所以一定要考虑WHERE子句的排序、分组和范围条件等其他因素。

  • 聚簇索引

聚簇索引 不是一种单独的索引类型,而是一种数据存储方式。叶子页包含了行的全部数据,但是节点页只包含索引列。

优点:

  • 可以把相关数据保存在一起, 只需从磁盘读取少数的数据页, 减少磁盘IO
  • 数据访问更快。
  • 使用覆盖索引扫描的查询可直接使用页节点的主键值

缺点:

  • 聚簇索引最大限度提高了I/O密集型应用的性能, 但如果数据全部存储在内存中, 则访问的顺序也就没那么重要了。
  • 插入速度依赖插入顺序,如果不是按照主键顺序加载数据, 在加载完成后最好使用OPTIMIZE TABLE命令重新组织下表
  • 更新聚簇索引的代价很高,会强制InnoDB将每个被更新的行移动到新的位置
  • 基于聚簇索引的表在插入新行, 或者主键被更新导致移动行的时候, 可能面临“页分裂”的问题。
  • 聚簇索引可能导致全表扫描变慢, 尤其是行比较稀疏,或者页分裂导致数据存储不连续的时候
  • 二级索引(非聚簇索引)可能比想象的要更大, 因为在二级索引的叶子节点包含了行的主键列
  • 二级索引访问需要两次索引查找,而不是一次。(二级索引中保存的“行指针”的实质。二级索引叶子节点保存的不是指向行的物理位置的指针,二是行的主键值)
  • 覆盖索引

定义: 如果一个索引包含了所有需要查询的字段的值,称为覆盖索引

优点:

  • 索引条目通常远小于数据行的大小
  • 因为索引是按照顺序存储的, 对I/O密集的范围查询会比随机从磁盘读取每一行数据的I/O要少很多。
  • 一些存储引擎如MyISAM在内存中只缓存索引, 数据依赖操作系统缓存, 访问数据需要一次系统调用, 可能会导致严重的性能问题
  • 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了行的主键值, 所以二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

索引无法覆盖的原因

  • 没有任何索引能够覆盖这个查询。因为查询从表中选择了所有的列, 没有任何索引覆盖了所有的列。理论上MySQL还有一个捷径可利用:WHERE条件中石油索引可以覆盖的,
    可使用该索引找到对应的>actor并检查title是否匹配,过滤后在读取所需要的数据行
  • MySQL不能再索引中执行LIKE操作。
  • 压缩(前缀压缩)索引

MyISAM使用前缀压缩来减少索引大小,让更多的索引可放入内存中,在某些情况下极大的提高性能。压缩块使用更少的空间,代价是某些操作可能更慢。因为每个值的压缩前缀都依赖
前面的值, 索引MyISAM在查找时无法在索引块使用二分查找只能从头开始扫描。

  • 冗余和重复索引

重复索引指在相同列上按照相同的顺序常见相同类型的索引。
冗余索引和重复索引有些不同。如果创建了所以(A,B),在创建索引(A)就是冗余索引

posted @ 2020-05-24 11:09  phper-liunian  阅读(123)  评论(0编辑  收藏  举报