背景：

在一般的数据库书籍中，简述到如何合理创建索引时都会出现这么一段话：

“索引能提高sql的执行效率，但是过多不合理的索引也会影响数据库的性能”

过度索引是如何影响数据库的性能的呢？

1。 在执行sql之前，数据库会根据metadata信息决定该使用哪个索引，如果索引过多会影响这一步骤的效率。

2。 由于每次数据更新和插入都要更新索引，因此会影响相关操作的效率

而第一点就是本文的讨论重点所在。

过度索引是否真的会影响sql执行效率？

如果影响，程度是多大？

测试环境：

drop table if EXISTS test_index_performance;

CREATE TABLE test_index_performance (

id int primary key ,

col1 varchar(10),

col2 varchar(10),

col3 varchar(10),

col4 varchar(10),

col5 varchar(10),

col6 varchar(10),

col7 varchar(10),

col8 varchar(10),

col9 varchar(10),

col10 varchar(10)

)engine=innodb;

delimiter $$

create PROCEDURE insert_data_for_test_index_performance ()

begin

DECLARE total int default 100000;

DECLARE i int default 0;

truncate table test_index_performance;

while(i < total)

do

insert into test_index_performance values (i,’a',’a',’a',’a',’a',’a',’a',’a',’a',’a');

set i=i+1;

end while ;

end $$

delimiter ;

call insert_data_for_test_index_performance();

正文：

结果一：与执行计划相关的索引（出现在possible keys的那些），索引的数量与sql执行消耗时间成正比。

create index idx1 on test_index_performance (col1);

create index idx2 on test_index_performance (col1,col2);

create index idx3 on test_index_performance (col1,col2,col3);

create index idx4 on test_index_performance (col1,col2,col3,col4);

create index idx5 on test_index_performance (col1,col2,col3,col4,col5);

create index idx6 on test_index_performance (col1,col2,col3,col4,col5,col6);

create index idx7 on test_index_performance (col1,col2,col3,col4,col5,col6,col7);

create index idx8 on test_index_performance (col1,col2,col3,col4,col5,col6,col7,col8);

create index idx9 on test_index_performance (col1,col2,col3,col4,col5,col6,col7,col8,col9);

create index idx10 on test_index_performance (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10);

执行以下语句

select count(*) from test_index_performance where col1=’a’ ;

- show profile for query 1; 结果的statistics部分

- 1索引 0.000070

- 2索引 0.000083

- 3索引 0.000107

- 4索引 0.000112

- 5索引 0.000126

- 6索引 0.000155

- 7索引 0.000152

- 8索引 0.000164

- 9索引 0.000187

结果二： 与执行计划无关的索引（不出现在possible keys的那些），不会影响sql的执行效率。

create index idx12 on test_index_performance (col2);

create index idx13 on test_index_performance (col2,col3);

create index idx14 on test_index_performance (col2,col3,col4);

create index idx15 on test_index_performance (col2,col3,col4,col5);

create index idx16 on test_index_performance (col2,col3,col4,col5,col6);

create index idx17 on test_index_performance (col2,col3,col4,col5,col6,col7);

create index idx18 on test_index_performance (col2,col3,col4,col5,col6,col7,col8);

create index idx19 on test_index_performance (col2,col3,col4,col5,col6,col7,col8,col9);

create index idx20 on test_index_performance (col2,col3,col4,col5,col6,col7,col8,col9,col10);

执行以下语句

select count(*) from test_index_performance where col1=’a’ ;

结果三： 表的大小，与索引对于sql执行效率的影响，没有直接联系

- show profile for query 1; 结果的statistics部分

- 1w条 0.000187

- 10w条   0.000192

- 20w条   0.000198

- 30w条   0.000192

总结：

1。与本条语句执行相关的index的数量（possible key），会影响最终效率

2。对效率的影响体现在，statistics阶段

3。原因在于优化器需要从information_schema中获取相关索引的metadata信息并分析，索引数量越多，这个过程越漫长

4。与本条语句执行无关的index数量不影响最终效率

5。 效率影响在10%左右

建议继续学习：

1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读：14622)
2. 浅谈MySQL索引背后的数据结构及算法    (阅读：9642)
3. MacBook Air与工作效率    (阅读：9384)
4. 由浅入深理解索引的实现(2)    (阅读：6106)
5. HBase二级索引与Join    (阅读：5625)
6. 如何建立合适的索引？    (阅读：5139)
7. InnODB和MyISAM索引统计集合    (阅读：4980)
8. Innodb 表和索引结构    (阅读：4598)
9. 加班与效率    (阅读：4524)
10. mysql查询中利用索引的机制    (阅读：4419)