文字教程推薦

MySQL 教程（菜鳥教程）

MySQL教程（易百教程）

視頻教程推薦

基礎入門： 與MySQL的零距離接觸-慕課網(wǎng)

Mysql開發(fā)技巧： MySQL開發(fā)技巧（一）??MySQL開發(fā)技巧（二）??MySQL開發(fā)技巧（三）

Mysql5.7新特性及相關優(yōu)化技巧： MySQL5.7版本新特性??性能優(yōu)化之MySQL優(yōu)化

MySQL集群（PXC）入門??MyCAT入門及應用

常見問題總結(jié)

• ①存儲引擎

  MySQL常見的兩種存儲引擎：MyISAM與InnoDB的愛恨情仇

• ②字符集及校對規(guī)則

  字符集指的是一種從二進制編碼到某類字符符號的映射。校對規(guī)則則是指某種字符集下的排序規(guī)則。Mysql中每一種字符集都會對應一系列的校對規(guī)則。

  Mysql采用的是類似繼承的方式指定字符集的默認值，每個數(shù)據(jù)庫以及每張數(shù)據(jù)表都有自己的默認值，他們逐層繼承。比如：某個庫中所有表的默認字符集將是該數(shù)據(jù)庫所指定的字符集（這些表在沒有指定字符集的情況下，才會采用默認字符集） PS：整理自《Java工程師修煉之道》

  詳細內(nèi)容可以參考： MySQL字符集及校對規(guī)則的理解

• ③索引相關的內(nèi)容（數(shù)據(jù)庫使用中非常關鍵的技術，合理正確的使用索引可以大大提高數(shù)據(jù)庫的查詢性能）

  ??Mysql索引使用的數(shù)據(jù)結(jié)構主要有BTree索引 和 哈希索引 。對于哈希索引來說，底層的數(shù)據(jù)結(jié)構就是哈希表，因此在絕大多數(shù)需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引。

  ??Mysql的BTree索引使用的是B數(shù)中的B Tree，但對于主要的兩種存儲引擎的實現(xiàn)方式是不同的。

  ??MyISAM: B Tree葉節(jié)點的data域存放的是數(shù)據(jù)記錄的地址。在索引檢索的時候，首先按照B Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址讀取相應的數(shù)據(jù)記錄。這被稱為“非聚簇索引”。

  ??InnoDB: 其數(shù)據(jù)文件本身就是索引文件。相比MyISAM，索引文件和數(shù)據(jù)文件是分離的，其表數(shù)據(jù)文件本身就是按B Tree組織的一個索引結(jié)構，樹的葉節(jié)點data域保存了完整的數(shù)據(jù)記錄。這個索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。這被稱為“聚簇索引（或聚集索引）”。而其余的索引都作為輔助索引，輔助索引的data域存儲相應記錄主鍵的值而不是地址，這也是和MyISAM不同的地方。在根據(jù)主索引搜索時，直接找到key所在的節(jié)點即可取出數(shù)據(jù)；在根據(jù)輔助索引查找時，則需要先取出主鍵的值，在走一遍主索引。 因此，在設計表的時候，不建議使用過長的字段作為主鍵，也不建議使用非單調(diào)的字段作為主鍵，這樣會造成主索引頻繁分裂。 PS：整理自《Java工程師修煉之道》

  詳細內(nèi)容可以參考：

  干貨：mysql索引的數(shù)據(jù)結(jié)構

  MySQL優(yōu)化系列（三）--索引的使用、原理和設計優(yōu)化

• ④查詢緩存的使用

  my.cnf加入以下配置，重啟Mysql開啟查詢緩存

  query_cache_type=1
query_cache_size=600000



  Mysql執(zhí)行以下命令也可以開啟查詢緩存

  set global  query_cache_type=1;
