公布MySQL查询的功能优化

最近由权威调查机构Evans数据公司进行的一项调查显示，MySQL在过去两年已经获得了25%的市场份额。该调查公司还预测，相比其他的开源数据库和闭源数据库。　查询是数据库手艺中最经常使用的操纵。查询操纵的历程对照复杂，起首从客户端收回查询的SQL语句，数据库服务端在吸收到由客户端发来的SQL语句后，实行这条SQL语句，然后将查询到的了局前往给客户端。固然历程很复杂，但分歧的查询体例和数据库设置，对查询的功能将会有很在的影响。

　　因而，本文就在MySQL中经常使用的查询优化手艺举行会商。会商的内容如：经由过程查询缓冲进步查询速率;MySQL对查询的主动优化;基于索引的排序;不成达查询的检测和利用各类查询选择来进步功能。

　　1、经由过程查询缓冲进步查询速率

　　一样平常我们利用SQL语句举行查询时，数据库服务器每次在收到客户端发来SQL后，城市实行这条SQL语句。但当在必定距离内(如1分钟内)，接到完整一样的SQL语句，也一样实行它。固然如许能够包管数据的及时性，但在年夜多半时分，数据其实不请求完整的及时，也就是说能够有必定的延时。假如是如许的话，在短工夫内实行完整一样的SQL就有些得失相当。

　　幸亏MySQL为我们供应了查询缓冲的功效(只能在MySQL4.0.1及以上版本利用查询缓冲)。我们能够经由过程查询缓冲在必定水平上进步查询功能。

　　我们能够经由过程在MySQL安装目次中的my.ini文件设置查询缓冲。设置也十分复杂，只必要将query_cache_type设为1便可。在设置了这个属性后，MySQL在实行任何SELECT语句之前，城市在它的缓冲区中查询是不是在不异的SELECT语句被实行过，假如有，而且实行了局没有过时，那末就间接取查询了局前往给客户端。但在写SQL语句时注重，MySQL的查询缓冲是辨别巨细写的。以下列的两条SELECT语句:SELECT*fromTABLE1

　　SELECT*FROMTABLE1

　　下面的两条SQL语句关于查询缓冲是完整分歧的SELECT。并且查询缓冲其实不主动处置空格，因而，在写SQL语句时，应只管削减空格的利用，特别是在SQL首和尾的空格(由于，查询缓冲其实不主动截取首尾空格)。

　　固然不设置查询缓冲，偶然大概带来功能上的丧失，但有一些SQL语句必要及时地查询数据，大概其实不常常利用(大概一天就实行一两次)。如许就必要把缓冲关了。固然，这能够经由过程设置query_cache_type的值来封闭查询缓冲，但这就将查询缓冲永世地封闭了。在MySQL5.0中供应了一种能够一时封闭查询缓冲的办法:SELECTSQL_NO_CACHEfield1,field2FROMTABLE1

　　以上的SQL语句因为利用了SQL_NO_CACHE，因而，不论这条SQL语句是不是被实行过，服务器都不会在缓冲区中查找，每次城市实行它。

　　我们还能够将my.ini中的query_cache_type设成2，如许只要在利用了SQL_CACHE后，才利用查询缓冲。SELECTSQL_CALHE*FROMTABLE1

　2、MySQL对查询的主动优化

　　索引关于数据库长短常主要的。在查询时能够经由过程索引来进步功能。但偶然利用索引反而会下降功能。我们能够看以下的SALES表:CREATETABLESALES

　　(

　　IDINT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,

　　NAMEVARCHAR(100)NOTNULL,

　　PRICEFLOATNOTNULL,

　　SALE_COUNTINTNOTNULL,

　　SALE_DATEDATENOTNULL,

　　PRIMARYKEY(ID)，

　　INDEX(NAME)，

　　INDEX(SALE_DATE)

　　)

　　假定这个表中保留了数百万条数据，而我们要查询商品号为1000的商品在2004年和2005年的均匀代价。我们能够写以下的SQL语句:SELECTAVG(PRICE)FROMSALES

