标签

MariaDB

MariaDB 是一个开源数据库服务器，为 MySQL 提供直接替换功能。

MariaDB

面试题12 问题6

服务端5月30日 19:50

MariaDB 和 MySQL 有什么区别？生产环境怎么选？MariaDB 和 MySQL 同源，但现在已经不能简单当成同一个数据库的两个名字。MariaDB 最初由 MySQL 原始作者创建，目标是保持开源和兼容，同时加入更多存储引擎、优化器能力和集群方案；MySQL 由 Oracle 维护，生态稳定，云厂商支持广，MySQL 8.0 在窗口函数、CTE、JSON、权限模型等方面也补齐了很多能力。 ## 追问 ### MariaDB 能直接替换 MySQL 吗？低版本和常规 SQL 场景通常迁移成本不高，客户端协议、基础语法和常用工具大多兼容。真正要小心的是高版本差异、系统表、复制、JSON 行为和认证插件。 ### 两者功能主要差在哪里？ MariaDB 提供 Aria、ColumnStore、Spider、MyRocks 等更多引擎选择，也有自己的 Galera 集群路线。MySQL 8.0 的数据字典、JSON、窗口函数、CTE 和权限体系更统一。 ### 性能上 MariaDB 一定更快吗？不一定。读写性能受版本、引擎、索引、SQL、参数和硬件影响很大，同一条 SQL 在两个优化器里的执行计划可能不同。 ### 生产环境怎么选？如果团队重度使用 MySQL 8.0、依赖云厂商托管能力，继续用 MySQL 更稳。如果看重开源路线、特定引擎或 MariaDB 生态，MariaDB 合适。 ## 写段 SQL ```sql SELECT VERSION(); SHOW VARIABLES LIKE 'version_comment'; EXPLAIN SELECT * FROM orders WHERE user_id = 1001; ```

服务端5月30日 19:50

MariaDB 存储引擎有哪些？不同场景怎么选？MariaDB 的存储引擎不是越多越好，而是要按事务、读写比例、数据规模和运维成本来选。默认优先 InnoDB，因为它支持事务、行级锁、崩溃恢复和外键，适合绝大多数 OLTP 系统。Aria 更像 MyISAM 的加强版，适合临时表、读多写少或不需要完整事务的场景；ColumnStore 面向分析查询；Spider 用来做分片和跨节点访问；MyRocks 适合写入密集、压缩率敏感的业务。 ## 追问 ### 为什么多数业务表建议用 InnoDB？ InnoDB 的优势不只是事务，还包括崩溃恢复、MVCC、行级锁和成熟生态。订单、账户、库存这类需要一致性的表，不应为了读性能换成非事务引擎。 ### Aria 和 MyISAM 有什么区别？ MyISAM 读性能不错，但表级锁明显，不支持事务，崩溃恢复弱。Aria 是 MariaDB 对这类场景的改进选择，但仍不能替代 InnoDB 做核心交易表。 ### ColumnStore 适合放业务明细表吗？不太适合。ColumnStore 优势是列式压缩和批量分析，适合报表和历史数据查询；频繁按主键更新、单行查询很多时会难受。 ### 更换存储引擎有什么坑？ ALTER TABLE 切引擎可能锁表、耗时长，还可能遇到索引长度、外键或数据类型兼容问题。大表建议在线变更或分批迁移。 ## 写段 SQL ```sql SHOW ENGINES; CREATE TABLE event_log (id BIGINT PRIMARY KEY, created_at DATETIME, payload TEXT) ENGINE=InnoDB; ```

服务端5月30日 19:50

MariaDB 主从复制如何配置？复制模式怎么选？MariaDB 主从复制的核心是：主库写 binlog，从库通过 IO 线程拉取日志，再由 SQL 线程重放。最基础的是异步复制，性能好、延迟低，但主库宕机时可能丢最后几笔事务；半同步复制会等至少一个从库确认收到日志，可靠性更好，但写入延迟会上升；GTID 复制用全局事务 ID 标记事务，主从切换和故障恢复更稳。 ## 追问 ### 最小可用的主从复制怎么配置？主库要开启 server_id、log_bin 和 ROW 格式 binlog，从库设置不同 server_id，并通过 CHANGE MASTER TO 指向主库。脚本要注意版本差异，新版本会逐步转向 replica 相关命令。 ### 异步、半同步和 GTID 有什么区别？异步最快但可能丢数据；半同步至少确认日志到一个从库，适合更重视数据安全的业务；GTID 是事务定位方式，让补复制、换主库更清楚。 ### 复制延迟一般从哪里排查？先看 Seconds_Behind_Master、Relay_Log_File、Exec_Master_Log_Pos，再结合慢 SQL、从库 IO、磁盘写入和大事务判断。 ### 主从切换最容易出什么问题？从库还没追平就提升为主库，会导致新主缺数据；应用仍写旧主，会形成双写。切换前要确认 relay log 执行完和应用连接串更新完成。 ## 写段 SQL ```sql CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; START SLAVE; SHOW SLAVE STATUS\G ```

服务端5月30日 19:50

