1、三范式

• 第一范式：每個(gè)表的每一列都要保持它的原子性，也就是表的每一列是不可分割的；
• 第二范式：在滿足第一范式的基礎(chǔ)上，每個(gè)表都要保持唯一性，也就是表的非主鍵字段完全依賴于主鍵字段；
• 第三范式：在滿足第一范式和第二范式的基礎(chǔ)上，表中不能產(chǎn)生傳遞關(guān)系，要消除表中的冗余性；

2、字符集

字符集規(guī)定了字符在數(shù)據(jù)庫(kù)中的存儲(chǔ)格式，比如占多少空間，支持哪些字符等等。不同的字符集有不同的編碼規(guī)則，在有些情況下，甚至還有校對(duì)規(guī)則的存，校對(duì)規(guī)則是指一個(gè)字符集的排序，在運(yùn)維和使用MySQL數(shù)據(jù)庫(kù)中，選取合適的字符集非常重要，如果選擇不恰當(dāng)，輕則影響數(shù)據(jù)庫(kù)性能，嚴(yán)重的可能導(dǎo)致數(shù)據(jù)存儲(chǔ)亂碼。

常見(jiàn)的MySQl字符集主要有以下四種：

MySQL數(shù)據(jù)庫(kù)在開(kāi)發(fā)運(yùn)維中，字符集選用規(guī)則如下：

• 如果系統(tǒng)開(kāi)發(fā)面向國(guó)外業(yè)務(wù)，需要處理不同國(guó)家、不同語(yǔ)言，則應(yīng)該選擇utf-8或者utf8mb4；
• 如果只需要支持中文，沒(méi)有國(guó)外業(yè)務(wù)，則為了性能考慮，可以采用GBK；

3、自定義變量

自定義變量是一個(gè)用來(lái)存儲(chǔ)內(nèi)容的臨時(shí)容器，在連接MySQL的整個(gè)過(guò)程中都存在?？梢允褂胹et的方式定義。

SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;
SELECT id,name from user where create_time > @last_week;

使用自定義變量的注意事項(xiàng)：

• 使用自定義變量的查詢，無(wú)法使用緩存；
• 不能在使用常量或標(biāo)識(shí)符的地方使用自定義變量，比如表名、列名和limit子句中；
• 自定義變量的生命周期實(shí)在一個(gè)連接中有效，不能用它們做連接間的通信；

避免重復(fù)查詢剛剛更新的數(shù)據(jù)

如果在更新行的同時(shí)又想獲得該行的信息，要怎么做才能避免重復(fù)的查詢呢？

一般都這樣做：

update user set update_time = now() where id = 1;
select update_time from user where id = 1;

使用自定義變量可以對(duì)其進(jìn)行優(yōu)化：

update user set update_time = now() where id = 1 and @now := now();
select @now;

看上去還是兩個(gè)查詢，但是第二次查詢無(wú)須訪問(wèn)任何數(shù)據(jù)表，所以會(huì)快很多。

4、選擇優(yōu)化的數(shù)據(jù)類(lèi)型

MySQL支持的數(shù)據(jù)類(lèi)型非常多，選擇正確的數(shù)據(jù)類(lèi)型對(duì)于獲得高性能至關(guān)重要。

（1）更小的

一般情況下，應(yīng)該盡量使用較小的數(shù)據(jù)類(lèi)型，更小的數(shù)據(jù)類(lèi)型通常更快，因?yàn)檎加酶俚拇疟P(pán)、內(nèi)存和CPU緩存，處理時(shí)需要的CPU周期更短。

（2）更簡(jiǎn)單的

簡(jiǎn)單的數(shù)據(jù)類(lèi)型通常需要更少的CPU周期，整形比字符串類(lèi)型代價(jià)更低，因?yàn)樽址托ｒ?yàn)規(guī)則使字符比較比整形比較更復(fù)雜。

（3）盡量避免NULL

很多表都包含可為NULL的列，即使應(yīng)用程序并不需要保存NULL也是如此，因?yàn)榭蔀镹ULL是列的默認(rèn)屬性，通常情況下，最好指定列為NOT NULL。

如果查詢中包含可為NULL的列，對(duì)MySQL來(lái)說(shuō)更難優(yōu)化，因?yàn)榭蔀镹ULL的列使索引、索引統(tǒng)計(jì)和值的比較都更復(fù)雜?？蔀镹ULL的列會(huì)使用更多的存儲(chǔ)空間，在MySQL里也需要特殊處理，可為NULL的列被索引時(shí)，每個(gè)索引記錄需要一個(gè)額外的字節(jié)，在MyISAM里甚至還可能導(dǎo)致固定大小的索引變成可變大小的索引。

5、視圖

