優(yōu)化 SQL 語句的一般步驟

通過 show status 命令了解各種 SQL 的執(zhí)行頻率

MySQL 客戶端連接成功后，通過 show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息似芝，也可以在操作系統(tǒng)上使用 mysqladmin extended-status 命令獲得這些消息侨把。show [session|global] status 可以根據(jù)需要加上參數(shù) session 或者 global 來顯示 session 級（當(dāng)前連接）的統(tǒng)計(jì)結(jié)果和 global級（自數(shù)據(jù)庫上次啟動至今）的統(tǒng)計(jì)結(jié)果浮入。如果不寫，默認(rèn)使用的參數(shù)是 session

show status like 'com_%';

返回的結(jié)果 com_xxx 表示每個 xxx 語句執(zhí)行的次數(shù)，通過比較關(guān)心的是 com_select，com_insert喷众，com_update，com_delete

上面這些參數(shù)對于所有存儲引擎的表操作都會進(jìn)行累計(jì)紧憾。下面這幾個參數(shù)只是針對 innoDB 存儲引擎的到千，累加的算法也略有不同

show status like 'InnoDB_rows%';

通過以上返回的幾個參數(shù)，可以很容易的了解當(dāng)前數(shù)據(jù)庫的應(yīng)用是以插入更新為主還是以查詢操作為主赴穗，以及各種類型的 SQL 大致的執(zhí)行比例是多少憔四。對于更新操作的技術(shù)膀息，是對執(zhí)行次數(shù)的技術(shù)，不論提交還是回滾都會進(jìn)行累加

對于事務(wù)性的應(yīng)用了赵，通過 com_commit 和 com_rollback 可以了解事務(wù)提交和回滾的情況潜支，對于回滾操作非常頻繁的數(shù)據(jù)庫，可能意味著應(yīng)用編寫存在問題

此外柿汛，以下幾個參數(shù)便于用戶了解數(shù)據(jù)庫的基本情況

• connections：試圖連接 MySQL 服務(wù)器的次數(shù)
• uptime：服務(wù)器工作時間
• slow_queries：慢查詢的次數(shù)

定位執(zhí)行效率較低的 SQL 語句

• 通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 SQL 語句 用 --log-slow-queries[=file_name] 選項(xiàng)啟動時冗酿，mysqld 寫一個包含所有執(zhí)行時間超過 long_query_time 秒的 SQL 語句的日志文件
• 慢查詢?nèi)罩驹诓樵兘Y(jié)束之后才記錄，所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問題的時候查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}络断，可以使用 show processlist 命令查看當(dāng)前 MySQL 在進(jìn)行的線程已烤，包括線程的狀態(tài)，是否鎖表等妓羊，可以實(shí)時的查看 SQL 的執(zhí)行情況胯究，同時對一些鎖表操作進(jìn)行優(yōu)化

通過 explain 分析低效 SQL 的執(zhí)行計(jì)劃

通過以上步驟查詢到效率低的 SQL 語句后，可以通過 explain 或者 desc 命令獲取 MySQL 如何執(zhí)行 select 語句的信息躁绸。包括在 select 語句執(zhí)行過程中表如何連接和連接的順序

exlain select * from tablename;

簡單說明以下返回的參數(shù)：

• select_type：表示 select 類型裕循，常見的取值有 simple（簡單表，即不用表連接或者子查詢）净刮，primary（主查詢剥哑，即外層的查詢），union（union 中的第二個或者后面的查詢語句）淹父，subquery（子查詢中第一個 select）等
• table：輸出結(jié)果集的表
• type：表示 MySQL 在表中找到所需行的方式株婴，或者叫訪問類型，常見的類型有 all暑认，index困介，range，ref蘸际，eq_ref座哩，const / system，null粮彤，左右到右根穷，性能由最差到最好
  • all：全表掃描，MySQL 遍歷全表來找到匹配的行
  • index：索引全掃描导坟，遍歷整個索引來查詢匹配的行
  • range：索引范圍掃描屿良，常見與 <，<=惫周，>尘惧，>=，between 等操作符
  • ref：使用非唯一索引掃描或唯一索引的前綴掃描闯两，返回匹配某個單獨(dú)值的記錄行
  • eq_ref：類似 ref褥伴，區(qū)別就在使用的索引是唯一的索引谅将，對于每個索引鍵值，表中只有一條記錄匹配重慢；簡單來說饥臂，就是多表連接中使用 primary key 或者 unique index 作為關(guān)聯(lián)條件
  • const / system：單表中最多有一個匹配行，查詢起來非常迅速似踱，所以這個匹配行中的其他列值可以被優(yōu)化器在當(dāng)前查詢中當(dāng)作常量來處理
  • null：不用訪問表或者索引就能直接得到結(jié)果
