Mysql高性能索引的策略

时间:2022-05-16 20:08:32
Mysql的explain命令:
explain命令显示了mysql如何使用索引来执行select查询语句,可以帮助写出更好的索引和更优化的查询语句。看示例如下:
Mysql高性能索引的策略

结果如下:
Mysql高性能索引的策略

explain列的解释:
table:显示这一行的数据是关于哪张表的
type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和all。显示index说明使用索引扫描来做排序。
possible_keys:显示可能应用在这张表中的索引。
key: 实际使用的索引。如果为null,则没有使用索引。很少的情况下,mysql会选择优化不足的索引。这种情况下,可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引。
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好。
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。
rows:mysql认为必须检查的用来返回请求数据的行数
extra:出现using index说明使用覆盖索引。
关于explain更加详细的信息,可参考博客 http://www.cnblogs.com/linjiqin/p/4125898.html

覆盖索引:

对于聚集索引(使用B-Tree),它的主键索引的data域中存储的是完整的行信息,它的二级索引的data域中存储的是主键。因此,当使用二级索引时,可能先在二级索引中找到目标行的主键,然后通过主键到主键索引中去查询完整的行信息,这就需要一次回表操作。

设想一下,如果二级索引是一个多列索引,这个多列索引中包含了一次查询所需要的全部字段,那么就不再需要回表操作了。我们把这种索引叫“覆盖索引”。

如下结构的一张表:
Mysql高性能索引的策略

它的索引情况如下:其中有一个主键索引(id)和一个多列索引(username, password);
Mysql高性能索引的策略

下面是建立表的语句:
Mysql高性能索引的策略


对于这张表,来看下面的查询:
1.
Mysql高性能索引的策略
结果:
Mysql高性能索引的策略

2.
Mysql高性能索引的策略
结果:
Mysql高性能索引的策略

查询1和查询2两者完全不同,查询1使用覆盖查询,覆盖查询使用到的索引是index_username_password(username, password)。一定有读者问,查询1需要的列id和username也没有被索引index_username_password覆盖啊,为何却显示覆盖索引?

其中的原因在于聚集索引的二级索引的机制,大家别忘了聚集索引的二级索引的data域中存储的是主键值。所以对于像InnoDB这种聚集索引来说, 它的二级索引index_username_password(username,password)实际上等同于四个索引(username,password),(username,password,id),(username),(username,id)。这一点大家要清楚。对于多列索引,大家也要注意,多列索引的顺序很重要,因为列的顺序决定了它的前缀索引可以是啥!而前缀索引经常要用到。继续》》》

3.
Mysql高性能索引的策略
结果:
Mysql高性能索引的策略

4.
Mysql高性能索引的策略
结果:
Mysql高性能索引的策略
查询3和查询4想必大家可以理解了,所以index_username_password(username,password)可以覆盖username,password,id三列,但不会覆盖enable这一列。因此查询4不是覆盖索引。

5.
Mysql高性能索引的策略
结果:
Mysql高性能索引的策略

6.
Mysql高性能索引的策略
结果:
Mysql高性能索引的策略

查询5是非覆盖索引,因为select *中包含了enable这一个字段。可以使用内连接进行优化,优化后子查询将使用覆盖索引,虽然没有全部使用覆盖索引,但比完全无法利用索引覆盖要好。

使用索引扫描来做排序:

Mysql有两种方式来生成排序的结果:一种是通过文件排序操作(file sort),另一种是按照索引顺序扫描。如果explain出来的type列的值为“index”或者extra列没有出现“using filesort”,那么说明了mysql使用索引扫描来做排序。通过索引扫描的方式进行排序是非常快的,这就需要设计一个索引,它既能满足排序又能满足查找。仍然以上面的数据库test_table为例。

1.
Mysql高性能索引的策略
结果:
Mysql高性能索引的策略

2.
Mysql高性能索引的策略

结果:
Mysql高性能索引的策略

