【網(wǎng)站構(gòu)建】關(guān)系型數(shù)據(jù)庫(kù)MySQL

Start：關(guān)注本公眾號(hào)后，可直接聯(lián)系后臺(tái)獲取排版美化的詳細(xì)文檔！

Hints：本篇文章所編纂的資料均來自網(wǎng)絡(luò)，特此感謝參與奉獻(xiàn)的有關(guān)人員。

• MySQL常用架構(gòu)：主從熱備

https://blog.csdn.net/weixin_37838921/article/details/104033403

https://www.cnblogs.com/honeylemon/p/10591899.html

http://www.cppcns.com/shujuku/mysql/203618.html

• MySQL邏輯架構(gòu)

客戶端層：連接處理、授權(quán)認(rèn)證、安全管理

核心服務(wù)層：查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)（時(shí)間、數(shù)學(xué)、加密等函數(shù)）、存儲(chǔ)過程、觸發(fā)器、視圖

存儲(chǔ)引擎層：數(shù)據(jù)的存儲(chǔ)和提取

• MySQL工作過程

• 客戶端-服務(wù)端通信協(xié)議：

MySQL客戶端/服務(wù)端通信協(xié)議是“半雙工”的：在任一時(shí)刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。一旦一端開始發(fā)送消息，另一端要接收完整個(gè)消息才能響應(yīng)它，所以我們無法也無須將一個(gè)消息切成小塊獨(dú)立發(fā)送，也沒有辦法進(jìn)行流量控制。

客戶端用一個(gè)單獨(dú)的數(shù)據(jù)包將查詢請(qǐng)求發(fā)送給服務(wù)器，所以當(dāng)查詢語句很長(zhǎng)的時(shí)候，需要設(shè)置max_allowed_packet參數(shù)。但是需要注意的是，如果查詢實(shí)在是太大，服務(wù)端會(huì)拒絕接收更多數(shù)據(jù)并拋出異常。

與之相反的是，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會(huì)很多，由多個(gè)數(shù)據(jù)包組成。但是當(dāng)服務(wù)器響應(yīng)客戶端請(qǐng)求時(shí)，客戶端必須完整的接收整個(gè)返回結(jié)果，而不能簡(jiǎn)單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。因而在實(shí)際開發(fā)中，盡量保持查詢簡(jiǎn)單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個(gè)非常好的習(xí)慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。

• 查詢緩存：

在解析一個(gè)查詢語句前，如果查詢緩存是打開的，那么MySQL會(huì)檢查這個(gè)查詢語句是否命中查詢緩存中的數(shù)據(jù)。如果當(dāng)前查詢恰好命中查詢緩存，在檢查一次用戶權(quán)限后直接返回緩存中的結(jié)果。這種情況下，查詢不會(huì)被解析，也不會(huì)生成執(zhí)行計(jì)劃，更不會(huì)執(zhí)行。

MySQL將緩存存放在一個(gè)引用表（不要理解成table，可以認(rèn)為是類似于HashMap的數(shù)據(jù)結(jié)構(gòu)），通過一個(gè)哈希值索引，這個(gè)哈希值通過查詢本身、當(dāng)前要查詢的數(shù)據(jù)庫(kù)、客戶端協(xié)議版本號(hào)等一些可能影響結(jié)果的信息計(jì)算得來。所以兩個(gè)查詢?cè)谌魏巫址系牟煌ɡ纾嚎崭瘛⒆⑨專?，都?huì)導(dǎo)致緩存不會(huì)命中。

如果查詢中包含任何用戶自定義函數(shù)、存儲(chǔ)函數(shù)、用戶變量、臨時(shí)表、mysql庫(kù)中的系統(tǒng)表，其查詢結(jié)果都不會(huì)被緩存。比如函數(shù)NOW()或者CURRENT_DATE()會(huì)因?yàn)椴煌牟樵儠r(shí)間，返回不同的查詢結(jié)果，再比如包含CURRENT_USER或者CONNECION_ID()的查詢語句會(huì)因?yàn)椴煌挠脩舳祷夭煌慕Y(jié)果，將這樣的查詢結(jié)果緩存起來沒有任何的意義。

MySQL的查詢緩存系統(tǒng)會(huì)跟蹤查詢中涉及的每個(gè)表，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。正因?yàn)槿绱?，在任何的寫操作時(shí)，MySQL必須將對(duì)應(yīng)表的所有緩存都設(shè)置為失效。如果查詢緩存非常大或者碎片很多，這個(gè)操作就可能帶來很大的系統(tǒng)消耗，甚至導(dǎo)致系統(tǒng)僵死一會(huì)兒。而且查詢緩存對(duì)系統(tǒng)的額外消耗也不僅僅在寫操作，讀操作也不例外：

1任何的查詢語句在開始之前都必須經(jīng)過檢查，即使這條SQL語句永遠(yuǎn)不會(huì)命中緩存

2如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后，會(huì)將結(jié)果存入緩存，也會(huì)帶來額外的系統(tǒng)消耗

