1. 硬件基础优化
2. 存储引擎配置优化(以InnoDB为主)
innodb_buffer_pool_size:这是InnoDB最重要的参数,用于缓存数据和索引。建议设置为系统总内存的60%-80%(如16GB内存可设置为10-12GB),能显著提高数据访问速度。innodb_log_file_size:增大日志文件大小(如256MB-512MB)可减少日志切换频率,提高事务处理性能。需平衡性能与数据恢复时间(日志越大,恢复越慢)。innodb_flush_log_at_trx_commit:该参数控制事务日志的刷新策略。默认值1(每次事务提交都刷新到磁盘)最安全,但性能较低;若可接受短暂数据丢失(如非金融场景),可设置为2(每秒刷新一次)或0(每秒刷新一次,崩溃时可能丢失1秒数据),大幅提升性能。innodb_file_per_table:将每个InnoDB表的数据和索引存储在独立文件(.ibd)中,便于单独优化表(如OPTIMIZE TABLE)和迁移数据,避免单个文件过大导致的性能下降。3. 索引策略优化
WHERE、JOIN、ORDER BY的列创建索引(如主键、外键、高频查询字段),能大幅减少全表扫描。例如,WHERE user_id = 100的查询,若user_id有索引,可直接定位到数据行。SHOW INDEX FROM table_name查看索引使用情况)。WHERE user_id = 100 AND status = 'active'),创建复合索引((user_id, status))能覆盖多个查询条件,提高查询效率。注意索引列的顺序(将选择性高的列放在前面)。SELECT user_id, status FROM users WHERE user_id = 100,若(user_id, status)有索引,可直接从索引中获取数据,无需回表查询),减少磁盘IO。4. 查询语句优化
EXPLAIN分析查询:通过EXPLAIN SELECT ...命令查看查询执行计划,识别性能瓶颈(如全表扫描、索引未使用、临时表创建)。例如,若type列为ALL,表示全表扫描,需优化索引。SELECT *:只选择需要的列(如SELECT user_id, name FROM users),减少数据传输量和内存占用,尤其在查询大表时效果明显。JOIN操作:确保JOIN字段有索引(如ON a.user_id = b.user_id,a.user_id和b.user_id都应有索引),避免笛卡尔积。优先使用INNER JOIN(效率高)替代LEFT JOIN(除非需要保留左表所有数据)。LIMIT分页:对于大数据量的分页查询(如LIMIT 10000, 10),避免使用大偏移量(可通过WHERE id > last_id ORDER BY id LIMIT 10优化,last_id为上一页最后一条记录的ID),减少查询时间。5. 定期维护操作
OPTIMIZE TABLE table_name命令,整理表碎片(尤其是频繁插入、更新、删除的表),提高数据访问效率。对于InnoDB表,该命令会重建表并整理索引。ANALYZE TABLE table_name命令更新表的统计信息,帮助优化器生成更优的执行计划(如选择正确的索引)。6. 缓存策略优化
query_cache_size=64M,query_cache_type=1),缓存查询结果,减少重复查询的开销。但需注意,频繁的写操作(如高并发的INSERT、UPDATE)会导致查询缓存频繁失效,反而降低性能(MariaDB 10.1.7+版本已移除查询缓存)。7. 监控与调优工具
top、vmstat、iostat等命令监控系统资源(CPU、内存、磁盘IO)的使用情况;使用SHOW STATUS、SHOW PROCESSLIST命令查看MariaDB的状态(如连接数、查询执行时间、锁等待);使用Percona Monitoring and Management(PMM)、MariaDB Enterprise Monitor等专业工具,全面监控数据库性能。slow_query_log=1,slow_query_log_file=/var/log/mysql/slow-queries.log,long_query_time=2),记录执行时间超过阈值的查询,定期分析这些查询(用EXPLAIN),针对性优化。