Ubuntu MariaDB索引优化策略

Ubuntu MariaDB 索引优化策略

一 基础规范与索引设计原则

• 使用 InnoDB 作为默认存储引擎，并为每张表定义显式主键；优先选择自增整数或顺序值作为主键，避免 UUID/MD5/长字符串 主键，以减少页分裂与随机 IO。
• 控制索引数量，建议单表不超过5个；避免“每列一索引”，优先设计联合索引以覆盖多条件查询。
• 联合索引列顺序遵循：
  1. 区分度最高的列放最左；2) 字段长度更小的列放更左（提升页容纳度与 IO 效率）；3) 使用更频繁的列放更左。
• 避免冗余/重复索引，例如同时存在 PRIMARY KEY(id)、INDEX(id)、UNIQUE INDEX(id)，或 INDEX(a,b,c) 与 INDEX(a,b)、INDEX(a)。
• 在 VARCHAR 上建立索引时指定前缀长度，用区分度评估：count(distinct left(列名, N))/count(*)，一般前缀长度为20时区分度可达90%+（视业务而定）。
• 优先设计覆盖索引（索引包含查询所需全部列），减少“回表”。
• 为以下场景建立索引：WHERE 条件列、ORDER BY/GROUP BY/DISTINCT 列、JOIN 关联列。

二 查询与索引匹配实践

• 用 EXPLAIN 检查执行计划：关注 type（尽量达到 ref/eq_ref/range/index，避免 ALL 全表扫描）、key（实际使用的索引）、rows（扫描行数）、Extra（避免 Using filesort/Using temporary）。
• 面向索引的 SQL 写法：
  • 仅查询需要的列，避免 *SELECT ；必要时用覆盖索引减少回表。
  • 联合索引遵循最左前缀；范围条件尽量放在索引右侧，把等值条件放在左侧。
  • 减少 ORDER BY/GROUP BY 与索引顺序不一致导致的额外排序。
  • 分页深翻时结合索引+子查询或延迟关联，避免大偏移量的高成本扫描。
• 谨慎使用 FORCE INDEX：仅在统计信息不准或优化器选择错误时短期使用，并通过修正索引/SQL/统计信息根治。

三 维护与执行效率

• 保持统计信息及时更新，便于优化器选择更优索引（如执行 ANALYZE TABLE）。
• 适度执行 OPTIMIZE TABLE 回收碎片（对高变更表更有价值，注意在低峰期进行）。
• 定期清理与分析慢查询日志，围绕扫描行数多、执行时间长、使用临时表/文件排序的 SQL 建立或调整索引。
• 监控连接与资源：用 SHOW PROCESSLIST、SHOW STATUS、mysqladmin status、top/htop、iostat、ss 观察慢查询、锁等待、IO 与连接数，结合 Prometheus/Grafana/Zabbix 做长期可视化监控。

四 Ubuntu 配置与运维要点

• 配置文件路径通常为 /etc/mysql/mariadb.conf.d/50-server.cnf 或 /etc/my.cnf。与索引相关的性能基础包括：
  • 将 innodb_buffer_pool_size 设为物理内存的约70%，提升索引与数据页的缓存命中率。
  • 适度增大 innodb_log_file_size，减少检查点触发的写放大。
  • 在可接受的持久性权衡下，将 innodb_flush_log_at_trx_commit=2 提升写吞吐（宕机可能丢失最近1秒事务）。
  • 合理设置 max_connections，避免连接风暴。
  • 启用 slow_query_log 与合理阈值，配合 mysqldumpslow 或 pt-query-digest 分析。
  • 注意：MariaDB 10.0+ 已移除查询缓存（query cache），如需缓存请在应用层/Redis/Memcached实现。

五 典型场景与索引示例

• 多条件查询：
  • SQL：SELECT id, name FROM t WHERE status='active' AND age>30;
  • 索引：CREATE INDEX idx_status_age_id_name ON t(status, age, id, name);（覆盖索引，避免回表）。
• 排序优化：
  • SQL：SELECT id, created FROM t WHERE status='open' ORDER BY created DESC;
  • 索引：CREATE INDEX idx_status_created ON t(status, created);（利用索引有序性避免 filesort）。
• 多表关联：
  • SQL：SELECT o.id, u.name FROM orders o JOIN users u ON o.user_id=u.id WHERE o.status='paid';
  • 索引：CREATE INDEX idx_orders_status_user ON orders(status, user_id);；CREATE INDEX idx_users_id ON users(id);（外键/关联列务必有索引）。
• 大文本前缀索引：
  • SQL：SELECT url FROM urls WHERE url LIKE 'https://example.com/%';
  • 索引：CREATE INDEX idx_url_prefix ON urls(url(20));（前缀长度依据区分度评估设定）。