单纯分享一张图:高性能索引|总结的很不错

MySQLasd 2018-11-06

索引是存储引擎用于快速查找记录的一种数据结构。索引优化是对查询性能优化最有效的手段。今天的核心就是下面这张图了...

单纯分享一张图:高性能索引|总结的很不错

高性能索引

1、索引的类型

在MySQL中,索引是在存储引擎层而不是服务器层实现的。所以没用统一的索引标准,不同存储引擎的索引工作方式并不相同。

B-Tree索引

B-Tree索引即使用B-Tree数据结构来存储数据。B-Tree通常意味着所有值都是按顺序存储的,并且每个叶子页到根的距离相同。存储引擎已不同的方式来使用B-Tree索引,性能也各不相同。

可以使用B-Tree索引的查询类型——全键值、键值范围和键前缀查找。其中键前缀查找只适用于根据最左前缀查找。

B-Tree索引有效的查询类型:

  • 全值匹配——全值匹配是指和索引中的所有列进行匹配。例如:例如有一个索引是key(name,age),该索引可用于查找name=xxx and age=20的人;
  • 匹配最左前准——最左前缀即只使用索引的第一列。例如:上边提到的索引,只使用name=xxx;
  • 匹配列前缀——也可以只匹配某一列的的值的开头部分。例如:上述索引可用于查找所有以J开头的姓的人;
  • 匹配范围值——例如:上述索引可用于查找姓在allen和bootstrap之间的人;
  • 精确匹配某一列并范围匹配另外一列——例如:第一列全值匹配,第二列范围匹配;
  • 只访问索引的查询——例如覆盖索引。

因为索引树是有序的,所以还可用于排序查找。

B-Tree索引的使用限制:

  • 如果不是按照索引的最左列开始查找,则索引失效。例如:索引key(name,age),无法查找where age=20 and name='tom'的人;
  • 不能跳过索引中的列。例如索引key(name,age,address),如果查找where name="xxx" and address="xxx",则只能使用第一列索引;
  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引。例如:索引key(name,age,address),如果查询条件where name like 'S%' and age=20,这个查询只能使用索引的第一列。

哈希索引

哈希索引基于哈希表实现,只有精确匹配索引的所有列的查询才有效。在MySQL中,只有Memory引擎显示支持哈希索引,这也是Memory引擎的默认索引类型。

对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,如果多个列的哈希码相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。

哈希索引使用限制:

  • 哈希索引值存储哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行数据;
  • 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序;
  • 哈希索引不支持部分索引列匹配查询,因为哈希索引始终是使用索引列的全部内容来计算哈希值的;
  • 哈希索引只支持等值比较查询,包括=、IN()、<=> 。不支持任何范围查询;
  • 访问哈希索引的数据非常快,除非出现哈希冲突,此时存储引擎需要遍历链表中所有行指针,逐行进行比较;
  • 如果哈希冲突非常多的话,一些索引维护操作的成本也会非常高。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时,它会在内存中基于B-Tree索引之上再创建一个哈希索引,这样B-Tree索引也具有可哈希索引的一些特性。这是一个自发的内部行为,用户无法控制,但用户可以关闭次功能。

创建自定义哈希索引

如果存储引擎不支持哈希索引创建一个伪哈希索引,可以自己。思路:在B-Tree索引的基础上创建一个伪哈希索引。

2、索引的优点

索引的三大优点:

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

三星系统——如何评价一个索引是否符合某个查询

索引将相关的记录放到一起则获得一星;如果索引中的数据顺序和查找中的排序顺序一致则获得两星;如果索引中的列包含了查询中的所有列则获得三星。

