MySQL中的SQL优化

## 课程目标

* 掌握SQL调优的原则

* 掌握SQL调优的基本逻辑

* 掌握优秀SQL的编写方案

* 掌握何为慢SQL以及检测方案

## 课程安排

* 优化原则以及基本逻辑

* 优秀SQL的编写方案

* 慢查询分析以及优化策略的设计

## SQL优化原则

* 减少数据量(表中数据太多可以分表，例如超出500万，例如双11是一个小时一张订单表)

* 减少数据访问量(将全表扫描可以调整为基于索引去查询)

* 减少数据计算操作(将数据库中的计算拿到程序内存中计算)

## SQL优化的基本逻辑

* 良好SQL编码的习惯(熟悉SQL编码规范、例如避免使用select *)

* 优秀SQL的编写逻辑(例如表关联时小表驱动大表)

* 定位需要优化的慢SQL语句(耗时多长时间的SQL是慢SQL)

* 调整优化策略并进行测试。(SQL结构上的调整、索引应用)

* 按业务进行分库分表。(分表可以在应用逻辑中减少单表数据量)

## 优秀的SQL编写方案分享

* 查询时尽量避免使用select *;

```

1. 这样可以减少数据扫描以及网络开销(很多查询不需要查询所有列)。

2. 要尽量使用覆盖索引(索引中已经包含你需要的数据)、减少回表查询。

```

例如，如何查询会基于salary找到雇员id，然后基于雇员id再去查hire_date.（回表）

```

create index index_salary on employees(salary);

select employee_id,salary,hire_date

from employees

where salary>15000

```

优化方案：

```

create index index_salary on employees(salary,hire_date);

select employee_id,salary,hire_date

from employees

where salary>15000

```

* 尽量避免在where子句中使用or作为查询条件。

```

1. or可能会使索引失效，进而执行全表扫描。

2. 全表查询的效率相对基于索引查询的效率会比较低。

```

例如

```

create index index_salary on employees(salary);

```

```

select first_name,hire_date,salary

from employees

where job_id='AD_VP' or salary>15000

```

方案优化: 将or操作换成union操作

```

select first_name,hire_date,salary

from employees

where job_id='AD_VP'

union all

select first_name,hire_date,salary

from employees

where salary>15000;

```

* where 条件中尽量不要出现与null值的比较

条件中包含和null值的比较时可能会不走索引，当然这也跟SQL优化器有关，优化器

有时会因为数据量的多少，对是否走索引进行评估，假如它认为不走索引效率可能

会更高，可能就不走索引了。

```

select first_name,salary,commission_pct

from employees

where commission_pct is null

```

* 避免在查询中存在隐式转换

```

create table tb_order

(

id int primary key,

user_id varchar(50) not null,

index index_user_id (user_id)

)

select * from tb_order where user_id=1; 这里存在隐式转换，有可能不走索引

select * from tb_order where user_id='2'; 推荐

```

* 避免在where子句中使用!=或者<>操作符

实际应用中这个查询是否走索引还与数据量有关。

```

select first_name

from employees

where job_id!='AD'

```

* 使用like查询条件时应尽量避免前缀使用"%"

```

select first_name,salary

from employees

where first_name like '%A%';

```

* 执行查询时尽量采用最做匹配原则？

```

create index 'index_hire_date_salary_pct' on employees (hire_date,salary,commission_pct);

这里相当于是创建了(hire_date),(hire_date,salary),(hire_date,salary,commission_pct)三个索引

假如我们执行如下查询，可能不走索引.

select *

from employees

where salary>15000;

假如我们这样执行查询，可能会走索引.

select *

from employees

where hire_date>'2000-01-01' and salary>15000

```

* 避免在查询条件中使用一些内置的SQL函数。

```

select *

from employees

where year(hire_date)='2000';

注意：在mysql8.0中也可以基于函数创建索引了。

```

* 假如in表达式后面的数据太多(一般不建议超过200)，尽量避免使用in作为查询条件。

* 当有多个查询条件、分组条件、排序条件时，尽量使用联合索引(组合索引)

