冗余索引对查询效率的影响
背景:
在一般的数据库书籍中,简述到如何合理创建索引时都会出现这么一段话:
“索引能提高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%左右
建议继续学习:
- 由浅入深探究mysql索引结构原理、性能分析与优化 (阅读:14945)
- 浅谈MySQL索引背后的数据结构及算法 (阅读:9861)
- MacBook Air与工作效率 (阅读:9504)
- 由浅入深理解索引的实现(2) (阅读:6315)
- HBase二级索引与Join (阅读:5764)
- 如何建立合适的索引? (阅读:5334)
- InnODB和MyISAM索引统计集合 (阅读:5161)
- Innodb 表和索引结构 (阅读:4774)
- 加班与效率 (阅读:4741)
- mysql查询中利用索引的机制 (阅读:4686)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:cenalulu 来源: SQL部落
- 标签: 冗余 效率 索引
- 发布时间:2010-09-24 23:47:45
- [67] Oracle MTS模式下 进程地址与会话信
- [65] 如何拿下简短的域名
- [65] Go Reflect 性能
- [59] 图书馆的世界纪录
- [59] 【社会化设计】自我(self)部分――欢迎区
- [59] android 开发入门
- [58] IOS安全–浅谈关于IOS加固的几种方法
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 读书笔记-壹百度:百度十年千倍的29条法则
- [47] 界面设计速成