MySQL之SQL优化

最新推荐文章于 2024-06-09 22:32:32 发布

程序员翟同学

最新推荐文章于 2024-06-09 22:32:32 发布

阅读量100

点赞数

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

本文链接：https://blog.csdn.net/qq_36986015/article/details/105140454

版权

MySQL 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

1. SQL优化的步骤

发现有性能问题的SQL；
分析SQL执行计划；
优化索引；
改写SQL；

2. 发现有性能问题的SQL

分析慢查询日志发现存在性能问题的SQL；
- 配置慢查询日志：SET GLOBAL slow_query_log = ON
- 指定慢查询日志位置：SET GOLBAL slow_query_log_file = /slow_query_log/slow_query_log.log
- 设定最长查询时间：SET GLOBAL long_query_time = x秒，如果等于0，则把所有SQL都写入日志中
- 把没有使用索引的SQL都记录到日志中：SET GLOBAL long_queries_not_using_indexes = ON
- 常用的慢查询日志工具：
```
 1、mysqldumpslow：MySQL官方提供的慢查询日志分析工具，可以汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。
 2、pt-query-digest：用于分析 MySQL 慢查询的一个工具。
```
数据库实时监控长时间运行的SQL：获取查询时间超过0秒的SQL
- SELECT * FROM information_schema.PROCESSLIST WHERE TIME >= 0;

3. 分析SQL执行计划

了解SQL如何访问表中的数据；
了解SQL如何使用表中的索引；
了解SQL所使用的查询类型；
执行计划内容分析：

id：查询执行的顺序，id相同时由上到下执行；id不同时，由大到小执行；

`EXPLAIN SELECT * FROM employee WHERE gender = '男'`

执行计划如下：在这里插入图片描述

EXPLAIN SELECT * FROM USER u LEFT JOIN user_role ur ON u.id = ur.uid;

上面的SQL的执行计划如下：
在这里插入图片描述
id相同的情况下，SQL由上至下开始执行，整个执行过程以u表为基础进行的；

EXPLAIN SELECT * FROM USER u WHERE u.id NOT IN (SELECT uid FROM user_role)

上面的SQL的执行计划如下：
在这里插入图片描述
可以看出，id为2的语句先执行，然后再执行id为1的语句。

select_type：表示查询类型

值	含义
SIMPLE	简单查询，不含有子查询或者UNION操作的查询
PRIMARY	查询中如果包含子查询，那么最外层的查询则被标记位PRIMARY
SUBQUERY	SELECT列表中的子查询
DEPENDENT SUBQUERY	被依赖的子查询

table：表示执行结果的数据由哪个表输出的

 -  <unionM,N>由id为M，N查询union产生的结果集；
 -  <derived N>或<subquery N>由id为N的查询产生的结果集；

partitions：对于分区表，显示查询的分区id，对于非分区表，显示为null
type：查询的方式
- ALL是全表扫描
- index表示全表索引扫描，遍历的是索引树
- range表示索引范围扫描，常见于between > <等查询条件
- eq_ref值唯一索引或主键查找，对于每一个索引，只有一条记录与之匹配，常出现使用主键或唯一索引关联join查询中
- ref表示非唯一索引查找，返回匹配某个单独值的所有行
- ref_or_null表示对于join查询，附加了对NULL值列的查询
- const表示表中有且只有一个匹配的行时，比如对主键或唯一索引的where查询
possible_key：查询中可能会用到的索引，比如：主键、唯一索引
key：查询中实际使用到的索引
key_len:实际使用索引的长度
ref：如果查询中未使用到任何索引，那么ref列的值就为null
rows：预估的扫描的行数
filtered：预估的符合条件的数据集在其所扫描的表的百分比，百分比越高，性能越好
extra：表示SQL用了哪些条件，比如using where、 distinct、not exists、using index（覆盖索引：查询的条件和结果完全不需要表扫描，都在索引上）、using temporary（使用临时表）等等

4. SQL优化的手段

优化SQL查询所涉及到的表中的索引
改写SQL已达到更好的利用索引的目的
索引的作用：告诉存储引擎如何快速找到数据
不同的存储引擎的索引底层实现是不相同的
Innodb支持的索引类型：

Btree索引
自适应hash索引
全文索引
空间索引

Btree索引的特点：

B+树的结构来存储索引数据
B+树是一个平衡二叉树
每一个叶子节点到根节点的距离是相同的
每个叶子节点之间通过指针连接，方便快速查找
Btree索引适用于全值匹配的查询
适合处理范围查找
从索引的最左侧开始匹配查找列
应该在什么列上建立索引：
- where子句中的列；
- 包含在order by、group by、distinct中的字段，索引列的顺序和order by的顺序一致；
- 多表join的关联列一定建立索引；
- 尽量选择筛选性较高的列；
如何选择复合索引的顺序？
- 区分度最高的列放在联合索引的最左侧；
- 使用最频繁的列放到联合索引的最左侧；
- 尽量把字段长度小的列放在联合索引列的最左侧；
Btree索引的限制？
- 只能从最左侧开始按索引的顺序使用索引，不能跳过索引；
- 比如：联合索引A、B、C，查询的时候只用了A和C，跳过了B，这样最后只能使用A；
- NOT IN 和< >操作无法使用索引；
- 索引列上不能使用表达式和函数；
索引使用的误区？
- 索引越多越好，索引太多会降低插入和更新的效率；
- 使用IN列表查询不会用到索引，使用OR的确不会用到索引，使用IN会用到索引，但是IN中的数据不能太多；
- 查询过滤顺序必须同索引键顺序相同才可以使用到索引，这是不对的，MySQL会自动调整索引顺序，只要你再where表达式后面用到了索引；
SQL改写的原则？
- 使用outer in代替not in ；
- 用计算列的方式巧妙的用到索引（MySQL5.7新增）；
- 拆分复杂的大SQL为多个简单的小SQL；