MariaDB 备份和恢复如何设计才可靠？MariaDB 备份不要只理解成把数据导出一份，真正要设计的是恢复能力。小库、低频变更、临时迁移，用 mysqldump 足够；线上核心库、数据量较大或恢复时间敏感，优先用 Mariabackup 做物理备份，再配合 binlog 做时间点恢复。备份策略一般按 RPO 和 RTO 倒推：能接受丢 5 分钟数据，就至少保留连续 binlog；要求 30 分钟内恢复，就不能只靠几十 GB 的 SQL 文件慢慢导入。 ## 追问 ### mysqldump 和 Mariabackup 怎么选？ mysqldump 是逻辑备份，优点是可读、可跨版本、适合单库单表恢复，缺点是大库导出和导入都慢。Mariabackup 是物理备份，恢复速度更接近文件拷贝，适合生产全量备份。 ### 如何恢复到指定时间点？常见做法是每天一次全量备份，持续保留 binlog，并记录全量备份完成时的 binlog 文件和 position。恢复时先还原全量，再用 mysqlbinlog 回放到指定时间点。 ### 备份脚本最容易踩什么坑？第一是只备份不校验，事故时才发现压缩包损坏。第二是把密码写在脚本里且权限过宽，建议用专门备份账号和最小权限。 ### 恢复前后要检查什么？恢复前确认目标目录为空、磁盘空间足够、服务已停止。恢复后要做表检查、核心 SQL 抽样和业务只读验证。 ## 写段命令 ```bash mariabackup --backup --target-dir=/backup/full --user=backup --password=xxx mariabackup --prepare --target-dir=/backup/full mysqlbinlog --stop-datetime="2026-05-30 10:18:00" mysql-bin.000123 | mysql -u root -p ```

服务端5月30日 19:40

MariaDB 性能调优应该先看慢查询还是参数？MariaDB 性能调优不要一上来就改几十个参数。更可靠的顺序是：先确认慢在哪里，再看执行计划和索引，最后才调整 InnoDB、连接数、临时表、排序缓冲等配置。参数调优像放大器，SQL 和索引方向对了，它能放大性能；方向错了，它只会把问题藏得更深。 ## 追问 ### 慢查询应该怎么打开和分析？开启 `slow_query_log`，把 `long_query_time` 设置成符合业务的阈值。再按出现频率、扫描行数、总耗时排序，不要只盯单次最慢 SQL。 ### 最关键的参数是哪个？ InnoDB 场景先看 `innodb_buffer_pool_size`，因为它影响数据页和索引页缓存命中率。专用数据库常按内存 60%-80% 估算，但要留空间给系统和连接线程。 ### 索引优化有什么取舍？高频 WHERE、JOIN、ORDER BY 列适合联合索引，但字段不是越多越好。索引会提升读，也会拖慢写入、占用磁盘。 ### max_connections 越大越好吗？不是。它只允许更多连接进来，不代表数据库能同时跑更多重查询。连接过多会放大线程、内存和锁竞争。 ### sort_buffer_size 和 join_buffer_size 能随便调大吗？它们通常是会话级参数，并发一高总内存会放大。只有确认瓶颈并估算过峰值连接数后，才适合小步调整。 ## 写段配置 ```ini slow_query_log=1 long_query_time=1 innodb_buffer_pool_size=8G ``` ```sql EXPLAIN SELECT * FROM orders WHERE user_id=1001 ORDER BY created_at DESC; CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); ```

服务端5月30日 19:40

MariaDB 如何做安全配置？哪些设置最容易被忽略？MariaDB 安全配置不能只理解成“把 root 密码改复杂一点”。更稳妥的做法是从账号权限、网络入口、传输加密、文件导入导出、审计日志和备份存放几个层面一起收紧。很多事故不是数据库漏洞导致的，而是测试账号没删、远程访问开太大、备份文件裸放。 ## 追问 ### 最小权限应该怎么落地？应用不要连 root，也不要授予 `ALL PRIVILEGES`。按业务库创建专用账号，只授予需要的 SELECT、INSERT、UPDATE、DELETE 等权限。 ### 远程访问应该怎么限制？ `bind-address` 不要直接监听公网地址，账号 host 也不要写成 `%`。需要临时排障时可以开白名单，但结束后要及时移除。 ### SSL/TLS 一定要开启吗？跨主机连接建议开启 `require-secure-transport`，避免账号密码和查询内容明文暴露。上线前要确认客户端驱动和证书配置支持。 ### 哪些文件相关配置容易被忽略？ `local_infile` 建议关闭，`secure_file_priv` 应限制导入导出目录。备份文件要加密并放在受控目录。 ### 审计和日常运维要看什么？关注失败登录、权限变更、异常来源 IP、大批量导出和高危 DDL。日志要轮转，否则磁盘打满也是事故。 ## 写段配置 ```sql CREATE USER 'app_user'@'10.%' IDENTIFIED BY 'strong_password'; GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'app_user'@'10.%'; ``` ```ini bind-address=10.0.0.5 local_infile=0 require-secure-transport=ON ```

服务端5月30日 19:40

MariaDB 分区表有哪些类型？如何创建和维护？MariaDB 分区表适合处理持续增长、访问模式明确的大表，比如日志、订单、流水。它不是把业务拆成多张表，而是在一张逻辑表下面按规则拆出多个物理分区；查询仍写同一张表名，优化器在条件命中分区键时可以做分区裁剪。分区能降低历史数据维护成本，也能减少部分扫描范围，但不能替代索引。 ## 追问 ### MariaDB 分区表有哪些类型？常见类型有 RANGE、LIST、HASH 和 KEY。RANGE 适合按时间、ID 区间切分；LIST 适合地区、业务状态；HASH/KEY 主要用来均匀打散。 ### RANGE、LIST、HASH、KEY 应该怎么选？查询经常带 `created_at`、`biz_date` 时优先 RANGE；固定枚举隔离用 LIST；没有明显范围条件但要拆散数据时考虑 HASH/KEY。 ### 创建分区表最容易踩什么坑？主键和唯一键通常必须包含分区键，否则可能建表失败或唯一性语义不符合预期。分区键不出现在查询条件里，收益也会很有限。 ### 历史分区怎么维护？ RANGE 分区通常提前创建未来分区。删除历史数据时，`DROP PARTITION` 比大批量 DELETE 更快，但它是物理删除，归档前要先备份。 ### 怎么确认查询真的用到了分区？用 `EXPLAIN PARTITIONS` 看 partitions 字段，确认只访问目标分区，而不是全分区扫描。 ## 写段 SQL ```sql CREATE TABLE orders (id BIGINT, created_at DATE, PRIMARY KEY(id, created_at)) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')), PARTITION pmax VALUES LESS THAN MAXVALUE); ```