基于此，我們要知道并不是什么情況下查詢緩存都會(huì)提高系統(tǒng)性能，緩存和失效都會(huì)帶來額外消耗，只有當(dāng)緩存帶來的資源節(jié)約大于其本身消耗的資源時(shí)，才會(huì)給系統(tǒng)帶來性能提升。但要如何評(píng)估打開緩存是否能夠帶來性能提升是一件非常困難的事情，也不在本文討論的范疇內(nèi)。如果系統(tǒng)確實(shí)存在一些性能問題，可以嘗試打開查詢緩存，并在數(shù)據(jù)庫(kù)設(shè)計(jì)上做一些優(yōu)化，比如：

1用多個(gè)小表代替一個(gè)大表，注意不要過度設(shè)計(jì)

2批量插入代替循環(huán)單條插入

3合理控制緩存空間大小，一般來說其大小設(shè)置為幾十兆比較合適

4可以通過SQL_CACHE和SQL_NO_CACHE來控制某個(gè)查詢語句是否需要進(jìn)行緩存

語法解析和預(yù)處理：

MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成一顆對(duì)應(yīng)的解析樹。這個(gè)過程解析器主要通過語法規(guī)則來驗(yàn)證和解析。比如SQL中是否使用了錯(cuò)誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等。預(yù)處理則會(huì)根據(jù)MySQL規(guī)則進(jìn)一步檢查解析樹是否合法。比如檢查要查詢的數(shù)據(jù)表和數(shù)據(jù)列是否存在等等。

• 查詢優(yōu)化：

經(jīng)過語法解析和預(yù)處理后合法的語法樹將由優(yōu)化器轉(zhuǎn)化成查詢計(jì)劃。多數(shù)情況下，一條查詢可以有很多種執(zhí)行方式，最后都返回相應(yīng)的結(jié)果。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃。MySQL使用基于成本的優(yōu)化器，它嘗試預(yù)測(cè)一個(gè)查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最小的一個(gè)。在MySQL可以通過查詢當(dāng)前會(huì)話的last_query_cost的值來得到其計(jì)算當(dāng)前查詢的成本。

有非常多的原因會(huì)導(dǎo)致MySQL選擇錯(cuò)誤的執(zhí)行計(jì)劃，比如統(tǒng)計(jì)信息不準(zhǔn)確、不會(huì)考慮不受其控制的操作成本（用戶自定義函數(shù)、存儲(chǔ)過程）、MySQL認(rèn)為的最優(yōu)跟我們想的不一樣（我們希望執(zhí)行時(shí)間盡可能短，但MySQL值選擇它認(rèn)為成本小的，但成本小并不意味著執(zhí)行時(shí)間短）等等。

MySQL常見的優(yōu)化策略：

1重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢時(shí)，并不一定按照SQL中指定的順序進(jìn)行，但有一些技巧可以指定關(guān)聯(lián)順序）

2優(yōu)化MIN()和MAX()函數(shù)（找某列的最小值，如果該列有索引，只需要查找B+Tree索引最左端，反之則可以找到最大值，具體原理見下文）

3提前終止查詢（比如：使用Limit時(shí)，查找到滿足數(shù)量的結(jié)果集后會(huì)立即終止查詢）

4優(yōu)化排序（在老版本MySQL會(huì)使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內(nèi)存中對(duì)其排序，然后再根據(jù)排序結(jié)果去讀取數(shù)據(jù)行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序。對(duì)于I/O密集型應(yīng)用，效率會(huì)高很多）

• 查詢執(zhí)行引擎

在完成解析和優(yōu)化階段以后，MySQL會(huì)生成對(duì)應(yīng)的執(zhí)行計(jì)劃，查詢執(zhí)行引擎根據(jù)執(zhí)行計(jì)劃給出的指令逐步執(zhí)行得出結(jié)果。整個(gè)執(zhí)行過程的大部分操作均是通過調(diào)用存儲(chǔ)引擎實(shí)現(xiàn)的接口來完成，這些接口被稱為handler API。查詢過程中的每一張表由一個(gè)handler實(shí)例表示。實(shí)際上，MySQL在查詢優(yōu)化階段就為每一張表創(chuàng)建了一個(gè)handler實(shí)例，優(yōu)化器可以根據(jù)這些實(shí)例的接口來獲取表的相關(guān)信息，包括表的所有列名、索引統(tǒng)計(jì)信息等。存儲(chǔ)引擎接口提供了非常豐富的功能，但其底層僅有幾十個(gè)接口，這些接口像搭積木一樣完成了一次查詢的大部分操作。

• 完整工作過程：

1客戶端向MySQL服務(wù)器發(fā)送一條查詢請(qǐng)求

2服務(wù)器首先檢查查詢緩存，如果命中緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一階段【是否緩存，需要提前設(shè)置】

3服務(wù)器進(jìn)行SQL解析、預(yù)處理

4優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃

5MySQL根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢

6將結(jié)果返回給客戶端，同時(shí)緩存查詢結(jié)果

