mysql三星索引测试

之前看了一篇关于索引的文章,说是索引的三星规则，现在就想测试测试有多大效果。

关于三星索引是这样描述的：
第一星：索引将相关的列放到一起，即在一系必要的列上建立索引，不必为在where条件里面的列都建立索引。 
第二星：索引中的数据列顺序和查找中排列顺序一致。通常将选择性最高的列放到索引的最前列。
第三星：索引中的列包含了查询中需要的全部列。索引包含查询所需要的数据列，不再进行全表查表（聚簇索引、覆盖索引）。
同时符合上述三个条件就记为三星索引。

对于这个一星的描述，我看不太懂是什么意思，接下来就对于二星和三星的描述进行测试。
测试前我关闭了Mysql的查询缓存功能，所以不会因为缓存导致测试结果不准的问题

现在开始测试

1.准备一个100万数据的表

2.按条件查询测试

查询从事金融行业的成年用户信息，根据用户年龄进行降序展示

SELECT 
user_name,user_sex,user_age,user_phone,user_province
FROM user 
WHERE user_job = '金融' AND user_age>=18  
ORDER BY user_age DESC

没建立索引，查询三次平均耗时查询耗时：5.7秒

3.建立索引测试

第二星：索引中的数据列顺序和查找中排列顺序一致。通常将选择性最高的列放到索引的最前列。

也就是说建立一个组合索引，索引顺序与where后面查询条件的顺序一致

ALTER TABLE user ADD INDEX index_test1(user_job,user_age);

接下来测试一下，查询三次平均耗时：13秒

反而更慢了，也不知道为啥

第三星：索引中的列包含了查询中需要的全部列。索引包含查询所需要的数据列，不再进行全表查表（聚簇索引、覆盖索引）。

也就是SELECT查询的列要全部建立在索引中

将刚才的索引删了重新建立索引：

-- 删除这个索引
ALTER TABLE user DROP index index_test1;
-- 重新建立索引，基于第三颗星的标准
ALTER TABLE user ADD INDEX index_test1(user_name,user_sex,user_age,user_phone,user_province);

查询，平均耗时：6秒

可以看实际上跟没使用索引的时候速度差不多

这是因为索引有一个最左匹配原则，我建立的这个索引在这条SQL查询中根本就没有使用，关于最左匹配原则会在文章末尾处详细说明

二星+三星：建立既符合第二颗星又符合第三颗星规则的索引进行测试

就是按照where查询条件列的顺序建立索引，同时select要展示的列也全部包含在索引中。

将刚才的索引删除，重新建立

-- 删除这个索引
ALTER TABLE user DROP index index_test1;
-- 重新建立索引，基于第二颗星和第三颗星的标准
ALTER TABLE user ADD INDEX index_test1(user_job,user_age,user_name,user_sex,user_phone,user_province);

查询平均耗时：0.17秒

快了几十倍...

4.测试总结：

100万数据，不建立索引条件查询：6秒左右

100万数据，仅仅对WHERE条件中的列建立索引：效果可能也不太理想（可能我WHERE条件中的列内容全是重复的，所以和没创建索引一样吧），如果是user_name这种重复性比较少的列建立索引，那速度肯定也会提升的非常明显

100万数据，按照顺序对WHERE条件后的列创建索引的同时，也为SELECT中要展示的列创建索引（这样就不用全表扫描了，直接走索引查询）,查询耗时：0.17秒，这速度提升的太明显了

5.关于组合索引的说明

组合索引的索引文件以B－Tree格式保存，在创建组合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。

如果有一个组合索引(col_a,col_b,col_c)，下面的情况都会用到这个索引：

（1）col_a = "some value";
（2）col_a = "some value" and col_b = "some value";
（3）col_a = "some value" and col_b = "some value" and col_c = "some value";
（4）col_b = "some value" and col_a = "some value" and col_c = "some value";

对于最后一条语句，mysql会自动优化成第3条的样子。下面的情况就不会用到索引：

col_b = "aaaaaa";
col_b = "aaaa" and col_c = "cccccc";

 

参考文章：
MYSQL创建索引语句

MySQL索引的三星评估（three-star system）