Mysql性能优化

Mysql性能优化 - where子句优化

1.删除不必要的括号

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))-> (a AND b AND c) OR (a AND b AND c AND d)

2.去掉恒定条件

   (a<b AND b=c) AND a=5-> b>5 AND b=c AND a=5   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)-> B=5 OR B=6

3.排序和聚合

如果 order by 和 group by 的所有列来自于同一个表，则优先使用该表

4.一些简单的快速查询

SELECT COUNT(*) FROM tbl_name;SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;SELECT MAX(key_part2) FROM tbl_name  WHERE key_part1=constant;SELECT ... FROM tbl_name  ORDER BY key_part1,key_part2,... LIMIT 10;SELECT ... FROM tbl_name  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;// 假设索引是一个数字， mysql仅仅只会使用索引树来进行查询SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;SELECT COUNT(*) FROM tbl_name  WHERE key_part1=val1 AND key_part2=val2;SELECT key_part2 FROM tbl_name GROUP BY key_part1;// 只通过索引进行排序，而不进行单独的排序过程SELECT ... FROM tbl_name  ORDER BY key_part1,key_part2,... ;SELECT ... FROM tbl_name  ORDER BY key_part1 DESC, key_part2 DESC, ... ;