如何設(shè)計(jì)最優(yōu)的數(shù)據(jù)庫(kù)表結(jié)構(gòu)蜘醋，如何建立最好的索引胁塞，以及如何擴(kuò)展數(shù)據(jù)庫(kù)的查詢，這些對(duì)于高性能來(lái)說(shuō)都是必不可少的压语。但是只有這些還不夠啸罢，要獲得良好的數(shù)據(jù)庫(kù)性能，我們還要設(shè)計(jì)合理的數(shù)據(jù)庫(kù)查詢胎食，如果查詢?cè)O(shè)計(jì)的很糟糕扰才，即使增加再多的只讀從庫(kù)，表結(jié)構(gòu)設(shè)計(jì)的再合理厕怜，索引再合適衩匣，只要查詢不能使用到這些東西，也無(wú)法實(shí)現(xiàn)高性能的查詢粥航。所以說(shuō)查詢優(yōu)化琅捏，索引優(yōu)化，庫(kù)表結(jié)構(gòu)優(yōu)化需要齊頭并進(jìn)递雀。

在進(jìn)行庫(kù)表結(jié)構(gòu)設(shè)計(jì)時(shí)午绳，我們要考慮到以后的查詢要如何的使用這些表，同樣映之，編寫(xiě) SQL 語(yǔ)句的時(shí)候也要考慮到如何使用到目前已經(jīng)存在的索引拦焚，或是如何增加新的索引才能提高查詢的性能。

想要對(duì)存在性能問(wèn)題的查詢進(jìn)行優(yōu)化杠输，需要能夠找到這些查詢赎败，下面先看下如何獲取有性能問(wèn)題的 SQL。

1.獲取有性能問(wèn)題的SQL

獲取有性能問(wèn)題的 SQL 的三種方法：

通過(guò)用戶反饋獲取存在性能問(wèn)題的 SQL蠢甲；
通過(guò)慢查日志獲取存在性能問(wèn)題的 SQL僵刮；
實(shí)時(shí)獲取存在性能問(wèn)題的 SQL；

1.慢查詢?nèi)罩精@取性能問(wèn)題SQL

MySQL 慢查詢?nèi)罩臼且环N性能開(kāi)銷(xiāo)比較低的獲取存在性能問(wèn)題 SQL 的解決方案鹦牛，其主要的性能開(kāi)銷(xiāo)在磁盤(pán) IO 和存儲(chǔ)日志所需要的磁盤(pán)空間搞糕。對(duì)于磁盤(pán) IO 來(lái)說(shuō)，由于寫(xiě)日志是順序存儲(chǔ)曼追，開(kāi)銷(xiāo)基本上忽略不計(jì)窍仰，所以主要需要關(guān)注的還是磁盤(pán)空間。

slow_query_log：是否啟動(dòng)慢查詢?nèi)罩纠袷猓J(rèn)不啟動(dòng)驹吮，on 啟動(dòng)针史；
slow_query_log_file：指定慢查詢?nèi)罩镜拇鎯?chǔ)路徑及文件，默認(rèn)情況下保存在 MySQL 的數(shù)據(jù)目錄中碟狞；
long_query_time：指定記錄慢查詢?nèi)罩?SQL 執(zhí)行時(shí)間的閾值啄枕，單位秒，默認(rèn) 10 秒族沃，通常對(duì)于一個(gè)繁忙的系統(tǒng)來(lái)說(shuō)频祝，改為0.001秒比較合適；
log_queries_not_using_indexes：是否記錄未使用索引的 SQL脆淹；

和二進(jìn)制日志不同常空，慢查詢?nèi)罩緯?huì)記錄所有符合條件的 SQL，包括查詢語(yǔ)句未辆、數(shù)據(jù)修改語(yǔ)句窟绷、已經(jīng)回滾的 SQL。

慢查詢?nèi)罩局杏涗浀膬?nèi)容：

