本文是學(xué)習(xí)《高性能 Mysql》中關(guān)于 Mysql 中查詢優(yōu)化需要注意的一些要點的總結(jié)：

Schema 和數(shù)據(jù)類型優(yōu)化盡量避免使用 NULL 值，尤其存在索引時，因為如果 NULL 列是索引，索引統(tǒng)計以及值的比較更加復(fù)雜

盡量選擇小的簡單的數(shù)據(jù)類型，因為它們占用更少的磁盤，內(nèi)存和 CPU 緩存

盡量使用 TIMESTAMP 代替 DATETIME，因為 TIMESTAMP 只是 DATETIME 一半大小存儲空間，還會跟時區(qū)變化，但是 TIMESTAMP 允許的時間范圍比較小(1970年~2038年)

對于字符串列的最大長度比平均長度大很多的情況建議使用 VARCHAR 類型

對于非常短且比較均衡的列建議使用 CHAR 類型，不容易產(chǎn)生太多的碎片

Mysql 對于 BLOB 和 TEXT 類型的排序和其它類型規(guī)則不同，只會對每個列的前 max_sort_length 字節(jié)的字符串進(jìn)行排序，這樣必然會使用臨時表，所以盡量確保 max_sort_length 的值下不要超過 max_heap_table_size 或者 max_table_size，以保證排序時使用內(nèi)存臨時表

不同類型字段進(jìn)行關(guān)聯(lián)查詢時往往成本比較高，建議如果需要關(guān)聯(lián)查詢盡量改為相同類型

在查詢時盡量不要使用太多的關(guān)聯(lián)，雖然 Mysql 限制了每個關(guān)聯(lián)操作最多只能有 61 張表，但是為了讓查詢執(zhí)行的速度快且并發(fā)性好，單個查詢不要超過 12 張表關(guān)聯(lián)

除非枚舉值是一些固定不變的值，例如“性別”，建議不要過度使用枚舉，因為在修改枚舉值時需要 ALTER TABLE 成本非常高，而且枚舉值的排序是按照枚舉順序來排序，并不是字面值

索引優(yōu)化如果查詢中某個列是范圍查詢，那么其右邊的所有列將無法使用索引優(yōu)化，所以盡量將范圍條件放在右邊或者使用多個等值條件來代替范圍查詢

ORDER BY 中的排序的列如果建了索引，則有可能使用索引進(jìn)行排序，進(jìn)行優(yōu)化性能

只有當(dāng)索引的列和 ORDER BY 子句的順序完全一致且所有列的排序方向一致時才能使用索引做排序

哈希索引對于等值查詢的性能提升非常高，但是哈希索引無法用來排序，也不支持部分索引列匹配查找

在使用索引時對應(yīng)的索引列必須獨立，不能是表達(dá)式的一部分也不能是函數(shù)的參數(shù)，否則不能使用索引：

-- 雖然 id 上建立了索引，但是無法使用索引優(yōu)化

select id from user where id + 1 =5;當(dāng)服務(wù)器出現(xiàn)多個列做 AND 操作查詢時，通常需要建了一個多列索引，而不是多個獨立的單列索引

當(dāng)不需要考慮排序和分組時，將選擇項最高的列放在前面通常是最好的，因為可以很快的過濾出需要的行

如果索引包含了需要查詢的所有字段值，那么就是可以使用覆蓋索引查詢，只需要讀取索引，極大地減少了數(shù)據(jù)訪問量，在 EXPLAIN 分析的 Extra 字段中可以看到 “Using index” 信息

查詢時盡量不要返回多余的列，第一可以減少網(wǎng)絡(luò)流量，第二增加使用覆蓋索引的可能性

如果關(guān)聯(lián)多張表時，只有當(dāng) ORDER BY 子句引用的字段全部是第一張表時才能使用索引排序

默認(rèn)類型轉(zhuǎn)換不僅增加開銷，還會使索引失效，比如 col 是 VCHAR 類型，那么 where col = '10' 會使用索引，而 where col = 10 不會使用索引

不要創(chuàng)建冗余的索引，Mysql 不僅需要單獨維護(hù)索引列，并且在優(yōu)化器查詢時也需要逐個索引進(jìn)行過濾，會影響性能