服务端5月27日 18:26

MariaDB 如何进行索引优化？有哪些索引类型和优化策略？## MariaDB 有哪些索引类型？各自的适用场景是什么？ MariaDB 支持多种索引类型，理解它们的区别是做优化决策的基础。 **B-Tree 索引**是 MariaDB 的默认索引结构，绝大多数场景下使用的都是它。B-Tree 采用平衡多路搜索树结构，叶子节点通过双向链表连接，天然支持等值查询、范围查询和排序操作。当你执行 `WHERE id BETWEEN 100 AND 200` 或 `ORDER BY created_at DESC` 时，B-Tree 可以高效地利用索引的有序性完成扫描，而不需要额外的文件排序（filesort）。 **哈希索引**仅支持等值匹配查询，不支持范围查询和排序。它的查询时间复杂度接近 O(1)，在精确查找场景下比 B-Tree 更快。MariaDB 中哈希索引主要用于 MEMORY 存储引擎的表，InnoDB 的自适应哈希索引（Adaptive Hash Index）是引擎内部自动维护的，不需要手动创建。面试中如果被问到"哈希索引为什么不能用于范围查询"，核心原因是哈希值之间没有大小关系，无法利用有序性做区间扫描。 **全文索引（FULLTEXT）**专门用于文本内容的模糊搜索，底层基于倒排索引实现。相比于 `LIKE '%关键词%'` 会导致全表扫描，全文索引可以快速定位包含目标词的记录。MariaDB 支持 `MATCH ... AGAINST` 语法，提供自然语言模式和布尔模式两种查询方式。需要注意的是，全文索引对中文分词的支持有限，通常需要借助 ngram 解析器或 Mroonga 引擎来处理中文场景。 **空间索引（SPATIAL）**用于地理空间数据类型的索引，底层基于 R-Tree 结构。适合处理点、线、多边形等 GIS 数据的空间关系查询，比如"查找某坐标 5 公里范围内的门店"。空间索引仅支持 InnoDB 和 MyISAM 引擎，且索引列必须声明为 `NOT NULL`。 **聚簇索引**不是一个独立的索引类型，而是 InnoDB 的数据组织方式。InnoDB 的主键索引就是聚簇索引——叶子节点直接存储完整的行数据，而非主键索引（二级索引）的叶子节点存储的是主键值。这意味着通过二级索引查找数据时，需要先查到主键值，再回表查询完整行数据，这个过程叫做"回表"。理解聚簇索引和回表机制，是掌握覆盖索引优化前提。 ## 创建索引时应该遵循哪些设计原则？索引不是越多越好。每多一个索引，INSERT/UPDATE/DELETE 就多一份维护成本，同时占用额外的磁盘空间。设计索引时需要把握几个关键原则。 **优先对高选择性列建索引。** 选择性指的是列中不同值的数量与总行数的比值。选择性越高，索引过滤效果越好。例如用户表的 email 列选择性接近 1，几乎每条记录值都不同，索引过滤效率极高；而性别列只有两三个值，选择性极低，索引对查询的帮助微乎其微，优化器大概率会选择全表扫描。一个经验阈值是：当某值占比超过全表的 20% 时，优化器通常放弃使用索引。 **复合索引要遵循最左前缀原则。** 对于索引 `idx_abc(a, b, c)`，查询条件用到了 a、(a,b)、(a,b,c) 都能命中索引，但只用 b 或 c 则无法使用。实际设计中，应该把等值查询的列放在前面，范围查询的列放在后面。例如 `WHERE status = 1 AND created_at > '2024-01-01'`，应建索引 `(status, created_at)` 而非 `(created_at, status)`，因为等值过滤在前可以大幅缩小范围查询的扫描区间。 **利用覆盖索引减少回表。** 如果查询需要的所有列都包含在索引中，InnoDB 直接从索引返回数据，无需回表读取行记录。例如 `SELECT id, name FROM users WHERE name = 'John'`，如果 name 列上有索引且索引包含了 id（InnoDB 二级索引自动包含主键），这就是一次覆盖索引扫描。在 EXPLAIN 输出中，覆盖索引的 Extra 列会显示 `Using index`。 **避免在索引列上使用函数或表达式。** `WHERE YEAR(created_at) = 2024` 会导致索引失效，因为 MariaDB 需要对每一行计算函数值后才能比较。正确的写法是 `WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'`，这样优化器可以利用 B-Tree 的有序性做范围扫描。 ## 如何使用 EXPLAIN 分析查询的索引使用情况？ EXPLAIN 是索引优化的核心工具，它展示优化器为查询选择的执行计划。 ```sql EXPLAIN SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1 AND o.created_at > '2024-01-01'; ``` 重点关注以下几个字段： - **type**：访问类型，从好到差依次为 `system > const > eq_ref > ref > range > index > ALL`。出现 `ALL` 意味着全表扫描，需要重点优化。`ref` 表示使用索引等值匹配，`range` 表示索引范围扫描。 - **key**：实际使用的索引名称。如果显示 NULL，说明没有可用索引。 - **rows**：预估扫描行数。这个值越小越好，但它是基于统计信息的估算值，不一定精确。 - **Extra**：额外信息。`Using index` 表示覆盖索引，`Using filesort` 表示需要额外排序，`Using temporary` 表示使用了临时表，后两者通常意味着性能瓶颈。一个实用的工作流是：先跑 EXPLAIN 看执行计划，发现 type 为 ALL 或 rows 过大时，针对性地添加或调整索引，再反复验证。 ## 什么是索引失效？哪些常见写法会导致索引失效？索引失效指的是查询条件虽然涉及了索引列，但优化器最终选择不使用索引而做全表扫描。以下几种写法是常见的索引失效陷阱。 **对索引列使用函数或运算：** `WHERE name LIKE '%John'`（左模糊）、`WHERE YEAR(date_col) = 2024`、`WHERE id + 1 = 100`，这些写法破坏了 B-Tree 的有序性，优化器无法利用索引定位。 **隐式类型转换：** 当列是 VARCHAR 类型，查询条件写成 `WHERE phone = 13800001111`（数字类型），MariaDB 会将列值转为数字再做比较，这相当于对列施加了隐式函数，导致索引失效。正确写法是 `WHERE phone = '13800001111'`。 **OR 条件连接不同索引列：** `WHERE name = 'John' OR age = 25`，如果 name 和 age 各有独立索引，MariaDB 在某些情况下可以使用 Index Merge 优化，但效果往往不如预期，不如改写为 UNION ALL 两个子查询。 **NOT IN、NOT EXISTS、!=、<>：** 这些否定条件可能导致索引失效，尤其是结果集占比较大时。但并非绝对——如果否定条件过滤性很强（排除的值很少），优化器仍可能选择索引。 **索引列 IS NULL：** 在 MariaDB 中，B-Tree 索引是包含 NULL 值的，`WHERE col IS NULL` 可以使用索引。这一点与 Oracle 等数据库不同，面试中注意区分。 ## 如何通过覆盖索引和索引下推提升查询性能？ **覆盖索引**在前文已经提到，核心思路是让查询所需的所有列都在索引中，从而避免回表。实际应用中，可以通过 SELECT 指定列或将常用查询列加入复合索引来实现。 ```sql -- 订单列表查询，只需要 id、status、created_at SELECT id, status, created_at FROM orders WHERE user_id = 100; -- 建立覆盖索引 (user_id, status, created_at) -- InnoDB 二级索引自动包含主键 id，因此这三列 + id 都在索引中 ALTER TABLE orders ADD INDEX idx_user_status_created(user_id, status, created_at); ``` **索引下推（Index Condition Pushdown，ICP）** 是 MariaDB 5.6+ 引入的优化。传统流程中，二级索引查到主键后必须回表才能判断 WHERE 中的其他条件；启用 ICP 后，存储引擎在索引扫描阶段就根据 WHERE 条件过滤，减少回表次数。 ```sql -- 假设有索引 (last_name, first_name) SELECT * FROM users WHERE last_name = 'Smith' AND first_name LIKE '%ohn'; -- 没有 ICP：先通过 last_name 索引查到所有 Smith 的主键，逐个回表再过滤 first_name -- 有 ICP：在索引扫描时直接对 first_name 做 LIKE 判断，不满足的跳过，减少回表 ``` 在 EXPLAIN 的 Extra 列中，ICP 会显示 `Using index condition`。ICP 的适用条件是：查询使用了复合索引，且 WHERE 中有索引前列的等值条件加上后续列的条件过滤。 ## 如何监控和维护索引的健康状态？索引不是建完就一劳永逸的，随着数据增删改，索引可能出现碎片化、统计信息过期等问题，需要定期维护。 ```sql -- 查看表的索引信息 SHOW INDEX FROM users; -- 查看索引统计信息 SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'users'; -- 更新表的统计信息（不锁表） ANALYZE TABLE users; -- 重建表以消除碎片（会锁表） OPTIMIZE TABLE users; ``` **识别无用索引：** 可以通过 `sys.schema_unused_indexes` 视图（MariaDB 10.6+）或开启 `userstat` 插件来追踪索引使用情况。长期未使用的索引应该清理，减少写入开销。 **监控索引碎片：** 频繁的增删改会导致索引页出现空洞，降低索引扫描效率。`OPTIMIZE TABLE` 会重建表和索引，消除碎片，但操作期间会锁表，建议在低峰期执行。对于大表，可以考虑使用 `ALTER TABLE ... ENGINE=InnoDB` 的方式在线重建。 **统计信息维护：** 优化器依赖统计信息（cardinality、rows 等）来选择执行计划。如果统计信息严重失真，可能导致优化器选错索引。定期执行 `ANALYZE TABLE` 可以刷新统计信息，且在 MariaDB 10.4+ 中该操作是在线进行的，不会阻塞读写。 ## MariaDB 索引优化有哪些常见的实战案例？ **案例一：分页查询优化** 深分页是典型的性能杀手。`SELECT * FROM orders ORDER BY id LIMIT 100000, 10` 需要先扫描 100010 行再丢弃前 100000 行。 ```sql -- 方案一：游标分页（推荐） -- 前端记录上一页最后一条的 id，下一页查询时带上 SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10; -- 方案二：延迟关联 -- 先通过子查询在索引上定位 id，再回表取数据 SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t ON o.id = t.id; ``` **案例二：多条件组合查询** `SELECT * FROM products WHERE category_id = 5 AND status = 1 AND price BETWEEN 100 AND 500 ORDER BY sales_count DESC LIMIT 20;` 索引设计：`(category_id, status, sales_count)`。前两列做等值过滤缩小范围，第三列利用索引有序性避免 filesort。price 列的范围查询放在最后处理。 **案例三：大表 JOIN 优化** ```sql SELECT o.id, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 2 AND u.region = 'CN'; ``` 确保 JOIN 条件列（user_id、id）有索引，同时 orders 表在 status 上建索引、users 表在 region 上建索引，使驱动表的过滤结果尽可能小，减少循环 JOIN 的次数。掌握以上索引类型、设计原则、分析工具和实战技巧，基本能应对 MariaDB 索引优化的大部分面试问题和线上场景。核心思路始终是：用 EXPLAIN 验证，让索引覆盖查询，减少回表和全表扫描。