# Query_time: 0.000220             //執(zhí)行時(shí)間咐柜，可以精確到毫秒兼蜈，220毫秒
# Lock_time: 0.000120              //所使用鎖的時(shí)間，可以精確到毫秒
# Rows_sent: 1                     //返回的數(shù)據(jù)行數(shù)
# Rows_examined: 1                 //掃描的數(shù)據(jù)行數(shù)
SET timestamp=1538323200;          //執(zhí)行sql的時(shí)間戳
SELECT c FROM test1 WHERE id =100;    //sql

通常情況下拙友，在一個(gè)繁忙的系統(tǒng)中为狸，短時(shí)間內(nèi)就可以產(chǎn)生幾個(gè) G 的慢查詢?nèi)罩荆斯z查幾乎是不可能的遗契，為了快速分析慢查詢?nèi)罩痉簦仨毥柚嚓P(guān)的工具。

常用的慢查詢?nèi)罩竟ぞ撸?/p>

1牍蜂、mysqldumpslow：一個(gè)常用的漾根，MySQL 官方提供的慢查詢?nèi)罩痉治龉ぞ撸S著 MySQL 服務(wù)器的安裝而被安裝鲫竞》拢可以匯總除查詢條件外其他完全相同的 SQL，并將分析結(jié)果按照參數(shù)中所指定的順序輸出从绘。

2寄疏、pt-query-digest：用于分析 MySQL 慢查詢的一個(gè)工具。

2.實(shí)時(shí)獲取性能問(wèn)題SQL

為了更加及時(shí)的發(fā)現(xiàn)當(dāng)前的性能問(wèn)題僵井，我們還可以通過(guò)實(shí)時(shí)的方法來(lái)獲取有性能問(wèn)題的 SQL陕截。最方便的一種方法就是利用 MySQL information_schema 數(shù)據(jù)庫(kù)下的 PROCESSLIST 表來(lái)實(shí)現(xiàn)實(shí)時(shí)的發(fā)現(xiàn)性能問(wèn)題 SQL。例如下面這條 SQL 表示查詢出當(dāng)前服務(wù)器中執(zhí)行時(shí)間超過(guò) 1 秒的 SQL：

SELECT id,user,host,db,command,time,state,info FROM information_schema.PROCESSLIST WHERE TIME>=1

然后我們可以通過(guò)腳本周期性的來(lái)執(zhí)行這條 SQL批什，實(shí)時(shí)的發(fā)現(xiàn)哪些 SQL 執(zhí)行的是比較慢的农曲。

2.SQL的解析預(yù)處理及生成執(zhí)行計(jì)劃

找到了那些查詢存在性能問(wèn)題的 SQL，那么下面我們就看下渊季，為什么這些 SQL 會(huì)存在性能問(wèn)題朋蔫？

為了搞清楚這個(gè)問(wèn)題罚渐，我們先來(lái)看下 MySQL 服務(wù)器處理一條 SQL 請(qǐng)求所需要經(jīng)歷的步驟都有哪些：

1. 客戶端通過(guò) MySQL 的接口發(fā)送 SQL 請(qǐng)求給服務(wù)器却汉，這一步通常不會(huì)影響查詢性能驯妄；
2. MySQL 服務(wù)器檢查是否可以在查詢緩存中命中該 SQL，如果命中合砂，則立即返回存儲(chǔ)在緩存中的結(jié)果青扔，否則進(jìn)入下一階段；
3. MySQL 服務(wù)器進(jìn)行 SQL 解析翩伪，預(yù)處理微猖，再由 SQL 優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃；
4. 根據(jù)執(zhí)行計(jì)劃缘屹，調(diào)用存儲(chǔ)引擎 API 來(lái)查詢數(shù)據(jù)凛剥；
5. 將結(jié)果返回給客戶端。
6. 這就是 MySQL 服務(wù)器處理查詢請(qǐng)求的整個(gè)過(guò)程轻姿。在第二到第五步犁珠，都有可能對(duì)查詢的響應(yīng)速度造成影響，下面來(lái)分別看下這些過(guò)程可能對(duì)查詢的響應(yīng)速度有影響的因素都有些什么：

