MySQL

慢查询日志

• MySQL内置功能,可以记录执行超过指定时间的SQL语句
• 相关参数

log_output:日志输出地方,默认FILE(文件),设置TABLE(将日志记录到mysql.slow_log中)
    也可以同时设置,逗号隔开
long_query_time:执行时间超过该配置则记录,单位秒,可适用小数,默认10
log_queries_not_using_indexes:是否将未适用索引的SQL记录到慢查询日志中,默认OFF
log_throttle_queries_not_using_indexes:与log_queries_not_using_indexes配合使用
    限制每分钟写入的SQL数量,默认0
min_examined_row_limit:扫描行数至少达到多少记录才记录到慢查询日志,默认0
log_slow_admin_statements:是否记录管理语句,默认关闭
slow_query_log_file:指定慢查询日志文件路径,默认/var路径
log_slow_slave_statements:从数据库设置,决定是否记录在复制过程中超过long_query_time
    的SQL,如果binlog格式是row,则该参数无效,默认OFF
log_slow_extra:当log_output=FILE时,是否记录额外信息(8.0.14开始提供)
    对TABLE的结果无影响

• mysqldumpslow分析日志文件

SQL性能分析

• EXPLAIN
  • id
  • select_type:查询类型
  • table:表名
  • partitions:匹配的分区
  • type:联接类型
  • possible_keys:可能的索引选择
  • key:实际选择的索引
  • key_len:索引的长度
  • ref:被引用的索引列
  • rows:估计要扫描的行
  • filtered:符合查询条件的数据百分比
  • extra:附加信息
  • 估算查询性能:log(row_count)/log(index_block_lenght / 3 * 2 / (index_length + data_pointer_length)) + 1
• SHOW PROFILE(简单方便,已废弃)

## 查看有无profile
select @@have_profiling;
select @@profiling;
## 启用
set @@profiling = 1;
set profiling_history_size = 100;
## 查看并找到id136
show profiles ;
show profile for query 136;
## 关闭
set @@profiling = 0;

• INFORMATION_SCHEMA_PROFILING(与profile本质一样)

select @@have_profiling;
select @@profiling;
set @@profiling = 1;
set profiling_history_size = 100;
show profiles ;
select state,format(duration,6) as duration 
from information_schema.PROFILING where QUERY_ID = 261 order by SEQ;

• PERFOMANCE_SCHEMA

## 设置
update performance_schema.setup_instruments
set enabled = 'YES',timed = 'YES'
where name like '%statement/%';

update performance_schema.setup_instruments
set enabled = 'YES',timed = 'YES'
where name like '%stage/%';

update performance_schema.setup_consumers
set enabled = 'YES'
where name like '%events_statements_%';

update performance_schema.setup_consumers
set enabled = 'YES'
where name like '%events_stage%';
## 查询并找到id1164
select event_id,truncate(timer_wait/1000000000000,6) as duration,sql_text
from performance_schema.events_statements_history_long 
where sql_text like '%salaries%';
## 具体信息
select event_name as stage,truncate(timer_wait/1000000000000,6) as duration
from performance_schema.events_stages_history_long 
where nesting_event_id = 1164;

• OPTIMIZER_TRACE

## 开启OPTIMIZER_TRACE功能
set optimizer_trace = 'enabled=on',end_markers_in_json=on;
## 设置要展示的条数
set optimizer_trace_offset = -30,optimizer_trace_limit =30;
## 查询
select * from information_schema.OPTIMIZER_TRACE limit 30;

常用诊断命令

• SHOW PROCESSLIST(查看当前正在运行的线程)
• SHOW STATUS(查看服务器相关信息)
• SHOW VARIABLES(查看变量)
• SHOW TABLE STATUS(查看表及视图状态)
• SHOW INDEX(查看索引信息)
• SHOW ENGINE(展示有关存储引擎的相关信息)
• SHOW MASTER STATUS(展示有关master binlog文件的相关信息)
• SHOW SLAVE STATUS(查看slave线程)

索引

• B+Tree
• Hash
• 空间索引(R-Tree)
• 全文索引

B+Tree特性

• 完全匹配
• 范围匹配
• 前缀匹配
• 限制
  • 查询条件不包括最左列,无法使用索引
  • 跳过索引中的列,则无法完全使用索引
  • 查询中某个列的范围(模糊)查询,则其右边所有列都无法使用索引

创建索引的原则

• 建议创建索引的场景
  • select语句,频繁作为where条件的字段
  • update/delete语句的where条件
  • 需要分组,排序的字段
  • distinct使用的字段
  • 字段的值有唯一性约束
  • 对于多表查询,连接字段应创建索引,且类型要保持一致
• 不建议创建索引的场景
  • where子语句里用不到的字段
  • 表的记录非常少
  • 有大量重复数据,选择性底
  • 频繁更新的字段,要考虑索引维护开销

索引失效

• 索引列不独立(列不能是表达式的一部分,也不能是函数的参数)
• 使用左模糊
• 使用or查询的部分字段没有索引
• 字符条件未使用’‘引起来
• 不符合最左前缀原则的查询
• 索引字段建议添加NOT NULL约束
  • 单列索引无法存储null值,复合索引无法存储全为null的值
  • 查询时,采用is null条件时,不能利用到索引,只能全表扫描
• 隐式转换导致索引失效

长字段索引优化

• 使用前缀索引(索引选择性越高越好:索引选择性=不重复的索引值/数据表的总记录数)
• 无法做order by,group by;无法使用覆盖索引

单列索引VS组合索引

