MySQL学习从零开始--第七部分

什么是全表扫描，哪些操作可能会导致全表扫描，如何避免

• 全表扫描：指数据库查询时未使用索引，而是逐行扫描表中所有记录以满足查询条件的操作，效率极低
• 导致全表扫描的操作
  • 未给查询条件中的字段建立索引。
  • 使用 SELECT * 且无有效索引。
  • 索引字段使用函数（如 WHERE SUBSTR(name,1,1)='A'）。
  • 索引字段参与运算（如 WHERE age+1=10）。
  • 使用 NOT IN、<>, IS NOT NULL（部分场景）。
  • 模糊查询以 % 开头（如 WHERE name LIKE '%abc'）。
  • 联合索引未满足最左匹配原则。
• 避免方式
  • 为查询条件中的字段建立合适的索引。
  • 避免使用 SELECT *，只查询需要的字段。
  • 避免在索引字段上使用函数或运算。
  • 优化模糊查询（如用 %abc% 时考虑全文索引）。
  • 调整查询条件，避免使用导致索引失效的操作。

什么是回表，如何避免

• 回表：指使用非聚集索引（如 MySQL 的二级索引）查询时，索引叶子节点只存储索引值和主键，需通过主键再次查询聚簇索引获取完整数据的过程。
• 避免方式：
  • 使用覆盖索引：将查询所需字段包含在索引中（如 CREATE INDEX idx_name ON t(name, age)，查询 SELECT name, age FROM t WHERE name='xxx' 可直接从索引获取数据）。
  • 主键查询：直接使用聚簇索引，无需回表

什么情况下索引会失效

• 使用 OR 查询但只有部分条件命中索引

  使用函数、表达式（如 abs(id)）

  隐式类型转换（如 id = '123' 但 id 是 int）

  %关键词% 模糊查询

  对复合索引中不是最左前缀的字段查询（破坏最左匹配原则）

  使用 !=、NOT IN、IS NULL 可能失效

  小表使用索引反而性能下降，优化器放弃索引

子查询和 JOIN 哪种性能更高，各有什么优缺点
小数据量子查询更简洁，大数据量 JOIN 性能更优（需合理索引）

• 类型	性能特点	优点	缺点
  子查询	小数据量场景性能尚可	逻辑清晰，适合简单嵌套查询	大数据量下可能多次扫描表，效率低
  JOIN	大数据量场景通常更优	可通过索引优化，减少表扫描次数	复杂 JOIN 可能导致临时表或文件排序

IN 和 EXISTS有什么区别，使用场景是什么

• IN：将外层查询结果与子查询的结果集逐个对比，适合结果集小
  EXISTS：检查是否“存在”符合条件的记录，适合子查询结果集大
  使用建议：
  • 子查询结果集小：用 IN
  • 子查询结果集大：用 EXISTS

如何记录慢查询日志，如何优化慢查询

• 记录慢查询日志
  • 开启：SET GLOBAL slow_query_log = 1；设置阈值：SET GLOBAL long_query_time = 1（秒）。
  • 日志位置：通过 show variables like 'slow_query_log_file' 查看。
• 优化慢查询
  • 添加或优化索引。
  • 避免全表扫描和回表。
  • 拆分大查询为小查询。
  • 优化 JOIN 操作，避免笛卡尔积。
  • 使用 EXPLAIN 分析执行计划，调整查询逻辑

什么是查询优化器，用于什么场景，执行计划是什么，是如何实现的

• 查询优化器：数据库内置组件，根据统计信息（如数据量、索引分布）生成最优执行计划的模块。
• 使用场景：所有 SQL 查询执行前，自动选择最优路径（如是否使用索引、JOIN 顺序等）。
• 执行计划：优化器生成的查询执行步骤，包含是否使用索引、表扫描方式、JOIN 类型等信息（通过 EXPLAIN 查看）。
• 实现方式
  • 基于规则（RBO）：按预设规则选择执行路径（如优先使用索引）。
  • 基于成本（CBO）：计算不同执行路径的成本（IO、CPU 消耗），选择成本最低的方案

如何根据执行计划来优化我的sql

• 查看 type 列：目标是 ref、range 或 eq_ref，避免 ALL（全表扫描）。
• 查看 key 列：确认是否使用了预期索引，未使用则检查索引是否失效。
• 查看 rows 列：预估扫描行数，行数过大需优化索引或查询条件。
• 查看Extra列：
  • 避免 Using filesort（文件排序）：优化排序字段，添加合适索引。
  • 避免 Using temporary（临时表）：减少 GROUP BY 或 DISTINCT 的使用，或优化索引。
  • 避免 Using where; Using index 外的 Using where（可能需覆盖索引）

什么是分区表，有哪几种类型，适用于什么场景

• 分区表：将大表按规则拆分为多个物理子表，提高查询效率。
• 类型：
  • 范围分区：按字段范围划分（如按时间 PARTITION p2023 VALUES LESS THAN ('2024-01-01')）。
  • 列表分区：按字段枚举值划分（如按地区 PARTITION p_asia VALUES IN ('CN', 'JP')）。
  • 哈希分区：按字段哈希值平均分配（适合数据均匀分布场景）。
  • 键分区：类似哈希分区，由数据库自动计算分区键。
• 适用场景
  • 表数据量超千万级，且查询多集中在部分数据（如按时间查询历史数据）。
  • 需快速删除历史数据（直接删除分区）。

