《高性能MySQL》之创建高性能的索引(1)

    xiaoxiao2021-03-25  8

    1:前言

             索引是存储引擎用于快速找到记录的一种数据结构,索引是优化中第一步应该想到的,良好的索引对性能的提高是几十倍甚至几百倍的。如果你的数据量很少时,可能对索引没有特别大的喜好,但是如果数据量超过10万级别百万级别,索引的作用越发重要。当然错误的索引会也是导致性能下降。下面说的都是普通硬盘,如果在固态硬盘会有着完全不同的性能特性。但索引的原则依然成立。

    2:索引类型

            通常我们没有明确指明类型的时候,多半是B-Tree索引,大多数MySql引擎都支持这种引擎。不过底层的存储引擎也可能使用不同的存储结构,存储引擎使用不同的方式使用B-Tree索引,性能也各有不同,各有劣势。              B-Tree:         意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同。存储引擎不需要进行全表扫描来获取,而是通过从索引的根节点开始搜索,根节点的槽中存放了指向子节点的指针。         B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据。我们可以使用B-Tree来进行全键值、键值范围或键前缀查找。               全值匹配                       全值匹配指的是和索引中所有列进行匹配,比如下面的索引适合与用于查找 名字为Mr.Cheng,年龄为22的人          I-2-1:ALTER TABLE `table_name` ADD INDEX index_name ( `name`,'age' )                 匹配最左前缀                      前面的索引(I-2-1)可以用于查找所有名字为Mr.Cheng的人,只用了索引第一列。                匹配列前缀                      前面的索引(I-2-1)可以用于查找名字为Mr.C 开头的人,只用了索引的第一列,但是不能用于查找Cheng开头的人,请记住前缀。                匹配范围值                      前面的索引(I-2-1)可以用于查找名字在Allen和Barrymore之间的人,这里只用了索引第一列                精确匹配某一列并范围匹配另外一列                      前面的索引(I-2-1)可以用于查找名字为叫做Mr.Cheng,并且年龄大于21的人,这里使用了索引的第一列和第二列。                只访问索引的查询                      B-Tree通常可以只访问索引的查询,即查询访问索引,无需访问数据行,为什么可以这样?因为和B-Tree存储结构是存储原数据格式存储。如果S-2-1查询使用了I-2-1索引后就不会去物理表查询,只是会在索引中查询,因为索引已经包含了需要的值 S-2-1:select name,age from table_name where name='Mr.Cheng' and age=22              因为B-Tree是按照顺序存储的,所以我们可以想到索引可以用于查询中的Order By操作(按顺序查找) 如果Order By子句满足前面列出的几种查询,那么这个索引也可以满足对应的排序需求。 限制:                     1:如果不是按照索引的最左列(如I-2-1中第一个索引name)开始查找,则无法使用索引。                     2:不能跳过索引中的列,比如一个索引有三个列,你不能只使用第一个和第三个列,这个索引不会起作用。                     3:查询中有某个列的范围查找,则右边所有列都不能使用索引优化,比如下面的查找S-2-2,只能使用索引第一个列 S-2-2 :select id from table_name where name like 'Mr.C%' and age =23    请记住下面查询S-2-3不能使用索引 I-2-1 S-2-3 :select id from table_name where name like '%C%' and age=23                  哈希索引: 哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。和B-Tree不一样。 为了深入理解哈希索引,要明白哈希表实现,所以如果不明白请先了解,这边放一张图。          对于每一行数据,存储引擎会对所有索引列计算一个哈希码(hash code),哈希码是一个比较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。         值得注意的是在Mysql中只有Memory引擎显示支持哈希索引,Memory同时也支持B-Tree索引。 http://blog.csdn.net/defonds/article/details/46787105                 我们来分析下这条语句,如果使用哈希索引,建立的索引只有一列name S-2-3 select name from table_name where name='Mr.Cheng'         MySql先计算‘Mr.Cheng’的哈希值,并且使用该值寻找的对应记录指针,假设计算出的哈希值为8784,所以Mysql在索引中查找8784,找到后,比较值是否一样,已确保是要查找的行。           因为索引自身只存储对应的哈希值,所以索引的结构非常紧凑,这也让哈希索引查找的速度非常快,而B-Tree存储的是原数据,所以会比较占空间,如果糟糕的索引太多会影响性能。          限制:                  1:哈希索引只包含哈希值和行指针,而不是存储字段值,所以不能使用索引中的值来避免读取行。但是访问内存中的行速度非常快,可以忽略这点性能。                  2:哈希索引数据不是按照索引值顺序存储的,所以不能排序,但是它的哈希值是按照顺序存储的。                  3:哈希索引是不支持部分索引匹配查找的,因为哈希索引始终使用索引列的全部内容来计算哈希值。                  4:哈希索引只支持等值比较查询包括 =,in(),<=>                  5:如果哈希冲突很多,索引维护代价也会很高,他会先找到对应的哈希值,然后遍历对应的链表中的每一行,冲突越多,代价越大。            InnoDB虽然不能显示的支持hash索引,但是它有一个“自适应哈希索引”。当InnoDB注意到某些索引值被使用的非常频繁时,会在B-Tree索引上创建一个哈希索引,但是这是我们无法控制的,但是可以关闭该功能。            其他的索引一般不会用到。。暂时不说

    3:总结

            使用索引有以下优点:               1:大大减少了服务器要扫描的数据量               2:可以帮助服务器避免排序和临时表               3:可以将随机I/o 变成顺序I/O
    转载请注明原文地址: https://ju.6miu.com/read-200126.html

    最新回复(0)