在如今的互联网时代,随着业务扩展,大数据量迸发,数据库必须进行性能优化。谈到mysql优化,相信很多读者都会感到很大,很远,以至于无从下手,而且mysql优化也是面试官必问题之一,本系列文章将会列出MySQL优化的几大常用步骤。
通过本章,你将学到数据库设计范式,MySQL的慢查询设置,慢查询定位分析,以及慢查询工具mysqldumpslow的使用。
表结构设计合理化,即符合3NF(三范式)
所谓数据库范式是指设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
第一范式(1NF):要求表的列具有原子性,不可再分解,一般关系型数据库都满足1NF。
第二范式(2NF):是在满足第一范式的基础上,表中每条记录或实例被唯一区分,通常可以设计一个主键来区分(该主键不能包含业务逻辑)。
第三范式(3NF):是在满足第二范式的基础上,数据库表中不包含已在其他表中已存在的非主键字段,即我们平常所说的字段冗余。
此处提一下,有时为了提高查询效率,会降低范式标准,适当的保留冗余数据,没有冗余的数据库未必是最优的数据库,需要视自己业务情况而定。
定位慢查询
在默认情况下,MySQL在10秒内没有响应结果,则视为慢查询,当然,我们也可以手动修改慢查询时间。
首先,连接到mysql中:mysql -uroot -p;在输入密码即可。其中root是用户名。
查询慢查询的次数:show status like 'slow_queries';
查询数据库连接数:show status like 'connections';
查询慢查询时间:
show variables like 'long_query_time';
修改慢查询时间,命令行设置,会立即生效,但重启mysql后会失效,即还是my.ini中的值:
set global long_query_time=5;
查询慢查询是否开启,以及慢查询存放日志的路径:
show variables like 'slow_query_log%';
上图默认开关状态是OFF,可通过如下命令打开:
set global slow_query_log=on;
慢查询日志存放的地址也可以自行设置:set global slow_query_log_file='/xx/oo.log'
根据慢查询日志,可定位到具体慢查询sql语句,从而进行sql优化,而生产环境日志较多,可以采用mysqldumpslow工具进行日志分析,再查看分析。
首先退出数据库,可以查看工具帮助:mysqldumpslow --help;
输出一大堆,总结下来如下:
-s, 是表示按照何种方式排序,可以跟下面参数
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
EXAMPLE:
得到返回记录集最多的20个SQL
mysqldumpslow -s r -t 20 /var/lib/mysql/xx_slow.log
得到访问次数最多的20个SQL,并分页。
mysqldumpslow -s c -t 20 /var/lib/mysql/xx_slow.log | more
得到时间排序的前20条里面含有group by的查询语句。
mysqldumpslow -s t -t 20 -g “group by” /database/mysql/mysql06_slow.log