Mysql-Day7

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

什么是全表扫描？

全表扫描（Full Table Scan） 是指数据库在执行查询时，没有使用索引，而是从头到尾逐行扫描整张表的所有数据来查找符合条件的记录。这种方式效率较低，尤其在表数据量大时会显著影响查询性能。

哪些操作可能会导致全表扫描？

如何避免全表扫描？

推荐做法

2. 什么是回表，如何避免

回表（Table Access / Back to Table） 是指在使用**非聚簇索引（二级索引）**查询数据时，数据库引擎需要先通过二级索引找到主键值，然后再根据主键值去聚簇索引中查找完整的行记录的过程。

如何避免回表？

方法一：使用覆盖索引（Covering Index）

定义：将查询所需的字段全部包含在索引中，使查询完全命中索引，无需回表。

示例：

-- 创建联合索引包含查询所需字段
ALTER TABLE users ADD INDEX idx_age_name_email (age, name, email);

-- 查询字段都在索引中，不会回表
SELECT name, email FROM users WHERE age > 30;

方法二：使用主键查询

直接使用主键进行查询，不需要回表。

SELECT * FROM users WHERE id = 100;

方法三：减少不必要的字段查询

避免使用 SELECT *，只查询需要的字段，并确保这些字段都在索引中。

-- 只查 name 和 age，并确保它们在索引中
SELECT name, age FROM users WHERE age > 30;

方法四：使用复合索引优化查询字段组合

对经常一起使用的查询条件建立复合索引，提升索引命中率。

-- 假设经常按 name 和 age 查询
ALTER TABLE users ADD INDEX idx_name_age (name, age);

3. 什么情况下索引会失效

在使用索引进行数据库查询时，索引失效（Index失效） 是指数据库无法有效利用索引，导致查询性能下降甚至退化为全表扫描。以下是常见的索引失效场景及避免方法。

一、常见导致索引失效的场景

4. 子查询和 JOIN 哪种性能更高，各有什么优缺点

在数据库查询中，子查询（Subquery） 和 JOIN 操作 是两种常见的数据关联方式。它们各有优劣，适用于不同场景。以下是详细的对比分析：

一、性能对比：子查询 vs JOIN

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

IN 和 EXISTS 是 SQL 中常用于子查询的两个关键字，它们都可以用来判断某个条件是否满足，但工作方式、性能表现和适用场景不同。以下是详细对比：

一、基本概念

IN

• 用途：判断某字段值是否在给定集合中。

• 语法示例：

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

EXISTS

• 用途：判断子查询是否有返回结果（不关心具体值）。

• 语法示例：

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

二、核心区别

三、使用场景对比

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

一、如何记录慢查询日志

MySQL 中开启慢查询日志的步骤：

1. 查看当前慢查询配置

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';

2. 临时开启慢查询日志（仅限本次运行）

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 设置超过多少秒为“慢查询”，如 1 秒
SET GLOBAL log_output = 'FILE'; -- 输出方式：文件或表（建议使用 FILE）

3. 指定慢查询日志路径（可选）

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

4. 记录未使用索引的查询（可选）

SET GLOBAL log_queries_not_using_indexes = ON;

5. 永久生效（修改配置文件） 在 my.cnf 或 my.ini 中添加：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_output = FILE
log_queries_not_using_indexes = 1

二、如何分析慢查询日志

使用 mysqldumpslow（MySQL 自带工具）

mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

• s at：按查询时间排序；

• -t 10：显示前 10 条；

• 其他常用参数：

  • -s al：按锁等待时间排序；

  • -s ar：按返回记录数排序；

使用 pt-query-digest（Percona Toolkit 工具）

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

特点：

• 支持聚合统计；

• 显示执行次数、平均耗时、最大耗时等指标；

• 推荐用于生产环境分析。

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

一、什么是查询优化器（Query Optimizer）？

查询优化器 是数据库管理系统中的一个核心组件，其主要作用是将用户提交的 SQL 查询语句转换为最高效的执行路径（Execution Plan），以尽可能快地返回结果并减少资源消耗。

它负责分析多个可能的执行路径，并选择代价最小的一种。这种“代价”通常基于 CPU 使用率、I/O 次数、内存消耗等指标估算。

