本文是學習《高性能 Mysql》中關于 Mysql 中查詢優化需要注意的一些要點的總結：

Schema 和數據類型優化

• 盡量避免使用 NULL 值，尤其存在索引時，因為如果 NULL 列是索引，索引統計以及值的比較更加復雜
• 盡量選擇小的簡單的數據類型，因為它們占用更少的磁盤，內存和 CPU 緩存
• 盡量使用 TIMESTAMP 代替 DATETIME，因為 TIMESTAMP 只是 DATETIME 一半大小存儲空間，還會跟時區變化，但是 TIMESTAMP 允許的時間范圍比較小（1970年~2038年）
• 對于字符串列的最大長度比平均長度大很多的情況建議使用 VARCHAR 類型
• 對于非常短且比較均衡的列建議使用 CHAR 類型，不容易產生太多的碎片
• Mysql 對于 BLOB 和 TEXT 類型的排序和其它類型規則不同，只會對每個列的前 max_sort_length 字節的字符串進行排序，這樣必然會使用臨時表，所以盡量確保 max_sort_length 的值下不要超過 max_heap_table_size 或者 max_table_size，以保證排序時使用內存臨時表
• 不同類型字段進行關聯查詢時往往成本比較高，建議如果需要關聯查詢盡量改為相同類型
• 在查詢時盡量不要使用太多的關聯，雖然 Mysql 限制了每個關聯操作最多只能有 61 張表，但是為了讓查詢執行的速度快且并發性好，單個查詢不要超過 12 張表關聯
• 除非枚舉值是一些固定不變的值，例如“性別”，建議不要過度使用枚舉，因為在修改枚舉值時需要 ALTER TABLE 成本非常高，而且枚舉值的排序是按照枚舉順序來排序，并不是字面值

索引優化

• 如果查詢中某個列是范圍查詢，那么其右邊的所有列將無法使用索引優化，所以盡量將范圍條件放在右邊或者使用多個等值條件來代替范圍查詢
• ORDER BY 中的排序的列如果建了索引，則有可能使用索引進行排序，進行優化性能
• 只有當索引的列和 ORDER BY 子句的順序完全一致且所有列的排序方向一致時才能使用索引做排序
• 哈希索引對于等值查詢的性能提升非常高，但是哈希索引無法用來排序，也不支持部分索引列匹配查找
• 在使用索引時對應的索引列必須獨立，不能是表達式的一部分也不能是函數的參數，否則不能使用索引：

-- 雖然 id 上建立了索引，但是無法使用索引優化 select id from user where id + 1 =5;

• 當服務器出現多個列做 AND 操作查詢時，通常需要建了一個多列索引，而不是多個獨立的單列索引
• 當不需要考慮排序和分組時，將選擇項最高的列放在前面通常是最好的，因為可以很快的過濾出需要的行
• 如果索引包含了需要查詢的所有字段值，那么就是可以使用覆蓋索引查詢，只需要讀取索引，極大地減少了數據訪問量，在 EXPLAIN 分析的 Extra 字段中可以看到 “Using index” 信息
• 查詢時盡量不要返回多余的列，第一可以減少網絡流量，第二增加使用覆蓋索引的可能性
• 如果關聯多張表時，只有當 ORDER BY 子句引用的字段全部是第一張表時才能使用索引排序
• 默認類型轉換不僅增加開銷，還會使索引失效，比如 col 是 VCHAR 類型，那么 where col = '10' 會使用索引，而 where col = 10 不會使用索引
• 不要創建冗余的索引，Mysql 不僅需要單獨維護索引列，并且在優化器查詢時也需要逐個索引進行過濾，會影響性能

下面是創建冗余索引的幾個例子：

- 創建了索引（A，B）再創建索引（A），那后者便是冗余索引 - 將一根索引擴展為（A，ID），其中 ID 是主鍵，對于 InnoDB 來說主鍵已經包含在二級索引中了，所以這也是冗余的