在解析查詢語(yǔ)句前互亮，如果查詢緩存是打開(kāi)的犁享，那么 MySQL 優(yōu)先檢查這個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)，這個(gè)檢查是通過(guò)一個(gè)對(duì)大小寫(xiě)敏感的 Hash 查找實(shí)現(xiàn)的豹休。由于 Hash 查找只能進(jìn)行全值匹配炊昆，所以請(qǐng)求的查詢和緩存中的查詢就算只有一個(gè)字節(jié)的不同，那么也不會(huì)匹配到緩存中的結(jié)果威根，這種情況下凤巨，查詢就會(huì)進(jìn)入到下一階段處理。如果正好命中查詢緩存洛搀，在返回查詢結(jié)果之前敢茁，MySQL 就會(huì)檢查用戶權(quán)限，也是無(wú)需解析 SQL 語(yǔ)句的姥卢，因?yàn)樵诓樵兙彺嬷芯硪呀?jīng)存放了當(dāng)前查詢所需要訪問(wèn)的表的信息，如果權(quán)限沒(méi)有問(wèn)題独榴，MySQL 會(huì)跳過(guò)所有的其他階段僧叉，直接從緩存中拿到結(jié)果，并返回給客戶端棺榔，這種情況下查詢是不會(huì)被解析的瓶堕，也不會(huì)生成查詢計(jì)劃，不會(huì)被執(zhí)行症歇。

可以發(fā)現(xiàn)郎笆，從查詢緩存中直接返回結(jié)果并不容易谭梗。

查詢緩存對(duì) SQL 性能的影響：

如果查詢緩存，一旦數(shù)據(jù)更新宛蚓，都要對(duì)緩存中數(shù)據(jù)進(jìn)行刷新激捏，影響性能；
每次在查詢緩存中檢查 SQL 是否被命中凄吏，都要對(duì)緩存加鎖远舅，影響性能；
對(duì)于一個(gè)讀寫(xiě)頻繁的系統(tǒng)來(lái)說(shuō)痕钢，查詢緩存很可能會(huì)降低查詢處理的效率图柏。所以在這種情況下建議大家不要使用查詢緩存。

對(duì)查詢緩存影響的一些系統(tǒng)參數(shù)：

query_cache_type: 設(shè)置查詢緩存是否可用任连，可以設(shè)置為ON蚤吹、OFF、DEMAND随抠，DEMAND表示只有在查詢語(yǔ)句中使用 SQL_CACHE 和 SQL_NO_CACHE 來(lái)控制是否需要緩存裁着。
query_cache_size: 設(shè)置查詢緩存的內(nèi)存大小，必須是1024字節(jié)的整數(shù)倍暮刃。 
query_cache_limit: 設(shè)置查詢緩存可用存儲(chǔ)的最大值跨算，如果知道很大不會(huì)被緩存，可以在查詢上加上 SQL_NO_CACHE 提高效率椭懊。
query_cache_wlock_invalidate: 設(shè)置數(shù)據(jù)表被鎖后是否返回緩存中的數(shù)據(jù)诸蚕，默認(rèn)關(guān)閉。
query_cache_min_res_unit: 設(shè)置查詢緩存分配的內(nèi)存塊最小單位氧猬。

對(duì)于一個(gè)讀寫(xiě)頻繁的系統(tǒng)來(lái)說(shuō)背犯，可以把 query_cache_type 設(shè)置為 OFF，并且把 query_cache_size 設(shè)置為 0盅抚。