• MySQL關(guān)鍵概念
  

• 存儲(chǔ)引擎：存儲(chǔ)引擎就是特定的數(shù)據(jù)存儲(chǔ)格式

innodb擅長(zhǎng)處理并發(fā)的。因?yàn)樗褂昧诵屑?jí)鎖定，只該行鎖了，其它行沒有鎖。innodb擅長(zhǎng)事務(wù)、數(shù)據(jù)的完整性及高并發(fā)處理，不擅長(zhǎng)快速插入（插入前要排序，消耗時(shí)間）和檢索。

MyISAM：ISAM,Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統(tǒng)。高速查詢及插入。擅長(zhǎng)插入和查詢。

Archive：存檔型，僅提供插入和查詢操作。非常高效阻塞的插入和查詢。

Memory：內(nèi)存型，數(shù)據(jù)存儲(chǔ)于內(nèi)存中，存儲(chǔ)引擎。緩存型存儲(chǔ)引擎。

插件式存儲(chǔ)引擎：用C和C++開發(fā)的存儲(chǔ)引擎。

• 范式：

第一范式：具有原子性，確保每列保持原子性。

第二范式：主鍵列與非主鍵列遵循完全函數(shù)依賴關(guān)系，確保表中的每列都和主鍵相關(guān)。

第三范式：非主鍵列之間沒有傳遞函數(shù)依賴關(guān)系索引，確保每列都和主鍵列直接相關(guān),而不是間接相關(guān)。

逆范式是指打破范式，通過增加冗余或重復(fù)的數(shù)據(jù)來提高數(shù)據(jù)庫(kù)的性能。

• 字段類型：

字段類型盡可能?。ㄕ加么鎯?chǔ)空間少）、盡可能定長(zhǎng)（占用存儲(chǔ)空間固定）、盡可能使用整數(shù)。

（1）整型

MySQL數(shù)據(jù)庫(kù)支持五種整型類型，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五種。

整型類型占用空間和取值范圍

類型 字節(jié) 最小值 最大值

TINYINT 1 有符號(hào)：-128 無符號(hào)：0 有符號(hào)：127 無符號(hào)：255

SMALLINT 2有符號(hào)：-32768無符號(hào)：0有符號(hào)：32767無符號(hào)：65535

MEDIUMINT 3有符號(hào)：-8388608無符號(hào)：0有符號(hào)：8388607無符號(hào)：16777215

INT/INTEGER 4有符號(hào)：-2147483648無符號(hào)：0有符號(hào)：2147483647無符號(hào)：4294967295

BIGINT 8 有符號(hào)：-9223372036854775808無符號(hào)：0 有符號(hào)：9223372036854775807無符號(hào)：18446744073709551615

五種整型的適用場(chǎng)景：

TINYINT，年齡，包含在0~255之間；

SMALLINT，端口號(hào)，包含在0~65535之間；

MEDIUMINT，中小型網(wǎng)站注冊(cè)會(huì)員，1600萬夠用；

INT，身份證編號(hào)，42億可以用很久；

BIGINT，Twitter微博量，幾百億

（2）浮點(diǎn)型(非精確)

MySQL數(shù)據(jù)庫(kù)支持兩種浮點(diǎn)類型：FLOAT(單精度)和DOUBLE(雙精度)兩種

浮點(diǎn)型(非精確)占用空間和取值范圍

類型 字節(jié) 范圍

FLOAT 4 正數(shù)范圍：1.175494351E-38~3.402823466E+38，負(fù)數(shù)范圍：-3.402823466E+38~-1.175494351E-38

DOUBLE 8 正數(shù)范圍：1.7976931348623157E-308~2.2250738585072014E+308

負(fù)數(shù)范圍：-2.2250738585072014E+308~-1.7976931348623157E-308

（3）定點(diǎn)型(精確)

浮點(diǎn)型由于內(nèi)部的存儲(chǔ)方式是數(shù)值，導(dǎo)致它在一定程度上取得的是近似值而非精確值。如果使用定點(diǎn)型，那么就可以精確取得小數(shù)部分，因?yàn)樗鼉?nèi)部存儲(chǔ)方式是字符串形式。

定點(diǎn)型(精確)占用空間和取值范圍

類型 字節(jié) 范圍

DECIMAL/NUMERIC M+2 M最大65位，D最大30位。

創(chuàng)建一個(gè)定點(diǎn)型格式：DECIMAL(M,D)，表示小數(shù)點(diǎn)D位，整數(shù)部分M位及M位內(nèi)。

2.列類型之日期

MySQL數(shù)據(jù)庫(kù)中有五個(gè)可用的日期時(shí)間數(shù)據(jù)類型，分別為：DATE、DATETIME、TIME、YEAR、TIMESTAMP。

日期時(shí)間類型占用空間和取值范圍

類型 字節(jié) 最小值 最大值

YEAR 1 1901 2155

TIME 3 -838:59:59838:59:59

DATE 4 1000-01-01 9999-12-31

TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07

DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59

TIMESTAMP有幾個(gè)特點(diǎn)：

a.當(dāng)更新一條數(shù)據(jù)的時(shí)候，設(shè)置此類型根據(jù)當(dāng)前系統(tǒng)更新可自動(dòng)更新時(shí)間；

b.如果插入一條NULL，也會(huì)自動(dòng)插入當(dāng)前系統(tǒng)時(shí)間；

c.創(chuàng)建字段時(shí)，系統(tǒng)會(huì)自動(dòng)給一個(gè)默認(rèn)值；

d.會(huì)根據(jù)當(dāng)前時(shí)區(qū)來存儲(chǔ)和查詢時(shí)間，存儲(chǔ)時(shí)對(duì)當(dāng)前時(shí)區(qū)進(jìn)行轉(zhuǎn)換，查詢時(shí)再轉(zhuǎn)換為當(dāng)前的時(shí)區(qū)。

//查看當(dāng)前時(shí)區(qū)

SHOW VARIABLES LIKE 'time_zone';

//設(shè)置為東九區(qū)，查詢時(shí)間就會(huì)加1小時(shí)

SET time_zone='+9:00';

DATE占用3個(gè)字節(jié)，包含年月日，范圍和DATETIME一樣。DATE長(zhǎng)度是0，無法設(shè)置。

YEAR占用1個(gè)字節(jié)，包年年份，長(zhǎng)度默認(rèn)為4位，無法設(shè)置。

TIME占用3個(gè)字節(jié)，包含時(shí)分秒，長(zhǎng)度0到6之間，用于設(shè)置微秒。對(duì)于TIME的范圍的時(shí)是-838到838的原因，是因?yàn)?/span>TIME類型不但可以保存一天的時(shí)，還可以包含時(shí)間之間的間隔。

綜上考慮：使用datetime，當(dāng)然也可以使用int（11）來保存時(shí)間戳。

關(guān)于INT(11)存放時(shí)間戳的優(yōu)點(diǎn)如下：

a.INT占4個(gè)字節(jié)，DATETIME占8個(gè)字節(jié)；

b.INT存儲(chǔ)索引的空間比DATETIME小，查詢快，排序效率高；

c.在計(jì)算機(jī)時(shí)間差等范圍問題，比較方便。

3.列類型之字符

字符集校對(duì)規(guī)則utf8_general_ci表示校對(duì)時(shí)不區(qū)分大小寫，相對(duì)的cs表示區(qū)分大小寫。還有一個(gè)bin結(jié)尾的是字節(jié)比較。而general是地區(qū)名，這里是通用，utf8表示編碼。如果是gbk，可以使用gbk_chinese_ci，如果是utf8則用utf8_general。MySQL提供了多種對(duì)字符數(shù)據(jù)的存儲(chǔ)類型，包括：CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多種字符類型。

（1）CHAR是保存定長(zhǎng)字符串，而VARCHAR則是保存變長(zhǎng)字符串。CHAR(5)表示必須保存5個(gè)字符，而VARCHAR(5)則表示最大保存字符為5。如果是UTF8編碼下，長(zhǎng)度為5的CHAR類型，最多可以存儲(chǔ)15字節(jié)，也就是5個(gè)漢字的內(nèi)容。因?yàn)橐粋€(gè)漢字占3個(gè)字節(jié)。

由于CHAR類型是定長(zhǎng)，MySQL會(huì)根據(jù)定義的長(zhǎng)度進(jìn)行分配空間，在處理速度上比VARCHAR快的多，所以適合存儲(chǔ)例如手機(jī)、身份證這種定長(zhǎng)的字符，否則就會(huì)造成浪費(fèi)。那么CHAR類型最大可以插入255個(gè)字符，最多可以存儲(chǔ)765個(gè)字節(jié)。

（2）BINARY和VARBINARY是采用二進(jìn)制存儲(chǔ)的，沒有字符集概念，意義在于防止字符集的問題導(dǎo)致數(shù)據(jù)丟失，存儲(chǔ)中文會(huì)占用兩個(gè)字符，會(huì)亂碼，半截會(huì)問號(hào)。因?yàn)槭遣捎枚M(jìn)制存儲(chǔ)，在比較字符和排序的時(shí)候，都是二進(jìn)制進(jìn)行的，所以只有需要操作二進(jìn)制時(shí)才需要使用。

（3）八種適合文本內(nèi)容的大數(shù)據(jù)類型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。

綜上：短文本定長(zhǎng)用char，變長(zhǎng)用varchar，長(zhǎng)文本用text

4.列類型之屬性

無符號(hào)(UNSIGNED)和填充零(ZEROFILL)，還有是否為空、默認(rèn)值、主鍵、自動(dòng)編號(hào)。

嚴(yán)格模式

