共计 2325 个字符,预计需要花费 6 分钟才能阅读完成。
高性能索引的策略
创立高性能索引的几种形式:
1. 独立的列
2. 前缀索引和索引选择性
3. 多列索引
4. 抉择适合的索引列程序
5. 主键索引自增和 uuid 的区别
6. 笼罩索引
7. 索引和锁
1. 独立的列
咱们要让 mysql 正当地应用索引,在查问中,这些索引的列要是“独立的列”。“独立的列”指的是索引列不能是表达式的一部分,也不能是函数的参数。
例如,咱们不必用上面这个查问无奈应用 actor_id 列的索引:
select actor_id from actor where actor_id + 1 = 5;
mysql 无奈主动解析 actor_id + 1 = 5,咱们要养成 始终将索引列独自放在比拟符号的一侧。
2. 前缀索引和索引选择性
有时候须要索引很长的字符列,这会让索引变得大且慢,对于 blob,text 或者很长的 varchar 类型的列,必须应用前缀索引,因为 mysql 不容许索引这些列的残缺长度。
窍门在于抉择足够长的前缀以保障较高的选择性,同时又不能太长(节约空间)。
为了决定前缀的适合长度,须要找到最常见的值的列表,而后和最常见的前缀列表进行比拟。
假如咱们有一张 City 表
每个值都呈现了 45~65 次,当初查找最频繁的城市前缀,先从 3 个前缀字母开始
每个前缀都比原来的城市呈现的次数更多,因为唯一性降落了,而后咱们减少前缀长度,直到前缀的选择性靠近残缺列的选择性。
计算适合的前缀长度另一个办法就是计算残缺列的选择性,并使前缀的选择性靠近于残缺列的选择性。
假如残缺列的选择性是 0.0312,此时咱们针对不同的列长度来别离计算列选择性。
查问显示以后前缀达到 7 的时候,再减少前缀长度,可选择性的晋升幅度曾经很小了。
只看均匀选择性还是不够的,也有例外的状况,须要思考最坏状况下的选择性。均匀选择性会让你认为前缀长度为 4 或者 5 的索引曾经足够了,单如果数据分布很不平均,就可能会有陷阱。如果察看前缀为 4 的最常呈现城市的次数,能够看到显著不平均:
前缀索引是一种能使索引更小,更快的无效办法,但另外一方面也有毛病,mysql 无奈对前缀索引做 order by 和 group by , 也无奈应用前缀索引做笼罩扫描。
3. 多列索引
很多人对多列索引的了解都不够,一个常见的谬误就是,为每个列创立独立的索引,或者一些专家说“把 where 条件里的列都建设索引”这样含糊的倡议导致的。
4. 抉择适合的索引列程序
咱们最容易遇到的困惑就是如何建设一个良好的索引列的程序。
对于如何抉择索引程序有一个教训法令:
将选择性高的列放到索引的最前列。
依据 B -tree 的个性,这个时候索引的确可能最快过滤出须要的行。
假如有以下查问:
select * from payment where staff_id = 2 and customer_id = 584;
此时,能够 distinct 以下 staff_id 和 customer_id 的数量,确定那个列的可选择性更高。
5. 主键索引自增和 uuid 的区别
咱们创立两个表,别离为 userinfo_incrid 和 userinfo_uuid,而后主键别离为自增 id 和 uuid,别离往每张表中插入 100W 行数据,检测一下工夫:
留神到 UUID 主键插入行不仅破费的工夫更长,而且索引占用的空间也更大。一方面是因为主键字段更长,另一方面毫无疑问是因为页决裂和碎片导致的。
咱们看看往第一个表中插入数据时,索引产生了什么变动:
如图所示,因为主键的值是程序的,所以把 InnoDB 的每一条记录都存储在上一条记录的前面,当达到页的最大填充因子的时候,下一条记录就会写入新的页中,一旦数据依照这种程序的形式加载,主键页就会近似于被程序的记录填满,这也正是所冀望的后果。
比照一下 uuid 聚簇索引的表插入数据:
因为新行的主键值是没有法则的,并不一定比之前的大,所以 InnoDB 无奈简略地把新行插入到索引的最初,二十须要为新的行寻找适合的地位——通常是两头地位——并且调配空间,这会减少许多的额定工作,并导致数据分布不够优化,下列是一些毛病:
1. 因为写入是乱序的,InnoDB 不得不频繁地做页决裂操作,以便为新的行调配空间。页决裂会导致挪动大量的数据,一次插入起码须要批改三个页。
2. 因为频繁页决裂,页会变得稠密并被不规则地填充,所以最终数据会有碎片。
把这些随机值载入到索引里后,还须要做一次 optimize table 来重建表并优化页的填充。
因为这个案例能够看出,应用 InnoDB 时应该尽可能地按住键程序插入数据,并且尽可能地应用枯燥减少的主键值来插入新行。
6. 笼罩索引
通常大家都会依据查问的 where 条件来创立适合的索引,然而 mysql 也能够应用索引来间接获取数据(并不过原表)
,如果索引的叶子节点中曾经蕴含要查问的数据,那么久没有必要再回表查问了。
如果一个索引蕴含所有须要查问字段的值,咱们就称之为笼罩索引。
笼罩索引的益处:
1. 索引条目通常远小于数据行大小,所以如果只须要读取索引,那 mysql 就会极大地缩小拜访数据量,对于 I / O 密集型的利用也十分有帮忙,因为索引比数据更小,更容易放入没存中。
2. 因为索引是依照列值顺序存储的,所以对于 IO 密集型的范畴查问会比随机从磁盘读取每一行数据的 IO 要少得多。
不是说所有类型的索引都能够成为笼罩索引,索引笼罩必须要存储索引列的值,而哈希索引,全文索引等都不存储列的值,所以 mysql 只能用 b -tree 索引做笼罩索引。
7. 索引和锁
咱们都晓得 InnoDB 的行锁是由索引实现的,没有索引的话,InnoDB 就会锁住所有的行,所以索引能够让查问锁定更少的行。
例如
select * from actor where actor_id <= 5 for update;
此时,如果有 actor_id 索引的话,只会锁住 1~5 行,否则将会进行全表扫描并锁住全副的行。