Loading

10-索引优化分析(2)

1. 避免索引失效

1.1 全值匹配

对索引中所有列都指定具体值。

很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 WHERE 子句的条件顺序以使用适合的索引，例如我们将 WHERE 中的条件顺序颠倒：

1.2 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。如果符合最左法则，但是出现跳跃某一列，则只有最左列索引生效。

titles 表的主索引为 <emp_no, title, from_date>：

此时索引使用情况和情况二相同，因为 title 未提供，所以查询只用到了索引的第一列，而后面的 from_date 虽然也在索引中，但是由于 title 不存在而无法和左前缀连接，因此需要对结果进行扫描过滤 from_date（这里由于 emp_no 唯一，所以不存在扫描）。如果想让 from_date 也使用索引而不是 WHERE 过滤，可以增加一个辅助索引 <emp_no, from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将 emp_no 与 from_date 之间的“坑”填上。

首先我们看下 title 一共有几种不同的值：只有 7 种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

这次 key_len 为 59，说明索引被用全了，但是从 type 和 rows 看出 IN 实际上执行了一个 range 查询，这里检查了 7 个 key。看下两种查询的性能比较：

“填坑”后性能提升了一点。如果经过 emp_no 筛选后余下很多数据，则后者性能优势会更加明显。当然，如果 title 的值很多，用填坑就不合适了，必须建立辅助索引。

1.3 索引上做操作

不要在索引列上做任何操作，如计算、函数、(自动/手动)类型转换等，这些操作都会导致索引失效而转向全表扫描。

字符串不加单引号导致 MySQL 查询优化器会自动进行类型转换，从而造成索引失效。

1.4 索引中范围条件

存储引擎不能使用索引中范围条件右边的列。

索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

可以看到索引对第二个范围索引无能为力。这里特别要说明 MySQL 一个有意思的地方，那就是仅用 explain 可能无法区分“范围索引”和“多值匹配”，因为在 type 中这两者都显示为 range。同时，用了 BETWEEN 并不意味着就是范围查询，例如下面的查询：

看起来是用了两个范围查询，但作用于 emp_no 上的 BETWEEN 实际上相当于 IN，也就是说 emp_no 实际是“多值精确匹配”。可以看到这个查询用到了索引全部三个列。因此在 MySQL 中要谨慎地区分多值匹配和范围匹配，否则会对 MySQL 的行为产生困惑。

1.5 索引中否定条件

MySQL 在使用不等于 !=、<> 的时候无法使用索引会导致全表扫描；IN 走索引（多值精确匹配），NOT IN 索引失效。

1.6 LIKE、通配符

LIKE 以通配符开头(如 '%abc...') 索引失效，会变成全表扫描的操作。但如果仅仅是尾部模糊匹配，索引不会失效；只有在头部模糊匹配，索引才失效。

如果必须要使用 %xxx% 作为 LIKE 查询条件，要使索引不失效，得用“覆盖索引”。

1.7 OR、UNION

用 OR 分割开的条件， 如果 OR 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。所以，对于包含 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

示例：name 字段是索引列 ，而 createtime 不是索引列，中间是 OR 进行连接是不走索引的。

建议使用 UNION 替换 OR

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge
> unique_subquery > index_subquery > range > index > ALL

1.8 尽量使用覆盖索引

什么是覆盖索引？

索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。就是 SELECT 的数据列直接从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

是非聚集组合索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建立索引的字段正好是覆盖查询语句「SELECT 子句」与查询条件「WHERE 子句」中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。当发起一个被索引覆盖的查询 (也叫作“索引覆盖查询”) 时，在 EXPLAIN 的 Extra 列可以看到“Using index”的信息。

Extra 相关：

using index              # 使用覆盖索引的时候就会出现
using where              # 查询的列未被索引覆盖，需要回表去查询所需的数据
using index; using where # 查询的列被索引覆盖，并且 WHERE 筛选条件与索引列相关，所以无需回表查询数据

1.9 全局扫描更快(&NULL)

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

IS NULL，IS NOT NULL 有时索引失效。

表中对应要筛选的非空列数据，如果 NULL 多，则 IS NULL 失效；如果 NULL 少，则 IS NOT NULL 失效。道理同上："认为全局扫描更快"。

2. 插入优化

2.1 大批量插入数据

