【mysql索引】之多列索引

第零步：简单说一说

多列索引并不是指建立多个单列索引，而是指在多个字段建立一个索引。

在多个列上建立独立的单列索引大部分情况下并不能提高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的用户等。