學習MySQL必須了解的13個關鍵字（總結分享）

本篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於關鍵字的相關問題，其中包括了三範式、字元集、自訂數量、視圖、分區表格等等內容，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

1、三範式

• 第一範式：每個表的每一列都要保持它的原子性，也就是表格的每一列是不可分割的；
• 第二範式：在滿足第一範式的基礎上，每個表都要保持唯一性，也就是表的非主鍵字段完全依賴主鍵字段；
• 第三範式：在滿足第一範式和第二範式的基礎上，表中不能產生傳遞關係，要消除表中的冗餘性；

2、字元集

字元集規定了字元在資料庫中的儲存格式，例如佔多少空間，支援哪些字元等等。不同的字元集有不同的編碼規則，在有些情況下，甚至還有校對規則的存，校對規則是指一個字元集的排序，在運維和使用MySQL資料庫中，選取合適的字元集非常重要，如果選擇不恰當，輕則影響資料庫效能，嚴重的可能導致資料儲存亂碼。

常見的MySQl字元集主要有四個：

##GBKUTF-8latin1

#字元集	長度
2	支援中文，但不是國際通用字元集
3	支援中英文混合場景，是國際通用字元集

1## ####MySQL預設字元集############utf8mb4#######4######完全相容於UTF-8，用四個位元組儲存更多的字符############

MySQL資料庫在開發運作中，字元集選用規則如下：

1. 如果系統開發面向國外業務，需要處理不同國家、不同語言，則應該選擇utf-8或utf8mb4；
2. 如果只需要支援中文，沒有國外業務，則為了效能考慮，可以採用GBK；

3、自訂變數

自訂變數是用來儲存內容的暫存容器，在連接MySQL的整個過程中都存在。可以使用set的方式定義。

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

使用自訂變數的注意事項：

• 使用自訂變數的查詢，無法使用快取；

• 不能在使用常數或標識符的地方使用自訂變量，例如表名、列名和limit子句中；

• 自訂變數的生命週期實在一個連接中有效，不能用它們做連接間的通訊；

避免重複查詢剛剛更新的資料

如果在更新行的同時又想獲得該行的信息，要怎麼做才能避免重複的查詢呢？

一般都這樣做：

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

使用自訂變數可以對其進行最佳化：

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

看起來還是兩個查詢，但第二次查詢無須存取任何數據表，所以會快很多。

4、選擇最佳化的資料類型

MySQL支援的資料類型非常多，選擇正確的資料類型對於獲得高效能至關重要。

（1）較小的

#一般情況下，應該盡量使用較小的資料類型，較小的資料類型通常會更快，因為佔用較少的磁碟、記憶體和CPU緩存，處理時所需的CPU週期更短。

（2）更簡單的

簡單的資料類型通常需要更少的CPU週期，整形比字串類型代價更低，因為字元集和校驗規則使字元比較比整形比較更複雜。

（3）盡量避免NULL

很多表都包含可為NULL的列，即使應用程式並不需要保存NULL也是如此，因為可為NULL是列的預設屬性，通常情況下，最好指定列為NOT NULL。

如果查詢中包含可為NULL的資料列，對MySQL來說更難最佳化，因為可為NULL的資料列使索引、索引統計和值的比較都更複雜。可為NULL的欄位會使用更多的儲存空間，在MySQL裡也需要特殊處理，可為NULL的欄位被索引時，每個索引記錄需要一個額外的位元組，在MyISAM裡甚至還可能導致固定大小的索引變成可變大小的索引。

5、視圖

視圖（view）是一種虛擬存在的表，是一個邏輯表，本身並不包含資料。作為一個select語句保存在資料字典中的。對多張表的複雜查詢，使用視圖可以簡化查詢，當視圖使用臨時表時，無法使用where條件，也不能使用索引。

單表視圖一般用於查詢和修改，會改變基本表的數據，多表視圖一般用於查詢，不會改變基本表的數據。

