高性能Mysql笔记

dickrong

性能优化
　　了解查询的整个生命周期，清楚每个阶段的时间消耗情况

性能分析

慢查询日志——服务器性能分析
　　参考
　　
慢查询日志是优化很重要的手段，但是开启慢查询日志对性能的影响并不大，所以可以考虑在线上打开慢查询日志

• 查看慢查询是否打开、以及日志存储位置：show variables like '%slow%'　　
  统计当前数据库连接状态
  　　
  mysql -e 'show processlist \G' -uroot -proot | grep State | sort | uniq -c | sort -rn
  
  

剖析单条查询
　　select @@profiling：查看profiling是否打开
　　
set profiling=1：打开profiling
　　
show profiles：查看每条查询的性能

　　
show profile for query>　　
information_schema.profiling：该表存储了每个query的详细时间花费
　　
show status：查看会话级别的计数器
　　
show global status：查看全局的计数器
　　
show status where variable_name like '%handler%'：查看某些变量的计数

查询性能优化
　　查询由多个子任务组成，优化查询也就是优化子任务

• 消除一些子任务
  
• 减少子任务执行次数
  
• 让子任务执行更快
  

优化数据访问
　　不要请求不需要的数据

• 只返回必要的行（limit）、列（尽量不要使用星号返回所有列）
  
• 尽量不要查询重复的数据，使用缓存
  

是否在扫描额外的记录
　　mysql衡量查询开销的指标：

• 响应时间
  
• 扫描行数
  
• 返回的行数
  

　　访问类型
　　
explain语句中的type指明了访问类型，包括：全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用，从左到右扫描的行数从多到少，速度从慢到快
　　
查询语句中where条件的使用，性能从好到坏是：

• 在索引中使用where条件过滤不匹配的记录，这是在存储引擎层完成的
  
• 使用覆盖引擎（extra中出现using index）来返回记录，直接从索引过滤不需要的记录并返回结果，在在服务器层完成，不需要回表
  
• 在表中返回数据，使用where过滤不匹配的记录（extra中出现using where），在服务层完成。mysql需要先读数据然后过滤
  

分解复杂查询

• 切分查询：将数据量大的查询切分为几次（有些情况分析查询的性能更好，比如删除数据，每次删除10条比一次删除100条来得好，当在数据库业务繁忙的时候）
  
• 分解关联查询：
  
  
  
  • 缓存效率高：mysql中如果关联表发生了变化，缓存就失效了；而且应用程序可以缓存切分查询之后的结果
    
  • 执行单个查询减少锁竞争
    
  • 数据库表不做强关联，在应用层做，扩展性更好
    
  
  

mysql执行、优化查询的方式

mysql查询优化器的局限性
　　优化器只关心随机页面的读取

• 关联子查询：有时候可以使用join的方式重写关联子查询，效率更好
  
• union的限制：mysql不能将条件放入union各个查询中，重写的时候可以把共同的条件写入各个查询中
  
• 索引合并优化：mysql可以利用同一张表上的多个索引，explain的时候type为index_merge，key为使用到的索引。如果存在合并（and的情况）那么可以考虑将多个单列索引合为一个多列索引
  
• 等值传递
  
• 并行执行：
  
• 松散索引
  
• 哈希关联
  
• 最大值和最小值：mysql的min和max函数
  
• 在同一个表上查询和更新：mysql不允许同时对一张表查询和更新，可以使用join的方式来select需要在该表上查询的字段
  

干涉查询优化器
　　mysql提供了一些选项来干涉优化器的行为，但是建议一般情况下不要使用，因为一般干涉优化器带来的收效较小，反而给版本升级的时候带来一些问题

优化特定类型的查询

count
　　count（col）：查询该列值得个数（不包含null）
　　
count(星号)：查询行数

• myisam全表count(星号)很快
  
• 对于不精确的统计使用缓存
  

优化关联查询

• 确保on和using列上有索引，A join B on col，那么一般只需要在B的col创建索引就够了
  
• 确保group by 和order by的表达式只涉及一个表中的列，mysql才可以使用索引来优化
  

优化子查询
　　使用关联查询代替子查询，在mysql5.6和mariadb不需要考虑

优化group by和order by
　　group by的结果默认会按照分组字段进行排序，如果不需要排序可以去掉排序，指定order by NULL

优化分页查询
　　当页码比较多的时候需要扫描的数据较大，这个时候可以使用覆盖索引进行优化，先使用索引覆盖查询出limit的分页数据，然后join该表，查询其他字段，这样就减少了扫描的行数
　　

select * from user_order inner join (select order_id from user_order order by buy_date limit 50, 5) as lim on lim.order_id=user_order.order_id;　　

　　或者可以记下该分界行的标识列（该列最好有索引），比如主键id，然后查询基于该分界的记录
　　

select * from user_order where order_id > 500 order by order_id limit 5;　　

　　对于总记录数，如果不那么精确的话可以使用explain的rows

优化union查询
　　除非有消除重复行的必要，否则使用union all，因为使用union会在临时表上加distinct，导致对整个临时表做唯一性校验

使用自定义变量