5.分页、join、in、exists、count查询优化

CREATE TABLE 'employees'( 
    'id' int(11) NOT NULL AUTO INCREMENT, 
    'name' varchar(24) NOT NULL DEFAULT '' COMMENT'姓名', 
    'age' int(11) NOT NULL DEFAULT 'O' COMMENT '年龄', 
    'position' varchar(28) NOT NULL DEFAULT '' COMMENT'职位', 
    'hire_time' timeStamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间', 
  PRIMARY KEY('id'), 
  KEY 'idx_name_age_position'('name','age','position')USING BTREE )ENGINE=InnODB_AUTO_INCREMENT=4 DEFAULT CHARSET=Utf8 COMMENT='员工记录表'; 
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NoW()); INSERT INTO employees(name,age,position,hire_time) VALUES( 'HanMeimei', 23, 'dev' ,NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23, 'dev',NOW()); 
‐‐ 插入一些示例数据 
drop procedure if exists insert_emp; 
delimiter ;; 
create procedure insert_emp();
  begin 
    declare i int; 
    set i=1; 
    while(i<=100000)do
      insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev'); 
      set i=i+1; 
    end while; 
  end;; 
  delimiter ; 
call insert_emp()

一、分页查询优化

SELECT * FROM employees limint 100000,10

这种写法也是我们常用的写法，看似只查出了1000001到1000010这10条数据，其实mysql内部是先全表排序，然后

先获取到前1000010条数据，然后把前1000000条数据截取掉，取剩下10条，这样要查询一个非常大的表，执行效率非常低。

如何优化呢？

1.根据主键自增且连续进行分页查询

常规查询

主键自增且连续查询

使用explain对比看下效果

从上面我们可以看出，两次查询结果一样，但是下面的查询效率远远高于上面查询；本身索引id就是聚簇索引，定位快、分为广，直接根据主键等于90001开始查询后面5条数据；

但是这样很不实用，如果我们前面删除一条数据，id就不是连续的了，查询的结果会发生变化；

所以这种写法只有满足两个条件才能使用：

主键连续且自增；

结果必须是按照主键进行排序的；

如何进行优化呢？

2.根据非主键字段的分页查询

-- 根据非主键查询 select * from employees ORDER BY name limit 90000,5; EXPLAIN select * from employees ORDER BY name limit 90000,5

mysql优化器内部会自动优化，根据索引第一个字段范围查询，会进行全表扫秒，效率也会很低下，会使用全表扫秒，

知道不走索引，那应该如何优化呢？

排序时返回的字段尽可能少，并且走索引；我们可以先查出主键，然后拿着少量的主键去聚簇索引查找；

修改sql如下

SELECT * FROM employees e inner join (select id from employess order by name limit 90000,5) ed on e.id = ed.id --- 查询的结果和之前一致；执行时间减少了一半；
 Explain SELECT * FROM employees e inner join (select id from employess order by name limit 90000,5) ed on e.id = ed.id;

从explain执行结果来看：

id=2，select_type=derived优先执行了子查询，并且使用了索引。将少量的ID查出后，使用id回表快速查询；key_len=140是为什么？因为这里不仅仅用的是name字段，是遍历了整个索引。

第一行id=1，虽然使用了全表扫面，table=derived2 说明了时走了这张衍生表的所有数据的全盘扫秒，衍生表只有5条数据。即使全盘扫秒也会很快。

第二行id=1,这里使用eq_ref减产查询，效率也会很快了。

二、JOIN查询优化

‐‐ 示例表：

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表插入1万行记录 
drop procedure if exists insert_t1; 
delimiter ;; 
create procedure insert_t1();
  begin declare i int; 
    set i=1; 
    while(i<=10000)do 
      insert into t1(a,b) values(i,i); 
      set i=i+1; 
    end while; 
  end;; 
delimiter call insert_t1(); 
‐‐ 往t2表插入100行记录 
drop procedure if exists insert_t2;
delimiter ;; 
create procedure insert_t2();
  begin declare i int; 
    set i=1; 
    while(i<=100)do 
      insert into t2(a,b) values(i,i); 
      set i=i+1; 
    end while; 
  end;; 
 delimiter ; 
call insert_t2();

这里要讲mysql2中关联方式：Nested-Loop Join (NLJ)和Block-Nested-Loop Join (BNL)

1.嵌套循环连接Nested-Loop Join (NLJ)

一次一次的循环一张表，从第一张执行的表（驱动表）中读取行，从每行的数据中获取关联字段，根据每行关联字段的数据再从另外一张表（被驱动表）里取出满足条件的行数据，然后取出两张表的结果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

    根据explain执行结果可以看出，先执行表t2(驱动表)的数据，在执行表t1（被驱动的表）的数据。通常mysql内部会做一次优化，将数据量小的表作为驱动表，数据量大的表作为被驱动表，所以排在inner join 之后的表不一定是驱动表。通常就是我们所说的小表驱动大表。

    当使用left join时，左边的时驱动表，右边时被驱动表。

    当使用right join时，左边的是是被驱动表，右边的表是驱动表；

    当使用join时，mysql一般会选择数量较小的表作为驱动表；

    当使用了NLJ算法，一般join语句中，explain信息中，当Extral列未出现Using join buffer，则表示使用的NLJ算法。

分析上面的SQL流程：

    1.Id相同，先执行上面的语句，即从t2中读取一行数据；

    2.从第一步中获取的行数据中取出关联字段a,到t1中去查找

    3.将t1和t2获取的数据汇总到一起。

    4.重复以上步骤。

    时间复杂度 100 + 100=200 次；从t2表中读取100行数据即100次，拿着t2表中100行对应的100个a字段值，去t1表中查找相对应的行数据也是100次，总共100+100=200次。

    如果驱动表的关联字段没有索引，使用NLJ效率也不会高。会选择使用BNL算法。

2. 基于块的嵌套循环连接Block Nested-Loop Join(BNL)

    把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

上面sql的大致流程如下：

    1. 把 t2 的所有数据放入到 join_buffer 中

    2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比

    3. 返回满足 join 条件的数据

    整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。

    这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空  join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

    被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。

对于关联sql的优化

    关联字段加索引，让mysql做join操作时尽量选择NLJ算法

    小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：

    straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

对于小表定义的明确

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

三、In和Exists优化

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

in：当B表的数据集小于A表的数据集时，in优于exists 

select * from A where id in (select id from B)

exists：当A表的数据集小于B表的数据集时，exists优于in

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id)

1.EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别

2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

四、Count查询优化

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

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

四个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版本才优化)

五、常见优化方法

1. 查询mysql自己维护的总行

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算

对于innodb存储引擎的表mysql不会存储表的总记录行数，查询count需要实时计算

2. show table status

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高，但是行数不一定准确。

3. 将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

4、增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作