邏輯架構(gòu)

MySQL邏輯架構(gòu)圖

MySQL邏輯架構(gòu)整體分為三層，最上層為客戶層，
并非MySQL所獨(dú)有，諸如，連接處理、授權(quán)認(rèn)證、
安全等功能均在這一層處理。
MySQL大多數(shù)核心服務(wù)均在中間這一層，包括查
詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)（時間、數(shù)學(xué)、
加密等），所有的跨存儲引擎的功能也在這一層實(shí)現(xiàn)：
存儲過程、觸發(fā)器、視圖等。
最下層為存儲引擎，其負(fù)責(zé)MySQL中的數(shù)據(jù)存儲
和提取，中間的服務(wù)層通過API與存儲引擎通信，這
些API接口屏蔽了不同存儲引擎的差異。

查詢過程

查詢過程時序圖

存儲引擎

支持的9種存儲引擎

從上圖我們可以查看出當(dāng)前版本的MySQL 默認(rèn)存儲引擎是InnoDB, 其實(shí)在5.5版本之前，MyISAM是它的默認(rèn)引擎，之后是InnoDB。我們項(xiàng)目用的是8.0.17 。

主流的引擎就兩種：innodb和myisam

MyISAM和InnoDB區(qū)別

MyISAM不支持事務(wù)，而InnoDB支持。
Myisam可以沒有主鍵，InnoDB表必須有主鍵（用戶沒有指定的話會自己找或生產(chǎn)一個主鍵）
而 MyISAM表不支持外鍵，InnoDB支持
MyISAM鎖的粒度是表級，而InnoDB支持行（默認(rèn)），表級鎖。
（InnoDB的行鎖是實(shí)現(xiàn)在索引上的，而不是鎖在物理行記錄上。潛臺詞是，如果訪問沒有命中索引，也無法使用行鎖，將要退化為表鎖）
MyISAM支持全文類型索引，而InnoDB不支持全文索引。(mysql 5.7后innodb支持全文索引)
InnoDB不保存表的具體行數(shù)，執(zhí)行select count(*) from table時需要全表掃描。而MyISAM用一個變量保存了整個表的行數(shù)，執(zhí)行上述語句時只需要讀出該變量即可，速度很快（注意不能加有任何WHERE條件）
清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表。
Innodb存儲文件有frm、ibd，而Myisam是frm、myd(my data)、myi(my index)
Innodb：frm是表定義文件，ibd是數(shù)據(jù)文件
Myisam：frm是表定義文件，myd是數(shù)據(jù)文件，myi是索引文件
MyISAM相對簡單，所以在效率上要優(yōu)于InnoDB，小型應(yīng)用可以考慮使用MyISAM。當(dāng)你的數(shù)據(jù)庫有大量的寫入、更新操作而查詢比較少或者數(shù)據(jù)完整性要求比較高的時候就選擇innodb表。當(dāng)你的數(shù)據(jù)庫主要以查詢?yōu)橹鳎啾容^而言更新和寫入比較少，并且業(yè)務(wù)方面數(shù)據(jù)完整性要求不那么嚴(yán)格，就選擇myisam表。

《MySQL高性能》上面有一句話這樣寫到:

不要輕易相信“MyISAM比InnoDB快”之類的經(jīng)驗(yàn)之談，這個結(jié)論往往不是絕對的。在很多我們已知場景中，InnoDB的速度都可以讓MyISAM望塵莫及，尤其是用到了聚簇索引，或者需要訪問的數(shù)據(jù)都可以放入內(nèi)存的應(yīng)用。

一般情況下我們選擇 InnoDB 都是沒有問題的，但是某些情況下你并不在乎可擴(kuò)展能力和并發(fā)能力，也不需要事務(wù)支持，也不在乎崩潰后的安全恢復(fù)問題的話，選擇MyISAM也是一個不錯的選擇。但是一般情況下，我們都是需要考慮到這些問題的。

MYSQL數(shù)據(jù)類型

MySQL支持多種類型，大致可以分為三類：數(shù)值、日期/時間和字符串(字符)類型

數(shù)值類型

數(shù)值類型

注：

M 的值跟 int(M) 所占多少存儲空間并無任何關(guān)系
TINYINT(M), M默認(rèn)為4;
SMALLINT(M), M默認(rèn)為6;
MEDIUMINT(M), M默認(rèn)為9;
INT(M),M默認(rèn)為11,其實(shí)如果我們明確了無符號，那么可以設(shè)置為10，因?yàn)樯倭朔栁?
BIGINT(M),M默認(rèn)為20.
M表示最大顯示寬度,不是用來限制INT列內(nèi)保存值的范圍的。建表若設(shè)置了zerofill(0填充), 會在數(shù)字前面補(bǔ)充0. int(M)的最大值和最小值與UNSIGNED有關(guān)

