centos mysql性能优化技巧有哪些

CentOS环境下MySQL性能优化技巧

1. 硬件级优化

• 使用物理服务器：避免虚拟机带来的资源竞争，提升IO和CPU利用率。
• 增加内存：MySQL依赖内存缓存数据和索引，建议物理内存足够大（如InnoDB缓冲池占内存的50%-80%）。
• 选择高速存储：主库使用SSD（或RAID10），系统盘用SSD、数据盘用SAS，分离日志与数据分区，提升IO性能。
• 避免Swap：Swap速度远慢于内存，可通过调整vm.swappiness参数（设为10以下）或直接禁用，防止数据交换导致性能暴跌。
• 双线双电：保障服务器供电稳定，避免因断电损坏磁盘数据。

2. 配置参数优化

• 调整缓冲池大小：innodb_buffer_pool_size是InnoDB最重要的参数，建议设置为物理内存的50%-80%（如8GB内存设为4-6GB），用于缓存数据和索引，减少磁盘IO。
• 优化连接数：max_connections根据并发需求调整（如普通应用设为200-500，高并发设为800-1000），避免过多连接导致内存耗尽；同时调整thread_cache_size（如100-200），缓存空闲线程，减少线程创建开销。
• 日志文件设置：innodb_log_file_size设置为缓冲池大小的25%-50%（如4GB缓冲池设为1-2GB），innodb_log_files_in_group设为2-3，减少日志切换频率；innodb_flush_log_at_trx_commit设为1（默认，保证数据安全）或2（提升性能但可能丢失1秒数据），根据业务需求权衡。
• 调整文件描述符：open_files_limit设置为10240以上，确保MySQL能打开足够多的表文件，避免“Too many open files”错误。

3. 索引优化（核心性能提升点）

• 选择合适列建索引：优先为WHERE、JOIN、ORDER BY、GROUP BY频繁使用的列创建索引；避免对低基数列（如性别、布尔字段）建索引（区分度低，收益小）。
• 遵循最左前缀原则：组合索引（如INDEX(name, age)）需按索引顺序查询（WHERE name='张三' AND age=20可使用索引，WHERE age=20无法使用），避免索引失效。
• 使用覆盖索引：查询仅选择索引中的字段（如SELECT name, age FROM users WHERE name='张三'），避免回表操作（无需访问主键索引），提升查询速度。
• 避免索引失效：不使用函数或计算操作（如WHERE YEAR(create_time)=2025应改为WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31'）；避免!=、<>、OR（未索引列）；避免隐式类型转换（如WHERE varchar_col=123应改为WHERE varchar_col='123'）。
• 定期维护索引：使用OPTIMIZE TABLE整理表碎片，ANALYZE TABLE更新统计信息，保持索引高效；使用pt-duplicate-key-checker工具删除冗余索引（如重复的(col1)索引）。

4. 查询优化

• 使用EXPLAIN分析：执行EXPLAIN SELECT ...查看查询计划，关注type（如ref、range优于ALL）、key（是否使用索引）、rows（扫描行数，越少越好），识别全表扫描、索引失效等问题。
• 优化SQL语句：避免SELECT *，只选择需要的列；减少JOIN操作（如用子查询替代多表JOIN）；使用LIMIT限制返回结果（如分页查询）；避免子查询（用JOIN替代，性能更高）。
• 分页优化：使用id > last_id代替LIMIT offset, count（如SELECT * FROM users WHERE id > 1000 LIMIT 10），避免深度分页导致的性能下降。

5. 存储引擎优化

• 选择合适引擎：InnoDB（默认）适用于大多数场景（支持事务、行级锁、外键）；MyISAM适用于读密集型应用（不支持事务，但查询速度快）。
• 配置InnoDB参数：innodb_buffer_pool_size（同上）、innodb_log_file_size（同上）、innodb_flush_log_at_trx_commit（权衡性能与安全）、innodb_io_capacity（设为SSD的IO能力，如5000-10000），提升InnoDB性能。
• 配置MyISAM参数：key_buffer_size（设为物理内存的10%-20%，用于缓存MyISAM索引）、table_open_cache（设为2000以上，缓存打开的表数量），提升MyISAM查询速度。

6. 定期维护

• 优化表：使用OPTIMIZE TABLE命令整理表碎片，释放空间（尤其适用于频繁插入、删除的表）。
• 备份数据：定期使用mysqldump或物理备份（如Percona XtraBackup）备份数据，确保数据安全。
• 监控性能：使用SHOW ENGINE INNODB STATUS查看InnoDB状态，SHOW GLOBAL STATUS查看全局状态（如Threads_running、Queries）；使用第三方工具（如mysqltuner、Percona Toolkit）分析性能瓶颈，针对性优化。

7. 缓存优化

• 应用层缓存：使用Redis或Memcached缓存频繁访问的数据（如热点商品信息），减少数据库查询次数。
• 查询缓存（MySQL 5.7.20前）：开启query_cache_type=1、query_cache_size=64M（适合读多写少的场景），但高并发写入时可能降低性能（MySQL 8.0已移除）。
• 连接池：使用连接池（如HikariCP、Druid）管理数据库连接，减少连接创建和销毁的开销，提升并发性能。