|
|
# 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会一直向右匹配直到遇到范围查询（>, |
|
