mysql7

最新推荐文章于 2024-04-15 17:03:54 发布

Markland_l

最新推荐文章于 2024-04-15 17:03:54 发布

阅读量131

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/Markland_l/article/details/115985613

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

mysql性能优化

总论

硬件和os调优
mysql调优
架构调优
- 读写分离
- 垂直拆分，水平拆分

慢查询

1.是否请求了不需要的数据
- 查询不需要的记录
- 总是取出全部列
- 重复查询相同的数据
2.是否在扫描额外的记录？
- 响应时间
- 扫描的行数和返回的行数
- 扫描的行数和访问类型

重构查询

一个复杂查询还是多个简单查询？
- 把复杂查询改成多个简单查询
切分查询
- 查询一口气返回10万条数据，切分成5000条每次
分解关联查询？如果出现明显的性能瓶颈，考虑把关联查询切分，如果通过关联部门查询员工，完全可以把部门表的数据查询一次缓存在本地，根据业务需求进行填充
- 让缓存的效率更高
- 可以减少锁的竞争
- 更容易做到高性能和可扩展
- 减少冗余记录的查询
- 相当于在应用中实现了哈希关联

慢查询配置

show VARIABLES like ‘slow_query_log’;
set GLOBAL slow_query_log=1;
show VARIABLES like ‘%long_query_time%’
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】

慢查询解读分析

慢查询日志

“Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
“User@Host: 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 -s r -t 10 slow-mysql.log
- -s order (c,t,l,r,at,al,ar)
  c:总次数
  t:总时间
  l:锁的时间
  r:获得的结果行数
  at,al,ar :指t,l,r平均数【例如：at = 总时间/总次数】
- -s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
- -t NUM just show the top n queries：仅显示前n条查询
- -g PATTERN grep: only consider stmts that include this string：通过grep来筛选语句。
通过上述工具可以找出慢sql语句，同时还有其他的第三方工具可以用来查找出慢sql

Explain执行计划

什么是执行计划

在SQL查询的前面加上EXPLAIN关键字就行。比如：EXPLAIN select * from order_exp;
- table：EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。
- id：查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列
- select_type：通过某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色
  - SIMPLE：简单的select 查询,不使用 union 及子查询
  - PRIMARY：最外层的 select 查询
  - UNION：UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
  - UNION RESULT：UNION 结果集
  - SUBQUERY：子查询中的第一个 select 查询,不依赖于外部查询的结果集
  - DEPENDENT UNION：UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
  - DEPENDENT SUBQUERY：子查询中的第一个 select 查询,依赖于外部查询的结果集
  - DERIVED：用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里。
  - MATERIALIZED：物化子查询
  - UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估,出现极少。
  - UNCACHEABLE UNION：UNION 中的第二个或随后的select 查询,属于不可缓存的子查询，出现极少。
- partitions：和分区表有关，一般情况下我们的查询语句的执行计划的partitions列的值都是NULL
- type：执行计划的一条记录就代表着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类型的特列，平时不会出现，这个也可以忽略不计。
    - innodb不会出现system这种级别，innodb对表中数据的统计不是精确统计，而是采用估计值，而myisam是精确值
  - const：根据主键或者唯一二级索引列与常数进行等值匹配时，这种基本是常量时间，是很快的
  - eq_ref：在连接查询时，如果驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的〈如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是eq_ref
    - select * from s1 inner join s2 on s1.id = s2.id
    - 先从s1表中查，然后再去s2表查，所以s1表是驱动表，s2是被驱动表，所以s1是eq_ref，s2是all
  - ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
    本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体
    - EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’;
    - 如果加索引的字段离散性很低，重复的数据很多，比如性别，对于这种字段，mysql要找出一般的数据进行回表，效率必然很差，
    - 如果是联合索引，遵循最左原则，假设其中有三个字段，前两个字段都是等值匹配，那么级别也是ref，但是第一个字段是等值匹配，第二个字段是大于，那么就很难是ref呢
  - fulltext 全文索引，跳过～
  - ref_or_null：有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像这个查询：SELECT * FROM order_exp WHERE idx_order_no= ‘abc’ OR idx_order_no IS NULL;
    - mysql把字段为null的排在最前面，非null的紧跟着，所以上述ref_or_null中的sql语句需要分成两个扫描区间去查询，然后再回表
  - index_merge：一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询
    - 索引
    - SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ AND order_note = ‘abc’;
      - 假设expire_time和order_note 都有索引，mysql只能从两个索引中挑选一个去B+树上查找记录，具体是先通过expire_time查找到记录，然后因为select *所以在回表，到最初的表中找到全部数据，然后再在这些记录里面判断order_note等不等与’abc’
    - 索引的作用？1.一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度。2.一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引
    - 对于这一句EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’ OR insert_time = ‘2021-03-22 18:36:47’;mysql进行了优化，原始只有这两个字段单独的所有，最后explain出来，type是index_merge，，具体索引合并的内容在后面mysql执行原理过程中
  - unique_subquery：类似于两表连接中被驱动表的eg_ref访问方法,unique _subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配。
    - EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.insert_time = s2.insert_time) OR order_no = ‘a’;其中s2表是unique_subquery，而s1是ALL
    - show warnings\G，可以看到