当使用 load 命令导入数据的时候，适当的设置可以提高导入的效率。

1. 主键顺序插入

因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB 表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

2. 关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

3. 手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

2.2 优化 INSERT 语句

当进行数据的 insert 操作的时候，可以考虑采用以下几种优化方案。原始方式如下：

insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jerry');
  1. 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的 insert 语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个 insert 语句快。
    insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
  2. 在事务中进行数据插入
    start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;
  3. 数据有序插入

2.3 测试用例

(1) 建表 emp，dept

(2) 开启对函数的信任，设置参数 log_bin_trust_function_creators

简单介绍一下，当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为 0（默认值），用户不得创建或修改存储函数，除非它们具有除 CREATE ROUTINE 或 ALTER ROUTINE 特权之外的 SUPER 权限。 设置为 0 还强制使用 DETERMINISTIC 特性或 READS SQL DATA 或 NO SQL 特性声明函数的限制。 如果变量设置为 1，MySQL 不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

(3) 创建函数，保证每条数据都不同

# 创建随机生成字符串的函数
DELIMITER $$
DROP FUNCTION IF EXISTS rand_string;
CREATE FUNCTION rand_string(n int) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i int DEFAULT 0;
    while i<n DO
        SET return_str=CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52), 1));
        SET i=i+1;
    END while;
    RETURN return_str;
END $$

# 创建随机生成编号的函数
DELIMITER $$
DROP FUNCTION IF EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS int(5)
BEGIN
    DECLARE i int DEFAULT 0;
    SET i=FLOOR(100+RAND()*100);
    RETURN i;
END $$

(4) 创建存储过程

# 创建往 dept 表中插入数据的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_dept;
CREATE PROCEDURE insert_dept(IN start int(10), IN max_num int(10))
BEGIN
    DECLARE i int DEFAULT 0;
    SET autocommit=0;
    repeat
        set i=i+1;
        INSERT INTO dept(deptno, dname, loc)
        VALUES (rand_num(), rand_string(10), rand_string(8));
        until i=max_num
    END repeat;
    COMMIT;
END $$

# 创建往 emp 表中插入数据的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_emp;
CREATE PROCEDURE insert_emp(IN start int(10), IN max_num int(10))
BEGIN
    DECLARE i int DEFAULT 0;
    SET autocommit=0;
    repeat
    SET i=i+1;
        INSERT INTO emp (empno, empname, job, mgr, hiredate, sal, comm, deptno)
        VALUES ((start+i), rand_string(6), 'developer', 0001, curdate(), 2000, 400, rand_num());
    until i=max_num
    END repeat;
    COMMIT;
END $$

(5) 调用存储过程

DELIMITER ;
CALL insert_dept(100, 10);
CALL insert_emp(100001, 500000);

3. 关联查询优化

有的时候我们在操作数据库时会将两个或多个数据表关联起来通过一些条件筛选数据，在关联表时我们要遵循一些原则，这样会使我们编写的 SQL 语句在效率上快很多。

3.1 JOIN

〈驱动表〉免不了全表扫描，重点是要给〈被驱动表〉的关联字段建立索引。

【建议】

  1. 保证被驱动表的 JOIN 字段已经被索引；
  2. LEFT JOIN 时，选择数据量较少的一张表作为驱动表，数据量较大的那张作为被驱动表（小表驱动大表）；
  3. INNER JOIN 时，MySQL 会自己帮你把小结果集的表选为驱动表；
  4. 子查询尽量不要放在被驱动表，有可能使用不到索引；
  5. 能够直接多表关联的尽量直接关联，不用子查询。换言之，子查询的优化方式就是将它用 JOIN 连接替代。

4.2 IN、EXISTS

摘自：https://www.ershicimi.com/p/740f59adfd45e68e3a3dd2ad187ef496

小表驱动大表，即小的数据集驱动大的数据集。在知道什么是“小表驱动达大表”之前，我们先来了解两个查询关键字，IN 与 EXISTS。我们通过两段查询语句先来了解一下它们的作用。我建立了两张表，一张员工表，一张部门表，员工表中有 dept_id 这个属性，将这两张表关联起来。

我们先使用 IN 来查询数据：

SELECT * FROM t_emp
WHERE dept_id IN (
    SELECT dept_id
    FROM t_dept
)
LIMIT 5;

