mysql面试题九（SQL优化）

1.一条 SQL 是如何执行的

2.索引失效的几种情况

3.EXPLAIN

4.Where 子句如何优化

5.超大分页或深度分页如何处理

6.大表查询如何优化

7.分库分表

基本概念

分库分表方法

水平拆分

垂直拆分

分库分表后的注意事项

1.一条 SQL 是如何执行的

在MySQL中，一条SQL语句从提交到服务器到最终返回结果，会经历一系列复杂的内部处理过程。以下是SQL语句在MySQL中执行的基本步骤概述：

连接器：
- 客户端应用程序（如数据库管理工具、应用程序代码等）与MySQL服务器建立连接。连接器负责验证用户的登录凭据（用户名、密码），以及与客户端保持通信通道。
查询缓存（MySQL 8.0之前）：
- 对于SELECT查询（不涉及数据修改的查询），在较早版本的MySQL（如8.0之前的版本）中，连接器会先检查查询缓存。如果查询语句与缓存中的某个条目匹配且数据未过期，查询结果可以直接从缓存返回给客户端，避免了进一步的处理。然而，由于查询缓存的局限性（如易受数据变更影响导致缓存失效、管理成本高等），MySQL 8.0版本及以后已移除了查询缓存功能。
分析器：
- 如果查询缓存不适用或未命中，MySQL会将接收到的SQL语句交给分析器。分析器首先进行词法分析，将SQL语句拆解成一个个有意义的单词（token），如标识符、关键词、操作符等。接着进行语法分析，根据MySQL的语法规则检查这些单词组成的序列是否符合SQL语法规则。如果SQL语句的语法错误，此时会返回错误信息。
优化器：
- 通过语法分析的SQL语句，会被优化器接手。优化器负责确定最优的执行计划，即如何最高效地获取所需数据。这包括选择合适的索引、决定表的连接顺序和方式（如Nested Loop Join、Hash Join、Merge Join）、评估是否需要进行全表扫描等。优化器基于统计信息、成本模型等进行决策，旨在最小化查询的执行时间和资源消耗。
执行器：
- 有了执行计划之后，执行器开始执行查询。它与存储引擎交互，调用相应的接口来访问数据。对于SELECT查询，执行器按计划读取数据并返回给客户端；对于INSERT、UPDATE、DELETE等写操作，执行器会根据计划修改数据，并确保事务的ACID特性得到保证。
权限校验：
- 在查询执行过程中，尤其是在涉及到特定表或列的操作时，执行器会与系统权限模块协作，检查当前用户是否有执行该操作的权限。如果没有相应权限，查询会被终止，并返回权限错误。
存储引擎：
- MySQL支持多种存储引擎（如InnoDB、MyISAM等）。执行器与具体的存储引擎交互，执行实际的数据读写操作。存储引擎负责管理数据的物理存储、索引结构、锁机制、事务支持等。
日志模块：
- 对于涉及数据更改的SQL语句，MySQL会记录相应的日志。例如，InnoDB存储引擎使用redo log（重做日志）保证 crash-safe 能力，binlog（二进制日志）用于复制和备份。这些日志在语句执行过程中或完成后被写入，以确保数据的一致性和可恢复性。
结果返回与清理：
- 执行完毕后，执行器将查询结果返回给客户端。如果是SELECT查询，结果集可能会被压缩后再传输。完成所有操作后，若连接不再需要，连接器会负责断开连接，释放相关资源。

综上所述，一条SQL语句在MySQL中执行的过程涉及多个组件的协作，包括连接管理、查询解析与优化、权限验证、数据访问、日志记录以及结果返回等环节，确保了SQL语句的正确执行和数据的安全访问。

2.索引失效的几种情况

数据类型不匹配：

当查询条件中的数据类型与索引列的数据类型不一致，或者转换可能导致索引无法精确匹配时，索引可能失效。例如，对整型索引列使用字符串类型的值进行查询，或对日期索引列使用错误的格式。
数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描；

不遵循最左前缀原则：

对于复合索引（包含多个列的索引），查询必须从索引的第一列开始，并按照索引定义的列顺序进行。如果查询条件没有以索引的第一列开头，或者跳过了索引中间的列，索引可能无法被充分利用。
在索引列上使用 IS NULL 或 IS NOT NULL操作。最好给列设置默认值。

使用NOT、!=或<>操作符：

对于某些类型的索引（如B树索引），使用NOT、!=或<>操作符时，MySQL可能无法有效地利用索引进行查询。因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。

使用OR连接多个条件：

or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有左右查询字段均为索引时，才会生效；

前导模糊查询：

使用LIKE操作符进行查询时，如果查询模式以通配符%开头（如LIKE '%value%'），索引通常无法发挥作用，因为这种模式不满足最左前缀匹配原则。

对索引进行表达式计算或函数操作：

