SQL万能优化思路

1、SQL万能调优步骤

注：本文是基于MySQL5.7.38版本做的所有测试

2、常用SQL介绍

1、查看MySQL服务器信息

show status

查看mysql服务器状态信息，可以有如下用法：

显示数据库的查询，更新，添加，删除的次数

show status like 'com_select';
show status like 'com_update';
show status like 'com_insert';
show status like 'com_delete';

显示mysql数据库的连接数

show status like 'connections';

显示慢查询次数

show status like 'slow_queries';

查看mysql数据库启动了多少时间

show status like 'uptime';

2、查看MySQL版本

select VERSION()

3、慢查询日志

概念：

1. 慢查询日志是用来记录执行时间超过指定时间的查询语句。
2. 通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。

1、设置慢查询日志

方式1：全局变量设置

该方式数据库重启全部失效，得重新配置

慢查询日志的打开正常情况下，只需要在配置文件中增加slow_query_log = 1配置，即打开慢查询日志，未指定slow_query_log_file的情况下，会自动生成一个以主机名+‘slow’.log 的文件，如下SQL，可以查询是否开启慢查询日志：

show variables like '%slow_query_log%';

执行结果：

慢查询日志存放路径：

ON开启，OFF关闭，这里默认是开启的，真实生产环境最好不要打开，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响

那如果是关闭的，我们有时候需要打开，去定位一下哪些查询是比较慢的SQL，怎么开启呢？

执行下面SQL即可开启慢查询日志功能：

set global slow_query_log='ON';

还可以设置慢查询日志文件存放路径：

set global slow_query_log_file='D:\\mysql\\data\\slow_sql.log';

然后再执行：show variables like ‘%slow_query%’;就会发现已经开启了，如下：

查询慢查询日志的时间（这个时间的含义是：SQL执行多长时间会被MySQL认为是慢查询，默认是10，单位：秒）

show variables like 'long_query_time';

执行结果：

修改慢查询时间为3秒，也就是说执行查询操作时，超过3秒的SQL，就会被MySQL定位为慢查询了（如果有时候用命令不起作用，那么可以关闭再打开）

set global long_query_time=3;//针对当前窗口失效，再开一个窗口就有效了
set long_query_time=3;//针对当前窗口有效

方式2：配置文件设置

服务器重启不影响

第一步：

MySQL配置文件my.ini中新增下面配置

[mysqld]
slow_query_log = ON
slow_query_log_file = D:\\mysql\\data\\slow_sql.log
long_query_time = 3

第二步：

重启MySQL服务：

service mysqld restart

2、实战

建表

t_user建表语句：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(100) DEFAULT NULL COMMENT '姓名',
  `sex` varchar(2) DEFAULT NULL COMMENT '性别',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `phone` varchar(12) DEFAULT NULL COMMENT '手机号',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `deptid` int(11) DEFAULT NULL COMMENT '归属部门ID',
  `udesc` varchar(255) DEFAULT NULL COMMENT '个人描述',
  `createtime` datetime DEFAULT NULL COMMENT '创建时间',
  `school` varchar(255) DEFAULT NULL COMMENT '毕业院校',
  `major` varchar(255) DEFAULT NULL COMMENT '专业名称',
  `nationality` varchar(255) DEFAULT NULL COMMENT '国籍',
  `nation` varchar(255) DEFAULT NULL COMMENT '民族',
  `idcard` varchar(255) DEFAULT