mysql中的explain居然也会骗人

最新推荐文章于 2024-08-27 16:57:07 发布

一只牛博

最新推荐文章于 2024-08-27 16:57:07 发布

阅读量491

点赞数 19

分类专栏： # mysql 文章标签： mysql 数据库 explain

本文链接：https://blog.csdn.net/Mrxiao_bo/article/details/141599845

版权

mysql 专栏收录该内容

46 篇文章 1 订阅

订阅专栏

直接甩张图，大家可以猜一下这个sql的执行时间

先提一嘴，bm表总共77条数据，而且没有大字段，字段数量也不多

在这里插入图片描述

explain中影响sql的关键

在 EXPLAIN 语句的输出中，影响 SQL 执行时间的关键项主要有以下几个：

type（连接类型）：
- 这是执行计划中最重要的列之一，表示 MySQL 用于查找行的连接类型。常见的类型按效率排序为：
  - system、const：单行查找，速度最快。
  - eq_ref、ref：使用索引查找行，效率较高。
  - range：范围扫描，效率次于 ref。
  - index：全索引扫描。
  - ALL：全表扫描，速度最慢。
- 执行时间通常随着连接类型的效率降低而增加。
rows（扫描行数）：
- 表示 MySQL 预计要读取的行数。行数越大，SQL 执行时间往往越长。通常通过优化查询条件和索引，可以减少扫描的行数。
Extra（附加信息）：
- 这里包含了许多关于查询执行方式的附加信息，其中一些可能会影响执行时间：
  - Using index：表示查询仅使用了索引，效率较高。
  - Using where：表示使用了 WHERE 过滤条件。
  - Using temporary：使用临时表，通常会降低查询效率。
  - Using filesort：表示需要对结果进行排序，可能导致较长的执行时间。
possible_keys 和 key（可能使用的索引与实际使用的索引）：
- possible_keys 列显示 MySQL 在执行查询时可以使用的所有索引。
- key 列显示了实际使用的索引。如果 key 为空，则表示没有使用索引，通常会导致全表扫描，增加执行时间。
filtered（过滤率）：
- 表示 MySQL 估计通过条件过滤后返回的行数百分比。值越低，表示更多的行被过滤，通常意味着更高效的查询。

通过这些项的分析，你可以更好地理解 SQL 查询的性能瓶颈，并进行优化以减少执行时间。

sql执行时间影响的因素

SQL 的执行时间受到多个因素的影响，包括硬件配置和数据库配置文件中的参数。以下是一些关键因素：

1. 硬件配置

CPU: 处理器的速度和核心数量直接影响SQL查询的计算速度。多核CPU可以提高并行查询的能力。
内存（RAM）: 足够的内存可以确保更多的数据被缓存，减少磁盘I/O，进而提高查询性能。
磁盘 I/O 性能: 磁盘的读写速度（如使用SSD而非HDD）对处理大数据量的查询尤其重要。RAID配置和I/O调度策略也会影响性能。
网络带宽: 在分布式数据库或远程数据库访问的场景中，网络带宽和延迟会影响SQL执行时间。

2. 数据库配置文件中的参数

缓存和缓冲区大小:
- innodb_buffer_pool_size (MySQL/InnoDB): 用于缓存数据和索引的缓冲池大小。更大的缓冲池可以减少磁盘I/O，提高性能。
- shared_buffers (PostgreSQL): 控制数据库中共享缓冲区的大小，用于缓存数据块。
work_mem/sort_buffer_size: 这些参数决定了用于排序和哈希操作的内存大小。如果设置过小，数据库可能需要在磁盘上创建临时文件，增加I/O操作。
max_connections: 允许的最大并发连接数。过多的并发连接可能导致资源竞争，降低单个查询的性能。
query_cache_size (MySQL): 用于缓存查询结果的内存大小。对重复查询的系统有帮助，但在某些情况下可能会导致性能下降。
temp_buffer_size (PostgreSQL): 用于临时表的内存大小。增大这个值可以加快处理复杂查询的速度。
effective_cache_size (PostgreSQL): 这是一个估计值，告诉查询优化器系统的缓存大小，帮助优化器做出更好的决策。

3. 其他因素

