技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> MySQL --> 冗余索引对查询效率的影响

冗余索引对查询效率的影响

浏览:3662次  出处信息

背景:

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

“索引能提高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索引结构原理、性能分析与优化    (阅读:14945)
  2. 浅谈MySQL索引背后的数据结构及算法    (阅读:9861)
  3. MacBook Air与工作效率    (阅读:9504)
  4. 由浅入深理解索引的实现(2)    (阅读:6315)
  5. HBase二级索引与Join    (阅读:5764)
  6. 如何建立合适的索引?    (阅读:5334)
  7. InnODB和MyISAM索引统计集合    (阅读:5161)
  8. Innodb 表和索引结构    (阅读:4774)
  9. 加班与效率    (阅读:4741)
  10. mysql查询中利用索引的机制    (阅读:4686)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1