一切福田,不離方寸,從心而覓,感無不通。

MySQL:创建高性能的索引

一、索引基础  定义 索引,也叫做“键(Key)”,是存储引擎用于快速查找记录的一种数据结构。索引对于良好的性能非常关键,索引是对查询性能优化最有效的手段。 索引类型 1.B-Tree索引 当人们谈论索引的时候,如果没有特别指明类型,那多半说的是B-Tree索引,它使用B-Tree数据结构来存储数据。B-Tree通常意味着所有的值是按顺序存储的,并且每一个叶子节点到根的距离相同。 B-Tree索引的几个匹配原则: a.全值匹配:和索引中的所有列进行匹配。 b.匹配最左前缀:即索引的第一列。 c.匹配列前缀:即只匹配某一列的值的开头部分。 d.匹配范围值。 e.精确匹配某一列并范围匹配另外一列。 f.只访问索引的查询,即:覆盖索引。   B-Tree索引的几个限制(索引失效): a.不是按照索引的最左列开始查找,则不能使用索引。 b.不能跳过索引中的列。 c.如果查询中有某个列的范围查询,则其右边所有列都无法使用索引。   2.哈希索引 哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。在MySQL中只有Memory引擎显式支持哈希索引。我们在这里只作了解。   3.R-Tree索引 R-Tree索引(空间数据索引),可以用作地理数据存储。MySQL中目前仅MyISAM引擎支持。与B-Tree不同,此类型的索引无须前缀查询。必须使用MySQL的GIS相关函数来维护数据,但遗憾的是MySQL的GIS支持并不完善。   4.全文索引 全文索引是一种特殊类型的索引,它查找的是文本中关键词,而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情,而不是简单的WHERE条件匹配。   二、索引的优点 优点: 1.索引大大减少了服务器需要扫描的数据量。 2.索引可以帮助服务器避免排序和临时表。 3.索引可以将随机I/O变为顺序I/O。   什么样的表需要使用索引?简单的说应该遵循以下3条准则: 1.非常小的表:全表扫描更高效。 2.中到大型表:索引非常有效。 3.特大型的表:创建和使用索引的代价非常高。如果表有大量插入和更新,更新索引将是很大的一个开销。对于特大表,建议使用表分区技术,分区后再使用索引。   三、高性能的索引策略 示例以【居民表:resident】为例,以下是此表的结构: 1.独立的列 独立的列是指索引列不能是表达式的一部分,也不能是函数的参数。 反例:   2.前缀索引和索引选择性 前缀索引:如果需要索引的字符列很长,这会让索引变得大且慢。通常可以只索引此列开始的部分字符。这样可以大大节约索引空间,从而提高索引效率。 索引选择性:是指不重复的索引值和数据表的记录总数的比值。此值越高,索引效率越高。例如:唯一索引和主键的索引选择性是1,性能也是最好的。 索引选择性是创建前缀索引依据。 例子:给列RESIDENT_NAME加索引,varchar(500)显然太长了,我们用前缀做索引。 先计算完整列的选择性:0.4867 再计算最接近的前缀选择性,可以看到15个字符后,20个字符也是0.4867,因此15个字符作为前缀是最合适的。 最后创建索引:   3.多列索引 很多人对多列索引的理解都不够。一个常见的错误是:为每个列都创建独立的索引;另一个是按照错误的顺序创建多列索引。 反例:独立索引对多条件查询的性能提升是很小的,一般只能匹配到一个索引,效率肯定要大打折扣的。 正例:创建一个多列索引,扫描的行数马上降了下来,快了十几倍,这还只是个简单的示例。   4.选择合适的列顺序 最让人困惑的问题莫过于索引列的顺序,正确的顺序依赖于使用该索引的查询。也要考虑到排序和分组的需要。 例子:是什么让我决定以SP_ID在前创建了上面的多列索引:SP_ID_GENDER_CODE ? 答案是:计算各列的选择性。   5.聚簇索引 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。主键就是一个典型的聚簇索引。   6.覆盖索引 如果一个索引包含要查询的所有字段,不需要再去表里读取数据,这样的索引就叫做覆盖索引。覆盖索引能极大的提高查询性能。 例子:把查询中需要的字段改为索引中的字段时,这样一个覆盖索引就形成了。索引也只匹配到多列索引,ref也变为了常数。Extra也显示了Using index。   7.用索引做排序 可以用同一个索引既满足排序,又用于查询。这样的索引是最优的,历为我们日常工作中遇到查询一般都是要排序的。这里有个限制:如果查询涉及到多表联合,排序用的字段必须全部是第一个表的,才能使用索引做排序。 例子:   8.冗余和重复索引 重复索引:是指在相同的列上按照相同的顺序创建的相同类型的索引。避免出现这样的情况,发现要删除。 冗余索引:是指一个或多个列同步出现在多个索引中,各索引的列数、顺序不同。冗余索引也应避免。但有时查询写的不合理,可能出现单独为优化某个查询出现的冗余索引。 P.S 创建冗余索引时可能影响其他索引的匹配,从而导致以前的查询性能降低。   四、索引案例 索引 index(a, b, […]

龙生   22 Aug 2021
View Details