之前看了一段时间关于MySQL优化方面的资料,虽然不是专业的DBA,但这些足以在日常工作中能够快速解决sql优化的问题。不过,发现无论什么时候,都缺少不了数据结构的出现。
1.慢查询日志
每个公司都会有自己的日志监控系统,可以通过监控系统速度定位慢查询的日志。如图:
当然,一些公司可能没有那么多的资源,可能会没有。另一中方式我也会告诉大家。
查询数据库慢查询日志是否开启
show VARIABLES LIKE '%slow%';
设置开关
set GLOBAL slow_query_log=ON;
#设置自己慢查询的时间(根据自己业务去定)
SHOW VARIABLES LIKE 'long_query_time';
#所有的查询都会记录下来
SET long_query_time=0;
查询存放慢查询日志的地方
下载pt-query-digest
浏览器输入:https://www.percona.com/get/pt-query-digest 保存脚本pt-query-digest.txt,上传到服务器上。
1.下载安装工具 #wget percona.com/get/pt-query-digest
2.授予用户执行权限 #chmod u+x pt-query-digest
3.移动位置,注意pt-query-digest安装位置不一定在/目录下,也可能在/root/目录下,根据实际情况调整
mv /pt-query-digest /usr/bin/ 或 mv /root/pt-query-digest /usr/bin/
打开slow.log
通过pt-query-digest 执行日志,找出慢查询sql
Rank Query ID 部分就是慢查询的sql记录,下面会有更详细的。
2.分析慢查询
大家熟悉的explain,先要直到执行计划的列
列 | 备注 |
table | 对应的列 |
type | 连接类型(system\const、eq_ref、ref、range、index、all) |
possible_keys | 可能使用的索引 |
key_len | 实际使用的索引 |
rows | 预计扫描行数 |
Extra | 解析查询的额外信息(usring index、usring where、useing temporary、useing filesort) |
type 列值:
值 | 备注 |
system\const | 效率会非常高 |
eq_ref | 用到了索引,效率也很高 |
ref | 用到索引,效率比较高 |
range | 性能次只,也用到索引 |
index | 需要优化 |
all | 需要优化 |
3.索引结构
B-Tree 索引结构
B-Tree索引 聚簇索引是一种存储数据结构,因为是存储引擎负责实现索引,因此不是所有的存储引擎都支持聚簇索引。InnoDB通过主键聚集数据,如果没有定义主键,InnDB会选择一个唯一的非空索引代替值。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。
主键索引下面挂载的是主键的值 (聚簇索引)
聚簇索引的优点:
可以把相关数据保存在一起,数据访问性块,覆盖索引查询只需要扫描该索引树。
非主键索引下面挂载的是主键(非聚簇索引)
MyISAM存储引擎数据存储方式是非聚簇索引
索引下挂载的是数据的行号(非聚簇索引)
总结:
- 匹配最左前缀 最左前缀要遵守
- 全职匹配 最前和中间不能断
- 匹配列前缀 索引列上少计算,范围之后全失效
- 匹配范围值 like百分写最右,覆盖索引不写*
- 精确匹配某列范围匹配另外一列 不等空值还有or,索引失效要少用
BTree索引的限制:
- 如果不是按照索引的最左列开始查找,则无法使用索引
- 不能跳过所索引中的列
- 如果查询中某一个列的范围查询,则其右边所有的列都无法使用索引优化查找。