```
(<in_optimizer>(`mysqladv`.`s1`.`id`.<exists>)
```
  - index_subquery：index_subquery与unique_subquery类似，只不过访问⼦查询中的表时使⽤的是普通的索引
    - 把上一个例子中的where id in改成where order_no in
  - range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在你的where语句中出现了between、<、>、in等的查询
    - 范围扫描可以限制范围，肯定比全表扫描要好
  - index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index
    - EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = ‘2021-03-22 18:36:47’;
    - insert_time和order_status和expire_time组成了一个唯一索引，除了这个索引，还有insert_time和order_no这两个单独的索引，这里mysql通过这个联合索引可以找到所有的相关记录，只不过要把这个二级索引从头扫到尾，但是不用回表
    - 为什么有联合索引，还要从头扫到尾？
      
      mysql保存联合索引的时候，先按insert_time进行排序，如果相同，再按照order_status排序，如果insert_time和order_status都相同，则按expire_time排序，所有没有办法按照有序，只能全表扫
  - all：最熟悉的全表扫描，将遍历全表以找到匹配的行
    - mysql要从B+树的第一个节点的第一条记录要扫描到最后一个节点的最后一个记录
    - EXPLAIN SELECT * FROM s1;
- possible_keys与key：possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引
- key_len：key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度
  - int固定4字节
  - bigint固定8字节
  - varchar(100) 在utf-8下，是100*3 +2（可变长字段，mysql需要记录长度）= 302字节，如果列允许为null，还需要单独拿一个字节出来说明这列允许为null，所以是303字节
- ref：当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery其中之一时,ref列展示的就是与索引列作等值匹配的是谁
- rows：如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。
- filtered：查询优化器预测有多少条记录满⾜其余的搜索条件
  - EXPLAIN SELECT * FROM s1 WHERE id > 5890 AND order_note = ‘a’;
    - rows:5286 filtered:10.00%
    - 意思最后满足条件的是528条左右
  - EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_no = s2.order_no WHERE s1.order_note > ‘你好，李焕英’;
    - 先从s1表找，再去s2查
    - s1------rows:10573 filtered:33.33
      
      s2-------rows:1 filtered:100
      
      意思s2还要进行3000多次查询(上面下面相乘)
- Extra：Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息很多，几十个，无法一一介绍，挑一些平时常见的或者比较重要的额外信息
  - No tables used
    当查询语句的没有FROM子句时将会提示该额外信息。
  - Impossible WHERE
    查询语句的WHERE子句永远为FALSE时将会提示该额外信息。
  - No matching min/max row
    当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息。
  - Using index
    当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息。
  - Using index condition
    有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：
    SELECT * FROM s1 WHERE order_no > ‘z’ AND order_no LIKE ‘%a’;
    MySQL进行了改进，在查询语句的执行过程中使用了索引条件下推（Index Condition Pushdown）这个特性，在Extra列中将会显示Using index condition
  - Using where
    当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。
    - Using where表示在server层使用了where条件进行过滤，跟是否使用索引、是否回表没有任何关系
  - Using join buffer (Block Nested Loop)
    在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度。
  - Not exists
    当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息
  - Using intersect(…)、Using union(…)和Using sort_union(…)
    如果执行计划的Extra列出现了Using intersect(…)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的…表示需要进行索引合并的索引名称；如果出现了Using union(…)提示，说明准备使用Union索引合并的方式执行查询；出现了Using sort_union(…)提示，说明准备使用Sort-Union索引合并的方式执行查询。
  - Zero limit
    当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息。
  - Using filesort
    有一些情况下对结果集中的记录进行排序是可以使用到索引的
    - 出现这个说明变慢
  - Using temporary
    在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的。
    - 出现这个说明变慢
  - Start temporary, End temporary
    
    下面标黄的是mysql中半连接优化策略
    
    有子查询时，查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，①当执行策略为DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示。
    
    LooseScan
    在将In子查询转为semi-join时，②如果采用的是LooseScan执行策略，则在驱动表执行计划的Extra列就是显示LooseScan提示。
    
    FirstMatch(tbl_name)
    在将In子查询转为semi-join时，③如果采用的是FirstMatch执行策略，则在被驱动表执行计划的Extra列就是显示FirstMatch(tbl_name)提示

复习二级索引 + 回表

MMR（Disk-Sweep Multi-Range Read）
- select * from order_exp where insert_time=‘2021-03-22 18:34:56’ and order_no > ‘你好，李焕英’
- 这里有两个索引，insert_time和order_no ，mysql会统计这两个索引扫描的记录行数，哪个扫描行数少就用哪个，还有一个限制条件，对于mysql，通过索引条件找到对应的数据，然后需要回表得到完整的数据，再判断另一个条件是否满足要求

高性能的索引使用策略

不在索引列上做任何操作
尽量全值匹配
最佳左前缀法则
范围条件放最后
覆盖索引尽量用
不等于要慎用
Null/Not 有影响
Like查询要当心
字符类型加引号
OR改UNION效率高
ASC、DESC别混用
尽可能按主键顺序插入行
优化Count查询
优化limit分页

分区表

分区表的原理
分区表的类型
不建议mysql分区表

Markland_l

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql7

mysql性能优化总论硬件和os调优mysql调优架构调优读写分离垂直拆分，水平拆分慢查询1.是否请求了不需要的数据查询不需要的记录总是取出全部列重复查询相同的数据2.是否在扫描额外的记录？响应时间扫描的行数和返回的行数扫描的行数和访问类型重构查询一个复杂查询还是多个简单查询？把复杂查询改成多个简单查询切分查询查询一口气返回10万条数据，切分成5000条每次分解关联查询？如果出现明显的性能瓶颈，考虑把关联查询切分，如果通过
复制链接

扫一扫

专栏目录