　　WHEREID=1000ANDSALE_DATEBETWEEN2004-01-01AND2005-12-31;

　　假如这类商品的数目十分多，差未几占了SALES表的纪录的50%或更多。那末利用SALE_DATE字段上索引来盘算均匀数就有些慢。由于假如利用索引，就得对索引举行排序操纵。当满意前提的纪录十分多时(如占全部表的纪录的50%或更多的比例)，速率会变慢，如许还不如对全部表举行扫描。因而，MySQL会主动依据满意前提的数据占全部表的数据的比例主动决意是不是利用索引举行查询。

　　关于MySQL来讲，上述的查询了局占全部表的纪录的比例是30%摆布时就不利用索引了，这个比例是MySQL的开辟职员依据他们的履历得出的。但是，实践的比例值会依据所利用的数据库引擎分歧而分歧。

　　3、基于索引的排序

　　MySQL的缺点之一是它的排序。固然MySQL能够在1秒中查询约莫15,000笔记录，但因为MySQL在查询时最多只能利用一个索引。因而，假如WHERE前提已占用了索引，那末在排序中就不利用索引了，这将年夜年夜下降查询的速率。我们能够看看以下的SQL语句:SELECT*FROMSALESWHERENAME=“name”ORDERBYSALE_DATEDESC;

　　在以上的SQL的WHERE子句中已利用了NAME字段上的索引，因而，在对SALE_DATE举行排序时将不再利用索引。为懂得决这个成绩，我们能够对SALES表创建复合索引:ALTERTABLESALESDROPINDEXNAME,ADDINDEX(NAME,SALE_DATE)

　　如许再利用上述的SELECT语句举行查询时速率就会年夜副提拔。但要注重，在利用这个办法时，要确保WHERE子句中没有排序字段，在上例中就是不克不及用SALE_DATE举行查询，不然固然排序快了，可是SALE_DATE字段上没有独自的索引，因而查询又会慢上去。

4、不成达查询的检测

　　在实行SQL语句时，不免会碰到一些必假的前提。所谓必假的前提是不管表中的数据怎样变更，这个前提都为假。如WHEREvalue<100ANDvalue>200。我们永久没法找到一个既小于100又年夜于200的数。

　　假如碰到如许的查询前提，再往实行如许的SQL语句就是画蛇添足。幸亏MySQL能够主动检测这类情形。如我们能够看看以下的SQL语句:SELECT*FROMSALESWHERENAME=“name1”ANDNAME=“name2”

　　以上的查询语句要查找NAME既即是name1又即是name2的纪录。很分明，这是一个不成达的查询，WHERE前提必定是假。MySQL在实行SQL语句之前，会先剖析WHERE前提是不是是不成达的查询，假如是，就不再实行这条SQL语句了。为了考证这一点。我们起首对以下的SQL利用EXPLAIN举行测试:EXPLAINSELECT*FROMSALESWHERENAME=“name1”

　　下面的查询是一个一般的查询，我们能够看到利用EXPLAIN前往的实行信息数据中table项是SALES。这申明MySQL对SALES举行操纵了。再看看上面的语句:EXPLAINSELECT*FROMSALESWHERENAME=“name1”ANDNAME=“name2”

　　我们能够看到，table项是空，这申明MySQL并没有对SALES表举行操纵。

　　5、利用各类查询选择来进步功能

　　SELECT语句除一般的利用外，MySQL还为我们供应了良多能够加强查询功能的选项。如下面先容的用于把持查询缓冲的SQL_NO_CACHE和SQL_CACHE就是个中两个选项。在这一部分，我将先容几个经常使用的查询选项。

　　1.STRAIGHT_JOIN:强迫毗连按次

　　当我们将两个或多个表毗连起来举行查询时，我们其实不用体贴MySQL先连哪一个表，后连哪一个表。而这统统都是由MySQL外部经由过程一系列的盘算、评价，最初得出的一个毗连按次决意的。以下列的SQL语句中，TABLE1和TABLE2其实不必定是谁毗连谁:SELECTTABLE1.FIELD1,TABLE2.FIELD2FROMTABLE1,TABLE2WHERE…

