Mysql测试_索引对查询性能影响

最新推荐文章于 2023-05-30 10:14:23 发布

转载最新推荐文章于 2023-05-30 10:14:23 发布 · 338 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://segmentfault.com/a/1190000008489810

文章标签：

#数据库

本文通过对比不同类型的索引在MySQL中的应用效果，展示了如何利用单列索引、多列索引来显著提升查询效率，特别是在大数据量场景下，合理使用索引可以将查询速度提高数百乃至数千倍。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 |
+----------+-------------+-------------------------------------------------------------+