MySQL索引对查询的影响_Mysql测试_索引对查询性能影响

1.环境信息

mysql-5.6.17-winx64

2.新建两张表，tb_big_data和tb_big_data2。tb_big_data数据1100000条，tb_big_data2数据2000条

mysql> select count(*) from tb_big_data;

+----------+

| count(*) |

+----------+

| 1100000 |

+----------+

1 row in set

mysql> select count(*) from tb_big_data2;

+----------+

| count(*) |

+----------+

| 2000 |

+----------+

1 row in set

3.执行join查询，查看执行join查询且结果为49000的查询时间

mysql> select * from tb_big_data2 b left join tb_big_data a on b.random_more = a.random where b.random_more=2;

4.对字段增加普通索引和不加索引，查询时间相差1000倍

mysql> show profiles;

+----------+--------------+--------------------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+--------------+--------------------------------------------------------------------------------------------------------+ |

| 9 | 135.77425125 | select * from tb_big_data2 b left join tb_big_data a on b.random_more = a.random where b.random_more=2 |

| 11 | 4.96485125 | ALTER TABLE tb_big_data ADD INDEX index_name (random) |

| 12 | 0.01095375 | describe tb_big_data |

| 13 | 0.14014425 | select * from tb_big_data2 b left join tb_big_data a on b.random_more = a.random where b.random_more=2 |

+----------+--------------+--------------------------------------------------------------------------------------------------------+

5.单列索引和多列索引

未创建索引，对两个字段过滤查询需要，结果为2条数据耗时6.4s，创建多列索引(count,random)，耗时0.03s，创建两个单列索引，耗时0.08s。在对多个字段进行过滤查询时，多列索引和单列索引的性能还是不一样的。

mysql> show profiles;

+----------+-------------+-------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+-------------------------------------------------------------+

| 8 | 6.439019 | select count(*) from tb_big_data where count=6 and random=2 |

| 9 | 44.570048 | create index index_name on tb_big_data(count,random) |

| 10 | 0.0311755 | select count(*) from tb_big_data where count=6 and random=2 | |

| 13 | 37.07460275 | create index index_name on tb_big_data(count) |

| 15 | 39.00397825 | create index index_name2 on tb_big_data(random) |

| 17 | 0.08649375 | select count(*) from tb_big_data where count=6 and random=2 |

+----------+-------------+-------------------------------------------------------------+