　　假如开辟职员必要工资地干涉毗连的按次，就得利用STRAIGHT_JOIN关头字，以下列的SQL语句:SELECTTABLE1.FIELD1,TABLE2.FIELD2FROMTABLE1STRAIGHT_JOINTABLE2WHERE…

　　由下面的SQL语句可知，经由过程STRAIGHT_JOIN强制MySQL按TABLE1、TABLE2的按次毗连表。假如你以为按本人的按次比MySQL保举的按次举行毗连的效力高的话，就能够经由过程STRAIGHT_JOIN来断定毗连按次。

　　2.干涉索引利用，进步功能

　　在下面已提到了索引的利用。一样平常情形下，在查询时MySQL将本人决意是不是利用索引，利用哪个索引。但在一些特别情形下，我们但愿MySQL只利用一个或几个索引，大概不但愿利用某个索引。这就必要利用MySQL的把持索引的一些查询选项。

　　限定利用索引的局限

　　偶然我们在数据内外创建了良多索引，当MySQL对索引举行选择时，这些索引都在思索的局限内。但偶然我们但愿MySQL只思索几个索引，而不是全体的索引，这就必要用到USEINDEX对查询语句举行设置。SELECT*FROMTABLE1USEINDEX(FIELD1,FIELD2)…

　　从以上SQL语句能够看出，不管在TABLE1中已创建了几个索引，MySQL在选择索引时，只思索在FIELD1和FIELD2上创建的索引。


　限定不利用索引的局限

　　假如我们要思索的索引良多，而不被利用的索引又很少时，可使用IGNOREINDEX举行反向拔取。在下面的例子中是选择被思索的索引，而利用IGNOREINDEX是选择不被思索的索引。SELECT*FROMTABLE1IGNOREINDEX(FIELD1,FIELD2)…

　　在下面的SQL语句中，TABLE1表中只要FIELD1和FIELD2上的索引不被利用。

　　强制利用某一个索引

　　下面的两个例子都是给MySQL供应一个选择，也就是说MySQL其实不必定要利用这些索引。而偶然我们但愿MySQL必需要利用某一个索引(因为MySQL在查询时只能利用一个索引，因而只能强制MySQL利用一个索引)。这就必要利用FORCEINDEX来完成这个功效。SELECT*FROMTABLE1FORCEINDEX(FIELD1)…

　　以上的SQL语句只利用创建在FIELD1上的索引，而不利用别的字段上的索引。

　　3.利用一时表供应查询功能

　　当我们查询的了局会合的数据对照多时，能够经由过程SQL_BUFFER_RESULT.选项强迫将了局集放光临时表中，如许就能够很快地开释MySQL的表锁(如许别的的SQL语句就能够对这些纪录举行查询了)，而且能够长工夫地为客户端供应年夜纪录集。SELECTSQL_BUFFER_RESULT*FROMTABLE1WHERE…

　　和SQL_BUFFER_RESULT.选项相似的另有SQL_BIG_RESULT，这个选项一样平常用于分组或DISTINCT关头字，这个选项关照MySQL，假如有需要，就将查询了局放光临时表中，乃至在一时表中举行排序。SELECTSQL_BUFFER_RESULTFIELD1,COUNT(*)FROMTABLE1GROUPBYFIELD1

　　6、结论

　　在程序计划中一样存在一个“二八准绳”，即20%的代码用往了80%的工夫。数据库使用程序的开辟亦然。数据库使用程序的优化，重点在于SQL的实行效力。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据和按次读页而不长短按次读页。
通过支付一定费用，客户可以得到优先的24/7支持，访问内容丰富的在线知识库和联系一个专门的技术负责经理。

所以你总能得到相应的升级版本，来满足你的需求。

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

你可以简单地认为适合的就是好，不适合就是不好。

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

仓酷云

我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！

对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

是要和操作系统进行Socket通讯的场景。否则建议慎重！

总感觉自己还是不会SQL