MySQL查询优化之一-WHERE语句优化，mysql-where

MySQL查询优化之一-WHERE语句优化

---

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：12951803

环境：
MySQL版本：5.5.15
操作系统：windows

本文讨论WHERE语句的优化。 这些示例使用SELECT语句，但是相同的优化适用于DELETE和UPDATE语句中的WHERE语句。

查询速度比较快的一些示例：

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

您可能会试图重写您的查询来加快算术运算，同时牺牲可读性。 因为MySQL会自动进行类似的优化，所以通常可以避免这种工作，并将查询留在更易于理解和维护的形式。 MySQL执行的一些优化如下：

1.删除不必要的括号

Removal of unnecessary parentheses

减少语法分许的or和and树层，减少cpu消耗

   ((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.常量传递

Constant folding

尽量不使用变量

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

3.消除无用的SQL条件

Constant condition removal (needed because of constant folding)

   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

4.被索引使用的常量表达式只被计算一次

Constant expressions used by indexes are evaluated only once

5.COUNT(*)优化

COUNT（*）在没有WHERE的单个表上时直接从MyISAM和MEMORY表的表信息中检索。 当仅与一个表使用时，这也适用于任何NOT NULL表达式。
对于诸如InnoDB之类的事务性存储引擎，存储确切的行数是有问题的，因为可能正在发生多个事务，每个事务都可能影响计数。

6.尽早检测无效的常量表达式。

MySQL快速检测到一些SELECT语句是不可能的，并且不返回任何行。

Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.

7.尽量将WHERE、HAVING合并

如果不使用GROUP BY或集合函数（COUNT()，MIN()等），尽量将HAVING与WHERE合并。

HAVING is merged with WHERE if you do not use GROUP BY or aggregate functions (COUNT(), MIN(), and so on).

select * from t1 (select*from tab where id>10) as t2 where t1.age>10 and t2.age<25;
-> select*from t1,tab as t2 where t1.age>10 and t2.age<25 and t2.id>10;

具体步骤：

1) from与from合并，修改相应参数
2) where与where合并，用and连接
3) 修改相应的谓词（in改=）

8.对于join中的每个表，构造一个更简单的WHERE，以获得表的快速WHERE评估，并尽快跳过行。

For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.

9.在查询中的任何其他表之前首先读取所有常量表。

常数表是以下任一项：
1） 一张空表或者仅有一行数据的表
2）与主键或唯一索引中的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并被定义为NOT NULL。
A table that is used with a WHERE clause on a PRIMARY KEY or a UNIQUE index, where all index parts are compared to constant expressions and are defined as NOT NULL.

以下所有表格均用作常量表格：

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

10.优化连接组合

尽可能找到join表的最佳连接组合。 如果ORDER BY和GROUP BY子句中的所有列都来自同一个表，则在加入时首先选择该表。

11.ORDER BY、GROUP BY与临时表

如果存在ORDER BY子句和不同的GROUP BY子句，或者ORDER BY或GROUP BY包含来自join队列中第一个表以外的表的列，则会创建一个临时表。

12.SQL_SMALL_RESULT修饰符

如果使用SQL_SMALL_RESULT修饰符，则MySQL使用内存中的临时表

13.使用最佳索引

查询每个表的索引，并使用最佳索引，除非优化器认为使用表扫描更高效。 有一次，根据最佳索引是否超过了表格的30％来使用扫描，但是固定百分比不再决定使用索引或扫描之间的选择。 优化器现在更加复杂，并且根据附加因素（如表大小，行数和I/O块大小）进行估计。

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

14.索引数字列

在某些情况下，MySQL甚至可以从索引中读取行，而无需询问数据文件。 如果索引中使用的所有列都是数字，则仅使用索引树来解析查询。

In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.

15.在输出每行之前，跳过与HAVING子句不匹配的行

16.优化选择条件的排列顺序

把能过滤更多数据的条件放在前面,过滤少的条件放后面

MySQL左到右的顺序处理条件,把能过滤更多数据的条件放在前面,过滤少的条件放后面

 select * from employee
where salary >1000     --条件1,过滤的数据较少
and   dept_id='01'    --条件2,过滤的数据比条件1多

上面的SQL就不符合我们的原则了,应该把过滤数据更多的条件放在前面,因此改为下面这样更好

select * from employee
where   dept_id='01'     --过滤更多数据的条件放在前面
and   salary > 1000

---

Reference：
https://dev.mysql.com/doc/refman/5.5/en/where-optimization.html

---

觉得文章对你有帮助，可以用微信扫描二维码捐赠给博主，谢谢！

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：12951803

查看评论