高性能mysql-查询性能优化

优化的核心

一个任务是由很多子任务组成的，每个子任务都会消耗一定时间，如果优化查询，要么消去一些子任务，要么让子任务更快。

慢查询的基础：优化数据访问

减少访问的行数。
在mysql服务器层是否在分析大量不必要的行。

扫描的行数和返回的行数

通常在1：1到10：1之间，比如一个联级查询，要扫描多行才能返回一行。

扫描的行数和访问类型

mysql有很多访问方式可以查找并且返回一行结果。
这些方式可能需要访问很多行才能返回一行结果，也可以不需要访问。
explain的type列反应了访问类型：全表扫描，索引扫描，范围扫描，唯一索引扫描，常数引用等。
索引的功能就是让mysql以扫描最少的方式找到需要的记录。
eg.如果使用了索引，type=ref可能只需要访问10行数据，而如果不使用则需要使用全表的行数。

where的实现方式：性能从高到低

• 在聚簇索引中使用where条件来过滤不匹配的记录，这是在存储引擎层面实现的。
• 使用索引返回覆盖索引记录，在覆盖索引记录中过滤不需要的记录并且返回命中的结果。这是在MYSQL服务器层实现的，无需回表。（Extra：Using index）
• 在mysql服务器层面，读出记录然后过滤。（Extra：using where）

优化where

• 使用覆盖索引，避免回表

临时表

临时表的生命周期通常仅限于当前会话，因此在会话结束后会自动删除。
修改临时表不会影响原表。这是因为临时表在数据库中是独立存在的，具有自己的数据和结构，和原表没有直接关联。

1. 当查询包含 GROUP BY 和 ORDER BY，（或只包含ORDERBY）,且两者的列不同时，MySQL通常会创建一个临时表来对结果进行排序和分组。
2. 在某些复杂的 DISTINCT 查询中，MySQL可能会创建临时表来帮助消除重复行。
3. 当使用 UNION 或 UNION ALL 将多个查询结果合并时，MySQL会使用临时表来存储中间结果，然后进行去重或合并操作。
4. 子查询
5. 主动创建
6. 复杂查询

分解联级查询

分解联级查询的一个重要基础就是将复杂查询中的子查询结果缓存起来，然后在后续的查询中使用这些中间结果。

步骤：

1. 识别子查询：找出联级查询中的子查询。
2. 提取子查询为独立查询：将每个子查询提取出来，作为独立的查询执行。
3. 使用临时表或CTE（Common Table Expressions）缓存结果：将子查询的结果存储在临时表或CTE中。
4. 在主查询中使用缓存的结果：在主查询中引用这些缓存的结果，简化原始查询。

sql关键字执行顺序

FROM：首先处理的是 FROM 子句，它决定了查询要处理的数据源。此步骤包括连接操作（JOIN）。
WHERE：接下来处理 WHERE 子句，用于过滤从 FROM 子句中选择的行。
GROUP BY：然后是 GROUP BY 子句，它将选择的行分组。
HAVING：接下来处理 HAVING 子句，对 GROUP BY 分组后的结果进行过滤。
SELECT：然后处理 SELECT 子句，确定要返回的列。
DISTINCT：如果使用了 DISTINCT 关键字，这一步会去除重复行。
ORDER BY：最后处理 ORDER BY 子句，对结果进行排序。
LIMIT：最后是 LIMIT 子句，用于限制返回的行数。

查询执行的基础

连接池-服务器-解析器-预处理器-优化器-存储引擎。

预估并且转换为常数表达式

mysql如何执行联级查询

mysql认为每一个查询都是join查询。

UNION执行：一个放在临时表，然后join

两层for循环，拿每一个外层行去匹配内层所有行。
在mysql8以后不再使用，而是使用hash的join。

join：小表驱动大表

减少扫描次数：因为小表先扫描并连接，减少了大表的扫描和连接次数。

排序优化

filesort：数据量小在内存，数据量大在磁盘。
需要创建临时表。
将所用到的列都放到内存，然后排序，返回需要返回的列。

特定的类型的优化

优化count（）

select count(color = ‘blue’ or null) as blue,count(color = ‘red’ or null) as red from items;

优化join查询

• 在on列上建立索引，通常是for联级内层的表需要建立：B，A：在A上建立
• 确保联级操作中的groupby和orderby只使用一个表中的索引，这样才能使用这个索引b加树。

优化limit和offset子句

当执行，order by id limit 10000，20这样的操作时，会访问10020行才返回20行。因为需要排序，会加载一个临时表。
这个语句主要的问题就是offset：10000

使用上一次查找获得的位置

where id > offset的位置 limit 20

使用覆盖索引

减少回表

延迟关联

SELECT *
FROM large_table
WHERE id IN (
    SELECT id 
    FROM large_table
    ORDER BY some_column
    LIMIT 10000, 20
);
1
2
3
4
5
6
7
8