使用檢視的目的是為了保障資料安全性，提高查詢效率。

視圖的優點：

• 使用視圖的使用者完全不需要關心後面對應的表的結構、關聯條件和篩選條件，對使用者來說已經是過濾好的複合條件的結果集。

• 使用檢視的使用者只能存取他們被允許查詢的結果集，對錶的權限管理並不能限製到某個行某個列，但是透過檢視就可以簡單的實現。

• 一旦視圖的結構確定了，可以屏蔽表結構變化對使用者的影響，來源表增加列對視圖沒有影響；來源表修改列名，則可以透過修改視圖來解決，不會造成對訪客的影響。

6、快取表和匯總表

#有時提升效能最好的方法是在同一張表中保存衍生的冗餘數據，有時候還需要建立一張完全獨立的總表或快取表。

• 快取表用來儲存那些取得很簡單，但速度較慢的資料；
• 總表用來保存使用group by語句聚合查詢的資料；

對於快取表，如果主表使用InnoDB，用MyISAM作為快取表的引擎將會得到更小的索引佔用空間，並且可以做全文檢索。

在使用快取表和匯總表時，必須決定是即時維護資料還是定期重建。哪個更好依賴應用程序，但是定期重建並不只是節省資源，也可以保持表不會有很多碎片，以及有完全順序組織的索引。

當重建匯總表和快取表時，通常需要保證資料在操作時仍然可用，這就需要透過使用影子表來實現，影子表指的是一張在真實表背後創建的表，當完成了建表操作後，可以透過一個原子的重命名操作切換影子表和原表。

为了提升读的速度，经常建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表，这些方法会增加写的负担妈也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧，虽然写操作变慢了，但更显著地提高了读的性能。

7、分区表

通常情况下，同一张表的数据在物理层面都是存放在一起的。随着业务增长，当同一张表的数据量过大时，会带来管理上的不便。而分区特性可以将一张表从物理层面根据一定的规则将数据划分为多个分区，多个分区可以单独管理，甚至存放在不同的磁盘/文件系统上，提升效率。

分区表的优势：

• 数据可以跨磁盘存储，适合存储大量数据；

• 数据管理起来很方便，以分区为单位操作数据，不影响其他分区的正常运行；

• 查询时可以通过锁定分区的特性，缩小查询范围，提高查询性能；

8、外键

外键通常都要求每次在修改数据时都要在另外一张表中进行一次额外的查询操作，虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。如果外键的选择性很低，则会导致一个选择性很低的索引。

不过在某些场景下，外键会提升一些性能，比如想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高的多，此外。外键在相关数据的删除和更新上，也比在应用中维护要更高效，不过，外键维护操作时逐行进行的，这样的更新会比批量删除和更新要慢些。

外键约束使查询时额外访问一些别的表，也就是需要额外的锁。如果向子表中写入一条记录，外键约束会让InnoDB检查对应的父表的记录，也就是需要对父表的对应记录进行加锁操作，来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待，甚至会导致一些死锁。因为没有直接访问这些表，所以这类死锁问题很难排查。

所以，在目前的很多项目中，为了性能的考虑，已经不使用外键了。

9、查询缓存

MySQL查询缓存保存查询返回的完整结果，当查询命中该缓存，MySQL会立刻返回结果，跳过解析、优化和执行过程。

查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有的缓存数据都将失效，这种机制效率看起来比较低，因为数据表变化时可能对查询结果并没有影响，但是这种简单实现代价很小，而这点对于一个非常繁忙的系统来说非常重要。

（1）MySQL如何判断缓存命中

判断是否命中时，MySQL不会解析，而是直接使用SQL语句和客户端发送过来的其它原始信息。任何字符上的不同，例如空格、注释，丢回导致缓存的不命中。通常使用统一的编码规则是一个好的习惯，会让你的系统运行的更快。

