优化杭州某著名电子商务网站高并发千万级大型数据库经验之- SQL语句优化

      昨天晚上看探索栏目，深海捕捞帝王蟹；在遥远的阿拉斯加，捕捞船若捞上来的是母蟹会全部重新放到海里，每个人手上拿了一个尺子，若尺寸没达标的公蟹会重新放到大海里，邪恶的美国你为什么这么强大、我愿意当个幸福的母蟹、但是千万不要把我生在邪恶的东海，曾经从来没想移民的愿望，看了这期探险节目后，更加懂了什么叫爱护环境爱护地球了。我们的东海别说螃蟹，好像连虾米都被电死得差不多了干得竟都是断子绝孙的事儿，邪恶的美帝你太强大了。希望我们不要成为人类的害虫。 

      我们可以无知，但是不能愚昧，不能干太多断子绝孙的事情，保护我们生存环境从你我做起。

      好久没写博客了，一方面是日常工作繁忙，另外一方面是想更多的时间陪陪家里人，享受春天的美好时光，还在写一本《程序员，你伤不起》的一本书要由人民邮电出版社出版；我的性格可能也跟大多数程序员类似吧，没什么兴趣爱好、不擅长与人交流、平时话也少、也不够幽默，唯一的优点就是一个实实在在。

下图命名为：孤独的程序员

     其实真正优化信息化系统的核心，还是要靠优化那些SQL语句，当然顶尖高手写的SQL语句没多少可优化的余地，毕竟不是人人都是工作10年以上的资深软件开发人员，有时候由于忙、事情紧急、或者没充分考虑业务逻辑，或者根本没想到网站从一个无名小站变成行业出名的站点。平时事情也是千头万绪只要服务器在能抗得过就没怎么关注吧。

     最近我看了主服务器的SQL语句耗时比较多，就没能忍住想折腾几下，也是由于视频处理相关的工作遇到了一些难题无法继续进行下去了，就想换个事情干干，然后思路清晰了再回来弄视频处理的事情。

     sql语句的优化主要是解决一下几个问题：

     1：由于白天服务的访问量大，只能去侦测一些耗时比较严重的sql语句，一般一天能检查出30-50个比较耗时的SQL语句，然后对业务逻辑进行对比分析。例如有LEN函数就非常耗时，因为每条记录都计算一遍，几十万条记录这么算一下，非常卡，改成 is not null 或者 <>'' 之类的，效率会高很多。还有一些已经废弃掉的 where id > 500000, 等等曾经为了优化数据库写的一些过滤条件现在反而变成了一些累赘。

     还有一些日期处理函数，尽量能用getdate()的，都把日期字符替换掉，例如超过24小时等等的相关处理逻辑等等，还就就是有些order by Id等等语句也非常耗时，能不order by的就去掉order by，能让程序处理的尽量让程序处理。

     系统里有些业务逻辑是每1分钟处理一次逻辑，每次处理逻辑服务器就被镇痛、性能被拖垮一下；类似这样的处理又跟金钱有关系，每小时处理上万的业务费用。若是程序改错了，就很可能需要进行赔偿，所以修改这部分程序心里承受的压力非常大，需要考虑很多因素；我把这个处理程序修改为5分钟调用一次，一次处理30笔业务，经过这么修改后服务器就变得更加顺畅了，不用每分钟都被折腾一下，同时把30笔业务处理的sql语句也进行了惊心的优化，耗费的性能甚至比1分钟1次的还要高。

    系统里还有一个最核心的存储过程，就是处理系统的交易费用的，这个存储过程我前后看了1-2天，优化了3次，把一些没必要写在事务里的，可以用更好的函数替代的，都进行了优化，经过3才优化后，这个存储过程的性能也有了很大提升。

    还有一些 count(*) 之类的细微的也都修改为 count(1) 等等，还有为了减少并发锁等的开支，还用了 with(nolock) ，但是效果不是很明显；原因是我们主机的内存太大，CPU太多，主机的性能太好了。

    SELECT TOP 1，判断是否存在的修改为 IF EXISTS，因为有几百万条以上的记录了，这个应该也有性能上的改进。还有一些细节改进就懒得弄了，下次有机会再改进一次，不过改进的余地不多了，这次改进得很满意了。

    白天可以找最耗时的SQL语句，晚上可以找所有常用的SQL语句清单，然后把这些SQL语句逐一进行优化改进，甚至打印出来，一条条进行核对。毛估估应该优化了接近200条SQL语句。

 

    当然打印整理每个耗时sql语句的同时进行了索引的优化，索引的优化比较靠谱的还是要靠懂业务逻辑，能抓住业务处理的核心逻辑，然后有针对性的进行优化，同时把所有的索引都进行一次梳理、当然时间不充裕时只能抓重点表的索引。

    1：索引太多了，导致占用的空间多，插入一条记录时会非常缓慢，插入记录时缓慢了，导致整个系统的业务逻辑处理缓慢。

    2：索引太少了，查询语句执行效率慢，也会导致整个系统的运行性能缓慢。

    3：每删除一条索引，特别是大表的索引，可能会耗时半个小时左右，创建一个索引最严重时需要2个小时，少则半个小时，若系统在正常处理数据时， 整个系统会进入恶性循环整个网站会崩溃，所以只能到深夜12点后进行优化，一般一晚上没多少后悔的机会，白天整理好思路，头绪都整理好，然后一晚上一般能优化好3-4个表的索引，或者核心的3-4个索引。要命的晚上也不能把网站关闭了，也需要正常运转才可以，因为深夜里也有业务在进行处理。

   其实漫长的深夜是最孤单的，一个人在空空荡荡冷冷清清的办公室里，心情焦虑的执行一个个优化索引的SQL语句，还没办法用可视工具进行操作，因为都会超时崩溃。早上6-7点开始系统就进入正常的业务处理状态了，也不能在7点后进行大动作，一口接一口的喝茶，一杯又一杯的，早上天亮了，还不敢回家休息，因为怕索引建错了，或者有什么情况发生整个网站崩溃了，责任重大，只有到接近10-11点才敢回家睡觉。下午还要看看工作QQ，有没有重大问题反馈。

   4：由于网站的并发性太大了，导致数据库的默认值都会变成系统性能的瓶颈，所以大数据并发的尽量少用数据库的默认值设置，当然小并发下是看不出问题的。

   5：把所有的大表都进行分区，诊断索引的使用效率，把业务最核心表的索引降低到最低，甚至有些后台调用得索引建立在新服务器上，只有前台需要的索引建立在主服务器上，这样核心业务表的索引又减少了30%-40%左右，索引少了后，插入语句的效率就高了，整个系统前台的运行效率又提高了很多。下图是通过一些报表来查看各种系统的核心参数。

   有一点比较遗憾的是，对数据进行了分区后，性能没能等到提升，很可能是由于主服务器的内存太大了，CPU太多了，分区没能改进性能，反而有些环节分区还降低了性能，而且比较明显，索引的大小也变了大很多，但是还是坚持分区了，对大数据进行分区应该没什么坏事。

   比较糟糕的一次是，对表进行分区结果分错了，又把分区去掉，重新分区，足足耗费了4个小时多才恢复正常，艾玛、折腾大数据伤不起啊。

  

  

   6： 对一些页面进行缓存处理，例如以前的程序是每点击一次执行一次，有些没必要每时每刻都读取的参数修改为每10分钟或者每5分钟读取一次等等，来减轻主服务器的压力。

   7： 有些统计信息，从单独的统计信息表读取，由于有些表有几千万条记录，执行一次统计几乎个主服务器带来的打击是刚刚的，所以把这些统计语句进行一些统计信息表，然后每天晚上12点后，写个处理脚本程序进行定期更新。然后页面上再做一些缓存处理，这样给主服务器的压力也少了很多了。

   8:  连续好几个晚上加班也不现实，有些不是很重要的，很关键的数据库处理可以编写好脚本，然后凌晨4-5点服务器最不忙的时候进行处理，第2天早上再看脚本任务的运行情况是否正常，这个方法也很不错，没必要深夜蹲点了，毕竟对身体的损害很大，还是正常上班时间好好工作就可以了。

   9： 还有几个关键的技术要点就是如何看死锁，什么语句在死锁状态；如何杀死那些死锁进程；哪些对象资源在进入阻塞；如何单用户模式对服务器进行处理操作，加快一些无法撤退的处理；如何用远程服务器上的数据库来更新本地数据库，当然是直接写SQL语句来进行2个服务器之间的update, 数据核对等等操作。简短的几篇文章无法面面都全，先只能写到这个程度了。

  10： 其实还有一个技术无关的问题就是，如何说服程序员按你的修正意图进行调整优化，哪个人都不想改程序，能不改就不改，说服别人去修正程序也是耗费很多精力的，毕竟每个人手上都有很多事情在干，如何把优先级提升，还要看平时的同事关系相处得如何。

     一般优化自己设计开发的系统都是相对容易的事情，去动别人维护多年的程序，能跟程序的负责人沟通协调好，说服负责人支持你的工作，也是需要一定的难度的，人家凭什么要听你的，你弄坏了责任谁承担，现在还有很多事情需要处理，凭什么优先要优化服务器？有必要吗？老板让你优化了吗？问题想复杂了也就复杂，想简单了也是很简单的事情。抱着不害人、不吭人，认真负责的心态，都是为了大家好对吧，都是为了公司的服务器更强劲、公司的网站打开速度更快、各种业务流程更顺畅、我们为什么不优化数据库服务器呢，对吧。

   这几年有一个小小的总结，每写一年博客，每年年收入就会增加10万，连续写了5年博客后，很开心了，所以将来有空还会坚持写博客，谁愿意打击就打击吧，对我来说各种打击只是挠痒痒一样，无所谓了，已经成了习惯了，我继续当我的流氓程序员，大家开心生活才是最重要。 

   其实写文章比空想需要更强大的能力，能通顺的写出一篇文章让别人有所收获是一种贡献；若这篇文章能对你有所帮助，请不要吝啬点击“推荐”，点一下推荐不收费、不费力、可以鼓励读者继续写出更好的文章分享给大家，不需要钞票，只求一个不费力的推荐鼓励。

   工作一上午大家累了吧，来点儿福利，调整好心态，下午继续战斗。