# 由于有很多的员工信息，在这里我就只查询 5 条数据。

+-------------+----------+------------+--------------+---------+
| emp_id      | emp_name | emp_gender | emp_email    | dept_id |
+-------------+----------+------------+--------------+---------+
| 00000000177 | 41d80    | m          | 41d80@zc.com |       1 |
| 00000000178 | a74b8    | m          | a74b8@zc.com |       1 |
| 00000000179 | 661ca    | m          | 661ca@zc.com |       1 |
| 00000000180 | 9413d    | m          | 9413d@zc.com |       1 |
| 00000000181 | 7d577    | m          | 7d577@zc.com |       1 |
+-------------+----------+------------+--------------+---------+

接下里使用 EXISTS 来查询数据：

SELECT * FROM t_emp
WHERE EXISTS (
    SELECT 1
    FROM t_dept
    WHERE t_dept.dept_id = t_emp.dept_id
)
LIMIT 5;

# 查询结果与上面的结果一样。

+-------------+----------+------------+--------------+---------+
| emp_id      | emp_name | emp_gender | emp_email    | dept_id |
+-------------+----------+------------+--------------+---------+
| 00000000177 | 41d80    | m          | 41d80@zc.com |       1 |
| 00000000178 | a74b8    | m          | a74b8@zc.com |       1 |
| 00000000179 | 661ca    | m          | 661ca@zc.com |       1 |
| 00000000180 | 9413d    | m          | 9413d@zc.com |       1 |
| 00000000181 | 7d577    | m          | 7d577@zc.com |       1 |
+-------------+----------+------------+--------------+---------+

既然 IN 和 EXISTS 都可以用来查询数据，那它们两个有什么区别呢？

这里虽然我们编写的 SQL 语句是主查询员工信息，子查询部门 id ，但 MySQL 实际的执行顺序会是先执行 IN 后的子查询，再执行主查询，然后获得我们要查询的数据。

SELECT * FROM t_emp
WHERE dept_id IN (
    SELECT dept_id
    FROM t_dept
)

# 这条 SQL 语句相当于：
for SELECT dept_id FROM t_dept
    for SELECT * FROM t_emp WHERE t_emp.dept_id = t_dept.dept_id

如下我们可以将 EXISTS 语法理解为：将主查询的数据放在子查询中做条件验证，根据结果 TRUE 和 FALSE 来决定主查询中的数据是否需要保留。

SELECT * FROM t_emp
WHERE EXISTS (
    SELECT 1
    FROM t_dept
    WHERE t_dept.dept_id = t_emp.dept_id
)

# 这条 SQL 语句相当于：
for SELECT * FROM t_emp
    for SELECT * FROM t_dept WHERE t_dept.dept_id = t_emp.dept_id

EXISTS 子查询只返回 TRUE 或 FALSE ，因此子查询中的 SELECT * 可以是 SELECT 1 或者其他，MySQL 的官方说在实际执行时会忽略 SELECT 清单，因此是没有什么区别的。EXISTS 子查询其实在执行时，MySQL 已经对它做了一些优化并不是对每条数据进行对比。

【小结】

在实际操作过程中我们要对两张表的 dept_id 都设置索引。在一开始我们就讲了一个优化原则即：小表驱动大表，在我们使用 IN 进行关联查询时，通过上面 IN 操作的执行顺序，我们是先查询部门表，再根据部门表查出来的 dept_id 信息来查询员工信息。

我们都知道员工表肯定会有很多的员工信息，但是部门表一般只会有很少的数据信息，我们事先通过查询部门表信息查询员工信息，以小表(t_dept) 的查询结果，去驱动大表(t_emp)，这种查询方式是效率很高的，也是值得提倡的。

但是我们使用 EXISTS 查询时，首先查询员工表，然后根据部门表的查询条件返回的 TRUE 或者 FALSE ，再决定员工表中的信息是否需要保留。这不就是用大的数据表(t_emp) 去驱动小的数据表小的数据表(t_dept) 了吗？虽然这种方式也可以查出我们想要的数据，但是这种查询方式是不值得提倡的。

当 t_emp 表中数据多于 t_dept 表中的数据时，这时我们使用 IN 优于 EXISTS。当 t_dept 表中数据多于 t_emp 表中的数据时（我们这里只是假设），这时我们使用 EXISTS 优于 IN。即：IN 后接小表，EXISTS 后接大表。

