一、常用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在索引中完成