表和索引设计: 合理的表结构和索引设计可以显著减少查询时间。适当的索引有助于快速查找数据。
查询优化器: 不同数据库有不同的查询优化器配置，影响查询计划的生成。适当调整优化器相关参数，可以改善SQL执行时间。
并行查询: 某些数据库支持并行查询，相关的配置参数可以影响SQL查询如何在多个CPU核心上并行执行。

通过优化这些硬件和配置文件中的参数，可以显著提高SQL查询的执行性能。

总结开始的sql

类型除了主表都是ref，说明效率已经是很高了
扫描的行数，仅仅不超过100
extra展示使用了where，也不错了
possible_keys与key都存在值
最后一项filtered表现不行，但是影响不会很大

所以这个sql应该是毫秒级别的，也就是肯定是一秒之内

可是结果为下图
在这里插入图片描述
所以：explain肯定是欺骗了我们，首先当一个字段可以是null，并且数量很大的时候，那么首先肯定是不走索引了，其实它的查询性能也会极大降低

null的利害

当查询条件中涉及 NULL 值和特定的字符串值时，即使在其他条件都没有变化的情况下，性能差异可以非常显著。这主要与数据库如何处理 NULL 值和具体值的索引有关。以下是详细的解释：

1. 索引的选择性

NULL 值：
- 选择性：NULL 值通常具有较低的选择性，因为在表中可能有许多 NULL 值。索引的选择性指的是索引中能够区分不同记录的能力。对于 NULL 值，选择性较低，导致索引可能不如具体值的查询那么有效。
- 索引存储：在许多数据库系统中，NULL 值的索引存储方式和非 NULL 值不同。处理 NULL 值的索引查询可能需要数据库扫描更多的记录或执行额外的逻辑，导致查询效率较低。
具体字符串值：
- 选择性：当查询条件是一个具体的字符串值（如 'string'），索引的选择性通常较高，特别是当字段值的分布比较均匀或值相对唯一时。数据库能够通过索引快速定位到匹配的记录。
- 索引利用：具体值的索引通常能够更高效地利用，因为索引可以直接定位到特定的值，从而减少扫描的记录数，提高查询速度。

2. 数据库索引处理

处理 NULL 值：
- 索引扫描：在处理 NULL 值时，数据库系统可能需要扫描大量的记录来匹配 NULL 条件。如果字段中 NULL 值的比例较高，数据库可能会选择全表扫描或者低效的索引扫描。
- 特殊处理：不同的数据库系统对 NULL 值的处理方式有所不同。在某些系统中，索引可能对 NULL 值的处理不如对具体值的处理高效。
处理具体值：
- 直接匹配：当查询条件是具体的字符串值时，数据库可以通过索引快速找到匹配的记录。索引结构（如 B-树、哈希表等）能够高效地定位到特定值，从而提高查询速度。
- 优化执行计划：数据库查询优化器通常会生成优化的执行计划来处理具体值的查询，利用索引减少扫描范围，从而提高查询效率。

3. 执行计划和性能

执行计划：
- IS NULL 查询：执行计划可能需要处理复杂的逻辑来匹配 NULL 值，特别是当字段中 NULL 值较多时。执行计划可能选择全表扫描或低效的索引扫描。
- 具体值查询：执行计划通常能够利用索引高效地定位到特定的字符串值，减少扫描范围，提高性能。
查询性能：
- IS NULL：在字段中 NULL 值的比例较高时，查询可能变得较慢，因为数据库需要处理和匹配更多的 NULL 值。
- 具体值：查询性能通常较好，因为数据库能够快速定位到具体的值，通过索引减少扫描的记录数。

示例说明

假设你的查询是：

SELECT * 
FROM your_table 
LEFT JOIN another_table 
ON your_table.id = another_table.id
WHERE your_table.some_field IS NULL;

和：

SELECT * 
FROM your_table 
LEFT JOIN another_table 
ON your_table.id = another_table.id
WHERE your_table.some_field = 'specific_value';

IS NULL：如果 your_table.some_field 中有大量 NULL 值，数据库可能需要扫描很多记录来找到匹配的 NULL 值，导致查询变慢。
具体值：如果 your_table.some_field 中有很少的 'specific_value'，数据库可以快速通过索引定位到这些记录，从而提高查询性能。