3.
Mysql高性能索引的策略

结果:
Mysql高性能索引的策略

4.
Mysql高性能索引的策略

结果:
Mysql高性能索引的策略


上面的四种查询均使用了索引扫描进行排序,这里where子句中的列用A表示,order by子句中的列用B表示, 只要A和B中列的总和是“索引的全部列或者索引的最左前缀”并且“位于左侧的索引列不能有范围查询”,那么就是索引扫描。实在无法确定的话,使用explain如果出现using filesort的话就是文件排序而非索引扫描排序。

下面是一些非索引扫描排序:
Mysql高性能索引的策略

上图中的查询中,第一个查询不存在可以将其覆盖的索引;第二个查询排序方向不同,无法使用索引;第三个查询位于左侧的列出现范围查询;第四个查询位于左侧的列有范围查询。
再一次强调一下,使用explain,如果extra列出现using index说明使用覆盖索引;如果extra列出现using filesort说明使用索引排序。


前缀索引:
介绍索引之前,先介绍下索引选择性。索引选择性=(不重复的索引值/总记录数)。索引选择性的值越接近1,说明该索引作为查询条件的过滤效果越好。仍然以test_table为例,来说明如何使用前缀索引。
在test_table表中,username的字段值太长,建立索引的时候占用的空间太大,而且长度越长索引需要的对比时间就越长,因此我们希望使用username字段的一部分前缀作为索引。但是尽管使用字段前缀,我们仍然希望它的索引选择性不要太低。为此,先有如下查询语句:

Mysql高性能索引的策略

结果如下:
Mysql高性能索引的策略

可见,当使用username字段的前8位时其索引选择性就和完整列的索引选择性基本一致了,继续增加长度帮助不大。
因此建立如下前缀索引:
Mysql高性能索引的策略
在建立该前缀索引之前,先删除原来的复合索引index_username_pass(username,password),因为该索引实际上等同于(username,password)和(username)两个索引。

Mysql高性能索引的策略

在删除原来的索引后执行如下语句:
Mysql高性能索引的策略

这条语句的时间时1.903s(在我的数据库中,大约有200万条记录)。然后创建如上的前缀索引,继续执行上面的语句时间缩短到0.01毫秒以下。说明该前缀索引效果显著。虽然前缀索引更小更高效,但是它却无法用来做order by和group by,并且也无法用来做覆盖扫描。







  • Mysql高性能索引的策略
  • 大小: 5.4 KB
  • Mysql高性能索引的策略
  • 大小: 7.1 KB
  • Mysql高性能索引的策略
  • 大小: 9.9 KB
  • Mysql高性能索引的策略
  • 大小: 7.9 KB
  • Mysql高性能索引的策略
  • 大小: 3.1 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 3.4 KB
  • Mysql高性能索引的策略
  • 大小: 4.6 KB
  • Mysql高性能索引的策略
  • 大小: 3.5 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 3.9 KB
  • Mysql高性能索引的策略
  • 大小: 5.1 KB
  • Mysql高性能索引的策略
  • 大小: 3.9 KB
  • Mysql高性能索引的策略
  • 大小: 4.6 KB
  • Mysql高性能索引的策略
  • 大小: 6.9 KB
  • Mysql高性能索引的策略
  • 大小: 5.2 KB
  • Mysql高性能索引的策略
  • 大小: 4.3 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 4.9 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 19.7 KB
  • Mysql高性能索引的策略
  • 大小: 9.2 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 2.2 KB
  • Mysql高性能索引的策略
  • 大小: 1.9 KB
  • Mysql高性能索引的策略
  • 大小: 1.9 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 4.3 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 4.9 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 4.8 KB
  • Mysql高性能索引的策略
  • 大小: 4.7 KB
  • Mysql高性能索引的策略
  • 大小: 3.4 KB
  • Mysql高性能索引的策略
  • 大小: 9.4 KB