MySQL架构优化实战系列4:SQL优化步骤与常用管理命令

DBAplus社群 2016-09-02 11:11:35
一、SQL语句优化步骤
 

 

1、查看MySQL状态及配置

 

show status 查看当前连接的服务器状态

show global status 查看MySQL服务器启动以来的状态

show global variables 查看MySQL服务器配置的变量

 

  • 增删改的统计

 

查看 insert delete update select查询总数


show global status like "com_insert%"

 

 

show global status like "com_delete%"

 

 

show global status like "com_update%"

 

 

show global status like "com_select%"

 

 

  • Innodb影响行数

 

show global status like "innodb_rows%";

 

 

  • MySQL连接总次数

 

show global status like "connection%";


包括成功和不成功的连接


 

  • MySQL已经工作的秒数

 

show global status like "uptime%";

 

 

  • 查看MySQL慢查询次数

 

show global status like "%slow%";

 

 

  • 查看慢查询日志相关设置

 

show global variables like "%slow%";


log_slow_queries = on slow_query_log = on 表明慢查询日志已经开启


slow_query_log_file 慢查询日志文件的路径


 

show global variables like "%long_query%";


查看慢查询执行时间粒度

 

 

2MySQL常规日志开启配置

 

  • 配置my.conf

 

general_log = on                                                              

general_log_file = /home/mysql-run/mysql.log

 


 

表明日志已经开启。

 

3、慢查询日志开启配置

 

  • 配置my.conf

 

  • 查看慢查询日志

 

cat mysql_slow.log

 

 

4、解释执行效率较低的SQL

 

  • exiplain sql

 

或者使用desc sql

 

 

select_type : 单表查询
rows: 查询扫描的行数
key:用到的索引
key_length:用到的索引的长度
extra: using index 表示使用索引过滤掉不需要的行

 

  • 分析表索引

 

myisam 索引存放于 .MYI文件中 与数据文件.MYD 分开 myisam索引可以压缩。


Innodb表中索引和数据存放同一个文件中共享表空间。

 

更多索引相关知识请点击:https://segmentfault.com/a/1190000005087951

 

二、MySQL常用管理命令
 

 

  • 查看数据库信息

 

mysql>\s

 

 

  • 查看引擎

 

mysql> show engines;

 

 

  • 查看插件

 

mysql> show plugins;

 

 

  • 查看数据库执行进程

 

mysql> show processlist ;

 

 

三、mysqldump工具
 

 

命令位于:/usr/local/mysql/bin/mysqldump

 

 

  • 备份所有数据库

 

  • 备份库smudge

 

  • 导出库smudge 中 表cs_line

 

  • 导出库smudge 中 表cs_line line_id = 6 的数据 及表结构

 

  • 备份同时生成新的binlog文件, 使用 -F

 

  • 只导出表结构不导出数据,--no-data

 

  • 跨服务器导出导入数据

 

将128服务器 smudge库中的in_line 表 导入到 130 服务器smudge 库中 加上-C参数可以启用压缩传递

 

 

  • mysqldumpslow

 

慢查询日志分析


首先是要开启慢查询日志 请看我的文章:https://segmentfault.com/a/1190000005342547


查看慢查询日志存储位置:


 

  • 用法

 

-s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;

-t, 是top n的意思,即为返回前面多少条的数据;

-g, 后边可以写一个正则匹配模式,大小写不敏感的。

 

  • mysqldumpslow -s -r 按照返回的记录数排序

 

mysqldumpslow -s -r /home/mysql-run/mysql_slow.log

 

  • mysqldumpslow -t 1 查看前1条

 

  • 过滤 order by 慢查询

 

四、mysqlsla工具
 

 

安装完毕命令位于: /usr/local/bin/mysqlsla

 

 

默认MySQLsla 工具是不可以使用的,需要添加依赖环境

 

  • 依赖相关

 

相关:DBD-mysql-4.013.tar.gz DBI-1.608.tar.gz mysqlsla-2.03.tar.gz

 

下载地址:http://pan.baidu.com/s/1eSCvFAq

 

  • DBI的编译安装

 

  • DBD-mysql驱动模块的编译安装

 

  • mysqlsla的编译安装

 

  • 用法

 

筛选数据库smudge慢查询
 

mysqlsla -lt slow /home/mysql-run/mysql_slow.log

 

 

筛选数据库smudge慢查询 ,并排除select语句 ,只取前两条


 

 

往期回顾:

MySQL架构优化实战系列1:数据类型与索引调优全解析

MySQL架构优化实战系列2:主从复制同步与查询性能调优

MySQL架构优化实战系列3:定时计划任务与表分区

 

 
 
精选专题(点击蓝色标题可阅读全文)

◆  近期热文  ◆  

 

 

 

RTO零切换!山东移动双活容灾最佳研究和实践(上)

引以为戒:记一次心惊肉跳的服务器误删文件的恢复过程

高可用系统设计精要: 定个能达到的小目标,比如先读完本文

解DBA之惑:数据库承载能力评估及优化手段

唯品会大数据存储和计算资源管理的痛、解决方法与思路

 

◆  专家专栏  ◆  

杨志洪杨建荣陈能技丁俊卢钧轶李海翔

魏兴华邹德裕周正中高强白鳝卢飞王佩

 

◆  近期活动  ◆  

Gdevops全球敏捷运维峰会广州站


峰会官网:www.gdevops.com

活动预告