为什么使用了索引，查询效率还是低？序言为什么使用了索引，查询效率还是低？，这个时候就不得不提到MySQL中的sql优化

序言

为什么使用了索引，查询效率还是低？，这个时候就不得不提到MySQL中的sql优化了，在MySQL中不仅仅是索引影响着查询效率，sql语句的优化同样重要

1. 关联查询优化

先说结论：

结论1：对于内连接来说，查询优化器可以决定谁来做驱动表，谁作为被驱动表出现
结论2：对于内连接来说，如果表的连接条件只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表
结论3：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。小表驱动大表

1.1 索引嵌套循环连接(Index Nested-Loop Join)

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。

概率理解：
- 索引嵌套循环连接(Index Nested-Loop Join，简称 INLJ)是一种数据库连接操作的算法。它主要用于连接两个表，其中一个表(通常是内层表)在连接条件上有合适的索引。
- 基本思想是对于外层表 (驱动表) 的每一行，利用内层表的索引来快速定位到与之匹配的行。例如，假设有表 A 和表 B 进行连接，表 A 作为外层表，表 B 作为内层表。当遍历表 A 的每一行时，通过表 B 中连接条件列的索引，快速查找与表 A 当前行相匹配的表 B 中的行。
工作原理：
- 驱动表选择：数据库优化器会选择一个表作为驱动表，通常是结果集相对较小的表。假设我们要连接员工表（employees）和部门表（departments），如果员工表经过筛选后（如通过职位等条件）行数较少，可能会被选为驱动表 (是否选择还是取决于优化器)。
- 遍历驱动表：以员工表为驱动表为例，开始遍历员工表中的每一行。对于员工表中的每一个员工记录，比如员工记录包含员工所属部门 ID 这个字段。
- 利用索引查询匹配行：在部门表中，连接条件通常是员工表中的部门 ID 与部门表中的部门 ID 相匹配。由于部门表在部门 ID 列上有索引，当遍历员工表中的每一个员工记录时，通过部门表的部门 ID 索引，可以快速定位到与该员工所属部门 ID 匹配的部门记录。这样就完成了一次连接操作，找到一对匹配的员工和部门记录。
- 性能优势：这种连接方式在连接条件列有合适索引的情况下，相比于全表扫描连接等方式，可以大大减少比较次数和数据读取量。因为每次查找内层表匹配行时，是通过索引进行快速定位，而不是遍历整个内层表。
性能问题
- 可能会存在外层表数据量过大的情况，此时就得优化驱动表的选择，例如先对外层表进行过滤筛选，减少数据量来进行查询操作。

1.2 Block Nested-Loop Join（块嵌套循环连接）

定义：Block Nested-Loop Join是一种在数据库查询处理中用于连接两个关系(表)的算法。它是嵌套循环连接的一种优化版本。在关系数据库中，当执行如SQL中的JOIN操作时，需要根据一定的条件将两个表的行组合起来，块嵌套循环连接就是实现这种组合的一种方式。
与传统嵌套循环连接对比
- 传统的嵌套循环连接时对于外层表 (驱动表) 的每一行，都要遍历内层表 (被驱动表) 的索引行来检查是否满足连接条件。这种方式在处理大数据集时效率很低，尤其是当内层表很大时，会导致大量的磁盘I/O和比较操作
- 而块嵌套循环连接通过每次读取外层表的一块数据，然后将这一块数据与内层表的所有行进行比较，减少了对外层表的重复读取操作，一定程度上提高了效率。例如，假设有表A和表B进行连接，传统嵌套循环连接会为表A的每一行单独读取表B，而在嵌套循环连接会先读取表A的一部分 (一块)，再用这部分数据和表B进行比较
实现细节
- 块的读取：
  - 块的大小是一个关键因素。数据库系统会根据内存容量、数据页面大小等因素来确定合适的块大小。通常，块大小是固定的，例如，可以是若干个数据页面（数据在磁盘存储时的基本单位）。假设块大小是 4KB，这意味着每次从外层表读取的数据量大约是 4KB。
  - 读取块的操作是由数据库的存储引擎来完成的。存储引擎会根据数据在磁盘上的存储位置和索引信息（如果有）来高效地获取数据块。
- 连接条件检查：
  - 对于外层表读取的每一个块中的行，会逐一与内层表的所有行进行连接条件的检查。连接条件通常是基于表中的列值相等或者满足一定的关系（如大于、小于等）。例如，在一个 SQL 查询SELECT * FROM Table1 JOIN Table2 ON Table1.id = Table2.fk_id中，Table1.id = Table2.fk_id就是连接条件。
  - 当检查到满足连接条件的行时，会将这些行组合成一个新的结果行，这些结果行最终构成了连接操作的输出结果集。
性能影响：
- 表的大小和数据分布：
  - 如果外层表（驱动表）的块能够很好地覆盖连接条件可能匹配的范围，并且内层表的大小相对合理，性能可以得到优化。例如，如果外层表的一个块中的行对应的内层表匹配行比较集中，而不是分散在整个内层表中，那么比较次数会减少。
  - 相反，如果两个表都很大，并且数据分布很分散，块嵌套循环连接可能会导致大量的比较操作，性能会下降。例如，在没有合适索引的情况下，连接两个包含数百万行数据的表，可能会使查询执行时间很长。

1.3 Hash JOIN

从MySQL 8.0.20版本开始将废弃块嵌套循环查询，从8.0.18版本开始就加入了Hash JOIN，默认都是使用Hash JOIN