• 有一些索引可能服務器永遠都不會用到，建議考慮刪除，在 percona 版本或 marida 中可以通過 information_schea.index_statistics 查看得到索引的使用情況，在官方版本中可以使用 performance_schema.table_io_waits_summary_by_index_usage 查看索引使用情況：

- 可以查到使用最多或者使用最少的表和索引 - 可以查到從未使用過的索引，考慮刪除之 - 可以查到線程的使用情況等等

事務優化

• 盡量不要在事務中混合使用存儲引擎，如果有些表支持事務，有些表不支持事務，回滾時會導致數據不一致問題
• 在應用層應該檢查在事務中是否存在 RPC 調用、HTTP 調用、消息隊列、緩存、循環查詢等耗時的操作，這個操作應該盡量移到事務之外，因為這些操作會增加事務的處理時間，使 sql 查詢不穩定，理想的情況是事務內只處理數據庫操作；

其它查詢優化

• 一個大的 DELETE 或者 UPDATE 查詢很可能會一次性鎖住很多數據，占滿整個事務日志，阻塞其它小的重要的查詢，如果有可能可以把大的查詢拆分成多個小的查詢。
• 關聯查詢分解：

- 讓單表查詢的緩存效率更高 - 拆分后用 IN() 代替關聯查詢，可以讓 Mysql 按照 ID 順序去查找 - 可以將數據分布到不同的 Mysql 服務器上

• 使用 IN 加子查詢性能通常都會很低，所以建議使用 EXISTS 等效的查詢來獲取更好的效率
• UNION 操作會比 UNION ALL 操作耗時,因為 UNION 操作在合并以后，還要作去重排序操作，除非必須使用 UNION 查詢，否則就使用 UNION ALL 查詢
• 能寫在 WHERE 條件中判斷不要寫在 HAVING 子句中，因為 GROUP BY 會對數據進行排序，如果事先排除掉一些數據，會減少排序量，還有就是聚合后的視圖可能索引條件已經丟失
• IS NULL 或者 IS NOT NULL 查詢會使索引失效
• 當以為當前查詢只有一行數據時使用可以使用 LIMIT 1，這樣檢索到一條數據后，就停止搜索了
• HAVING 子句和 GROUP BY 子句一起使用時比先 GROUP BY 成中間表再執行 WHERE 要快
• GROUP BY 子句會自動對分組的列進行排序，如果不希望進行排序可以使用 ORDER BY NULL
• 盡可能將 GROUP BY WITH ROLLUP 放到應用程序去完成，因為 Mysql 做超級聚合往往性能不佳
• 優化策略在 UNION 查詢中沒法很好的使用，一般需要將 WHERE，ORDER BY，LIMIT 子句下推到各個子查詢中
• 優化 COUNT() 查詢：

- 如果是統計結果集的大小，請使用 COUNT(*)，使用 COUNT(cloumn) 有可能某個列存在 NULL 導致統計不準確，排除 NULL 計算也是要成本的 - 對于 MyIsam 存儲引擎，如果不帶任何 WHERE 條件的情況下， COUNT(*) 不需要計算，直接通過存儲引擎特性獲得

• LIMIT 分頁優化

-- 分頁時對于偏移量特別大的情況下，查詢所有列分頁將非常耗時，可以使用“延遲關聯”的方式，其中一個查詢中盡可能的使用索引覆蓋掃描方式 LIMIT 查詢出主鍵 ID，然后再和原表做一次關聯返回需要的列： -- 優化前 select * from user order by id limit 1000, 5; -- 優化后 select user.* from user join (select id from user order by id limit 1000, 5) new_user on new_user.id = user.id;-- 如果在一個位置上預先計算出了邊界，可以將 limit 查詢轉換為已知位置的查詢進行優化 select * from user where id between 1000 and 1005 order by id

使用查詢提示進行優化

如果對優化器的執行計劃不滿意可以使用優化器的幾個提示來控制最終的執行計劃：

• HIGH_PRIORITY 和 LOW_PRIORITY

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

• DELAYED

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