二、查询优化器用于什么场景？

三、执行计划（Execution Plan）是什么？

执行计划 是查询优化器输出的结果，它描述了数据库将如何执行你的 SQL 查询。它是优化器根据当前统计信息、索引结构、数据分布等因素推导出的一组操作步骤。

示例：

EXPLAIN SELECT * FROM users WHERE age > 30;

输出可能如下（MySQL 示例）：

关键字段解释：

• type: 访问类型（ALL 全表扫描，range 索引范围扫描）

• key: 实际使用的索引

• rows: 预估需要扫描的行数

• Extra: 额外信息（是否回表、是否排序）

四、查询优化器是如何实现的？

查询优化器通常分为两个阶段：

1. 逻辑优化（Logical Optimization）

• 目的：将 SQL 转换为等价但更高效的逻辑表达式。

• 常见操作：

  • 子查询重写为 JOIN；

  • 排除冗余条件（如 WHERE 1=1 AND name = 'Alice' → WHERE name = 'Alice'）；

  • 视图合并（View Merging）；

  • 谓词下推（Pushing down predicates to early stages）；

2. 物理优化（Physical Optimization）

• 目的：基于实际数据分布、索引统计信息、代价模型，选择最优的执行路径。

• 关键技术：

  • 代价模型（Cost Model）：评估不同执行路径的 I/O、CPU、内存开销；

  • 动态规划（Dynamic Programming）：对多表连接顺序进行组合计算，找出最优路径；

  • 统计信息（Statistics）：利用 ANALYZE TABLE 更新的统计信息（如行数、索引基数）；

  • 索引选择性判断：判断某个索引是否值得使用；

  • 并行执行决策：决定是否并行执行某些操作（如并行扫描、并行聚合）；

五、常见的优化策略

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

一、如何获取执行计划？

MySQL：

EXPLAIN SELECT * FROM users WHERE age > 30;

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

一、什么是分区表？

分区表（Partitioned Table） 是将一个大表从逻辑上划分成多个物理子集（分区），每个分区可以独立进行存储、查询和管理。虽然对用户来说仍然是一张表，但数据库内部会将数据按一定规则分布到不同文件或磁盘位置中。

二、分区表的类型

常见的分区方式有以下几种：

示例：MySQL 中创建分区表

RANGE 分区：

CREATE TABLE sales (
    id INT,
    sale_date DATE
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

HASH 分区：

CREATE TABLE users (
    id INT,
    name VARCHAR(50)
) PARTITION BY HASH(id) PARTITIONS 4;

KEY 分区：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT
) PARTITION BY KEY() PARTITIONS 4;

三、适用场景

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

一、什么是分表？

分表（Table Sharding） 是指将一张大表的数据逻辑上或物理上拆分成多个独立的小表，每个小表结构相同但数据不同。这种拆分可以是水平分表（按行拆分） 或 垂直分表（按列拆分）。

二、分表的类型

水平分表示例：

-- 主表 orders 已拆分为多个子表
CREATE TABLE orders_2024 (
    id INT,
    user_id INT,
    amount DECIMAL(10,2)
);

CREATE TABLE orders_2025 (
    id INT,
    user_id INT,
    amount DECIMAL(10,2)
);

垂直分表示例：

-- 用户基本信息
CREATE TABLE user_base (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20)
);

-- 用户详细信息
CREATE TABLE user_profile (
    user_id INT PRIMARY KEY,
    address TEXT,
    avatar_url VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user_base(id)
);

三、分区表 vs 分表：核心区别

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

一、分表的常见策略

分表（Table Sharding） 是将一张大表的数据按照一定规则拆分为多个小表，以提升查询效率和系统可扩展性。常见的分表策略如下：

二、每种分表策略适用的场景

1. 水平分表（Horizontal Sharding）

• 适用场景：

  • 数据量极大（如单表超过 1 亿条）；

  • 查询条件固定（如按年份、地区等）；

  • 高并发写入导致锁竞争严重；

• 优点：

  • 减少单表数据量，提高查询性能；

  • 可并行处理多个子表；

