優(yōu)化SQL語句的一般步驟
通過show status 命令了解各種SQL的執(zhí)行頻率朋鞍，具體命令如下：
show [session|global] status like 'Com_%'; # 默認(rèn)是session
1. 命令顯示了當(dāng)前session 中所有統(tǒng)計(jì)參數(shù)的值惯雳，Com_xxx 表示每個(gè)xxx 語句執(zhí)行的次數(shù)朝巫，我們通常比較關(guān)心的是以下幾個(gè)統(tǒng)計(jì)參數(shù)。
- Com_select：執(zhí)行select 操作的次數(shù)石景，一次查詢只累加1劈猿。
- Com_insert：執(zhí)行INSERT 操作的次數(shù)，對(duì)于批量插入的INSERT 操作潮孽，只累加一次糙臼。
- Com_update：執(zhí)行UPDATE 操作的次數(shù)。
- Com_delete：執(zhí)行DELETE 操作的次數(shù)恩商。
2. 上面這些參數(shù)對(duì)于所有存儲(chǔ)引擎的表操作都會(huì)進(jìn)行累計(jì)。下面這幾個(gè)參數(shù)只是針對(duì)
- InnoDB 存儲(chǔ)引擎的必逆，累加的算法也略有不同怠堪。
- Innodb_rows_read：select 查詢返回的行數(shù)。
- Innodb_rows_inserted：執(zhí)行INSERT 操作插入的行數(shù)名眉。
- Innodb_rows_updated：執(zhí)行UPDATE 操作更新的行數(shù)粟矿。
- Innodb_rows_deleted：執(zhí)行DELETE 操作刪除的行數(shù)。
3. 此外损拢，以下幾個(gè)參數(shù)便于用戶了解數(shù)據(jù)庫(kù)的基本情況陌粹。
- Connections：試圖連接MySQL 服務(wù)器的次數(shù)。
- Uptime：服務(wù)器工作時(shí)間福压。
- Slow_queries：慢查詢的次數(shù)掏秩。
定位執(zhí)行效率較低的SQL 語句
1. 利用慢查詢?nèi)罩荆唧w測(cè)試過程可點(diǎn)開下面的鏈接
http://orangeholic.iteye.com/blog/1700509
2. 慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才紀(jì)錄荆姆，所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問題的時(shí)候查詢慢查
詢?nèi)罩静⒉荒芏ㄎ粏栴}蒙幻，可以使用show processlist 命令查看當(dāng)前MySQL 在進(jìn)行的線程，
包括線程的狀態(tài)胆筒、是否鎖表等邮破，可以實(shí)時(shí)地查看SQL 的執(zhí)行情況，同時(shí)對(duì)一些鎖表操
作進(jìn)行優(yōu)化。
通過explain或desc分析低效SQL 的執(zhí)行計(jì)劃
1. 通過以上步驟查詢到效率低的SQL 語句后抒和，可以通過EXPLAIN 或者DESC 命令獲取MySQL
如何執(zhí)行SELECT 語句的信息矫渔，包括在SELECT 語句執(zhí)行過程中表如何連接和連接的順序。
每個(gè)列的簡(jiǎn)單解釋如下：
- select_type：表示SELECT 的類型摧莽，常見的取值有SIMPLE（簡(jiǎn)單表庙洼，即不使用表連接或者子查詢）、PRIMARY（主查詢范嘱，即外層的查詢）送膳、UNION（UNION 中的第二個(gè)或者后面的查詢語句）、SUBQUERY（子查詢中的第一個(gè)SELECT）等丑蛤。
- table：輸出結(jié)果集的表叠聋。
-
type：表示表的連接類型，性能由好到差的連接類型為：
system（表中僅有一行受裹，即常量表）碌补。
const（單表中最多有一個(gè)匹配行，例如primary key 或者unique index）棉饶。
eq_ref（對(duì)于前面的每一行厦章，在此表中只查詢一條記錄，簡(jiǎn)單來說照藻，就是多表連接中使用primary key或者 unique index）袜啃。
ref （與eq_ref類似，區(qū)別在于不是使用primary key 或者unique index幸缕，而是使用普通的索引）群发。
ref_or_null（與ref 類似，區(qū)別在于條件中包含對(duì)NULL 的查詢）发乔、index_merge(索引合并優(yōu)化)熟妓。
unique_subquery（in的后面是一個(gè)查詢主鍵字段的子查詢）。
index_subquery（與unique_subquery 類似栏尚，區(qū)別在于in 的后面是查詢非唯一索引字段的子查詢） 起愈。
range（單表中的范圍查詢）。
index（對(duì)于前面的每一行译仗，都通過查詢索引來得到數(shù)據(jù)）抬虽。
all（對(duì)于前面的每一行，都通過全表掃描來得到數(shù)據(jù)）纵菌。 - possible_keys：表示查詢時(shí)斥赋，可能使用的索引。
- key：表示實(shí)際使用的索引产艾。
- key_len：索引字段的長(zhǎng)度疤剑。
- rows：掃描行的數(shù)量滑绒。
- Extra：執(zhí)行情況的說明和描述。
優(yōu)化SQL之索引問題
在一些情況下隘膘，在某些字段上加上了索引（注意不要在有大量重復(fù)數(shù)據(jù)的字段上加索引）疑故，sql進(jìn)行查詢時(shí)卻沒有發(fā)揮應(yīng)有的作用，進(jìn)而導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描弯菊。下面就來梳理一下這些情況：
如果條件中有or纵势，即使其中有條件帶索引也不會(huì)使用(這也是為什么盡量少用or的原因)，注意：要想使用or管钳，又想讓索引生效钦铁，只能將or條件中的每個(gè)列都加上索引。
對(duì)于多列索引才漆，不是使用的第一部分牛曹，則不會(huì)使用索引，這是因?yàn)樗饕侨∽钭笄熬Y的醇滥。
like查詢是以%開頭黎比，這也是因?yàn)樗饕侨∽钭笄熬Y的。
如果列類型是字符串鸳玩，那一定要在條件中將數(shù)據(jù)使用單引號(hào)引用起來阅虫，否則不使用索引。所以不管是什么類型不跟，都加上單引號(hào)颓帝。
查詢條件使用函數(shù)在索引列上，或者對(duì)索引列進(jìn)行運(yùn)算窝革，運(yùn)算包括(+购城，-，*聊闯，/，! 等) 米诉。
# 錯(cuò)誤的例子
select * from test where id-1 = 9;
# 正確的例子
select * from test where id = 10;
在 where 子句中使用 != 或 <> 操作符菱蔬。
在 where 子句中使用 IN 或 NOT IN，用exists 或 not exists來代替史侣。
查詢出來的表上的數(shù)據(jù)行超出表總記錄數(shù)30%拴泌，變成全表掃描。
如果mysql估計(jì)使用全表掃描要比使用索引快惊橱，則不使用索引蚪腐。
此外，查看索引的使用情況
show status like ‘Handler_read%';
大家可以注意：
handler_read_key：這個(gè)值越高越好税朴，越高表示使用索引查詢到的次數(shù)回季；
handler_read_rnd_next：這個(gè)值越高家制，說明查詢低效。
兩個(gè)簡(jiǎn)單實(shí)用的優(yōu)化SQL方法
1. 定期分析表和檢查表
- 分析表用于分析和存儲(chǔ)表的關(guān)鍵字分布泡一，分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計(jì)信息颤殴，使得SQL 能夠生成正確的執(zhí)行計(jì)劃。
- 檢查表的作用是檢查一個(gè)或多個(gè)表是否有錯(cuò)誤鼻忠。CHECK TABLE 對(duì)MyISAM 和InnoDB 表有作用涵但。
# 分析表的語法如下：
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
# 檢查表的語法如下：
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED| CHANGED}
2. 定期優(yōu)化表
# 優(yōu)化表的語法如下：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
常用SQL的優(yōu)化
1. 大批量插入數(shù)據(jù)
當(dāng)用load 命令導(dǎo)入數(shù)據(jù)的時(shí)候，適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的速度帖蔓。
-
MyISAM
對(duì)于MyISAM 存儲(chǔ)引擎的表矮瘟，可以通過以下方式快速的導(dǎo)入大量的數(shù)據(jù)。
ALTER TABLE tbl_name DISABLE KEYS;
load data infile '/home/mysql/film_test.txt' into table film_test2;
ALTER TABLE tbl_name ENABLE KEYS;
DISABLE KEYS 和ENABLE KEYS 用來打開或者關(guān)閉MyISAM 表非唯一索引的更新塑娇。在導(dǎo)入
大量的數(shù)據(jù)到一個(gè)非空的MyISAM 表時(shí)澈侠，通過設(shè)置這兩個(gè)命令，可以提高導(dǎo)入的效率钝吮。對(duì)于
導(dǎo)入大量數(shù)據(jù)到一個(gè)空的MyISAM 表埋涧，默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的，所以不用進(jìn)
行設(shè)置奇瘦。
-
InnoDB
上面是對(duì)MyISAM表進(jìn)行數(shù)據(jù)導(dǎo)入時(shí)的優(yōu)化措施棘催，對(duì)于InnoDB類型的表，這種方式并不
能提高導(dǎo)入數(shù)據(jù)的效率耳标，可以有以下幾種方式提高InnoDB表的導(dǎo)入效率醇坝。
（1）因?yàn)镮nnoDB 類型的表是按照主鍵的順序保存的，所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順
序排列次坡，可以有效地提高導(dǎo)入數(shù)據(jù)的效率呼猪。
（2）在導(dǎo)入數(shù)據(jù)前執(zhí)行SET UNIQUE_CHECKS=0，關(guān)閉唯一性校驗(yàn)砸琅，在導(dǎo)入結(jié)束后執(zhí)行
SET UNIQUE_CHECKS=1宋距，恢復(fù)唯一性校驗(yàn)，可以提高導(dǎo)入的效率症脂。
（3）如果應(yīng)用使用自動(dòng)提交的方式谚赎，建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0，關(guān)閉自
動(dòng)提交诱篷，導(dǎo)入結(jié)束后再執(zhí)行SET AUTOCOMMIT=1壶唤，打開自動(dòng)提交，也可以提高導(dǎo)入的效率棕所。
2. 優(yōu)化INSERT語句
當(dāng)進(jìn)行數(shù)據(jù)INSERT 的時(shí)候闸盔，可以考慮采用以下幾種優(yōu)化方式。
- 如果同時(shí)從同一客戶插入很多行琳省，盡量使用多個(gè)值表的INSERT 語句迎吵，這種方式將大大
縮減客戶端與數(shù)據(jù)庫(kù)之間的連接躲撰、關(guān)閉等消耗，使得效率比分開執(zhí)行的單個(gè)INSERT 語
句快(在一些情況中幾倍)钓觉。下面是一次插入多值的一個(gè)例子：
insert into test values(1,2),(1,3),(1,4)… - 如果從不同客戶插入很多行茴肥，能通過使用INSERT DELAYED 語句得到更高的速度。
DELAYED 的含義是讓INSERT 語句馬上執(zhí)行荡灾，其實(shí)數(shù)據(jù)都被放在內(nèi)存的隊(duì)列中瓤狐，并沒有
真正寫入磁盤，這比每條語句分別插入要快的多批幌；LOW_PRIORITY 剛好相反础锐，在所有其
他用戶對(duì)表的讀寫完后才進(jìn)行插入； - 將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放（利用建表中的選項(xiàng)）荧缘；
- 如果進(jìn)行批量插入皆警，可以增加bulk_insert_buffer_size 變量值的方法來提高速度，但是截粗，
這只能對(duì)MyISAM 表使用信姓； - 當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí)，使用LOAD DATA INFILE绸罗。這通常比使用很多INSERT 語
句快20 倍意推。
3. 優(yōu)化GROUP BY 語句
- 默認(rèn)情況下，MySQL 對(duì)所有GROUP BY col1珊蟀，col2....的字段進(jìn)行排序菊值。這與在查詢中指定
ORDER BY col1，col2...類似育灸。因此腻窒，如果顯式包括一個(gè)包含相同的列的ORDER BY 子句，則
對(duì)MySQL 的實(shí)際執(zhí)行性能沒有什么影響磅崭。 - 如果查詢包括GROUP BY 但用戶想要避免排序結(jié)果的消耗儿子，則可以指定ORDER BY NULL
禁止排序。禁止后用explain查看的Extra不會(huì)顯示Using filesort砸喻。
4. 優(yōu)化ORDER BY 語句
在某些情況中柔逼，MySQL 可以使用一個(gè)索引來滿足ORDER BY 子句，而不需要額外的排序恩够。WHERE 條件和ORDER BY 使用相同的索引卒落，并且ORDER BY 的順序和索引順序相同羡铲，并且ORDER BY 的字段都是升序或者都是降序蜂桶。
- 例如，下列SQL 可以使用索引也切。
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
- 但是在以下幾種情況下則不使用索引：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC扑媚；
# order by 的字段混合ASC 和DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1腰湾；
# 用于查詢行的關(guān)鍵字與ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2；
# 對(duì)不同的關(guān)鍵字使用ORDER BY：
5. 優(yōu)化嵌套查詢
MySQL4.1開始支持SQL 的子查詢疆股。這個(gè)技術(shù)可以使用SELECT 語句來創(chuàng)建一個(gè)單列的查詢結(jié)果费坊，然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中。使用子查詢可以一次性地完成很多邏輯上需要多個(gè)步驟才能完成的SQL 操作旬痹，同時(shí)也可以避免事務(wù)或者表鎖死附井，并且寫起來也很容易。但是两残，有些情況下永毅，子查詢可以被更有效率的連接（JOIN）替代。
6. MySQL如何優(yōu)化OR 條件
對(duì)于含有OR 的查詢子句人弓，如果要利用索引沼死，則OR 之間的每個(gè)條件列都必須用到索引；如果沒有索引崔赌，則應(yīng)該考慮增加索引意蛀。這點(diǎn)在索引優(yōu)化中有說到。
7. 使用SQL 提示
SQL 提示（SQL HINT）是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段健芭，簡(jiǎn)單來說就是在SQL 語句中加入一些人為的提示來達(dá)到優(yōu)化操作的目的县钥。
- 下面是一個(gè)使用SQL 提示的例子：
SELECT SQL_BUFFER_RESULTS * FROM...
這個(gè)語句將強(qiáng)制MySQL 生成一個(gè)臨時(shí)結(jié)果集。只要臨時(shí)結(jié)果集生成后吟榴，所有表上的鎖
定均被釋放魁蒜。這能在遇到表鎖定問題時(shí)或要花很長(zhǎng)時(shí)間將結(jié)果傳給客戶端時(shí)有所幫助，因?yàn)?br>
可以盡快釋放鎖資源吩翻。
- 下面是一些在MySQL 中常用的SQL 提示兜看，以下命令加在表名的后面。
1．USE INDEX（索引名）
在查詢語句中表名的后面狭瞎，添加USE INDEX 來提供希望MySQL 去參考的索引列表细移，就可
以讓MySQL 不再考慮其他可用的索引，也就是只使用這一個(gè)索引熊锭。
2．IGNORE INDEX（索引名）
如果用戶只是單純地想讓MySQL 忽略一個(gè)或者多個(gè)索引弧轧，則可以使用IGNORE INDEX 作
為HINT（提示）。
3．FORCE INDEX（索引名）
為強(qiáng)制MySQL 使用一個(gè)特定的索引碗殷，可在查詢中使用FORCE INDEX 作為HINT精绎。例如，
當(dāng)不強(qiáng)制使用索引的時(shí)候锌妻，因?yàn)閕d 的值都是大于0 的代乃，因此MySQL 會(huì)默認(rèn)進(jìn)行全表掃描，
而不使用索引，但是搁吓，當(dāng)使用FORCE INDEX 進(jìn)行提示時(shí)原茅，即便使用索引的效率不是最高，MySQL 還是選擇使用了索引堕仔。
【注】：本文的編寫主要參考《深入淺出MySQL》一書擂橘！
