【mysql索引】之多列索引

xiaoxiao2021-02-28  8

第零步:简单说一说

多列索引并不是指建立多个单列索引,而是指在多个字段建立一个索引。 在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能,MySQL在5.0之后推出了索引合并策略(index merge),一定程度上可以使用多个单列索引来定位指定的行,但实际上更多时候说明了表上的索引建的很糟糕: 1.当数据库服务器对多个单列索引做相交操作(intersection,通常伴有多个AND条件)时,通常意味着需要一个包含多个相关列的多列索引,而不是多个单列索引。 2.当数据库服务器对多个单列索引做联合操作(union,通常伴有多个OR条件)时,需要消耗大量的CPU和内存在算法缓存、排序与合并上,特别在某些索引选择性不高、且需要合并扫描并返回大量数据的时候。

3.更重要的是,优化器不会把以上的计算成本加入到查询成本之中,优化器只关心随机页面读取,这会导致查询成本被低估。

第一步:选择合适的索引顺序

这里适用于B-Tree索引,因为哈希索引并不像B-Tree那样按顺序存储。 有一个经验法则:将选择性最高的列放到索引的最前列,这个法则通常情况下有用,但通常没有避免随机IO和排序那么重要。当不需考虑排序和分组时,这个法则通常是很好的,但还需要留意的是值的分布,拿sakila数据库里的payment表举例(关于选择性和sakila这里有上一篇的链接【mysql索引】之前缀索引),查询结果如图1: SELECT COUNT(DISTINCT staff_id)/COUNT(*) staff_id_selectivity, COUNT(DISTINCT customer_id)/COUNT(*) customer_id_selectivity, COUNT(*) total FROM payment 图1: 可以看出customer_id的选择性更高,因此应该把customer_id作为多列索引的第一项,第二项是staff_id。

第二步:添加多列索引

执行下面语句即可: ALTER TABLE payment ADD KEY `idx_customer_id_staff_id` (customer_id, staff_id);

最后需注意的一点:

上面的经验法则对于下面的情况,需要额外的处理: 如果某个条件值得基数过大,例如一个商城网站数据库里customer表里字段username为guest(游客)的数量太大,几乎占了整个表的9/10,那么如果用索引来查询username为guest的用户时,那个索引基本上是起不了作用的,这样有可能会极大损耗服务器的性能,解决方法是在代码层面上解决,如限制查询username值为guest的用户等。
转载请注明原文地址: https://www.6miu.com/read-2300097.html

最新回复(0)