继上篇记录索引，order by、group by优化，继续记录分页、关联查询等等的优化。

分页查询优化（limit）

select * from employees order by name limit 100000,10;

此sql看似查询10条数据，其真实是查询了100010条数据，随后抛弃前100000条数据，保留最后10条数据。

mysql分组查询字段忽略分组_字段

type为ALL表示全表扫描而且Extra出现Using filesort，此时需要优化尽量让他走索引。我们可以先通过子查询（尽量使用索引）先得到主键id，再使用主键id查询所有的行数据。

SELECT m.* FROM (select id from employees ORDER BY name limit 100000,10) e INNER JOIN employees m ON e.id=m.id;

mysql分组查询字段忽略分组_mysql_02

先执行id=2的语句也就是子查询，通过走联合索引name排序字段时只返回id，就能保证一定会走联合索引，在之后就可以将返回的id视为常量匹配employees中id来走主键索引拿到所有的数据。

优化后的sql都会走索引，而原先sql不走索引还使用文件排序。

join关联查询优化

MySQL的表关联算法有两种：

1、嵌套循环连接算法（Nested-Loop Join简称：NLJ）

2、基于块的嵌套循环连接算法（Blocked Nested-Loop Join简称：BNL）

创建表以及插入数据演示

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
#t1表中插入10000条数据
CREATE  PROCEDURE insert_t1()
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= 10000 ) DO
      INSERT INTO `explain_test`.`t1` (`a`, `b`) VALUES (i, i);
			set i=i+1;
    END WHILE;
END
call insert_t1();
#t2表中插入100条数据
DROP PROCEDURE insert_t2 if EXISTS;
CREATE  PROCEDURE insert_t2()
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= 100 ) DO
      INSERT INTO `explain_test`.`t2` (`a`, `b`) VALUES (i, i);
			set i=i+1;
    END WHILE;
END
call insert_t2();

嵌套循环连接算法--NLJ

t1和t2进行关联查询。 

mysql分组查询字段忽略分组_mysql_03

从执行计划得知，t1和t2表关联执行时，id相等但是排在前面的表先执行

注意这里执行的sql使用的关联是inner join并不代表t1驱动t2，mysql优化器会选择小表作为驱动表提供执行效率，t2是小表用来做驱动表，t1是被驱动表。而left join时，左表作为驱动表，右表作为被驱动表；right join时，右表作为驱动表，左表作为被驱动表。

该sql执行大致流程：先从t2取一条数据，拿t2.a字段去t1中idx_a索引中查找对应的数据，返回客户端；一直往返执行直到t2表中100行数据取完。（前提条件是t1中a字段创建了索引，否则会使用BNL算法）这里相当于扫描了200次

基于块的嵌套循环连接算法--BNL

通过使用b字段来替换a字段进行测试，这里b字段没有创建索引。 

mysql分组查询字段忽略分组_字段_04

从执行计划得知，这里都没有使用索引。而且Extra中出现了Using join buffer（Block Nested Loop）说明使用的算法为BNL，如果没有出现Using join buffer就说明使用的是NLJ算法。

该sql执行的大致流程：先将t2表中的数据全部加载到join buffer内存中，然后从t1表中一条一条的取数据在join buffer中与t2表的数据一一对比，最后合并符合条件的数据返回

整个过程相当于对t1和t2对进行了一次扫描，共扫描了10100次。如果join buffer内存不够放不下t2表中所有的数据，mysql会分段取数据，第一次取一段数据进行比较得到结果集，然后清楚join buffer取第二段数据知道结束，这就相当于对t1表多了（分段次数-1）次的扫描。

in和exists优化

原则：小表驱动大表，即小的数据集驱动大的数据集

t2表的数据集小于t1表的数据集，使t2表驱动t1表，in的使用

select * from t1 where id in (select id from t2)

exists的用法

SELECT * from t2 where exists (SELECT id from t1 WHERE t1.id=t2.id);

EXISTS (subquery)只返回TRUE或FALSE

count(*)

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

四个sql的执行计划一样，说明这四个sql执行效率应该差不多

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。

为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。

 注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行