如果查询条件包含对索引列进行数学运算、函数调用（如CONCAT(), SUBSTRING()，日期函数等）或类型转换，索引通常无法被直接使用，因为索引存储的是原始值而非计算后的结果。

索引列包含大量重复值：

当索引列的值分布非常集中，尤其是存在大量重复值时，即使查询使用了索引列，MySQL可能判断全表扫描比使用索引更为高效。

索引列使用范围查询后紧跟非索引列查询：

当索引列使用范围查询（如>、<、BETWEEN等）后，如果紧随其后的查询条件包含未被索引的列，那么索引可能只对范围查询部分有效，后续的查询条件可能导致索引失效。

索引未被更新或维护：

如果数据发生改变（如插入、更新或删除）后，对应的索引没有及时更新，或者索引由于长时间未进行优化（如重建、修复）而导致统计数据过时或索引结构损坏，都可能导致索引无法正常工作。

3.EXPLAIN

EXPLAIN是MySQL提供的一种用于分析SQL查询执行计划的命令。通过在查询语句前添加EXPLAIN关键字，可以获取到MySQL对这条查询的详细执行信息，包括访问哪些表、采用何种连接类型、使用哪些索引、是否进行排序和分组、估计的行数、是否使用临时表等。这些信息有助于理解查询的执行流程，识别潜在性能瓶颈，并据此进行优化。

以下是对EXPLAIN输出结果中各字段的简要解释：

id：查询执行的顺序号。相同id表示这部分查询是一个执行单元，从上到下执行。id值越大，执行优先级越高，越先被执行。
select_type：查询类型。常见的有SIMPLE（简单查询，无子查询或UNION）、PRIMARY（外部查询，最外层的SELECT）、SUBQUERY（子查询中的第一个SELECT）、DEPENDENT SUBQUERY（子查询依赖于外部查询的结果）、UNION（UNION中的第二个或后续SELECT）等。
table：查询涉及的表名。如果查询使用了别名，这里显示的是别名。
type：访问类型，即MySQL决定如何查找表中的行。从最优到最差依次为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说，最好能尽可能接近"system"或"const"。
possible_keys：查询可能使用的索引。如果为空，表示没有可用的索引。
key：实际使用的索引。如果没有使用索引，显示为NULL。
key_len：使用的索引长度，用于估算索引中使用的字节数。这可以帮助判断是否使用了索引的一部分（前缀索引）或者是否完全使用了索引。
ref：哪个列或常量与索引进行了比较。
rows：MySQL根据统计信息估算的需要读取的行数。这个值越小越好。
filtered（MySQL 5.7.8及以上版本）：一个百分比值，表示存储引擎认为需要访问的行数占总行数的比例。结合rows字段，可以更准确地预估查询的实际工作量。
Extra：包含其他额外信息，如是否使用了覆盖索引（Using index）、是否使用了临时表（Using temporary）、是否进行了文件排序（Using filesort）、是否进行了全表扫描（Using where; Using index）等。

使用EXPLAIN的示例：

EXPLAIN SELECT * FROM employees e JOIN departments d ON e.dept_id = d.id WHERE e.salary > 50000;

运行上述命令后，MySQL将返回一个表格，展示对查询的执行计划分析结果。通过解读这些信息，可以了解MySQL如何执行查询，识别是否存在全表扫描、未使用索引等问题，进而针对性地优化查询或调整索引策略。

4.Where 子句如何优化

优化WHERE子句是提高SQL查询性能的关键步骤之一。以下是一些建议和策略，帮助您优化WHERE子句以提高查询效率：

使用索引：
- 确保WHERE子句中涉及的列已被正确索引。对于频繁查询且数据范围筛选明显的列，创建合适的索引（如单列索引、复合索引、全文索引等），并遵循最左前缀原则。使用EXPLAIN分析查询计划，确认索引是否被正确使用。
避免全表扫描：
- 尽可能减少对大数据表的全表扫描。如果查询条件能限定在一个较小的数据范围内，通过索引快速定位数据，可显著提高查询速度。避免在WHERE子句中使用无法利用索引的操作，如对非索引列的范围查询、非索引列的IN列表查询等。
简化条件表达式：
- 减少复杂的逻辑表达式和嵌套查询。尽量将OR条件改写为UNION或IN子查询，因为OR可能导致索引失效。避免在WHERE子句中使用否定条件（如NOT IN、!=），它们往往难以利用索引。
避免使用函数和表达式：
- 不要在WHERE子句中对索引列使用函数或表达式，因为这会导致索引失效。如果必须使用函数或表达式，考虑是否能在查询外先计算好值，然后作为常量传入查询。
合理使用IN和EXISTS：
- 当需要检查某列值是否在一组值中时，使用IN通常比多次OR条件更高效。对于关联子查询，当只需要判断是否存在匹配记录时，使用EXISTS通常比IN或JOIN更高效。
避免在关联查询中使用!=或<>：
- 在多表关联查询中，避免在ON或WHERE子句中使用!=或<>比较关联键，因为这可能导致无法使用索引。改用LEFT JOIN ... IS NULL或NOT EXISTS等方法来实现相同的效果。
减少NULL值的处理：
- NULL值在索引中处理起来较为复杂，可能导致索引部分失效。尽量避免在频繁查询的列中使用NULL，并在设计表结构时考虑使用默认值或空字符串代替。对于必须存在的NULL值，可以创建NULL友好索引（如FULLTEXT索引、SPATIAL索引等）或使用COALESCE()、IFNULL()等函数转换为非NULL值进行查询。
避免在索引列上使用LIKE通配符：
- 对于LIKE查询，尽量避免在索引列上使用前导通配符（如'%value%'），因为这会导致索引失效。如果必须使用LIKE，尝试将通配符置于模式末尾（如'value%'），并确保模式足够短以利用索引。
批量处理：
- 如果需要对大量数据进行过滤，考虑将多次单个查询合并为一次批量查询，利用IN或JOIN处理，减少网络通信开销和数据库服务器的压力。
定期维护索引和统计信息：
- 定期对表进行分析（如ANALYZE TABLE），更新索引统计信息，确保优化器能做出准确的成本估算。定期检查并重建（如OPTIMIZE TABLE）或修复（如REPAIR TABLE）索引，保持索引结构健康。

