Mysql优化之创建高性能索引(二)

时间:2021-08-15 00:46:51

1.索引的优点

索引可以让服务器快速地定位到表的指定位置。总结下来有三大优点:

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机I/O变为顺序I/O

2.高性能的索引策略

独立的列

我们通常会看到一些查询不当地使用索引,或者使得Mysql无法使用已有的索引,比如:

 SELECT ID FROM B WHERE ID + 1 = 5; 

Mysql无法解析这个方程式,我们应该养成简化where条件的习惯,下面是另一个常见错误:

 SELECT A FROM B TO_DAYS(CURRENT_DATE) - TO_DAYS(date) <= 10; 

前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样会降低索引的选择性。索引的选择性指不重复的索引值和数据表的记录总数的比值,比值越高表示选择性越好。

一般来说,对于BLOB、TEXT或者很长的VARCHAR类型的列来说,必须使用前缀索引,因为Mysql不允许索引这些列的完整长度,那么如何选取合适的前缀长度又不失选择性呢,我们来做个实验:

//创建一个只含有字符串的数据表
CREATE TABLE `demo` (
name varchar(
50) not null
);

//往里面插入1000000条乱序字符串
CALL `insert_demo`(1000000);

下面我们观察下数据表:

select count(distinct name) / count(*) from demo;
select count(distinct LEFT(name,7)) / count(*) from demo;
select count(distinct LEFT(name,9)) / count(*) from demo;
select count(distinct LEFT(name,11)) / count(*) from demo;
select count(distinct LEFT(name,12)) / count(*) from demo;
select count(distinct LEFT(name,13)) / count(*) from demo;

Mysql优化之创建高性能索引(二)

可以看到取前缀长度为11,12,13时,选择性已跟第一条基准非常接近,取其中任一长度都可以。只看平均选择性是不够的,还需要看数据分布均不均匀。

//比如取的是11,可对比取11个长度与取总长度的数据分布对比,大家可以自行实验
SELECT COUNT(*) as ant, LEFT(name, 11) as pref FROM demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;//对比
SELECT COUNT(*) as ant, name as pref FROM demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;//基准

前缀索引是一种能使索引更小,更快的方法,但也有个缺点,就是无法使用前缀索引进行覆盖扫描。一个常见的场景就是对很长的ID字段进行前缀索引。

 

多列索引

上一节讨论过索引顺序问题,比如以下情况:

//把where条件里面的列都建上索引
CREATE TABLE t ( c1 INT,
c2 INT,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
);

在复杂场景下,这些索引并非最好,比最优的索引还要差几个数量级。单列索引并不能提高Mysql的查询性能,常见策略有“索引合并”,下面我们来讨论一下

以下面的查询为例:

 SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584; 

这里应该创建一个(staff_id,customer_id)索引还是应该颠倒以下顺序?我们先看看各个WHERE条件分支对于的数据基数有多大:

Mysql优化之创建高性能索引(二)

根据经验法则,应该将索引列customer_id放在前面,因为其对应的数据量更小,我们再来看看对customer_id的条件值,对应的staff_id列的选择性如何:

Mysql优化之创建高性能索引(二)

这里需要注意的是,查询结果非常依赖于选定的具体值,如果按上述优化可能对其他条件的查询不公平,服务器的整体性能也不见得会变好,如果是从pt-query=digest这样的工具提取的“最差”查询,那么再按上述办法选定索引顺序才会变得更加高效。

Mysql优化之创建高性能索引(二)

customer_id的选择性更高,因此customer_id作为索引第一列。

 

最后,尽管关于选择性和基数的经验法则值得去研究和分析,但一定要记住别忘了where子句的排序、分组和范围条件等其他因素,这些因素可能对查询的性能找出非常大的影响。