• 缺点：

  • 聚合统计、JOIN 查询复杂；

  • 应用层需管理分表逻辑。

2. 垂直分表（Vertical Sharding）

• 适用场景：

  • 表字段较多，部分字段更新频繁，部分字段很少使用；

  • 查询模式差异大（有些只查基础字段，有些涉及大量文本）；

• 优点：

  • 提高 I/O 效率；

  • 减少热点字段的锁冲突；

• 缺点：

  • 外键引用困难；

  • JOIN 查询性能下降。

3. 按范围分表（Range-based Sharding）

• 适用场景：

  • 时间序列数据（如日志、订单）；

  • 历史数据归档；

  • 冷热数据分离；

• 优点：

  • 易于清理历史数据；

  • 查询命中特定时间段时性能更高；

• 缺点：

  • 数据分布可能不均；

  • 新旧数据访问频率差异可能导致资源浪费。

4. 哈希分表（Hash-based Sharding）

• 适用场景：

  • 数据分布无明显规律；

  • 需要负载均衡；

  • 高并发读写；

• 优点：

  • 数据均匀分布，避免热点；

  • 支持大规模分布式部署；

• 缺点：

  • 查询无法利用索引优化；

  • 聚合操作复杂。

5. 按地理位置分表（Geographic Sharding）

• 适用场景：

  • 用户地域分布明确；

  • 需要低延迟访问；

  • 多数据中心部署；

• 优点：

  • 提升本地访问速度；

  • 支持灾备和容错；

• 缺点：

  • 管理复杂度高；

  • 不适合用户跨区域移动频繁的系统。

6. 按业务逻辑分表（Logical Sharding）

• 适用场景：

  • 业务模块清晰区分（如订单、支付、物流）；

  • 不同业务有不同访问模式；

• 优点：

  • 模块化管理；

  • 便于独立维护；

• 缺点：

  • 查询跨业务时性能差；

  • 主键命名需统一管理。

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

一、什么是分库？

分库（Database Sharding） 是将一个数据库实例中的多个表拆分到不同的独立数据库中。每个数据库实例拥有相同的表结构，但存储的数据不同。它是一种横向扩展策略，用于解决单库容量瓶颈和性能问题。

与“分表”不同的是：

• 分表：在同一数据库内将一张大表拆分为多个小表；

• 分库：将数据分布到多个物理数据库中，可能部署在不同服务器上；

二、什么场景下需要进行分库？

三、常见的分库策略

13. 生成一个分库分表的checklist

分库分表 Checklist

在进行分库分表设计与实施前，应系统性地评估业务需求、数据增长趋势、查询模式和架构扩展能力。以下是完整的 Checklist，帮助你判断是否需要进行分库分表，并选择合适的策略。

一、是否需要分库分表？

二、分库策略选择

推荐组合：

• 按范围 + 垂直：适用于日志/订单 + 详情分离；

• 按业务 + 哈希：适用于多模块 + 高并发写入；

三、分表策略选择

推荐组合：

• 哈希 + 垂直：适用于高并发 + 字段多变；

• 按范围 + 哈希：适用于历史数据归档 + 负载均衡；

四、技术实现检查清单

五、上线前验证清单

六、运维监控建议

七、常见误区与避坑指南

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

在分库分表架构中，传统的自增主键（AUTO_INCREMENT）已无法满足全局唯一性要求。因此，必须采用其他机制来保证分布式环境下的主键唯一性和有序性。

一、常见解决方案及适用场景

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

在分库分表架构中，由于数据被拆分到多个物理节点或子表中，传统的聚合查询（如 COUNT, SUM, AVG, GROUP BY）将无法直接通过单一 SQL 语句完成。需要借助一定的策略和工具来实现。

一、常见问题与挑战

二、解决方案及适用场景

三、详细方案解析

1. 应用层手动聚合

• 实现方式：

  • 在应用代码中分别访问每个子库/子表；

  • 获取局部结果后再进行合并计算；

• 优点：

  • 实现简单；

  • 不依赖中间件；

• 缺点：

  • 并发大时性能差；

  • 维护成本高；

• 适用场景：

  • 聚合频率低；

  • 结果集小；

  • 对实时性要求不高；

