1.环境信息
mysql-5.6.17-winx642.新建两张表，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 setmysql> 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 |+----------+-------------+-------------------------------------------------------------+