BlackChen'site

MySQL- 索引

索引是存储引擎用于快速找到记录的一种数据结构.

索引基础

要理解索引是如何工作的,最简单的方法就是看看一本书的"索引"部分,如果想在一本书中找到某个特定主题,一般会先看书的"索引",找到对应的页码.

索引的类型

B-Tree索引

B-Tree索引
InnoDB 使用的是B+Tree.
B-Tree 索引能够加快访问数据的速度,存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索,根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找. 叶子节点比较特别,他们的指针指向的是被索引的数据,而不是其他的节点页.
B-Tree 对索引列是顺序组织存储的,所以很适合查找范围数据.

B-Tree 索引的查询类型
  1. 全值匹配
  2. 匹配最左前缀
    • 只使用索引的第一列
  3. 匹配列前缀
    • 只匹配某一列值的开头部分
  4. 匹配范围值
  5. 精确匹配某一列,并范围匹配另外一列
  6. 只访问索引的查询(覆盖索引优化)
B-Tree索引的限制
  1. 如果不是按照索引的最左列开始查找,则无法使用索引
  2. 不能跳过索引中的列
  3. 如果查询中有某个列的范围查询,则其右边的所有列都无法使用索引优化.

哈希索引

基于哈希表实现,只有精确匹配索引所有列的查询才有效

InnoDB 引擎有一个特殊功能叫做"自适应哈希索引",当InnoDB注意到某些索引值被使用的非常平凡时,他会在内存中基于B-Tree索引之上再创建一个哈希索引.

创建自定义哈希索引:
1. 在B-Tree的基础上创建一个伪哈希索引. 不是真正的哈希索引,是使用B-Tree进行查找,但是本身使用哈希值,而不是键本身
2. 例如存储URL,如果需要存储大量的url,并根据URL进行查找. 使用B-Tree存储URL,存储内容会很大,因为URL本身都很长.这个时候,可以新增一列,存储URL的哈希值.

空间数据索引

可以用作地理数据存储

全文索引

一种特殊类型的索引,查找的是文本中的关键词,而不是直接比较索引中的值.

索引的优点

  1. 索引大大减少服务器需要扫描的数据量
  2. 索引可以帮助服务器避免排序和临时表
  3. 索引可以将随机IO变为顺序IO

高性能的索引

独立的列

如果查询中的列不是独立的,则MySQL就不会使用索引. 独立的列是指索引列不能使表达式的一部分.

例如:
1.SELECT name FROM T WHERE id + 1 = 5;
2.SELECT ... WHERE TO_DAYS(CURRENT_DAYE) - TO_DAYS(data_col) <= 10;

前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得大且慢. 一个策略是模拟哈希索引,另外一个,可以索引开始的部分字符串. 这样可以大大减少索引空间,提高索引的效率,但是也会降低索引的选择性.

索引的选择性: 不重复的索引值数据表的记录总数的比值. 索引的选择性越高,查询效率越高.
对于BLOB,TEXT或者很长的VARCHAR 类型的列, 必须使用前缀索引.

前缀索引是一种能使索引更小,更快的有效方法.

前缀索引的缺点
  1. MySQL无法使用前缀索引做GROUP BYORDER BY
  2. 无法使用前缀索引做覆盖扫描

多列索引 和 索引列的顺序

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

当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的. 这个时候,索引的作用只是用于优化WHERE条件的查找.

性能不只是依赖所有索引列的选择性,也和查询条件的具体指有关,也就是和值的分布有关. 可能需要根据运行频率最高的查询,调整索引列的顺序.

聚簇索引

聚餐索引并不是一种单独的索引类型,而是数据存储方式.

聚簇索引和非聚簇索引

聚簇索引: 叶子页包含了行的全部数据. InnoDB通过主键聚集数据. 如果没有定义主键,InnoDB会选择一个唯一费控的索引代替. 如果没有这样的索引,则会隐式定义一个主键来作为聚簇索引.

聚簇索引主键可能对性能有帮助,但是也可能有严重的性能问题.

聚簇索引优点
  1. 可以把相关数据保存在一起,可以根据用户ID来聚集数据. 这样只要从磁盘读取少量数据,就能获取某个用户的全部邮件
  2. 数据访问更快
  3. 使用覆盖索引扫描的查询可以直接使用叶节点上的主键值.
聚簇索引缺点
  1. 聚簇索引最大限度的提高了IO密集型应用的性能,但是如果数据全部都放在内存中,则访问顺序就没那么重要了.
  2. 插入速度严重依赖于插入的顺序.
  3. 更新聚簇索引列的代价很高
  4. 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂. 页分裂导致表占用更多的磁盘空间
  5. 聚餐索引可能导致全表扫描变慢.尤其是行比较稀疏,或者页分裂导致数据存储不连续的时候.
  6. 二级索引可能比想象的要大,因为二级索引的叶子节点包含了引用行的主键列
  7. 二级索引访问需要两次索引查找,而不是一次(回表)

最好避免随机的(不连续且值的分布范围很大)聚簇索引,特别是IO密集型应用.从性能的角度考虑,UUID作为聚簇索引很糟糕. 他使得索引的插入变得完全随机.

顺序主键什么时候会造成更坏的结果
  1. 高并发工作负载.在InnoDB中按住键顺序插入可能会造成明显的争用.主键的上界成为热点.因为所有的插入都发生在这里,并发插入可能导致间隙锁的竞争.
  2. 另一个热点是AUTO_INCREMENT锁机制.

覆盖索引

MySQL可以使用索引直接获取列的数据,这样就不再需要读取数据行.

InnoDB的二级索引可以有效利用"额外"的主键来覆盖查询.

使用索引扫描做排序

MySQL 有两种方式生成有序结果

  1. 排序操作
  2. 按索引顺序扫描

索引扫描本身很快, 只需要从一条索引记录移动到紧接着下一条记录. 但是如果所有不能覆盖查询所需的列,那就不得不每扫描一条记录就都回表查询一次. 这基本上都是随机IO,因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢,尤其是在IO密集型工作负载时.

只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或者正序)都一样时,MySQL才能使用索引来对结果做排序.

如果查询需要关联多张表,则只有当ORDER BY子句引用的字段全部为第一张表时,才能使用索引做排序.
有一种情况下,ORDER BY 子句可以不满足索引的最左前缀要求, 就是前导列为常量时,如果Where子句或者Join子句中队这些列指定了常量,可以弥补索引的不足.

冗余和重复索引

适当的情况下,可以冗余索引.

表中的索引越多,插入的速度越慢. 增加新的索引,会导致INSERT,UPDATE,DELETE等操作速度变慢.

索引和锁

索引可以让查询锁定更少的行.

总结

  1. 索引是存储引擎用于快速找到记录的一种数据结构.
  2. 索引的类型主要有: B-Tree索引,哈希索引,空间数据索引,全文索引等.
  3. InnoDB存在聚簇索引和二级索引.聚簇索引叶子页包含了行的全部数据. 二级索引的叶子包含了主键的ID.
  4. 插入数据的时候,最好选择有序的ID做主键,不要使用无序的UUID,影响插入效率
  5. 使用覆盖索引可以减少回表的次数,从而加快对数据的访问.
  6. MySQL无法使用前缀索引做GROUP BYORDER BY
  7. 无法使用前缀索引做覆盖扫描
  8. 使用InnoDB的B-Tree索引要注意 独立的列,最左前缀匹配,以及覆盖索引.

评论