2. 中间件聚合（如 ShardingSphere、MyCat）

• 实现方式：

  • 使用中间件代理所有数据库请求；

  • 中间件自动路由查询并聚合结果；

• 优点：

  • 透明化分库分表逻辑；

  • 支持复杂聚合操作；

• 缺点：

  • 有学习成本；

  • 性能损耗（需经过中间层）；

• 适用场景：

  • 需要统一访问接口；

  • 查询模式复杂多变；

  • 需要兼容原有 SQL 习惯；

3. 物化视图 / 预聚合表

• 实现方式：

  • 定期执行聚合任务（如每小时一次）；

  • 将结果写入预聚合表；

• 优点：

  • 查询响应快；

  • 减少数据库压力；

• 缺点：

  • 实时性差；

  • 存储空间占用增加；

• 适用场景：

  • 报表类系统；

  • BI 展示；

  • 不要求毫秒级一致性的业务；

4. Elasticsearch / Redis 缓存聚合结果

• 实现方式：

  • 将聚合结果缓存至 Elasticsearch 或 Redis；

  • 查询时优先读取缓存；

• 优点：

  • 高并发下性能好；

  • 可支持全文检索；

• 缺点：

  • 需要维护缓存同步机制；

  • 成本较高；

• 适用场景：

  • 实时监控面板；

  • 高频访问的报表；

  • 对延迟容忍度较高的业务；

5. 定时任务 + 批量汇总（ETL）

• 实现方式：

  • 使用 ETL 工具（如 Sqoop、DataX）定时拉取各分片数据；

  • 汇总后写入集中式仓库；

• 优点：

  • 数据集中便于分析；

  • 降低在线数据库负载；

• 缺点：

  • 实时性差；

  • 开发维护成本高；

• 适用场景：

  • 数据仓库构建；

  • 日终对账；

  • 复杂维度分析；

6. 大数据平台（ClickHouse / Spark / Hadoop）

• 实现方式：

  • 将分库分表数据导入大数据平台；

  • 利用其强大的分布式计算能力进行聚合；

• 优点：

  • 支持 PB 级数据分析；

  • 支持复杂 OLAP 查询；

• 缺点：

  • 架构复杂；

  • 实时性一般；

• 适用场景：

  • 大数据分析；

  • 用户行为分析；

  • 风控建模；

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

一、什么是查询缓存？

查询缓存（Query Cache） 是数据库或应用层用于存储 SQL 查询结果的一种机制。其核心思想是：将相同的 SQL 查询结果缓存起来，避免重复执行相同查询，从而提升系统性能。

常见的查询缓存层级：

二、为什么选择 Redis 作为查询缓存？

Redis 是一个高性能的内存数据库，广泛用于分布式系统的缓存场景。在数据库查询缓存中，Redis 具有以下优势：

三、Redis 作为查询缓存的典型使用场景

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

在使用 Redis 作为查询缓存时，常见的缓存策略主要包括以下几种。每种策略都有其适用场景和优缺点，选择合适的策略可以有效提升系统性能并减少数据库压力。

常见的 Redis 缓存策略

1. Cache-Aside（旁路缓存）

• 描述：应用层负责管理缓存与数据库之间的同步。当数据请求发生时，首先从 Redis 中读取数据；如果未命中，则从数据库中读取，并将结果写入 Redis。

• 写操作处理：更新数据库后，手动删除或更新 Redis 中的缓存。

• 适用场景：

  • 数据读多写少的场景。

  • 对缓存一致性要求不高的场景。

• 优点：

  • 实现简单，灵活性高。

  • 不依赖 Redis 的复杂功能。

• 缺点：

  • 需要应用层维护缓存一致性。

  • 初次加载时会有缓存穿透风险。

• 应对情况：

  • 缓存穿透：可通过布隆过滤器（Bloom Filter）拦截无效请求。

  • 缓存击穿：对热点数据设置永不过期或使用互斥锁（Mutex）控制并发重建。

  • 缓存雪崩：为缓存设置随机过期时间，避免大量缓存同时失效。

2. Read-Through / Write-Through（读写穿透）

