MySQL - 语句优化

使用 ENUM 而不是 VARCHAR

1、In和Exists优化

• In优化

select * from t1 where id in (select id from t2) ;

可以理解为

for(select id from t2){
      select * from t1 where t1.id = t2.id
    }

原则：小表驱动大表，即小的数据集驱动大的数据集

当T2表的数据集小于T1表的数据集时，in优于exists

• exists优化

select * from A where exists (select 1 from B where B.id = A.id)

可以理解为

for(select * from A){
   select * from B where B.id = A.id
}

当A表的数据集小于B表的数据集时，exists优于in

EXISTS子查询往往也可以用JOIN来代替

2、not in 优化，

如果not in 的指标范围非常大的话，这个效率很差。

举个例子

select customer_id ,first_name ,last_name ,email 
from customer 
where customer_id 
not in (select customer_id from payment);

每个customer_id都要到payment中查询一遍， 数据量大时很慢。

优化后 -----------> left join

select customer_id ,first_name ,last_name ,email 
from customer  a 
left join payment b 
on a.customer_id = b.customer_id 
where b.customer_id  is null

这样的话，可以避免对payment表的多次查询。

3、limit 分页优化

Case1： 根据主键字段排序的分页查询

mysql> select * from employees limit 10000,10;

这条sql语句从表中取出10010条数据，最后取最后10条数据。可想而知，如果要查询一张大表比较靠后的数据，这效率是非常低的。

当主键自增时，进行查询时。因为没有order by排序，所以通过主键排序，执行计划进行全表扫描。

优化：

mysql> select * from employees where id>10000 limit 10; -- 利用了主键id

会使用主键索引，所以查询会比较快。

适用条件：

1. id 主键自增且连续
2. 结果是按照主键排序的

Case2：根据非主键字段排序的分页查询

select * from employees  ORDER BY name limit 10000, 10  ;

关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录.

 select * from employees a inner join (select id from employees order by name limit 10000,10) b on a.id = b.id;

4、join 关联查询优化

1. 优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

2. 当使用left join时，左表是驱动表，右表是被驱动表

3. 当使用right join时，右表时驱动表，左表是被驱动表

4. 当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

关联sql的优化的两个核心点

关联字段加索引，让mysql做join操作时尽量选择NLJ算法

小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间.

• 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎好。

面试题：

怎么加快查询速度，优化查询效率，主要原则避免全表扫描，考虑在where及order by涉及的列建立索引！

1、使用order by的话，加上limit、where
原因：组合索引优化

2、使用like时，尽量查询信息前面不要加%，在后面加%
原因：前面加%会进行全局搜索

3、不要使用！=或<> ,比如不等于5，换为 >5 or <5
原因：索引会被放弃使用，进行全表扫描

4、where子句使用is null 和 is not null，where ！= 0
原因：把默认值设置为0，确保表中num没有null值

5、where子句使用or的优化，使用union all
原因：使用了or,索引将被放弃使用

6、where子句使用IN 或 NOT IN的优化，between替换in

7、不要使用select *

8、尽量条件加上索引

9、group by的优化
MySQL中的GROUP BY语句会对其后出现的字段进行默认排序（非主键情况），可以使用ORDER BY NULL禁止排序达到优化目的

sql大批量数据优化

（1）、MyISAM 存储引擎的表
可以使用：DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。
（2）、InnoDB 存储引擎的表
导入的数据按照主键的顺序保存，导入前设置SET AUTOCOMMIT=0，关闭自动提交，导入后结束再设置为1

Insert的优化

1、尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗。
2、当从一个文本文件装载一个表时，使用LOAD DATA INFILE，通常比INSERT语句快20倍。

参考链接：

https://www.cnblogs.com/jian0110/p/9410981.html

https://www.cnblogs.com/parryyang/p/5711537.html