MySQL语句高效写法整理

优先使用INNER JOIN

多表关联查询，扫描的行尽量少

        关联的时候下条件减少扫描的行数

SELECT

     ...

FROM

     ad_ad_summary_for_pos_eq sum4eq

INNER  JOIN  ( SELECT id, NAME FROM wlw_area WHERE grade = 2 ) area  ON  area.id = position.city_id

避免使用SELECT *　

         你从你的表里返回每个列，甚至后期加的列。被传递到了线路上并被JDBC驱动器加载到 了内存中。如果你知道你只需要2-3列数据的话，这就造成了严重的IO和内存的浪费，对数据库执行计划的不良影响。 《如何阻止SELECT * 语句》  http://t.cn/RGmpdoJ ，《Java开发者写SQL时常犯的10个错误》 http://t.cn/RGm05ex

使用IN代替OR 

        两者结果是一样的，IN执行的速度比OR快，or的效率是n级别， in的消息时log(n)级别 , in的个数建议控制在200以内《MySQL5.5 从零开始学》P197

如果可以尽量使用UNION ALL替代UNION

        UNION ALL执行所需要的时间少，确定查询结果中不会有重复的或不需要去重的优先使用ALL，《MySQL5.5 从零开始学》P223

 

OR改写为UNION

mysql的索引合并很弱智
select id from t where phone = ’159′ or name = ‘john’;
=>
select id from t where phone=’159′
union
select id from t where name=’jonh’

批量INSERT插入多条转为一条（分批次）

INSERT  INTO  t (id,  name )  VALUES (2, 'Belle' );

INSERT  INTO  t (id,  name )  VALUES (3, 'Bernice' );

----->

INSERT  INTO  t (id,  name )  VALUES (1, 'Bea' ), (2, 'Belle' ),(3, 'Bernice' );

分批次批量更新

        程序可以一次执行多条update语句

禁止不必要的ORDER BY排序

SELECT  count (1)  FROM  user  u  LEFT  JOIN  user_info i  ON  u.id = i.user_id  WHERE  1 = 1  ORDER  BY  u.create_time  DESC ;

----->

SELECT  count (1)  FROM  user  u  LEFT  JOIN  user_info i  ON  u.id = i.user_id;

分组统计可以禁止排序

SELECT  goods_id, count (*)  FROM  t  GROUP  BY  goods_id;

默认情况下，MySQL对所有 GROUP  BY  col1，col2...的字段进行排序。如果查询包括 GROUP  BY ，想要避免排序结果的消耗，则可以指定 ORDER  BY  NULL 禁止排序。

----->

SELECT  goods_id, count (*)  FROM  t  GROUP  BY  goods_id  ORDER  BY  NULL ;

避免随机取记录

SELECT  *  FROM  t1  WHERE  1=1  ORDER  BY  RAND() LIMIT 4;

MySQL不支持函数索引，会导致全表扫描

----->

SELECT  *  FROM  t1  WHERE  id >= CEIL(RAND()*1000) LIMIT 4;    

不使用子查询而使用关联查询

    SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');修改为：

    SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

       子查询虽然可以使查询语句很灵活，但是执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。所以子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

        在MySQL中，可以使用JOIN查询替代子查询。JOIN查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能会更好。JOIN之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成查询工作。

避免函数索引

    SELECT * FROM t WHERE YEAR(d) >= 2016;修改为：

    SELECT * FROM t WHERE d >= '2016-01-01';

避免数据类型不一致

    SELECT * FROM t WHERE id = '19';

    SELECT * FROM t WHERE id = 19;

使用load data导数据

         load data比insert快约20倍

---

参考：

1. 《MySQL5.5 从零开始学》

2. 赶集mysql军规

3.  http://t.cn/RGFvnbY 【SQL语句常见优化十大案例】