下面是創(chuàng)建冗余索引的幾個例子：

- 創(chuàng)建了索引(A，B)再創(chuàng)建索引(A)，那后者便是冗余索引

- 將一根索引擴(kuò)展為(A，ID)，其中 ID 是主鍵，對于 InnoDB 來說主鍵已經(jīng)包含在二級索引中了，所以這也是冗余的有一些索引可能服務(wù)器永遠(yuǎn)都不會用到，建議考慮刪除，在 percona 版本或 marida 中可以通過 information_schea.index_statistics 查看得到索引的使用情況，在官方版本中可以使用 performance_schema.table_io_waits_summary_by_index_usage 查看索引使用情況：

- 可以查到使用最多或者使用最少的表和索引

- 可以查到從未使用過的索引，考慮刪除之

- 可以查到線程的使用情況等等

事務(wù)優(yōu)化盡量不要在事務(wù)中混合使用存儲引擎，如果有些表支持事務(wù)，有些表不支持事務(wù)，回滾時會導(dǎo)致數(shù)據(jù)不一致問題

在應(yīng)用層應(yīng)該檢查在事務(wù)中是否存在 RPC 調(diào)用、HTTP 調(diào)用、消息隊列、緩存、循環(huán)查詢等耗時的操作，這個操作應(yīng)該盡量移到事務(wù)之外，因為這些操作會增加事務(wù)的處理時間，使 sql 查詢不穩(wěn)定，理想的情況是事務(wù)內(nèi)只處理數(shù)據(jù)庫操作；

其它查詢優(yōu)化一個大的 DELETE 或者 UPDATE 查詢很可能會一次性鎖住很多數(shù)據(jù)，占滿整個事務(wù)日志，阻塞其它小的重要的查詢，如果有可能可以把大的查詢拆分成多個小的查詢。

關(guān)聯(lián)查詢分解：

- 讓單表查詢的緩存效率更高

- 拆分后用 IN() 代替關(guān)聯(lián)查詢，可以讓 Mysql 按照 ID 順序去查找

- 可以將數(shù)據(jù)分布到不同的 Mysql 服務(wù)器上使用 IN 加子查詢性能通常都會很低，所以建議使用 EXISTS 等效的查詢來獲取更好的效率

UNION 操作會比 UNION ALL 操作耗時,因為 UNION 操作在合并以后，還要作去重排序操作，除非必須使用 UNION 查詢，否則就使用 UNION ALL 查詢

能寫在 WHERE 條件中判斷不要寫在 HAVING 子句中，因為 GROUP BY 會對數(shù)據(jù)進(jìn)行排序，如果事先排除掉一些數(shù)據(jù)，會減少排序量，還有就是聚合后的視圖可能索引條件已經(jīng)丟失

IS NULL 或者 IS NOT NULL 查詢會使索引失效

當(dāng)以為當(dāng)前查詢只有一行數(shù)據(jù)時使用可以使用 LIMIT 1，這樣檢索到一條數(shù)據(jù)后，就停止搜索了

HAVING 子句和 GROUP BY 子句一起使用時比先 GROUP BY 成中間表再執(zhí)行 WHERE 要快

GROUP BY 子句會自動對分組的列進(jìn)行排序，如果不希望進(jìn)行排序可以使用 ORDER BY NULL

盡可能將 GROUP BY WITH ROLLUP 放到應(yīng)用程序去完成，因為 Mysql 做超級聚合往往性能不佳

優(yōu)化策略在 UNION 查詢中沒法很好的使用，一般需要將 WHERE，ORDER BY，LIMIT 子句下推到各個子查詢中

優(yōu)化 COUNT() 查詢：

- 如果是統(tǒng)計結(jié)果集的大小，請使用 COUNT(*)，使用 COUNT(cloumn) 有可能某個列存在 NULL 導(dǎo)致統(tǒng)計不準(zhǔn)確，排除 NULL 計算也是要成本的

- 對于 MyIsam 存儲引擎，如果不帶任何 WHERE 條件的情況下， COUNT(*) 不需要計算，直接通過存儲引擎特性獲得LIMIT 分頁優(yōu)化