• 描述：缓存层（如 Redis）与数据库之间通过中间层（如缓存服务）进行自动同步。当读取数据时，如果缓存未命中，由中间层自动从数据库加载并写入缓存；写操作时，先更新数据库再更新缓存。

• 适用场景：

  • 需要强一致性的场景。

  • 系统架构较为复杂，需要统一的数据访问层。

• 优点：

  • 应用层无需关心缓存的加载和更新逻辑。

  • 可以保证缓存与数据库的一致性。

• 缺点：

  • 实现复杂，依赖额外的中间件。

  • 写操作可能增加延迟。

• 应对情况：

  • 缓存一致性：适用于对数据一致性要求较高的业务场景。

3. Write-Behind（异步回写）

• 描述：写操作先写入缓存，然后由后台任务异步刷新到数据库。这种方式可以显著提高写性能。

• 适用场景：

  • 写操作频繁且对实时性要求不高的场景。

  • 需要高吞吐量的应用。

• 优点：

  • 提升写性能，降低数据库压力。

• 缺点：

  • 数据可能存在短暂不一致。

  • 如果 Redis 故障，可能导致数据丢失。

• 应对情况：

  • 数据可靠性：适合非关键数据（如日志、统计信息）。

  • 容错机制：需配合持久化和故障恢复机制。

4. TTL + LRU（生存时间 + 最近最少使用）

• 描述：为缓存设置一个生存时间（TTL），并在缓存满时采用 LRU 算法淘汰最近最少使用的数据。

• 适用场景：

  • 数据有明显冷热区分的场景。

  • 缓存容量有限的情况下。

• 优点：

  • 自动管理缓存生命周期，避免内存溢出。

• 缺点：

  • 可能导致部分热点数据被提前淘汰。

• 应对情况：

  • 缓存淘汰：适用于数据访问模式具有局部性特征的场景。

5. All-In-One（全量缓存）

• 描述：将所有数据都缓存到 Redis 中，数据库仅用于持久化存储。Redis 成为主要数据源。

• 适用场景：

  • 数据量较小且对性能要求极高的场景。

  • 需要快速响应的实时系统。

• 优点：

  • 极高的读写性能。

• 缺点：

  • Redis 宕机会导致服务不可用。

  • 需要强大的 Redis 集群支持和容灾机制。

• 应对情况：

  • 高可用性：适用于对性能要求极高但可接受一定风险的场景。

总结与推荐

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

什么是冷热数据分离？

冷热数据分离是一种数据管理策略，根据数据的访问频率和重要性将数据划分为：

• 热数据（Hot Data）：频繁访问的数据，要求高性能、低延迟的存储和快速响应。

• 温数据（Warm Data）：偶尔访问的数据，对性能要求中等。

• 冷数据（Cold Data）：极少访问的历史数据或归档数据，对访问速度要求较低。

通过将不同类型的数据存储在不同的系统、数据库、表或存储介质中，实现资源的最优利用。

冷热数据分离的常见实现方式

1. 按时间分片：

   • 最常见的做法是根据数据的时间属性进行划分。

   • 例如：最近 30 天的数据作为热数据存储在高性能数据库中，超过 30 天的数据迁移至低成本存储或归档系统。

2. 垂直分表 / 水平分表：

   • 将热数据与冷数据拆分到不同表中，提升查询效率。

   • 垂直分表可将常用字段与不常用字段分离，减少 I/O 负载。

3. 分库分表：

   • 使用分库策略将热数据部署在高性能数据库集群中，冷数据部署在成本更低、容量更大的存储节点上。

4. 使用不同存储引擎：

   • 热数据：使用内存型数据库（如 Redis）、SSD 存储的 MySQL；

   • 冷数据：使用磁带存储、对象存储（如 S3）、HDFS 等低成本方案。

5. Elasticsearch 分片策略：

   • 利用 hot-warm-cold 架构，设置不同类型的节点承载不同类型的数据。

   • 热节点用于写入和高频查询，冷节点用于长期存储。

6. 日志类系统的 TTL（Time To Live）机制：

   • 设置索引或数据的生存时间，自动清理过期数据或将数据归档。

冷热数据分离的好处

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

