查询优化

1.单表使用索引常见的索引失效
2.关联查询优化
3.子查询优化

1.单表使用索引常见的索引失效

1. 全值匹配我最爱

1.1 有以下 SQL

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

1.2 建立索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

结论：全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到！

2. 最佳左前缀法则

3. 不要在索引列上做任何计算

不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。

3.2 在查询列上做了转换

create index idx_name on emp(name);
explain select sql_no_cache * from emp where name='30000';
explain select sql_no_cache * from emp where name=30000;

字符串不加单引号，则会在 name 列上做一次转换！

4. 索引列上不能有范围查询

存储引擎不能使用索引中范围条件右边的列，即范围之后全失效

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';

5. 尽量使用覆盖索引

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';

6. 使用不等于(!= 或者<>)的时候

mysql 在使用不等于(!= 或者<>)时，会无法使用索引会导致全表扫描。

7. 字段的 is not null 和 和 is null

当字段允许为 Null 的条件下：

8. like 的前后模糊匹配

LIKE 百分写最右

问题：解决like '%字符串%'时索引不被使用的方法？？

• 用覆盖索引

9. 减少使用 or

使用 union all 或者 union 来替代：

口诀：
全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写 *；
不等空值还有 OR，索引影响要注意；
VAR 引号不可丢，SQL 优化有诀窍。

一般性建议：

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

2.关联查询优化

1. 建表语句

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
......

2. 案例

2.1 left join

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

如何优化？在哪个表上建立索引？

ALTER TABLE `book` ADD INDEX idx_card( `card`);

删除 book 表的索引：drop index idx_card on book;
在 class 表上建立索引：alter table class add index idx_card(card);

结论：
①在优化关联查询时，只有在被驱动表上建立索引才有效！
②left join 时，左侧的为驱动表，右侧为被驱动表！要小表(驱动表)驱动大表(被驱动表)。

小表驱动大表的思想：

2.2 inner join
①

EXPLAIN SELECT * FROM book inner join class on class.card=book.card;

②两个查询字段调换顺序，发现结果也是一样的！



2.3 四个关联查询案例分析

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,

PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,

PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#增加掌门字段
ALTER TABLE `t_dept` add CEO INT(11) ;

EXPLAIN SELECT ed.name '人物',c.name '掌门' FROM
(SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed
LEFT JOIN t_emp c on ed.ceo= c.id;

EXPLAIN SELECT e.name '人物',tmp.name '掌门'
FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp
ON e.deptId=tmp.did;

上述两个案例，第一个查询效率较高，且有优化的余地。第二个案例中，子查询作为被驱动表，由于子查询是虚表，无法建立索引，因此不能优化。

结论：
子查询尽量不要放在被驱动表，有可能使用不到索引；
left join时，尽量让实体表作为被驱动表。

EXPLAIN SELECT e1.name '人物',e2.name '掌门' FROM t_emp e1
LEFT JOIN t_dept d on e1.deptid = d.id
LEFT JOIN t_emp e2 on d.ceo = e2.id ;

Explain SELECT e2.name '人物',
(SELECT e1.name FROM t_emp e1 where e1.id= d.ceo) '掌门'
from t_emp e2 LEFT JOIN t_dept d on e2.deptid=d.id;

结论：能够直接多表关联的尽量直接关联，不用子查询！

关联查询优化建议：
1、保证被驱动表的join字段已经被索引

• 被驱动表 join 后的表为被驱动表 (需要被查询)

2、left join 时，选择小表作为驱动表，大表作为被驱动表。

• left join 时一定是左边是驱动表，右边是被驱动表

3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

• mysql 自动选择。小表作为驱动表。因为 驱动表无论如何都会被全表扫描？。所以扫描次数越少越好

4、子查询尽量不要放在被驱动表，有可能使用不到索引。

select a.name ,bc.name from t_emp a left join
 (select b.id , c.name from t_dept b inner join t_emp c on b.ceo = c.id) 
 bc on bc.id = a.deptid.

上段查询中用到了子查询，必然 bc 表没有索引。肯定会进行全表扫描上段查询 可以直接使用 两个 left join 优化

select a.name, c.name from t_emp a
    left outer join t_dept b on a.deptid = b.id
    left outer join t_emp c on b.ceo=c.id

所有条件都可以使用到索引

若必须用到子查询，可将子查询设置为驱动表，因为驱动表的type 肯定是 all，而子查询返回的结果表没有索引，必定也是all

3.子查询优化

表关联是可以利用两个表的索引的，如果是用子查询，至少第二次查询是没有办法使用索引的。

SQL中优化中有一条：将子查询转换为连接查询。
原因：子查询会多次运算查询，而连接查询利于优化器优化，而且可以使用多线程查询各个连接子句。

子查询
在某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询
为了给主查询提供数据而首先执行的查询被叫做子查询
用于子查询的关键字主要包括 in、not in、=、<> 等

1、如果只需要返回 一个表的数据，建议用exists 或者in。
2、如果要返回2个或多个表的数据，那么就用关联

1. 案例

取所有不为掌门人的员工，按年龄分组！

select age as '年龄', count(*) as '人数' from t_emp where id not in
(select ceo from t_dept where ceo is not null) group by age;

如何优化？
①解决 dept 表的全表扫描，建立 ceo 字段的索引：

此时，再次查询：

②进一步优化，替换 not in。
上述 SQL 可以替换为：

select age as '年龄',count(*) as '人数' from emp e left join dept d on e.id=d.ceo where d.id is null group by age;

结论： 在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx is null 代替。

用in 还是 exists?

有索引的情况下 用 inner join 是最好的 其次是 in ，exists最糟糕

无索引的情况下用 小表驱动大表 因为join 方式需要distinct，没有索引distinct消耗性能较大
所以 exists 性能最佳 in其次 join性能最差？

无索引的情况下大表驱动小表 in 和 exists 的性能应该是接近的 都比较糟糕 exists稍微好一点 超不过5% 但是inner join 优于使用了 join buffer 所以快很多
如果left join 则最慢

常见的 Join 查询图