* 表连接时优先使用内连接(inner join),使用小表驱动大表。

* 进行表关联的字段尽量使用相同的编码(不能一个字段utf8,一个字段utf8mb4)

* 表设计时字段类型能用简单数据类型不用复杂类型。

* 清空表中数据可优先使用truncate.

* 插入多条数据时可考虑使用批量插入。

例如：

```

insert into regions (region_id,region_name) values (1,'A');

insert into regions (region_id,region_name) values (2,'B');

```

替换为

```

insert into regions (region_id,region_name) values (1,'A'),(2,'B');

```

# 慢SQL查询分析

## 如何定位慢SQL？

优化 SQL 的前提是能定位到慢 SQL,例如查看慢查询日志，确定已经执行完的慢查询。

## 基于慢SQL日志查询慢SQL？

使用慢查询日志一般分为四步：

1. 开启慢查询日志（一般默认是关闭状态）

2. 设置慢查询阀值(响应速度是多长时间被定为是慢查询)

3. 确定慢查询日志路径(日志文件在哪里)

4. 确定慢查询日志的文件名(具体日志文件是哪个)，然后对文件内容进行分析。

* 查看慢查询日志的打开状态？

```

show variables like '%slow_query_log%';

```

默认环境下，慢查询日志是关闭的(OFF)。

* 如何开启慢查询日志?

```

set global slow_query_log=ON

```

* 查看默认慢查询阈值 (默认为10秒中)

```

show variables like '%long_query_time%';

```

* 如何设置慢查询的阈值？

设置慢查询时间阀值(响应时间是多长时间是慢查询)

MySQL5.7

```

mysql> set long_query_time = 1;

Query OK, 0 rows affected (0.00 sec)

```

* 如何知道慢查询日志路径？

慢查询日志的路径默认是 MySQL 的数据目录

```

mysql> show global variables like 'datadir';

+---------------+------------------------+

| Variable_name | Value |

+---------------+------------------------+

| datadir | /mysql/data/ |

+---------------+------------------------+

1 row in set (0.00 sec)

```

* 如何知道慢查询日志的文件名？

```

mysql> show global variables like 'slow_query_log_file';

+---------------------+----------------+

| Variable_name | Value |

+---------------------+----------------+

| slow_query_log_file | mysql-slow.log |

+---------------------+----------------+

1 row in set (0.00 sec)

```

* 执行一个耗时SQL，然后查看慢SQL日志

例如

```

select * from t1 where id between 10000 and 20000;

```

打开日志文件，可以对日志文件中的内容进行分析，常用选项说明：

```

Time：慢查询发生的时间

User@Host：客户端用户和IP

Query_time：查询时间

Lock_time：等待表锁的时间

Rows_sent：语句返回的行数

Rows_examined：语句执行期间从存储引擎读取的行数

```

* 如何对慢查询进行分析？

工欲善其事，必先利其器，分析慢查询可以通过 explain、show profile 等工具来实现。

# 执行计划(Explain)

## 执行计划(Explain)是什么？

执行计划是mysql优化器对SQL进行默认调优后，给出的一种执行方案，这个方案我们可以通过explain

这个指令进行查询。例如，对select语句进行分析，并输出select执行时的详细信息，开发人员可以

基于这些信息进行有针对性的优化，例如：

```

mysql> explain select * from employees where employee_id<100 \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

partitions: NULL

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: NULL

rows: 1

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

```

## 分析执行计划的目的

* 检查关联查询的执行顺序

* 查询操作的具体类型

* 哪些索引可能会命中以及实际命中的索引有哪些

* 每张表可能有多少条记录参与到了查询中

## 执行计划中相关字段说明

* id

select 的序列号，有几个select 就有几个 id，id 的顺序是按 select 出现的顺序增长的。

即：id 越大执行优先级越高，id相同则从上往下执行，id 为NULL最后执行。

```sql

explain

select last_name,salary

from employees

where employee_id=(

select manager_id

from employees

where employee_id=206);

```

