我要评论慢sql
指执行时间超过预定阈值的sql，通常由long_query_time参数设定，默认为10秒。
定位
- 通过在配置文件my.cnf里的[mysqld] 下添加代码开启（重启后依然生效） 如下：
[mysqld] slow_query_log=1 //开启慢查询日志 slow_query_log_file=/var/lib/mysql/mysql-slow.log //慢查询日志存放路径 long_query_time=3 //慢查询阈值 log_output=file
通过在mysql客户端执行命令开启（重启后失效） 如下：
set global slow_query_log = ‘on'; set global slow_query_log_file = ‘/var/lib/mysql/mysql-slow.log'; set global long_query_time = 3;
分析
通过执行 在对应sql前 添加 explain后的sql 得到该sql的执行计划 如下
id select_type table partitions type possible_keys key key_len ref rows filtered extra
------ ----------- ------- ---------- ------ ------------- -------- ------- ------ ------ -------- -------------
1 simple student (null) index (null) name_age 68 (null) 10 100.00 using index
主要关注 type、key、rows、extra
type
代表此次执行该sql所使用的索引类型
索引类型的效率：
null > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all
null指该sql并不需要访问表，或者取的是索引的最大值或最小值（只需取索引叶子节点的左端或右端）
system指该sql所执行的表只有一行记录
const该sql使用的是主键索引或唯一索引，只需通过一次索引就能找到记录
eq_ref指该sql使用的是join查询，且能使用对应的索引找到唯一的记录（主键索引、唯一索引）
ref指能用非唯一索引找到唯一的一条记录
ref*_or_null *指在ref基础上索引列支持null值
index_merge指使用了一个以上的索引进行组合查询（非联合索引）
range 指使用索引列进行范围条件查询如
=, <>, >, >=, <, <=, is null, <=>, between, in
index 指使用该索引进行了全表遍历
all 指未使用索引，直接进行全表遍历
key
实际上使用的索引名称（建立索引时定义），没有用索引则为null
rows
指mysql估计该sql通过索引（走了索引时）所要读取到server层的数据行数，同一sql在不同索引下，数值越小代表索引越好
filtered
估计读取到server层后没有被过滤掉的数据行数比例 n/rows*100%
extra
mysql的索引优化信息
using index：使用覆盖索引using where：需要回表进行where条件数据过滤using index condition:使用索引下推using temporary：建立了个临时表，常见于使用了聚会函数、子查询等需要进一步对数据操作的sqlusing filesort: 对结果集进行排序，但是没有用到索引（索引失效）或该没有对应索引
索引优化
最左前缀匹配
联合索引建立时的索引顺序通过使用频率，字段区分度及范围查询（导致后面索引失效）来进行建立
如 sql:
select * from student where score=60 and finished_time >'2000-10-10'
建立索引
idx_score_finished（score,finished)
注：具体需根据业务场景进行判断
索引覆盖
在高频、少字段的sql中，考虑对字段加联合索引，实现索引覆盖
如 sql:
select name,score from student where finished_time ='2000-10-10'
建立索引
idx_finished_name_score
索引下推
对查询条件建立索引，减少回表到server层进行过滤的行数（当查询条件中存在不生效索引或不存在索引时，需要将数据传到server层进行过滤）
如 sql:
select score from student where name ='stu' and finished_time ='2000-10-10'
建立索引
idx_name_finished
表结构优化
数据类型
如果确定该列不为null（有默认值），勾选not null省去部分空间
数字类型
- 按数值范围选tinyint、int、bigint，避免空间浪费。确认数值无负数时，选择unsigned
- double类型的计算存在精度丢失问题，可改成int类型，小数在业务层进行处理，也可使用decimal
字符类型
- 避免使用text类型
- 在定长字符或长度差距不大的字段时，选用char能够节省空间（省去varchar的len部分）
- varchar的长度根据业务需求选择，太长会导致行数据读入内存时消耗过大（相比同长度但不同varchar类型长度的数据）
日期类型
- 对范围（1970-2038）无要求，使用timestamp，同精度存储大小为datetime的一半
- 只需要保存到日，可以用datetime，只需3字节
字符编码
如果对字符要求不高，可以不选utf等有冗余的编码方式，按需选字符集
注：有联表关系的表的表间关联键的字符集应保持一致，避免关联时字符集不同导致索引失效
范式与反范式平衡
可以根据业务需求进行反范式设计，即添加冗余字段
如: product表 kind表 可在product表上冗余kind_name字段
大字段如text会影响数据行加载到内存页的速度（大字段会行溢出，引入磁盘io），可以拆分为表，使用关联方式读取
sql优化
- 按需select， 避免使用select*，减少网络io带宽消耗
- 索引字段避免使用mysql的隐式转换，如bigint的id传int64，而不是字符串，避免索引失效
- like避免将通配符加到左侧，避免不遵守最左前缀原则导致索引失效
- 避免在索引列上使用mysql内置函数（server层），导致索引失效
- 避免对索引列进行运算
- 对索引列等值查询时，使用= 而不是<>
- 多表操作如join，in时使用小表驱动大表的sql编写，减少关联数据量
- 批量操作在业务层进行分批，sql中进行批量执行，避免并发数据插入带来的锁竞争
- 使用limit减少每次回传的数据行数以及数据库回表行数，减少网络io及sql执行时间
- 深分页使用延时分页优化：将符合条件的id limit查出来后再去主表联表，省去二次回表
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论