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、设置慢查询日志
方式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) DEF