# MySQL调优
## 数据库优化常见方案
1. 优化shema,sql语句+索引
2. 加缓存,memcached,redis
3. 主从复制,读写分离
4. 垂直拆分
5. 水平拆分
为了知道怎么优化SQL,必须先清楚SQL的生命周期
## SQL生命周期
1. 应用服务器连接数据库服务器,建立一个TCP/IP连接,发送SQL请求给MySQL服务器
2. 查询缓存,有缓存则直接返回数据到应用服务器,没有则进入到SQL解析器
3. SQL解析器:匹配SQL语句,主要是解析语法是否正确,查询中的表,列名是否存在,检查表名,列名是否有歧义
4. 查询优化器:MySQL服务器自己对SQL做优化找到SQL的最佳执行方案,生成执行计划,优化的方面有索引优化(利用索引和列是否为空来优化count,min,max等聚合函数),顺序优化(重新定义表的关联关系),将外连接转换为内连接,使用等价变换,比如(1=1 and a>1)将被优化为a>1,如果索引列包含查询的所有列,则使用索引返回需要的数据,把子查询转换成关联查询,减少表的查询次数,
5. SQL执行器:判断用户权限,根据执行计划调用存储引擎接口获取数据
6. 将处理结果通过连接返回到应用服务器
![image-20230219180341134](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302191803899.png)
## 慢查询日志
在优化SQL前需要先找到需要优化的SQL,一般是通过慢查询日志来查询
### 查询是否开启慢查询日志
```sql
SHOW VARIABLES LIKE 'slow_query_log';
```
![image-20230223073602878](https://gitee.com/zhangchuan11/pic-go/raw/master/img/image-20230223073602878.png)
### 开启慢查询日志
```sql
SET GLOBAL slow_query_log = 'ON';
```
### 查询慢查询日志的路径
默认和数据文件放一起
```sql
show VARIABLES like '%slow_query_log_file%';
```
![image-20230219142030300](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302191420455.png)
### 慢查询记录时间的阈值
默认十秒
```sql
show VARIABLES like '%long_query_time%';
```
![image-20230219142051315](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302191420013.png)
### 查询是否开启记录未使用索引的SQL
```sql
show VARIABLES like '%log_queries_not_using_indexes%';
```
![image-20230223074706493](https://gitee.com/zhangchuan11/pic-go/raw/master/img/image-20230223074706493.png)
找到了需要优化的SQL,下面开始分析SQL的组成
## SQL执行计划
MySQL使用explain关键字来分析SQL,只要在SQL语句前加上一个explain关键字,就可以得到一个SQL的执行计划
```sql
explain select * from sys_user where id = 2979;
```
![image-20230219154514132](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302191545516.png)
### 执行计划字段详解
#### ID
执行顺序的标识,值越大的越优先执行,相同的值由上往下执行
#### select_type
查询语句的类型,下面是各个值
1. SIMPLE:简单的select查询,不包含任何子查询和联合查询
2. PRIMARY:主查询,如果有子查询的话,最外层的查询会被标记为PRIMARY
3. SUBQUERY:在select或where列表中包含了子查询,表示该语句属于子查询语句
4. DERIVED:生成的临时表的查询语句,也就是子查询from的一部分
5. DEPENDENT SUBQUERY:子查询中的第一个SELECT,子查询依赖于外出查询的结果
6. UNION:表示union中的第二个或后面的select 语句
7. UNION RESULT:从UNION语句中获取结果
#### table
显示这一行的数据来源于那张表
#### type
定位SQL性能因素最重要的指标,值包括system,const,eq_ref,ref,Range,index,All,性能从高到低
- System: 表只有一行记录,基本不会出现
- Const:通过索引一次就找到了数据,一般出现在使用了primary key或者unique索引匹配到了数据,匹配的条件常量(字符串,数字)
- eq_ref:使用主键索引或者非空唯一索引,在表中只有一条记录与索引键匹配,匹配条件是某个表的列(需要转义替换才能拿到的值,简单理解为关联查询)
- ref:非唯一性索引扫描,和eq_ref不同的是eq_ref匹配的是唯一索引,ref它返回所有匹配某个单独值的行,它可能会找到多个符合条件的行
- range:范围数据扫描
- index:全索引扫描,通过扫描整棵索引树来获取到的结果
- All:全表扫描
#### possible_keys
可能会用到的索引
#### Key
实际使用的索引,如果为空,表示没有使用索引
#### key_len
使用到的索引key长度,如果为联合索引则显示已命中的联合索引长度之和(如:联合索引为a+b+c,如果索引命中了a+b,那么长度就为a+b的索引长度,通过可以通过key_len来分析联合索引所命中的情况)
关于possible_keys和key的三种关系场景
possible_keys != null && key != null:正常使用到了索引的情况
possible_keys != null && key==null,这种情况说明通过索引并不能提升多少效率,一般在表的数据量很少,或者是索引的字段离散性不高,执行计划发现用索引和扫描差不多
possible_keys == null && key!= null:这种情况一般为where条件没有命中索引,但是查询的列是索引字段,也就是查询的列命中了覆盖索引的情况
#### ref
实际用到的索引是哪个表的列,const代表常量
#### row
扫描的数据行数,不是准确的值,只是估算,一般来说扫描的数据行数越少,性能越好
#### filtered
返回结果的行数占需读取行数的百分比,值越大越好
#### rows
查询的结果集大小
#### Extra
对整个SQL做概括性总结,包含使用了什么索引,排序方式
- using where:使用了where条件.
- using index:使用了覆盖索引(通常是一种好现象,意味着查询的数据直接在二级索引返回了,从而减少了回表的过程)。
- using filesort:文件排序,使用了非索引的字段进行排序(通常这种情况需要优化)。
- using index sort:使用了索引排序,通常这是一种好现象,索引天然有序,避免了通过sort buffer来排序的流程
- using temporary:使用了临时表(常见于group by,order by)
- using join buffer:使用 了join buffer缓存(这种情况关注一下查询的字段是不是没有建立索引)
- using index condition:索引下推
## SQL优化
### 优化原则
正确使用索引
### 优化查询列
尽量避免select *,改使用select 列名,避免返回多余的列。
```sql
优化前:select * from sys_suer
优化后:select id,username,nickname,mobile from sys_user
```
### 优化where子句
优化方案:避免索引失效,可能导致全表扫描的情况
1. 避免对字段进行null判断,用特殊值代替,如0
```sql
优化前:select * from sys_user where id = null
优化后:select * from sys_user where id = 0
```
2. 避免使用!=或<>操作符
```sql
优化前:select * from sys_user where dept_id <> 2;
优化后:explain select * from sys_user where dept_id > 2 union all select * from sys_user where dept_id < 2;
```
3. 避免使用or连接条件
```sql
优化前: select * from sys_user where id = 3 or id = 4;
优化后:select * from sys_user where id = 3 union all select * from sys_user where id = 2;
```
4. 避免使用参数,表达式,函数,操作
在应用层将参数转换成常量
5. 避免在where子句中的“=”左边进行函数,算术运算或者其他表达式运算
### 优化长难语句
优化方案:分解关联查询,执行单个查询,减少锁的竞争,减少冗余记录的查询
### 优化关联查询
优化方案:确定ON或者USING子句中是否有索引,确保GROUP BY和ORDER BY只有一个表中的列
### 优化子查询
优化方案:使用关联查询,优化GROUP BY和DISTINCT,这两种可以根据索引来优化,使用索引列分组效率更高,如果不需要ORDER BY进行GROUP BY时加ORDER BY NULL,mysql不会再进行文件排序
### 优化LIMIT分页
优化方案:记录上次查询的大ID,下次查询时直接根据该ID来查询因为LIMIT偏移量越大,查询效率越低,因为MySQL不是跳过偏移量,而是先把偏移量+要取出的出来,然后抛弃偏移量后再返回
```
优化前:select * from sys_user order by id desc limit 1,20
优化后:select * from sys_user where id > 1 order by id desc limit 20
```
### 优化 UNION查询
优化方案: UNION ALL的效率高于UNION
### like语句优化
```
优化前:select * from sys_user where username like "%ws%"
优化后:select * from sys_user where username like "ws%"
```
优化后符合最左前缀原则,会走索引,第一种会索引失效
## 索引优化
### 分类
- 功能上分类:普通索引(NORMAL),唯一索引(UNIQUE),主键索引(PRIMARY KEY),全文索引(FULLTEXT)
- 实现方式分类:聚簇索引(主键属于聚簇索引),非聚簇索引
- 字段个数分类:单列索引,多列索引(联合索引,覆盖索引)
### 原则
1. 左前缀原则,mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配
2. 频繁作为查询条件的字段适合创建索引
3. 频繁更新的字段不适合创建索引
4. 尽量扩展索引,不要新建索引,一个联合索引比多个单个索引效率更高
### 建议
1. 尽量使用自增主键
2. 索引字段越小越好,因为查询索引的时候需要把索引列转换成一个关键字来查询,字段越小,转换的时间越短
3. 索引不要超过6个
4. 删除冗余和无效的索引
5. 尽量使用数字型字段
6. 非空字段应该指定列为NOTNULL,在mysql中,含有空值的列很难进行查询优化,因为他使得索引的统计信息变得更加复杂,应该用0或者一个特殊的值来代替空值
7. 将离散大的字段(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查询字段的差异值,返回值越大说明字段的离散程度越高
### 索引案例
```sql
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
`nickname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵称',
`gender` tinyint(1) NULL DEFAULT 1 COMMENT '性别((1:男;2:女))',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
`dept_id` int NULL DEFAULT NULL COMMENT '部门ID',
`avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用户头像',
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系方式',
`status` tinyint(1) NULL DEFAULT 1 COMMENT '用户状态((1:正常;0:禁用))',
`email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户邮箱',
`deleted` tinyint(1) NULL DEFAULT 0 COMMENT '逻辑删除标识(0:未删除;1:已删除)',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `sys`(`username`, `dept_id`, `nickname`, `deleted`) USING BTREE,
INDEX `dept_id`(`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1021664 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;
```
#### 有效的索引
1. 符合最左匹配原则,where后面的字段顺序和索引顺序一致
```sql
explain select * from sys_user where username = '有来技术' and dept_id = 2 and nickname = "test用户" and deleted = 1
```
![image-20230223234727187](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232347399.png)
2. 覆盖索引,虽然不符合最左匹配原则,但是查询的列都在索引中
```
explain select username,nickname,deleted from sys_user where nickname = "test用户" and deleted = 1
```
![image-20230223234841822](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232348466.png)3.索引下推,虽然username是索引字段,但是后面加上了模糊查询,并且是以%开头,应该是用不了索引的,但是MYSQL在5.6引入了(index Condition Pushdown)简称ICP特性,在存储引擎层优化了这种情况,也能使用索引
```
explain select * from sys_user where username = '有来技术' and email like '%youlai'
```
![image-20230223235039353](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232350854.png)
#### 无效的索引
1. 不符合最左匹配原则
```
explain select * from sys_user where dept_id = 2 and nickname = "test用户" and deleted = 1
```
![image-20230223225539068](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232255781.png)
2. 模糊查询以%开头
```
explain select * from sys_user where username like '%有来技术'
```
![image-20230223232159388](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232322624.png)
3. 使用!=导致索引失效,虽然username是索引字段,但是因为使用了!=,需要回表根据值来过滤数据,所以索引失效了
```
explain select * from sys_user where username != '有来技术'
```
![image-20230223231553123](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232315466.png)
4.使用了计算表达式
```
explain select * from sys_user where dept_id - 1 = 1
```
![image-20230223234129601](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232341585.png)
5.使用索引自身类型不同的值
```
explain select * from sys_user where username = 1
```
![image-20230223234353336](https://gcore.jsdelivr.net/gh/zhangchuan11/images/202302232343535.png)
标签:where,查询,sys,索引,调优,MySQL,优化,select From: https://www.cnblogs.com/chuan2/p/17149950.html