mysql 性能优化（九）limit、 join（聚集索引、二级索引，辅助索引）、in、exsits、count优化策略

• 优化场景：当表数据非常大，百万、千万甚至更多，例如： select * from table limit 100000,10;随着limit的值越来越大，查询效率也越来越低。此sql结果是查询10条数据，实际上是从，所以查询分页越靠后的效率越低

1.1、若表id是连续自增的分页优化

如果表是中id是连续自增，没有间断的，可以直接用主键id当作查询条件

select * from employees where id > 100000 limit 10;

由mysql中数据结构B+树，主键存储是有序的，所以使用 id > 100000 即走了索引表，又优化了查询速度

实际中，mysql内部索引优化器也会对我们写的sql进行优化，可能
EXPLAIN select * from table limit 100000,10;
也使用了索引，实际是因为mysql内部对sql进行了优化的原因

1.2、根据非主键字段排序优化排序

假设表table中name是索引字段

select * from table ORDER BY name limit 90000,5;

可以如下方式写sql

需要的结果与原 SQL 一致，执行的时间可能减少了一半以上

可以执行EXPLAIN操作试试

• 虽然使用了衍生表，性能略有下降，但是子查询中使用了索引覆盖，完全走索引表，会非常高效的查询出分页的id衍生表。

mysql的表关联常见有两种算法

• Nested-Loop Join 算法
• Block Nested-Loop Join 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

2.3、嵌套循环连接 Nested-Loop Join(NLJ) 算法

• 优化器一般会优先选择小表做驱动表。所以使用 inner join 时，sql实际执行的排在前面的表并不一定就是驱动表。
• 尽量使用小表在前，大表在后
• 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下（假设t1是小表，t2是大表）：

1. 从表 t1 中读取一行数据；
2. 从第 1 步的数据中，取出关联字段 a，到表 t2 中查找；
3. 取出表 t2 中满足条件的行，跟 t1 中获取到的结果合并，作为结果返回给客户端；
4. 重复上面 3 步。

整个读取过程

1. 读取 t1 表的所有数据，然后遍历这每行数据中字段 a 的值（总共100次）
2. 根据 t1 表中 a 的值扫描 t2 表中的对应行(扫描100次 t2 表的，1次扫描可以认为最终只扫描 t2 表一行完整数据，也就是总共 t2 表也扫描了100 行)。
3. 因此整个过程扫描了 200 行，也就是2倍的t1表总数。

2.2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

• join_buffer：MySQL对于table join的一个重要的优化手段，用来缓存这两类 JOIN 检索的一个 BUFFER 内存区域块。在"暴力"连接的时候可以极大提高join查询的效率。

  • 如果应用中，很少出现join语句，则可以不用太在乎join_buffer_size参数的设置大小。
  • 如果join语句不是很少的话，个人建议可以适当增大join_buffer_size到1MB左右，如果内存充足可以设置为2MB或者根据实际情况综合考虑设置。

  每张使用 InnoDB 作为存储引擎的表都有一个特殊的索引称为聚集索引，它保存着每一行的数据，通常，聚集索引就是主键索引。为了得到更高效的查询、插入以及其他的数据库操作的性能，你必须理解 InnoDB 引擎是如何使用聚集索引来优化常见的查找和 DML 操作

  如果你的表定义了一个主键，InnoDB 就使用它作为聚集索引。因此，尽可能的为你的表定义一个主键，如果实在没有一个数据列是唯一且非空的可以作为主键列，建议添加一个自动递增列作为主键列。

  如果你的表既没有主键，又没有合适的唯一索引，InnoDB 内部会生成一个隐式聚集索引 —— GEN_CLUST_INDEX，该索引建立在由 rowid 组成的合成列上。数据行根据 InnoDB 分配的 rowid 排序，rowid 是一个 6 字节的字段，随着数据插入而单调递增。也就是说，数据行根据 rowid 排序实际上是根据插入顺序排序。

• 二级索引：

  问题：

  1. 相比于叶子节点中存储行指针，二级索引存储主键值会占用更多的空间，那为什么要这样设计呢？
     　　InnoDB在移动行时，无需维护二级索引，因为叶子节点中存储的是主键值，而不是指针。

  2. 那么InnoDB有了聚簇索引，为什么还要有二级索引呢？
     ，而，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索引时，如果都是聚簇索引，那将占用大量内存空间，所以InnoDB中主键所建立的是聚簇索引，而。
     

  3. 为什么一般情况下，我们建表的时候都会使用一个自增的id来作为我们的主键？
     InnoDB中表中的数据是直接存储在主键聚簇索引的叶子节点中的，每插入一条记录，其实都是增加一个叶子节点，如果主键是顺序的，只需要把新增的一条记录存储在上一条记录的后面，当页达到最大填充因子的时候，下一跳记录就会写入新的页中，这种情况下，主键页就会近似于被顺序的记录填满。

  
  
  

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

1. 把 t1 的所有数据放入到 join_buffer 中
2. 把表 t2 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，所以

因 join_buffer 里的数据是无序的，因此对表 t2 中的每一行，都需要做100次判断（t1表总数）因此

被驱动表的关联字段没索引或二级索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000（无索引全盘扫描） = 100万次，这个是磁盘扫描，效率非常低

实际开发中可以使用explain多试试

straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。 比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql选着 t2 表作为驱动表。

，并不适用于left join，right join。（因为left join，right join已经代表指 定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

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

1. EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别
2. EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3. EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

假设table表中有唯一索引、复合索引、单值索引中一种

执行结果key中发现除了无索引字段count(code)，其余key中都是二级索引名

如果无二级索引，则count(1)、count(id)、count(*)都会使用PRIMARY查询总数

• 为什么mysql最终选择辅助索引而不是主键聚集索引？
  因为二级索引相对主键索引存储数据更少，检索性能应该更高

实际四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于，因为null在mysql底层B+树索引中存储没有跟节点，而是在最小叶子节点的最前端，所以不统计

实际性能基本性能区别不大

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

4.1、show table status

如果不需要很精准使用如下sql性能极高，获取sql中返回的rows对象，就是相似总数

4.2、使用redis中incr、decr

借助redis做原子计数器

缺点：如果mysql 事物回滚，redis会导致计数不准。

4.3、增加计数表

增加一个计数表，每一行统计一个表的总数

当某个表发生事物问题统计表可以与之同步回滚（在同一个事物下）