邏輯架構

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_開頭的數據庫）默認可以被任意用戶使用（即使沒有權限的用戶）