在关系型数据库中，除前期对数据库的物理设计、关系规范化等方面进行优化外，一个简单直接有效的方法是对SQL语句进行调整，进行优化来减少计算量和内存需求，提高响应速度。

本篇文章将讨论mysql如何优化where子句

1.where条件的顺序（*）

在网上有很多的博客都说数据库执行where条件的的顺序都是从左往右执行，所以把过滤数据多的的条件放左边，过滤数据少的条件放右边，从而达到提高查询的效率。

其实这个观点是不完全正确的

通常数据库选择从左到右进行计算，还是从右到左进行计算，是由数据库软件自行决定的。如果数据库认为优先计算其他表达式可以提高计算速度，那么他可以自行选择求值顺序。

代码示例

首先创建一个测试表： t_testWhere

 CREATE TABLE t_testWhere( -- 学生编号 studentId INT PRIMARY KEY AUTO_INCREMENT, -- 创建了唯一索引（主键） -- 学生姓名 studentName VARCHAR(20), -- 学生性别 studentGender CHAR(3), -- 学生年龄  studentAge INT, -- 家庭住址 studentAddress VARCHAR(255), -- 所在班级 classId INT )ENGINE=MYISAM,DEFAULT CHARSET=utf8

添加索引

 CREATE INDEX idx_name ON t_testWhere(studentName); CREATE INDEX idx_address ON t_testWhere(studentAddress); CREATE INDEX idx_classid ON t_testWhere(classid);

添加的数据

 DELIMITER $  CREATE PROCEDURE pro_whereDatas() BEGIN DECLARE num INT; DECLARE age INT; DECLARE result INT; DECLARE gender CHAR(3); DECLARE address VARCHAR(25); DECLARE classid INT; SET num:=1; WHILE num <= 8000000 DO  -- 设置年龄  SET age:= 18 + CEIL(RAND()*15); -- 设置性别 SET result:= CEIL(RAND()*100);  IF result < 50 THEN SET gender:="男"; ELSE  SET gender:="女"; END IF;  -- 设置城市 SET result:= CEIL(RAND()*100);  IF result < 25 THEN SET address:="成都市"; ELSEIF result<50 THEN SET address:="绵阳市"; ELSEIF result<75 THEN SET address:="昆明市"; ELSE  SET address:="贵阳市" ; END IF;  -- 设置班级编号 SET classid:=CEIL(RAND()*4); INSERT INTO t_testWhere(studentName,studentGender,studentAge,studentAddress,classId) VALUES(CONCAT('学生',num),gender,age,address,classid);  SET num:=num+1; END WHILE;  END $

从数据看：

查询所有男同学为 ：3920182（ 需要过滤 8000000-3920182=4079818 过滤数据少）

查询所在城市在成都市为 ：1920733(需要过滤 8000000-1920733=6079267 过滤数据多)

执行结果：

从查询结果看，改变顺序并没有提高查询的效率，

从sql语句执行计划上看，两条sql语句执行计划是一样的。

2. like 语句优化

使用like进行模糊查询时应注意,不要在关键词前加%（例如： %张 或则 %张%），%号加在关键词前面都无法使用索引，从而引发全表扫描.

如果必须使用%x% 可以考虑全文检索。

例如：（使用之前的 t_customer_two 表进行测试）

 SELECT customerName, customerGender FROM t_customer_two WHERE customerName LIKE '张%';

使用索引 idx_name

例如: % 在前 或则 %x%

SELECT customerName, customerGender FROM t_customer_two WHERE customerName LIKE '%张';

SELECT customerName, customerGender FROM t_customer_two WHERE customerName LIKE ‘%张%’;

type=ALL 未使用索引，进行全表扫描,效率低

3. 使用 nuion all 来替换 or 条件，避免进行全表扫描，降低效率。

例如:

查询客户年龄在20—25之间 或则是 省份是 四川的客户的信息。

使用 or ：

 SELECT customerName, customerGender,customerPhone FROM t_customer_two WHERE customerAge BETWEEN 20 AND 25 OR province='四川';

使用关键字 or 将导致无法使用索引，从而导致全表扫描

使用 nuion all 来替换 or 条件

 SELECT customerName, customerGender,customerPhone FROM t_customer_two WHERE customerAge BETWEEN 20 AND 25  UNION ALL  SELECT customerName, customerGender,customerPhone FROM t_customer_two WHERE province='四川';

从上面的结果看：

第一条 sql语句：能够使用索引idx_age，但是实际并没有使用该索引，可能是mysql优化器觉得使用全表扫描比使用索引快，所以它不会使用索引。

SELECT customerName, customerGender,customerPhone FROM t_customer_two WHERE customerAge BETWEEN 20 AND 25

但是第二条sql语句： 使用了索引，所以效率较快

SELECT customerName, customerGender,customerPhone FROM t_customer_two WHERE province='四川';

综合上述： 使用 union all 来代替 or，效率更高。

4. where子句使用 != 或 <>(不等于) 的优化.

在where子句中使用 ！= 或 <>操作符，将不会使用索引，会进行全表查询。

例如：

SELECT studentName,studentGender FROM t_testWhere WHERE studentAddress !='成都市';

可以修改为：

 SELECT studentName,studentGender FROM t_testWhere WHERE studentAddress ='昆明市' UNION ALL SELECT studentName,studentGender FROM t_testWhere WHERE studentAddress ='贵阳市';

5. 使用IN 或 NOT IN的优化

in 和 not in也要慎用，否则会导致全表扫描。

解决方法：

1. between 替换 in ( 如果 in 的条件是连续的)

例如：

SELECT studentName,studentAge FROM t_testWhere WHERE classid IN(1,2,3,4);替换为SELECT studentName,studentAge FROM t_testWhere WHERE classid BETWEEN 1 AND 4;

2. 用exists替代in、用not exists替代 not in (无论在哪种情况下,not in 都是最低效的 (因为它对子查询中的表执行了全表扫描))

3. left join 替换 in

例如：

6. 优化 is null

在where子句中使用 IS NULL 或 IS NOT NULL进行null值的判断，可能将导致引擎放弃使用索引而进行全表扫描

解决方案：

MySQL可以结合col_name（列名）= constant_value（常量）的使用 来对 col_name（列名） IS NULL进行相同的优化。

例如：

SELECT studentName,studentAge FROM t_testWhere WHERE classid IS NULL;替换为SELECT studentName,studentAge FROM t_testWhere WHERE classid= 0;（ classid（列名） = 0（ 常量））

7. 尽量不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统可能无法正确使用索引。

8 .在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引（最左前缀原则），否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

9. 任何地方都不要用 select from 表名 ，使用具体的字段列表替换”“，不要返回用不到的字段。

以上就是今天的知识分享啦~

如果大家有问题或者想了解更多的

欢迎加入成都Java交流群，群聊号码：319010197