MySQL怎样优化WHERE子句_数据库技巧

在每个记录被输出前，那些不匹配HAVING子句的行将被跳过。

下面是一些快速的查询例子：

mysql> SELECT COUNT(*) FROM tbl_name;

mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

mysql> SELECT MAX(key_part2) FROM tbl_name

WHERE key_part_1=constant;

mysql> SELECT ... FROM tbl_name

ORDER BY key_part1,key_part2,... LIMIT 10;

mysql> 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;

mysql> SELECT COUNT(*) FROM tbl_name

WHERE key_part1=val1 AND key_part2=val2;

mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

下列查询使用索引以排序顺序检索，不用一次另外的排序：

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...

mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...