SQL优化浅谈
最近的工作一直围绕着如何减轻一个老服务器数据库负载的问题展开。整个team都在寻求着更为有效的解决方案，作为项目新人的我也只能在项目中所学到的东西来做点总结，以便以后可以使用到。
现在的项目是一个产品管理系统的升级/更新项目，由于客户端所涉及到的数据繁多，所以数据表也就成为了一个大数据量的表，由于对整个系统的业务逻辑不是过于了解，所以不能判断这种设计是否存在不合理性。再点，自己也是个菜鸟，所以只能就team leader提出的解决方案的学习做点学习总结。
由于表的结构限制，以及义务相关的限制，数据库中存在一些大数据量的表，而这个数量级也是自己在校时并没有去考虑过的，现在面对如此大的数据量，确实有点不知所措。
此次需要优化的部分涉及到了后台数据库中数据的检索及提取，然后到客户端画面的显示。而更为重要的就是减轻数据库的负载，以便单个的业务操作不会占用太多的资源，而导致其余业务处理无法被满足。根据leader的暂行解决方案，对于数据库管理系统级别的优化就先不在考虑范围之内，首要是解决SQL代码中的处理，以及如何将部分运算转移至客户端，以减轻服务器数据库的负载量。
首先，要考虑的是大数据量的表，如何优化针对大数据量表的查询等操作，总结大概有一下一些。
由于数据表的数据量很大，那么在查询中遍历每条数据进行判断几乎是不可能的，所以数据在表中的结构就应该得到优化，也就是在数据存入表中，或者在表中时应该有一些为了以后查询等处理所作出的准备工作。
此类优化首选是加入索引(index)，而加入索引也并非简单的对关键字段建立索引就好。需要考虑到此次加入的索引对于以后的操作是否有消极的影响。建立索引的对象应该是极少变动或者是不变动的字段，而这些字段都应该是拥有大量重复的字段，此时的索引将会有很大的表现，查询效率也将提升不小。此处应该建立的是聚集索引，而建立的过程中应该可以明显体会到此段SQL很慢，是因为在建立聚集索引的时候，表中的数据会根据你所建立的聚集索引的相应字段来排序，而这种排序将有利于你以后对该表的查询操作。使用SQL Server中的执行计划来看，你会发现，每个表的查询都会建立一个主键索引，而这种索引执行速度很快，但却没有对查询做什么优化，如果查询语句中包含GROUP BY等时，那么在执行完主键索引的时候，还会有更具GROUP BY的一个sort步骤，而这个sort过程也会根据数据量的增加而增加，那么可以通过建立聚集索引来替代这个sort构成，虽然聚集索引的执行时间和cost将会增加，但相比之前的操作，明显有提升。
当然，索引也不是想怎么加就怎么加的，加入过多的索引，虽然在查询时提高了效率，但在对表的维护上，会投入更大的成本。比如，往表中插入数据，而此条数据刚好包含了已经建立的索引的字段，那么索引会因为你新插入了数据而重新对表中的数据进行排序，而这个排序工作将和数据量有着极为密切的关系，想想海量数据的后果吧！
再继续谈谈关于索引失效的问题。
建立了索引的字段中，是不能包含有NULL值的，因为任何包含NULL值的列都会被排除在索引之外，那么这个索引也就失去了效果，反而会占用更多的空间。所以，任何在WHERE子句中使用is null或者是is not null的语句，优化器是不允许使用索引的。
既然说到了WHERE子句，那么在WHERE子句中写的SQL语句是否对索引会有影响呢，答案当然是肯定的。比如，查询id大于10的记录时，可能会写WHERE id > 10，如果没有对id字段建立索引，那当然没什么，但如果对id字段建立了索引，那么这种写法将会使索引失效，因为根据这条语句，还是会去判断每条数据的id是否大于10，而和索引没有直接的关系。那么，如何来写才能使得索引有用呢？
此时，可以写WHERE id >= 11，貌似也没有什么特别，但是此时索引会直接找到id为11的相关记录，然后在这些记录中再去判断其他的条件，此时，效率就有明显的提升。
谈完了索引相关的小结之后，谈谈查询时数据过滤的问题。
在数据查询时，WHERE子句中的条件还有顺序之说。也就是强弱条件的区分。这种说法我倒是第一次听说，不过讲到具体的原理，应该是个很浅显易懂的东西。
作为查询条件，那么它最为直观的功能就是过滤掉那些“没用”的数据。而这个过滤就是这个问题的关键。如果一个查询条件加在那里，但却没有过滤掉一条数据，那么这种条件叫做弱条件；反之，如果一个条件可以过滤掉一大半或者90%以上的数据，那么这个条件就是强条件。就因为过滤的条件有强弱之分，所以它们的顺序也至关重要。
强过滤条件应该首先被执行，那么之后的比对次数就会减少很多了，效率就是从这些小细节中体现出来的。如何让强条件先执行呢？就是把它放在WHERE子句的最后就是了，也就是说，弱条件紧跟WHERE关键字，而越是强的条件越靠后，则它们就会被先执行（具体是因为什么，也许是SQL语句解析器的原因，也没再深究，如果大家有了解，可以告诉我）。
再呢，其实就是SQL代码的优化，把不必要的处理都去掉，比如使用过多的临时表，虽然在某种角度来讲，可以提高可与读性，但读与数据库来说，那是个不小的负荷，所以在可能的前提下，将那些没有必要的代码统统去掉。
先将这么多吧，很少写总结，估计不是很好，多多提意见及建议。
另外，从网上找到些关于SQL优化的原则：作者：yashi 来源：赛迪网
2024年5月17日 22:14
This is a magnificent article, Given such an exceptional measure of information in it, These kind of articles keeps the clients fervor for the site, and continue sharing more ... positive conditions . I can’t believe focusing long enough to research; much less write this kind of article. You’ve outdone yourself with this material without a doubt. It is one of the greatest contents . I really loved reading your blog
2024年6月11日 19:19
Attractive section of content. I just stumbled upon your weblog and in accession capital to assert t
2024年6月11日 19:20
I found your this post while searching for some related information on blog search...Its a good post..keep posting and update the information.
2024年6月11日 19:20
You completed certain reliable points there. I did a search on the subject and found nearly all persons will agree with your blog.
2024年6月11日 19:21
Reward frequently most important great stuff -- you will have an understanding of all of the meal around
2024年6月11日 19:21
You completed certain reliable points there. I did a search on the subject and found nearly all persons will agree with your blog.
2024年6月11日 19:23
ProvaDent Healthy Gums and Teeth is the revolutionary product that will change your oral health routine! With organic xylitol, this amazing innovation acts as a natural sweetener, supports a balanced oral microbiome, and keeps your breath fresh all day.
2024年6月11日 19:26
Construction jobs are one field that provides great opportunity to many individuals who want to possess a stable source of income and great career. Construction management position, for example, is these days one of the popular occupations many like to pu
2024年6月11日 19:28
I’m wondering if you have noticed how the media has changed? What once seemed like a never discussed issue has become more prevelant. It’s that time to chagnge our stance on this though.
2024年6月11日 19:29
After study some of the blogs on your web site now, and that i really such as your technique of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls consider my web site at the same time and make me aware what you consider.
2024年6月11日 19:30
While i offer for you to your ex simply highly rated in partnership with trusted specifics, as a result understand the object:
2024年6月11日 19:30
Construction jobs are one field that provides great opportunity to many individuals who want to possess a stable source of income and great career. Construction management position, for example, is these days one of the popular occupations many like to pursue owing to the various benefits it provides. But despite the promising career and better income opportunity, a shortage in this field has been reported.
2024年6月11日 19:31
Construction jobs are one field that provides great opportunity to many individuals who want to possess a stable source of income and great career. Construction management position, for example, is these days one of the popular occupations many like to pursue owing to the various benefits it provides. But despite the promising career and better income opportunity, a shortage in this field has been reported.
2024年6月11日 19:32
Paying internet Writing Jobs is an internet platform that helps you find a well-paying online writing job that you can do from home, even if you have no experience. After completing a 1-minute questionnaire on the official Paying Online Writing Jobs website, you can choose the ideal form of Online Writing work for your specific profile.
2024年6月11日 19:33
reading your articles. This is truly a great read for me. I have book
2024年6月11日 20:08
Java Burn weight loss supplement is a strong, all-natural superfood mix made for weight management. It helps reduce weight by burning subcutaneous fat. The formula is easy to add to your daily routine and only uses natural ingredients to work effectively.
2024年6月11日 20:09
I am hoping the same best effort from you in the future as well. In fact your creative writing skills has inspired me.
2024年6月11日 20:10
I am not really good with English but I find this rattling easygoing to read .
2024年6月11日 20:10
While i offer for you to your ex simply highly rated in partnership with trusted specifics, as a result understand the object:
2024年6月11日 20:11
I’m so happy to read this. This is the kind of manual that needs to be given and not the accidental misinformation that’s at the other blogs. Appreciate your sharing this best doc.
2024年6月11日 20:12
When you request a moon reading, our powerful program generates a 3D model of the solar system on your birthday and calculates the orbits and phases of the moon and planets. We take this data and combine it with our extensive collection of astrological knowledge to generate your own bespoke moon reading that is entirely unique to you!
2024年6月11日 20:12
SeroLean is a natural dietary supplement that optimizes natural brain hormones that aid in weight loss by regulating food consumption. This weight loss medication has a unique blend of components that have been clinically proved to boost the synthesis of brain neurotransmitters
2024年6月11日 20:13
a scientifically researched dietary supplement, is a new option for efficient weight loss. PuraVive, inspired by a critical study that revealed a frequent shortage in brown adipose tissue (BAT) among persons battling with weight management, serves as a light of hope for those seeking to accomplish long-term weight loss. PuraVive, led by the brain behind its formulation, takes advantage of the robust powers of natural components known for their ability to stimulate the creation of brown adipose tissue in the body. PuraVive catalyzes a massive increase in the body’s metabolic rate by boosting the presence of this metabolically active tissue, kicking off a dramatic path toward a better weight profile.
2024年6月11日 20:13
Enter the 1win bonus code MAXVIP500 when signing up to receive a generous 500% bonus on your initial four deposits. Check out our 1win review and sign up now! The 1Win welcome bonus will be added to your account once you have a positive balance. Remember to fulfill the bonus wagering requirements before making a withdrawal. Discover how to receive a welcome offer of up to $1025 by using the 1win promo code MAXVIP500. New players can benefit from a 500% deposit bonus when creating an account. The 1Win Bonus offers a generous 500% match on deposits, giving you the chance to earn up to INR 75,000 in bonus funds. The bonus is spread out over the first four deposits, with different bonus percentages for each deposit: 1st deposit — 500% bonus. Some of the top games to enjoy at 1win Casino are Blackjack and Roulette. Blackjack is a fan-favorite with its exciting gameplay, while Roulette offers a thrilling experience for players. Don't forget to check out the 1Win Express Bonus and the 1Win Leaderboard Prize for extra fun and rewards! Remember, online gambling is legal in India, so feel free to sign up and start playing at 1win Casino today. With a Curacao gaming license, you can trust that your gaming experience is safe and secure.
2024年6月11日 20:14
I studied all these years only now to find out that your site is so much interesting information:) You can treat it as a compliment from my side
2024年6月11日 20:14
There can be a tremendous amount within this putting up i usually may not incorporate searched into by myself. Your articles makes it possible for readers components to consider from the valuable strategy.
2024年6月11日 20:15
Wow what a Great Information about World Day its extremely pleasant instructive post. a debt of gratitude is in order for the post.
2024年6月11日 20:16
Wow what a Great Information about World Day its extremely pleasant instructive post. a debt of grat
2024年6月11日 20:17
I have read all the comments and suggestions posted by the visitors for this article are very fine,We will wait for your next article so only.Thanks!