当查询语句中有一些不确定的数据时，不会被缓存，比如函数now()。实际上，如果缓存中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL系统表、或者任何包含列级别权限的表，都不会被缓存。

（2）使用查询缓存需谨慎

打开查询缓存对读和写操作都会带来额外的消耗：

• 读查询在执行之前要先检查是否命中缓存；

• 如果读查询可以被缓存，那么当完成执行后，MySQL如果发现缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗；

• 对写操作也有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大的系统消耗；

虽然如此，查询缓存仍然会给系统带来性能的提升。但是，上述的额外消耗也可能不断增加，再加上对查询缓存操作是一个加锁排它操作，这个消耗也不小。

对InnoDB用户来说，事务的一些特性会限制查询缓存的使用。当一个语句在事务中修改了某个表，在事务提交前，MySQL都会将这个表对应的查询缓存设置失效，因此，长时间运行的事务，会大大降低查询缓存的命中率。

（3）如何分析和配置查詢快取

10、預存程序

預存程序是一組為了完成特定功能的SQL 語句集合，經過編譯後保存在資料庫中，透過指定預存程序的名字並給出參數的值，也可以傳回結果。

預存程序的優點：

• 減少網路流量

• 提高執行速度

• 減少資料庫連線次數

• #安全性高

• 多用性高

#預存程序的缺點：

可移植性差

11、事務

事務內的語句，要麼全執行，要麼全不執行。事務具有ACID特性，ACID表示原子性（atomicity）、一致性（consistency）、隔離性（isolation）、持久性（durability）。

（1）原子性（atomicity）

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

（2）一致性（consistency）

資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。

（3）隔離性（isolation）

一個事務所所做的修改在最終提交以前，對其它事務是不可見的。

（4）持久性（durability）

交易一旦提交，則七所做的修改就會永久的保存在資料庫中。

12、索引

索引是儲存引擎用於快速尋找記錄的一種資料結構。我覺得資料庫中最重要的知識點，就是索引。

儲存引擎以不同的方式使用B-Tree索引，效能也各有不同，各有優劣。例如MyISAM使用前綴壓縮技術使得索引更小，但InnoDB則依照原始資料格式進行儲存。 MyISAM索引透過資料的實體位置引用被索引的行，而InnoDB則根據主鍵引用被索引的行。

B-Tree通常意味著所有的值都是按順序儲存的，並且每一個葉子頁到根的距離都相同。 B-Tree索引能夠加快存取資料的速度，因為儲存引擎不再需要進行全表掃描來獲取所需的數據，取而代之的是從索引的根結點開始進行搜尋。根結點的槽中存放了指向子結點的指針，儲存引擎根據這些指針向下層尋找。透過比較節點頁的值和要尋找的值可以找到合適的指標進入下層子節點，這些指標實際上定義了子節點頁中值的上限和下限。最終儲存引擎要么找到對應的值，要么該記錄不存在。

###葉子節點比較特別，它們的指標指向的是被索引的數據，而不是其他的節點頁。 B-Tree對索引列是順序組織儲存的，所有很適合尋找範圍資料。 B-Tree適用於全鍵值、鍵值範圍或鍵前綴查找。 ### 因為索引樹中的節點是有序的，所以除了按值查找之外，索引還可以用於查詢中的order by操作。一般來說，如果B-Tree可以按照某種方式查找到值，那麼也可以按照這種方式用於排序。 ############13、全文索引######全文索引的目的是 透過關鍵字的匹配進行查詢過濾，基於相似度的查詢，而不是精確查詢。 ######全文索引利用分詞技術分析出文字中某關鍵字的頻率和重要性，並依照一定的演算法智能的篩選出我們想要的結果。 ######全文索引一般用於字串中某關鍵字的查詢，例如char、varchar、text，也支援自然語言全文索引和布林全文索引。 ######推薦學習：###mysql影片教學#######

以上是學習MySQL必須了解的13個關鍵字（總結分享）的詳細內容。更多資訊請關注PHP中文網其他相關文章！