定义：
- Hash JOIN是一种数据库查询处理中的连接操作算法。它主要用于高效地将两个表(关系)基于特定的连接条件组合在一起。这种算法利用了哈希函数的特性来加速匹配的过程
- 假设有两张表，表A和表B，要基于一个连接条件 (例如，表A中的列a和表B中的列b相等)进行连接，Hash JOIN算法会对其中一个表 (通常是较小的那个表) 构建哈希表，然后用另一个表的行去探测这个哈希表，以快速找到匹配的行。
工作原理：
- 构建哈希表阶段：
  - 首先选择两个表的一个作为构建哈希表的基础，这个表通常为较小的表，被称为构件表。例如，在表 A 和表 B 中，如果表 A 较小，就选择表 A。
  - 对于构建表的每一行，根据连接条件的列 (例如，表A中的列a) 计算哈希值。哈希函数将列值转换为一个固定长度的哈希码。例如，一个简单的哈希函数可能会对一个整数列的值取模（如对列 a 的值模 100）来得到哈希码。
  - 以哈希码为索引，将构建表中的行存储到哈希表中。哈希表通常是一个数组，每个数组元素可以使一个链表或者桶，用于存储具体相同的哈希码的行。这样，具体相同哈希码的行就会被存储在同一个位置或者桶中。
探测哈希表阶段（Probe Phase） ：
- 另一个表（被称为探测表，Probe Table）用来探测哈希表。对于探测表中的每一行，根据连接条件中的相应列（例如，表 B 中的列 b）计算哈希值。
- 用这个哈希值去查找哈希表中对应的位置。如果在该位置找到了行，就比较探测表中的行和哈希表中的行是否满足连接条件（例如，比较表 A 中的列 a 和表 B 中的列 b 是否真正相等，因为哈希值相等不一定意味着行完全匹配）。
- 如果满足连接条件，就将两个表中的匹配行组合起来，形成连接后的结果行。
性能影响：
- 当连接条件合适且数据分布比较均匀时，Hash Join 的性能通常比嵌套循环连接（Nested - Loop Join）要好得多。在处理大数据集时，尤其是两个表的大小都比较大的情况下，如果有足够的内存来构建哈希表，它可以快速地找到匹配行。
- 一旦哈希表构建完成，探测阶段可以高效地进行，因为哈希函数能够快速地定位到可能匹配的位置，减少了不必要的行比较。例如，如果哈希表构建得当，探测表中的行可以直接定位到哈希表中的少量几个位置进行比较，而不是遍历整个表。

2. 子查询优化

在MySQL中子查询属于效率底下的查询方式。原因有以下几点：

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕，在撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，索引查询性能会受到一定的影响。
对于返回结果集比较大子查询，其对查询性能的影响也就越大 所以在MySQL中，我们通常使用连接 (JOIN) 查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。

3. 排序优化

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

何为FileSort？

FileSort 是 MySQL 在无法使用索引进行排序时采用的一种排序方式。当执行一个带有 ORDER BY 子句的查询，且没有合适的索引来满足排序要求时，MySQL 会使用 FileSort。它可能会在内存中进行排序，如果内存不够，还会借助磁盘来完成排序操作。例如，在一个查询SELECT * FROM table WHERE condition ORDER BY non - indexed_column中，如果non - indexed_column没有索引，就很可能会触发 FileSort。

如何对FileSort调优？

增大 sort_buffer_size 参数: sort_buffer_size 是 MySQL 用于排序操作的内存缓冲区大小。增大这个参数可以让 MySQL 在内存中处理更多的排序数据，减少对磁盘的依赖。因为磁盘 I/O 操作的速度远远慢于内存操作，所以尽可能在内存中完成排序可以显著提高排序速度。

4. GROUP BY 优化

GROUP BY使用索引原则几乎和ORDER BY一致，GROUP BY即使没有过滤条件用到索引，也可以直接使用索引。
GROUP BY先排序在分组，遵照索引键的最佳左前缀法则
当无法使用索引时，可以适当修改max_length_for_sort_data和sort_buffer_size参数的设置
WHERE 语句的执行效率比HAVING执行效率高

5. 优化分页查询

在MySQL中的分页关键字Limit并不是直接取到所需要的队友行的数据，而是取出所有的数据，再丢弃前面所不需要的数据。例如SELECT * FROM student ORDER BY id LIMIT 2000000,10，MySQL会查询出 2000010 条数据，然后丢弃前面2000000条数据，取剩下的10条数据，当这个偏移量足够大时，所消耗的性能是无法估量的。所以我们需要对分页进行优化。 EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id; 在上面这条数据中我们使用SELECT id FROM student ORDER BY id LIMIT 2000000,10替代SELECT * FROM student ORDER BY id LIMIT 2000000,10，由于主键本身自带索引，所以里面的SQL语句本身查询效率并不满，在对结果与外面的SQL关联，即可利用索引的特性实现高效率的查询。

6. 优先考虑覆盖索引

我们知道在MySQL中非叶子节点是不直接存储数据的，而覆盖索引是一个比较特殊的索引，它包含了查询语句（SELECT 子句）所需要的所有列，当查询可以通过只访问索引就能获取所有需要的数据时，就称这个索引为覆盖索引。例如，有一个customers表，包含customer_id、customer_name和customer_email列，若创建一个索引包含这三个列，当执行查询SELECT customer_id, customer_name, customer_email FROM customers WHERE customer_id > 100时，这个索引就是覆盖索引，因为查询所需的数据都在索引中，无需再访问表中的数据行。

优点：避免Innodb表进行索引的二次查询（回表）。因为所有的数据都已经在覆盖索引当中存在，所以不需要去内存页查询对应索引的数据，避免了回表操作。

总结

这篇文章主要阐述了对应的SQL优化，在实际的工作的当中，需要根据特性的情况进行选择。