-- 分頁時對于偏移量特別大的情況下，查詢所有列分頁將非常耗時，可以使用“延遲關(guān)聯(lián)”的方式，其中一個查詢中盡可能的使用索引覆蓋掃描方式 LIMIT 查詢出主鍵 ID，然后再和原表做一次關(guān)聯(lián)返回需要的列：

-- 優(yōu)化前

select * from user order by id limit 1000, 5;

-- 優(yōu)化后

select user.* from user join (select id from user order by id limit 1000, 5) new_user on new_user.id = user.id;

-- 如果在一個位置上預(yù)先計算出了邊界，可以將 limit 查詢轉(zhuǎn)換為已知位置的查詢進(jìn)行優(yōu)化

select * from user where id between 1000 and 1005 order by id

使用查詢提示進(jìn)行優(yōu)化

如果對優(yōu)化器的執(zhí)行計劃不滿意可以使用優(yōu)化器的幾個提示來控制最終的執(zhí)行計劃：HIGH_PRIORITY 和 LOW_PRIORITY

HIGH_PRIORITY 和 LOW_PRIORITY 對于使用表鎖的存儲引擎有效，HIGH_PRIORITY 會將當(dāng)前查詢插入到所有處于表鎖等待的 SQL 隊列前面，而 LOW_PRIORITY 會將當(dāng)前查詢放在所有等待表鎖的 SQL 隊列隊尾，只要隊列中還有需要訪問同一張表的 SQL， 它就被處于等待狀態(tài)。DELAYED

該提示對 INSERT 和 REPLACE 有效，使用該提示后會立即返回給客戶端，然后將插入的行放入緩存區(qū)，等待表空閑時批量寫入數(shù)據(jù)。

該操作導(dǎo)致 LAST_INSERT_ID() 函數(shù)無法正常工作。

對于一些數(shù)據(jù)記錄，即使插入失敗也不影響服務(wù)正常運行，可以使用該操作，及時響應(yīng)客戶端，加快響應(yīng)速度。STRAIGHT_JOIN

讓所有查詢中的的表按照語句中出現(xiàn)的順序進(jìn)行關(guān)聯(lián)，不需要 Mysql 優(yōu)化器去重新選擇關(guān)聯(lián)順序，如果能確保自己寫的關(guān)聯(lián)順序性能比較好的情況下可以選擇該提示，減少 Mysql 優(yōu)化器本身選擇分析的時間。SQL_SMALL_RESULT 和 SQL_BIG_RESULT

這兩個提示針對 select 操作，告訴優(yōu)化器對 group by 或 distinct 如何使用臨時表及排序，如果 SQL_SMALL_RESULT 表示結(jié)果集很小，使用內(nèi)存排序，如果是 SQL_BIG_RESULT 表示結(jié)果集很大，使用磁盤臨時表排序。SQL_CACHE 和 SQL_NO_CACHE

這個提示告訴 Mysql 結(jié)果集是否要緩存在查詢緩存中SQL_CALC_FOUND_ROWS

FOUND_ROWS 這個函數(shù)一般情況下只會返回上一次查詢的數(shù)據(jù)集大小，但是如果加了 SQL_CALC_FOUND_ROWS 提示，那么將返回不帶 limit 情況下整個數(shù)據(jù)集大小，這個參數(shù)對于分頁有一定的用處，不需要多次查詢。FOR_UPDATE 和 LOCK IN SHARE MODE

該提示只對支持行級鎖的存儲引擎生效，該提示會對查詢中符合條件的數(shù)據(jù)加鎖

這兩個提示會讓 InnoDB 覆蓋索引優(yōu)化失效，因為 InnoDB 需要訪問主鍵中的版本信息。USE INDEX 和 IGNORE INDEX 及 FORCE INDEX

告訴優(yōu)化器是否使用某個索引

合理使用分區(qū)表分區(qū)表數(shù)據(jù)更容易維護(hù)，想刪除大量數(shù)據(jù)可以直接使用清除某個分區(qū)的方式，并且可以獨立備份和恢復(fù)某個分區(qū)

分區(qū)表的數(shù)據(jù)可以分布到多個物理設(shè)備上，有效的利用硬件設(shè)備

如果分區(qū)列有 NULL 值，可能使分區(qū)過濾無效，因為 NULL 值會被存儲在第一個分區(qū)中

