MySQL优化SQL思路

一、实现功能

MySQL数据库项目中使用，排除项目初期，项目运行中肯定会出现各种慢查询的操作，需要优化。本文结合实际项目经验，总结下SQL调优的思路。

二、实现步骤

1.定位需要优化SQL思路

1.业务慢：打时间日志确认耗时的业务点（大多是由于SQL导致的）

2.MYSQL慢查询日志：慢查询日志，给where条件字段添加索引。

2.1查询mysql慢查询是否开启相关配置

#查看慢查询是否开启，以及日志
root@10.7.221.25 [(none)]> show variables like 'slow_query%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | ON                                  |
| slow_query_log_file | /var/lib/mysql/cloudera-slow.log |
+---------------------+-------------------------------------+
2 rows in set (0.00 sec)

#查看慢查询设置临界时间
root@10.7.221.25 [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.00 sec)

2.2 配置慢查询

临时配置

默认没有开启慢查询日志记录，通过命令临时开启：

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global slow_query_log_file='/var/lib/mysql/instance-1-slow.log';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

永久配置

修改配置文件达到永久配置状态：

/etc/mysql/conf.d/mysql.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/instance-1-slow.log
long_query_time = 2

配置好后，重新启动 MySQL 即可。

2.3 查询慢查询日志：查看慢查询语句

[root@cloudera-02 ~]# cat /var/lib/mysql/cloudera-02-slow.log 
/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2020-04-09T08:32:36.003677Z
# User@Host: root[root] @  [172.17.1.11]  Id:   169
# Query_time: 0.100692  Lock_time: 0.000174 Rows_sent: 0  Rows_examined: 1
use infodt;
SET timestamp=1586421156;
DELETE FROM `bd_batch_file` WHERE `FILE_ID` = '581d8854b9e249ac9d2195f775cf0478';
/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

3.使用：explain确认执行行数

进而对需要的where条件字段添加索引

4.使用：force 强制指定索引

一个很好的实例 ：利用 force index优化sql语句性能

因为SQL会优化选择认为比较好的索引，但是，实际上可能并不是，所以，需要手动指定需要索引

三、总结

SQL多坑，一路走一路填。

四、参考

1.MySQL 慢查询日志

2.分享：详记一次MySQL千万级大表优化过程！

3.MySQL如何使用索引