視圖（view）是一種虛擬存在的表，是一個(gè)邏輯表，本身并不包含數(shù)據(jù)。作為一個(gè)select語(yǔ)句保存在數(shù)據(jù)字典中的。對(duì)多張表的復(fù)雜查詢，使用視圖可以簡(jiǎn)化查詢，當(dāng)視圖使用臨時(shí)表時(shí)，無(wú)法使用where條件，也不能使用索引。

單表視圖一般用于查詢和修改，會(huì)改變基本表的數(shù)據(jù)，多表視圖一般用于查詢，不會(huì)改變基本表的數(shù)據(jù)。

使用視圖的目的是為了保障數(shù)據(jù)安全性，提高查詢效率。

視圖的優(yōu)勢(shì)

• 使用視圖的用戶完全不需要關(guān)心后面對(duì)應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件，對(duì)用戶來(lái)說(shuō)已經(jīng)是過(guò)濾好的復(fù)合條件的結(jié)果集。
• 使用視圖的用戶只能訪問(wèn)他們被允許查詢的結(jié)果集，對(duì)表的權(quán)限管理并不能限制到某個(gè)行某個(gè)列，但是通過(guò)視圖就可以簡(jiǎn)單的實(shí)現(xiàn)。
• 一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對(duì)用戶的影響，源表增加列對(duì)視圖沒(méi)有影響；源表修改列名，則可以通過(guò)修改視圖來(lái)解決，不會(huì)造成對(duì)訪問(wèn)者的影響。

6、緩存表和匯總表

有時(shí)提升性能最好的方法是在同一張表中保存衍生的冗余數(shù)據(jù)，有時(shí)候還需要?jiǎng)?chuàng)建一張完全獨(dú)立的匯總表或緩存表。

• 緩存表用來(lái)存儲(chǔ)那些獲取很簡(jiǎn)單，但速度較慢的數(shù)據(jù)；
• 匯總表用來(lái)保存使用group by語(yǔ)句聚合查詢的數(shù)據(jù)；

對(duì)于緩存表，如果主表使用InnoDB，用MyISAM作為緩存表的引擎將會(huì)得到更小的索引占用空間，并且可以做全文檢索。

在使用緩存表和匯總表時(shí)，必須決定是實(shí)時(shí)維護(hù)數(shù)據(jù)還是定期重建。哪個(gè)更好依賴于應(yīng)用程序，但是定期重建并不只是節(jié)省資源，也可以保持表不會(huì)有很多碎片，以及有完全順序組織的索引。

當(dāng)重建匯總表和緩存表時(shí)，通常需要保證數(shù)據(jù)在操作時(shí)依然可用，這就需要通過(guò)使用影子表來(lái)實(shí)現(xiàn)，影子表指的是一張?jiān)谡鎸?shí)表背后創(chuàng)建的表，當(dāng)完成了建表操作后，可以通過(guò)一個(gè)原子的重命名操作切換影子表和原表。

為了提升讀的速度，經(jīng)常建一些額外索引，增加冗余列，甚至是創(chuàng)建緩存表和匯總表，這些方法會(huì)增加寫(xiě)的負(fù)擔(dān)媽也需要額外的維護(hù)任務(wù)，但在設(shè)計(jì)高性能數(shù)據(jù)庫(kù)時(shí)，這些都是常見(jiàn)的技巧，雖然寫(xiě)操作變慢了，但更顯著地提高了讀的性能。

7、分區(qū)表

通常情況下，同一張表的數(shù)據(jù)在物理層面都是存放在一起的。隨著業(yè)務(wù)增長(zhǎng)，當(dāng)同一張表的數(shù)據(jù)量過(guò)大時(shí)，會(huì)帶來(lái)管理上的不便。而分區(qū)特性可以將一張表從物理層面根據(jù)一定的規(guī)則將數(shù)據(jù)劃分為多個(gè)分區(qū)，多個(gè)分區(qū)可以單獨(dú)管理，甚至存放在不同的磁盤(pán)/文件系統(tǒng)上，提升效率。

分區(qū)表的優(yōu)勢(shì)

• 數(shù)據(jù)可以跨磁盤(pán)存儲(chǔ)，適合存儲(chǔ)大量數(shù)據(jù)；
• 數(shù)據(jù)管理起來(lái)很方便，以分區(qū)為單位操作數(shù)據(jù)，不影響其他分區(qū)的正常運(yùn)行；
• 查詢時(shí)可以通過(guò)鎖定分區(qū)的特性，縮小查詢范圍，提高查詢性能；

8、外鍵

外鍵通常都要求每次在修改數(shù)據(jù)時(shí)都要在另外一張表中進(jìn)行一次額外的查詢操作，雖然InnoDB強(qiáng)制外鍵使用索引，但還是無(wú)法消除這種約束檢查的開(kāi)銷(xiāo)。如果外鍵的選擇性很低，則會(huì)導(dǎo)致一個(gè)選擇性很低的索引。