因此是使用 IN 还是使用 EXISTS 就需要根据我们的需求决定了。但是如果两张表中的数据量差不多时那么是使用 IN 还是使用 EXISTS 差别不大。

4. ORDER BY 优化

4.1 两种排序方式

第 1 种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 using filesort。

第 2 种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

【小结】了解了 MySQL 的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。Where 条件和 Order by 使用相同的索引，并且 Order By 的顺序和索引顺序相同，以及 Order by 的字段要么都升序，或者都是降序，否则肯定需要额外的操作，这样就会出现 filesort。

4.2 几个案例

key_len 说的是 WHERE 后面的筛选条件命中索引的长度，不包括 ORDER BY！

就默认使用 MySQL 做出的对索引的选择。

4.3 filesort

通过创建合适的索引，能够减少 filesort 的出现，但是在某些情况下，条件限制不能让 filesort 消失，那就需要加快 filesort 的排序操作。对于 filesort，MySQL 有两种排序算法：

a. 双路排序

MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。

先读取行指针和 orderby 排序列，然后在排序区 sort_buffer 中排序，如果 sort_buffer 不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机 I/O 操作。

取一批数据，要对磁盘进行了两次扫描，众所周知，I/O是很耗时的，所以在 MySQL 4.1 之后，出现了第二种改进的算法，就是单路排序。

b. 单路排序

从磁盘读取查询需要的所有列，按照 order by 列在排序区 sort_buffer 中对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机 I/O 变成了顺序 I/O，但是它会使用更多的空间，因为它把每一行都保存在内存中了，但是排序效率比两次扫描算法要高。

但是用单路有些问题：在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，但由于方法 B 是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排 …… 从而多次 I/O。本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

c. 优化策略

Order by 时 SELECT * 是一个大忌，切记只 Query 需要的字段， 这点非常重要。在这里的影响是：

  • 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT/BLOB 类型时，会用改进后的算法 —— 单路排序， 否则用老算法 —— 多路排序。
  • 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后会创建 tmp 文件进行合并排序，从而导致多次 I/O，只是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

  • 尝试提高 sort_buffer_size，不管用哪种算法，提高这个参数都会提高效率。当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。通常在 1M ~ 8M 之间调整；
  • 尝试提高 max_length_for_sort_data，提高这个参数会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。通常在 1024 ~ 8192 之间调整。

【小结】

  1. 增大 sort_buffer_size 参数的设置
  2. 增大 max_length_for_sort_data 参数的设置
  3. 减少 SELECT 后面的查询的字段

5. GROUP BY 优化

由于 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

GROUP BY 使用索引的原则几乎跟 ORDER BY 一致 ，唯一区别是 GROUP BY 即使没有过滤条件用到索引，也可以直接使用索引。

如果查询包含 GROUP BY 但是用户想要避免排序结果的消耗， 则可以添加 ORDER BY NULL 禁止排序。如下 ：

从上面的例子可以看出，第一个 SQL 语句需要进行 "filesort"，而第二个 SQL 由于 order by null 不需要进行 "filesort"， 而上文提过 Filesort 往往非常耗费时间。

6. 分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000, 10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000 ~ 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

  1. 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容；
  2. 该方案适用于主键自增(还不能有断层) 的表，可以把 Limit 查询转换成某个位置的查询 。

7. 补充

7.1 查看索引的使用情况

show status like 'Handler_read%';
show global status like 'Handler_read%';

  • Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
  • Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
  • Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
  • Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY ... DESC。
  • Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
  • Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

7.2 使用 SQL 提示

SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

a. USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

b. IGNORE INDEX

如果用户只是单纯的想让 MySQL 忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

c. FORCE INDEX

为强制 MySQL 使用一个特定的索引，可在查询中使用 force index 作为 hint。

7.3 联合索引在 B+树上的存储结构

摘自：https://blog.csdn.net/ibigboy/article/details/104571930

首先，表 T1 有字段 a,b,c,d,e，其中 a 是主键，除 e 为 varchar 其余为 int 类型，并创建了一个联合索引 idx_t1_bcd(b,c,d)，然后 b、c、d 三列作为联合索引，在 B+ 树上的结构正如下图所示。联合索引的所有索引列都出现在索引树上，并依次比较三列的大小。下面是假设的表数据以及我对其联合索引在 B +树上的结构图的改进。PS：基于 InnoDB 存储引擎。

