MySQL查询优化

一、SQL优化原则

1.合理建立索引
2.不要使用select *
3.善用with as
4.善用exists
5.关联表要注意字符集是否相同
6.善用执行计划
7.每张表都要有自增无意义主键

二、建立索引原则

1.经常排序、分组、联合的列（where,order by）的列加索引
2.选择区分度高的列建立索引，区分度计算：一般是count(distinct col)/count(*)
3.数据量如果小（1W以下），加不加索引区别不大，原则上10W以上的数据量就要加索引了
4.尽量选择数据量小的字段加索引（类似varchar(255)这种字段就不要加索引），如果一定要加，可以考虑加前缀索引，加前缀索引的时候也要考虑区分度，区分度也是越高越好
5.索引不要建太多，如果需要多个索引可以考虑联合索引，要考虑最左匹配原则
6.索引列不要参与计算
7.当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率

三、最左匹配原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a =’1’ and b=’2’ and c> 3 and d = ‘4’ 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

四、in和exists的区别

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

如果A表大，适合用in
如果B表大，适合用exists

五、with as 的好处

WITH AS 短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在 UNION ALL 的不同部分，作为提供数据的部分。 特别对于 UNION ALL 比较有用。因为 UNION ALL 的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用 WITH AS 短语，则只要执行一遍即可。如果 WITH AS 短语所定义的表名被调用两次以上，则优化器会自动将WITH AS 短语所获取的数据放入一个 TEMP 表里，如果只是被调用一次，则不会。而提示materialize则是强制将 WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。<br /> WITH AS 短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在 UNION ALL 的不同部分，作为提供数据的部分。 特别对于 UNION ALL 比较有用。因为 UNION ALL 的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用 WITH AS 短语，则只要执行一遍即可。如果 WITH AS 短语所定义的表名被调用两次以上，则优化器会自动将WITH AS 短语所获取的数据放入一个 TEMP 表里，如果只是被调用一次，则不会。而提示materialize则是强制将 WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。