mysql6

mysql性能优化

总论

调优金字塔

• 硬件和os调优
  • 硬件用几级的磁盘阵列（从0到10），一般来说用RAID 5
  • 当使用多磁盘后，能不能把mysql的数据文件分散到不同的磁盘上
  • 能不能用裸设备存放mysql文件
    • 裸设备：又叫裸分区，没有经过格式化，无法通过操作系统文件系统读取，而是直接由应用程序读取
  • 文件系统用什么？
    • 在windows下面用ntfx
    • 在linux下面又ext3、ext4
    • 比较适合mysql的有xfs
  • 操作系统的磁盘调度算法
    • 在linux环境下用deadline，在linux2.68版本后默认CFQ算法，但是CFQ算法不利于mysql的性能，真正优秀的dba会改成deadline算法的
• mysql调优
• 架构调优

变化趋势

• 从下往上成本越来越高，从上往下效果越来越好
• 1.所以最先开始应该考虑的是架构调优，如果架构设计的不好，dba能够做的优化也很有限
  • 有些业务类型mysql并不是适合，可以考虑放到大数据仓库，或者搜索引擎、或者缓存去做
  • 如果写并发量多大，考虑分布式，如果读压力大，考虑读写分离
  • 所以架构调优作为调优金字塔的底部，在上线后，无论是sql语句、硬件、还是各种参数定义方面，单台mysql服务器能够提供的扩展性基本定型了，性能很难再去提升了，但是如果架构设计的好，性能可以有几百倍，乃至几千倍的提高
• 2.对于mysql调优
  • 数据表设计是否合理
  • sql语句优化是否足够，该添加的索引是否都添加了
• 3.对于硬件和os调优

慢查询

慢查询定义及作用

• 慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

慢查询基础-优化数据访问

• 大部分查询慢的sql都是查询数据太多，想办法减少查询数量

请求了不需要的数据？

• 查询不需要的记录
  • 比如查询了1000条，但是业务中只使用到了10条，其余990条都是白查的
  • limit 10000,20，此时就是查询了10020列，然后不要前10000条，只取20条，白扫描了10000条
• 总是取出全部列
  • 最常见的就是select *，即使建立了二级索引，只要查询不是走主键，就一定要回表的， 回表是一个典型的随机io操作，性能很差，但是并不是绝对不能用select *，比如提前查出所有的数据，然后放到缓存中，这种是为了提高查询性能，是可以的
• 重复查询相同的数据
  • 比如查询部门表，在一个业务中反复查，既然要反复查，而且部分表本身就是不常变的内容，为什么不把它缓存起来?

是否在扫描额外的记录？

• 导致响应时间增长
  • 你写的sql语句本意只查1000条语句，但是sql写的不好，导致必须扫描10w条数据才能找出这1000条来，而在扫描这10w条的数据的时候，有可能会出现磁盘的io，同时也有可能锁表，这些最后就反应成响应时间的增长
• 扫描的行数和返回的行数
  • 扫描的行数很多，但是结果就返回几行数据
  • 特别是在关联查询的时候，mysql需要扫描很多行，才能形成返回记录
  • 发现扫描的行数和返回的行数相差很大时，一般是使用覆盖索引，二可以改变库表结构，建立单独的汇总表，第三个可以重写这条查询语句
• 扫描的行数和访问类型
  • 同样是查找数据，使用不同的索引会产生不同的结果，有的索引需要回表，而覆盖索引和主键索引可以直接返回数据

带索引的查询

• select * from where a>xx
• 不同的where条件情况
  • 1.在存储引擎层就能通过where条件过滤掉不匹配的记录，这样的where条件是最好的
    • 类比之前提到的三星索引，就是希望在存储引擎层就能使扫描行数越少越好，把不需要扫描的行都过滤掉
  • 2.where条件使用覆盖索引是第二好的，不用回表，这是在servcice层做的
    • Using index表示使用了覆盖索引
  • 3.最坏的是需要回表的，回表后从聚簇索引中找出符合的数据，再过滤where条件，此时多了一步回表的过程，
    • Using where表示进行了回表

