mysql语句优化心得

一、常用SQL优化处理

1.1 like查询优化方案

like模糊查询形如'%AAA%'和'%AAA'将不会使用索引，但是业务上不可避免可能又需要使用到这种形式查询方式：

优化方案一：

使用覆盖索引，即查询出的列只是用索引就可以获取，而无须查询表记录，这样也走了索引；

优化方案二：

使用locate函数或者position函数代替like查询：
如table.field like '%AAA%'可以改为locate('AAA', table.field) > 0或POSITION('AAA' IN table.field)>0

1.2 日期函数优化

原SQL1:

select d.id, d.project_id, b.project_name, sum(d.order_sum)as order_sum,
sum(d.order_rate)as order_rate, sum(d.unfinished)as unfinished, d.data_time
from data_workorder_num d
left join base_project b on b.project_id = d.project_id
where d.project_id in
#{proId}
and date_format(d.data_time, "%Y-%m-%d") between str_to_date( #{startDate}, "%Y-%m-%d" )
and str_to_date( #{endDate}, "%Y-%m-%d" )
group by d.project_id

优化后的SQL1:

select d.id, d.project_id, b.project_name, sum(d.order_sum)as order_sum,
sum(d.order_rate)as order_rate, sum(d.unfinished)as unfinished, d.data_time
from data_workorder_num d
left join base_project b on b.project_id = d.project_id
where d.project_id in
#{proId}
and d.data_time between concat(str_to_date( #{startDate}, "%Y-%m-%d" ),':00:00:00')
and concat(str_to_date( #{endDate}, "%Y-%m-%d" ),':00:00:00')
group by d.project_id

1.3 查询两表使用join on和使用子查询in的比较

不要轻易使用in子查询，由于in子查询总是以外层查询的table作为驱动表，所以如果想用in子查询的话，一定要将外层查询的结果集降下来，降低io次数，降低nested loop循环次数，即：永远用小结果集驱动大的结果集。

1.4 in和exists使用场景

1.4.1 in和exists

如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：例如：表A（小表），表B（大表）

1：

select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。

2：

select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。

1.4.2 not in和not exists使用比较

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in要快。

1.4.3 in 与 =的区别

select name from student where name in ('zhang','wang','li','zhao'); 
与 
select name from student where name='zhang' or name='li' or name='wang' or name='zhao' 
的结果是相同的。

1.5 left join的优化

用小表驱动大表，同时对关联的条件列建立相关的索引。

二、mysql常用关键字处理

2.1 password

password-->passwd

2.2 type

type-->前缀_type  //建议前缀可以是表名的部分

2.3 其他关键字与保留字

关键字、保留字--->前缀_关键字或保留字

三、分页

LIMIT高效的分页

3.1 传统分页

select * from table limit 10000,10

3.2 LIMIT原理：

（1） Limit 10000,10 （2）偏移量越大则越慢

3.3 推荐分页

分页方式一：
select * from table where id>=23423 limit 11;
 #10+1(每页10条)
select * from table where id>=23434 limit 11;

分页方式二：
select * from table where id>=(select id from table limit 10000,1) limit 10;
 
分页方式三：
 select * from table INNER JOIN (select id from table limit 10000,10) using(id);

分页方式四：
a. 程序获取id: select id from table limit 10000,10;
b. select * from table where id in (123,456...);

3.4 案例2

1、普通写法

select  *  from t where sellerid=100 limit 100000，20 
普通limit M，N的翻页写法，往往在越往后翻页的过程中速度越慢，原因 
mysql会读取表中的前M+N条数据，M越大，性能就越差。

2、优化化的写法

优化写法： 
select t1.* from  t t1, (select id from t where sellerid=100 limit 100000，20) t2 
where t1.id=t2.id; 
优化后的翻页写法，先查询翻页中需要的N条数据的主键id，在根据主键id 
回表查询所需要的N条数据，此过程中查询N条数据的主键ID在索引中完成