频道栏目
首页 > 数据库 > 其他综合 > 正文
性能优化二高性能的索引策略
2017-03-20 09:28:03      个评论      
收藏   我要投稿

二、高性能的索引策略

1.独立的列

我们通常会看到一些查询不当地使用索引,或者使得mysql无法使用已有的索引。如果查询中的列不是独立的,则mysql就不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数。

例如:下面这个查询也无法使用actor_id列的索引

select actor_id from sakila.actor where actor_id + 1 = 5;

2.前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变的大且慢。

通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这个也会降低索引的选择性。所谓选择性,也就是说使查询条件变少了,匹配率更高了,索引的效率就低了。

一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能。对于BLOB、TEXT或很长的VARCHAR类型的列,必须使用前缀索引。

诀窍在于要选择足够长的前缀保证较高的选择性,同时又不能太长,前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。

为了决定前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。

例子如下:

这里写图片描述

注意到,上面每个值都出现了45到65次,现在查找到最频繁出现的城市前缀,先从3个前缀字母开始:

这里写图片描述

每个前缀都比原来的城市出现的次数更多,因此唯一前缀比唯一城市少的多。然后我们增加前缀长度,直到这个前缀的选择性接近完整列的选择性,经过实验发现前缀长度为7时,比较合适:

这里写图片描述

找到合适的前缀的长度,下面创建前缀索引:

alter table sakila.city_demo add key(city(7));

3.多列索引

很多人对多列索引的理解都不够,一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引。例如:

create table t(

c1 int,

c2 int,

c3 int,

key(c1);

key(c2);

key(c3)

);

这种索引策略,也只能算作“一星”索引,其性能比起真正优秀的索引可能差好几个数量级。在多个列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能。

在5.0及以后版本,引入了一种叫“索引合并”的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的mysql只能使用其中某一个单列索引,然而这种情况下没有哪一个单独的单列索引是非常有效的。例如,表film_actor在字段film_id 和 actor_id上各有一个单列索引。但对于下面这个索引where条件,这两个单列索引都不是最好的选择:

select film_id,actor_id from sakila.film_actor where actor_id =1 or film_id =1;

在老的msql版本中,mysql对这个查询会使用全表扫描(索引无用)。除非改写成如下的查询:

select film_id,actor_id from sakila.film_actor where actor_id =1 union all select film_id,actor_id from sakila.film_actor where film_id = 1;

但是在5.0和更新的版本中,查询能够同时使用这两个单列索引进行扫描,并讲结果进行合并。

这种算法有三个变种:

OR条件 联合(union);

and条件 相交(intersection);

组合前两种情况的联合及相交;

通过explain中的extra列可以看到:

这里写图片描述

当出现对多个索引做相互操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。 当需要对多个索引做联合操作时(通常有多个OR条件),通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并上。 更重要的是,优化器不会计算优化的成本,导致还不如直接走全表扫描。

如果再explain中看到索引合并,应该好好检查一下查询和表的结构,看是不是已经是最优了。

4.使用索引扫描来做排序

mysql有两种方式可以生成有序的结果:

通过排序操作; 按索引顺序扫描。

如果explain出来的Type列值为“index”,则说明mysql使用了索引扫描来做排序。

只有当索引的列和order by子句的顺序完全一致,并且所有的排序方向都一样时,mysql才能使用索引来对结果做排序。

有一种情况下order by子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。如何where子句或者join子句中对这些列指定了常量,就可以“弥补”索引的不足。如下表:

这里写图片描述

mysql可以使用rental_date索引为下面的查询做排序

这里写图片描述

即使order by子句不满足索引的最左前缀的要求,也可以由于查询拍下,这是因为索引的第一列被指定为一个常数。

还有更多可以使用索引做排序的查询示例。下面这个查询可以利用索引排序,是因为查询为索引的第一列提供了常量条件,而是用第二列进行排序,将两列组合在一起,就形成了索引的最左前缀:

... where rental_date = '2005-05-25' order by inventory_id desc;

下面这个查询也没问题,因为order by使用的就是索引的最左前缀:

... where rental_data > '2005-05-25' order by rental_date,inventory_id;

下面一些不能使用索引做排序的查询:

- 下面这个查询不能使用两种不同的排序方向,但是索引列都是正序排序的:

... where rental_date = '2005-05-25' order by inventory_id desc,customer_id asc;

下面这个查询的order by 子句中引用了一个不在索引中的列:

... where rental_date = '2005-05-25' order by inventory_id,staff_id;

下面这个查询的where 和 order by中的列无法组合成索引的最左前缀:

... where rental_date = '2005-05-25' order by customer_id;

下面这个查询在索引列的第一列上是范围条件,所以mysql无法使用索引的其余列:

... where rental_date > '2005-05-25' order by customer_id;

这个查询在inventory_id列上有多个等于条件。对于排序来说,这也是一种范围查询:

... where rental_date = '2005-05-25' and inventory_id in(1,2) order by customer_id;

 

点击复制链接 与好友分享!回本站首页
上一篇:数据挖掘的功能
下一篇:双峰数据和长尾数据
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站