服务端5月27日 18:24

MariaDB 的 JSON 函数怎么用？有哪些常见坑？MariaDB 从 10.2 开始提供了一套 JSON 函数，能直接在 SQL 里创建、查询、修改和校验 JSON 数据。JSON 列本质是 LONGTEXT 加 CHECK 约束，不是 MySQL 那种二进制格式，这一点在迁移时容易踩坑。 ## 追问 ### JSON_EXTRACT、JSON_VALUE、JSON_QUERY 有什么区别？三个都是取值，但返回类型不同： | 函数 | 返回值 | 示例 | |------|--------|------| | JSON_EXTRACT | 原始 JSON 片段（带引号） | `"John"` | | JSON_VALUE | 标量值（去引号） | `John` | | JSON_QUERY | 对象或数组 | `{"city":"NY"}` | 日常取字符串值用 `->` 操作符（JSON_EXTRACT 的语法糖），取标量用 JSON_VALUE，取嵌套对象用 JSON_QUERY。 ### JSON_SET、JSON_INSERT、JSON_REPLACE 有什么区别？ - **JSON_SET**：存在则更新，不存在则插入——万能选手 - **JSON_INSERT**：只在路径不存在时插入，已有值不动 - **JSON_REPLACE**：只在路径已存在时替换，没找到就跳过记住一句话：不确定用 SET，只想加新字段用 INSERT，只想改旧字段用 REPLACE。 ### JSON 列怎么加索引？ JSON 列不能直接建普通索引。两种方式： 1. **生成列 + 索引**（推荐）： ```sql ALTER TABLE products ADD COLUMN brand VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.brand'))) STORED, ADD INDEX idx_brand(brand); ``` 2. **函数索引**（MariaDB 10.3+）： ```sql CREATE INDEX idx_brand ON products((CAST(attributes->'$.brand' AS CHAR(50)))); ``` ### MariaDB 的 JSON 和 MySQL 的 JSON 有什么区别？这是迁移时最容易翻车的地方： | 对比项 | MariaDB | MySQL | |--------|---------|-------| | 存储格式 | 原文 LONGTEXT | 二进制 JSON | | JSON 类型 | LONGTEXT 的别名 | 独立数据类型 | | 自动校验 | 需要 CHECK 约束 | 内置校验 | | 部分更新 | 不支持 | 支持二进制增量更新 | MariaDB 存原文的好处是可以直接用文本函数处理，坏处是每次修改整个字段重写，大 JSON 字段更新性能差。 ### 用 JSON 列存数据有什么坑？ 1. **没有 schema 约束**：同列不同行结构可以完全不同，查出来才知道长什么样，排查问题靠蒙 2. **查询性能**：每次取值都要解析 JSON，高频查询字段务必抽成普通列加索引 3. **更新代价**：改一个字段整个 JSON 重写，大文档更新慢 4. **CHECK 约束别忘了加**： ```sql CREATE TABLE products ( id INT PRIMARY KEY, attrs JSON, CONSTRAINT chk_json CHECK (JSON_VALID(attrs)) ); ``` ## 写段代码 ```sql -- 建表 + 插入 + 查询一条龙 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), attrs JSON CHECK (JSON_VALID(attrs)) ); INSERT INTO products (name, attrs) VALUES ('Laptop', '{"brand":"Dell","ram":"16GB"}'); -- 查品牌、改内存 SELECT name, attrs->'$.brand' AS brand FROM products; UPDATE products SET attrs = JSON_SET(attrs, '$.ram', '32GB') WHERE id = 1; ```

