Linux MariaDB性能调优技巧有哪些

一、硬件优化：提升基础性能支撑

二、配置文件调优：核心参数优化

调整缓冲区大小：
- innodb_buffer_pool_size：设置为系统内存的50%-80%（InnoDB引擎的核心缓存，影响数据读取效率）；
- key_buffer_size：用于MyISAM索引缓存（若使用MyISAM表，建议设置为内存的20%-30%）；
- tmp_table_size/max_heap_table_size：增加临时表大小（避免大查询因临时表满而转为磁盘临时表）。
优化连接数：
- max_connections：根据应用需求调整（避免过多连接导致资源耗尽）；
- thread_cache_size：缓存线程以减少创建/销毁开销（建议设置为100-200）。
日志与事务设置：
- innodb_log_file_size：增大重做日志文件大小（如256M-512M），减少日志切换频率；
- innodb_flush_log_at_trx_commit：设置为2（平衡性能与数据安全性，牺牲少量持久性换取更高写入性能）；
- slow_query_log：启用慢查询日志（记录执行时间超过阈值的查询，帮助定位性能瓶颈）。

三、索引优化：加速查询的关键

创建合适索引：为经常用于WHERE、JOIN、ORDER BY的列创建索引（如主键、唯一索引、普通索引）。
使用复合索引：针对多列查询创建复合索引（遵循最左前缀原则，如(col1, col2, col3)可覆盖col1、col1+col2、col1+col2+col3的查询）。
避免过度索引：过多索引会增加写操作（INSERT/UPDATE/DELETE）的开销，并占用更多存储空间。
定期维护索引：通过ANALYZE TABLE更新索引统计信息，使用OPTIMIZE TABLE重建碎片化索引。

四、查询优化：减少数据库负载

使用EXPLAIN分析：通过EXPLAIN命令查看查询执行计划（识别全表扫描、索引未使用等问题）。
**避免SELECT ***：只选择需要的列（减少数据传输量，提高查询效率）。
优化WHERE子句：避免在索引列上使用函数或计算（如WHERE YEAR(create_time) = 2025会导致索引失效，改为WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31'）。
使用JOIN代替子查询：在大多数情况下，JOIN比子查询更高效（如SELECT a.* FROM table_a a JOIN table_b b ON a.id = b.a_id）。
分页查询优化：对于大数据量分页，使用LIMIT offset, size时，可通过子查询优化（如SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT size，其中last_id为上一页最后一条记录的ID）。

五、缓存优化：减少重复计算

启用查询缓存：若应用有大量重复查询，可启用query_cache_size（如设置为64M-256M），但需注意高并发写入场景下可能降低性能（MySQL 8.0已移除查询缓存，可替代为Redis/Memcached）。
应用层缓存：使用Redis、Memcached等缓存频繁访问的数据（如热点数据、报表数据），减少数据库查询次数。
MariaDB内部缓存：合理设置table_open_cache（表缓存，减少表打开/关闭次数）、table_definition_cache（表定义缓存，提高表结构读取效率）。

六、定期维护：保持数据库健康

七、监控与分析：持续优化依据

使用监控工具：通过top、htop、iostat、vmstat等工具监控CPU、内存、磁盘I/O使用情况；使用SHOW STATUS、SHOW PROCESSLIST查看数据库状态（如活跃连接、锁等待）。
慢查询日志分析：使用pt-query-digest、mysqldumpslow等工具分析慢查询日志（找出执行慢的查询，针对性优化）。
性能指标跟踪：关注关键指标（如QPS、TPS、连接数、缓存命中率），及时发现性能瓶颈。

最新问答