MySQL使用innodb引擎的查询优化
MySQL使用innodb引擎的查询优化
mysql 查询的算法
filesort和借助索引
1. 索引
MySQL索引是数据库表中一种特殊的数据结构，它可以帮助数据库快速地检索和访问数据。
1.1 索引类型
-
B-Tree索引：这是MySQL中最常见的索引类型，它适用于大部分查询操作。
MyISAM使用B-Tree实现主键索引、唯一索引和非主键索引。
InnoDB中非主键索引使用的是B-Tree数据结构，而主键索引使用的是B+Tree。 -
哈希索引：哈希索引是基于哈希表实现的，它可以非常快速地定位到特定的键值，但是它不支持范围查询和排序。
-
复合索引：也称为多列索引，可以包含多个列。
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）
取出排序字段进行排序，排序完成后再次回表查询所需要的其他字段
- 双路排序过程：
- 找到第一个满足主键id
- 根据主键 id 取出整行，把排序字段和主键 id 这两个字段放到 sort buffer(排序缓存) 中
- 直到取出所有匹配
- 对 sort_buffer 中的排序字段 和主键 id 进行排序
- 遍历排序好的 id 和排序字段，按照 id 的值回到原表中取出 所有字段的值返回给客户端
2.2 单路排序（Single-Pass Sort）
一次取出所有字段进行排序，内存不够用的时候会使用磁盘
- 单路排序过程：
- 找到第一个满足条件的主键 id
- 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
- 直到取出所有匹配行
- 对 sort_buffer 中的数据进行order by 字段进行排序
- 返回结果给客户端
2.3 对比
在对比这两种排序模式时，单路排序的操作是将所有查询所需的字段全部加载到sort buffer中。与之相对，双路排序则更为精简，它仅将主键以及那些用于排序的字段放入sort buffer中进行排序。随后，双路排序会利用排序后的主键回到原始表中检索完整的记录。
单路排序的优势在于其效率，因为它避免了重复读取数据的过程。此外，这种排序方式将原本的随机I/O转换成了更高效的顺序I/O。然而，单路排序的代价是它需要更多的内存空间，因为必须将每一行数据完整地保存在内存中。
2.4 影响MySQL选择排序的因素：
- 数据量：如果数据量非常大，MySQL可能会使用多路排序。
- 可用内存：如果内存充足，MySQL可能会选择单路排序；如果内存不足，它可能会选择多路排序。
- 排序键的基数：如果排序键的基数（不同值的数量）很高，MySQL可能会使用多路排序。
- MySQL版本和配置：不同的MySQL版本和配置可能会影响
filesort算法的选择。