在 MySQL 中，表的膨胀（Table Bloat）和碎片（Fragmentation）通常是指由于频繁的 INSERT、UPDATE 和 DELETE 操作导致数据文件中存在大量未使用的空间。这不仅浪费存储资源，还可能影响查询性能。

下面是如何监控 MySQL 表的膨胀与碎片的方法：

一、使用系统表 information_schema.TABLES 查看表统计信息

你可以通过以下 SQL 查询查看每个表的平均行长度、数据长度和索引长度等信息：

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    TABLE_ROWS AS `Rows`,
    DATA_LENGTH / 1024 / 1024 AS `DataSize_MB`,
    INDEX_LENGTH / 1024 / 1024 AS `IndexSize_MB`,
    (DATA_FREE / 1024 / 1024) AS `FreeSpace_MB`
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
    AND ENGINE = 'InnoDB'
ORDER BY 
    FreeSpace_MB DESC;

• DATA_LENGTH：表示数据占用的空间大小。

• INDEX_LENGTH：表示索引占用的空间大小。

• DATA_FREE：表示当前表中已分配但尚未使用的空间，是判断表是否膨胀的重要指标。

如果 FreeSpace_MB 值较大，说明该表可能存在较多碎片或空闲空间，可以考虑进行优化。

二、使用 SHOW TABLE STATUS 查看单个表的状态

你可以对某个具体表执行如下命令：

SHOW TABLE STATUS FROM your_database LIKE 'your_table';

输出字段包括：

• Rows: 表中的记录数；

• Data_length: 数据段大小；

• Index_length: 索引段大小；

• Data_free: 已分配但未使用的空间大小；

• Engine: 使用的存储引擎（建议为 InnoDB）；

三、使用 innodb_sys_tables 和 innodb_sys_indexes（仅适用于 InnoDB）

如果你使用的是 InnoDB 存储引擎，还可以直接查询 InnoDB 的内部系统表来获取更详细的信息：

SELECT 
    t.NAME AS table_name,
    t.SPACE as space_id,
    t.PAGE_NO as root_page,
    i.INDEX_ID,
    i.NAME as index_name,
    i.PAGE_NO as index_root_page,
    i.TYPE as index_type
FROM 
    information_schema.INNODB_SYS_TABLES t
JOIN 
    information_schema.INNODB_SYS_INDEXES i ON t.TABLE_ID = i.TABLE_ID
WHERE 
    t.NAME LIKE 'your_database/your_table';

更多关于 InnoDB 内部结构的信息可参考官方文档或使用 INFORMATION_SCHEMA 扩展。

四、定期分析慢查询日志和表统计信息

结合《数据库慢查询日志与优化规范》中提到的内容，建议定期使用 ANALYZE TABLE 更新表的统计信息：

sqlANALYZE TABLE your_table;

五、使用第三方工具辅助监控

1. pt-index-usage

Percona Toolkit 提供了多个用于分析表和索引的工具，如 pt-index-usage 可以帮助识别低效索引。

2. MySQL Workbench

图形化工具支持查看表的空间使用情况、索引效率等。

3. Prometheus + Grafana

结合 Prometheus 的 mysqld_exporter 和 Grafana 可视化模板，可以实现对表膨胀、碎片率等指标的实时监控。

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

在 MySQL 中，即使你删除了大量数据（例如执行 DELETE 操作），数据库文件的大小并不会立即减少。这是因为 MySQL 的存储引擎（尤其是 InnoDB）会保留已分配的空间，以便后续的插入或更新操作使用，避免频繁地进行磁盘空间申请和释放。

为什么删除数据后数据库大小不变？

InnoDB 存储机制特性：

1. 空间回收机制：

   • 删除数据后，InnoDB 会标记这些空间为“可重用”，但不会立即返还给操作系统。

   • 后续的 INSERT 或 UPDATE 操作会优先使用这些“空闲空间”。

2. 表空间未收缩：

   • InnoDB 表空间（如 ibdata1 或独立的 .ibd 文件）一旦增长，就不会自动缩小。

   • 即使整张表的数据都被删除，其占用的空间也不会自动释放。

