Ubuntu MariaDB索引优化策略是什么

Ubuntu MariaDB 索引优化策略

一 索引设计原则

• 控制数量与避免冗余：单表索引建议不超过5个；避免给每列单独建索引，优先设计高效的联合索引；删除重复或包含关系导致的冗余索引（如 primary key(id)、index(id)、unique index(id) 或 index(a,b,c)、index(a,b)、index(a)）。
• InnoDB 主键规范：每个 InnoDB 表必须有主键；优先使用自增整数或顺序递增列，避免UUID/MD5/长字符串作主键，以降低页分裂与随机 I/O。
• 索引列顺序：将区分度最高的列放在最左；在区分度相近时，优先放字段长度更小的列；把最常用的查询列放在左侧，减少索引数量。
• 覆盖索引优先：让索引包含查询所需全部列（SELECT、WHERE、ORDER BY、GROUP BY），避免“回表”。
• 字符串索引长度：对 VARCHAR 使用前缀索引，通过区分度评估选择长度，例如计算 count(distinct left(col, N))/count(*)。
• 为高频访问路径建索引：对 WHERE、ORDER BY、GROUP BY、DISTINCT 以及多表 JOIN 的关联列建立索引。

二 索引类型选择与创建

• B-Tree/B+Tree 为主：InnoDB 默认使用 B+Tree 索引，适合范围查询、排序与等值匹配。
• 联合索引遵循最左前缀：查询条件需按索引列顺序从左到右匹配；跳过左侧列或打乱顺序，右侧列通常无法使用索引。
• 前缀索引示例：对长文本列只索引前 N 个字符（N 由区分度决定）。
• 覆盖索引示例：将 SELECT 与 WHERE/GROUP/ORDER 所需列一并纳入索引，减少回表。
• 主键选择示例：使用自增 ID 作为主键，避免频繁更新的列或 UUID。

三 让索引生效的 SQL 写法

• 避免对索引列做计算或函数：如 LEFT(name,3)=‘abc’、stuno+1=900001 会导致索引失效，改写为 name LIKE ‘abc%’、stuno=900000。
• 避免隐式类型转换：如 name=123（字符串列对数字比较）会触发转换导致索引失效，统一为 name=‘123’。
• 范围条件放右侧：联合索引中范围条件（>、<、BETWEEN、LIKE ‘prefix%’ 以外的模式）右侧的列通常无法使用索引，将范围条件置于索引最右。
• 谨慎使用否定与空值：!=、<> 常导致索引失效；IS NULL 可使用索引，IS NOT NULL 可能无法使用，必要时改为覆盖索引或改写条件。
• 利用 EXPLAIN 验证：关注 type（目标为 ref/eq_ref/range/index，避免 ALL 全表扫描）、key（实际用到的索引）、Extra 中是否出现 Using filesort/Using temporary。

四 维护与监控

• 慢查询驱动优化：启用慢查询日志，用 pt-query-digest 或 mysqldumpslow 聚合分析，针对 TOP SQL 建立或调整索引。
• 更新统计信息：执行 ANALYZE TABLE 保持索引统计最新，帮助优化器选择更优执行计划。
• 必要时的表维护：对高碎片表执行 OPTIMIZE TABLE（InnoDB 通常受益于重建整理），并定期归档历史数据降低索引与扫描压力。
• 监控与告警：结合 SHOW STATUS/LONG QUERY 与系统工具（如 top、iostat、ss），或使用 Prometheus/Grafana/Zabbix 建立可视化监控与阈值告警。

五 Ubuntu 配置与落地要点

• 配置文件路径：常见为 /etc/mysql/mariadb.conf.d/50-server.cnf 或 /etc/my.cnf。
• InnoDB 缓冲池：将 innodb_buffer_pool_size 设为物理内存的约70%，提升索引与数据页命中率。
• 日志与持久性：适度增大 innodb_log_file_size；在可接受的场景下将 innodb_flush_log_at_trx_commit=2 以换取吞吐（存在少量数据丢失风险）。
• 查询缓存注意：MariaDB 10.1+ 已移除查询缓存（query cache），如需缓存请在应用层/Redis/Memcached实现。
• 变更流程：索引变更在低峰期执行，先在测试环境验证 EXPLAIN 与基准性能，再灰度上线并持续观测。