避免建立與分區(qū)列不匹配的索引，因為這樣根據(jù)索引查詢會使分區(qū)無法區(qū)分

在查找訪問分區(qū)時，Mysql 需要打開并鎖住所有的底層表，對于簡單的查詢來說這個消耗還是有點高，可以使用批量操作減少開銷次數(shù)

所有分區(qū)都必須使用相同的存儲引擎，分區(qū)中可以使用的函數(shù)和表達(dá)式也有一定的限制

Mysql 只能使用分區(qū)函數(shù)列本身查詢時才可以使用分區(qū)過濾，不能將分區(qū)列放入表達(dá)式，此時無法找到對應(yīng)分區(qū)進(jìn)行過濾

合理使用視圖/外鍵/觸發(fā)器創(chuàng)建視圖有兩種算法：臨時表算法和合并算法，如果可能盡量使用合并算法，使用合并算法時 Mysql 會將視圖與基于視圖的查詢語句進(jìn)行合并然后優(yōu)化器基于此進(jìn)行優(yōu)化

通過 explain 解析字段 select_type 判斷視圖使用臨時表算法還是合并算法，在創(chuàng)建查詢時可以指定具體使用什么算法，

如果只是使用外鍵做約束，那么通常在應(yīng)用程序里實現(xiàn)會更好，外鍵會帶來很大的額外開銷

觸發(fā)器容易掩蓋背后的工作，而且問題比較難以排查，可能導(dǎo)致死鎖，盡量不要使用觸發(fā)器

合理使用綁定變量使用綁定變量，Mysql 服務(wù)器只需要解析一次 SQL 語句，并且會緩存一部分執(zhí)行計劃

使用綁定變量每次僅僅發(fā)送的參數(shù)，而不是整個查詢語句，減少網(wǎng)絡(luò)開銷

綁定變量也相對安全，不需要處理轉(zhuǎn)義，大大減少 SQL 注入和攻擊的風(fēng)險

綁定變量是會話級別的，不同連接之間不能共用

合理使用查詢緩存如果表發(fā)生變化，對應(yīng)的查詢緩存則會失效

查詢緩存是否命中與本身查詢 SQL，查詢的數(shù)據(jù)庫，客戶端協(xié)議的版本有關(guān)系

查詢中包含自定義函數(shù)，存儲函數(shù)，用戶變量，臨時表，Mysql 庫中的系統(tǒng)表都不會設(shè)置緩存，也不會命中緩存

只有整個事務(wù)提交后，相關(guān)的查詢結(jié)果才會被緩存

查詢緩存對于復(fù)雜計算，耗時比較長的查詢有很大優(yōu)化效果，

對于簡單的查詢，因為查詢緩存的預(yù)判檢查也本身比較耗時，再加上數(shù)據(jù)變化比較快時，相反會降低性能

建議查詢時使用 SQL_CACHE 和 SQL_NO_CACHE 來進(jìn)行選擇性的使用查詢緩存

對于 InnoDB 如果表上有任何鎖，那么任何查詢都無法從緩存中讀取與這個表相關(guān)的緩存結(jié)果

如何優(yōu)化查詢緩存：

- 用多個小表代替一個大表，可以讓緩存失效在一個更細(xì)的粒度上進(jìn)行

- 批量寫入時只做一次緩存失效，所以比單條寫入更好

- 如果無法在數(shù)據(jù)庫或者表級別控制查詢緩存，則可以使用 SQL_CACHE 和 SQL_NO_CACHE 來控制單個 select 語句是否進(jìn)行緩存，并且可以修改會話級別的 query_cache_type 來控制查詢緩存

- 對于寫密集型的應(yīng)用來說，關(guān)閉查詢緩存對性能會更好

合理使用 Mysql 服務(wù)器配置mysql 的配置文件一般在 /etc/my.cnf 或者 /etc/mysql/my.cnf

任何打算長期保存的配置都應(yīng)該通過配置文件保存，不應(yīng)該在命令行里生效，以防下次啟動失效

DEFAULT 是一個特殊值可以通過 SET 設(shè)置給變量：這個值會把會話級變量設(shè)置為全局變量，會把全局變量設(shè)置為編譯器內(nèi)置的默認(rèn)值