重构查询的方式

一个复杂查询还是多个简单查询？

• 一个查询很慢，多半情况下是这个查询很复杂，能不能把这个复杂查询改成多个简单查询

切分查询

• 限制返回的数据量，总共查询可以返回10w条数据，分多次返回，每次只返回1000

分解关联查询？

• 分解关联查询并不是说数据库中不能有关联查询，需要根据查询性能而定，如果性能还可以没必要分解

• 比如把5个关联查询分解成2个关联查询或者3个关联查询

怎么分解关联查询

• 让缓存的效率更高
  • 比如某个关联只是关联一个部门名称，这种数据量不多并且不经常变化的量，把这些数据缓存起来
• 可以减少锁的竞争
  • 把一个关联查询分解以后还可以减少锁的竞争，因为当一个关联查询持续的时间很久，持有锁的时间也会变得很长
• 更容易做到高性能和可扩展
  • 将关联查询拆分成单表后，也更容易做扩展，像分库分表
• 减少冗余记录的查询
• 相当于在应用中实现了哈希关联

慢查询配置

• // 观察慢查询有没有开启
  show VARIABLES like 'slow_query_log';
  // 启动慢查询日志
  set GLOBAL slow_query_log=1;
  // 慢查询阈值，默认是10秒
  show VARIABLES like '%long_query_time%'
  // sql语句没有使用索引的都会记录在慢查询日志里面
  show VARIABLES like '%log_queries_not_using_indexes%'
  // 慢查询记录位置，默认是输出到文件
  set global log_output='FILE,TABLE'
  
  

总结

• slow_query_log 启动停止技术慢查询日志
  slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
  long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
  log_queries_not_using_indexes  是否记录未使用索引的SQL
  log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】 
  

启动慢查询

慢查询日志观察

• mysql查询日志存放位置：
  • /home/mysql/data/主机名-slow.log
• 直接查看日志中的数据很不方便，mysql提供了一个工具来查看
  • /home/mysql/bin/mysqldumpslow
  • mysqldumpslow专门用来帮我们对慢查询日志进行分析的

慢查询解读分析

• “Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
  “[email protected]: root[root] @ localhost []  Id:     3”：用户名 、用户的IP信息、线程ID号
  “Query_time: 0.000495”：执行花费的时长【单位：毫秒】
  “Lock_time: 0.000170”：执行获得锁的时长
  “Rows_sent”：获得的结果行数
  “Rows_examined”：扫描的数据行数
  “SET timestamp”：这SQL执行的具体时间
   最后一行：执行的SQL语句
  

mysqldumpslow

• mysqldumpslow --help

  • 查看命令解释

• mysqldumpslow -s 是进行排序

• mysqldumpslow -t 是限制查询结果的条数

• mysqldumpslow -g是通过grep来删选语句

• 综合使用

  • mysqldumpslow -s t -t 10 /home/mysql57/data/主机名-slow.log
    • 对慢查询日志中花费时间最多的前10条进行排序并显示
  • mysqldumpslow -s t -t 10 /home/mysql57/data/主机名-slow.log -g select
    • 对上面的进一步过滤，只显示select相关的

pt_query_digest

• mysql高性能优化第三版中提到的一个工具
• 是用python编写的
• 分析结果要比mysqldumpslow更全

Explain执行计划

什么是执行计划

执行计划的语法

• 在SQL查询的前面加上EXPLAIN关键字就行。比如：EXPLAIN select * from order_exp;

  • id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
    1	SIMPLE	order_exp	NULL	ALL	NULL	NULL	NULL	NULL	NULL	100.00	NULL

table

• EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

id

