介绍一下MYSQL常用优化技巧

介绍一下MYSQL常用的优化技巧MySQL自带slowlog的剖析工具mysqldumpslow，可是没有说明。本文经过剖析该脚本，介绍了其用法。slowlog是MySQL依据SQL语句的履行时间设定，写入的一个文件，用于分析履行较慢的语句。只要在f文件中配置好：log-slow-queries=[slow_query_log_filename]即可记录超出默认的10s履行时间的SQL语句。假如要改正默认设置，能够增添：long_query_time=5设定为5s。假如要记录所有SQL语句，可以写入：log-long-format#t=time,l=locktime,r=rows#at,al,以及ar是对应的均匀值mysqldumpslow能够接受的参数有：‘v+’,#verbose‘d+’,#debug‘s=s’,#排序(t,at,l,al,r,aretc)‘r!’,#倒排序(largestlastinsteadoffirst)‘t=i’,#显示最高的n个查问‘a!’,#不把所有的数字以N，字符串以‘S’显示‘n=i’,#abstractnumberswithatleastndigitswithinnames‘g=s’,#grep:onlyconsiderstmtsthatincludethisstring‘h=s’,#hostnameofdbserverfor*-slow.logfilename(canbewildcard)‘i=s’,#nameofserverinstance(ifusingmysql.serverstartupscript)‘l!’,#don’tsubtractlocktimefromtotaltimemysqlexplain的使用说明explain显示了mysql怎样使用索引来办理select语句以及连结表。能够帮助选择更好的1索引和写出更优化的查问语句。使用方法，在select语句前加上explain就能够了：如：explainselectsurname,first_nameforma,bwherea.id=b.id剖析结果形式以下：引用mysql>explainSELECT*FROM`whisper`WHEREto_id=6696ANDdel=0ANDwhisper=0ORDERBY`send_time`DESCLIMIT4;+—-+————-+———+——+—————+——-+———+——-+——+—————————–+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+—-+————-+———+——+—————+——-+———+——-+——+—————————–+|1|SIMPLE|whisper|ref|to_id|to_id|4|const|1|Usingwhere;Usingfilesort|+—-+————-+———+——+—————+——-+———+——-+——+—————————–+1rowinset(0.00sec)EXPLAIN列的解说：table显示这一行的数据是对于哪张表的type这是重要的列，显示连结使用了何种种类。从最好到最差的连结种类为const、eq_reg、ref、range、indexhe和ALLpossible_keys显示可能应用在这张表中的索引。假如为空，没有可能的索引。能够为有关的域从WHERE语句中选择一个适合的语句key实质使用的索引。假如为NULL，则没有使用索引。极少的状况下，MYSQL会选择优化不足的索引。这类状况下，能够在SELECT语句中使用USEINDEX（indexname）来强迫使用一个索引或许用IGNOREINDEX（indexname）来强迫MYSQL忽视索引key_len使用的索引的长度。在不损失精准性的状况下，长2度越短越好ref显示索引的哪一列被使用了，假如可能的话，是一个常数rowsMYSQL认为一定检查的用来返回恳求数据的行数Extra对于MYSQL怎样分析查问的额外信息。将在表4.3中议论，但这里能够看到的坏的例子是Usingtemporary和Usingfilesort，意思MYSQL根本不可以使用索引，结果是检索会很慢extra列返回的描绘的意义Distinct一旦MYSQL找到了与行相结合般配的行，就不再搜寻了NotexistsMYSQL优化了LEFTJOIN，一旦它找到了般配LEFTJOIN标准的行，就不再搜寻了RangecheckedforeachRecord（indexmap:#）没有找到理想的索引，所以对于以前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连结之一Usingfilesort看到这个的时候，查问就需要优化了。MYSQL需要进行额外的步骤来发现怎样对返回的行排序。它依据连结种类以及储存排序键值和般配条件的所有行的行指针来排序所有行Usingindex列数据是从只是使用了索引中的信息而没有读取实质的行动的表返回的，这发生在对表的所有的恳求列都是同一个索引的部分的时候Usingtemporary看到这个的时候，查问需要优化了。这里，MYSQL需要创立一个暂时表来储存结果，这往常发生在对不一样的列集进行ORDERBY上，而不是GROUPBY上Whereused使用了WHERE从句来限制哪些行将与下一张表般配或许是返回给用户。假如不想返回表中的所有行，而且连结种类ALL或index，这就会发生，或许是查问有问题不一样连结种类的解说（依据效率高低的次序排序）system表只有一行：system3表。这是const连结种类的特别状况const表中的一个记录的最大值能够般配这个查问（索引能够是主键或唯一索引）。因为只有一行，这个值实质就是常数，因为MYSQL先读这个值而后把它当成常数来对待eq_ref在连结中，MYSQL在查问时，以前面的表中，对每一个记录的结合都从表中读取一个记录，它在查问使用了索引为主键或唯一键的所有时使用ref这个连结种类只有在查问使用了不是唯一或主键的键或许是这些种类的部分（比方，利用最左侧前缀）时发生。对于以前的表的每一个行结合，所有记录都将从表中读出。这个种类严重依靠于依据索引般配的记录多少—越少越好range这个连结种类使用索引返回一个范围中的行，比方使用>或200。我们永久没法找到一个既小于100又大于200的数。假如碰到这样的查问条件，再去履行这样的SQL语句就是画蛇添足。幸亏MySQL能够自动检测这类状况。如我们能够看看以下的SQL语句:1.SELECT*FROMSALESWHERENAME=“name1”ANDNAME=“name2”以上的查问语句要查找NAME既等于name1又等于name2的记录。很显然，这是一个不行达的查问，WHERE条件必定是假。MySQL在履行SQL语句以前，会先剖析WHERE条件是不是不行达的查问，假如是，就不再履行这条SQL语句了。为了考证这一点。我们第一对以下的SQL使用EXPLAIN进行测试:1.EXPLAINSELECT*FROMSALESWHERENAME=“name1”上边的查问是一个正常的查问，我们能够看到使用EXPLAIN返回的履行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下边的4语句:1.EXPLAINSELECT*FROMSALESWHERENAME=“name1”ANDNAME=“name2”我们能够看到，table项是空，这说明MySQL并无对SALES表进行操作。五、使用各样查问选择来提升性能SELECT语句除了正常的使用外，MySQL还为我们供给了好多能够加强查问性能的选项。如上边介绍的用于控制查问缓冲的SQL_NO_CACHE和SQL_CACHE就是此中两个选项。在这一部分，我将介绍几个常用的查问选项。1.STRAIGHT_JOIN:强迫连结次序当我们将两个或多个表连结起来进行查问时，我们其实不用关怀MySQL先连哪个表，后连哪个表。而这全部都是由MySQL内部通过一系列的计算、评估，最后得出的一个连结次序决定的。以以下的SQL语句中，TABLE1和TABLE2其实不必定是谁连结谁:1.SELECTTABLE1.FIELD1,TABLE2.FIELD2FROMTABLE1,TABLE2WHERE假如开发人员需要人为地干涉连结的次序，就得使用STRAIGHT_JOIN重点字，以以下的SQL语句:1.SELECTTABLE1.FIELD1,TABLE2.FIELD2FROMTABLE1STRAIGHT_JOINTABLE2WHERE由上边的SQL语句可知，经过STRAIGHT_JOIN逼迫MySQL按TABLE1、TABLE2的次序连结表。假如你认为按自己的次序比MySQL介绍的次序进行连结的效率高的话，就能够经过STRAIGHT_JOIN来确立连结次序。2.干涉索引使用，提升性能在上边已经提到了索引的使用。一般状况下，在查问时MySQL将自己决定能否使用索引，使用哪一个索引。但在一些特别状况下，我们希望MySQL只使用一个或几个索引，或许不希望使用某个索引。这就5需要使用MySQL的控制索引的一些查问选项。限制使用索引的范围有时我们在数据表里成立了好多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是所有的索引，这就需要用到USEINDEX对查问语句进行设置。1.SELECT*FROMTABLE1USEINDEX(FIELD1,FIELD2)从以上SQL语句能够看出，不论在TABLE1中已经成立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上成立的索引。限制不使用索引的范围假如我们要考虑的索引好多，而不被使用的索引又极少时，能够使用IGNOREINDEX进行反向选用。在上边的例子中是选择被考虑的索引，而使用IGNOREINDEX是选择不被考虑的索引。1.SELECT*FROMTABLE1IGNOREINDEX(FIELD1,FIELD2)在上边的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。逼迫使用某一个索引上边的两个例子都是给MySQL供给一个选择，也就是说MySQL其实不必定要使用这些索引。而有时我们希望MySQL一定要使用某一个索引(因为MySQL在查问时只好使用一个索引，所以只好逼迫MySQL使用一个索引)。这就需要使用FORCEINDEX来达成这个功能。1.SELECT*FROMTABLE1FORCEINDEX(FIELD1)以上的SQL语句只使用成立在FIELD1上的索引，而不使用其余字段上的索引。3.使用暂时表供给查问性能当我们查问的结果集中的数据比许多时，能够经过SQL_BUFFER_RESULT选.项强迫将结果集放来暂时表中，这样就能够很快地开释MySQL的表锁(这样其余的SQL语句就能够对这些