讲在前面，正在学习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
796
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
