日常工作中常见的mysql优化技能

1.介绍一下MYSQL常用的优化技巧.MySQL 自带 slow log 的分析工具 mysqldumpslow ，但是没有说明。本文通过分析该脚本，介绍了其用法。slow log 是 MySQL 根据 SQL 语句的执行时间设定，写入的一个文件，用于分析执行较慢的语句。只要在 my.cnf 文件中配置好：log-slow-queries = [slow_query_log_filename]即可记录超过默认的 10s 执行时间的 SQL 语句。如果要修改默认设置，可以添加：long_query_time = 5设定为 5s 。如果要记录所有 SQL 语句，可以写入：log-long-format# t=time, l=lock time, r=rows# at, al, 以及 ar 是对应的平均值mysqldumpslow 可以接受的参数有：‘v+’, # verbose‘d+’, # debug‘s=s’, # 排序 (t, at, l, al, r, ar etc)‘r!’, # 倒排序 (largest last instead of first)‘t=i’, # 显示最高的 n 个查询‘a!’, # 不把所有的数字以 N ，字符串以 ‘S’ 显示‘n=i’, # abstract numbers with at least n digits within names‘g=s’, # grep: only consider stmts that include this string‘h=s’, # hostname of db server for *-slow.log filename (can be wildcard)‘i=s’, # name of server instance (if using mysql.server startup script)‘l!’, # don’t subtract lock time from total timemysql explain的使用说明explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了：如：explain select surname,first_name form a,b where a.id=b.id分析结果形式如下：引用mysql> explain SELECT * FROM `whisper` WHERE to_id = 6696 AND del = 0 AND whisper=0 ORDER BY `send_time` DESC LIMIT 4;+—-+————-+———+——+—————+——-+———+——-+——+—————————–+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+———+——+—————+——-+———+——-+——+—————————–+| 1 | SIMPLE | whisper | ref | to_id | to_id | 4 | const | 1 | Using where; Using filesort |+—-+————-+———+——+—————+——-+———+——-+——+—————————–+1 row in set (0.00 sec)EXPLAIN列的解释：table 显示这一行的数据是关于哪张表的type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALLpossible_keys 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句key 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好ref 显示索引的哪一列被使用了，如果可能的话，是一个常数rows MYSQL认为必须检查的用来返回请求数据的行数Extra 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢extra 列返回的描述的意义Distinct 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了Not exists MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了Range checked for eachRecord（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一Using filesort 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）system 表只有一行：system表。这是const连接类型的特殊情况const 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待eq_ref 在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用ref 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好range 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况index 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）ALL 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免mysql常用的hint（原创）mysql常用的hint对于经常使用oracle的朋友可能知道，oracle的hint功能种类很多，对于优化sql语句提供了很多方法。同样，在mysql里，也有类似的hint功能。下面介绍一些常用的。[b]强制索引 FORCE INDEX[/b]SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。[b]忽略索引 IGNORE INDEX[/b]SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。[b]关闭查询缓冲 SQL_NO_CACHE[/b]SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。[b]强制查询缓冲 SQL_CACHE[/b]SELECT SQL_CALHE * FROM TABLE1;如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。[b]优先操作 HIGH_PRIORITY[/b]HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。SELECT HIGH_PRIORITY * FROM TABLE1;[b]滞后操作 LOW_PRIORITY[/b]LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。update LOW_PRIORITY table1 set field1= where field1= …[b]延时插入 INSERT DELAYED[/b]INSERT DELAYED INTO table1 set field1= …INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有 空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增 的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。[b]强制连接顺序 STRAIGHT_JOIN[/b]SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。[b]强制使用临时表 SQL_BUFFER_RESULT[/b]SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。[b]分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT[/b]SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。查询是数据库技术中最常用的操作。查询操作的过程比较简单，首先从客户端发出查询的SQL语句，数据库服务端在接收到由客户端发来的 SQL语句后， 执行这条SQL语句，然后将查询到的结果返回给客户端。虽然过程很简单，但不同的查询方式和数据库设置，对查询的性能将会有很在的影响。因此，本文就在MySQL中常用的查询优化技术进行讨论。讨论的内容如：通过查询缓冲提高查询速度；MySQL对查询的自动优化；基于索引的排序；不可达查询的检测和使用各种查询选择来提高性能。一、 通过查询缓冲提高查询速度一般我们使用SQL语句进行查询时，数据库服务器每次在收到客户端发来SQL后，都会执行这条SQL语句。但当在一定间隔内(如1分钟内)，接到完 全一样的SQL语句，也同样执行它。虽然这样可以保证数据的实时性，但在大多数时候，数据并不要求完全的实时，也就是说可以有一定的延时。如果是这样的 话，在短时间内执行完全一样的SQL就有些得不偿失。幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲。设置也非常简单，只需要将query_cache_type设为1即可。在设 置了这个属性后，MySQL在执行任何SELECT语句之前，都会在它的缓冲区中查询是否在相同的SELECT语句被执行过，如果有，并且执行结果没有过 期，那么就直接取查询结果返回给客户端。但在写SQL语句时注意，MySQL的查询缓冲是区分大小写的。如下列的两条SELECT语句:1. SELECT * from TABLE12.3. SELECT * FROM TABLE1上面的两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为，查询缓冲并不自动截取首尾空格)。虽然不设置查询缓冲，有时可能带来性能上的损失，但有一些SQL语句需要实时地查询数据，或者并不经常使用(可能一天就执行一两次)。这样就需要把 缓冲关了。当然，这可以通过设置query_cache_type的值来关闭查询缓冲，但这就将查询缓冲永久地关闭了。在MySQL 5.0中提供了一种可以临时关闭查询缓冲的方法:1. SELECT SQL_NO_CACHE field1, field2 FROM TABLE1以上的SQL语句由于使用了SQL_NO_CACHE，因此，不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。我们还可以将my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。1. SELECT SQL_CALHE * FROM TABLE1二、MySQL对查询的自动优化索引对于数据库是非常重要的。在查询时可以通过索引来提高性能。但有时使用索引反而会降低性能。我们可以看如下的SALES表:1. CREATE TABLE SALES2.3. (4.5. ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,6.7. NAME VARCHAR(100) NOT NULL,8.9. PRICE FLOAT NOT NULL,10.11. SALE_COUNT INT NOT NULL,12.13. SALE_DATE DATE NOT NULL,14.15. PRIMARY KEY(ID)，16.17. INDEX (NAME)，18.19. INDEX (SALE_DATE)20.21. );假设这个表中保存了数百万条数据，而我们要查询商品号为1000的商品在2004年和2005年的平均价格。我们可以写如下的SQL语句:SELECT AVG(PRICE) FROM SALESWHERE ID = 1000 AND SALE_DATE BETWEEN ’2004-01-01′ AND ’2005-12-31′;如果这种商品的数量非常多，差不多占了SALES表的记录的50%或更多。那么使用SALE_DATE字段上索引来计算平均数就有些慢。因为如果使 用索引，就得对索引进行排序操作。当满足条件的记录非常多时(如占整个表的记录的50%或更多的比例)，速度会变慢，这样还不如对整个表进行扫描。因 此，MySQL会自动根据满足条件的数据占整个表的数据的比例自动决定是否使用索引进行查询。对于MySQL来说，上述的查询结果占整个表的记录的比例是30%左右时就不使用索引了，这个比例是MySQL的开发人员根据他们的经验得出的。然而，实际的比例值会根据所使用的数据库引擎不同而不同。三、 基于索引的排序MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果WHERE条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。我们可以看看如下的SQL语句:1. SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;在以上的SQL的WHERE子句中已经使用了NAME字段上的索引，因此，在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题，我们可以对SALES表建立复合索引:1. ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时，要确保WHERE子句中没有排序字段，在上例中就是不能用SALE_DATE进行查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的索引，因此查询又会慢下来。四、 不可达查询的检测在执行SQL语句时，难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化，这个条件都为假。如WHERE value < 100 AND value > 200。我们永远无法找到一个既小于100又大于200的数。如果遇到这样的查询条件，再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:1. SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显，这是一个不可达的查询，WHERE条件一定是假。MySQL在执行 SQL语句之前，会先分析WHERE条件是否是不可达的查询，如果是，就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用 EXPLAIN进行测试:1. EXPLAIN SELECT * FROM SALES WHERE NAME = “name1”上面的查询是一个正常的查询，我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:1. EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”我们可以看到，table项是空，这说明MySQL并没有对SALES表进行操作。五、 使用各种查询选择来提高性能SELECT语句除了正常的使用外，MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分，我将介绍几个常用的查询选项。1. STRAIGHT_JOIN:强制连接顺序当我们将两个或多个表连接起来进行查询时，我们并不用关心MySQL先连哪个表，后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估，最后得出的一个连接顺序决定的。如下列的SQL语句中，TABLE1和TABLE2并不一定是谁连接谁:1. SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE …如果开发人员需要人为地干预连接的顺序，就得使用STRAIGHT_JOIN关键字，如下列的SQL语句:1. SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。2. 干预索引使用，提高性能在上面已经提到了索引的使用。一般情况下，在查询时MySQL将自己决定是否使用索引，使用哪一个索引。但在一些特殊情况下，我们希望MySQL只使用一个或几个索引，或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。限制使用索引的范围有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USE INDEX对查询语句进行设置。1. SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …从以上SQL语句可以看出，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。限制不使用索引的范围如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。1. SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。强迫使用某一个索引上面的两个例子都是给MySQL提供一个选择，也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。1. SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。3. 使用临时表提供查询性能当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。1. SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。1. SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1