3. 碎片化问题：

   • 频繁的 DELETE 和 UPDATE 操作会导致表空间碎片化，使得实际占用空间大于有效数据所需。

如何让数据库文件真正变小？

方法一：使用 OPTIMIZE TABLE（推荐）

sql

• 作用：重建表并整理碎片，将未使用的空间返还给操作系统。

• 适用场景：删除了大量数据、表存在严重碎片。

• 注意事项：

  • 会锁表（只读状态）一段时间；

  • 建议在低峰期执行；

  • 对于分区表，可逐个优化每个分区。

参考《MySQL 表膨胀与碎片监控规范》中关于 OPTIMIZE TABLE 的使用建议。

方法二：手动导出导入（适合无法在线优化的情况）

OPTIMIZE TABLE your_table;

方法三：启用 innodb_file_per_table 并重建表（仅限独立表空间）

确保你的配置中启用了：

# 导出
mysqldump -u root -p --single-transaction your_database your_table > your_table.sql

# 删除原表
DROP TABLE your_table;

# 重新导入
mysql -u root -p your_database < your_table.sql

然后通过以下方式重建表：

ALTER TABLE your_table ENGINE=InnoDB;

• 效果：相当于重建表并释放未使用的空间；

• 优势：不依赖 mysqldump，操作简单；

• 注意：同样会锁表，需谨慎执行。

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

一、整体分析流程

步骤 1：确认当前负载状态

• 使用 SHOW STATUS 查看 MySQL 当前的运行状态：

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Qcache%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

步骤 2：查看慢查询日志（参考《数据库慢查询日志与优化规范》）

确保已开启慢查询日志并设置合理阈值（默认 1 秒）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

然后使用 mysqldumpslow 或 pt-query-digest 分析日志文件：

mysqldumpslow /var/log/mysql/slow.log

二、瓶颈分类分析

1. IO 瓶颈分析（磁盘 I/O）

检查指标：

• SHOW ENGINE INNODB STATUS\G 中的 BUFFER POOL AND MEMORY 和 LOG 部分；

• iostat -xmt 1 查看磁盘读写情况；

• SHOW STATUS LIKE 'Innodb_data_read'; 查看 InnoDB 数据读取量；

常见问题：

优化建议：

• 增大 innodb_buffer_pool_size，提升缓存命中率；

• 启用 SSD 存储 提升磁盘吞吐；

• 避免全表扫描，建立合适索引；

• 冷热数据分离，减少无效 I/O（参考《冷热数据分离策略》）；

2. CPU 瓶颈分析

检查指标：

• 使用 top 或 htop 查看 CPU 使用率；

• SHOW PROCESSLIST; 查看当前执行线程；

• SHOW STATUS LIKE 'Sort_merge_passes'; 查看排序操作次数；

常见问题：

优化建议：

• 优化 SQL 查询，避免文件排序和临时表；

• 使用覆盖索引 减少回表查询；

• 拆分复杂查询，避免单个 SQL 处理大量数据；

• 升级硬件配置，增加 CPU 核心或采用更高性能服务器；

3. 连接数瓶颈分析

检查指标：

• SHOW STATUS LIKE 'Threads_connected'; 查看当前连接数；

• SHOW STATUS LIKE 'Max_used_connections'; 查看历史最大连接数；

• SHOW VARIABLES LIKE 'max_connections'; 查看最大连接限制；

常见问题：

优化建议：

• 调整 max_connections 参数；

• 使用连接池（如 HikariCP、Druid） 减少连接开销；

• 定期清理空闲连接，释放资源；

• 避免长事务占用连接，影响并发处理能力；

4. 索引使用瓶颈分析

检查方法：

• 使用 EXPLAIN 分析 SQL 执行计划；

• 查看是否有 Using filesort、Using temporary；

• 检查是否命中索引（key 字段不为 NULL）；

常见问题：

优化建议：

• 建立复合索引，遵循最左前缀原则；

• 避免对字段使用函数 导致索引失效；

• 使用覆盖索引 避免回表查询；

• 定期更新统计信息，使用 ANALYZE TABLE；

• 监控碎片率，及时执行 OPTIMIZE TABLE（参考《MySQL 表膨胀监控与空间回收规范》）；