数据库服务器性能调优实战 MySQL与Redis缓存配置最佳实践
数据库是网站最值钱也最容易被拖垮的环节。很多人服务器配置越加越高,4核变8核、16G变32G,MySQL还是慢,原因是查询本身就没写好,加硬件只是给烂SQL续命。调优的第一原则不是改参数,是看慢查询日志。
MySQL慢查询日志必须开。在my.cnf里加:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
long_query_time设1秒,超过1秒的查询全记下来。log_queries_not_using_indexes把没走索引的也记,哪怕它执行快,没索引意味着全表扫描,数据量一上来就爆。日志开了之后用mysqldumpslow或pt-query-digest分析,排在前几位的就是最该优化的查询。
索引是性价比最高的优化手段。WHERE条件里的列、JOIN的关联列、ORDER BY的列,都要有索引。但索引不是越多越好,每个索引都占空间、拖写入速度。复合索引注意最左前缀原则,INDEX(a,b,c)能命中a、a+b、a+b+c,但查b或c单独条件时索引不生效。VARCHAR字段建索引时指定长度,INDEX(name(20))够用就行,别整列索引浪费空间。
常见慢查询重灾区:LIKE '%关键词%'无法用索引,改成LIKE '关键词%'或上全文检索;SELECT * 拉全字段,只查需要的列;ORDER BY RAND() 随机排序在大表上灾难,用预生成随机ID替代;子查询嵌套三层,改成JOIN;隐式类型转换,字符串字段用数字查,索引直接失效。
InnoDB缓冲池是MySQL内存使用的核心。innodb_buffer_pool_size一般设物理内存的50%到70%,8G内存的机器设4G到5G。缓冲池越大,热点数据越可能留在内存里,磁盘IO越少。但别设超过80%,系统要留内存给操作系统缓存、Redis、PHP-FPM。
连接数别乱加。max_connections默认151,很多人一卡就改到1000,结果每个连接占内存,1000个连接把内存吃完,OOM killer直接杀MySQL。正确做法是控制应用端连接池大小,PHP用持久连接或连接池中间件,Java用HikariCP配合理maxPoolSize。连接不够用往往是慢查询占着连接不放,优化查询比加连接数管用。
Redis做缓存时,先确认它真的在干活。WordPress装了Redis Object Cache插件不等于缓存生效,要看插件状态页显示Connected还是Not Connected,或者用redis-cli info stats看keyspace_hits和keyspace_misses,命中率低于80%说明缓存策略有问题。对象缓存存的是数据库查询结果,不是整页HTML,所以页面里动态内容多、登录用户多时效果最明显。
Redis内存上限必须设。maxmemory 2gb这种明确值,加上淘汰策略maxmemory-policy allkeys-lru或allkeys-lfu。不设上限,Redis慢慢吃光内存,最后被系统OOM干掉或者开始用Swap,性能断崖。缓存键要加前缀区分站点,别多个站共用一个Redis实例时键名冲突。敏感数据别放Redis,它默认不加密不认证,端口一暴露数据全裸。
MySQL和Redis配合的正确姿势:读请求先查Redis,命中直接返回;未命中查MySQL,结果写回Redis再返回。写操作先更MySQL,再删Redis对应缓存键,下次读时自动重建。别先更Redis再更MySQL,数据一致性难保证。缓存穿透用空值缓存或布隆过滤器挡;缓存击穿用互斥锁或逻辑过期;缓存雪崩用随机过期时间分散。
表结构也影响性能。大表超过500万行就要考虑分表或归档,日志表、会话表、历史订单按月分表。字段类型选最小够用的,INT够别用BIGINT,VARCHAR(50)够别用VARCHAR(255)。TEXT和BLOB字段单独拆表,主表只留引用ID,列表查询时不拉大字段。时间字段用TIMESTAMP或DATETIME,别用INT存时间戳,范围查询没法走索引。
定期维护不能省。ANALYZE TABLE更新统计信息让优化器选对索引;OPTIMIZE TABLE回收碎片空间,但会锁表,大表在低谷期做;binlog和慢查询日志定期清理,别让磁盘被日志吃满。监控方面,CPU持续超70%、连接数长期在max_connections的60%以上、InnoDB缓冲池命中率低于95%、主从延迟持续增长,这些都是要扩容或优化的信号。
数据库调优不是一锤子买卖,是持续看慢日志、看监控、看执行计划的过程。EXPLAIN是每个开发都要会用的工具,看type列是不是ALL全表扫描,看rows列预估扫描行数,看Extra列有没有Using filesort或Using temporary。能把这些看明白,八成慢查询都能自己解决。

发表评论