SQL语句的基本优化，mysqlsla工具使用

一个有梦想的咸鱼
1,300 阅读6分钟

记录一些常用的SQL优化方式 和 mysqlsla 慢日志工具分析的基本操作，供自己复习

用到工具：

mysql-5.6.46
mysqlsla-2.03
navicat
Xshell

sql基本优化

添加索引

索引是必备的，不解释。

创建索引
create index index_name on table_name(列);

删除索引
DROP INDEX index_name ON table_name;

查看现有索引：

show index from 表;

explain命令

用于查看sql执行时是否使用了索引

语法：
explain 操作语句

explain详细参数说明：

参考 www.cnblogs.com/hailexuexi/…

是否需要优化主要看type 和 Extra这两列， Extra 感觉这篇文章说的更详细一些
www.cnblogs.com/linjiqin/p/…

查看索引使用情况

show status like 'Handler_read%';

Handler_read_key: 越高越好

Handler_read_rnd_next:越低越好

查询条件是否走索引的几种情况

  1. in走索引
  2. 模糊查询百分号在右边走索引,左右两侧都有或者左侧有都不走索引
  3. 范围查询走索引
  4. !=、<>、not null、is not null 这些反向条件都不走索引
  5. 条件计算或者调用函数不走索引，比如 where id/10=2 或者 where time<now()
  6. 使用 or 时 A or B 必须A和B两个字段都存在索引才会走索引
  7. 使用union\union all 代替or,有索引的就会使用索引。但是union使用了临时表，相对union all效率会差一些
  8. 字符串类型必须加引号才走索引
  9. order by 排序
    (1)A和B都有索引，where A='1' order by A asc 是走索引的，如果去除条件A='1' 直接排序是不走索引的
    (2)使用group by 进行分组统计时可以加上order by null,关闭排序功能
  10. exists 和 in 的用法：
    一条sql，select * from A where id in(select a_id from B > 100)
    当表A的数量大于表B数量时，使用in可减少查询时间。如果相同情况下使用exists则查询时间增加。
    sql: select * from A where id exists(select a_id from B > 100)
    两句 sql 分别执行 explain 可看出 rows 这一行数量 in 小于 exists，rows代表需要检查的行数，行数当然越少越好。 所以当表A的数量小于表B数量时，使用exists

一些查看mysql状态的操作

查看mysql版本

select version();

查看 mysql 的增删改查是以什么操作为主

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

这里记录了增删改查的次数，可以判断哪种操作居多
这里执行了一下 show status like 'com_select';

记录了查询的次数

数据库启动运行的时间

show status like 'uptime';

数据库连接次数

show status like 'connections';

也可以查询全部状态（但是参数太多了我看不懂也记不住，淦！(╯°Д°)╯︵ ┻━┻）

show status;

查询事务提交和回滚次数

show status like 'com_commit';
show status like 'com_rollback';

mysql慢查询

慢查询相关变量

show variables like '%slow%';

慢查询的一些参数说明：

slow_query_log=off|on --------------是否开启慢查询日志
slow_query_log_file=filename -------指定保存路径及文件名，默认为数据文件目录，hostname-slow.log
long_query_time=10 ----------------指定多少秒返回查询的结果为慢查询
long-queries-not-using-indexe -----记录所有没有使用到索引的查询语句
min_examined_row_limit=1000------记录那些由于查找了多余1000次而引发的慢查询
long-slow-admin-statements--------记录那些慢的optimize table，analyze table和alter table语句
log-slow-Slave-statements-----------记录由Slave所产生的慢查询

也可以看看官网的说明：

dev.mysql.com/doc/refman/…

mysql默认慢查询时间是10秒

show variables like 'long_query_time';

这里可以执行一句11秒的sql，然后查看一下执行了哪些慢查询

select sleep(11);

然后查询慢查询语句的次数：
show status like 'slow_queries';

觉得时间长可以改一下慢查询记录的时间：
改成3秒或者更短
set long_query_time = 3;

这属于临时设置，关闭连接之后会失效。最好在配置文件中修改

mysql的配置文件

文件位置在 /etc/my.cnf 中，慢查询的配置信息最好在配置文件中修改，然后重启mysql。

可以先查询文件的位置：
find / -name my.cnf

讲道理一般都在 /etc/my.cnf

编辑my.cnf文件，编辑之前先备份一下比较好：
vim /etc/my.cnf

如果编辑的时候出现swp文件可以按名字删除
rm -rf xxx.swp

切记：rm -rf 操作一定要加文件名，一定要加文件名，一定要加文件名。否则删库跑路不是梦。

按i 进入编辑模式

我这里用的是mysql 5.6.46 版本

查看mysql版本：
show variables like '%version%';

在[mysqld]下添加

---------------配置内容-----------------
#1表示开启 0表示关闭，也可以用ON\OFF
slow_query_log =1

#日志位置 （也自定义过其他路径，尝试了很多次，但是全部都失效，文件夹的所属也从root改成了mysql,并没有什么用。/var/lib/mysql 这个路径下可以成功写入慢查询信息，套个文件夹都会失败。没搞懂为什么，放弃。。）
slow_query_log_file=/var/lib/mysql/mysql-slow.log

#超过几秒加入慢查询记录
long_query_time=0.01
---------------配置内容-----------------

编辑完毕之后按Esc 退出 ，然后保存。
:q 不保存
:wq! 保存并退出

重启mysql:
service mysql restart

如果卡着不动，说明参数写错了位置或者是参数本身错误，所以要备份嘛~

使用 mysqlsla 分析慢查询日志

mysqlsla工具的作用：

自带的慢查询日志不太好看，体力活。这个工具是为了提取日志中的重点信息

自带的慢查询日志是这样的

用工具列出来的日志是这样的

mysqlsla下载和安装

www.jianshu.com/p/7f9b370b1…

一些操作的语法

指定数据库，提取查询最多的10条sql，直接展示 mysqlsla -lt slow -sort t_sum -sf "+select,update,INSERT" -db hard_db -top 10 /var/lib/mysql/mysql-slow.log

统计慢查询文件为/var/lib/mysql/mysql-slow.log 的数据库为mydata的所有select和update的慢查询sql，并查询次数最多的100条sql，并写到 sql_select_update.sql中去

mysqlsla -lt slow -sf "+select,update" -top 100 -sort c_sum -db mydata /var/lib/mysql/mysql-slow.log >/log/mysqlslalog/sql_select_update.log

查询记录最多的20个sql语句，并写到select.log中去 mysqlsla -lt slow --sort t_sum --top 20 /var/lib/mysql/mysql-slow.log >/log/mysqlslalog/select.log

统计慢查询文件为/var/lib/mysql/mysql-slow.log 的所有select的慢查询sql，并显示执行时间最长的100条sql，并写到 sql_select.log中去

mysqlsla -lt slow -sf "+select" -top 100 /var/lib/mysql/mysql-slow.log >/log/mysqlslalog/sql_select.log

分析出来的参数说明

Count--------------sql的执行次数及占总的slow log数量的百分比.
Time---------------执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.
Lock Time---------等待锁的时间.95% of Lock , 95%的慢sql等待锁时间.Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined---扫描的行数量.
Database----------属于哪个[数据库]
Users--------------哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract----抽象后的sql语句
Query sample-----执行的sql语句

总结

这里只记录了最基本的Sql优化和慢查询日志分析，如果感觉有帮助，麻烦点个赞，注册个账户很麻烦吗？

avatar
文章
阅读
粉丝