我們使用的是WAMP集成環(huán)境，默認(rèn)安裝的情況下，是非嚴(yán)格模式，用于部署階段。而開發(fā)調(diào)試階段，強(qiáng)烈建議使用嚴(yán)格模式，方便開發(fā)中調(diào)試將問題及時(shí)暴露出來。因?yàn)樵诜菄?yán)格模式下將NULL插入NOTNULL等非法操作都是被運(yùn)行的。設(shè)置嚴(yán)格模式只要打開my.ini文件，在末尾添加一句：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后，重啟服務(wù)器即可。檢查SQL_MODE狀態(tài)

SELECT @@global.sql_mode;

• 鎖：

鎖的概念：當(dāng)客戶端操作表（記錄）時(shí)，為了保證操作的隔離性（多個(gè)客戶端操作不能互相影響），通過加鎖來處理。

操作方面：

讀鎖：讀操作時(shí)增加的鎖，也叫共享鎖，S-lock。特征是阻塞其他客戶端的寫操作，不阻塞讀操作。（并發(fā)讀）

寫鎖：寫操作時(shí)增加的鎖，也叫獨(dú)占鎖或排他鎖，X-lock。特征是阻塞其他客戶端的讀，寫操作。

鎖定粒度（范圍）：

行級(jí)：提升并發(fā)性，鎖本身開銷大

表級(jí)：不利于并發(fā)性，鎖本身開銷小。

• 索引

• MySQL索引相關(guān)的數(shù)據(jù)結(jié)構(gòu)和算法：

MySQL索引采用的數(shù)據(jù)結(jié)構(gòu)是B+Tree, 而B+Tree就是一種多路搜索樹。

MySQL將每個(gè)節(jié)點(diǎn)的大小設(shè)置為一個(gè)頁的整數(shù)倍. 也就是在節(jié)點(diǎn)空間大小一定的情況下，每個(gè)節(jié)點(diǎn)可以存儲(chǔ)更多的內(nèi)結(jié)點(diǎn)，這樣每個(gè)結(jié)點(diǎn)能索引的范圍更大更精確。所有的葉子節(jié)點(diǎn)使用指針鏈接的好處是可以進(jìn)行區(qū)間訪問.

頁是計(jì)算機(jī)管理存儲(chǔ)器的邏輯塊，硬件及OS往往將主存和磁盤存儲(chǔ)區(qū)分割為連續(xù)的大小相等的塊，每個(gè)存儲(chǔ)塊稱為一頁（許多OS中，頁的大小通常為4K）。主存和磁盤以頁為單位交換數(shù)據(jù)。當(dāng)程序要讀取的數(shù)據(jù)不在主存中時(shí)，會(huì)觸發(fā)一個(gè)缺頁異常，此時(shí)系統(tǒng)會(huì)向磁盤發(fā)出讀盤信號(hào)，磁盤會(huì)找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁或幾頁載入內(nèi)存中，然后一起返回，程序繼續(xù)運(yùn)行。

MySQL巧妙利用了磁盤預(yù)讀原理，將一個(gè)節(jié)點(diǎn)的大小設(shè)為等于一個(gè)頁，這樣每個(gè)節(jié)點(diǎn)只需要一次I/O就可以完全載入。為了達(dá)到這個(gè)目的，每次新建節(jié)點(diǎn)時(shí)，直接申請(qǐng)一個(gè)頁的空間，這樣就保證一個(gè)節(jié)點(diǎn)物理上也存儲(chǔ)在一個(gè)頁里，加之計(jì)算機(jī)存儲(chǔ)分配都是按頁對(duì)齊的，就實(shí)現(xiàn)了讀取一個(gè)節(jié)點(diǎn)只需一次I/O。假設(shè)B+Tree的高度為h，一次檢索最多需要h-1次I/O（根節(jié)點(diǎn)常駐內(nèi)存），復(fù)雜度O(h) = O(logmN)。實(shí)際應(yīng)用場(chǎng)景中，M通常較大，常常超過100，因此樹的高度一般都比較小，通常不超過3。

索引首先根據(jù)第一個(gè)字段來排列順序，當(dāng)名字相同時(shí)，則根據(jù)第三個(gè)字段，即出生日期來排序，正是因?yàn)檫@個(gè)原因，才有了索引的“最左原則”。

• 索引設(shè)計(jì)：

從索引的定義方式和用途中來看：主鍵索引，唯一索引，普通索引，全文索引。

無論任何類型，都是通過建立關(guān)鍵字與位置的對(duì)應(yīng)關(guān)系來實(shí)現(xiàn)的。索引是通過關(guān)鍵字找對(duì)應(yīng)的記錄的地址。

以上類型的差異：對(duì)索引關(guān)鍵字的要求不同。

關(guān)鍵字：記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段，某些字段，某個(gè)字段的一部分）。

普通索引,index：對(duì)關(guān)鍵字沒有要求。

唯一索引,unique index：要求關(guān)鍵字不能重復(fù)。同時(shí)增加唯一約束。

主鍵索引,primary key：要求關(guān)鍵字不能重復(fù)，也不能為NULL。同時(shí)增加主鍵約束。

