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 配置与运维要点

五典型场景与索引示例

多条件查询：
- 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));（前缀长度依据区分度评估设定）。

最新问答