什么是分表，与分区表有什么区别，什么情况下应当进行分表

• 分表：将一张大表从物理上拆分为多张独立的小表（逻辑上为多张表），需应用层适配。

• 与分区表的区别

  维度	分区表	分表
  物理存储	子表同属一个数据库	子表可独立存储（同库或跨库）
  逻辑管理	数据库自动管理	需应用层手动管理（如路由）
  适用场景	单库内数据拆分	跨库拆分或单库无法承载

• 分表时机：单表数据量超亿级，或查询性能无法通过索引 / 分区优化提升时

分表有哪些策略，对应什么样的场景

• 按时间分表（适合日志、订单）

  按用户ID哈希（适合用户信息）

  按业务维度（如产品、城市）

什么是分库，什么场景下需要进行分库，有哪些策略

• 将数据拆分到多个数据量，减轻单机的压力
• 场景：单库性能瓶颈，高并发高数据量写入
• 策略：垂直分库-按业务模块，水平分库-按用户ID或时间

生成一个分库分表的checklist

1. 确定分库分表维度（水平 / 垂直，范围 / 哈希）。
2. 设计路由规则（如 ID 哈希、范围映射）。
3. 保证全局唯一主键（如雪花算法、UUID）。
4. 处理跨库事务（如分布式事务 TCC/SAGA）。
5. 适配关联查询（如应用层聚合、冗余字段）。
6. 考虑扩容方案（如增加分表数量时的迁移策略）。
7. 监控与运维（分表状态、数据均衡性）。
8. 数据迁移方案（全量 + 增量迁移，兼容旧系统）

分库分表后如何保证全局唯一主键

• 雪花算法：使用机器ID+时间戳生成唯一ID
• 数据库中间件统一分配主键

分库分表后的聚合查询如何实现

• 在应用层做多库多表聚合，再统一处理
• 使用中间件（如 ShardingSphere、TiDB）支持聚合查询
• 数据汇总定时同步到中间表（如数据仓库）

什么是查询缓存，为什么选择Redis作为查询缓存

• 查询缓存：将数据库查询结果暂存在内存中，后续相同查询直接从缓存获取，减少数据库访问
• Redis 优点：
  • 访问速度快（内存存储）
  • 支持丰富数据结构
  • 可设置过期时间：支持键过期自动删除，方便管理缓存生命周期
  • 支持分布式缓存架构：可集群部署，支持高可用

使用redis作为查询缓存时，有哪些缓存策略，分别要应对什么情况

• Cache-Aside（旁路缓存）：先查缓存，未命中则查数据库并更新缓存，适合读多写少场景。
• Write-Through（写透）：写操作同时更新缓存和数据库，保证数据一致性，适合数据一致性要求高的场景。
• Write-Back（写回）：先更新缓存，异步更新数据库，适合写性能要求高的场景（可能丢失数据）。
• 过期策略
  • 时间过期：设置 TTL，避免缓存长期有效（如热点数据 10 分钟过期）。
  • 主动删除：更新数据时主动删除缓存，避免脏数据。
• 缓存穿透应对：缓存空结果、布隆过滤器过滤无效请求。
• 缓存击穿应对：热点数据永不过期、互斥锁防止并发回源。
• 缓存雪崩应对：过期时间加随机值、集群部署避免单点失效。

什么是冷热数据分离，有什么好处

• 冷热数据分离：将频繁访问的数据（热）和少访问的数据（冷）分开存储管理。

  好处：

  • 降低存储成本（冷数据用慢速磁盘）
  • 提升性能（热数据在高性能磁盘）
  • 便于数据归档和清理

mysql中如何监控表的膨胀和碎片

SHOW TABLE STATUS LIKE 'table_name';

关注字段：

• Data_length：数据大小
• Index_length：索引大小
• Data_free：碎片空间

mysql中删除了大量数据，但是数据库大小却没有减少，为什么，应该如何处理

• 因为删除只是标记空间可用，未释放文件空间。(避免频繁申请/释放空间)
  执行 OPTIMIZE TABLE table_name（InnoDB 会重建表，释放碎片空间，期间表锁定）

MySQL 在大数据场景下如何进行瓶颈分析，比如IO、CPU、连接、索引

• IO 瓶颈
  • 症状：iostat 显示磁盘 %util 接近 100%，await （IO 响应时间）过高。
  • 分析：慢查询多、全表扫描、日志刷盘频繁。
  • 优化：加索引、分库分表、开启 innodb_flush_log_at_trx_commit=2（权衡一致性）、使用 SSD。
• CPU 瓶颈
  • 症状：top 显示 MySQL 进程 CPU 使用率高。
  • 分析：复杂查询（如多表 JOIN、排序）、频繁计算（如函数 / 运算）。
  • 优化：简化查询、避免大表排序、用缓存减少计算。
• 连接瓶颈
  • 症状：show processlist 显示大量连接，Too many connections 错误。
  • 分析：连接池配置不合理、长连接未释放。
  • 优化：调大 max_connections、使用连接池（如 HikariCP）、设置 wait_timeout 自动关闭闲置连接。
• 索引瓶颈
  • 症状：查询慢，但 CPU/IO 不高，执行计划显示全表扫描。
  • 分析：索引缺失、索引失效、索引过多（维护成本高）。
  • 优化：重建有效索引、删除冗余索引、优化查询避免索引失效。