日常工作中常见的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 time

mysql 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和ALL
possible_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 each
Record（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 TABLE1
2.
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 SALES
2.
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 SALES
WHERE 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


1.介绍一下MYSQL常常使用的优化技能.
MySQL 自带 slow log 的剖析工具 m