當(dāng)查詢緩存未啟用或者未命中則會(huì)進(jìn)入下一階段漠魏，也就是需要將一個(gè) SQL 轉(zhuǎn)換成一個(gè)執(zhí)行計(jì)劃，MySQL 再依據(jù)這個(gè)執(zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互妄均，這個(gè)階段包括了多個(gè)子過(guò)程：解析 SQL柱锹，預(yù)處理，優(yōu)化 SQL 執(zhí)行計(jì)劃丰包。在這個(gè)過(guò)程中禁熏，出現(xiàn)任何錯(cuò)誤，比如語(yǔ)法錯(cuò)誤等邑彪，都有可能中止查詢的過(guò)程瞧毙。

在語(yǔ)法解析階段，主要是通過(guò)關(guān)鍵字對(duì) MySQL 語(yǔ)句進(jìn)行解析，并生成一棵對(duì)應(yīng)的 “解析樹(shù)”宙彪。這一階段矩动，MySQL 解析器將使用 MySQL 語(yǔ)法規(guī)則驗(yàn)證和解析查詢，包括檢查語(yǔ)法是否使用了正確的關(guān)鍵字释漆、關(guān)鍵字的順序是否正確等悲没。

預(yù)處理階段則是根據(jù) MySQL 規(guī)則進(jìn)一步檢查解析樹(shù)是否合法，比如檢查查詢中所涉及的表和數(shù)據(jù)列是否存在灵汪、檢查名字或別名是否存在歧義等檀训。

如果語(yǔ)法檢查全部都通過(guò)了柑潦，查詢優(yōu)化器就可以生成查詢計(jì)劃了享言。

• 會(huì)造成 MySQL 生成錯(cuò)誤的執(zhí)行計(jì)劃的原因：
• 統(tǒng)計(jì)信息不準(zhǔn)確；
• 執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際的執(zhí)行計(jì)劃的成本渗鬼；
• MySQL 查詢優(yōu)化器所認(rèn)為的最優(yōu)可能與你所認(rèn)為的最優(yōu)不一樣览露；
• MySQL 從不考慮其他并發(fā)的查詢，這可能會(huì)影響當(dāng)前查詢的速度譬胎；
• MySQL 有時(shí)候也會(huì)基于一些固定的規(guī)則來(lái)生成執(zhí)行計(jì)劃差牛；
• MySQL 不會(huì)考慮不受其控制的成本，例如存儲(chǔ)過(guò)程堰乔、用戶自定義的函數(shù)等偏化。
  MySQL 的查詢優(yōu)化器可以優(yōu)化的 SQL 類(lèi)型：

重新定義表的關(guān)聯(lián)順序，優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息來(lái)決定表的關(guān)聯(lián)順序镐侯；

• 將外連接轉(zhuǎn)化為內(nèi)連接侦讨，比如 where 條件和庫(kù)表結(jié)構(gòu)都可能讓一個(gè)外連接等價(jià)于內(nèi)連接；
• 使用等價(jià)變換規(guī)則苟翻，比如 (5=5 and a>5) 將被改寫(xiě)為 a>5韵卤；
• 利用索引和列是否為空來(lái)優(yōu)化 count()、min() 和 max() 等聚合函數(shù)崇猫；
• 將一個(gè)表達(dá)式轉(zhuǎn)換為常數(shù)表達(dá)式沈条；
• 使用等價(jià)變換規(guī)則，比如覆蓋索引诅炉，當(dāng) MySQL 查詢優(yōu)化器發(fā)現(xiàn)索引中的列包含所有查詢中所需要的信息的時(shí)候蜡歹，MySQL 就能使用索引返回需要的數(shù)據(jù)；
• 子查詢優(yōu)化涕烧，比如把子查詢轉(zhuǎn)換為關(guān)聯(lián)查詢月而，減少表的查詢次數(shù)；
• 提前終止查詢澈魄；
• 對(duì) in() 條件進(jìn)行優(yōu)化景鼠。
  以上這些就是 MySQL 查詢優(yōu)化器可以自動(dòng)對(duì)查詢所做的一些優(yōu)化。經(jīng)過(guò)查詢優(yōu)化器改寫(xiě)后的 SQL，查詢優(yōu)化器會(huì)對(duì)其生成一個(gè) SQL 執(zhí)行計(jì)劃铛漓，然后 MySQL 服務(wù)器就可以根據(jù)執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎的 API溯香，通過(guò)存儲(chǔ)引擎獲取數(shù)據(jù)了。

3.確定查詢處理各個(gè)階段的耗時(shí)

SQL 查詢優(yōu)化的主要目的就是減少查詢所消耗的時(shí)間浓恶，加快查詢的響應(yīng)速度玫坛。下面來(lái)介紹如何度量查詢處理各個(gè)階段所消耗的時(shí)間。

對(duì)于一個(gè)存在性能問(wèn)題的 SQL 來(lái)說(shuō)包晰，必須知道在查詢的哪一階段消耗的時(shí)間最多湿镀，然后才能有針對(duì)性的進(jìn)行優(yōu)化。度量查詢處理各個(gè)階段所消耗的時(shí)間伐憾，常用的方法有兩種：

• 使用 profile勉痴；
• 使用 performance_schema；

4.特定SQL的查詢優(yōu)化

前面介紹的方法树肃，已經(jīng)可以獲取一個(gè)存在性能問(wèn)題的 SQL 和獲取一個(gè) SQL 在執(zhí)行的各個(gè)階段所消耗的時(shí)間了蒸矛。得到這些信息后，我們就可以針對(duì)性的對(duì) SQL 進(jìn)行優(yōu)化了胸嘴，下面舉幾個(gè)對(duì)特定 SQL 優(yōu)化的案例：

1.大表的更新和刪除

對(duì)于大表的數(shù)據(jù)修改最好要分批處理雏掠，比如我們要在一個(gè) 1000 萬(wàn)行記錄的表中刪除/更新 100 萬(wàn)行記錄，那么我們最好分多個(gè)批次進(jìn)行刪除/更新劣像，一次只刪除/更新 5000 行記錄乡话，避免長(zhǎng)時(shí)間的阻塞，并且為了減少對(duì)主從復(fù)制帶來(lái)的壓力耳奕，每次刪除/修改數(shù)據(jù)后需要暫停幾秒绑青。這里提供一個(gè)可以完成這樣工作的 MySQL 存儲(chǔ)過(guò)程的實(shí)例：

DELIMITER $$
USE 'db_name'$$
DROP PROCEDURE IF EXISTS 'p_delete_rows'$$
CREATE DEFINER='mysql'@'127.0.0.1' PROCEDURE 'p_delete_rows'()
BEGIN
        DECLARE v_rows INT;
        SET v_rows = 1;
        WHERE v_rows > 0
        DO
                DELETE FROM table_name WHERE id >= 9000 AND id <= 290000 LIMIT 5000;
                SELECT ROW_COUNT() INTO v_rows;
                SELECT SLEEP(5);
        END WHERE;
END$$
DELIMITER;

大家可以根據(jù)自己的情況來(lái)修改這個(gè)存儲(chǔ)過(guò)程，或者使用自己熟悉的開(kāi)發(fā)語(yǔ)言實(shí)現(xiàn)這個(gè)處理過(guò)程吮铭，使用這個(gè)存儲(chǔ)過(guò)程只需要修改 DELETE FROM table_name WHERE id >= 9000 AND id <= 290000 LIMIT 5000; 部分的內(nèi)容即可时迫。

2.如何修改大表的表結(jié)構(gòu)

對(duì)于 InnoDB 存儲(chǔ)引擎來(lái)說(shuō)，對(duì)表中的列的字段類(lèi)型進(jìn)行修改或者改變字段的寬度時(shí)還是會(huì)鎖表谓晌，同時(shí)也無(wú)法解決主從數(shù)據(jù)庫(kù)延遲的問(wèn)題掠拳。

解決方案：

在主服務(wù)器上建立新表，新表的結(jié)構(gòu)就是修改之后的結(jié)構(gòu)纸肉，再把老表的數(shù)據(jù)導(dǎo)入到新表中溺欧，并且在老表上建立一系列的觸發(fā)器，把老表數(shù)據(jù)的修改同步更新到新表中柏肪，當(dāng)老表和新表的數(shù)據(jù)同步后姐刁，再對(duì)老表加一個(gè)排它鎖，然后重新命名新表為老表的名字烦味，最好刪除重命名的老表聂使，這樣就完成了大表表結(jié)構(gòu)修改的工作壁拉。這樣處理的好處是可以盡量減少主從延遲，以及在重命名之前不需要加任何的鎖柏靶，只需要在重命名的時(shí)候加一個(gè)短暫的鎖弃理，這對(duì)應(yīng)用通常是無(wú)影響的，缺點(diǎn)就是操作比較復(fù)雜屎蜓。好在有工具可以幫我們實(shí)行這個(gè)過(guò)程痘昌，這個(gè)工具同樣是 percona 公司 MySQL 工具集中的一個(gè)，叫做 pt-online-schema-change：

pt-online-schema-change \
--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT ''" \
--user=root --password=password D=db_name,t=table_name \
--charset=utf8 --execute

這個(gè)命令就是把 db_name 數(shù)據(jù)庫(kù)下的 table_name 表中 c 列的寬度改為 VARCHAR(150)炬转。

3.如何優(yōu)化not in和<>查詢

MySQL 查詢優(yōu)化器可以自動(dòng)的把一些子查詢優(yōu)化為關(guān)聯(lián)查詢辆苔，但是對(duì)于存在not in和<>這樣的子查詢語(yǔ)句來(lái)說(shuō)，就無(wú)法進(jìn)行自動(dòng)優(yōu)化了扼劈，這就造成了會(huì)循環(huán)多次來(lái)查找子表來(lái)確認(rèn)是否滿足過(guò)濾條件驻啤，如果子查詢恰好是一個(gè)很大的表的話，這樣做的效率會(huì)非常低测僵，所以我們?cè)谶M(jìn)行 SQL 開(kāi)發(fā)時(shí)街佑，最好把這類(lèi)查詢自行改寫(xiě)成關(guān)聯(lián)查詢。

