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