数据库慢查询怎么优化
数据库慢查询怎么优化
慢查询优化最怕的不是技术难,是方向错。大多数人一上来就加机器,而问题往往只是少了一条索引,或者一句 SQL 写错了。
先把慢查询日志打开
日志不开,后面全是猜。阈值设一秒还是两百毫秒,取决于业务能忍多久:电商列表页两百毫秒就该记,后台报表五秒也不算慢。
日志要带执行用户、来源地址、扫描行数。只知道"哪条语句慢",不知道"谁在什么场景下跑的",改起来没有方向,改完也不知道有没有改对。
看执行计划,不看感觉
执行计划里看四列就够:访问类型、用到的索引、预估扫描行数、额外信息。全表扫描、用错索引、临时表加文件排序,这三种都写得很直白。
我习惯把执行计划和实际耗时对一遍。计划说扫十行、实际跑三秒,问题就不在语句本身,而在锁等待或者磁盘,方向完全不同。
缺索引的三种典型
联合索引没覆盖查询条件、范围查询排在等值条件前面、排序字段没进索引。这三种在业务表上最常见,加对一条索引,往往比改代码见效快。
索引也不是越多越好。每个索引都拖慢写入、占内存,一张表超过六个索引,就该回头看看哪些是重复的,哪些其实从没被用上。
索引失效的几种写法
列上套函数、隐式类型转换、前导通配的模糊查询、用 or 连接不同列,这几种写法会让索引白建。字符串字段传数字,是最隐蔽的一种。
排查办法是对比加与不加条件的执行计划。一条语句加个看似无关的条件,就从走索引变成全表扫,问题基本就定位了。
深分页
翻到很后面的页码越来越慢,这是翻页写法的通病:先扫过前面所有行再丢掉,和索引好坏关系不大,翻得越深越明显。
改成按主键或者时间游标翻页,让数据库从上次的位置继续。这需要前端配合改"下一页"的逻辑,改完效果立竿见影,改造量也不大。
大事务和锁等待
一个事务里更新几千行,锁持有时间被拉长,后面的请求全在排队。表现是平时很快、一发批量任务就全站卡,光看慢日志还找不到问题语句。
把大事务拆小,按主键分批提交,每批几百行。锁的时间短了,并发自然回来,这比换更贵的机器有效,也更省钱。
连接池
连接池太小,请求排队等连接;太大,数据库线程切换开销上来。上限按数据库核数的几倍配,不是按应用实例数随便乘。
连接泄漏比池子小更常见。代码里有分支没归还连接,跑几天池子就满,重启就好,过几天又满,这种要看活跃连接曲线,不是看报错。
缓存不是万能
加一层缓存能挡住大部分读,但一致性和穿透要处理好。热点键过期的那一瞬间全打回数据库,能把本来正常的库压垮。
我的判断是:能靠索引解决的先改索引,缓存放在索引之后。顺序反了,缓存会掩盖慢查询,直到某天缓存失效,一起爆发。
读写分离什么时候上
读多写少、又能忍受秒级延迟的业务适合读写分离。写完立刻要读到最新数据的场景,分离之后会出现"刚下的单查不到",解释起来很费劲。
上分离之前先确认从库延迟。延迟一大,报表和列表页读到旧数据,用户感知很强,而且这类问题在监控里不一定显眼。
分表之前先想清楚
单表几千万行不是分表的理由,查询慢才是。如果慢在缺索引,分表只是把同样的慢放大到十张表上,复杂度翻倍,速度没变。
真要分,按业务主键分,别按时间分。按时间分看起来整齐,跨时间查询会变成十张表联合,复杂度全压到应用层。
硬件:内存和磁盘
数据量小于内存时,加内存最直接,热数据全在缓存里。数据远超内存,瓶颈就变成随机读,该看的是磁盘类型和每秒读写次数。
机械盘跑高并发写入,再怎么优化语句也顶不住。带本地固态盘的方案,或者干脆上 定制高配物理机,在这个阶段比调参有效。
优化后怎么验证
拿同一批语句、同样的数据量,在压测环境跑前后对比。只看单条变快没用,要看整体吞吐和尾部延迟,后者才决定用户感受。
顺便记录执行计划的变化。优化后计划从全表扫变成索引扫,才算改对了;计划没变而速度快了,多半是缓存起的作用,过一阵还会回来。
监控和基线
慢查询数量、平均耗时、锁等待时间,这三条曲线要长期留着。没有基线,你判断不了今天变慢是业务涨了还是代码改了。
大促前把基线拉出来对一遍,容量规划就有依据,不用凭感觉加机器,也不用等活动结束再后悔。
价格档位怎么对
宿迁 100G 三线 BGP 方案 的 119 到 299 那几档,内存覆盖 2G 到 32G,中小业务的库够用;宿迁高防物理机 799 起那类适合数据和 IO 都吃紧的场景,内存从 32G 起。
西安云服务器 35 到 330 元按型号分档,适合先拿一台做压测环境。数据库这种组件,买之前最好跑一遍真实查询,别只看核数和内存。
语句写法的三个习惯
只查需要的列、避免在循环里查库、把多次单条写入合成批量。这三条不涉及索引,但能砍掉大量重复往返,延迟往往比调索引降得更多。
循环查库最典型:一个列表一百行,背后一百次查询。改成一次批量取,数据库压力立刻下来,代码还更短,两边都受益。
什么时候该动架构
单表写入长期打满、分析查询和在线查询抢资源,说明这套库同时承担了两种负载。把报表迁到只读实例,是在线库最划算的一次减负。
换引擎或者拆服务是最后一步,动作大、回滚难。做之前先把慢查询清零,不然新环境里同样的问题会原样出现,等于白搬一次。
一句判断
慢查询优化的顺序是:先看计划,再改语句,然后加索引,最后才动架构和硬件。反过来做,钱花了,问题还在,只是被暂时盖住了。
本文价格取自官网公开报价页,均为起价,实际以下单页为准。