MySQL中的sql优化

一、SQL优化原则

1、减少数据量（表中数据太多可以分表，例如超过500万数据  双11一个小时一张订单表）

2、减少数据访问量（将全表扫描可以调整为基于索引去查询）

3、减少数据计算操作（将数据库中的计算拿到程序内存中计算）

二、SQL优化的基本逻辑

1、良好的SQL编码习惯（熟悉SQL编码规范、例如避免使用“select * “）

2、优秀SQL的编写逻辑（例如关联时遵循小表驱动大表）

3、定位需要优化的慢SQL语句（耗时多少时间的SQL是慢SQL）

4、调整优化策略并进行测试（SQL结构上的调整、索引应用）

5、按业务进行分库分表。（分表可以在应用逻辑中减少单表数据量）

三、优秀的SQL编写方案

1、查询时尽量避免使用 select *。

　　这样可以减少数据扫描以及网络开销（很多查询不需要查询所有列）

　　要尽量使用覆盖索引（索引中已经包含你需要的数据）、减少回表查询

　　　　如何查询会基于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

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

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

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

　　　　例如：

　　　　　　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（union 是将查询结果去重）

　　　　　　select first_name,hire_date,salary

　　　　　　from employees

　　　　　　where salary>15000

3、where条件中尽量不要出现null值的比较

　　条件中包含和null值的比较时可能会不走索引，当然这也跟SQL优化器有关，优化器有时会因为数据量的多少，对是否走索引进行评估，假如它认为不走索引效率可能会更　　　　　　　　高，可能就不走索引了。

　　　　　  select first_name,salary,commission_pct

　　　　　  from employees

　　　　　  where commission_pct is null

4、避免在查询中存在隐式转换

　　　　　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'; 推荐

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

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

　　　Select first_name

　　　from employees

　　　where job_id!='AD'

6、使用like查询条件时应尽量避免前缀使用‘%’

　　　　Select first_name,salary

　　　　from employees

　　　　where  first_name like ‘%A%’

7、执行查询时尽量采用最左匹配原则

　　　　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

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

　　　　select * 

　　　　from  employees

　　　　where year(hire_date)=‘2000’

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

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

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

11、表连接时优先使用内连接(inner join)，使用小表驱动大表

12、进行表关联的字段尽量使用相同的编码(不能一个字段utf-8，一个字段utf8mb4)

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

14、清空表中数据可优先使用truncate

15、插入多条数据时考虑使用批量插入

四、慢sql查询分析

1、如何定位慢sql？

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

2、如何基于慢SQL日志查询慢SQL？

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

　　1、开启慢查询日志（一般默认是关闭状态） set global slow_query_log=ON

　　2、设置慢查询阈值（响应速度是多长时间被定义为慢SQL）set long_query_time=1

　　3、确定慢查询日志的路径（日志文件在哪里）show variables like ‘datadir’

　　4、确定慢查询日志的文件名（具体日志文件是哪一个），然后对文件内容进行分析 show global variables like ‘slow_query_log_file’

执行计划（Explain）

1、执行计划是什么？

执行计划是MySQL优化器对SQL进行默认调优的，给出的一个执行方案，这个方案我们可以通过explain这个指令进行查询。例如，对select语句进行分析，并输出select执行时的详细信息，开发人员可以基于这些信息进行有针对性的优化。

2、分析执行计划的目的？

1、检查关联查询的执行顺序

2、查询操作的具体类型

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

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

3、执行计划中相关字段的说明

1、id

select的序列号，有几个select就有几个id，id的顺序是按select出现的顺序增长的，即：id越大执行优先级越高，id相同则从上往下执行，id为null最后执行

2、select_type

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

PRIMARY：表示此查询最外层的查询

UNION RESULT：union的结果

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

SUBQUERY：select子查询语句

DEPENDENT SUBQUERY：子查询中的第一个select，select子查询语句依赖外层查询

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

3、type表示查询数据的方式(重点)

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

Index:表示基于索引的全表扫描，先扫描索引再扫描全表数据

Range：表示使用索引范围查询。使用>、>=、

Index_merge：表示查询中使用到了多个索引，然后进行了索引合并

Ref：表示使用非唯一索引进行单值查询

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

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

Null：表示不用访问表，也没有引用，速度最快

4、Extra值的含义是什么？

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

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

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

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

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