通过以上策略优化WHERE子句，可以有效提高查询性能，降低数据库服务器负载，提升应用程序响应速度。记得在修改查询后使用EXPLAIN分析查询计划，验证优化效果。

5.超大分页或深度分页如何处理

处理超大分页或深度分页时，由于涉及大量数据的检索和排序，传统基于LIMIT和OFFSET的分页方式往往会面临性能瓶颈。以下是一些优化超大分页或深度分页的方法：

Keyset（Cursor-Based）分页：
- 使用有序的唯一键或索引来实现分页。首次查询时，获取指定数量的记录以及最后一个记录的排序键值。后续分页时，直接使用这个键值作为起始点进行查询，而不是使用OFFSET。这样可以避免跳跃式读取大量无关数据，显著减少IO和CPU负担。
示例：
```
-- 第一页
SELECT * FROM table ORDER BY sort_column LIMIT 10;

-- 第二页，假设上一页最后一条记录的sort_column值为last_value
SELECT * FROM table WHERE sort_column > last_value ORDER BY sort_column LIMIT 10;
```
优化LIMIT和OFFSET查询：
- 避免使用大数值的OFFSET。当OFFSET值较大时，可以尝试改写查询，利用索引来直接定位数据。例如，如果表有连续的主键或唯一索引，且数据按主键递增排列，可以利用主键值直接跳过不需要的数据。
示例：
```
-- 原查询：SELECT * FROM table LIMIT 1000000, 10
-- 优化为：SELECT * FROM table WHERE id > 1000000 LIMIT 10
```
利用索引覆盖（Index Covering）：
- 创建覆盖索引来包含查询所需的所有列，使得查询仅需从索引中获取数据，无需回表，从而减少IO。如果查询结果集可以完全由索引提供，性能将大大提升。

子查询优化：

使用子查询预先筛选出需要的记录范围，然后再进行主查询，避免在大表上直接进行排序和分页。

示例：

-- 原查询：SELECT * FROM table ORDER BY sort_column LIMIT 1000000, 10
-- 优化为：SELECT * FROM (
    SELECT id FROM table ORDER BY sort_column LIMIT 1000000, 10
) AS subquery
JOIN table ON table.id = subquery.id;

复合索引优化：
- 根据查询条件和排序需求，创建复合索引来同时涵盖筛选条件和排序字段，减少排序开销。
基于索引的再排序：
- 如果必须对大量数据进行排序，考虑在索引中预先存储排序后的数据，或者在查询时仅对索引进行排序，避免对整个数据集进行排序。
利用PREPARE语句：
- 对于频繁执行的分页查询，使用PREPARE语句预编译查询计划，可以减少解析和优化的时间。
数据分片（Sharding）：
- 如果数据规模极其庞大，考虑使用分片技术将数据分散到多个物理节点上。这样，分页查询仅需在单个分片上执行，显著减小数据处理量。
缓存策略：
- 对于访问频繁且结果集相对固定的分页查询，可以利用缓存（如Redis、Memcached等）存储部分或全部结果，减少数据库查询压力。特别是对于较深的分页，缓存前几页或者最近访问过的页面可以显著提升用户体验。
应用层面分页：
- 如果业务允许，将分页操作移到应用层面。例如，先获取所有符合条件的ID列表（使用索引来快速获取），然后在应用中进行分页。这种方法适用于数据总量大但实际展示数据量小的场景。

综上所述，处理超大分页或深度分页时，应结合具体业务场景和数据特点，灵活运用上述策略进行优化。关键在于减少不必要的数据访问、排序和传输，以及合理利用索引来加速查询。同时，持续监控查询性能，根据实际情况调整优化措施。