該操作導致 LAST_INSERT_ID() 函數無法正常工作。

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

• STRAIGHT_JOIN

讓所有查詢中的的表按照語句中出現的順序進行關聯，不需要 Mysql 優化器去重新選擇關聯順序，如果能確保自己寫的關聯順序性能比較好的情況下可以選擇該提示，減少 Mysql 優化器本身選擇分析的時間。

• SQL_SMALL_RESULT 和 SQL_BIG_RESULT

這兩個提示針對 select 操作，告訴優化器對 group by 或 distinct 如何使用臨時表及排序，如果 SQL_SMALL_RESULT 表示結果集很小，使用內存排序，如果是 SQL_BIG_RESULT 表示結果集很大，使用磁盤臨時表排序。

• SQL_CACHE 和 SQL_NO_CACHE

這個提示告訴 Mysql 結果集是否要緩存在查詢緩存中

• SQL_CALC_FOUND_ROWS

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

• FOR_UPDATE 和 LOCK IN SHARE MODE

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

這兩個提示會讓 InnoDB 覆蓋索引優化失效，因為 InnoDB 需要訪問主鍵中的版本信息。

• USE INDEX 和 IGNORE INDEX 及 FORCE INDEX

告訴優化器是否使用某個索引

合理使用分區表

• 分區表數據更容易維護，想刪除大量數據可以直接使用清除某個分區的方式，并且可以獨立備份和恢復某個分區
• 分區表的數據可以分布到多個物理設備上，有效的利用硬件設備
• 如果分區列有 NULL 值，可能使分區過濾無效，因為 NULL 值會被存儲在第一個分區中
• 避免建立與分區列不匹配的索引，因為這樣根據索引查詢會使分區無法區分
• 在查找訪問分區時，Mysql 需要打開并鎖住所有的底層表，對于簡單的查詢來說這個消耗還是有點高，可以使用批量操作減少開銷次數
• 所有分區都必須使用相同的存儲引擎，分區中可以使用的函數和表達式也有一定的限制
• Mysql 只能使用分區函數列本身查詢時才可以使用分區過濾，不能將分區列放入表達式，此時無法找到對應分區進行過濾

合理使用視圖/外鍵/觸發器

• 創建視圖有兩種算法：臨時表算法和合并算法，如果可能盡量使用合并算法，使用合并算法時 Mysql 會將視圖與基于視圖的查詢語句進行合并然后優化器基于此進行優化
• 通過 explain 解析字段 select_type 判斷視圖使用臨時表算法還是合并算法，在創建查詢時可以指定具體使用什么算法，
• 如果只是使用外鍵做約束，那么通常在應用程序里實現會更好，外鍵會帶來很大的額外開銷
• 觸發器容易掩蓋背后的工作，而且問題比較難以排查，可能導致死鎖，盡量不要使用觸發器

合理使用綁定變量

• 使用綁定變量，Mysql 服務器只需要解析一次 SQL 語句，并且會緩存一部分執行計劃
• 使用綁定變量每次僅僅發送的參數，而不是整個查詢語句，減少網絡開銷
• 綁定變量也相對安全，不需要處理轉義，大大減少 SQL 注入和攻擊的風險
• 綁定變量是會話級別的，不同連接之間不能共用

合理使用查詢緩存

• 如果表發生變化，對應的查詢緩存則會失效
• 查詢緩存是否命中與本身查詢 SQL，查詢的數據庫，客戶端協議的版本有關系
• 查詢中包含自定義函數，存儲函數，用戶變量，臨時表，Mysql 庫中的系統表都不會設置緩存，也不會命中緩存
• 只有整個事務提交后，相關的查詢結果才會被緩存
• 查詢緩存對于復雜計算，耗時比較長的查詢有很大優化效果，
• 對于簡單的查詢，因為查詢緩存的預判檢查也本身比較耗時，再加上數據變化比較快時，相反會降低性能
• 建議查詢時使用 SQL_CACHE 和 SQL_NO_CACHE 來進行選擇性的使用查詢緩存
• 對于 InnoDB 如果表上有任何鎖，那么任何查詢都無法從緩存中讀取與這個表相關的緩存結果
• 如何優化查詢緩存：