SELECT id,name,email 
FROM customer 
WHERE id 
NOT IN(SELECT id FROM payment)

優(yōu)化改寫(xiě)后：

SELECT a.id,a.name,a.email 
FROM customer a 
LEFT JOIN payment b ON a.id=b.id 
WHERE b.id IS NULL

使用 LEFT JOIN 關(guān)聯(lián)替代了 NOT IN 過(guò)濾捍靠，這樣避免了對(duì) payment 表進(jìn)行多次查詢，這是一種非常常用的對(duì) NOT IN 的優(yōu)化方式森逮。

4.使用匯總表優(yōu)化查詢

最常見(jiàn)的就是商品的評(píng)論數(shù)榨婆，如果我們?cè)谟脩粼L問(wèn)頁(yè)面時(shí)，實(shí)時(shí)的訪問(wèn)商品的評(píng)論數(shù)褒侧，通常來(lái)說(shuō)良风，查詢的 SQL 會(huì)類(lèi)似于下面這個(gè)樣子：

SELECT COUNT(*) FROM product_comment WHERE product_id = 10001;

這個(gè) SQL 就是統(tǒng)計(jì)出所有 product_id = 10001 的評(píng)論，假設(shè)評(píng)論表中有上億條記錄闷供，那么這個(gè) SQL 執(zhí)行起來(lái)是非常的慢的烟央，如果有大量的并發(fā)訪問(wèn)，則會(huì)對(duì)數(shù)據(jù)庫(kù)帶來(lái)很大的壓力歪脏。對(duì)于這么情況疑俭，我們通常使用匯總表的方式進(jìn)行優(yōu)化。所謂的匯總表就是提前把要統(tǒng)計(jì)的數(shù)據(jù)進(jìn)行匯總并記錄到表中已備后續(xù)的查詢使用婿失。針對(duì)這個(gè)查詢钞艇，我們可以使用下面的方式進(jìn)行優(yōu)化：

CREATE TABLE product_comment_cnt(product_id INT, cnt INT);   //建立匯總表

//查詢?cè)u(píng)論數(shù)
SELECT SUM(cnt) FROM(
    SELECT cnt FROM product_comment_cnt WHERE product_id = 10001
    UNION ALL
    SELECT COUNT(*) FROM product_comment WHERE product_id = 10001
    AND timestr > DATE(NOW())
);