日期類型

日期類型

注：

每種日期時間類型都有一個有效值范圍，如果超出這個范圍，在默認(rèn)的SQLMode下，系統(tǒng)會進(jìn)行錯誤示，并將以“零值”來進(jìn)行存儲。
datetime : 0000-00-00 00:00:00
date :0000-00-00
timestamp:00000000000000

字符/字符串類型

字符/字符串類型

注：

char(N)用于存放固定長度的字符串，長度最大為255，比指定長度大的值將被截斷，而比指定長度小的值將會用空格進(jìn)行填補(bǔ)；
varchar(N)用于保存可變長度的字符串，長度最大為65535，只存儲字符串實(shí)際需要的長度，它會增加一個額外字節(jié)來保存字符串本身的長度，varchar使用額外的1~2字節(jié)來存儲值得長度，如果列的最大長度小于或等于255，則使用1字節(jié)，否則就是使用2字節(jié)；（1個字節(jié)占8位，2的8次方是256（-128~127）；2個字節(jié)占16位，2的16次方為65536）
char和varchar跟字符編碼也有密切聯(lián)系，lantin1占用1個字節(jié)，gbk占用2個字節(jié)，utf8占用3個字節(jié)。

索引失效場景

當(dāng)查詢條件存在隱式轉(zhuǎn)換（字符串沒加單引號，數(shù)字加了單引號）
索引失效使用like時通配符在前
在查詢條件（只要有一個條件列沒索引）中使用OR
對索引列進(jìn)行函數(shù)或者數(shù)學(xué)運(yùn)算
聯(lián)合索引未遵循最左前綴原則
范圍條件右邊的列索引失效（< ,> between and）
例如INDEX（a,b,c）,where a=1 and b>2 and c=3,c是不走索引的
使用不等于（<>,!=）
is null或者is not null

a.單列索引無法儲null值，復(fù)合索引無法儲全為null的值。

b.查詢時，采用is null條件時，不能利用到索引，只能全表掃描。

為什么索引列無法存儲Null值？

①.索引是有序的。NULL值進(jìn)入索引時，無法確定其應(yīng)該放在哪里。（將索引列值進(jìn)行建樹，其中必然涉及到諸多的比較操作，null 值是不確定值無法比較，無法確定null出現(xiàn)在索引樹的葉子節(jié)點(diǎn)位置。）　

②.如果需要把空值存入索引，方法有二：其一，把NULL值轉(zhuǎn)為一個特定的值，在WHERE中檢索時，用該特定值查找。其二，建立一個復(fù)合索引。例如　create index IDX on table(col1,1); 通過在復(fù)合索引中指定一個非空常量值，而使構(gòu)成索引的列的組合中，不可能出現(xiàn)全空值。

左連接查詢或者右連接查詢,查詢關(guān)聯(lián)的字段編碼格式不一樣
如果mysql覺得全表掃描更快時（數(shù)據(jù)少）

Explain查看執(zhí)行計劃后主要字段詳解：

id: SQL執(zhí)行的順序的標(biāo)識,SQL從大到小的執(zhí)行,先執(zhí)行的語句編號大

1、id相同：按從上到下的順序執(zhí)行

2、id不同：按id從大到小執(zhí)行

3、id部分不同：先執(zhí)行id大的，id相同的，按從上到下的順序執(zhí)行

select_type:就是select的類型

SIMPLE簡單的查詢，沒有子查詢，也沒有union

PRIMAR主查詢，有子查詢的外邊的查詢

UNIONUNION中的第二個或者后邊的select

DEPENDENT UNION 指子查詢中的第二個或者后邊的select

UNION RESULTUNION 的整個查詢

SUBQUERY子查詢中的第一個SELECT

DEPENDENT SUBQUERY子查詢中的第一個SELECT，取決于外面的查詢

DERIVED派生表的SELECT, FROM子句的子查詢

table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
partitions:分區(qū)
possible_keys：顯示可能應(yīng)用在這張表中的索引
type:

mysql在表中找到需要的行的方式，也叫訪問類型（好的sql至少達(dá)到range級別，最好能達(dá)到ref）

all全表掃描

index全索引掃描

range給定索引范圍進(jìn)行掃描

ref表示連接，即掃描條件是某個常量，或者列

eq_ref類似于ref，區(qū)別是所使用的索引是唯一索引不存在相同的值

const,system 在查詢時mysql的優(yōu)化，where語句后被優(yōu)化為一個常量進(jìn)行查詢

null MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨(dú)索引查找完成

key: 顯示Mysql實(shí)際決定使用的索引
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
rows: 表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)
filtered：返回結(jié)果的行占需要讀到的行(rows列的值)的百分比(只對index和all的掃描有效)
extra:不適合在其他字段中顯示，但是十分重要的額外信息

extra子項(xiàng)

MYSQL優(yōu)化方案

索引

單表索引不超過6個；
單個索引不超過5列；
innodb主鍵推薦自增列；主鍵不該被修改；字符串不應(yīng)該做主鍵；若不指定主鍵，innodb會使用唯一且非空值索引代替；
若是復(fù)合索引，區(qū)分度大的前置；
核心sql優(yōu)先考慮索引；
區(qū)分度高的字段前置；
避免冗余或重復(fù)索引：合理創(chuàng)建聯(lián)合索引：index(a,b,c)相當(dāng)于：index(a),index(a,b),index(a,b,c)
不在低基數(shù)列建索引，如性別；
不在索引列進(jìn)行數(shù)學(xué)、函數(shù)運(yùn)算；
盡量不要使用外鍵；
不使用%前導(dǎo)查詢，如like "%xxx"，無法使用索引；
不使用反響查詢，如not in、not like，無法使用索引；
盡量要有主鍵。

字段

避免使用TEXT、BLOG類型；
MySQL 內(nèi)存臨時表不支持 TEXT、BLOB 這樣的大數(shù)據(jù)類型，如果查詢中包含這樣的數(shù)據(jù)，在排序等操作時，就不能使用內(nèi)存臨時表，必須使用磁盤臨時表進(jìn)行。而且對于這種數(shù)據(jù)，MySQL 還是要進(jìn)行二次查詢，會使 sql 性能變得很差，但不是說一定不能使用這樣的數(shù)據(jù)類型。
將字符轉(zhuǎn)化為數(shù)字；
使用TINYINT代替ENUM類型；
字段長度盡量按照實(shí)際進(jìn)行分配，不要隨意給一個大容量；
所有字段盡量not null;
索引 NULL 列需要額外的空間來保存，所以要占用更多的空間；進(jìn)行比較和計算時要對 NULL 值做特別的處理
使用unsigned存儲非負(fù)整數(shù)；
INT類型固定占用4個字節(jié)存儲；
小數(shù)類型為 decimal，禁止使用 float 和 double。
使用TIMESTAMP存儲時間；因?yàn)閠imestamp使用4字節(jié)，datetime使用8字節(jié)，同時timestamp具有自動復(fù)制以及自動更新的特性。
禁止在數(shù)據(jù)庫中存儲明文密碼

SQL

禁止使用存儲過程、觸發(fā)器、視圖等；
讓數(shù)據(jù)庫做最擅長的事，降低業(yè)務(wù)耦合度；
用小表驅(qū)動大表，盡可能減少JOIN中Nested Loop（兩個表讀一行數(shù)據(jù)進(jìn)行兩兩對比）的循環(huán)次數(shù)，避免使用大表的join；
避免數(shù)據(jù)庫中進(jìn)行數(shù)學(xué)運(yùn)算，數(shù)學(xué)運(yùn)算和邏輯判斷，無法使用索引；
減少與數(shù)據(jù)庫的交互次數(shù)；
拒絕大sql，拆分成小sql，充分使用query cache，充分利用多核CPU.
使用in 代替 or,in 的值不超過1000個；
禁止使用order by rand(); 因?yàn)槭褂胦rder by rand() 會將數(shù)據(jù)從磁盤讀取進(jìn)行排序，耗費(fèi)大量IO和CPU，可以再程序中獲取一個rand值，然后通過數(shù)據(jù)庫中獲取對應(yīng)的值。
使用union all 而不是 union.
禁止單條sql語句同時更新多個表(跨表更新)；
Update 中禁止使用left join
不使用select *;
不要使用count(常量)或者count(列名)來替代count(*),count(*)是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計行數(shù)的語法，和數(shù)據(jù)庫無關(guān)，和NULL和非 NULL也無關(guān)，而 count(列名)不會統(tǒng)計此列為 NULL 值的行
禁止使用test庫;
默認(rèn)情況下，mysql.db表中包含的行表示任意用戶可以訪問test數(shù)據(jù)庫和test_開頭的數(shù)據(jù)庫。這些行的User字段的值為空，表示匹配任意用戶。這意味著這些數(shù)據(jù)庫（test數(shù)據(jù)庫和test_開頭的數(shù)據(jù)庫）默認(rèn)可以被任意用戶使用（即使沒有權(quán)限的用戶）