MySQL调优

标签：where 查询 sys 索引 调优 MySQL 优化 select

# 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. 将处理结果通过连接返回到应用服务器


 

 

## 慢查询日志

在优化SQL前需要先找到需要优化的SQL,一般是通过慢查询日志来查询

### 查询是否开启慢查询日志

```sql
SHOW VARIABLES LIKE 'slow_query_log';
```


### 开启慢查询日志

```sql
SET GLOBAL slow_query_log = 'ON';
```

 

### 查询慢查询日志的路径

默认和数据文件放一起

```sql
show VARIABLES like '%slow_query_log_file%';
```


### 慢查询记录时间的阈值

默认十秒

```sql
show VARIABLES like '%long_query_time%';
```


### 查询是否开启记录未使用索引的SQL

```sql
show VARIABLES like '%log_queries_not_using_indexes%';
```


找到了需要优化的SQL,下面开始分析SQL的组成

## SQL执行计划

MySQL使用explain关键字来分析SQL，只要在SQL语句前加上一个explain关键字，就可以得到一个SQL的执行计划

```sql
explain select * from sys_user where id = 2979;
```


### 执行计划字段详解

#### 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
```


2. 覆盖索引，虽然不符合最左匹配原则，但是查询的列都在索引中

```
explain select username,nickname,deleted from sys_user where nickname = "test用户" and deleted = 1
```

 

3.索引下推，虽然username是索引字段，但是后面加上了模糊查询，并且是以%开头，应该是用不了索引的，但是MYSQL在5.6引入了(index Condition Pushdown)简称ICP特性，在存储引擎层优化了这种情况，也能使用索引
```
explain select * from sys_user where username = '有来技术' and email like '%youlai'
```


#### 无效的索引

1. 不符合最左匹配原则

```
explain select * from sys_user where dept_id = 2 and nickname = "test用户" and deleted = 1
```


2. 模糊查询以%开头

```
explain select * from sys_user where username like '%有来技术'
```


3. 使用!=导致索引失效，虽然username是索引字段，但是因为使用了！=，需要回表根据值来过滤数据，所以索引失效了

```
explain select * from sys_user where username != '有来技术'
```


4.使用了计算表达式

```
explain select * from sys_user where dept_id - 1 = 1
```


5.使用索引自身类型不同的值

```
explain select * from sys_user where username = 1
```


 

标签：where,查询,sys,索引,调优,MySQL,优化,select
From： https://www.cnblogs.com/chuan2/p/17149950.html