讲在前面,正在学习mysql,试图做一些笔记归纳,欢迎大家来一起讨论
一、背景
- 现象
- 执行时间长
- 等待时间长
- 常见瓶颈
- CPU负担重
- IO负担重
- 数据库服务器硬件负担重
二、常见问题分析和处理
- 查询语句不合理
- 索引失效(无索引、或者有索引但失效)
- 关联查询太多(join太多)
- 数据库服务器参数调优
三、定位问题
- 开启慢查询日志
//1、查看慢查询日志配置
show variables like '%slow_query_log%';
slow_query_log 是否开启慢查询日志:ON|OFF
slow_query_log_file 慢查询日志保存路径
show variables like '%long_query_time%';
long_query_time 判断慢查询sql语句的条件,默认为10秒
show variables like '%log_output%';
log_output 日志输出方式:FILE|TABLE,默认FILE,配置为TABLE将存储到slow.log表
中,且支持双选(FILE,TABLE)
show variables like '%log_queries_not_using_indexes%';
log_queries_not_using_indexes 未使用索引的sql查询也添加到慢查询中
show global status like '%slow_queries%';
slow_queries 慢查询sql语句数量
//2、命令行配置慢查询日志(暂时生效)
set global slow_query_log=1;
set global long_query_time=3;
//3、文件配置慢查询日志(永久生效)
vim /etc/my.cnf 新增配置项reload mysql
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/localhost-slow.log
long_query_time=3
//4、慢查询分析工具,常用方法
mysqldumpslow --help
-s 表示按照某种方式排序
c 访问计数
l 锁定时间
r 返回时间
t 查询时间
al 平均锁定时间
at 平均查询时间
-t 表示top n,即取排序前多少条数据
-g 支持正则匹配模式,大小写不敏感
mysqldumpslow常用
//得到返回记录最多的10条sql
mysqldumpslow -s r -t 10 /usr/local/mysql/data/localhost-slow.log
//得到访问次数最多的10条sql
mysqldumpslow -s c -t 10 /usr/local/mysql/data/localhost-slow.log
//得到按照时间排序的前10条含有左连接的sql语句
mysqldumpslow -s t -t 10 -g 'left join' /usr/local/mysql/data/localhost-slow.log
//建议结合more使用,避免刷屏
mysqldumpslow -s r -t 20 /usr/local/mysql/data/localhost-slow.log | more
- 开启全局查询日志
//注意:全局查询日志,在生产环境中不能开启
//1、查看全局查询日志配置
show variables like '%general%';
general_log 是否开启全局查询日志:ON|OFF
general_log_file 日志保存路径
//2、命令行设置全局查询日志参数
set global general_log=1;
set global general_log_output='TABLE';
set global general_log_file='/usr/local/mysql/data/loalhost.log';
如设置output为TABLE则,全局日志被记录到mysql.general_log表中
//3、文件配置全局查询日志参数 略
- 执行show status like ‘%table%’
show status like 'table%';
table_locks_immediate 产生表级锁定次数
table_locks_waited 出现表级锁定争用而发生等待的次数
- 执行show status like ‘innodb_row_lock%’;
show status like 'innodb_row_lock%'
innodb_row_lock_current_waits 当前正在等待锁定的数量
innodb_row_lock_time 从系统启动到现在锁定总时间长度(ms)
innodb_row_lock_time_avg 每次等待所花平均时间(ms)
innodb_row_lock_time_max 从系统启动到现在等待最长一次所话时间(ms)
innodb_row_lock_time_waits 从系统启动到现在总共等待次数
-
执行explain分析(sql语句效率分析)
语法:explain sql语句
详见补(1) -
执行profile分析(sql语句全生命周期执行情况)
//1、查看profile相关配置
show variables like 'profiling%';
profiling 是否开启profile:ON|OFF
profiling_history_size 显示最近发送到服务器上执行的语句的资源使用情况,默认15条
//2、profile相关操作
//展示最近一条语句执行的详细信息
show profile;
//查看分析列表,近期执行sql语句
show profiles;
//查询指定sql语句执行详情
show profile for query 2;
//指定查询项
show profile cpu,swaps for query 2;
四、优化
- join相关
- 减少join操作
- 小表驱动大表原则:left join将小表放在左表执行全表扫描,大表放在右表并在连接字段建立索引
- 适当调整join buffer连接缓存大小
- 索引相关
- 索引全匹配(依次使用到全部索引)
- 最佳左前缀匹配法则(依次从左边到右顺序匹配部分索引)
- 索引列不做计算操作,索引列发生隐形数据类型转换导致索引失效
- 索引列使用范围查询后,导致后续索引失效
- 尽量使用索引覆盖(查询字段是索引字段的子集)
- 避免使用关键字导致索引失效(!=,<>,is null,is not null,or等)
- like通配符匹配%开头(’%abc’)导致索引失效
- order by(排序)
- 最佳左前缀匹配
- 不同字段升降序保持一致
补充mysql filesort两种方法:
1、双路排序:扫描两次磁盘,实现排序结果。第一次读取行指针和需要排序字段并进行排序,然后扫描已经排好序的数据,依次从磁盘中读取对应的数据并输出。
2、单路排序:从磁盘中读取查询所需的所有数据列,根据排序规则在buffer中进行排序,最后扫描排序后的数据直接输出。
3、对比:单路排序避免了两次IO操作,效率高。但内存空间消耗大,因为它把查询所需所有数据都读取出来,同时单路排序还需要注意调整buffer大小,否则buffer过小会导致多次IO一般大于2次。
- group by(分组)
- 分组规律为先排序后分组,所以group by优化策略同order by一致
补(1):explain详解
- id
- 参数值:数字1|2|3…
- 含义:select查询的序列号,包括一组数字,表示查询中执行select子句或者操作表的顺序
- 解析:id相同select执行顺序由上而下;id不同值越大执行优先级越高
- select_type
- 参数值:simple | primary | subquery | derived | union | union result
- 含义:select查询类型,主要用于区别普通查询、联合查询、子查询等复杂查询
- table
- 参数值:表名
- 含义:表示数据是关于哪一张表的
- type
- 参数值:system | const | eq_ref | ref | range | index | all…
- 含义:体现sql查询效率
- 解析:效率依次从高到低
- system 表中只有一行记录
- const 通过索引一次检索查到数据
- eq_ref 唯一性索引扫描,对于每一个索引键,表中只有一条记录匹配
- ref 非唯一索引扫描
- range 检索给定范围值
- index 全索引扫描
- all 全表扫描
- possible_keys
- 参数值:索引列
- 含义:可能使用到索引
- key
- 参数值:索引列
- 含义:实际使用到索引
- key_len
- 参数值:数字
- 含义:实际使用到索引列字节数,同等情况下越小效率越高
- ref
- 参数值:表名.列名|const
- 含义:表示哪些列或者常量被用于查找索引列上的值
- rows
- 参数值:数字
- 含义:根据表统计信息及索引选用情况,大致估算出找到所需记录所要读取的行数
- Extra
- 参数值:using filesort | using index …
- 含义:explain分析补充说明
- 解析:常出现字段如下
- using filesort 产生文件内排序
- using temporary 产生临时表
- using index 产生索引覆盖
- using where 使用到where
- using join buffer 使用到join buffer
- impossible where 使用where查询有误
- select tables optimized away 存在优化空间
- distinct 查询重复
补(2):练习题
待补充
mysql执行流程和索引数据结构可参考:
https://mp.weixin.qq.com/s/Vo1rOsrFL_ZMPYzqM5205Q
http://www.cnblogs.com/vianzhang/p/7922426.html