- 用多個小表代替一個大表，可以讓緩存失效在一個更細的粒度上進行 - 批量寫入時只做一次緩存失效，所以比單條寫入更好 - 如果無法在數據庫或者表級別控制查詢緩存，則可以使用 SQL_CACHE 和 SQL_NO_CACHE 來控制單個 select 語句是否進行緩存，并且可以修改會話級別的 query_cache_type 來控制查詢緩存 - 對于寫密集型的應用來說，關閉查詢緩存對性能會更好

合理使用 Mysql 服務器配置

• mysql 的配置文件一般在 /etc/my.cnf 或者 /etc/mysql/my.cnf
• 任何打算長期保存的配置都應該通過配置文件保存，不應該在命令行里生效，以防下次啟動失效
• DEFAULT 是一個特殊值可以通過 SET 設置給變量：這個值會把會話級變量設置為全局變量，會把全局變量設置為編譯器內置的默認值
• mysql 主要的幾個環境變量配置說明：

datadir= /var/lib/mysql # 數據的存儲位置user= mysql # 執行 mysql 用戶運行 mysql 實例，要保證該用戶存在port= 3306 # mysql 實例的端口號socket： =/var/lib/mysql/mysql.sock # socket 文件存儲位置，用于 TCP/IP 套接字連接數據庫pid_file = /var/lib/mysql/mysql.pid # mysql 進程 iddefault_storage_engine = InnoDB # 默認的存儲引擎innodb = FORCE # 只有在 Innodb 存儲引擎正常啟動時，服務器才能正常啟動，一般建議設置為 FORCE，保證可以正確使用 InnoDB 存儲引擎innodb_buffer_pool_size = <value> # InnoDB 存儲引擎可以使用的緩存大小innodb_log_file_size = <value> # 設置重做日志大小，太小寫入日志需要頻繁的刷新磁盤，使寫入變慢，太大奔潰恢復時間變慢，要合理設置innodb_thread_concurrency = 0 # 它可以限制一次性有多少線程進入內核，0 表示不限制。一般建議設置為：CPU 數量 * 磁盤數量 * 2innodb_thread_sleep_delay = 10000 # 為了減少因為操作系統調度引起的上下文切換，線程第一次無法進入內核會休眠 innodb_thread_sleep_delay 秒以后再嘗試# 如果再次無法進入內核，則放入線程等待隊列，讓操作系統來處理innodb_file_per_table = 1 # 是否讓每一張表使用一個獨立文件存儲，使得刪除表變的簡單，并且容易分散到不同的磁盤上，但是會導致空間的浪費innodb_flush_method = 0_DIRECT # 控制 InnoDB 如何和文件系統相互作用，控制將數據刷新到磁盤的方式，要不要使用磁盤緩存等key_buffer_size = <value> # MyISAM 存儲引擎分配的鍵緩存大小，該值對使用 MyISAM 存儲引擎的數據庫非常重要# 即使是使用 InnoDB 存儲引擎也應該分配一定空間（32M），因為 Mysql 中一些系統表會使用 MyISAM 存儲引擎# Group by 創建臨時表時也可能使用 MyISAM 存儲引擎sort_buffer_size = <value> # 該參數會在查詢使用內存排序時分配內存，一旦需要排序就會指定這么大的內存，不管是否需要這么大的內存 # 一般建議把 sort_buffer_size 修改的小一點，如果某個查詢確實需要很大內存排序，可以在會話級臨時調大該值log_error=/var/lib/mysql/mysql-error.log # 錯誤日志存放位置slow_query_log=/var/lib/mysql/mysql-show.log# 慢查詢日志存放位置tmp_table_size/max_heap_table_size = 32M # 這兩個變量用于控制使用內存臨時表（Memory存儲引擎）的大小，如果超過這個值，將使用磁盤臨時表（MyISAM存儲引擎）# 通過 show status 觀察 Created_tmp_disk_tables 和 create_tmp_tables 的變化來調整這兩個參數query_cache_type = 0 # 控制查詢緩存功能的開啟和關閉，0 表示關閉，1 表示開啟，2 表示只有 select 中明確指定 SQL_CACHE 才緩存query_cache_size = 0 # 設置查詢緩存的大小max_connections = <value> # 最大連接數，默認是 100，往往太小，如果太小會報太多連接被拒絕的錯誤，觀察 Max_used_connections 狀態變量來設置該參數thread_cache_size = <value> # 指定 Mysql 可以保存在緩存中的線程數，一般通過觀察 Thread_connected 變量的大小來調整該值的大小table_cache_size = 1000 # 設置表緩存大小，設置足夠的大小以避免總是需要重新打開并重新解析表定義open_files_limit = 65535 # 這個參數可以盡量設置大，因為打開句柄的開銷很小，否則會出現“too many open files”expire_logs_days = 10 # 服務器在指定的天數后清理二進制日志max_connect_errors = 100 # 容許某個應用最大錯誤次數，如果超過該值，將被加入黑名單，除非刷新主機緩存

