基础 SQL 调优技巧

星号（＊）与字段的区别, SELECT* 不会走覆盖索引，会出现大量的回表操作，而从导致查询 sql 的性能很低。很多时候用 EXISTS 代替 IN 是一个好的选择： exists

SQL 语句旨在搜索出所有下过单的会员


SELECT FROM user WHERE id exists (SELECT FROM order WHERE user.id = order.user_id)
exists 会优先查询外表的数据来进行与内表的匹对

 result=[];
users = "SELECT * FROM user";
for(i=0;i<users.length;i++){
  if(exists(users[i].id)){
    // 执行 SELECT _ FROM order WHERE user.id = order.user_id$result[] =users[i];
    }
}

用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值 True 或 False 当子查询返回为真时，则外层查询语句将进行查询, 当子查询返回为假时，外层查询语句将不进行查询或者查询不出任何记录* IN SELECT * FROM user WHERE id in (SELECT user*id FROM order) IN 会先缓存内部的结果集到内存中等待与外表的对比 result=[]; users = “SELECT * FROM user”; orders=" SELECT userid FROM ‘order‘"; for(i = 0;$i < users.length;i++){ for(j=0;j < orders.length;j++){ // 此过程为内存操作，不涉及数据库查询。 if(users[i].id == orders[j].user_id){ $result[] =users[i]; break; } } } IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN select id from t where num between 1 and 3 IN 中值太多用连接查询代替子查询

反例

SELECT _ FROM order WHERE user_id IN (SELECT id FROM user WHERE status = 1)

正例

SELECT o._ FROM order o INNER JOIN user u ON o.user\*id = u.id WHERE u.status = 1

在 IN 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数 IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN SELECT id FROM t WHERE num BETWEEN 1 AND 3 LIMIT 的用法应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num is null join 的表不宜过多 mysql 在选择索引的时候会非常复杂,很容易选错索引,并且如果没有命中,nested loop join 就是分别从两个表读一行数据进行两两对比,复杂度是 n^2,所以我们应该尽量控制 join 表的数量,目前根据阿里的规范的话，一般默认是 3 个表避免 OR 的使用, 在 WHERE 子句中使用 OR 来连接条件，基本上会导致引擎放弃使用索引而进行全表扫描，我们可以使用 UNION 来进行合并查询

SELECT id FROM t WHERE num = 10
UNION ALL
SELECT id FROM t WHERE num = 20

应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及左侧的%LIKE 使用表的别名：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误

反例

SELECT a,b,c,d FROM tm1 INNER JOIN tm2 ON a = c WHERE b = 1

正例

SELECT tm1.a, tm1.b, tm2.c, tm2.d FROM temp1 tm1 INNER JOIN temp2 tm2 ON tm1.a = tm2.c WHERE tm1.b = 1 条件左置，尽量编写编译时期就可以确定的条件下列 SQL 条件语句中的列都建有恰当的索引，但执行速度却非常慢：

SELECT * FROM record WHERE substrINg(card*no, 1, 4) = '5378'
SELECT _ FROM record WHERE amount/30 < 1000
SELECT _ FROM record WHERE convert(char(10), date, 112) = '19991201'

分析：

WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样： SELECT * FROM record WHERE card*no like ‘5378%’ SELECT FROM record WHERE amount < 100030 SELECT * FROM record WHERE date = ‘1999/12/01’ 提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉

反例

SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’;

正例

SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP BY JOB 在适当的情形下使用 GROUP BY 而不是 DISTINCT，在 WHERE、GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引简单，不在多个索引中包含同一个列 SQL 关键字用大写，除了辨识规范以外， Oracle 总是先解析 SQL 关键字，把小写的字母转换成大写的再执行临时表是特殊的表，只会存在与内存中，会话结束后会自动销毁尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理比较明显易犯的 SQL 条件顺序错误


SELECT count(\_) FROM task WHERE status=2 AND operator_id=20839 AND operate_time>1371169729 AND operate_time<1371174603 AND type=2;
SELECT count(\*) FROM task WHERE operate_time BETWEEN 1371169730 AND 1371174602 AND operator_id = 20839 AND status = 2 AND type = 2;
优化示例
-- 优化前
select distinct cert.emp_id from cm_log cl inner join
(
select emp.id as emp_id, emp_cert.id as cert_id from employee emp left join emp_certificate emp_cert on emp.id = emp_cert.emp_id where emp.is_deleted=0
) cert on ( cl.ref_table='Employee' and cl.ref_oid= cert.emp_id ) or (cl.ref_table='EmpCertificate' and cl.ref_oid= cert.cert_id)
where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00';
-- 优化后
select emp.id from cm_log cl inner join employee emp on cl.ref_table = 'Employee' and cl.ref_oid = emp.id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0
union
select emp.id from cm_log cl inner join emp_certificate ec on cl.ref_table = 'EmpCertificate' and cl.ref_oid = ec.id inner join employee emp on emp.id = ec.emp_id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0

优化成本：硬件>系统配置>数据库表结构>SQL 及索引优化效果：硬件<系统配置<数据库表结构<SQL 及索引