不過(guò)在某些場(chǎng)景下，外鍵會(huì)提升一些性能，比如想確保兩個(gè)相關(guān)表始終有一致的數(shù)據(jù)，那么使用外鍵比在應(yīng)用程序中檢查一致性的性能要高的多，此外。外鍵在相關(guān)數(shù)據(jù)的刪除和更新上，也比在應(yīng)用中維護(hù)要更高效，不過(guò)，外鍵維護(hù)操作時(shí)逐行進(jìn)行的，這樣的更新會(huì)比批量刪除和更新要慢些。

外鍵約束使查詢時(shí)額外訪問(wèn)一些別的表，也就是需要額外的鎖。如果向子表中寫(xiě)入一條記錄，外鍵約束會(huì)讓InnoDB檢查對(duì)應(yīng)的父表的記錄，也就是需要對(duì)父表的對(duì)應(yīng)記錄進(jìn)行加鎖操作，來(lái)確保這條記錄不會(huì)在這個(gè)事務(wù)完成之時(shí)就被刪除了。這會(huì)導(dǎo)致額外的鎖等待，甚至?xí)?dǎo)致一些死鎖。因?yàn)闆](méi)有直接訪問(wèn)這些表，所以這類(lèi)死鎖問(wèn)題很難排查。

所以，在目前的很多項(xiàng)目中，為了性能的考慮，已經(jīng)不使用外鍵了。

9、查詢緩存

MySQL查詢緩存保存查詢返回的完整結(jié)果，當(dāng)查詢命中該緩存，MySQL會(huì)立刻返回結(jié)果，跳過(guò)解析、優(yōu)化和執(zhí)行過(guò)程。

查詢緩存系統(tǒng)會(huì)跟蹤查詢中涉及的每個(gè)表，如果這些表發(fā)生變化，那么和這個(gè)表相關(guān)的所有的緩存數(shù)據(jù)都將失效，這種機(jī)制效率看起來(lái)比較低，因?yàn)閿?shù)據(jù)表變化時(shí)可能對(duì)查詢結(jié)果并沒(méi)有影響，但是這種簡(jiǎn)單實(shí)現(xiàn)代價(jià)很小，而這點(diǎn)對(duì)于一個(gè)非常繁忙的系統(tǒng)來(lái)說(shuō)非常重要。

（1）MySQL如何判斷緩存命中

判斷是否命中時(shí)，MySQL不會(huì)解析，而是直接使用SQL語(yǔ)句和客戶端發(fā)送過(guò)來(lái)的其它原始信息。任何字符上的不同，例如空格、注釋?zhuān)瑏G回導(dǎo)致緩存的不命中。通常使用統(tǒng)一的編碼規(guī)則是一個(gè)好的習(xí)慣，會(huì)讓你的系統(tǒng)運(yùn)行的更快。

當(dāng)查詢語(yǔ)句中有一些不確定的數(shù)據(jù)時(shí)，不會(huì)被緩存，比如函數(shù)now()。實(shí)際上，如果緩存中包含任何用戶自定義函數(shù)、存儲(chǔ)函數(shù)、用戶變量、臨時(shí)表、MySQL系統(tǒng)表、或者任何包含列級(jí)別權(quán)限的表，都不會(huì)被緩存。

（2）使用查詢緩存需謹(jǐn)慎

打開(kāi)查詢緩存對(duì)讀和寫(xiě)操作都會(huì)帶來(lái)額外的消耗：

• 讀查詢?cè)趫?zhí)行之前要先檢查是否命中緩存；
• 如果讀查詢可以被緩存，那么當(dāng)完成執(zhí)行后，MySQL如果發(fā)現(xiàn)緩存中沒(méi)有這個(gè)查詢，會(huì)將其結(jié)果存入查詢緩存，這會(huì)帶來(lái)額外的系統(tǒng)消耗；
• 對(duì)寫(xiě)操作也有影響，因?yàn)楫?dāng)向某個(gè)表寫(xiě)入數(shù)據(jù)的時(shí)候，MySQL必須將對(duì)應(yīng)表的所有緩存設(shè)置失效。如果查詢緩存非常大或者碎片很多，這個(gè)操作就可能會(huì)帶來(lái)很大的系統(tǒng)消耗；

雖然如此，查詢緩存仍然會(huì)給系統(tǒng)帶來(lái)性能的提升。但是，上述的額外消耗也可能不斷增加，再加上對(duì)查詢緩存操作是一個(gè)加鎖排它操作，這個(gè)消耗也不小。

