首先看一下分享的目錄
邏輯架構
查詢過程
存儲引擎
數據類型
索引失效
優化方案
邏輯架構
mysql邏輯架構圖
- MySQL邏輯架構整體分為三層，最上層為客戶層，
- 並非MySQL所獨有，諸如，連接處理、授權認證、
- 安全等功能均在這一層處理。
- MySQL大多數核心服務均在中間這一層，包括查
- 詢解析、分析、優化、緩存、內置函數（時間、數學、
- 加密等），所有的跨存儲引擎的功能也在這一層實現：
- 存儲過程、觸發器、視圖等。
- 最下層為存儲引擎，其負責MySQL中的數據存儲
- 和提取，中間的服務層通過API與存儲引擎通信，這
- 些API接口屏蔽了不同存儲引擎的差異。
查詢過程
查詢過程時序圖
存儲引擎
支持的9種存儲引擎
從上圖我們可以查看出當前版本的MySQL 默認存儲引擎是InnoDB, 其實在5.5版本之前，MyISAM是它的默認引擎，之後是InnoDB。我們項目用的是8.0.17 。
主流的引擎就兩種：innodb和myisam
- MyISAM和InnoDB區別
- MyISAM不支持事務，而InnoDB支持。
- Myisam可以沒有主鍵，InnoDB表必須有主鍵（用戶沒有指定的話會自己找或生產一個主鍵）
- 而 MyISAM表不支持外鍵，InnoDB支持
- MyISAM鎖的粒度是表級，而InnoDB支持行（默認），表級鎖。
- （InnoDB的行鎖是實現在索引上的，而不是鎖在物理行記錄上。潛臺詞是，如果訪問沒有命中索引，也無法使用行鎖，將要退化為表鎖）
- MyISAM支持全文類型索引，而InnoDB不支持全文索引。(mysql 5.7後innodb支持全文索引)
- InnoDB不保存表的具體行數，執行select count(*) from table時需要全表掃描。而MyISAM用一個變量保存了整個表的行數，執行上述語句時只需要讀出該變量即可，速度很快（注意不能加有任何WHERE條件）
- 清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表。
- Innodb存儲文件有frm、ibd，而Myisam是frm、myd(my data)、myi(my index)
- Innodb：frm是表定義文件，ibd是數據文件
- Myisam：frm是表定義文件，myd是數據文件，myi是索引文件
- MyISAM相對簡單，所以在效率上要優於InnoDB，小型應用可以考慮使用MyISAM。當你的數據庫有大量的寫入、更新操作而查詢比較少或者數據完整性要求比較高的時候就選擇innodb表。當你的數據庫主要以查詢為主，相比較而言更新和寫 入比較少，並且業務方面數據完整性要求不那麼嚴格，就選擇myisam表。
《MySQL高性能》上面有一句話這樣寫到:
不要輕易相信“MyISAM比InnoDB快”之類的經驗之談，這個結論往往不是絕對的。在很多我們已知場景中，InnoDB的速度都可以讓MyISAM望塵莫及，尤其是用到了聚簇索引，或者需要訪問的數據都可以放入內存的應用。
一般情況下我們選擇 InnoDB 都是沒有問題的，但是某些情況下你並不在乎可擴展能力和併發能力，也不需要事務支持，也不在乎崩潰後的安全恢復問題的話，選擇MyISAM也是一個不錯的選擇。但是一般情況下，我們都是需要考慮到這些問題的。
MYSQL數據類型
MySQL支持多種類型，大致可以分為三類：數值、日期/時間和字符串(字符)類型
- 數值類型
數值類型
注：
- M 的值跟 int(M) 所佔多少存儲空間並無任何關係
- TINYINT(M), M默認為4;
- SMALLINT(M), M默認為6;
- MEDIUMINT(M), M默認為9;
- INT(M),M默認為11,其實如果我們明確了無符號，那麼可以設置為10，因為少了符號位;
- BIGINT(M),M默認為20.
- M表示最大顯示寬度,不是用來限制INT列內保存值的範圍的。建表若設置了zerofill(0填充), 會在數字前面補充0. int(M)的最大值和最小值與UNSIGNED有關
- 日期類型
日期類型
注：
- 每種日期時間類型都有一個有效值範圍，如果超出這個範圍，在默認的SQLMode下，系統會進行錯誤示，並將以“零值”來進行存儲。
- datetime : 0000-00-00 00:00:00
- date :0000-00-00
- timestamp:00000000000000
- 字符/字符串類型
字符/字符串類型
注：
- char(N)用於存放固定長度的字符串，長度最大為255，比指定長度大的值將被截斷，而比指定長度小的值將會用空格進行填補；
- varchar(N)用於保存可變長度的字符串，長度最大為65535，只存儲字符串實際需要的長度，它會增加一個額外字節來保存字符串本身的長度，varchar使用額外的1~2字節來存儲值得長度，如果列的最大長度小於或等於255，則使用1字節，否則就是使用2字節；（1個字節佔8位，2的8次方是256（-128~127）；2個字節佔16位，2的16次方為65536）
- char和varchar跟字符編碼也有密切聯繫，lantin1佔用1個字節，gbk佔用2個字節，utf8佔用3個字節。
索引失效場景
- 當查詢條件存在隱式轉換（字符串沒加單引號，數字加了單引號）
- 索引失效使用like時通配符在前
- 在查詢條件（只要有一個條件列沒索引）中使用OR
- 對索引列進行函數或者數學運算
- 聯合索引未遵循最左前綴原則
- 範圍條件右邊的列索引失效（< ,> between and）
- 例如INDEX（a,b,c）,where a=1 and b>2 and c=3,c是不走索引的
- 使用不等於（<>,!=）
- is null或者is not null
a.單列索引無法儲null值，複合索引無法儲全為null的值。
b.查詢時，採用is null條件時，不能利用到索引，只能全表掃描。
為什麼索引列無法存儲Null值？
①.索引是有序的。NULL值進入索引時，無法確定其應該放在哪裡。（將索引列值進行建樹，其中必然涉及到諸多的比較操作，null 值是不確定值無法比較，無法確定null出現在索引樹的葉子節點位置。）
②.如果需要把空值存入索引，方法有二：其一，把NULL值轉為一個特定的值，在WHERE中檢索時，用該特定值查找。其二，建立一個複合索引。例如 create index IDX on table(col1,1); 通過在複合索引中指定一個非空常量值，而使構成索引的列的組合中，不可能出現全空值。
- 左連接查詢或者右連接查詢,查詢關聯的字段編碼格式不一樣
- 如果mysql覺得全表掃描更快時（數據少）
- Explain查看執行計劃後主要字段詳解：
- id: SQL執行的順序的標識,SQL從大到小的執行,先執行的語句編號大
1、id相同：按從上到下的順序執行
2、id不同：按id從大到小執行
3、id部分不同：先執行id大的，id相同的，按從上到下的順序執行
- select_type:就是select的類型
SIMPLE簡單的查詢，沒有子查詢，也沒有union
PRIMAR主查詢，有子查詢的外邊的查詢
UNIONUNION中的第二個或者後邊的select
DEPENDENT UNION 指子查詢中的第二個或者後邊的select
UNION RESULTUNION 的整個查詢
SUBQUERY子查詢中的第一個SELECT
DEPENDENT SUBQUERY子查詢中的第一個SELECT，取決於外面的查詢
DERIVED派生表的SELECT, FROM子句的子查詢
- table：顯示這一行的數據是關於哪張表的
- partitions:分區
- possible_keys：顯示可能應用在這張表中的索引
- type:
mysql在表中找到需要的行的方式，也叫訪問類型（好的sql至少達到range級別，最好能達到ref）
all全表掃描
index全索引掃描
range給定索引範圍進行掃描
ref表示連接，即掃描條件是某個常量，或者列
eq_ref類似於ref，區別是所使用的索引是唯一索引不存在相同的值
const,system 在查詢時mysql的優化，where語句後被優化為一個常量進行查詢
null MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡選取最小值可以通過單獨索引查找完成
- key: 顯示Mysql實際決定使用的索引
- key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
- ref: 表示上述表的連接匹配條件，即哪些列或常量被用於查找索引列上的值
- rows: 表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數
- filtered：返回結果的行佔需要讀到的行(rows列的值)的百分比(只對index和all的掃描有效)
- extra:不適合在其他字段中顯示，但是十分重要的額外信息
extra子項
MYSQL優化方案
- 索引
- 單表索引不超過6個；
- 單個索引不超過5列；
- innodb主鍵推薦自增列； 主鍵不該被修改； 字符串不應該做主鍵； 若不指定主鍵，innodb會使用唯一且非空值索引代替；
- 若是複合索引，區分度大的前置；
- 核心sql優先考慮索引；
- 區分度高的字段前置；
- 避免冗餘或重複索引：合理創建聯合索引：index(a,b,c)相當於：index(a),index(a,b),index(a,b,c)
- 不在低基數列建索引，如性別；
- 不在索引列進行數學、函數運算；
- 儘量不要使用外鍵；
- 不使用%前導查詢，如like "%xxx"，無法使用索引；
- 不使用反響查詢，如not in、not like，無法使用索引；
- 儘量要有主鍵。
- 字段
- 避免使用TEXT、BLOG類型；
- MySQL 內存臨時表不支持 TEXT、BLOB 這樣的大數據類型，如果查詢中包含這樣的數據，在排序等操作時，就不能使用內存臨時表，必須使用磁盤臨時表進行。而且對於這種數據，MySQL 還是要進行二次查詢，會使 sql 性能變得很差，但不是說一定不能使用這樣的數據類型。
- 將字符轉化為數字；
- 使用TINYINT代替ENUM類型；
- 字段長度儘量按照實際進行分配，不要隨意給一個大容量；
- 所有字段儘量not null;
- 索引 NULL 列需要額外的空間來保存，所以要佔用更多的空間；進行比較和計算時要對 NULL 值做特別的處理
- 使用unsigned存儲非負整數；
- INT類型固定佔用4個字節存儲；
- 小數類型為 decimal，禁止使用 float 和 double。
- 使用TIMESTAMP存儲時間；因為timestamp使用4字節，datetime使用8字節，同時timestamp具有自動複製以及自動更新的特性。
- 禁止在數據庫中存儲明文密碼
- SQL
- 禁止使用存儲過程、觸發器、視圖等；
- 讓數據庫做最擅長的事，降低業務耦合度；
- 用小表驅動大表，儘可能減少JOIN中Nested Loop（兩個表讀一行數據進行兩兩對比）的循環次數，避免使用大表的join；
- 避免數據庫中進行數學運算，數學運算和邏輯判斷，無法使用索引；
- 減少與數據庫的交互次數；
- 拒絕大sql，拆分成小sql，充分使用query cache，充分利用多核CPU.
- 使用in 代替 or,in 的值不超過1000個；
- 禁止使用order by rand(); 因為使用order by rand() 會將數據從磁盤讀取進行排序，耗費大量IO和CPU，可以再程序中獲取一個rand值，然後通過數據庫中獲取對應的值。
- 使用union all 而不是 union.
- 禁止單條sql語句同時更新多個表(跨表更新)；
- Update 中禁止使用left join
- 不使用select *;
- 不要使用count(常量)或者count(列名)來替代count(*),count(*)是SQL92定義的標準統計行數的語法，和數據庫無關，和NULL和非 NULL也無關，而 count(列名)不會統計此列為 NULL 值的行
- 禁止使用test庫;
- 默認情況下，mysql.db表中包含的行表示任意用戶可以訪問test數據庫和test_開頭的數據庫。這些行的User字段的值為空，表示匹配任意用戶。這意味著這些數據庫（test數據庫和test_開頭的數據庫）默認可以被任意用戶使用（即使沒有權限的用戶）
- 利用延遲關聯或者子查詢優化超多分頁場景
- 行為規範
- 禁止super權限應用賬號存在；
- 對單表的多次order必須合併為一次操作；
- 不在業務高峰期批量更新、查詢數據庫
閱讀更多 碼農的搬磚生涯 的文章