• SQL存在多个条件,多个单列索引会使用索引合并

覆盖索引

• 对于索引x,select的字段只需要从索引就能获得,而无需到表数据里获取,这样的索引为覆盖索引

重复索引,冗余索引,未使用索引

• 重复索引:在相同的列上按照相同的顺序创建的索引
• 冗余索引:如果已经存在索引indx(a,b),又创建了index(a),那么index(a)就是index(a,b)的冗余索引
• 未使用的索引:某个索引根本未曾使用

JOIN优化

• JOIN算法-NLJ(Nested-Loop Join)
• JOIN算法-BNLJ(Block Nested-Loop Join)
• JOIN算法-BKA(Batched Key Access Join):5.6引入
• JOIN算法-HASH JOIN(用来代替BNLJ):8.0.18引入
• JOIN调优原则
  • 用小表驱动大表:一般无需考虑,关联查询优化器会自动选择最优的执行顺序
  • 如果有where语句,应当尽可能使用索引,并尽可能地减少外层循环的数据量
  • join字段尽量创建索引(字段类型要一致)
  • 尽量减少扫描的行数
  • 参与join的表不要太多
  • 如果被驱动表的join字段用不了索引,且内存充足,可以考虑将join buffer设置大一些

分页查询优化

• 覆盖索引
• 覆盖索引+join方式
• 覆盖索引+子查询
• 范围查询+limit语句
• 如果能获得起始主键值和结束主键值可以用between and
• 禁止传入过大的页码

count优化

• 直接count(*)
  • 没有非主键索引使用主键索引,存在则使用非主键索引
  • 存在多个非主键索引会使用最小的非主键索引
• count(field)
  • 只会针对该字段统计,使用该字段的索引(如果有的话)
  • 统计时会排除null值的行,count(*)不会排除
• count(1)
  • 与count(*)一样
• 创建更小的非主键索引
• 创建汇总表
• sql_calc_found_rows
  • select sql_calc_found_rows * from table limit 0,10;
  • 在做完查询后,自动执行count
  • select found_rows() as table_count
  • 8.0.17已经废弃该方法
• 缓存
• information_schema.tables(估算值)
• show table status where name = 'table'(估算值)
• explain

order by优化(利用索引避免排序)

• 排序模式1-rowid排序
  • 从表中获取满足where条件的记录
  • 对于每条记录,将记录的主键及排序键(id,order_column)取出放入sort buffer(由sort_buffer_size控制)
  • 如果sort buffer能存放所有满足条件的(id,order_column),则进行排序,否则满了之后,排序并写到临时文件
    • 快速排序
  • 若排序中产生临时文件,还需使用归并排序算法,从而保证记录有序
  • 循环执行上述过程,直到所有满足条件的记录全部参与排序
  • 扫描排序好的(id,order_column),并利用id去取select需要返回的其他字段(随机IO优化,id排序存缓存再取记录,缓存大小由read_rnd_buffer_size控制)
  • 返回结果集
• 排序模式2-全字段排序
  • 直接取出SQL中需要的多有字段,放到sort buffer
  • sort buffer排序完成后直接返回
  • max_length_for_sort_data:当order by sql中出现字段的总长度小于该值,使用全字段排序,否则使用rowid排序
• 排序模式3-打包字段排序
  • 5.7引入
  • 全字段模式的优化,将字段紧密地排列在一起,而不是使用固定长度空间
• 利用索引,防止filesort的发生
• filesort优化
  • 调大sort_buffer_siez,减少/避免临时文件,归并操作
  • 调大read_rnd_buffer_size,让一次顺序IO返回的结果更多
  • 设置合理的max_length_for_sort_data的值(一般不调整)
  • 调小max_sort_length(排序时取多少字节)

group by优化

• 松散索引扫描
  • 无需扫描满足条件的所有索引键即可返回结果
  • 查询作用在单张表上
  • group指定的所有字段要符合最左前缀原则,且没有其他字段
  • 如果存在聚合函数,只支持min()/max(),如果使用min()和max()必须在同一个字段,且该字段必须在索引中&&紧跟group by所指定的字段
  • 如果查询中除group by指定的列以外的其他部分,必须以常量形式出现
  • 索引必须索引整个字段的值,不能是前缀索引
• 紧凑索引扫描
  • 需要扫描满足条件的所有索引键才能返回结果
  • 性能比松散索引扫描差
• 临时表
  • 紧凑索引扫描也没发使用的话,会读取需要的数据,并创建一个临时表,用临时表实现group by操作
• 办法使用松散索引或紧凑索引扫描

distinct优化

• distinct是在group by操作后,每组只取1条
• 和group by优化思路一致

表结构设计

• 三范式
  • 原子性:数据库表的每个字段都是不可分割的原子数据项
  • 唯一性:满足原子性基础上,要求每行数据具有唯一性,并且非主键字段完全依赖主键字段
  • 满足唯一性的基础上,不能存在传递依赖
• 字段少而精,建议20个以内,超过可以拆分
• 尽量用小型字段
• 避免使用允许为NULL的字段
• 合理平衡范式和冗余
• 数据量非常大,考虑分库分表

工具

Percona Toolkit

• MySQL工具套件
• pt-query-digest
  • 分析日志(包括binlog,gerneral log,slowlog),processlist以及tcpdump中的查询
• pt-index-usage
  • 通过日志文件分析查询,并分析查询如何使用索引
• pt-variable-advisor
  • 分析MySQL变量,并对可能出现的问题提出建议
• pt-online-schema-change
  • 在线修改表结构,无需锁表地alter表结构