mysql 主要的幾個環(huán)境變量配置說明：

datadir= /var/lib/mysql # 數(shù)據(jù)的存儲位置

user= mysql # 執(zhí)行 mysql 用戶運行 mysql 實例，要保證該用戶存在

port= 3306 # mysql 實例的端口號

socket： =/var/lib/mysql/mysql.sock # socket 文件存儲位置，用于 TCP/IP 套接字連接數(shù)據(jù)庫

pid_file = /var/lib/mysql/mysql.pid # mysql 進(jìn)程 id

default_storage_engine = InnoDB # 默認(rèn)的存儲引擎

innodb = FORCE # 只有在 Innodb 存儲引擎正常啟動時，服務(wù)器才能正常啟動，一般建議設(shè)置為 FORCE，保證可以正確使用 InnoDB 存儲引擎

innodb_buffer_pool_size = # InnoDB 存儲引擎可以使用的緩存大小

innodb_log_file_size = # 設(shè)置重做日志大小，太小寫入日志需要頻繁的刷新磁盤，使寫入變慢，太大奔潰恢復(fù)時間變慢，要合理設(shè)置

innodb_thread_concurrency = 0 # 它可以限制一次性有多少線程進(jìn)入內(nèi)核，0 表示不限制。一般建議設(shè)置為：CPU 數(shù)量 * 磁盤數(shù)量 * 2

innodb_thread_sleep_delay = 10000 # 為了減少因為操作系統(tǒng)調(diào)度引起的上下文切換，線程第一次無法進(jìn)入內(nèi)核會休眠 innodb_thread_sleep_delay 秒以后再嘗試

# 如果再次無法進(jìn)入內(nèi)核，則放入線程等待隊列，讓操作系統(tǒng)來處理

innodb_file_per_table = 1 # 是否讓每一張表使用一個獨立文件存儲，使得刪除表變的簡單，并且容易分散到不同的磁盤上，但是會導(dǎo)致空間的浪費

innodb_flush_method = 0_DIRECT # 控制 InnoDB 如何和文件系統(tǒng)相互作用，控制將數(shù)據(jù)刷新到磁盤的方式，要不要使用磁盤緩存等

key_buffer_size = # MyISAM 存儲引擎分配的鍵緩存大小，該值對使用 MyISAM 存儲引擎的數(shù)據(jù)庫非常重要

# 即使是使用 InnoDB 存儲引擎也應(yīng)該分配一定空間(32M)，因為 Mysql 中一些系統(tǒng)表會使用 MyISAM 存儲引擎

# Group by 創(chuàng)建臨時表時也可能使用 MyISAM 存儲引擎

sort_buffer_size = # 該參數(shù)會在查詢使用內(nèi)存排序時分配內(nèi)存，一旦需要排序就會指定這么大的內(nèi)存，不管是否需要這么大的內(nèi)存

# 一般建議把 sort_buffer_size 修改的小一點，如果某個查詢確實需要很大內(nèi)存排序，可以在會話級臨時調(diào)大該值

log_error=/var/lib/mysql/mysql-error.log # 錯誤日志存放位置

slow_query_log=/var/lib/mysql/mysql-show.log# 慢查詢?nèi)罩敬娣盼恢?/p>

tmp_table_size/max_heap_table_size = 32M # 這兩個變量用于控制使用內(nèi)存臨時表(Memory存儲引擎)的大小，如果超過這個值，將使用磁盤臨時表(MyISAM存儲引擎)

# 通過 show status 觀察 Created_tmp_disk_tables 和 create_tmp_tables 的變化來調(diào)整這兩個參數(shù)

query_cache_type = 0 # 控制查詢緩存功能的開啟和關(guān)閉，0 表示關(guān)閉，1 表示開啟，2 表示只有 select 中明確指定 SQL_CACHE 才緩存

query_cache_size = 0 # 設(shè)置查詢緩存的大小

max_connections = # 最大連接數(shù)，默認(rèn)是 100，往往太小，如果太小會報太多連接被拒絕的錯誤，觀察 Max_used_connections 狀態(tài)變量來設(shè)置該參數(shù)

