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)
  

 

  
1
  
2
 

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
  

 

  
1
  
2
  
3
  
4
  
5
  
6
 

3.排序和聚合

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

 

  
1
 

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, ... ;
  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
 

文章来源: wretchant.blog.csdn.net，作者：简简单单OnlineZuozuo，版权归原作者所有，如需转载，请联系作者。

原文链接：wretchant.blog.csdn.net/article/details/78002437