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

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;

可以看到取前缀长度为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条件分支对于的数据基数有多大：

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

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

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

 

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