MySQL（三）｜《千万级大数据查询优化》第一篇：创建高性能的索引

MySQL优化一般是需要索引优化、查询优化、库表结构优化三驾马车齐头并进。
可以说，索引优化是对查询性能优化最有效的手段，索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好几个数量级。创建一个真正“最优”的索引经常需要重写查询，所以索引优化和查询优化的关系很紧密。
本文是《千万级大数据查询优化》系列第一篇：创建高性能的索引。
我们先从一个面试题开始。

面试题：
如果有四条sql语句，查询条件分别是
where A=1 and B=1 and C=1.
where A=1 and B=1.
where A=1 and C=1.
where B=1 and C=1.
问该怎么设计索引？
四位求职者回答：
第一个求职者回答到：需要创建3个组合索引(a, b, c)(a, c)(b, c)；
第二个求职者回答：也是需要3个组合索引(a, b)(b, c)(a, c)；
第三个求职者回答：只需要2个组合索引(a, b, c)(b, c)；
第四个求职者回答：这个要看索引的区分度是怎么样，如果ab区分度都很好，单独idx_a和idx_b，普通情况idx_abc和idx_bc。

看完四位求职者的回答，你的答案是什么呢？

一、分析四位求职者的答案

上面的面试题涉及到的知识是多列索引的创建和选择合适的索引列顺序，我们先创建一个表进行测试。

# 创建数据表
create table tb_test_1(
   id smallint unsigned auto_increment primary key,
   AAA varchar(100) not null,
   BBB varchar(100) not null,
   CCC varchar(100) not null,
   DDD varchar(100) not null
);

# 插入数据，执行几十次
INSERT INTO tb_test_1 VALUES 
(null, CONCAT(‘aaa‘, ROUND(RAND()*1)), CONCAT(‘bbb‘, ROUND(RAND()*1)), CONCAT(‘ccc‘, ROUND(RAND()*1)), CONCAT(‘ddd‘, ROUND(RAND()*1))),
(null, CONCAT(‘aaa‘, ROUND(RAND()*10)), CONCAT(‘bbb‘, ROUND(RAND()*10)), CONCAT(‘ccc‘, ROUND(RAND()*10)), CONCAT(‘ddd‘, ROUND(RAND()*10))),
(null, CONCAT(‘aaa‘, ROUND(RAND()*100)), CONCAT(‘bbb‘, ROUND(RAND()*100)), CONCAT(‘ccc‘, ROUND(RAND()*100)), CONCAT(‘ddd‘, ROUND(RAND()*100))),
(null, CONCAT(‘aaa‘, ROUND(RAND()*1000)), CONCAT(‘bbb‘, ROUND(RAND()*1000)), CONCAT(‘ccc‘, ROUND(RAND()*1000)), CONCAT(‘ddd‘, ROUND(RAND()*1000)));

再按照四位面试者的回答一一进行测试。
先按照第一个求职者回答到：需要创建3个组合索引(a, b, c)、(a, c)、(b, c)：

ALTER TABLE tb_test_1 ADD INDEX idx_abc (AAA, BBB, CCC);
ALTER TABLE tb_test_1 ADD INDEX idx_ac (AAA, CCC);
ALTER TABLE tb_test_1 ADD INDEX idx_bc (BBB, CCC);

再把四个查询分别执行下，通过执行计划检查命中索引的情况如何，在分析之前先把EXPLAIN字段的含义进行一个说明，如下所示：

1）Table：
显示这一行的数据是关于哪张表的。
2）possible_keys：
显示可能应用在这张表中的索引。如果为空，没有可能的索引。
3）key：
实际使用的索引。如果为NULL，则没有使用索引。MySQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引。
4）key_len：
使用的索引的长度。在不损失精确性的情况下，长度越短越好。
5）ref：
显示索引的哪一列被使用了，如果可能的话，是一个常数。
6）rows：
MySQL认为必须检索的用来返回请求数据的行数。
7）type：
这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL。

分别执行如下四个查询：

EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ AND BBB=‘bbb1‘ AND CCC=‘ccc1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ and BBB=‘bbb1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ and CCC=‘ccc1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE BBB=‘bbb1‘ and CCC=‘ccc1‘;

执行计划如下：

 

WHERE AAA=‘aaa1‘ AND BBB=‘bbb1‘ AND CCC=‘ccc1‘

 

WHERE AAA=‘aaa1‘ and BBB=‘bbb1‘

 

WHERE AAA=‘aaa1‘ and CCC=‘ccc1‘

 