thread_cache_size = # 指定 Mysql 可以保存在緩存中的線程數(shù)，一般通過觀察 Thread_connected 變量的大小來調(diào)整該值的大小

table_cache_size = 1000 # 設(shè)置表緩存大小，設(shè)置足夠的大小以避免總是需要重新打開并重新解析表定義

open_files_limit = 65535 # 這個參數(shù)可以盡量設(shè)置大，因為打開句柄的開銷很小，否則會出現(xiàn)“too many open files”

expire_logs_days = 10 # 服務(wù)器在指定的天數(shù)后清理二進(jìn)制日志

max_connect_errors = 100 # 容許某個應(yīng)用最大錯誤次數(shù)，如果超過該值，將被加入黑名單，除非刷新主機(jī)緩存幾個 timeout 相關(guān)參數(shù)說明：

- connect_timeout

在獲取連接階段(authenticate)起作用，獲取 MySQL 連接是多次握手的結(jié)果，除了用戶名和密碼的匹配校驗外，還有 IP->HOST->DNS->IP 驗證，任何一步都可能因為網(wǎng)絡(luò)問題導(dǎo)致線程阻塞。

為了防止線程浪費在不必要的校驗等待上，超過 connect_timeout 的連接請求將會被拒絕，默認(rèn)值 10 秒。

- interactive_timeout 和 wait_timeout

在連接空閑階段(sleep)起作用，即使沒有網(wǎng)絡(luò)問題，也不能允許客戶端一直占用連接。

對于保持 sleep 狀態(tài)超過了 wait_timeout(或 interactive_timeout，取決于 client_interactive 標(biāo)志)的客戶端，MySQL 會主動斷開連接，默認(rèn)值是 8 小時。

- net_read_timeout 和 net_write_timeout

則是在連接繁忙階段(query)起作用，即使連接沒有處于 sleep 狀態(tài)，即客戶端忙于計算或者存儲數(shù)據(jù)，MySQL 也選擇了有條件的等待。

在數(shù)據(jù)包的分發(fā)過程中，客戶端可能來不及響應(yīng)(發(fā)送、接收、或者處理數(shù)據(jù)包太慢)。

為了保證連接不被浪費在無盡的等待中，MySQL 也會選擇有條件(net_read_timeout和net_write_timeout)地主動斷開連接。默認(rèn)是 30 秒。

- innodb_lock_wait_timeout

innodb 使用這個參數(shù)能夠有效避免在資源有限的情況下產(chǎn)生太多的鎖等待，指的是事務(wù)等待獲取資源時等待的最長時間，超過這個時間還未分配到資源則會返回應(yīng)用失敗。

參數(shù)的時間單位是秒，最小可設(shè)置為1s(一般不會設(shè)置得這么小)，最大可設(shè)置1073741824秒(34年)，默認(rèn)安裝時這個值是 50 s。

超過這個時間會報 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。

- innodb_rollback_on_timeout

默認(rèn)情況下 innodb_lock_wait_timeout 超時后只是超時的 sql 執(zhí)行失敗，整個事務(wù)并不回滾，也不做提交。

如需要事務(wù)在超時的時候回滾，則需要設(shè)置 innodb_rollback_on_timeout=ON，該參數(shù)默認(rèn)為 OFF。

- lock_wait_timeout

和 innodb_lock_wait_timeout 的區(qū)別是前者是 Innodb 的 DML 操作的行級鎖的等待時間，后面是數(shù)據(jù)結(jié)構(gòu) DDL 操作的鎖的等待時間。

- innodb_flush_log_at_timeout

參數(shù) innodb_flush_log_at_trx_commit = 1 時，此超時參數(shù)不起作用。當(dāng) innodb_flush_log_at_trx_commit=0/2 時才起作用。

表示每 innodb_flush_log_at_timeout 秒進(jìn)行一次的頻率刷新 redo log

在 5.6.6 版本之前是固定每秒一次刷新 redo log，5.6.6 版本之后刷新頻率可以通過這個參數(shù)設(shè)置，當(dāng)然，這個參數(shù)本身默認(rèn)值也是 1S

總結(jié)

以上是生活随笔為你收集整理的mysql优化要点_你需要掌握的 Mysql 优化的一些要点的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。