MySQL索引优化

分页查询优化

SELECT * FROM employees limit 10000, 10;

表示从employees表10001行开始的取10条记录。执行这条SQL是先读取10010条记录，然后抛弃前10000条，然后读到后面10条想要的数据。

1. 根据自增且连续的主键排序的分页查询

SELECT * FROM employees limit 90000, 5;（没添加单独order by，表示通过主键排序）

SELECT * FROM employees where id > 90000 limit 5; （按照主键去查询从第90001开始的5行数据）

 必须满足：

        1. 主键自增且连续；

        2. 结果是按照主键排序的；

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

SELECT * FROM employees ORDER BY name limit 90000, 5;

并未使用name字段的索引：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。

优化：让排序时返回的字段尽可能的少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录  

SELECT * FROM employees e INNER JOIN (SELECT id FROM employees ORDER BY name limit 90000, 5) ed ON e.id = ed.id;

原SQL使用的是filesort排序，而优化后的SQL使用的是索引排序。

Join关联优化查询

MySQL的表关联常见算法

        Nested-Loop Join算法

        Block Nested-Loop Join算法

1. 嵌套循环连接（NLJ）算法

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

        SELECT * FROM t1 INNER JOIN t2 on t1.a=t2.a;

         驱动表是t2，被驱动表是t1.MySQL会优先选择小表（数据量较小）做驱动表，所以驱动、非驱动和SQL排列无关；

        left join时，左表是驱动表，右表是非驱动表，当使用right join时，右表是驱动表，左表是被驱动表，简单的join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表；

        一般join语句中，如果执行计划Extra中未出现Using join buffer则表示使用join算法是NLJ

执行流程：

        1. 从t2中读取一行数据（如果有过滤条件，则会从过滤结果中取出一行数据）

        2. 从第1步的数据中，取出关联字段a，到t1中查找；

        3. 取出t1中满足条件的行，跟t2中获取到的结果合并，作为结果返回给客户端；

        4. 重复1-3

整个过程会读取t2表的所有数据，然后根据t2.a行的值索引扫描t1表中的对应行。一共扫描200行。

2. 基于块的嵌套循环连接算法（BNL）

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

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

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

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

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

对于被驱动表的关联字段没索引的关联查询，一般都会使用BNL算法。如果有索引一般选择NLJ算法，有索引的情况下NLJ算法比BNL算法性能更高。

对于关联SQL的优化

        关联字段加索引：尽量使用NLJ算法；

        小表驱动大表：在SQL进行多表连接时，如果明确知道哪张表是小表，可以直接使用straight_join，省去mysql优化器自己判断的时间；straight_join只适用于inner_join，并不适用于left, right_join

IN和EXISTS优化

        小表驱动达标：小的数据集驱动大的数据集。

in：当B表的数据集小于A表的数据集时，in优于exists：select * from A where id in (select id from B)

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

        1. EXISTS只返回TRUE和FALSE，因此子查询中的SELECT * 也可以用SELECT 1替换

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

        3. EXISTS子查询往往也可以用JOIN来代替