• 幾個 timeout 相關參數說明：

- connect_timeout在獲取連接階段（authenticate）起作用，獲取 MySQL 連接是多次握手的結果，除了用戶名和密碼的匹配校驗外，還有 IP->HOST->DNS->IP 驗證，任何一步都可能因為網絡問題導致線程阻塞。 為了防止線程浪費在不必要的校驗等待上，超過 connect_timeout 的連接請求將會被拒絕，默認值 10 秒。- interactive_timeout 和 wait_timeout在連接空閑階段（sleep）起作用，即使沒有網絡問題，也不能允許客戶端一直占用連接。 對于保持 sleep 狀態超過了 wait_timeout（或 interactive_timeout，取決于 client_interactive 標志）的客戶端，MySQL 會主動斷開連接，默認值是 8 小時。- net_read_timeout 和 net_write_timeout則是在連接繁忙階段（query）起作用，即使連接沒有處于 sleep 狀態，即客戶端忙于計算或者存儲數據，MySQL 也選擇了有條件的等待。 在數據包的分發過程中，客戶端可能來不及響應（發送、接收、或者處理數據包太慢）。 為了保證連接不被浪費在無盡的等待中，MySQL 也會選擇有條件（net_read_timeout和net_write_timeout）地主動斷開連接。默認是 30 秒。- innodb_lock_wait_timeoutinnodb 使用這個參數能夠有效避免在資源有限的情況下產生太多的鎖等待，指的是事務等待獲取資源時等待的最長時間，超過這個時間還未分配到資源則會返回應用失敗。 參數的時間單位是秒，最小可設置為1s(一般不會設置得這么小)，最大可設置1073741824秒（34年），默認安裝時這個值是 50 s。 超過這個時間會報 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。- innodb_rollback_on_timeout默認情況下 innodb_lock_wait_timeout 超時后只是超時的 sql 執行失敗，整個事務并不回滾，也不做提交。 如需要事務在超時的時候回滾，則需要設置 innodb_rollback_on_timeout=ON，該參數默認為 OFF。- lock_wait_timeout和 innodb_lock_wait_timeout 的區別是前者是 Innodb 的 DML 操作的行級鎖的等待時間，后面是數據結構 DDL 操作的鎖的等待時間。- innodb_flush_log_at_timeout參數 innodb_flush_log_at_trx_commit = 1 時，此超時參數不起作用。當 innodb_flush_log_at_trx_commit=0/2 時才起作用。 表示每 innodb_flush_log_at_timeout 秒進行一次的頻率刷新 redo log 在 5.6.6 版本之前是固定每秒一次刷新 redo log，5.6.6 版本之后刷新頻率可以通過這個參數設置，當然，這個參數本身默認值也是 1S

總結

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

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。