数据库是应用系统的核心，优化数据库性能是提升用户体验的关键。
一、SQL优化：让查询速度提升10倍的核心技巧
1. 索引优化：命中率决定效率
联合索引 > 单列索引-- 错误：多个单列索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_email ON users(email);
-- 正确：联合索引
CREATE INDEX idx_users_search ON users(name, email, phone);
覆盖索引减少回表
查询时直接通过索引获取数据，避免访问主表：CREATE INDEX idx_orders_covering ON orders(order_id, status, created_at);
SELECT order_id, status FROM orders WHERE status = 'pending'; -- 命中覆盖索引
2. 避免索引失效的陷阱
函数操作导致索引失效-- 错误：使用YEAR函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 正确：范围查询
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
二、配置优化：榨干硬件性能的终极参数
1. 内存分配策略
# MySQL my.cnf配置示例
innodb_buffer_pool_size = 总内存的70% # InnoDB的核心缓存
innodb_buffer_pool_instances = CPU核心数 # 提升并发能力
tmp_table_size = 64M # 内存临时表上限
2. I/O性能调优
SSD环境配置innodb_io_capacity = 2000 # SSD的IOPS能力
innodb_flush_log_at_trx_commit = 1 # 事务安全优先
三、表结构设计：从范式到反范式的平衡艺术
1. 数据类型优化
整数类型：用SMALLINT代替INT，节省存储空间。时间类型：用TIMESTAMP代替VARCHAR，支持自动更新。JSON字段：合理使用JSON类型并建立虚拟索引：CREATE TABLE product_attributes (
id INT,
attributes JSON,
INDEX idx_color ((CAST(attributes->>'$.color' AS CHAR(20))))
);
2. 分表与分区策略
水平分表：按时间或用户ID分表，减少单表压力。CREATE TABLE orders_2023 (
order_id INT PRIMARY KEY,
order_date DATE CHECK (YEAR(order_date) = 2023)
);
分区维护：定期清理旧数据分区：ALTER TABLE orders DROP PARTITION p2022;
四、缓存优化：降低数据库负载的黄金法则
1. 多级缓存设计
-- 内存、Redis、磁盘三级缓存
CREATE TABLE cache_levels (
id INT PRIMARY KEY,
level_name VARCHAR(50),
priority INT,
max_size BIGINT,
expire_time INT
);
INSERT INTO cache_levels VALUES
(1, 'memory', 1, 1GB, 300),
(2, 'redis', 2, 10GB, 3600);
2. 缓存一致性保障
通过触发器自动清理失效缓存：
CREATE TRIGGER after_product_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
DELETE FROM query_cache WHERE cache_key LIKE CONCAT('product:', NEW.id, '%');
END;
五、安全优化：数据防泄漏的终极防线
1. 字段脱敏与加密
敏感数据加密存储CREATE FUNCTION encrypt_data(p_data TEXT, p_key VARCHAR(32))
RETURNS VARBINARY(1000)
RETURN AES_ENCRYPT(p_data, p_key);
手机号脱敏显示SELECT mask_phone('13812345678'); -- 输出：138****5678
2. 权限最小化原则
基于角色的权限控制（RBAC）CREATE ROLE analyst;
GRANT SELECT ON analytics.* TO analyst;
六、监控与维护：数据库健康的“体检报告”
1. 慢查询实时监控
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; # 超过2秒的查询记录
2. 自动化维护脚本
-- 每日自动优化表
CREATE EVENT daily_maintenance
ON SCHEDULE EVERY 1 DAY
DO
CALL maintain_tables();
CALL cleanup_old_data();
结语：优化是持续的过程
数据库优化没有银弹，需要结合业务场景持续调整。建议每季度进行一次全面的性能评估与参数调优，并建立完善的监控告警体系。
推荐工具：
监控工具：Prometheus + Grafana压测工具：sysbench、TPCC-MySQL