```sql

explain

select m.first_name,m.salary

from employees e join employees m

on e.manager_id=m.employee_id

where e.employee_id=206

```

* select_type表示的查询类型有哪些？

```

1. SIMPLE ： 表示查询语句不包含子查询或 union

2. PRIMARY：表示此查询是最外层的查询

3. UNION：表示此查询是 union 的第二个或后续的查询

4. UNION RESULT：union 的结果

5. DEPENDENT UNION：子查询中的UNION操作，UNION后的所有select都是DEPENDENT UNION。

6. SUBQUERY：SELECT 子查询语句

7. DEPENDENT SUBQUERY：子查询中的第一个SELECT,SELECT 子查询语句依赖外层查询。

8. DERIVED: from 子句后的相对比较复杂子查询(相当于一个临时表)，当看到derivedN时，这里N表时查询id

```

案例分析：

> select_type为SIMPLE (表示查询语句不包含子查询或 union)

```sql

explain

select *

from employees

where employee_id<100;

```

> select_type为PRIMARY、SUBQUERY (PRIMARY表示最外层查询，SUBQUERY表示嵌套查询)

```sql

explain

select last_name,salary

from employees

where employee_id=(

select manager_id

from employees

where employee_id=206);

```

>select_type为 UNION(union操作)、UNION RESULT(union的结果)

```sql

explain

select first_name,hire_date,salary

from employees

where job_id='AD_VP'

union

select first_name,hire_date,salary

from employees

where salary>15000;

```

> select_type为DEPENDENT UNION (子查询中的UNION操作，UNION后的所有select都是DEPENDENT UNION。)

```sql

explain

select *

from employees e3

where first_name in (

select first_name

from employees e1

where job_id = 'AD_VP'

union

select first_name

from employees e2

where salary > 15000

)

```

> select_type 为 DEPENDENT SUBQUERY

求每个部门中薪资最高的那个雇员的信息(雇员id，名字，薪水)

```sql

explain

selectfirst_name,salary

from employees e1

where salary=( employee_id,

select max(salary)

from employees e2

where e1.department_id=e2.department_id);

```

说明，一般出现DEPENDENT SUBQUERY时，SQL的执行效率都会比较低，可以调整为多表查询，例如：

```sql

explain

select e2.department_id,e2.employee_id,e2.first_name,e2.salary

(

select department_id,max(salary) max_salary

from employees

group by department_id) e1 join employees e2

e1.department_id=e2.department_id

where e1.max_salary=e2.salary

```

> select_type为DERIVED(这里一般表示from后面的一个衍生表-临时表)

```sql

explain

select min(avg_salary)

from (

select avg(salary) avg_salary

from employees

group by department_id) emp;

```

* type表示查询数据的方式。(重点)

type是一个比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。

常用属性值如下，从上至下效率依次增强。调优时，建议type类型至少要为range，才能提高

查询效率。

```

1. ALL：表示全表扫描，性能最差。(数据量小时无所谓)

2. index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

3. range：表示使用索引范围查询。使用 >、>=、<、<=、in 等等。

4. index_merge: 表示查询中实用到了多个索引，然后进行了索引合并

5. ref：表示使用非唯一索引进行单值查询。

6. eq_ref：一般情况下出现在多表 join 查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。

7. const：表示使用主键或唯一索引做等值查询，常量查询。(效率非常高)

8. NULL：表示不用访问表，也没有索引，速度最快。(了解，例如select version())

```

案例分析：

> type为null

```sql

explain

select now()

```

> type 为const (基于主键或唯一键执行的查询)

```sql

explain

select *

from employees

where employee_id=206

```

> type 为eq_ref (多表join，前面表的每一行记录，只能匹配后面表的一行记录)

```sql

explain

select d.department_id,d.department_name,e.first_name

from departments d join employees e on d.manager_id = e.employee_id;

```

> type为 ref (使用非唯一索引进行的等值查询)

```sql

create index index_first_name on employees(first_name);

explain

select *

from employees

where first_name='Steven';

```

```sql

create index index_salary on employees (salary);

explain

select salary,first_name from employees where salary=17000;

```