• 查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列

  • EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’;

    • id = 1

  • EXPLAIN SELECT * FROM s1 INNER JOIN s2;

    • 两个表的id都等于1

  • EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = ‘a’;

    • 这里的子查询转换成了关联查询–通过命令show warnings\G可以观察到mysql优化器优化后的sql

    • s1表的id等于1，s2表的id等于2

  • EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = ‘a’);

    • 子查询不算额外的

    • 两个表的id都等于1

  • EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

    • s1表的id等于1，s2表的id等于2，还多了一个id为null的行，这里会创建一个临时表，用来合并两个表的结果的

  • EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

    • s1表的id等于1，s2表的id等于2，用union all，就不会有临时表了，因为union all不用去重，直接合并就可以了

select_type

• 通过某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色

  • 上面id中有两个不同id的查询，他们的select_type也是不一样的，select_type能体现两者之间的关联关系

• SIMPLE：简单的select 查询,不使用 union 及子查询，即使是关联查询，也是两个id都等于1，同时select_type都是SIMPLE
  
  PRIMARY：最外层的 select 查询，union查询中，最外面的查询称为primary
  
  UNION：UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
  
  UNION RESULT：UNION 结果集，union查询中用于去重的临时表
  
  SUBQUERY：子查询中的第一个 select 查询，不相关的子查询,不依赖于外部查询的结果集，不能semi-join（semi-join：半连接的优化技术，本质上是一个查询上拉，把子查询的查询条件上拉到父查询里面），使用物化表的方式来执行这个查询时，这个子查询前面出现的就是SUBQUERY（物化表：物化的含义指的是mysql在执行这个查询的时候，发现可能子查询的内容有点大，需要把子查询的内容缓存一下，缓存在内存或者临时表里面）
  
  DEPENDENT UNION：UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
  
  DEPENDENT SUBQUERY：子查询中的第一个 select 查询,依赖于外部查询的结果集
  
  DERIVED：用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。DERIVED是对于派生表而言的，是对派生表的物化，并不是实际存在的，mysql会把这张表物化
  
  MATERIALIZED：物化子查询，MATERIALIZED是对子查询表的物化
  
  UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估,出现极少。(全局变量是不能被缓存的)
  
  UNCACHEABLE UNION：UNION 中的第二个或随后的select 查询,属于不可缓存的子查询，出现极少。
  

partitions

• 和分区表有关，一般情况下我们的查询语句的执行计划的partitions列的值都是NULL
  • 如果没有分区的话，就是null

type

• explain后很重要的观察指标

• 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的type列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：

• system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• 出现比较多的是system>const>eq_ref>ref>range>index>ALL

• 一般来说，得保证查询至少达到range级别，最好能达到ref。

system

• 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
  • explain select * from test
    • 因为innodb引擎里面统计数据是不精确的，而myisam和memory这张存储引擎它的统计数据是精确的

const

• 根据主键或者唯一二级索引列与常数进行等值匹配时

  • EXPLAIN SELECT * FROM s1 WHERE id = 716;

• 由于是等值匹配，算上二级索引查到主键索引回表的时间，就是两个常量时间，其结果仍然是常量时间

• 对于联合索引，只要每个索引列都有一个等值匹配，这样的实现也是const级别的，但是当有一个字段是允许null时，是达不到const级别的，

  • 因为mysql在存储时，认为每一个null都是独一无二的，在每一个b+树的叶子节点里面，前面的记录全部用来存null值，null值存完了再开始存非null值的
  • 当索引里面有null值，由于无法确定null值有多少个，所以就达不到const级别，所以尽量不要把列定义为允许null值，这样不光是在数据存储（优先存储在最前面），还是索引扫描（不能达到const级别），null值都会引入很多的麻烦
  • 为什么要把null定义为独一无二的？mysql对null值有三种定义，第一种是独一无二的，第二种认为null值都是相等的，第三种是完全忽略null值，所以mysql本身对null值的处理就很分裂