MySql慢查询检测与优化

幽鸿

发布于 2020-04-02 18:37:23

6520

发布于 2020-04-02 18:37:23

眼下用的最多的关系型数据库数MySql莫属了，之前也用过其它各种数据库。最近使用MySql一段时间了，突然好奇心下，想看看MySql到底性能如何？刚好最近手上有一份2000W的数据集，刚好拿过来练练手。

这份数据csv格式总共有3个G，原始Sqlserver的bak文件解压后有7.8个G。数据总共有2000W条。这份数据仔细看过之后，消息量惊人，以下本文中以OR代替。查询OR记录数如下：

mysql> select count(*) from OR;
+----------+
| count(*) |
+----------+
| 20051420 |
+----------+
1 row in set (37.25 sec)

这个数据量是刚导入的，我先用程序把原始的3G数据文件切割成100W条的文件，共22个。刚开始以为MySql对这个量是很难支持的，实际上官方说单表之前就支持4G，如果采用MyISAM存储引擎，最大表尺寸增加到了65536TB。一句话，3G是没有问题的。事实证明，确实如此。

一、关于Count的使用

这里使用了count(*)，突然好奇心下，查询了以下两条记录：

mysql> select count(name) from OR where name='张三'; 
+-------------+
| count(name) |
+-------------+
|         321 |
+-------------+
1 row in set (36.61 sec)

mysql> select count(*) from OR where name='张三';    
+----------+
| count(*) |
+----------+
|      321 |
+----------+
1 row in set (35.90 sec)

mysql> select count(1) from OR where name='张三'; 
+----------+
| count(1) |
+----------+
|      321 |
+----------+
1 row in set (35.65 sec)

从以上查询结果可以看出，论速度count(1) > count(*) > count(name)，注意，这里前提是没有任何主键和索引。并且这个速度差体现的也并非如何明显，不到1秒钟。还不足以让我们为此舌战。

二、加入索引

对于上面查询，一下子就查了37秒，表没有做任何优化，显然速度还是可以大大提高的。要想提高查询速度，建立索引是最快最基本的方法啦，建立索引如下：

create index idx_open_room_name on OR (name);

建立索引之后再重新执行上面那些查询，速度提升了不是几倍。如下：

mysql> select count(name) from OR where name='张三';   
+-------------+
| count(name) |
+-------------+
|         321 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from OR;                   
+----------+
| count(*) |
+----------+
| 20051420 |
+----------+
1 row in set (1 min 8.92 sec)

可见对于索引字段的相关操作，效率会大大优化。

三、检测慢查询

对于MySql中的慢查询，MySql是有一套成熟的监控日志的。可以自己配置慢查询监控日志，修改/etc/my.cnf，添加如下配置：

log-slow-queries = /data/mysql/logs/sql_slow.log       /*指定查询日志路径*/
long_query_time = 1                                    /*设定慢查询时间阀值：1S*/
log-queries-not-using-indexes

重启MySql服务，即可在对应的日志路径下找到慢查询SQL。另外，MySql对应的慢查询分析软件很多，比如mysqldumpslow等等。大家都可以看看。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

数据库