set global  query_cache_size=600000;



  如上，開啟查詢緩存后在同樣的查詢條件以及數(shù)據(jù)情況下，會直接在緩存中返回結(jié)果。這里的查詢條件包括查詢本身、當前要查詢的數(shù)據(jù)庫、客戶端協(xié)議版本號等一些可能影響結(jié)果的信息。因此任何兩個查詢在任何字符上的不同都會導致緩存不命中。此外，如果查詢中包含任何用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時表、Mysql庫中的系統(tǒng)表，其查詢結(jié)果也不會被緩存。

  緩存建立之后，Mysql的查詢緩存系統(tǒng)會跟蹤查詢中涉及的每張表，如果這些表（數(shù)據(jù)或結(jié)構）發(fā)生變化，那么和這張表相關的所有緩存數(shù)據(jù)都將失效。

  緩存雖然能夠提升數(shù)據(jù)庫的查詢性能，但是緩存同時也帶來了額外的開銷，每次查詢后都要做一次緩存操作，失效后還要銷毀。 因此，開啟緩存查詢要謹慎，尤其對于寫密集的應用來說更是如此。如果開啟，要注意合理控制緩存空間大小，一般來說其大小設置為幾十MB比較合適。此外，還可以通過sql_cache和sql_no_cache來控制某個查詢語句是否需要緩存：

  select sql_no_cache count(*) from usr;



• ⑤事務機制

  關系性數(shù)據(jù)庫需要遵循ACID規(guī)則，具體內(nèi)容如下：

1. 原子性： 事務是最小的執(zhí)行單位，不允許分割。事務的原子性確保動作要么全部完成，要么完全不起作用；

2. 一致性： 執(zhí)行事務前后，數(shù)據(jù)保持一致；

3. 隔離性： 并發(fā)訪問數(shù)據(jù)庫時，一個用戶的事物不被其他事物所干擾，各并發(fā)事務之間數(shù)據(jù)庫是獨立的；

4. 持久性: 一個事務被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫 發(fā)生故障也不應該對其有任何影響。

為了達到上述事務特性，數(shù)據(jù)庫定義了幾種不同的事務隔離級別：

• READ_UNCOMMITTED（未授權讀?。? 最低的隔離級別，允許讀取尚未提交的數(shù)據(jù)變更，可能會導致臟讀、幻讀或不可重復讀

• READ_COMMITTED（授權讀取）: 允許讀取并發(fā)事務已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發(fā)生

• REPEATABLE_READ（可重復讀）: 對同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務自己所修改，可以阻止臟讀和不可重復讀，但幻讀仍有可能發(fā)生。

• SERIALIZABLE（串行）: 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執(zhí)行，這樣事務之間就完全不可能產(chǎn)生干擾，也就是說，該級別可以防止臟讀、不可重復讀以及幻讀。但是這將嚴重影響程序的性能。通常情況下也不會用到該級別。

  這里需要注意的是：Mysql 默認采用的 REPEATABLE_READ隔離級別 Oracle 默認采用的 READ_COMMITTED隔離級別.

  事務隔離機制的實現(xiàn)基于鎖機制和并發(fā)調(diào)度。其中并發(fā)調(diào)度使用的是MVVC（多版本并發(fā)控制），通過保存修改的舊版本信息來支持并發(fā)一致性讀和回滾等特性。

  詳細內(nèi)容可以參考： 可能是最漂亮的Spring事務管理詳解

• ⑥鎖機制與InnoDB鎖算法

  MyISAM和InnoDB存儲引擎使用的鎖：

• MyISAM采用表級鎖(table-level locking)。

• InnoDB支持行級鎖(row-level locking)和表級鎖,默認為行級鎖

  表級鎖和行級鎖對比：

• 表級鎖： Mysql中鎖定 粒度最大 的一種鎖，對當前操作的整張表加鎖，實現(xiàn)簡單，資源消耗也比較少，加鎖快，不會出現(xiàn)死鎖。其鎖定粒度最大，觸發(fā)鎖沖突的概率最高，并發(fā)度最低，MyISAM和 InnoDB引擎都支持表級鎖。

• 行級鎖： Mysql中鎖定 粒度最小 的一種鎖，只針對當前操作的行進行加鎖。 行級鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小，并發(fā)度高，但加鎖的開銷也最大，加鎖慢，會出現(xiàn)死鎖。

  詳細內(nèi)容可以參考： Mysql鎖機制簡單了解一下

  InnoDB存儲引擎的鎖的算法有三種：

• Record lock：單個行記錄上的鎖

• Gap lock：間隙鎖，鎖定一個范圍，不包括記錄本身