服务端5月27日 17:38

如何优化MariaDB查询性能？查询性能是数据库系统的生命线。一条低效的SQL可能拖垮整个应用，而一次精准的优化能让响应时间从秒级降到毫秒级。这篇文章从诊断、索引、写法、配置四个层面，给出经过生产验证的优化方法。 ## 用 EXPLAIN 定位性能瓶颈优化之前，先要找到问题。EXPLAIN 是最直接的诊断工具： ```sql EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid'; ``` 输出中有四个字段值得重点关注： - **type** — 访问类型，从差到优依次为 ALL → index → range → ref → eq_ref → const。出现 ALL 意味着全表扫描，必须优化 - **key** — 实际使用的索引。如果为 NULL，说明索引未被命中 - **rows** — 预估扫描行数。数字越大，查询越慢 - **Extra** — Using filesort 表示额外排序，Using temporary 表示使用了临时表，两者都应尽量避免一个简单的判断标准：type 不是 ALL 且 Extra 没有 Using filesort/Using temporary，查询基本合格。 ## 索引：最有效的加速手段 ### 建立合适的复合索引单列索引在多条件查询时往往不够用。复合索引遵循最左前缀原则，把区分度高的列放前面： ```sql -- 假设查询条件为 WHERE user_id = ? AND status = ? -- user_id 区分度远高于 status，放前面 CREATE INDEX idx_user_status ON orders(user_id, status); ``` ### 用覆盖索引避免回表当查询的列全部包含在索引中时，引擎无需回表读取数据行，性能提升显著： ```sql -- 索引 idx_user_status(user_id, status) 无法覆盖此查询（需要 amount 列） SELECT user_id, status, amount FROM orders WHERE user_id = 100; -- 建立覆盖索引后，直接从索引读取所有数据 CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount); ``` ### 避免索引失效的常见写法以下写法会导致索引无法命中： - 对索引列使用函数：`WHERE YEAR(created_at) = 2025` 改为 `WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'` - 隐式类型转换：`WHERE varchar_col = 123` 改为 `WHERE varchar_col = '123'` - 前缀模糊查询：`WHERE name LIKE '%John'` 改为 `WHERE name LIKE 'John%'` - 使用 OR 连接不同索引列：改用 UNION ALL 拆分 ## 查询写法的优化技巧 ### 只查需要的列 SELECT * 是性能杀手。它强制读取所有列的数据，增加 I/O 和内存开销，还可能破坏覆盖索引： ```sql -- 不推荐 SELECT * FROM users WHERE id = 1; -- 推荐：只查业务需要的列 SELECT id, name, email FROM users WHERE id = 1; ``` ### 用 JOIN 替代子查询 MariaDB 优化器对子查询的处理不如 JOIN 高效，特别是 IN 子查询： ```sql -- 不推荐 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000); -- 推荐 SELECT u.id, u.name, u.email FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000; ``` ### UNION ALL 替代 UNION UNION 会对结果去重，需要额外的排序操作。如果确定结果集无重复，用 UNION ALL 省掉去重开销： ```sql -- 不需要去重时 SELECT name FROM customers WHERE region = 'east' UNION ALL SELECT name FROM suppliers WHERE region = 'east'; ``` ### 深分页的两种优化方案 OFFSET 值很大时，数据库需要扫描并跳过前面的所有行： ```sql -- 传统写法：跳过 10 万行，极其缓慢 SELECT * FROM orders ORDER BY id LIMIT 100000, 10; -- 方案一：游标分页（要求排序字段连续且有索引） SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10; -- 方案二：延迟关联（先查主键再回表，减少扫描列数） SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp ON o.id = tmp.id; ``` ### JOIN 优化 - 被驱动表的连接列必须有索引 - 小结果集驱动大表，减少循环次数 - 当优化器选错连接顺序时，用 STRAIGHT_JOIN 强制指定： ```sql SELECT * FROM small_table s STRAIGHT_JOIN large_table l ON s.id = l.small_id; ``` ## 配置层面的调优 ### InnoDB 缓冲池这是影响 InnoDB 性能最重要的参数，建议设为物理内存的 50%-70%： ```ini innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4 ``` ### 排序和连接缓冲 ```ini sort_buffer_size = 4M -- 每个连接的排序缓冲 join_buffer_size = 4M -- 每个无索引连接的缓冲 read_rnd_buffer_size = 4M -- MRR 读取缓冲 ``` ### 临时表大小 ```ini tmp_table_size = 256M max_heap_table_size = 256M ``` 超过此大小的临时表会写到磁盘，导致性能骤降。 ### 关于查询缓存注意：MariaDB 10.6 起默认禁用查询缓存，后续版本已移除该功能。如果使用 10.6+，不要配置 query_cache_size，而是关注应用层缓存（如 Redis）。 ## 监控慢查询开启慢查询日志，定期分析并优化： ```sql SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 超过 1 秒记录 SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询 ``` 结合 pt-query-digest 工具分析慢查询日志，找出最需要优化的 SQL： ```bash pt-query-digest /var/lib/mysql/slow.log ``` ## 优化决策路径面对一个慢查询，按以下顺序排查： 1. 先用 EXPLAIN 查看执行计划，确认是否走了索引 2. 如果走了索引仍然慢，考虑建立覆盖索引或调整索引列顺序 3. 如果索引没有问题，检查查询写法是否有优化空间（避免 SELECT *、子查询改 JOIN、深分页优化） 4. 如果单条 SQL 已最优，考虑配置调优（缓冲池、排序缓冲、临时表大小） 5. 配置也调不动了，考虑架构层面优化（读写分离、分库分表、引入缓存）每个阶段都有明确的检查点和动作，避免盲目调参。