WHERE BBB=‘bbb1‘ and CCC=‘ccc1‘

通过执行计划得知，前面两个查询使用了idx_abc组合索引，后面两个查询分别使用了idx_ac和idx_bc两个组合索引。

再按照第二个求职者回答：也是需要3个组合索引(a, b)、(b, c)、(a, c)。为了排除干扰，先把之前的索引全部删除。

# 删除索引
DROP INDEX idx_abc ON tb_test_1;
DROP INDEX idx_ac ON tb_test_1;
DROP INDEX idx_bc ON tb_test_1;

# 创建3个组合索引(a, b)、(b, c)、(a, c)
ALTER TABLE tb_test_1 ADD INDEX idx_ab (AAA, BBB);
ALTER TABLE tb_test_1 ADD INDEX idx_ac (AAA, CCC);
ALTER TABLE tb_test_1 ADD INDEX idx_bc (BBB, CCC);

再次执行

EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ AND BBB=‘bbb1‘ AND CCC=‘ccc1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ and BBB=‘bbb1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ and CCC=‘ccc1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE BBB=‘bbb1‘ and CCC=‘ccc1‘;

 

WHERE AAA=‘aaa1‘ AND BBB=‘bbb1‘ AND CCC=‘ccc1‘

 

WHERE AAA=‘aaa1‘ and BBB=‘bbb1‘

 

WHERE AAA=‘aaa1‘ and CCC=‘ccc1‘

 

WHERE BBB=‘bbb1‘ and CCC=‘ccc1‘

再来看看第三个求职者回答（第四个的回答在普通情况下一致）：只需要2个组合索引(a, b, c)、(b, c)。

# 删除索引
DROP INDEX idx_ab ON tb_test_1;
DROP INDEX idx_ac ON tb_test_1;
DROP INDEX idx_bc ON tb_test_1;

# 创建2个组合索引(a, b, c)、(b, c)
ALTER TABLE tb_test_1 ADD INDEX idx_abc (AAA, BBB, CCC);
ALTER TABLE tb_test_1 ADD INDEX idx_bc (BBB, CCC);

执行计划如下：

EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ AND BBB=‘bbb1‘ AND CCC=‘ccc1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ and BBB=‘bbb1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE AAA=‘aaa1‘ and CCC=‘ccc1‘;
EXPLAIN SELECT * FROM tb_test_1 WHERE BBB=‘bbb1‘ and CCC=‘ccc1‘;

 

WHERE AAA=‘aaa1‘ AND BBB=‘bbb1‘ AND CCC=‘ccc1‘

 

WHERE AAA=‘aaa1‘ and BBB=‘bbb1‘

 

WHERE AAA=‘aaa1‘ and CCC=‘ccc1‘

 

WHERE BBB=‘bbb1‘ and CCC=‘ccc1‘

最后，我们从key和rows的值来对比这三种情况的结果如何。
第一种：创建3个组合索引(a, b, c)(a, c)(b, c)
key:idx_abc；rows:2
key:idx_abc；rows:7
key:idx_ac；rows:8
key:idx_bc；rows:5
第二种：创建3个组合索引(a, b)(b, c)(a, c)
key:idx_bc,idx_ab；rows:1
key:idx_ab；rows:7
key:idx_ac；rows:8
key:idx_bc；rows:5
第三种：创建2个组合索引(a, b, c)(b, c)
key:idx_abc；rows:2
key:idx_abc；rows:7
key:idx_abc；rows:21
key:idx_bc；rows:5

从索引数量和遍历的行数两个指标来评价，第一、二种的效果是一样的，都需要3个组合索引，第三种的组合索引数量是2个，但是在WHERE AAA=‘aaa1‘ and CCC=‘ccc1‘查询时遍历的行数为21，比前面两种的8要大。
综合来说，在普通情况下，四位求职者的回答都是正确的。但是作为面试官来说，虽然前面三位都回答正确了，但是肯定都得不到录用！
第四位求职者说到“索引的区分度”是什么意思呢？我们以此为契机来分析如何创建一个高性能的索引。

二、创建高性能的索引

理解了后面的内容，第四位求职者的答案是否正确读者自己去判断。

2.1、组合索引：将选择性最高的列放到索引最前列

在创建组合索引时，需要选择合适的索引列顺序。合适的索引列顺序有一个经验法则：将选择性最高的列放到索引最前列（注意：这个法则也是在不需要考虑排序和分组的通常情况下有用）。
比如我们要查询WHERE AAA=‘aaa1‘ and BBB=‘bbb1‘，组合索引是应该idx_ab还是idx_ba？参考经验法则，先来看看这两个值在这个表中的分布情况，确定哪个列的选择性更高。如下查看AAA和BBB两个列的选择性值“

