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

结果一：与执行计划相关的索引(出现在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