全文索引,fulltext key：關(guān)鍵字的來源不是所有字段的數(shù)據(jù)，而是從字段中提取的特別關(guān)鍵詞。

 關(guān)鍵字含義：可以是某個(gè)字段，也可以是某些字段。如果一個(gè)索引通過在多個(gè)字段上提取的關(guān)鍵字，稱之為復(fù)合索引。 命令:alter table exp add index (field1, field2);

PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個(gè)，唯一索引可以創(chuàng)建多個(gè)。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。

索引原則：如果索引不遵循使用原則，則可能導(dǎo)致索引無效。

（1）列獨(dú)立

如果需要某個(gè)字段上使用索引，則需要在字段參與的表達(dá)中，保證字段獨(dú)立在一側(cè)。

（2）左原則

Like：匹配模式必須要左邊確定不能以通配符開頭。

（3）OR的使用

必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。

（4）MySQL智能選擇

即使?jié)M足了上面說原則，MySQL也能棄用索引：查詢即使使用索引，會(huì)導(dǎo)致出現(xiàn)大量的隨機(jī)IO，相對(duì)于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開銷，還要大。

綜上歸納：

a、不要過度索引。索引越多，占用空間越大，反而性能變慢；

b.只對(duì)WHERE子句中頻繁使用的建立索引；

c.盡可能使用唯一索引，重復(fù)值越少，索引效果越強(qiáng)；

d.使用短索引，如果char(255)太大，應(yīng)該給它指定一個(gè)前綴長(zhǎng)度，大部分情況下前10位或20位值基本是唯一的，那么就不要對(duì)整個(gè)列進(jìn)行索引；

e.充分利用左前綴，這是針對(duì)復(fù)合索引，因?yàn)?/span>WHERE語句如果有AND并列，只能識(shí)別一個(gè)索引(獲取記錄最少的那個(gè))，索引需要使用復(fù)合索引，那么應(yīng)該將WHERE最頻繁的放置在左邊。

f.索引存在，如果沒有滿足使用原則，也會(huì)導(dǎo)致索引無效： 

5.索引的使用場(chǎng)景

（1）索引檢索：檢索數(shù)據(jù)時(shí)使用索引。

（2）索引排序

如果order by 排序需要的字段上存在索引，則可能使用到索引。

（3）索引覆蓋

索引擁有的關(guān)鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)，此時(shí)，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù)，僅僅在索引區(qū)即可。覆蓋就是直接在索引區(qū)獲取內(nèi)容，而不需要在數(shù)據(jù)區(qū)獲取。

6.前綴索引

前綴索引是建立索引關(guān)鍵字一種方案。通常會(huì)使用字段的整體作為索引關(guān)鍵字。有時(shí)，即使使用字段前部分?jǐn)?shù)據(jù)，也可以去識(shí)別某些記錄。就比如一個(gè)班級(jí)里，我要找王xx，假如姓王的只有1個(gè)人，那么就可以建一個(gè)前綴索引，就是王。

7.全文索引

該類型的索引特殊在：關(guān)鍵字的創(chuàng)建上。是為了解決 like‘%keyword%’這類查詢的匹配問題。（mysql的全文索引幾乎不用，因?yàn)樗恢С种形?，我們?yīng)該使用sphinx全文索引）

• MySQL性能優(yōu)化

• 整體優(yōu)化方向：

設(shè)計(jì)：存儲(chǔ)引擎，字段類型，范式與逆范式

功能：索引，緩存，分區(qū)分表。

架構(gòu)：主從復(fù)制，讀寫分離，負(fù)載均衡。

合理SQL：測(cè)試，經(jīng)驗(yàn)。

• 具體優(yōu)化建議：

1不要輕易打開查詢緩存，特別是寫密集型應(yīng)用。如果需要，可以將query_cache_type設(shè)置為DEMAND，這時(shí)只有加入SQL_CACHE的查詢才會(huì)走緩存，其他查詢則不會(huì)，這樣可以非常自由地控制哪些查詢需要被緩存。

2選擇數(shù)據(jù)類型只要遵循小而簡(jiǎn)單的原則就好，越小的數(shù)據(jù)類型通常會(huì)更快，占用更少的磁盤、內(nèi)存，處理時(shí)需要的CPU周期也更少。越簡(jiǎn)單的數(shù)據(jù)類型在計(jì)算時(shí)需要更少的CPU周期，比如，整型就比字符操作代價(jià)低，因而會(huì)使用整型來存儲(chǔ)ip地址，使用DATETIME來存儲(chǔ)時(shí)間，而不是使用字符串

3 把可為NULL的列改為NOT NULL不會(huì)對(duì)性能提升有多少幫助，只是如果計(jì)劃在列上創(chuàng)建索引，就應(yīng)該將該列設(shè)置為NOT NULL。

4對(duì)整數(shù)類型指定寬度，比如INT(11)，沒有任何卵用。INT使用32位（4個(gè)字節(jié)）存儲(chǔ)空間，那么它的表示范圍已經(jīng)確定，所以INT(1)和INT(20)對(duì)于存儲(chǔ)和計(jì)算是相同的。

