MariaDB
MariaDB 是一个开源数据库服务器，为 MySQL 提供直接替换功能。
查看更多相关内容
MariaDB 的 JSON 函数有哪些？如何使用 JSON 数据类型？MariaDB 的 JSON 函数提供了强大的 JSON 数据处理能力，从 10.2 版本开始引入，并在后续版本中不断增强。以下是主要的 JSON 函数和使用方法：
## 1. 创建 JSON 数据
```sql
-- 创建 JSON 对象
SELECT JSON_OBJECT('name', 'John', 'age', 30, 'city', 'New York');
-- 创建 JSON 数组
SELECT JSON_ARRAY('apple', 'banana', 'orange');
-- 合并 JSON
SELECT JSON_MERGE(
JSON_OBJECT('name', 'John'),
JSON_OBJECT('age', 30, 'city', 'New York')
);
```
## 2. 查询 JSON 数据
```sql
-- JSON_EXTRACT：提取 JSON 值
SELECT JSON_EXTRACT(
'{"name": "John", "age": 30, "address": {"city": "New York"}}',
'$.name'
);
-- 使用 -> 操作符（简写）
SELECT data->'$.name' AS name FROM users;
-- JSON_VALUE：提取标量值
SELECT JSON_VALUE(
'{"name": "John", "age": 30}',
'$.name'
);
-- JSON_QUERY：提取 JSON 对象或数组
SELECT JSON_QUERY(
'{"name": "John", "address": {"city": "New York"}}',
'$.address'
);
-- JSON_KEYS：获取所有键
SELECT JSON_KEYS('{"name": "John", "age": 30, "city": "New York"}');
```
## 3. 修改 JSON 数据
```sql
-- JSON_SET：设置值（如果存在则更新，不存在则插入）
SELECT JSON_SET(
'{"name": "John", "age": 30}',
'$.age', 31,
'$.city', 'Boston'
);
-- JSON_INSERT：插入值（仅当不存在时）
SELECT JSON_INSERT(
'{"name": "John", "age": 30}',
'$.city', 'Boston'
);
-- JSON_REPLACE：替换值（仅当存在时）
SELECT JSON_REPLACE(
'{"name": "John", "age": 30}',
'$.age', 31
);
-- JSON_REMOVE：删除值
SELECT JSON_REMOVE(
'{"name": "John", "age": 30, "city": "New York"}',
'$.city'
);
-- JSON_ARRAY_APPEND：追加到数组
SELECT JSON_ARRAY_APPEND(
'{"fruits": ["apple", "banana"]}',
'$.fruits', 'orange'
);
-- JSON_ARRAY_INSERT：插入到数组
SELECT JSON_ARRAY_INSERT(
'{"fruits": ["apple", "orange"]}',
'$.fruits[1]', 'banana'
);
```
## 4. JSON 搜索和过滤
```sql
-- JSON_CONTAINS：检查是否包含值
SELECT JSON_CONTAINS(
'{"fruits": ["apple", "banana", "orange"]}',
'"banana"',
'$.fruits'
);
-- JSON_CONTAINS_PATH：检查路径是否存在
SELECT JSON_CONTAINS_PATH(
'{"name": "John", "address": {"city": "New York"}}',
'one',
'$.name', '$.address.city'
);
-- JSON_SEARCH：搜索值
SELECT JSON_SEARCH(
'{"items": [{"name": "apple"}, {"name": "banana"}]}',
'one',
'banana'
);
```
## 5. JSON 实用函数
```sql
-- JSON_LENGTH：获取长度
SELECT JSON_LENGTH('{"name": "John", "age": 30}');
SELECT JSON_LENGTH('[1, 2, 3, 4, 5]');
-- JSON_DEPTH：获取深度
SELECT JSON_DEPTH('{"name": "John", "address": {"city": "New York"}}');
-- JSON_VALID：验证 JSON
SELECT JSON_VALID('{"name": "John"}');
SELECT JSON_VALID('invalid json');
-- JSON_PRETTY：格式化 JSON
SELECT JSON_PRETTY('{"name":"John","age":30}');
-- JSON_COMPACT：压缩 JSON
SELECT JSON_COMPACT('{"name": "John", "age": 30}');
```
## 6. 在表中使用 JSON
```sql
-- 创建包含 JSON 列的表
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
attributes JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入 JSON 数据
INSERT INTO products (name, attributes) VALUES (
'Laptop',
JSON_OBJECT('brand', 'Dell', 'specs', JSON_OBJECT('ram', '16GB', 'storage', '512GB SSD'))
);
-- 查询 JSON 数据
SELECT
name,
attributes->'$.brand' AS brand,
attributes->'$.specs.ram' AS ram
FROM products;
-- 更新 JSON 数据
UPDATE products
SET attributes = JSON_SET(attributes, '$.specs.ram', '32GB')
WHERE id = 1;
-- 在 JSON 列上创建索引（MariaDB 10.3+）
CREATE INDEX idx_brand ON products((attributes->'$.brand'));
-- 使用 JSON 索引查询
SELECT * FROM products WHERE attributes->'$.brand' = 'Dell';
```
## 7. 实际应用场景
### 存储灵活的产品属性
```sql
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attributes JSON
);
INSERT INTO products VALUES
(1, 'Laptop', JSON_OBJECT('brand', 'Dell', 'ram', '16GB', 'storage', '512GB')),
(2, 'Phone', JSON_OBJECT('brand', 'Apple', 'model', 'iPhone 15', 'storage', '256GB'));
-- 查询特定属性
SELECT name, attributes->'$.brand' AS brand FROM products;
```
### 存储用户配置
```sql
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
settings JSON
);
INSERT INTO users VALUES
(1, 'John', JSON_OBJECT('theme', 'dark', 'notifications', JSON_ARRAY('email', 'sms')));
-- 更新配置
UPDATE users
SET settings = JSON_SET(settings, '$.theme', 'light')
WHERE id = 1;
```
JSON 函数为 MariaDB 提供了灵活的数据存储和查询能力，特别适合存储半结构化数据和动态属性。
服务端 · 2月21日 15:51
MariaDB 如何进行索引优化？有哪些索引类型和优化策略？MariaDB 的索引优化是提升数据库性能的关键，以下是主要的优化策略：
## 1. 索引类型选择
**B-Tree 索引**（默认）：
- 适用于等值查询、范围查询
- 支持排序和分组操作
- 适用于：大多数查询场景
**哈希索引**：
- 仅支持等值查询
- 查询速度极快
- 适用于：精确匹配查询
**全文索引**：
- 支持文本搜索
- 适用于：内容搜索、文章检索
**空间索引**：
- 支持地理空间数据
- 适用于：地理位置查询
## 2. 索引设计原则
1. **选择合适的列**：
- WHERE、JOIN、ORDER BY、GROUP BY 子句中的列
- 高选择性的列（唯一值多）
- 避免在低选择性列上创建索引
2. **复合索引顺序**：
- 将最常用的列放在前面
- 遵循最左前缀原则
- 考虑列的选择性
3. **避免过度索引**：
- 索引会增加写入开销
- 占用额外存储空间
- 定期清理无用索引
## 3. 查询优化技巧
```sql
-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE name = 'John';
-- 创建合适的索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_age ON users(name, age);
-- 使用覆盖索引避免回表
SELECT id, name FROM users WHERE name = 'John';
-- 避免在索引列上使用函数
-- 不推荐：WHERE YEAR(created_at) = 2024
-- 推荐：WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
```
## 4. 索引维护
```sql
-- 分析索引使用情况
SELECT * FROM information_schema.statistics
WHERE table_schema = 'your_database';
-- 重建索引
ALTER TABLE users ENGINE=InnoDB;
-- 删除无用索引
DROP INDEX idx_unused ON users;
```
## 5. 性能监控
```sql
-- 查看索引统计信息
SHOW INDEX FROM users;
-- 分析表
ANALYZE TABLE users;
-- 优化表
OPTIMIZE TABLE users;
```
通过合理的索引设计和维护，可以显著提升 MariaDB 的查询性能。
服务端 · 2月21日 15:51
MariaDB 性能调优有哪些关键参数和优化策略？MariaDB 的性能调优需要从多个维度进行优化，以下是主要的调优策略：
## 1. 配置参数优化
```ini
# my.cnf 配置文件
# 连接配置
max_connections = 500
max_connect_errors = 100000
wait_timeout = 28800
interactive_timeout = 28800
# InnoDB 配置
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# MyISAM 配置
key_buffer_size = 256M
myisam_sort_buffer_size = 64M
# 查询缓存（MariaDB 10.3+ 已移除）
# query_cache_size = 64M
# query_cache_type = 1
# 临时表配置
tmp_table_size = 256M
max_heap_table_size = 256M
# 排序和连接配置
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M
# 线程配置
thread_cache_size = 16
thread_stack = 256K
# 日志配置
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1
```
## 2. 内存优化
```sql
-- 查看 InnoDB 缓冲池使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
-- 查看内存使用
SHOW STATUS LIKE 'Memory%';
```
## 3. 硬件优化
1. **CPU**：多核处理器，建议 8 核以上
2. **内存**：建议 16GB 以上，InnoDB 缓冲池占用 70-80%
3. **磁盘**：使用 SSD，配置 RAID 10
4. **网络**：千兆以上网络带宽
## 4. 表结构优化
```sql
-- 使用合适的数据类型
-- 不推荐：VARCHAR(255) 用于状态字段
-- 推荐：TINYINT 或 ENUM
-- 规范化与反规范化权衡
-- 读取频繁：适当反规范化
-- 写入频繁：保持规范化
-- 分区表
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
```
## 5. 查询优化
```sql
-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 创建合适的索引
CREATE INDEX idx_user_id_created ON orders(user_id, created_at);
-- 避免全表扫描
SELECT * FROM large_table WHERE indexed_column = 'value';
-- 使用批量操作
INSERT INTO users (name, email) VALUES
('John', 'john@example.com'),
('Jane', 'jane@example.com');
```
## 6. 监控和诊断
```sql
-- 查看慢查询
SELECT * FROM mysql.slow_log
ORDER BY query_time DESC LIMIT 10;
-- 查看表状态
SHOW TABLE STATUS FROM database_name;
-- 查看索引使用情况
SELECT * FROM information_schema.statistics
WHERE table_schema = 'database_name';
-- 查看进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
```
## 7. 性能测试工具
```bash
# 使用 sysbench 进行性能测试
sysbench oltp_read_write \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=test \
--tables=10 \
--table-size=100000 \
--threads=16 \
--time=300 \
--report-interval=10 \
run
```
## 8. 定期维护
```sql
-- 分析表
ANALYZE TABLE table_name;
-- 优化表
OPTIMIZE TABLE table_name;
-- 检查表
CHECK TABLE table_name;
-- 修复表
REPAIR TABLE table_name;
```
通过系统性的性能调优，可以显著提升 MariaDB 的整体性能和稳定性。
服务端 · 2月21日 15:51
MariaDB 如何进行安全配置？有哪些安全最佳实践？MariaDB 的安全配置是保护数据库安全的重要环节，以下是主要的安全配置措施：
## 1. 用户权限管理
```sql
-- 创建用户并设置密码
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
-- 授予最小必要权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'app_user'@'localhost';
-- 撤销权限
REVOKE DELETE ON database_name.* FROM 'app_user'@'localhost';
-- 删除用户
DROP USER 'app_user'@'localhost';
-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
```
## 2. 配置文件安全
```ini
# my.cnf 安全配置
# 禁止远程 root 登录
skip-networking
# 或
bind-address = 127.0.0.1
# 禁用本地文件加载
local-infile = 0
# 限制最大连接数
max_connections = 100
# 启用 SSL
require-secure-transport = ON
ssl-ca = /path/to/ca-cert.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem
# 设置默认认证插件
default-authentication-plugin = mysql_native_password
```
## 3. 密码策略
```sql
-- 安装密码验证插件
INSTALL PLUGIN simple_password_check SONAME 'simple_password_check.so';
-- 配置密码策略
SET GLOBAL simple_password_check_minimal_length = 12;
SET GLOBAL simple_password_check_minimal_digit_count = 2;
SET GLOBAL simple_password_check_minimal_special_char_count = 1;
SET GLOBAL simple_password_check_minimal_uppercase_char_count = 1;
-- 强制密码过期
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
```
## 4. 网络安全
```bash
# 配置防火墙
# 只允许特定 IP 访问
iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP
# 使用 SSH 隧道
ssh -L 3306:localhost:3306 user@remote_server
```
## 5. 数据加密
```sql
-- 启用 InnoDB 表加密
-- my.cnf 配置
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1
-- 创建加密表
CREATE TABLE sensitive_data (
id INT PRIMARY KEY,
data VARCHAR(255),
ENCRYPTION='Y'
) ENGINE=InnoDB ENCRYPTED=YES;
-- 使用加密函数
SELECT AES_ENCRYPT('sensitive_data', 'encryption_key');
SELECT AES_DECRYPT(encrypted_data, 'encryption_key');
```
## 6. 审计日志
```sql
-- 启用审计日志
-- my.cnf 配置
plugin_load_add = server_audit
server_audit_events = CONNECT,QUERY,TABLE
server_audit_logging = ON
server_audit_file_path = /var/log/mariadb/audit.log
server_audit_file_rotate_size = 100M
server_audit_file_rotations = 9
-- 查看审计日志
SELECT * FROM information_schema.server_audit;
```
## 7. 定期安全检查
```sql
-- 查看所有用户
SELECT user, host FROM mysql.user;
-- 查看空密码用户
SELECT user, host FROM mysql.user WHERE authentication_string = '';
-- 查看具有所有权限的用户
SELECT user, host FROM mysql.user WHERE Grant_priv = 'Y';
-- 查看匿名用户
SELECT user, host FROM mysql.user WHERE user = '';
```
## 8. 备份安全
```bash
# 加密备份文件
mysqldump -u root -p database_name | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc
# 解密备份文件
openssl enc -d -aes-256-cbc -in backup.sql.gz.enc | gunzip | mysql -u root -p database_name
```
## 9. 安全最佳实践
1. **最小权限原则**：只授予必要的权限
2. **定期更新**：及时安装安全补丁
3. **强密码策略**：使用复杂密码并定期更换
4. **网络隔离**：限制数据库的网络访问
5. **加密传输**：使用 SSL/TLS 加密连接
6. **审计监控**：启用审计日志并定期审查
7. **备份保护**：加密备份文件并安全存储
8. **定期检查**：定期进行安全审计和漏洞扫描
通过以上安全配置措施，可以显著提升 MariaDB 的安全性，保护数据免受未授权访问和攻击。
服务端 · 2月21日 15:51
MariaDB 如何进行备份和恢复？有哪些备份策略和工具？MariaDB 的备份与恢复是保障数据安全的重要环节，以下是主要的备份和恢复方法：
## 1. 逻辑备份（mysqldump）
**全量备份**：
```bash
# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql
# 备份指定数据库
mysqldump -u root -p database_name > database_name.sql
# 备份指定表
mysqldump -u root -p database_name table_name > table_name.sql
# 备份并压缩
mysqldump -u root -p database_name | gzip > database_name.sql.gz
```
**增量备份**：
```bash
# 启用二进制日志
# my.cnf 配置
log-bin = mysql-bin
binlog-format = ROW
# 备份二进制日志
mysqlbinlog mysql-bin.000001 > binlog_backup.sql
```
**恢复数据**：
```bash
# 恢复完整备份
mysql -u root -p < all_databases.sql
# 恢复指定数据库
mysql -u root -p database_name < database_name.sql
# 恢复压缩备份
gunzip < database_name.sql.gz | mysql -u root -p database_name
# 应用二进制日志
mysqlbinlog mysql-bin.000001 | mysql -u root -p
```
## 2. 物理备份（Mariabackup）
**全量备份**：
```bash
# 创建备份
mariabackup --backup --target-dir=/backup/full \
--user=root --password=password
# 准备备份
mariabackup --prepare --target-dir=/backup/full
# 恢复备份
mariabackup --copy-back --target-dir=/backup/full
```
**增量备份**：
```bash
# 创建全量备份
mariabackup --backup --target-dir=/backup/full \
--user=root --password=password
# 创建增量备份
mariabackup --backup --target-dir=/backup/inc1 \
--incremental-basedir=/backup/full --user=root --password=password
# 准备备份
mariabackup --prepare --target-dir=/backup/full
mariabackup --prepare --target-dir=/backup/full \
--incremental-dir=/backup/inc1
```
## 3. 快照备份
```bash
# 使用 LVM 快照
lvcreate -L 10G -s -n mysql_snapshot /dev/vg0/mysql
mount /dev/vg0/mysql_snapshot /mnt/backup
rsync -av /mnt/backup/ /backup/mysql/
umount /mnt/backup
lvremove /dev/vg0/mysql_snapshot
```
## 4. 自动化备份脚本
```bash
#!/bin/bash
# backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mariadb"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
# 创建备份目录
mkdir -p $BACKUP_DIR
# 全量备份
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases \
--single-transaction --quick --lock-tables=false \
| gzip > $BACKUP_DIR/all_$DATE.sql.gz
# 保留最近7天的备份
find $BACKUP_DIR -name "all_*.sql.gz" -mtime +7 -delete
echo "Backup completed: all_$DATE.sql.gz"
```
## 5. 备份策略建议
1. **全量备份**：每天凌晨执行
2. **增量备份**：每小时执行
3. **二进制日志**：实时保留
4. **异地备份**：定期同步到远程服务器
5. **备份验证**：定期测试恢复流程
## 6. 恢复注意事项
1. 恢复前先停止 MariaDB 服务
2. 确保有足够的磁盘空间
3. 恢复后验证数据完整性
4. 记录恢复过程和时间点
5. 在测试环境先验证恢复流程
通过合理的备份策略和恢复流程，可以最大程度保障 MariaDB 数据的安全性和可靠性。
服务端 · 2月21日 15:51
MariaDB 的分区表有哪些类型？如何创建和管理分区表？MariaDB 的分区表（Partitioning）是将大表分割成更小、更易管理的部分的技术，可以显著提升查询性能和管理效率。
## 1. 分区类型
### RANGE 分区
```sql
-- 按日期范围分区
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 按数值范围分区
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
age INT
) PARTITION BY RANGE (age) (
PARTITION p0 VALUES LESS THAN (18),
PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
```
### LIST 分区
```sql
-- 按离散值列表分区
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATE,
region VARCHAR(20),
amount DECIMAL(10,2)
) PARTITION BY LIST COLUMNS(region) (
PARTITION p_east VALUES IN ('New York', 'Boston', 'Philadelphia'),
PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco', 'Seattle'),
PARTITION p_midwest VALUES IN ('Chicago', 'Detroit', 'Cleveland'),
PARTITION p_south VALUES IN ('Atlanta', 'Miami', 'Dallas')
);
```
### HASH 分区
```sql
-- 哈希分区（均匀分布数据）
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
category_id INT,
price DECIMAL(10,2)
) PARTITION BY HASH(id) PARTITIONS 4;
-- 线性哈希分区（更快但分布可能不均匀）
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2)
) PARTITION BY LINEAR HASH(customer_id) PARTITIONS 8;
```
### KEY 分区
```sql
-- KEY 分区（类似 HASH，使用主键或唯一键）
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
) PARTITION BY KEY(id) PARTITIONS 4;
```
## 2. 分区管理
### 添加分区
```sql
-- RANGE 分区添加新分区
ALTER TABLE orders ADD PARTITION (
PARTITION p2025 VALUES LESS THAN (2026)
);
-- LIST 分区添加新分区
ALTER TABLE orders ADD PARTITION (
PARTITION p_other VALUES IN ('Other', 'Unknown')
);
-- HASH/KEY 分区增加分区数量
ALTER TABLE products REORGANIZE PARTITION (
PARTITION p0, PARTITION p1, PARTITION p2, PARTITION p3
) INTO (
PARTITION p0, PARTITION p1, PARTITION p2,
PARTITION p3, PARTITION p4, PARTITION p5
);
```
### 删除分区
```sql
-- 删除分区（同时删除数据）
ALTER TABLE orders DROP PARTITION p2022;
-- 删除所有分区（转换为普通表）
ALTER TABLE orders REMOVE PARTITIONING;
```
### 合并分区
```sql
-- 合并 RANGE 分区
ALTER TABLE orders REORGANIZE PARTITION p2022, p2023 INTO (
PARTITION p2022_2023 VALUES LESS THAN (2024)
);
```
## 3. 分区查询
```sql
-- 查看分区信息
SELECT
PARTITION_NAME,
PARTITION_METHOD,
PARTITION_EXPRESSION,
PARTITION_DESCRIPTION,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';
-- 查询特定分区
SELECT * FROM orders PARTITION (p2023);
-- 使用分区裁剪优化查询
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
```
## 4. 分区维护
```sql
-- 检查分区
ALTER TABLE orders CHECK PARTITION p2023;
-- 优化分区
ALTER TABLE orders OPTIMIZE PARTITION p2023;
-- 分析分区
ALTER TABLE orders ANALYZE PARTITION p2023;
-- 修复分区
ALTER TABLE orders REPAIR PARTITION p2023;
```
## 5. 分区索引
```sql
-- 创建本地索引（每个分区独立索引）
CREATE INDEX idx_customer_id ON orders(customer_id);
-- 创建全局索引（MariaDB 10.3+）
CREATE UNIQUE INDEX idx_global ON orders(id);
```
## 6. 分区使用场景
### 时间序列数据
```sql
CREATE TABLE logs (
id BIGINT PRIMARY KEY,
log_time TIMESTAMP,
message TEXT,
level VARCHAR(10)
) PARTITION BY RANGE (UNIX_TIMESTAMP(log_time)) (
PARTITION p2024_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
PARTITION p2024_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01')),
PARTITION p2024_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01')),
PARTITION p2024_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01'))
);
```
### 大表归档
```sql
-- 定期归档旧数据
ALTER TABLE orders DROP PARTITION p2022;
```
## 7. 分区注意事项
1. **分区键选择**：选择查询中常用的列作为分区键
2. **分区数量**：不宜过多，建议 10-100 个分区
3. **主键约束**：主键必须包含分区键
4. **唯一索引**：唯一索引必须包含分区键
5. **数据分布**：确保数据在各分区间均匀分布
6. **维护成本**：分区表需要额外的维护操作
分区表是处理大数据量的有效手段，合理使用可以显著提升查询性能和管理效率。
服务端 · 2月21日 15:51
MariaDB 和 MySQL 有什么区别？MariaDB 是一个开源的关系型数据库管理系统，由 MySQL 的原始开发者创建，作为 MySQL 的直接替代品。它保持了与 MySQL 的高度兼容性，同时提供了更多的存储引擎、性能优化和新功能。
主要区别包括：
1. **存储引擎**：MariaDB 提供了更多存储引擎选择，如 Aria、ColumnStore、Spider、RocksDB 等，而 MySQL 主要使用 InnoDB 和 MyISAM。
2. **性能优化**：MariaDB 在查询优化、索引处理、缓存机制等方面进行了改进，通常在相同硬件上性能优于 MySQL。
3. **功能特性**：
- MariaDB 支持窗口函数（MySQL 8.0+ 才支持）
- 更丰富的 JSON 功能支持
- 支持动态列（Dynamic Columns）
- 更好的复制和集群功能
4. **开源策略**：MariaDB 采用完全开源的 GPL 许可证，而 MySQL 在 Oracle 收购后采用双重许可证。
5. **更新频率**：MariaDB 发布新版本更频繁，包含更多创新功能。
6. **兼容性**：MariaDB 10.x 之前版本与 MySQL 5.x 高度兼容，但后续版本逐渐分化。
在实际应用中，选择 MariaDB 还是 MySQL 主要取决于具体需求、团队技术栈和对开源策略的偏好。
服务端 · 2月21日 15:51
MariaDB 有哪些存储引擎？它们各自适用于什么场景？MariaDB 提供了多种存储引擎，每种引擎都有其特定的优势和适用场景：
1. **InnoDB**（默认引擎）
- 支持事务处理（ACID）
- 行级锁定，适合高并发环境
- 支持外键约束
- 支持崩溃恢复
- 适用于：OLTP 应用、电子商务、金融系统
2. **MyISAM**
- 表级锁定，读取速度快
- 不支持事务
- 不支持外键
- 适用于：读密集型应用、数据仓库、日志存储
3. **Aria**
- MyISAM 的改进版本
- 支持崩溃恢复
- 更好的并发性能
- 适用于：需要 MyISAM 特性但要求更高可靠性的场景
4. **ColumnStore**
- 列式存储引擎
- 适合大规模数据分析
- 支持压缩和并行查询
- 适用于：数据仓库、BI 分析、大数据处理
5. **Spider**
- 分片存储引擎
- 支持数据分片和分布式查询
- 可实现跨服务器数据访问
- 适用于：分布式数据库、水平扩展场景
6. **RocksDB**
- 基于 LSM-Tree 的存储引擎
- 高写入性能
- 支持压缩
- 适用于：写入密集型应用、时序数据
7. **TokuDB**
- 高压缩率
- 支持快速索引
- 适用于：大数据存储、归档系统
选择存储引擎时需要考虑：事务需求、读写比例、数据量、并发量、可靠性要求等因素。
服务端 · 2月21日 15:51
MariaDB 如何实现主从复制？有哪些复制模式？MariaDB 提供了多种复制方式，以满足不同场景的需求：
## 1. 主从复制（Master-Slave Replication）
**异步复制**：
- 主库执行事务后立即返回，不等待从库确认
- 性能最好，但可能存在数据延迟
- 适用于：读多写少、对数据一致性要求不高的场景
**半同步复制**：
- 主库等待至少一个从库确认接收事务后才返回
- 平衡了性能和数据一致性
- 适用于：需要较高数据一致性的场景
## 2. 组复制（Group Replication）
- 基于 Paxos 算法的多主复制
- 支持自动故障转移
- 提供强一致性保证
- 适用于：高可用性、高可靠性要求的生产环境
## 3. Galera 集群
- 同步多主复制
- 所有节点都可读写
- 无数据丢失风险
- 适用于：需要高可用性和读写负载均衡的场景
## 4. GTID 复制
- 使用全局事务 ID 标识事务
- 简化故障恢复和主从切换
- 便于管理复制拓扑
- 适用于：复杂的复制环境
## 配置示例
```sql
-- 主库配置
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
-- 从库配置
server-id = 2
relay-log = relay-bin
read-only = 1
```
## 监控复制状态
```sql
-- 查看主库状态
SHOW MASTER STATUS;
-- 查看从库状态
SHOW SLAVE STATUS;
```
选择复制方式时需要考虑：性能需求、数据一致性要求、故障恢复能力、运维复杂度等因素。
服务端 · 2月21日 15:51
如何优化 MariaDB 的查询性能？有哪些常用的优化技巧？MariaDB 的查询优化是提升数据库性能的核心，以下是主要的优化策略：
## 1. 使用 EXPLAIN 分析查询
```sql
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;
```
**关键指标**：
- **type**：访问类型（ALL < index < range < ref < eq_ref < const < system）
- **key**：使用的索引
- **rows**：预估扫描行数
- **Extra**：额外信息（Using filesort, Using temporary 需要优化）
## 2. 索引优化
```sql
-- 创建合适的索引
CREATE INDEX idx_name_age ON users(name, age);
-- 使用覆盖索引
SELECT id, name, age FROM users WHERE name = 'John';
-- 避免索引失效
-- 不推荐：WHERE name LIKE '%John%'
-- 推荐：WHERE name LIKE 'John%'
```
## 3. 查询重写
```sql
-- 避免 SELECT *
-- 不推荐：SELECT * FROM users WHERE id = 1;
-- 推荐：SELECT id, name, email FROM users WHERE id = 1;
-- 使用 LIMIT 限制结果
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- 避免子查询，使用 JOIN
-- 不推荐：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 推荐：SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;
-- 使用 UNION ALL 替代 UNION（如果不需要去重）
-- 不推荐：SELECT name FROM users UNION SELECT name FROM admins;
-- 推荐：SELECT name FROM users UNION ALL SELECT name FROM admins;
```
## 4. 分页优化
```sql
-- 传统分页（深分页性能差）
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 优化方案1：使用游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
-- 优化方案2：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp
ON o.id = tmp.id;
```
## 5. JOIN 优化
```sql
-- 确保被驱动表有索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 小表驱动大表
SELECT * FROM small_table s JOIN large_table l ON s.id = l.small_id;
-- 使用 STRAIGHT_JOIN 强制连接顺序
SELECT * FROM large_table l STRAIGHT_JOIN small_table s ON s.id = l.small_id;
```
## 6. 配置优化
```ini
# my.cnf 配置
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
```
## 7. 监控慢查询
```sql
-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
```
通过以上优化策略，可以显著提升 MariaDB 的查询性能。
服务端 · 2月21日 15:39