服务端5月27日 15:06

MariaDB 的事务隔离级别如何工作？怎样根据业务场景选择合适的隔离级别？## 事务隔离级别要解决什么问题多个事务并发执行时，如果不加任何隔离措施，会出现三类数据不一致的问题： - **脏读（Dirty Read）**：事务 A 读到了事务 B 尚未提交的数据。如果事务 B 回滚，事务 A 拿到的就是根本不存在的"脏数据"。 - **不可重复读（Non-Repeatable Read）**：事务 A 两次读取同一行数据，中间事务 B 修改并提交了这行，导致两次读到的值不同。 - **幻读（Phantom Read）**：事务 A 两次执行相同的范围查询，中间事务 B 插入了新行并提交，导致第二次查询多出了"幻影行"。这三类问题逐层递进：脏读是读到了未提交的修改，不可重复读是已提交的修改导致同一行前后不一致，幻读是已提交的新增导致行数变化。SQL 标准据此定义了四种隔离级别，每种级别禁止一部分问题。 ## 四种隔离级别 ### READ UNCOMMITTED（读未提交）最低隔离级别，允许事务读取其他事务未提交的修改。在这个级别下，脏读、不可重复读、幻读都可能发生。实际业务中几乎不会使用——读到未提交的数据意味着可能基于错误数据做出决策，风险极高。 ### READ COMMITTED（读已提交）只允许读取已经提交的数据，杜绝了脏读。但同一事务内两次读取同一行，可能因为其他事务的提交而得到不同结果，所以不可重复读和幻读仍然存在。 Oracle 和 PostgreSQL 默认使用这个级别。如果你的业务对同一事务内数据一致性要求不高（比如报表查询、大多数 Web 应用的读操作），READ COMMITTED 是一个性能和正确性的折中选择。 ### REPEATABLE READ（可重复读）保证同一事务内多次读取同一行的结果一致，杜绝了脏读和不可重复读。按照 SQL 标准，幻读在这个级别仍然可能发生。但 MariaDB/MySQL 的 InnoDB 引擎通过 MVCC 和 Gap Lock 机制，在 REPEATABLE READ 下也避免了幻读——这比 SQL 标准更严格。 MariaDB 和 MySQL 的默认隔离级别就是 REPEATABLE READ。大多数 OLTP 场景不需要改动它。 ### SERIALIZABLE（串行化）最高隔离级别，所有事务按顺序串行执行，完全杜绝脏读、不可重复读和幻读。实现方式是对所有读取的行加共享锁，其他事务无法修改这些行直到锁释放。性能代价极大——并发度几乎归零。只在对数据一致性有极端要求的场景下使用，比如金融对账、审计等。 ## 隔离级别与并发问题的对应关系 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | |---|---|---|---| | READ UNCOMMITTED | 可能 | 可能 | 可能 | | READ COMMITTED | 不会 | 可能 | 可能 | | REPEATABLE READ | 不会 | 不会 | 可能（SQL 标准）/ 不会（MariaDB InnoDB） | | SERIALIZABLE | 不会 | 不会 | 不会 | ## MVCC 是怎么工作的 MVCC（Multi-Version Concurrency Control，多版本并发控制）是 InnoDB 实现 REPEATABLE READ 和 READ COMMITTED 的核心机制。它的基本思路是：每行数据保留多个版本，读操作访问的是某个历史快照，写操作创建新版本，读写互不阻塞。 InnoDB 在每行记录后添加两个隐藏列： - **DB_TRX_ID**：最后修改该行的事务 ID。 - **DB_ROLL_PTR**：回滚指针，指向 undo log 中该行的前一个版本。每个事务开始时会获得一个递增的事务 ID。读取数据时，InnoDB 根据当前事务 ID 和 undo log 链构建一个一致性视图（Read View），只返回对当前事务可见的版本。 MVCC 在两个隔离级别下的行为差异： - **REPEATABLE READ**：事务第一次读取时创建 Read View，整个事务期间复用同一个 View，所以同一行数据多次读取结果一致。 - **READ COMMITTED**：每次 SELECT 都创建新的 Read View，所以能看到其他事务已提交的最新数据。这就是为什么 READ COMMITTED 下会出现不可重复读，而 REPEATABLE READ 不会——Read View 的创建时机不同。 ## Gap Lock 与 Next-Key Lock MVCC 解决了快照读（普通 SELECT）的幻读问题，但当前读（SELECT ... FOR UPDATE、UPDATE、DELETE 等加锁读）怎么办？InnoDB 的答案是 Gap Lock 和 Next-Key Lock。 - **Record Lock**：锁定索引上的单条记录。 - **Gap Lock**：锁定两条记录之间的间隙，阻止其他事务在该间隙中插入新行。 - **Next-Key Lock**：Record Lock + Gap Lock 的组合，锁定一条记录及其前面的间隙。这是 InnoDB 在 REPEATABLE READ 下的默认加锁方式。举个例子：表中有 id = 1、5、10 三条记录。对 id = 5 加 Next-Key Lock 时，实际锁住的范围是 (1, 5]，即 id 大于 1 且小于等于 5 的区间。其他事务无法在这个范围内插入新行（比如 id = 3），从而防止了幻读。在 READ COMMITTED 下，Gap Lock 被禁用（外键约束检查和唯一键冲突检查除外），只使用 Record Lock。这意味着其他事务可以在已锁定记录的间隙中自由插入，并发度更高，但可能出现幻读。 ## InnoDB 与 MyISAM 的关键区别讨论事务隔离级别的前提是存储引擎支持事务。MariaDB 同时支持 InnoDB 和 MyISAM，但两者在事务能力上有本质区别： - **InnoDB**：支持完整的 ACID 事务、行级锁、MVCC、外键约束和崩溃恢复。事务隔离级别的所有讨论都基于 InnoDB。 - **MyISAM**：不支持事务、不支持行级锁（只有表级锁）、没有 MVCC、没有崩溃恢复。在 MyISAM 表上执行 `START TRANSACTION` 不会有任何效果，`ROLLBACK` 也不会回滚任何修改。如果你的表使用 MyISAM 引擎，事务隔离级别的设置毫无意义。检查方法： ```sql SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table'; ``` 如果是 MyISAM，需要先转为 InnoDB： ```sql ALTER TABLE your_table ENGINE = InnoDB; ``` MariaDB 5.5 起默认存储引擎已经是 InnoDB，新建的表无需额外指定。 ## 如何设置隔离级别 ### 查看当前隔离级别 ```sql -- 查看全局默认隔离级别 SELECT @@GLOBAL.transaction_isolation; -- 查看当前会话隔离级别 SELECT @@SESSION.transaction_isolation; -- 兼容写法（MariaDB 中仍可用） SELECT @@tx_isolation; ``` ### 设置隔离级别 ```sql -- 仅影响下一个事务 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 影响当前会话的所有后续事务 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 影响所有新会话的默认隔离级别（需要 SUPER 权限） SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` 注意：事务已经开始后不能修改隔离级别，否则会报错 `ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress`。 ### 在配置文件中设置在 `my.cnf` 中设置全局默认： ```ini [mysqld] transaction-isolation = READ-COMMITTED ``` 重启后生效。 ## MariaDB 与 MySQL 的差异 MariaDB 是 MySQL 的分支，事务隔离机制基本一致，但有几个值得注意的差异： - **tx_isolation vs transaction_isolation**：MySQL 8.0.3 移除了 `tx_isolation` 别名，只使用 `transaction_isolation`；MariaDB 两者都支持。 - **WITH CONSISTENT SNAPSHOT**：MariaDB 的 `START TRANSACTION WITH CONSISTENT SNAPSHOT` 兼容所有隔离级别，MySQL 8.0 前只支持 REPEATABLE READ。 - **Gap Lock 行为**：两者在 REPEATABLE READ 下的 Gap Lock 策略相同，但具体死锁场景可能因版本不同而有差异。 - **默认二进制日志**：MySQL 8.0 默认开启 binlog，MariaDB 默认关闭。binlog 的开启与否会影响事务的提交流程和性能。 - **Aria 引擎**：MariaDB 用 Aria 替代 MyISAM 作为非事务型引擎的选择，Aria 支持崩溃安全特性。 ## 怎么选择隔离级别选择隔离级别本质上是正确性和并发性能之间的权衡： - **大多数 Web 应用**：保持默认的 REPEATABLE READ 即可。InnoDB 的 MVCC 让读操作不加锁，性能开销可控。 - **高并发短事务场景**（如秒杀、库存扣减）：考虑降级到 READ COMMITTED。Gap Lock 在高并发下容易导致死锁，去掉 Gap Lock 可以减少锁冲突。代价是需要业务层处理不可重复读。 - **报表和数据分析**：READ COMMITTED 通常够用。报表查询对同一事务内的一致性要求不高，但需要看到最新提交的数据。 - **金融对账和审计**：SERIALIZABLE 或者在应用层加分布式锁。数据一致性优先，性能可以妥协。 - **READ UNCOMMITTED**：几乎没有任何合理的使用场景。即使你不在乎一致性，它也不会比 READ COMMITTED 快多少——InnoDB 在 RC 级别下读操作同样不加锁。一个常见的调优方向：把 REPEATABLE READ 降为 READ COMMITTED，减少 Gap Lock 带来的死锁问题。Drupal 官方就推荐使用 READ COMMITTED 替代默认的 REPEATABLE READ 来避免死锁。如果你的业务逻辑中大量使用范围查询和插入操作混合的场景，值得做这个调整。从性能角度看，隔离级别从低到高，锁持有时间递增、锁范围递增、并发度递减。REPEATABLE READ 的 Read View 在事务期间一直持有，长事务会占用大量 undo log 空间；READ COMMITTED 每次 SELECT 创建新 Read View，undo log 压力更小。所以控制事务长度比选择隔离级别本身更重要——无论用哪个级别，都应该让事务尽可能短。

