一、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%";
查看慢查询执行时间粒度
2、MySQL常规日志开启配置
- 配置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语句 ,只取前两条