深入MySQL优化-索引优化

环境搭建

#查询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';