Ubuntu MariaDB查询优化策略有哪些

Ubuntu MariaDB查询优化的系统策略

一 监控与定位瓶颈

• 开启并分析慢查询：在配置中启用 slow_query_log，设置 long_query_time（如 1 秒），用 mysqldumpslow 或 pt-query-digest 聚合分析；结合 EXPLAIN FORMAT=JSON 检查是否出现全表扫描（type: ALL）、是否发生 filesort 或 Using temporary。
• 实时观测与排障：用 SHOW PROCESSLIST 定位阻塞与长事务；用 SHOW STATUS LIKE ‘Handler%’、SHOW ENGINE INNODB STATUS 观察扫描行数与锁等待；系统层面配合 top/htop、iostat、ss 判断 CPU、I/O 与连接压力。
• 建立基线：持续记录 QPS、TPS、连接数、InnoDB 缓冲池命中率、慢查询数 等指标，便于评估优化成效与回滚风险。

二 索引设计与使用

• 为高频 WHERE、JOIN、ORDER BY/GROUP BY、DISTINCT 列建立索引；优先设计联合索引而非单列索引堆砌，遵循最左前缀；尽量使用覆盖索引减少回表；在 VARCHAR 上使用前缀索引控制索引体积。
• 控制索引数量与质量：单表索引建议不超过5 个，避免重复/冗余索引；InnoDB 表必须有主键，优先选择自增整数，避免 UUID/MD5/长字符串 作为主键；连接字段需保持数据类型、字符集、排序规则一致以避免隐式转换。
• 提升索引选择性：将区分度高（基数高）的列放在联合索引左侧；对长文本用区分度评估选择前缀长度（如 count(distinct left(col, N))/count(*)）。
• 避免使索引失效的写法：不要在索引列上使用函数或表达式（如 YEAR(col)=2025、LOWER(col)=…），改写为区间条件或把计算移到常量侧。

三 SQL 写法与执行计划优化

• 精简投影与分页：避免 SELECT ，仅返回必要列；分页结合索引有序性与游标/键集分页*（记住上一页最后一条的主键/索引列），减少 OFFSET 带来的深翻成本。
• 优化 JOIN 与子查询：为所有 JOIN 条件列建立索引；能用 JOIN 替代低效子查询时优先使用；确保关联字段类型与字符集一致。
• 让排序与分组走索引：将 ORDER BY/GROUP BY 与索引顺序对齐，避免 filesort 与 Using temporary；必要时增加合适的联合索引。
• 控制中间结果集：在子查询或派生表中尽早过滤与聚合，减少参与后续运算的数据量。

四 配置参数与存储引擎优化

• InnoDB 缓冲池：将 innodb_buffer_pool_size 设为物理内存的约70%（专用库），提升数据与索引命中率。
• 日志与持久性权衡：适度增大 innodb_log_file_size 提升批量写入吞吐；将 innodb_flush_log_at_trx_commit=2 可降低提交延迟但会牺牲部分持久性（合规/强一致场景保持默认 1）。
• 临时表与内存：适当提高 tmp_table_size / max_heap_table_size，减少磁盘临时表的使用。
• 查询缓存：在 MariaDB 10.1 及更早版本可酌情开启 query_cache_size；自 MariaDB 10.2 起默认禁用，MariaDB 10.3+ 移除查询缓存，建议以 Redis/Memcached 等应用层缓存替代。
• 连接与会话：根据业务峰值设置 max_connections，并优化应用连接池与超时，避免连接风暴。

五 表设计与维护策略

• 规范化与类型：适度规范化减少冗余；选用合适的数据类型（如用整型存数字），降低存储与比较成本。
• 分区与分表：对超大表按时间/租户/地域等维度做分区或分表，缩小扫描范围、便于维护。
• 碎片治理：周期性执行 OPTIMIZE TABLE 或使用在线 DDL 工具回收碎片（注意锁与复制延迟影响）。
• 高可用与扩展：读写分离、主从复制、分片可提升并发与可用性，把复杂查询放到从库或专用分析节点。