mysql优化慢查询

mysql优化慢查询

背景：

最近mysql数据库因为某张表的数据量越来越大，导致数据库的查询速度越来越慢，慢查询越来越多，为了解决这个问题，决定将此表(table1_name)备份到历史备份表(table2_name)中

分析：

一、查看慢查询次数

2、mysqldumpslow慢日志分析工具查找都是哪些sql慢查询操作
经常使用的参数：
-s，是order的顺序
al 平均锁定时间
ar 平均返回记录时间
at 平均查询时间（默认）
c 计数
l 锁定时间
r 返回记录
t 查询时间

得到返回记录最多的20个sql

mysqldumpslow -s r -t 20 sqlslow.log

得到平均访问次数最多的20条sql

mysqldumpslow -s ar -t 20 sqlslow.log

得到平均访问次数最多,并且里面含有ttt字符的20条sql

mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log

解决办法

一、备份历史表数据

$ mysqldump -uuser -ppasswd --add-drop-table --force dbname table2_name > /data/services/mysql_bak/table2_name.sql

二、清空历史表并且释放表空间

mysql> use dbname;
mysql> truncate table table2_name;
mysql> OPTIMIZE TABLE  table2_name;   ##释放表空间；显示不支持，实际上已进行重建和分析，空间已经回收
mysql> show table status like 'table2_name' \G    ##查看表空间状态

三、备份数据表到历史表

##1、根据创建时间条件查询每个月的表数据量
mysql> SELECT * FROM table1_name WHERE DATE_FORMAT(create_date,'%Y-%m') = '2018-10' ;
mysql> SELECT COUNT(1) FROM table1_name WHERE DATE_FORMAT(create_date,'%Y-%m') = '2018-10' ;
mysql> SELECT COUNT(1) FROM table1_name WHERE DATE_FORMAT(create_date,'%Y-%m') = '2018-11' ;
mysql> SELECT COUNT(1) FROM table1_name;
##2、按每月备份数据到历史表
mysql> INSERT INTO table2_name seletct * FROM table1_name WHERE DATE_FORMAT(create_date,'%Y-%m') = '2018-10' ;
mysql> INSERT INTO table2_name seletct * FROM table1_name WHERE DATE_FORMAT(create_date,'%Y-%m') = '2018-11' ;
##3、验证备份是否成功
mysql> SELECT COUNT(1) FROM table2_name;
##4、清空数据表
mysql> delete from table1_name WHERE DATE_FORMAT(create_date,'%Y-%m') = '2018-10' ;
mysql> delete from table1_name WHERE DATE_FORMAT(create_date,'%Y-%m') = '2018-11' ;

好了，这就是msyql优化慢查询的方法了，当然，优化慢查询还有许多方法，包括建立索引等等，视情况而定，如有问题可与博主一起交流讨论！