5UNSIGNED表示不允許負(fù)值，大致可以使正數(shù)的上限提高一倍。比如TINYINT存儲(chǔ)范圍是-128 ~ 127，而UNSIGNED TINYINT存儲(chǔ)的范圍卻是0 - 255。

5沒有太大的必要使用DECIMAL數(shù)據(jù)類型。即使是在需要存儲(chǔ)財(cái)務(wù)數(shù)據(jù)時(shí)，仍然可以使用BIGINT。比如需要精確到萬分之一，那么可以將數(shù)據(jù)乘以一百萬然后使用BIGINT存儲(chǔ)。這樣可以避免浮點(diǎn)數(shù)計(jì)算不準(zhǔn)確和DECIMAL精確計(jì)算代價(jià)高的問題。

6 TIMESTAMP使用4個(gè)字節(jié)存儲(chǔ)空間，DATETIME使用8個(gè)字節(jié)存儲(chǔ)空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范圍小得多，而且TIMESTAMP的值因時(shí)區(qū)不同而不同。

7大多數(shù)情況下沒有使用枚舉類型的必要，其中一個(gè)缺點(diǎn)是枚舉的字符串列表是固定的，添加和刪除字符串（枚舉選項(xiàng)）必須使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

8盡量避免使用SELECT *，schema的列不要太多。原因是存儲(chǔ)引擎的API工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層之間通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列，這個(gè)轉(zhuǎn)換過程的代價(jià)是非常高的。如果列太多而實(shí)際使用的列又很少的話，有可能會(huì)導(dǎo)致CPU占用過高。

9盡量加上LIMIT限制查詢返回?cái)?shù)量

10索引是提高MySQL查詢性能的一個(gè)重要途徑，但過多的索引可能會(huì)導(dǎo)致過高的磁盤使用率以及過高的內(nèi)存占用，從而影響應(yīng)用程序的整體性能。

11 MySQL不會(huì)使用索引的情況：“獨(dú)立的列”是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。比如：select * from where id + 1 = 5

12使用前綴索引，如果列很長(zhǎng)，通?？梢运饕_始的部分字符，這樣可以有效節(jié)約索引空間，從而提高索引效率。

13 多列索引和索引順序問題：在多數(shù)情況下，在多個(gè)列上建立獨(dú)立的索引并不能提高查詢性能。理由非常簡(jiǎn)單，MySQL不知道選擇哪個(gè)索引的查詢效率更好，所以在老版本，比如MySQL5.0之前就會(huì)隨便選擇一個(gè)列的索引，而新的版本會(huì)采用合并索引的策略。

select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1

老版本的MySQL會(huì)隨機(jī)選擇一個(gè)索引，但新版本做如下的優(yōu)化：

select film_id,actor_id from film_actor where actor_id = 1  

union all

select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1

-出現(xiàn)多個(gè)索引做相交操作時(shí)（多個(gè)AND條件），通常來說一個(gè)包含所有相關(guān)列的索引要優(yōu)于多個(gè)獨(dú)立索引。

-出現(xiàn)多個(gè)索引做聯(lián)合操作時(shí)（多個(gè)OR條件），對(duì)結(jié)果集的合并、排序等操作需要耗費(fèi)大量的CPU和內(nèi)存資源，特別是當(dāng)其中的某些索引的選擇性不高，需要返回合并大量數(shù)據(jù)時(shí)，查詢成本更高。所以這種情況下還不如走全表掃描

索引的順序?qū)τ诓樵兪侵陵P(guān)重要的，很明顯應(yīng)該把選擇性更高的字段放到索引的前面，這樣通過第一個(gè)字段就可以過濾掉大多數(shù)不符合條件的數(shù)據(jù)。

14避免多個(gè)范圍條件

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;

這個(gè)查詢有一個(gè)問題：它有兩個(gè)范圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無法同時(shí)使用它們。

覆蓋索引：

如果一個(gè)索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因?yàn)椴樵冎恍枰獟呙杷饕龝?huì)帶來許多好處：

-條目遠(yuǎn)小于數(shù)據(jù)行大小，如果只讀取索引，極大減少數(shù)據(jù)訪問量

-引是有按照列值順序存儲(chǔ)的，對(duì)于I/O密集型的范圍查詢要比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的IO要少的多

16 使用索引掃描來排序：掃描索引本身很快，因?yàn)橹恍枰獜囊粭l索引記錄移動(dòng)到相鄰的下一條記錄。但如果索引本身不能覆蓋所有需要查詢的列，那么就不得不每掃描一條索引記錄就回表查詢一次對(duì)應(yīng)的行。這個(gè)讀取操作基本上是隨機(jī)I/O，因此按照索引順序讀取數(shù)據(jù)的速度通常要比順序地全表掃描要慢。

在設(shè)計(jì)索引時(shí)，如果一個(gè)索引既能夠滿足排序，又滿足查詢，是最好的。