# 查看选择性值
SELECT COUNT(DISTINCT AAA)/COUNT(*) AS aaa_selectivity, COUNT(DISTINCT BBB)/COUNT(*) AS bbb_selectivity, COUNT(*) FROM tb_test_1;

执行结果如下：

 

查看选择性值

从结果中的值来看，AAA的选择性高于BBB，那么从这个方面来考虑组合索引应该为idx_ab。

2.2、索引长度和区分度的取舍

首先介绍下索引长度和区分度的概念。索引长度很好理解，就是这个索引的长度。我们在上面提到的：

4）key_len：
使用的索引的长度。在不损失精确性的情况下，长度越短越好。

这里提到的精确性也就是稍微的区分度。通常情况下索引长度和区分度是相互矛盾的。我们举例说明，向tb_test_1表中插入如下数据。

INSERT INTO tb_test_1 VALUES 
(null, ‘aaaaaaaaaaaaaaaaaaaaaaaa1111‘, ‘b‘, ‘c‘, ‘d‘),
(null, ‘aaaaaaaaaaaaaaaaaaaaaaaa1112‘, ‘b‘, ‘c‘, ‘d‘),
(null, ‘aaaaaaaaaaaaaaaaaaaaaaaa1122‘, ‘b‘, ‘c‘, ‘d‘),
(null, ‘aaaaaaaaaaaaaaaaaaaaaaaa1222‘, ‘b‘, ‘c‘, ‘d‘);

在创建索引之前我们需要找出“索引长度和区分度”之间的平衡值，这个很有必要。因为当索引很长时，这会让索引变得大且很慢。诀窍就是选择足够长的索引长度以保证较高的区分度，同时又不能太长（以便节约空间），也就是前缀索引应该足够长，以使得前缀索引的选择性接近于整个列。我们先找出整个列的选择性：

SELECT COUNT(DISTINCT AAA)/COUNT(*) AS aaa_selectivity FROM tb_test_1;

得出的选择性值为0.5625，如下图。

 

AAA列的选择性值

进行找出最接近整个列的选择性值的最小索引长度。

SELECT COUNT(DISTINCT LEFT(AAA,4))/COUNT(*) AS aaa_selectivity_4,
COUNT(DISTINCT LEFT(AAA,5))/COUNT(*) AS aaa_selectivity_5,
COUNT(DISTINCT LEFT(AAA,6))/COUNT(*) AS aaa_selectivity_6,
COUNT(DISTINCT LEFT(AAA,7))/COUNT(*) AS aaa_selectivity_7,
COUNT(DISTINCT LEFT(AAA,8))/COUNT(*) AS aaa_selectivity_8,
COUNT(DISTINCT LEFT(AAA,9))/COUNT(*) AS aaa_selectivity_9,
COUNT(DISTINCT LEFT(AAA,26))/COUNT(*) AS aaa_selectivity_26,
COUNT(DISTINCT LEFT(AAA,27))/COUNT(*) AS aaa_selectivity_27,
COUNT(DISTINCT LEFT(AAA,28))/COUNT(*) AS aaa_selectivity_28,
COUNT(DISTINCT LEFT(AAA,29))/COUNT(*) AS aaa_selectivity_29
FROM tb_test_1;

执行结果如下：

 

找出索引长度的平衡值

从结果中我们得知，当索引长度为28时，区分度和整个列是一致的，当索引长度为6之后，区分度也已经很高了，为0.5391，比整个列的0.5625差不了多少。当然因为长度为28也不是很大，我们把索引长度定位28，在实际应用中，当索引再长的话就不得不的损失一些精确性。
ALTER TABLE tb_test_1 ADD INDEX idx_a (AAA(28));

2.3、网上关于索引的一些传说

最后贴上网上关于索引的一些传说，读者可以先判断下是否正确。我也会在后面的文章一一验证。

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5、in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6、下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%‘
若要提高效率，可以考虑全文检索。
7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=‘abc‘--name以abc开头的id
select id from t where datediff(day,createdate,‘2005-11-30‘)=0--‘2005-11-30’生成的id
应改为:
select id from t where name like ‘abc%‘
select id from t where createdate>=‘2005-11-30‘ and createdate<‘2005-12-1‘
10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12、不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
13、很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
17、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
18、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
19、避免频繁创建和删除临时表，以减少系统表资源的消耗。
20、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
21、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
22、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
23、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。