MySQL使用innodb引擎的查询优化

MySQL使用innodb引擎的查询优化

mysql 查询的算法filesort和借助索引

1. 索引

MySQL索引是数据库表中一种特殊的数据结构，它可以帮助数据库快速地检索和访问数据。

1.1 索引类型

1. B-Tree索引：这是MySQL中最常见的索引类型，它适用于大部分查询操作。MyISAM使用B-Tree实现主键索引、唯一索引和非主键索引。
   InnoDB中非主键索引使用的是B-Tree数据结构，而主键索引使用的是B+Tree。

2. 哈希索引：哈希索引是基于哈希表实现的，它可以非常快速地定位到特定的键值，但是它不支持范围查询和排序。

3. 复合索引：也称为多列索引，可以包含多个列。

1.2 B树和B+树的区别

• 节点结构：
  B树：每个节点可以包含多个键和指针。每个节点可能包含一个键，也可能包含多个键，但通常每个节点的键数量是有限的。

  B+树：节点中的键数量比B树多，每个节点可以有多个键，而且每个节点中除了键以外，还包括指向子节点的指针。在B+树中，只有叶子节点存储数据。

• 数据访问：
  B树：所有的键都存储在非叶子节点中，并且所有的数据都存储在叶子节点中。

  B+树：所有数据都存储在叶子节点中，并且叶子节点之间通过指针相连，形成了一个有序链表。

• 查找性能：
  B树：查找一个键可能需要访问多个节点。

  B+树：查找一个键通常只需要访问到叶子节点，因为所有的数据都存储在叶子节点，且叶子节点之间是顺序存储的。

• 为什么MySQL使用B+树索引
  减少磁盘I/O：由于所有数据都存储在叶子节点，并且叶子节点之间是顺序存储的，因此对数据的访问可以通过顺序读取来减少磁盘I/O次数。

  提高范围查询性能：在B+树中，范围查询可以直接通过叶子节点中的有序链表进行，而无需像B树那样逐级访问非叶子节点。

  减少索引的存储空间：由于数据只存储在叶子节点，可以减少索引的存储空间。

1.3 索引的特性

• 提高查询效率：通过索引，数据库可以快速定位到数据行，而不需要扫描整个表。
• 降低数据修改的成本：例如，插入、删除和更新操作需要维护索引。
• 排序：索引可以用来在查询时对结果进行排序。

1.4 索引的创建

CREATE INDEX index_name ON table_name(column1, column2, ...);

1.5 索引的最左前缀法则示例

假设index(a,b,c)

Where语句	索引是否被使用
where a = 1	使用到a
where a = 1 and b = 2	使用到a，b
where a = 1 and b = 2 and c = 3	使用到a,b,c
where b = 1 或者 where b = 2 and c = 3 或者 where c = 4	没用使用
where a = 1 and c = 2	使用到a， 但是c不可以，b中间断了
where a = 1 and b > 2 and c = 3	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不一定能用上索引（8.0）
where a <> 1	不能使用索引
where abs(a) =1	函数不能使用 索引
where a = 1 and b like ‘xx%’ and c = 3	使用到a,b,c
where a = 1 and b like ‘%xx’ and c = 3	只用到a
where a = 1 and b like ‘%xx%’ and c = 3	只用到a
where a = 1 and b like ‘x%xx%’ and c = 3	使用到a,b,c

1.6 索引的使用建议

• 对于单键索引，尽量选择过滤性更好的索引（例如：手机号，邮件，身份证）

• 在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。`

• `选择组合索引时，尽量包含where中更多字段的索引

• 组合索引出现范围查询时，尽量把这个字段放在索引次序的最后面`

• 尽量避免造成索引失效的情况

1.8 分析工具

EXPLAIN SELECT age FROM emp ORDER BY age;

1.9 索引的缺点

• 维护成本：每次数据插入、删除或更新时，索引都需要更新。
• 额外的存储空间：索引需要额外的磁盘空间。

2. FileSort

在MySQL的filesort算法中，排序过程可以采用两种不同的方法：双路排序（multi-pass sort）和单路排序（single-pass sort）。

2.1 双路排序（Multi-Pass Sort）

取出排序字段进行排序，排序完成后再次回表查询所需要的其他字段

• 双路排序过程：

1. 找到第一个满足主键id
2. 根据主键 id 取出整行，把排序字段和主键 id 这两个字段放到 sort buffer(排序缓存) 中
3. 直到取出所有匹配
4. 对 sort_buffer 中的排序字段 和主键 id 进行排序
5. 遍历排序好的 id 和排序字段，按照 id 的值回到原表中取出 所有字段的值返回给客户端

2.2 单路排序（Single-Pass Sort）

一次取出所有字段进行排序，内存不够用的时候会使用磁盘

• 单路排序过程：

1. 找到第一个满足条件的主键 id
2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
3. 直到取出所有匹配行
4. 对 sort_buffer 中的数据进行order by 字段进行排序
5. 返回结果给客户端

2.3 对比

在对比这两种排序模式时，单路排序的操作是将所有查询所需的字段全部加载到sort buffer中。与之相对，双路排序则更为精简，它仅将主键以及那些用于排序的字段放入sort buffer中进行排序。随后，双路排序会利用排序后的主键回到原始表中检索完整的记录。

单路排序的优势在于其效率，因为它避免了重复读取数据的过程。此外，这种排序方式将原本的随机I/O转换成了更高效的顺序I/O。然而，单路排序的代价是它需要更多的内存空间，因为必须将每一行数据完整地保存在内存中。

2.4 影响MySQL选择排序的因素：

• 数据量：如果数据量非常大，MySQL可能会使用多路排序。
• 可用内存：如果内存充足，MySQL可能会选择单路排序；如果内存不足，它可能会选择多路排序。
• 排序键的基数：如果排序键的基数（不同值的数量）很高，MySQL可能会使用多路排序。
• MySQL版本和配置：不同的MySQL版本和配置可能会影响filesort算法的选择。