服务端5月27日 10:48

MariaDB 窗口函数怎么用？排名、累计和同比计算详解窗口函数是 SQL 里做数据分析最好用的工具——不用窗口函数，计算排名、累计、同比这些需求得写各种子查询和自连接，代码又长又慢。MariaDB 从 10.2 开始支持窗口函数，基本覆盖了 SQL 标准的核心功能。 ## 语法结构每个窗口函数都遵循同一套语法： ```sql 函数名(表达式) OVER ( PARTITION BY 分组字段 ORDER BY 排序字段 ROWS/RANGE 窗口范围 ) ``` OVER 子句定义了"窗口"——函数在这个范围内计算。PARTITION BY 把数据分组，每组独立计算；ORDER BY 决定组内排序；ROWS/RANGE 进一步约束参与计算的行范围。 ## 排名函数：ROW_NUMBER、RANK、DENSE_RANK 三个排名函数的区别在处理并列值时的行为： - **ROW_NUMBER**：严格递增，1-2-3-4，不管值是否相同 - **RANK**：并列同名，跳号，1-1-3-4 - **DENSE_RANK**：并列同名，不跳号，1-1-2-3 典型场景——每个部门薪资前三名： ```sql SELECT * FROM ( SELECT name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk <= 3; ``` 用 DENSE_RANK 而不是 RANK，因为如果前三名有并列，RANK 会跳号，导致实际返回的记录少于 3 条。 ## 聚合函数做累计和移动平均窗口函数让 SUM/AVG/COUNT 不再只是"一组一个数"，而是逐行累计： ```sql -- 累计销售额 SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders; -- 7 天移动平均 SELECT order_date, amount, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM orders; ``` ROWS BETWEEN ... AND ... 定义了参与计算的行范围。6 PRECEDING AND CURRENT ROW 表示当前行和前 6 行，总共 7 行做平均。 ## LAG 和 LEAD：访问前后行 LAG 取前 N 行的值，LEAD 取后 N 行的值。算环比增长率靠它们： ```sql SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month, ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month)) / LAG(revenue, 1) OVER (ORDER BY month) * 100, 2) AS growth_pct FROM monthly_sales; ``` LAG 的第二个参数是偏移量，第三个参数是默认值（缺省返回 NULL）。算同比就改成 `LAG(revenue, 12)`，往前取 12 个月。 ## FIRST_VALUE 和 LAST_VALUE 的坑 FIRST_VALUE 取分组内第一个值，LAST_VALUE 取最后一个值。但 LAST_VALUE 有个常见陷阱——默认窗口范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，不是整个分区。所以如果你想取部门最低薪资，必须显式指定窗口范围： ```sql -- 正确写法：指定完整窗口范围 SELECT name, department, salary, LAST_VALUE(salary) OVER ( PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS lowest_in_dept FROM employees; ``` 不加 ROWS BETWEEN ... UNBOUNDED FOLLOWING，LAST_VALUE 每行返回的值都不一样——因为窗口只到当前行为止。 ## ROWS 和 RANGE 的区别 - **ROWS**：按物理行号计算，窗口大小固定 - **RANGE**：按逻辑值范围计算，同一排序值的行作为一个整体 ```sql -- ROWS：固定 3 行窗口 SUM(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) -- RANGE：同一天的行一起算 SUM(amount) OVER (ORDER BY date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW) ``` RANGE 适合按时间窗口聚合，同一时间点的所有行会被包含在同一个窗口内。ROWS 更精确，适合固定行数的滑动窗口。