• Next-key lock：record gap 鎖定一個范圍，包含記錄本身

  相關知識點：

1. innodb對于行的查詢使用next-key lock

2. Next-locking keying為了解決Phantom Problem幻讀問題

3. 當查詢的索引含有唯一屬性時，將next-key lock降級為record key

4. Gap鎖設計的目的是為了阻止多個事務將記錄插入到同一范圍內(nèi)，而這會導致幻讀問題的產(chǎn)生

5. 有兩種方式顯式關閉gap鎖：（除了外鍵約束和唯一性檢查外，其余情況僅使用record lock） A. 將事務隔離級別設置為RC B. 將參數(shù)innodb_locks_unsafe_for_binlog設置為1

• ⑦大表優(yōu)化

  當MySQL單表記錄數(shù)過大時，數(shù)據(jù)庫的CRUD性能會明顯下降，一些常見的優(yōu)化措施如下：

1. 限定數(shù)據(jù)的范圍： 務必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句。比如：我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的范圍內(nèi)。；

2. 讀/寫分離： 經(jīng)典的數(shù)據(jù)庫拆分方案，主庫負責寫，從庫負責讀；

3. 緩存： 使用MySQL的緩存，另外對重量級、更新少的數(shù)據(jù)可以考慮使用應用級別的緩存；

4. 垂直分區(qū)：

   根據(jù)數(shù)據(jù)庫里面數(shù)據(jù)表的相關性進行拆分。 例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。

   簡單來說垂直拆分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。 如下圖所示，這樣來說大家應該就更容易理解了。

   

   垂直拆分的優(yōu)點： 可以使得行數(shù)據(jù)變小，在查詢時減少讀取的Block數(shù)，減少I/O次數(shù)。此外，垂直分區(qū)可以簡化表的結(jié)構，易于維護。

   垂直拆分的缺點： 主鍵會出現(xiàn)冗余，需要管理冗余列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直分區(qū)會讓事務變得更加復雜；

5. 水平分區(qū)：

   保持數(shù)據(jù)表結(jié)構不變，通過某種策略存儲數(shù)據(jù)分片。這樣每一片數(shù)據(jù)分散到不同的表或者庫中，達到了分布式的目的。 水平拆分可以支撐非常大的數(shù)據(jù)量。

   水平拆分是指數(shù)據(jù)表行的拆分，表的行數(shù)超過200萬行時，就會變慢，這時可以把一張的表的數(shù)據(jù)拆成多張表來存放。舉個例子：我們可以將用戶信息表拆分成多個用戶信息表，這樣就可以避免單一表數(shù)據(jù)量過大對性能造成影響。

   

   水品拆分可以支持非常大的數(shù)據(jù)量。需要注意的一點是:分表僅僅是解決了單一表數(shù)據(jù)過大的問題，但由于表的數(shù)據(jù)還是在同一臺機器上，其實對于提升MySQL并發(fā)能力沒有什么意義，所以 水品拆分最好分庫 。

   水平拆分能夠 支持非常大的數(shù)據(jù)量存儲，應用端改造也少，但 分片事務難以解決 ，跨界點Join性能較差，邏輯復雜?！禞ava工程師修煉之道》的作者推薦 盡量不要對數(shù)據(jù)進行分片，因為拆分會帶來邏輯、部署、運維的各種復雜度 ，一般的數(shù)據(jù)表在優(yōu)化得當?shù)那闆r下支撐千萬以下的數(shù)據(jù)量是沒有太大問題的。如果實在要分片，盡量選擇客戶端分片架構，這樣可以減少一次和中間件的網(wǎng)絡I/O。

   下面補充一下數(shù)據(jù)庫分片的兩種常見方案：

• 客戶端代理： 分片邏輯在應用端，封裝在jar包中，通過修改或者封裝JDBC層來實現(xiàn)。 當當網(wǎng)的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實現(xiàn)。

• 中間件代理： 在應用和數(shù)據(jù)中間加了一個代理層。分片邏輯統(tǒng)一維護在中間件服務中。 我們現(xiàn)在談的 Mycat、360的Atlas、網(wǎng)易的DDB等等都是這種架構的實現(xiàn)。

詳細內(nèi)容可以參考： MySQL大表優(yōu)化方案