小徐带你了解项目中经常用到的mysql优化

一：EXPLAIN

做Mysql优化，我们要善用EXPLAIN查看SQL执行计划。

explain select id,name,age,email from tbl_user 
	where name like '%aa%';

explain中属性的相关介绍

type列：连接类型，一个好的SQL语句至少要达到range级别，杜绝出现all级别。

key列：使用到的索引名，如果没有选择索引，值是NULL，可以采取强制索引方式

key_len列：索引长度

rows列：扫描行数，该值是个预估值。

extra列：详细说明，注意，常见的不太友好的值，如：Using filesort，Using temporary。

二：SQL语句中IN包含的值不应过多

Mysql对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的，
但是如果数组值较多，产生的消耗也是比较大的，再例如：select id from t where num in (1,2,3)对于连续的数值，
能用between就不要用in；再或者使用连接来替换。

三：select语句务必指明字段名称，* 一定要杜绝

select 	*	增加很多不必要的消耗(CPU，IO，内存，网络宽带)；增加了使用覆盖索引的可能性，
当表的结构发生改变时，前断也需要更新，所以要求直接在select后面接上字段名。

四：当只需要一条数据的时候，使用limit 1

这是为了使explain中的type列达到const类型

五：如果排序字段没有用到索引，就尽量少排序

六：如果限制条件中其它字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况，

很多时候使用union all或者是 union（必要的时候)的方式来代替 "or"会得到更好的效果。

七：尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并再进行唯一性过滤操作，这就会涉及到排序

，增加大量的CPU运算，加大资源消耗及延迟，当然，union all的前提条件是两个结果集没有重复数据。

八：不使用order by rand()

select	id	from dynamic	order by rand() limit 1000;

上面的SQL语句，可优化为：

select id from dynamic t1 join (select rand()* (select max(id)from
ynamic) as nid) t2 on t1.id > t2.nid limit 1000;

九：区分in和exists，not in 和not exists

select * from 表A where id in(select id from 表B);

**上面的SQL语句相当于**

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

区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层
表为驱动表，

九.一：in和exists

in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，

一直依赖认为exists比in效率高的说法是不准确的，如果查询的两个表大小相当，那么用in和exists

差别不大；如果一个表中有一个较小一个较大，则子查询表大的用exists,子表小的用in;

例如：表A(小表)，表B(大表)

#效率低,用到了A表上cc列的索引
select * from A where cc in(select cc from B);   
#效率高,用到了B表上的cc列的索引
select * from A where exists(select cc from B where cc = A.cc); 

#相反的：
#效率高,用到了B表上cc列的索引
select * from B where cc in(select cc from A)
#效率低,用到了A表上cc列的索引
select * from B where exists(select cc from A where cc = B.cc);

九.二：not in 和 not exists

not int 逻辑上不完全等同于 not exists，如果你误用了not in，小心你的成勋存在致命的BUG，请看例子：

#创建表 t1m=,t2
create table t1(c1 int,c2 int);
create table t2(c1 int,c2 int);
#向表t1,t2中插入语句
insert into t1 values(1,2);
insert into t1 values(1,3);
insert into t2 values(1,2);
insert into t1 values(1,null);

#执行结果,无
select * from t1 where c2 not in(select c2 from t2);
#执行结果,1  3
select * from t1 where not exists(select 1 from t2 where t2.c2 = t1.c2);

正如所看到的，not in出现了不期望的结果集，存在逻辑错误，如果看一下上述两个select语句执行计划，

也会不同，后者使用了hash_aj，所以，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists

（它会调用关联子查询)，如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，如果

子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它使用hasg_aj或merge_aj连接。

如果查询语句使用了not in，那么对内表都进行全表扫描，没有用到索引，而not exists的子查询依然能用到

表上的索引，所以无论哪个表大，用not exists都比not in要快。

十：前导模糊查询不能使用索引

例如 name line '%s'

十一：Union，in，or 可以命中索引，建议使用in

十二：负条件查询（!=，<>，not in，not exists，not like）不能使用索引，可以优化为in查询

十三：联合索引最左前缀原则，又叫最左侧查询，如果在(a，b，c)三个字段上建立联合索引，那么它能够加快 a | (a，b），(a，b，c）三组查询。

十四：建立联合索引使，区分度高的字段在最左前面

十五：如果建立了(a，b)联合索引，就不必再单独建立a索引，同理，如果建立了(a，b，c）索引就不必再建立a，（a，b）索引。