SQL优化

优化原因

性能低、执行时间太长、等待时间长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理

SQL编写和执行过程

编写过程： select dinstinct … from … join …on … where … group by … having … order by … limit …
解析过程： from … on …join … where … group by …having … select dinstinct … order by
mysql解析过程链接：https://www.cnblogs.com/annsshadow/p/5037667.html

SQL优化主要手段

主要是优化索引，相当于书的目录

索引的简介

概念

index是指帮助mysql高效获取数据结果，索引是数据结构（二叉树，B树）
二叉树：小的放在左边，大的放在右边
B树：有一个范围的概念：范围内的放在中间，范围小的放在左边，范围大的放在右边。三层B树可以存放上百万条数据
B+树所有数据都存在叶节点中，查询所有数据都需要N次（N为树的层数）

索引弊端

1. 索引本身很大，可以存放在内存或者硬盘中
2. 索引不是所有情况都适用：a：少量数据 b：频繁变更的字段 c：很少使用的字段
   3.索引提高查询效率，降低增删改效率

索引优点

1. 提高查询效率
2. 降低CPU使用率（order by 不要排序等）

索引分类

1. 单值索引：单列
2. 唯一索引：不能重复 id
3. 复合索引：多个列构成的索引（相当于二级目录，不一定每列都中）

索引的操作

创建索引

create 索引类型 索引名 on 表(字段)

单值：create index xxx_index on tb(name);
唯一索引：create unique index xxx_index on tb(name);
复合索引：create index xxx_index on tb(dept,name)；
注意：如果一个字段是主键，则该列为主键索引

删除索引

drop index 索引名 on 表名；

查询索引：

show index from table 表名；

SQL性能优化

分析SQL执行计划：explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的sql执行是否符合预期。
MySQL优化方法：官网：https://dev.mysql.com/doc/refman/5.5/en/optimization.html

SQL执行计划

id：编号
id值相同：从上往下，依次执行，连接查询的话数据小的表优先查询，因为笛卡尔积小
id值不同：id越大优先查询（本质：嵌套子查询的时候，先查内层，再查外层）
select_type:查询类型
primary：包含子查询sql中的主查询
subquery：包含子查询中的子查询
simple：简单查询（不包含子查询，union）
derived：衍生查询（使用了临时表）
a：在from子查询中只有一张表
select cr.name from (select * from course where tid in (1,2) cr)
b：在from子查询中，如果有table1 union table2，则table1就是derived
select cr.name from (select * from course where tid = 1 union select * from course where tid = 2)
table：表
type：类型,索引类型
system > const > eq_ref > ref > range > index > all
system:只有一条数据的系统表或衍生表只有一条数据的主查询
const：仅仅能查到一条数据的sql，用于primary key或者unique索引
eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一行数据，常见于唯一索引合主键索引。
ref：非唯一性索引，查询返回一条或者多条。
range：检索指定范围的行，where后面是一个范围的查询（between, in , > , <, ==）
index：查询全部索引数据
all：查询全部数据
possiable_keys:预测用到的索引，预测值，不准
key：实际使用的索引
key_len:实际使用索引长度
作用：用于判断符合索引是否被完全使用。
如果字段可以为null，则mysql会使用一个字节用于标识，若字段为varchar，会使用两个字节用于标识可变长度。utf8：一个字符3个字节
ref：表之间的引用
作用：用于指明当前表锁参照的字段。
rows：通过索引查询到的数据量，被索引优化查询的个数。
Extra：额外的信息
using filesort：性能消耗大，需要额外的一次排序。
对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则出现
避免：where 哪些字段，就order by哪些字段
复合索引：不能跨列（最佳左前缀）
避免：where和order by按照复合索引的顺序使用，不要跨列或无序使用。
using temporary：性能损耗大，用到了临时表，一般出现在group by语句中
避免：查询哪些列，就按照哪些列group by

索引优化

explain select a1,a2,a3,a4 from test where a1=1 and a2=2 and a3=3 and a4=4;
推荐写法，使用了全部索引。
explain select a1,a2,a3,a4 from test where a4=1 and a3=2 and a2=3 and a1=4;
使用了全部复合索引，虽然索引顺序和where条件顺序不一致，但是sql优化会将顺序重新排列为一致。
explain select a1,a2,a3,a4 from test where a1=1 and a2=2 and a4=4 order by a3;
以上sql用到了a1，a2两个索引，该两个字段，不需要回表查询 using index，而a4因为跨列使用，造成索引失效
注意：使用索引不能跨列，若是跨列，则跨的列失效
explain select a1,a2,a3,a4 from test where a1=1 and a4=4 order by a3;
以上sql出现了using filesort（文件内排序，多了一次额外查找），不要跨列使用。where 和 order by拼起来不要跨列使用。
若是where 和 order by拼起来满足索引顺序，则不会出现文件内排序。
如果（a,b,c,d）复合索引和使用顺序全部一致（且不跨列使用），则复合索引全部用到。

优化案例

单表优化

1.给表加索引
2.根据sql实际解析的顺序，调整索引的顺序
3.索引一旦进行升级优化，需要将之前废弃的索引删掉。
4.最佳左前缀，保持索引的定义和使用顺序的一致性。
5.索引需要逐步优化。
6.将含In的范围查询放到where条件的最后，因为in有可能导致索引失效。

两表优化

1.一般情况，对于左外连接给左表加索引，右外连接给右表加索引。
2.索引加在经常使用的字段上面。
3.小表驱动大表。

多张表优化

1.小表驱动大表。
2.索引加在经常查询的字段上面。

索引失效的一些原则

1.复合索引，不要跨列或者无序使用。
2.复合索引，尽量使用全索引匹配。
3.不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效。
4.in语句可能使索引失效。
5.对于复合索引，左边若失效，右边全部失效。
6.复合索引不能使用不等于（<>,!=）或者（is null） （is not null）。
7.索引优化是一个大部分情况都适用的结论，但由于sql优化器等原因，该结论不是100%正确。
8.like尽量以"常量"开头，不要以"%"开头，否则索引失效，若必须使用%开头，可以使用索引覆盖挽救一部分，
即：查询索引列
9.尽量不要使用类型转换。
10.尽量不要使用or，否则索引失效。
11.如果主查询的数据集大，则使用in，效率高。如果子查询的数据集大，则使用exist，效率高。
12.order by优化