环境搭建
#查询category_id为1且comments大于1的情况下,views最多的article_id
explain select id,author_id
from article
where category_id=1 and comments>1
ORDER BY views
limit 1;
Explain:
type为ALL
Extra:Using filesort ;Using where
初步优化
#为category_id,comments,views字段创建复合索引
create index idx_ccv on article(category_id,comments,views);
Explain:
type从ALL变为range,但是Extra仍然有Using filesort。可是我们已经创建索引了,为什么还会进行文件排序。
我们对查询语句进行修改:
explain select id,author_id
from article
where category_id=1 and comments=1//将>1改为=1
ORDER BY views
limit 1;
Explain:
我们可以发现type从range变为了ref,而且Extra没有了Using filesort。其实这两者的变化涉及到了索引失效的知识点。因为索引失效,才导致了file sort。
索引失效
按照BTree索引的工作原理,
#先排序category_id,再排序comments,如果遇到相同的comments,再排序views(就类似于上楼梯,需要1楼1楼按顺序往上爬一样)
#因comments>1条件是一个范围值(所谓range)
#MySQL无法再利用索引对后面的views部门进行检索,即range类型查询字段后面的索引失效
二次优化
因为comments是一个范围,不适合建立索引,我们直接对category_id和views建立索引就好。
create index idx_cv on article(category_id,views);
Explain:
这样就成功实现了优化。
多表索引优化
两表联合时会有左外连接,以及右外连接。当为左外连接时,由于左表一定会全部导入,所以只需要关心右表,将索引添加到右表即可;右外连接也是同样道理。
小结
Join语句的优化,
- 尽可能较少Join语句的NestedLoop的循环总次数;“永远用小结果集驱动大的结果集”(驱动的一方相当于左右外连接中全部导入的一方)
- 优先优化NestedLoop的内层循环
- 保证Join语句被驱动表上的Join条件字段已经被索引
- 当无法保证被驱动表上的Join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置
SQL语句优化
-
不要把SELECT子句写成 SELECT *
SELECT * FROM t_emp;
-
谨慎使用模糊查询
SELECT ename FROM t_emp WHERE ename LIKE '%S%'; #不使用索引 SELECT ename FROM t_emp WHERE ename LIKE 'S%';
-
对ORDER BY排序的字段设置索引
-
少用IS NULL
索引是二叉树,NULL值无法进行排序,所以不会记录在索引里面。SELECT ename FROM t_emp WHERE comm IS NULL; #不使用索引 SELECT ename FROM t_emp WHERE comm =-1;
-
尽量少用 != 运算符
索引是二叉树,大于、小于、等于可以使用到二叉树机制,但是!=没办法应用二叉树机制
SELECT ename FROM t_emp WHERE deptno!=20; #不使用索引
SELECT ename FROM t_emp WHERE deptno<20 AND deptno>20;
-
尽量少用 OR 运算符
OR前面的字段会使用索引,OR后面的字段不会使用索引SELECT ename FROM t_emp WHERE deptno=20 OR deptno=30; #不使用索引 SELECT ename FROM t_emp WHERE deptno=20 UNION ALL SELECT ename FROM t_emp WHERE deptno=30;
-
尽量少用 IN 和 NOT IN 运算符
SELECT ename FROM t_emp WHERE deptno IN (20,30); #不使用索引 SELECT ename FROM t_emp WHERE deptno=20 UNION ALL SELECT ename FROM t_emp WHERE deptno=30;
-
避免条件语句中的数据类型转换
SELECT ename FROM t_emp WHERE deptno='20';
-
在表达式左侧使用运算符和函数都会让索引失效
SELECT ename FROM t_emp WHERE salary*12>=100000; #不使用索引 SELECT ename FROM t_emp WHERE salary>=100000/12; SELECT ename FROM t_emp WHERE year(hiredate)>=2000; #不使用索引 SELECT ename FROM t_emp WHERE hiredate>='2000-01-01 00:00:00';