對(duì)InnoDB用戶來(lái)說(shuō)，事務(wù)的一些特性會(huì)限制查詢緩存的使用。當(dāng)一個(gè)語(yǔ)句在事務(wù)中修改了某個(gè)表，在事務(wù)提交前，MySQL都會(huì)將這個(gè)表對(duì)應(yīng)的查詢緩存設(shè)置失效，因此，長(zhǎng)時(shí)間運(yùn)行的事務(wù)，會(huì)大大降低查詢緩存的命中率。

（3）如何分析和配置查詢緩存

10、存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是一組為了完成特定功能的SQL 語(yǔ)句集合，經(jīng)編譯后保存在數(shù)據(jù)庫(kù)中，通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)的值，也可以返回結(jié)果。

存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：

• 減少網(wǎng)絡(luò)流量
• 提高執(zhí)行速度
• 減少數(shù)據(jù)庫(kù)連接次數(shù)
• 安全性高
• 復(fù)用性高

存儲(chǔ)過(guò)程的缺點(diǎn)：

可移植性差

11、事務(wù)

事務(wù)內(nèi)的語(yǔ)句，要么全執(zhí)行，要么全不執(zhí)行。事務(wù)具有ACID特性，ACID表示原子性（atomicity）、一致性（consistency）、隔離性（isolation）、持久性（durability）。

（1）原子性（atomicity）

一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中的所有操作要么全執(zhí)行提交成功，要么全不失敗回滾。

（2）一致性（consistency）

數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另一個(gè)一致性的狀態(tài)。

（3）隔離性（isolation）

一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其它事務(wù)是不可見(jiàn)的。

（4）持久性（durability）

事務(wù)一旦提交，則七所做的修改就會(huì)永久的保存在數(shù)據(jù)庫(kù)中。

12、索引

索引是存儲(chǔ)引擎用于快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)。我覺(jué)得數(shù)據(jù)庫(kù)中最重要的知識(shí)點(diǎn)，就是索引。

存儲(chǔ)引擎以不同的方式使用B-Tree索引，性能也各有不同，各有優(yōu)劣。例如MyISAM使用前綴壓縮技術(shù)使得索引更小，但I(xiàn)nnoDB則按照原數(shù)據(jù)格式進(jìn)行存儲(chǔ)。MyISAM索引通過(guò)數(shù)據(jù)的物理位置引用被索引的行，而InnoDB則根據(jù)主鍵引用被索引的行。

B-Tree通常意味著所有的值都是按順序存儲(chǔ)的，并且每一個(gè)葉子頁(yè)到根的距離相同。

B-Tree索引能夠加快訪問(wèn)數(shù)據(jù)的速度，因?yàn)榇鎯?chǔ)引擎不再需要進(jìn)行全表掃描來(lái)獲取需要的數(shù)據(jù)，取而代之的是從索引的根結(jié)點(diǎn)開(kāi)始進(jìn)行搜索。根結(jié)點(diǎn)的槽中存放了指向子結(jié)點(diǎn)的指針，存儲(chǔ)引擎根據(jù)這些指針向下層查找。通過(guò)比較節(jié)點(diǎn)頁(yè)的值和要查找的值可以找到合適的指針進(jìn)入下層子節(jié)點(diǎn)，這些指針實(shí)際上定義了子節(jié)點(diǎn)頁(yè)中值的上限和下限。最終存儲(chǔ)引擎要么找到對(duì)應(yīng)的值，要么該記錄不存在。

葉子節(jié)點(diǎn)比較特別，它們的指針指向的是被索引的數(shù)據(jù)，而不是其他的節(jié)點(diǎn)頁(yè)。B-Tree對(duì)索引列是順序組織存儲(chǔ)的，所有很適合查找范圍數(shù)據(jù)。B-Tree適用于全鍵值、鍵值范圍或鍵前綴查找。 因?yàn)樗饕龢?shù)中的節(jié)點(diǎn)是有序的，所以除了按值查找之外，索引還可以用于查詢中的order by操作。一般來(lái)說(shuō)，如果B-Tree可以按照某種方式查找到值，那么也可以按照這種方式用于排序。

13、全文索引

全文索引的目的是 通過(guò)關(guān)鍵字的匹配進(jìn)行查詢過(guò)濾，基于相似度的查詢，而不是精確查詢。

全文索引利用分詞技術(shù)分析出文字中某關(guān)鍵字的頻率和重要性，并按照一定的算法智能的篩選出我們想要的結(jié)果。

全文索引一般用于字符串中某關(guān)鍵字的查詢，比如char、varchar、text，也支持自然語(yǔ)言全文索引和布爾全文索引。

到此這篇關(guān)于MySQL中必須了解的13個(gè)關(guān)鍵字總結(jié)的文章就介紹到這了,更多相關(guān)MySQL關(guān)鍵字內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論