> type 为 index_merge (索引合并，同时应用两个索引)

```sql

create index index_salary on employees(salary);

explain

select first_name,hire_date,salary

from employees

where job_id='AD_VP' or salary>15000;

```

> type 为 range (这里的range表示一个范围查询)

```sql

create index index_salary on employees (salary);

explain

select first_name,salary

from employees

where salary between 10000 and 30000;

```

> type 为index (基于索引的全表扫描)

```sql

explain

select count(*)

from employees

group by department_id;

```

> type 为 all (表示全表扫描)

```sql

explain

select *

from employees

```

* Extra中值的含义是什么？

Extra 表示很多额外的信息，各种操作会在 Extra 提示相关信息，常见几种如下：

> Using where 表示查询需要通过where条件查询数据(可能没有用到索引,也可能一部分用到了索引)。

```

explain

select *

from hr.employees

where salary>10000;

```

> Using index 表示查询需要通过索引，索引就可以满足所需数据(不需要再回表查询，这里出现了索引覆盖)。

```

create index index_hire_date_salary on employees(hire_date,salary);

explain

select employee_id,hire_date,salary

from hr.employees

where hire_date>'2000-03-06' and salary>10000;

```

> Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有 Using filesort 建议优化。

```

explain

select first_name,hire_date,salary

from hr.employees

order by hire_date

```

> Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作(这里一般也需要优化)。

```

explain

select first_name,salary

from hr.employees

where first_name like 'A%'

union

select first_name,salary

from hr.employees

where first_name like 'B%'

```

> Using index condition 表示查询的记录，在索引中没有完全覆盖(可能要基于where或二级索引对应的主键再次查询-回表查询)。

```

create index index_hire_date_salary on employees (hire_date,salary)

explain

select employee_id,hire_date,salary,commission_pct

from hr.employees

where hire_date>'2000-03-06' and salary>10000;

```

# 性能分析工具(Profile)应用（了解）

## 什么是Profile？

Profile 是MySQL内置的一个性能分析工具，基于Profile可以更好的了解SQL执行过程中的资源情况。

例如你的CPU,内存，IO等

## 如何使用Profile?

1. 确定你的数据库是否支持Profile。

2. 确定Profile是否是关闭的,假如是关闭的需要先开启。

3. 执行SQL语句

4. 查看执行SQL的query id。

5. 通过query id查看SQL每个状态的耗时时间。

## 确定数据库是否支持Profile?

```

select @@have_profiling

假如查询结果为yes表示支持。

```

## 确定Profile是否是关闭的？

```

select @@profiling

假如查询结果为0表示没有开启。

```

## 开启和查看Profile

```

set profiling=1

```

* 执行SQL语句？

```

select * from employees where salary>5000;

```

* 查看SQL的query id？

```

show profiles;

```

* 查看具体SQL的执行详情？

```

show profile for query 1;

```

* Show Profile的常用查询选项

```

ALL：显示所有的开销信息。

BLOCK IO：显示块IO开销。

CONTEXT SWITCHES：上下文切换开销。

CPU：显示CPU开销信息。

IPC：显示发送和接收开销信息。

MEMORY：显示内存开销信息。

PAGE FAULTS：显示页面错误开销信息。

SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

```

例如

```sql

show profile CPU,block io for 6

```

# 总结(Summary)

## 重难点分析

* 思维导图(https://www.processon.com/view/link/632d82e35653bb1ab0ea0fab)

* SQL调优原则以及基本方案

* 慢SQL定义以及分析(Explain,Profile)

## 常见FAQ

* SQL调优的原则是什么？(减少数据、减少数据的访问量、减少计算)

* SQL调优的基本方案？

* 优秀SQL编写的基本逻辑？

* 如何定位执行效率比较慢的SQL语句？(慢SQL日志)

* 如何对慢SQL进行分析？(执行计划-Explain,Profile,....)

* 说说执行计划中几个重要的字段？(id,select_type,type,key,extra,rows)

## BUG分析

* 数据库的版本