3、高性能的索引策略

  1. 独立的列——索引列不能是表达式的一部分,也不能是函数的参数。例如:SELECT actor_id FROM actor WHERE actor_id + 1 = 5;或者SELECT actor_id FROM actor WHERE f(actor_id) = 5;
  2. 前缀索引和索引选择性——有时候需要索引很长的字符列,这会让索引变得很大且很慢。此时可以有两个策略,一个是自定义哈希索引,另一个就是前缀索引;
  • 前缀索引能大大节约索引空间,从而提高索引效率,但这样也会降低索引的选择性(索引选择性——不重复的索引值和数据表记录总数的比值);
  • 索引前缀长度的选择——计算法。例如:LELECT COUNT(DISTINCT city)/COUNT(*) AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel2, ...; 如果前缀的选择性接近sel1就可以使用了。有时候只看平均选择型也不靠谱,还需要做进一步判断。
  • 缺点:MySQL无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描;
  • 有时候也可以使用前缀索引——可将对应列的字符串反序存储,并创建前缀索引。
  1. 多列索引——为多列创建合适的索引
  • 多列索引。例如:key(col1, col2, col3);
  • MySQL5.0之后的版本引入了“索引合并”的策略,一定程度上可以使用表上的多个单列索引来定位表中的行;
  • 索引合并策略有时候是一种优化后的结果,但实际上更说明表上的索引建得很糟糕。
  • 当出现服务器对多个索引做相交操作时(多个AND),通常意味着需要一个包含相关列的多列索引,而不是多个独立的单列索引;
  • 当服务器需要对多个索引做联合操作时(多个OR),通常需要耗费大量的CPU和内存在算法的缓存、排序和合并上。
  1. 选择合适的索引顺序
  • 正确的索引顺序依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要;
  • 索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求;
  • 索引列顺序的选择——在不考虑分组和排序的情况下,将选择性最高的列放到索引最前面(经验法则);
  • 避免随机I/O和排序;
  • 对于某些特殊用户和分组,避免其使用普通的索引查询。
  1. 聚簇索引——聚簇索引并不是一种单独的索引类型,而是一种数据存储方式
  2. 覆盖索引
  3. 使用索引扫描排序——MySQL有两种方式可以生成有序结果:通过排序操作;按照索引顺序扫描。
  • 只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(升序/降序)都一样时,MySQL才能使用索引来对结果做排序;
  • 当查询需要关联多张表时,只有当ORDER BY子句引用的字段全部来自第一张表时,才能使用索引排序;
  • ORDER BY子句中的字段需要满足索引的最左前缀的要求,才能使用索引排序;
  • 当索引的前导列为常量时,ORDER BY子句可以不满足索引的最左前缀要求也能使用索引排序。例如:key(rental_date, inventory_id, customer_id);... where rental_data='2018-01-08' ORDER BY inventory_id DESC;

4、维护索引和表

维护表有三个目的:找到并修复损坏的表;维护准确的索引统计信息;减少碎片。

  • 更新索引统计信息——MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息,已决定如何使用索引信息。(1)、records_in_range();(2)、info()。如果存储引擎向优化器提供的索引统计信息不准确,就会导致优化器做出错误的优化决定,这会严重影响查询性能。可通过执行ANALYZE TABLE 来重新生成统计信息以解决这个问题。
  • 减少索引和数据的碎片
  • B-Tree索引可能会碎片化,碎片化的索引可能会以很差或无序的方式存储在磁盘上,这会降低查询效率;
  • 表数据存储也可能碎片化。主要有行碎片行间碎片剩余空间碎片三种。对于MyISAM表,这三类碎片都可能发生,但InnoDB不会出现短小的行碎片,InnoDB会移动短小的行,并重写到一个片段中。
  • 【维护方法】可通过执行POTIMIZE TABLE或者导出再导入来重新整理数据;对于那些不支持POTIMIZE TABLE命令的引擎,可以执行ALTER TABLE操作来重建表。只需要将表的存储引擎改为当前的引擎即可。例如:ALTER TABLE <table> ENGINE=<engine>;

总结:索引并不是适用于任何情况。对于中型、大型表适用。对于小型表全表扫描会高效(涉及到多块读的问题),而对于特大型表,考虑”分区”技术。(上面的图如果看不清可以单独找我拿)

后面会分享更多关于devops和运维DBA内容,感兴趣的朋友可以关注下~

PS:我每天都是看为知笔记然后看兴趣发的,大家如果有什么比较关注的点或者疑问可以在下方留言,我后面也会多发一下相关内容。

单纯分享一张图:高性能索引|总结的很不错

相关推荐