select id from `dynamic` order by rand() limit 1000; 上面的SQL语句,可优化为: select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;
9、区分in和exists、not in和not exists
1 2 3
select * from 表A where id in (select id from 表B) 上面SQL语句相当于: select * from 表A where exists(select * from 表B where 表B.id=表A.id)
原SQL语句: select colname … from A表 where a.id not in (select b.id from B表) 高效的SQL语句: select colname … from A表 Left join B表 on where a.id = b.id where b.id is null 取出的结果集为A表不在B表中的数据
10、使用合理的分页方式以提高分页的效率
1 2 3 4
select id,name from product limit 866613, 20 使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。 优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法: select id,name from product where id> 866612 limit 20
在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。
1 2 3 4 5
创建全文索引的SQL语法是: ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`); 使用全文索引的SQL语句是: select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan‘ in boolean mode); 注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。
14、避免在where子句中对字段进行表达式操作
1 2 3 4
比如: select user_id,user_project from user_base where age*2=36; 中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成: select user_id,user_project from user_base where age=36/2;
注意: 1)MySQL中没有full join,可以用以下方式来解决: select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B; 2)尽量使用inner join,避免left join: 参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。 3)合理利用索引: 被驱动表的索引字段作为on的限制字段。 4)利用小表去驱动大表: 如果能够减少驱动表的话,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数。 5)巧用STRAIGHT_JOIN: inner join是由MySQL选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by、order by等“Using filesort”、“Using temporary”时。STRAIGHT_JOIN来强制连接顺序,在STRAIGHT_JOIN左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接,也就是inner join。其他链接不推荐使用STRAIGHT_JOIN,否则可能造成查询结果不准确。 SELECT `rl`.`fid`, `rl`.`update_time` as `add_time`, `f`.`fnum`, `f`.`dst_parking`, `f`.`aircraft_num`, `f`.`touch_down_runway`, `f`.`forg`, `f`.`fdst`, `f`.`flight_status_code`, `fd`.`fdst` as `fd_fdst`, `ds`.`touch_down_runway_source`, `ds`.`touch_down_runway_update_time`, `u`.`truename`, `a`.`aircraft_model` FROM `goms_flight_runway_log` as `rl` STRAIGHT_JOIN `goms_flight` as `f` ON `rl`.`fid` = `f`.`fid` LEFT JOIN `goms_aircraft` as `a` ON `f`.`aircraft_num` = `a`.`aircraft_num` LEFT JOIN `goms_flight_link` as `fl` ON `f`.`fid` = `fl`.`arr_fid` LEFT JOIN `goms_flight` as `fd` ON `fl`.`dept_fid` = `fd`.`fid` LEFT JOIN `goms_flight_field_data_source` as `ds` ON `f`.`fid` = `ds`.`fid` LEFT JOIN `goms_user` as `u` ON `ds`.`touch_down_runway_source` = `u`.`uid` WHERE `rl`.`op_type` = 0 AND `f`.`fdst` = ‘PVG‘ AND `f`.`fid` != ‘‘ GROUP BY `rl`.`fid` ORDER BY `rl`.`update_time` DESC LIMIT 20