• possible_keys：表示查詢時可能使用的索引
• key：表示實(shí)際使用的索引
• key_len：使用索引字段的長度
• rows：掃描行的數(shù)量
• extra：執(zhí)行情況的說明和描述隅熙，包含不適合在其他列中顯示但是對執(zhí)行計(jì)劃非常重要的額外信息

explain extended 命令可以獲取更詳細(xì)的信息，配合 show warnings` 查看警告

explain extended select * from xixi;

explain partitions 命令查看 SQL 所訪問的分區(qū)

explain partitions select * from xixi;

通過 show profile 分析 SQL

通過 have_profiling 參數(shù)核芽，能夠查看當(dāng)前 MySQL 是否支持 profile

select @@have_profiling;

默認(rèn) profiling 是關(guān)閉的囚戚，可以通過 set 語句 在 session 級別開啟 profiling

select @@profilinng;
set profiling=1;

執(zhí)行一個查詢后，通過 show profiles 語句查看 query_id 和消耗的時間

通過 show profile for query query_id 語句能夠查看執(zhí)行過程中線程的每個狀態(tài)消耗的時間

在獲取了最消耗時間的線程狀態(tài)后轧简，還可以進(jìn)一步選擇 all驰坊，cpu，block io哮独，context拳芙，switch，page faults 等明細(xì)類型來查看 MySQL 在使用什么資源上耗費(fèi)了過高的時間

show profile cpu for query 1;

還可以通過 show profile source for query 查看 SQL 解析執(zhí)行過程中每個步驟對應(yīng)的源碼文件皮璧，函數(shù)名以及具體的源文件行數(shù)

通過 trace 分析優(yōu)化器如何選擇執(zhí)行計(jì)劃

使用方式：首先打開 trace舟扎，設(shè)置格式為 JSON，設(shè)置 trace 最大能夠使用的內(nèi)存大小悴务，避免解析過程中因?yàn)槟J(rèn)內(nèi)存過小而不能夠完整顯示

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_men_size=1000000;

先執(zhí)行一個查詢睹限，然后檢查 information_schema.optimizer_trace 就可以知道 MySQL 是如何執(zhí)行 SQL 的

select * from information_schema.optimizer_trace \G;

索引問題

索引的存儲分類

索引是在 MySQL 的存儲引擎層中實(shí)現(xiàn)的，而不是在服務(wù)器層實(shí)現(xiàn)的讯檐。所以每種存儲引擎的索引都不一定完全相同羡疗，也不是所有的存儲引擎都支持所有的索引類型

• B-Tree 索引：最常見的索引類型，大部分引擎都支持 B 樹索引
• hash 索引：只有 memory 引擎支持
• R-Tree 索引（空間索引）：空間索引是 MyISAM 的一個特殊索引類型裂垦，主要用于地理空間數(shù)據(jù)類型
• Full-text（全文索引）

MySQL 目前不支持函數(shù)索引顺囊，但是能對列的前面某一部分進(jìn)行索引，這個特性可以大大縮小索引文件的大小蕉拢，但前綴索引的缺點(diǎn)是在排序 order by 和分組 group by 操作的時候無法使用

create index indexname on tablename(col(10))

MySQL 如何使用索引

B-Tree 索引是最常見的索引，構(gòu)造類似二叉樹诚亚，能夠根據(jù)鍵值提供一行或者一個行集的快速訪問晕换，通常只需要很少的讀操作就可以找到正確的行。不過站宗，需要注意 B-Tree 索引中的 B 不代表二叉樹（binary）闸准，而是代表平衡樹（balanced）。B-Tree 索引并不是一顆二叉樹

MySQL 中能夠使用索引的典型場景

• 匹配全值（Match the full value）梢灭，對索引中的所有列都指定具體值夷家，即是對索引中的所有列都有等值匹配的條件
• 匹配值的范圍查詢（Match a range of values）蒸其，對索引的值能夠進(jìn)行范圍查找
• 匹配最左前綴（Match a leftmost prefix），僅僅使用索引中的最左邊列進(jìn)行查找
• 僅僅對索引進(jìn)行查詢（Index only query）库快，當(dāng)查詢的列都在索引的字段中時摸袁，查詢的效率更高
• 匹配列前綴（Match a column prefix），僅僅使用索引中的第一列义屏，并且只包含索引第一列的開頭一部分進(jìn)行查找
• 能夠?qū)崿F(xiàn)索引匹配部分精確而其他部分進(jìn)行范圍匹配（Match one part exactly and match a range on another part）
• 如果列名是索引靠汁，那么使用 column_name is null 就會使用索引
• Index Condition Pushdown （ICP）特性，進(jìn)一步優(yōu)化查詢

存在索引當(dāng)不能使用索引的典型場景

• 以 % 開頭的 like 查詢不能夠利用 B-Tree 索引
• 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的時候也不會使用索引闽铐，特別是當(dāng)列類型是字符串蝶怔，那么一定記得在 where 條件中把字符常量值用引號引起來，否則即便這個列上有索引兄墅，MySQL 也不會用到踢星，因?yàn)?MySQL 默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索
• 復(fù)合索引的情況下，假如查詢條件不包含索引列最左邊部分隙咸，即不滿足最左原則 leftmost沐悦，是不會使用復(fù)合索引的
• 如果 MySQL 估計(jì)使用索引比全表掃描更慢，則不使用索引
• 用 or 分割開的條件扎瓶，如果 or 前的條件中的列有索引所踊，而后面的列中沒有索引，那么涉及的索引都不會被用到

查看索引使用情況

show status like 'handler_read%';

如果索引正在工作概荷，handler_read_key 的值將很高秕岛，這個值代表了一個行被索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高误证，因?yàn)樗饕⒉唤?jīng)常使用

handler_read-rnd_next 的值高則意味著查詢運(yùn)行低效继薛，并且應(yīng)該建立索引補(bǔ)救。這個值的含義是在數(shù)據(jù)文件中讀下一行的請求數(shù)愈捅。如果正進(jìn)行大量的表掃描遏考，handler_read_rnd_next 的值較高，則通常說明表索引不正確或?qū)懭氲牟樵儧]有利用索引

兩個簡單實(shí)用的優(yōu)化方法

定期分析表和檢查表

分析表的語法如下：

analyze [local|no_write_to_binlog] table tablename[,tablename]...

本語句用于分析和存儲表的關(guān)鍵字分布蓝谨，分析的救過將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計(jì)信息灌具，使得 SQL 能夠生成正確的執(zhí)行計(jì)劃。如果用戶感覺實(shí)際執(zhí)行計(jì)劃并不是預(yù)期的執(zhí)行計(jì)劃譬巫，執(zhí)行一次分析表可能會解決問題咖楣。在分析期間，使用一個讀取鎖定對表進(jìn)行鎖定

檢查表的語法如下：

check table tablename[,tablename]... [option]... option={quick|fast|medium|extended|changed}

檢查表的作用是檢查一個或多個表是否有錯誤

定期優(yōu)化表

優(yōu)化表的語法如下：

optimize [local|no_write_to_binlog] table tablename[,tablename]...

如果已經(jīng)刪除了表的一大部分芦昔，或者如果已經(jīng)對含有可變長度行的表進(jìn)行了很多更改诱贿，則應(yīng)使用 optimize table 命令來進(jìn)行表優(yōu)化。這個命令可以將表中的空間碎片進(jìn)行合并，并且可以消除由于刪除或者更新造成的空間浪費(fèi)

常用 SQL 的優(yōu)化

大批量插入數(shù)據(jù)

當(dāng)用 load 命令導(dǎo)入數(shù)據(jù)的時候珠十，適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的速度

對于 MyISAM 存儲引擎的表料扰，可以通過以下方式快速的導(dǎo)入大量的數(shù)據(jù)

alter table tablename disable keys;
loading the data
alter table tablename enable keys;

disable keys 和 enable keys 用來打開或者關(guān)閉 MyISAM 表非唯一索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個非空的 MyISAM 表時焙蹭，通過設(shè)置這兩個命令晒杈，可以提高導(dǎo)入的效率。對于導(dǎo)入大量數(shù)據(jù)到一個空的 MyISAM 表壳嚎，默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的桐智，所有不用進(jìn)行設(shè)置

提高 InnoDB 表的導(dǎo)入效率

1. 因?yàn)?InnoDB 類型的表是按照主鍵的順序保存的，所以將導(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ǎo)入前執(zhí)行 set autocommit=0寡润，關(guān)閉自動提交捆憎，導(dǎo)入結(jié)束后再執(zhí)行 set autocommit=1，打開自動提交梭纹，也可以提高導(dǎo)入的效率

優(yōu)化 insert 語句

• 如果同時從同一客戶插入很多行躲惰，應(yīng)盡量使用多個值表的 insert 語句，這種方式將大大縮減客戶端與數(shù)據(jù)庫之間的連接变抽，關(guān)閉等消耗础拨，使得效率比分開執(zhí)行的單個 insert 語句快

    insert into tablename values(1,2),(3,4)...
  

• 如果從不同客戶插入很多行，可以通過使用 insert delayed 語句得到更高的速度绍载。delayed 的含義是讓 insert 語句馬上執(zhí)行诡宗，其實(shí)數(shù)據(jù)都被放在內(nèi)存的隊(duì)列中，并沒有真正寫入磁盤击儡，這比每條語句分別插入要快得多塔沃；low_priority 剛好相反，在所有其他用戶對表的讀寫完成后才進(jìn)行插入

• 將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放（利用建表中的選項(xiàng)）

• 如果進(jìn)行批量插入阳谍，可以通過增加 bulk_insert_buffer_size 變量值的方法來提高速度蛀柴，但是，只能對 MyISAM 表使用

• 當(dāng)從一個文本文件裝載一個表時矫夯，使用 load date infile名扛。這通常比使用很多 insert 語句快 20 倍

優(yōu)化 order by 語句

MySQL 中有兩種排序方式

• 第一種通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種方式在使用 explain 分析查詢的時候顯示為 using index茧痒，不需要額外的排序，操作效率較高
• 第二種是通過對返回?cái)?shù)據(jù)進(jìn)行排序融蹂，也就是通常說的 filesort 排序旺订，所有不是通過索引直接俄返回排序結(jié)果的排序都叫 filesort 排序弄企。filesort 并不代表通過磁盤文件進(jìn)行排序，而只是說明進(jìn)行了一個排序操作区拳，至于排序操作是否使用了磁盤文件或臨時表等拘领，則取決于 MySQL 服務(wù)器對排序參數(shù)的設(shè)置和需要排序數(shù)據(jù)的大小

了解了 MySQL排序的方式，優(yōu)化目標(biāo)就清晰了：盡量減少額外的排序樱调，通過索引直接返回有序數(shù)據(jù)约素。where 條件和 order by 使用相同的索引，并且 order by 的順序和索引順序相同笆凌，并且 order by 的字段都是升序或者都是降序圣猎。否則肯定需要額外的排序操作，這樣就會出現(xiàn) filesort

filesort 的優(yōu)化

filesort 有兩種算法

• 兩次掃描算法：首先根據(jù)條件去除排序字段和行指針信息乞而，之后在排序區(qū) sort buffer 中排序送悔。如果排序區(qū) sort buffer 不夠，則在臨時表 temporary table 中存儲排序結(jié)果爪模。完成排序后根據(jù)行指針回表讀取記錄
• 一次掃描算法：一次性取出滿足條件的行的所有字段欠啤，然后在排序區(qū) sort buffer 中排序后直接輸出結(jié)果集。排序的時候內(nèi)存開銷比較大屋灌，但是排序效率比兩次掃描算法要高

MySQL 通過比較系統(tǒng)變量 max_lenth_for_sort_data 的大小和 query 語句取出的字段總大小來判斷使用哪種排序算法洁段。如果 max_length_for_sort_data 更大，那么使用第二種優(yōu)化之后的算法共郭；否則使用第一種算法

適當(dāng)加大系統(tǒng)變量 max_length_for_sort_data 的值祠丝，能夠讓 MySQL 選擇更優(yōu)化的 filesort 排序算法。當(dāng)然落塑，加入 max_length_for_sort_data 設(shè)置過大纽疟，會造成 CPU 利用率過低和磁盤 I/O 過高，CPU 和 I/O 利用平衡就足夠了

適當(dāng)加大 sort_buffer_size 排序區(qū)憾赁，盡量讓排序在內(nèi)存中完成污朽，而不是通過創(chuàng)建臨時表放在文件中進(jìn)行；當(dāng)然也不能無限制加大 sort_buffer_size 排序區(qū)龙考，因?yàn)?sort_buffer_size 參數(shù)是每個線程獨(dú)占的蟆肆，設(shè)置過大，會導(dǎo)致服務(wù)器 SWAP 嚴(yán)重晦款，要考慮數(shù)據(jù)庫活動連接數(shù)和服務(wù)器內(nèi)存的大小來適當(dāng)設(shè)置排序區(qū)

盡量只是用必要的字段炎功，select 具體的字段名稱，而不是 select * 選擇所有字段缓溅，這樣可以減少排序區(qū)的使用蛇损，提高 SQL 性能

優(yōu)化 group by 語句

默認(rèn)情況下，MySQL 對所有的 group by col1,col2,... 的字段進(jìn)行排序。這與在查詢中指定 order by col1,col2,... 類似淤齐。因此股囊，如果顯示包括一個包含相同列的 order by 子句，則對 MySQL 的實(shí)際執(zhí)行性能沒什么影響

如果查詢包括 group by 但用戶想要避免排序結(jié)果的消耗更啄，則可以指定 order by null 禁止排序

優(yōu)化嵌套查詢

子查詢可以被更有效率的連接（join）替代稚疹，連接之所以更有效率一些，是因?yàn)?MySQL 不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上需要兩個步驟的查詢工作

MySQL 如何優(yōu)化 OR 條件

對于含有 or 的查詢子句祭务，如果要利用索引内狗，則 or 之間的每個條件列都必須用到索引；如果沒有索引义锥，則應(yīng)該考慮增加索引

優(yōu)化分頁查詢

• 在索引上完成排序分頁的操作柳沙，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容
• 把 limit 查詢轉(zhuǎn)換成某個位置的查詢

使用 SQL 提示

SQL 提示（SQL hint）是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說就是在 SQL語句中加入一些人為的提示來達(dá)到優(yōu)化操作的目的

select sql_buffer_result * from...

這個語句將強(qiáng)制 MySQL 生成一個臨時結(jié)果集缨该。只要臨時結(jié)果集生成后偎行，所有表上的鎖定均被釋放

在查詢語句中表名的后面，添加 use index 來提供希望 MySQL 去參考的索引列表贰拿，就可以讓 MySQL 不在考慮其他可用的索引

添加 ignore index 讓 MySQL 忽略一個或者多個索引

添加 force index 強(qiáng)制 MySQL 使用一個特定的索引

常用 SQL 技巧

正則表達(dá)式的使用

MySQL 利用 regexp 命令提供給用戶擴(kuò)展正則表達(dá)式功能

select 'xixi' regexp '^xi';

巧用 rand() 提取隨機(jī)行

rand() 函數(shù)與 order by 子句可以一起完成隨機(jī)抽取行的功能

select * from xixi order by rand();

利用 group by 的 with rollup 子句

在 SQL 語句中蛤袒，使用 group by 的 with rollup 子句可以檢索出更多的聚合信息，它不僅僅能像一般的 group by 語句那樣檢索出各組的聚合信息膨更，能還檢索出本組類的整體聚合信息

當(dāng)使用 rollup 時妙真，不能同時使用 order by 子句進(jìn)行結(jié)果排序，換言之荚守，rollup 和 order by 是互相排斥的珍德，此外，limit 用在 rollup 后面

用 bit group functions 做統(tǒng)計(jì)

group by 語句和 bit_or()矗漾，bit_and() 函數(shù)可以配合做一些統(tǒng)計(jì)

數(shù)據(jù)庫名锈候，表名大小寫問題

在 MySQL 中，如何在硬盤上保存敞贡，使用表名和數(shù)據(jù)庫名是由 loser_case_tables_name 系統(tǒng)變量覺得的泵琳，用戶可以在啟動 MySQL 服務(wù)時設(shè)置這個變量

使用外鍵需要注意的問題

在 MySQL 中鹏漆，InnoDB 存儲引擎支持對外部關(guān)鍵字約束條件的檢查巩梢。而對于其他類型存儲引擎的表创泄，當(dāng)使用 references tablename(col) 子句定義列時可以使用外部關(guān)鍵字，但是該子句沒有實(shí)際的效果且改，只作為備忘錄或注釋來提醒用戶目前正定義的列指向另一個表中的一個列