High Performance MySQL閱讀筆記
- 第一層是連線處理,授權認證等方面 - 第二層是查詢解析,分析,優化,快取和所有內建函式如日期,時間等, 此外還有所有儲存引擎都提供的功能例如儲存過程,觸發器,檢視等。 - 第三層是儲存引擎,儲存引擎負責儲存和提取所有存放在SQL/">MySQL中的和資料。 伺服器通過儲存引擎API與具體的儲存引擎來進行通訊。
-
MySQL中預設的操作模式是AUTOCOMMIT模式,即,除非顯式的開始一個事務,否則它將每個 查詢視為一個單獨的事務自動執行
-
選擇優化的資料型別
- 更小通常更好,選擇能表示資料的最小的型別。更小的資料需要更少的磁碟,記憶體和CPU快取,通常所需要的CPU週期也更少
- 更簡單通常更好,簡單地資料型別所需要的CPU週期也更少
- 儘量避免NULL,儘量將列定義成NOT NULL
-
MySQL資料型別
ofollow,noindex" target="_blank">https://dev.mysql.com/doc/refman/8.0/en/data-types.html
-
索引基礎知識
-
能夠使用B-Tree索引的查詢型別
LIKE 'J%'
-
高效能索引策略
- 避免使用表示式和計算,如
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
就不能有效的用上索引。也不能快取。 - 選擇區分度高的列。如UNIQUE索引,其區分度為1,是最高效的。
- MySQL產生排序結果有兩種方式:使用檔案排序,或者掃描有序的索引。EXPLAIN語句 中
type
列的值如果是Index
,則說明MySQL會掃描索引。MySQL按照索引對結果進行 排序,只有當索引的順序和ORDER BY子句中的順序完全一致,並且所有列的方向一樣才可以, 如升序或者降序。 此外,如果查詢聯接了多個表,只有在ORDER BY子句的所有列引用的是第一個表才可以。 - 分頁時可以限制使用者可以檢視的最大頁數,例如
LIMIT 10000, 10
時,一定會慢, 因為要排序然後丟棄前面的資料。可以限制使用者的行為來進行優化。
- 避免使用表示式和計算,如
-
聚集索引中鍵值的邏輯順序決定了表中相應行的物理順序。聚集索引確定表中資料的 物理順序。聚集索引類似於電話簿,後者按姓氏排列資料。由於聚集索引規定資料在表 中的物理儲存順序,因此一個表只能包含一個聚集索引。但該索引可以包含多個列(組 合索引),就像電話簿按姓氏和名字進行組織一樣。InnoDB一般把主鍵設定成聚集索引
-
覆蓋索引是指三星索引中的第三星。即
SELECT xxx
中xxx直接從索引的節點中取值。 如果是用了覆蓋索引,那麼EXPLAIN語句中,Extra列會顯示Using index
-
-
查詢效能優化
- 是否訪問了太多的資料,是否獲取了不需要的資料?(如果是用ORM的話,那麼十之八九是的)
- 檢查MySQL是否檢查了太多的資料,一般通過以下三個指標
- 執行時間
- 檢查的行數
- 返回的行數
- 在應用端進行“JOIN”操作。可以加速的原因是MySQL可以很好的快取查詢結果。
- 縮小查詢量。例如一共需要500萬行資料,分解成5萬行乘以100次會比較好。最少MySQL不會被拖垮。
- 減少使用MySQL的表示式和計算
- 索引覆蓋
-
MySQL查詢的過程
- 客戶端將查詢傳送到伺服器
- 伺服器檢查查詢快取,如果找到了,就從快取中返回結果,否則進行下一步
- 伺服器進行解析,預處理和優化查詢,生成執行計劃
- 執行引擎呼叫儲存引擎API進行查詢
- 伺服器將結果傳送給客戶端
- MySQL客戶端伺服器協議
MySQL客戶端/伺服器協議是半雙工的,也就是說,客戶端和伺服器在某一個時刻只能傳送 或者接受資料。不能同時進行。優點是簡單,缺點是不能控制行為,例如客戶端傳送了查詢 的請求,接下來能做的事情就只有等待,而不能中斷查詢,除非去MySQL Server上kill。
-
一些細節
- IN比OR快。因為MySQL會對IN進行排序,然後用二分法查詢某個值是否在列表中,時間 複雜度是lg(n)而不是n。
- 提示MySQL優化器使用索引可以使用
USE INDEX
也可以使用FORCE INDEX
- 使用prepared語句可以加快速度,因為只需要parse一次。節省了解析和其他開銷。
-
索引合併優化
MySQL 5.0之前一次查詢只能用一個索引。此後可以使用多個索引,然後將結果合並。即例如 SELECT * FROM user WHERE mobile='110' OR user_id=10
,MySQL會分別使用mobile和 id兩個索引,然後將結果合併。
-
對MySQL進行查詢優化的時候,要考慮到的東西
- MySQL檢查的行數
- MySQL返回的行數
- 查詢過程中的隨機IO