做站十年踩坑总结：新手也能上手的mysql优化实用技巧
去年帮一个做流量站的朋友排查问题，页面打开半分钟出不来，服务器负载直接飙到10，重启都不管用。查来查去，居然是他放在首页的热门文章列表查询没加索引，每次打开都全表扫十万条数据。改完之后，页面秒开。负载直接掉到0.3。
就这么夸张。
先搞定索引——90%的慢查询都是索引没玩对
很多新手刚学mysql，觉得加索引不就是点一下的事？我刚做站那会也这么想，直到踩了大坑，卡了大半个月，流量掉了一半才反应过来。
不是加了索引就一定有用。
很多人写模糊搜索，习惯给关键词加前后百分号，比如`where title like '%mysql优化%'`，这种写法，就算你给title加了索引，它也用不了。直接全表扫描，数据量上去了肯定卡。
https://obgeo.oss-cn-beijing.aliyuncs.com/pvc-articles/f628ba68-0521-472c-8d0a-f4d09f379391.jpg
mysql索引失效场景对比表
那联合索引呢？很多人喜欢给每个查询条件单独加索引，觉得这样覆盖全，其实错得离谱。mysql每次查询一般只会走一个索引，多个单列索引还不如一个正确的联合索引好用。
联合索引一定要遵守最左前缀原则，把区分度高的字段放在最前面。比如你要按分类和时间查文章，大部分查询都是先筛分类再看时间，那分类放前面，时间放后面。别搞反了。
还有一个误区：索引越多越好。我见过一个表，一共12个字段，加了8个索引。插入一条数据要更新8个索引，写性能直接掉成渣。你用不到的索引，赶紧删掉。真的。
干掉不合理的SQL——别让垃圾语句拖垮你的服务器
说实话，我看了那么多新手写的SQL，80%都能随手挑出毛病。
最常见的就是图省事写`select *`。你明明只需要id、标题、发布时间三个字段，非要把整个表的字段都查出来，不仅多占带宽内存，还会让本来可以用上的覆盖索引失效，本来走索引就能拿到数据，现在还要回表去取整行数据，速度能不慢吗？
养成习惯，用到什么字段查什么，没坏处。
然后就是大分页问题。做内容站、电商站的肯定都碰到过，翻到后面几十上百页，查询速度越来越慢。你原来写的肯定是`limit 100000, 10`对吧？mysql要扫描前100000条数据再扔掉，只留后面10条，能不慢吗？
https://obgeo.oss-cn-beijing.aliyuncs.com/pvc-articles/c0636ac8-eb8d-40ed-b090-5e3048aedca0.jpg
mysql大分页优化前后速度对比
解决方法其实很简单，用延迟关联优化，先走索引拿到符合条件的主键ID，再用ID关联原表拿需要的字段。就这一个小改动，我之前把一句1.2秒的查询改成了0.01秒，客户当场给我发了两百块红包买咖啡。
还有那种嵌套了好几层的子查询，能改成join就改。很多旧版本的mysql，子查询优化做的很差，明明可以很快，它给你跑半天。
不过话说回来，也不是说子查询完全不能用，简单的子查询没问题，太复杂的就换写法试试。
配置和架构的小调整，效果远超出预期
https://obgeo.oss-cn-beijing.aliyuncs.com/pvc-articles/4d622e4c-a05b-449f-83e7-a5d9eab9eebe.jpg
配置和架构的小调整，效果远超出预期
很多人上线项目，直接用服务器默认安装的mysql配置，一点不改就跑。那默认配置是给低配置机器留的，你现在拿个4核8G的服务器跑站，innodb_buffer_pool_size还是默认的128M，能不卡吗？
这个参数是给innodb缓存索引和数据用的，越大，走内存的请求就越多，磁盘IO就越少，速度就越快。如果你的服务器只跑mysql，直接给物理内存的70%就好，比如8G内存给5G，完全没问题。就改这么一个配置，重启完你就能感觉到速度快了不少。
还有，慢查询日志一定要打开。别嫌占那点硬盘空间，真出问题了，你全靠它帮你定位哪条SQL慢。我现在不管搭什么站，第一件事就是打开慢查询日志，设置超过1秒的查询就记录下来，定期扫一遍，有问题早点改，别等用户卡的退站了你才发现。
如果你已经做到这里，站的日活也上来了，过万了，那可以考虑搞读写分离。大部分站都是读多写少，80%以上的压力都来自读请求，把读请求扔给从库，主库只处理写请求，压力一下就下来了。
不过话说回来，如果你只是一个日活几千的小站，真没必要上来就搞读写分离分库分表这些。先把索引、SQL、基础配置调好，性能翻个三五倍完全没问题，过度优化就是浪费时间，还容易出莫名其妙的bug。
我这些年做站，碰到的mysql性能问题99%都是前面说的几个小问题，根本不是什么架构不够高级，就是基础细节没做好。 已经推荐给朋友来看了，好东西就应该一起分享一起学习。 感谢楼主无私分享经验，少走了很多弯路，对我们帮助特别大。 每一条都很实用，已经记下来了，以后遇到类似问题就能用上。 不管是内容还是排版都很用心，看得出来楼主花了不少时间，必须支持。 看完楼主的分享感觉很有收获，感谢这么用心的整理，学到很多新知识。 楼主态度很认真，回复也很耐心，这样的楼主值得大家支持。 支持楼主继续更新，这么好的内容值得让更多人看到和学习。 内容写得很详细，逻辑也很清晰，对新手来说非常友好，支持一下楼主。 思路很清晰，步骤也很详细，跟着操作应该不会出什么问题。 认真看完了整篇内容，感觉受益匪浅，期待楼主后续更多优质的分享。 楼主的经历很有参考价值，给了我很多新的思考方向，非常感谢。 说得很客观中立，没有偏激言论，理性讨论就该是这个样子。 帖子内容很扎实，不浮夸不炒作，真正有用的信息都在里面。 看了这么多帖子，还是觉得你这篇最实在，条理清晰又容易理解。 虽然篇幅不长，但句句都是重点，简洁又有深度，非常不错。 楼主考虑得很全面，不仅讲了方法，还提到了注意事项，非常贴心。 这样用心的帖子不多见，必须顶上去让更多人看到优质内容。 支持理性讨论，反对无脑争吵，楼主带了个很好的头。
页:
[1]
2