只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向也一樣時(shí)，才能夠使用索引來對(duì)結(jié)果做排序。如果查詢需要關(guān)聯(lián)多張表，則只有ORDER BY子句引用的字段全部為第一張表時(shí)，才能使用索引做排序。ORDER BY子句和查詢的限制是一樣的，都要滿足最左前綴的要求（有一種情況例外，就是最左的列被指定為常數(shù)，下面是一個(gè)簡(jiǎn)單的示例），其他情況下都需要執(zhí)行排序操作，而無法利用索引排序。

17冗余和重復(fù)索引：冗余索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引，應(yīng)當(dāng)盡量避免這種索引，發(fā)現(xiàn)后立即刪除。比如有一個(gè)索引(A,B)，再創(chuàng)建索引(A)就是冗余索引。冗余索引經(jīng)常發(fā)生在為表添加新索引時(shí)，比如有人新建了索引(A,B)，但這個(gè)索引不是擴(kuò)展已有的索引(A)。

18 刪除長(zhǎng)期未使用的索引：索引并不總是最好的工具，只有當(dāng)索引幫助提高查詢速度帶來的好處大于其帶來的額外工作時(shí)，索引才是有效的。對(duì)于非常小的表，簡(jiǎn)單的全表掃描更高效。對(duì)于中到大型的表，索引就非常有效。對(duì)于超大型的表，建立和維護(hù)索引的代價(jià)隨之增長(zhǎng)，這時(shí)候其他技術(shù)也許更有效，比如分區(qū)表。

19如果要統(tǒng)計(jì)行數(shù)，直接使用COUNT(*)，意義清晰，且性能更好。

20 優(yōu)化關(guān)聯(lián)查詢：

- 確保ON和USING字句中的列上有索引。在創(chuàng)建索引的時(shí)候就要考慮到關(guān)聯(lián)的順序。當(dāng)表A和表B用列c關(guān)聯(lián)的時(shí)候，如果優(yōu)化器關(guān)聯(lián)的順序是A、B，那么就不需要在A表的對(duì)應(yīng)列上創(chuàng)建索引。沒有用到的索引會(huì)帶來額外的負(fù)擔(dān)，一般來說，除非有其他理由，只需要在關(guān)聯(lián)順序中的第二張表的相應(yīng)列上創(chuàng)建索引（具體原因下文分析）。

- 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列，這樣MySQL才有可能使用索引來優(yōu)化。

當(dāng)前MySQL關(guān)聯(lián)執(zhí)行的策略非常簡(jiǎn)單，它對(duì)任何的關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作，即先在一個(gè)表中循環(huán)取出單條數(shù)據(jù)，然后在嵌套循環(huán)到下一個(gè)表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止。

21優(yōu)化LIMIT分頁

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

如果這張表非常大，那么這個(gè)查詢最好改成下面的樣子：

SELECT film.film_id,film.description

FROM film INNER JOIN (

   SELECT film_id FROM film ORDER BY title LIMIT 50,5

) AS tmp USING(film_id);

這里的延遲關(guān)聯(lián)將大大提升查詢效率，讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后在根據(jù)關(guān)聯(lián)列回原表查詢所需要的列。

22優(yōu)化UNION

MySQL處理UNION的策略是先創(chuàng)建臨時(shí)表，然后再把各個(gè)查詢結(jié)果插入到臨時(shí)表中，最后再來做查詢。因此很多優(yōu)化策略在UNION查詢中都沒有辦法很好的時(shí)候。經(jīng)常需要手動(dòng)將WHERE、LIMIT、ORDER BY等字句“下推”到各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件先優(yōu)化。

除非確實(shí)需要服務(wù)器去重，否則就一定要使用UNION ALL，如果沒有ALL關(guān)鍵字，MySQL會(huì)給臨時(shí)表加上DISTINCT選項(xiàng)，這會(huì)導(dǎo)致整個(gè)臨時(shí)表的數(shù)據(jù)做唯一性檢查，這樣做的代價(jià)非常高。當(dāng)然即使使用ALL關(guān)鍵字，MySQL總是將結(jié)果放入臨時(shí)表，然后再讀出，再返回給客戶端。雖然很多時(shí)候沒有這個(gè)必要，比如有時(shí)候可以直接把每個(gè)子查詢的結(jié)果返回給客戶端。

• 參考鏈接

https://blog.csdn.net/weixin_37838921/article/details/104033403

https://www.cnblogs.com/honeylemon/p/10591899.html

http://www.cppcns.com/shujuku/mysql/203618.html

https://www.cnblogs.com/sharpest/p/10390035.html

https://zhuanlan.zhihu.com/p/59818056

https://blog.csdn.net/qq_35642036/article/details/82820129

https://www.cnblogs.com/eric-fang/p/9285093.html

公眾號(hào)二維碼

End：如果有興趣了解金融量化交易和其他數(shù)據(jù)分析的實(shí)用技術(shù)，歡迎關(guān)注本公眾號(hào)