我们先看 T1 表，他的主键暂且我们将它设为整型自增的，InnoDB 会使用主键索引在 B+ 树维护索引和数据文件，然后我们创建了一个联合索引(b，c，d) 也会生成一个索引树，同样是 B+ 树的结构，只不过它的 data 部分存储的是联合索引所在行的主键值（上图叶子节点紫色背景部分）。

对于联合索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上。对于联合索引，存储引擎会首先根据第一个索引列排序，如上图我们可以单看第一个索引列，如，1,1,5,12,13 … 它是单调递增的；如果第一列相等则再根据第二列排序，依次类推就构成了上图的索引。

而之所以会有「最左前缀匹配原则」和联合索引的索引构建方式及存储结构是有关系的。

首先我们创建的 idx_t1_bcd(b,c,d) 索引，相当于创建了 (b)、(b、c)、(b、c、d) 三个索引，看完下面你就知道为什么相当于创建了三个索引。

我们看，联合索引是首先使用多列索引的第一列构建的索引树，用上面 idx_t1_bcd(b, c, d) 的例子就是优先使用 b 列构建，当 b 列值相等时再以 c 列排序，若 c 列的值也相等则以 d 列排序。我们可以取出索引树的叶子节点看一下。

索引的第一列也就是 b 列可以说是从左到右单调递增的，但我们看 c 列和 d 列并没有这个特性，它们只能在 b 列值相等的情况下这个小范围内递增，如第一叶子节点的第 1、2 个元素和第二个叶子节点的后三个元素。
​
由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含 b 列如 (c,d)、(c)、(d) 是无法应用缓存的，以及跨列也是无法完全用到索引，如 (b,d)，只会用到 b 列索引。

这就像我们的电话本一样，有名和姓以及电话，名和姓就是联合索引。在姓可以以姓的首字母排序，姓的首字母相同的情况下，再以名的首字母排序。

7.4 案例

1. 列出自己的掌门比自己年龄小的人员

EXPLAIN SELECT SQL_NO_CACHE e.id, e.name
FROM emp e LEFT JOIN dept d
ON e.deptId = d.id
WHERE e.age > (SELECT age FROM emp WHERE id = d.ceo);


EXPLAIN SELECT SQL_NO_CACHE a.id, a.name
FROM emp a
LEFT JOIN dept b ON a.deptId = b.id
LEFT JOIN emp c ON b.ceo = c.id
WHERE a.age > c.age;

2. 列出所有年龄低于自己门派平均年龄的人员

EXPLAIN SELECT SQL_NO_CACHE id, name FROM emp e
WHERE age < (SELECT AVG(age) FROM emp WHERE deptId = e.deptId);

EXPLAIN SELECT SQL_NO_CACHE id, name FROM emp a INNER JOIN
(SELECT deptId, AVG(age) avg FROM emp WHERE deptId IS NOT NULL GROUP BY deptId) b
ON a.deptId = b.deptId
WHERE a.age < b.avg;

3. 列出至少有 2 个年龄大于 40 岁的成员的门派

EXPLAIN SELECT SQL_NO_CACHE a.id FROM dept a
INNER JOIN emp b ON a.id = b.deptId
WHERE b.age > 40
GROUP BY a.id
HAVING COUNT(*) >= 2;

STRAIGHT_JOIN：直连；左表驱动，右表被驱动。因为是自己指定的驱动关系，所以用的时候要明确两表的数量级。

EXPLAIN SELECT a.id FROM dept a
STRAIGHT_JOIN emp b ON a.id = b.deptId
WHERE b.age > 40
GROUP BY a.id
HAVING COUNT(*) >= 2;

4. 至少有 2 位非掌门人成员的门派

EXPLAIN SELECT SQL_NO_CACHE c.deptname, c.id, COUNT(*)
FROM dept c STRAIGHT_JOIN emp a ON a.deptId = c.id
LEFT JOIN dept b ON a.id = b.ceo
WHERE b.id IS NULL
GROUP BY c.id
HAVING COUNT(*) >= 2;

posted @ 2020-11-09 17:53  tree6x7  阅读(152)  评论(0)  编辑  收藏  举报