作為程序員，我們在項目中會使用到許多種類的數據庫，根據業務類型、并發量和數據要求等選擇不同類型的數據庫，比如MySQL、Oracle、SQLServer、SQLite、MongoDB和redis等。今天我們就來系統的介紹一下這其中的關系型數據庫MySQL，這也是在很多開源系統中使用比較多的，因為其體積小、速度快、并且開源等的特征吸引很多開發人員的青睞。不管是自用還是商用，一些業務不太復雜的系統使用MySQL都是一個不錯的選擇。
接下來我們主要通過編寫SQL語句的方式來講解SQL的一些知識點，首先我們創建兩張表，用戶表（user）和崗位表（post），設計幾個簡單的字段，后續的SQL語句都是基于這兩張表。以下是這兩張表的結構。
1、基礎概念
我們先來了解一些MySQL中常涉及的一些基礎概念。
1.1、MySQL存儲引擎
1.1.1、什么是存儲引擎
MySQL中的數據是用各種不同的技術存儲在文件中，每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中稱為存儲引擎。存儲引擎是MySQL將數據存儲在文件系統中的存儲方式或者存儲格式。
1.1.2、MySQL有哪些存儲引擎
MySQL的存儲引擎包括： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE 和 FEDERATED等。其中 MylSAM 和 lnnoDB 是比較常用的兩種。
MyISAM的主要特點是擁有較高的插入，查詢速度，但不支持事務，支持表鎖（即使操作一條記錄也會鎖住整個表，不適合高并發的操作）；而InnoDB引擎提供對數據庫事務的支持，并且還提供了行級鎖和外鍵的約束（操作時只鎖某一行，不對其它行有影響，適合高并發的操作），InnoDB也是MySQL5.5版本后默認數據庫存儲引擎。
我們用一張圖來對比一下兩者的區別。
1.1.3、怎么查詢當前數據的存儲引擎
可以使用數據庫管理工具客戶端查看（表類型會顯示），也可以使用查詢語句進行查詢。使用 show engines 查詢當前數據庫支持的存儲引擎，使用 show variables like '%storage_engine%' 查詢l數據庫默認的存儲引擎。前面也說到了，MySQL5.5之前的默認存儲引擎是MyISAM，MySQL5.5版本后默認數據庫存儲引擎改為了InnoDB【我們可以通過select version() 查詢mysql的版本】
1.2、MySQL事務
1.2.1、什么是事務？
事務就是一組獨立不可分割的工作單元，事務中的操作要么全部執行成功，要么全部執行失敗，沒有其他的中間狀態。具體地說，就是在一個事務中執行多個操作時，要么所有的事務都被提交（commit），那么這些修改就永久保存下來；要么數據庫放棄所做的所有修改，整個事務回滾（rollback）到最初狀態。
1.2.2、事務的四大特性
事務的四個特性分別是：原子性、一致性、隔離性和持久性，即所謂的ACID。
- 原子性：將事務看作是不可再分的原子，一旦開始事務，要么全部執行，要么全部取消，是不存在中間狀態的。即把一個事務看成一個整體，牽一發而動全身。
- 一致性：在事務開始之前和之后，數據從一個合法性狀態變換到另一個合法性狀態，數據庫的完整性約束沒有被破壞。例如買早餐，顧客付了10塊錢。老板收到了10塊錢（這里不考慮手續費的問題），顧客和老板加起來的總額不會變化，不多也不少。
- 隔離性：每個讀寫事務對其他事務的操作對象能相互分離。例如事務T1和事務T2的操作不能相互干擾。
- 持久性：事務一旦提交，它對數據庫中數據的改變就是永久性，接下來的其他操作和數據庫故障不應該對其有任何影響。持久性是通過事務日志來保證的。日志包括了重做日志和回滾日志。
1.2.3、事務的實現
在介紹存儲引擎的時候，也說到了InnoDB是支持事務的，那么接下來我們就以InnoDB為例來說明。
數據庫通常借助日志來實現事務，常見的有undo log 和 redo log，這兩種都能保證事務特性，undo log實現事務的原子性，redo log實現事務的持久性。
1.2.3.1、redo log(重做日志)
每當操作時，在磁盤數據變更之前，將操作寫入redo log，這樣當系統崩潰重啟后可以繼續執行。
（1）redolog的作用
MySQL在innodb引擎下，所做的增刪改查都是先去buffer pool緩沖池（內存區域）里面操作，然后再把數據寫入磁盤，因為增刪改都是在內存操作，這樣就存在系統異常導致數據丟失的情況，redolog就是為了解決系統異常導致內存修改丟失的問題。
（2）redolog如何保證數據不丟失
所有的操作都是以事務為單位的，在事務未執行完畢的時候數據庫異常導致數據丟失是正常的，因為事務未提交成功。在事務提交的時候，我們把redolog從內存刷入到磁盤中去，從而保證修改不丟失，如果寫入磁盤失敗，那事務也將提交失敗。
（3）為什么不直接把修改更新到磁盤？
MySQL操作數據是在內存中完成的，然后再把內存中的數據頁寫入到磁盤中。不直接操作磁盤目的就是為了提高性能。雖然redolog落盤的時候也是入磁盤，但它是順序寫入，而直接對磁盤上數據修改是隨機寫入，順序寫的速度要遠遠快于隨機寫。
1.2.3.2、undo log(回滾日志)
當一個事務執行一半無法繼續執行時，可以根據回滾日志將之前的修改恢復到變更之前的狀態。undo log 是 innodb 實現，總的來說提供兩個作用：回滾和多版本控制(MVCC)。是事務特性的重要組成部分，在數據發生更新操作時（INSERT、DELETE、UPDATE）會產生 undo 記錄。先于 redo log 被記錄。
（1）提供回滾操作
我們在進行數據更新操作的時候，不僅會記錄redo log，還會記錄undo log，如果因為某些原因導致事務回滾，那么這個時候MySQL就要執行回滾（rollback）操作，利用undo log將數據恢復到事務開始之前的狀態。如我們執行下面兩條SQL語句（一條更新、一條刪除）：
此時undo log會記錄兩條對應的SQL語句，update對應的是更新之前的數據，delete對應的是insert 語句【反向操作的語句】。在操作出現異常時，可以使用undo log日志來實現回滾操作，將數據還原回去，以保證事務的一致性。
我們來看一下執行的過程（以第一條更新語句為例）：
第一步：從磁盤讀取到user_id=1的記錄，放到內存
第二步：記錄undo log 日志
第三步：記錄redo log (預提交狀態)
第四步：修改內存中user_id=1的記錄
第五步：記錄binlog，記錄完整SQL
第六步：提交事務，寫入redo log (commit狀態)
我們根據上面的流程來看，如果在上面的某一個階段數據庫崩潰，如何恢復數據。
在第一二三步執行時數據庫崩潰：此時數據還沒有發生任何變化，故沒有任何影響，不需要做任何操作。
第四步修改內存中的記錄時數據庫崩潰：此時事務沒有commit，所以這里要進行數據回滾，此時通過undo log進行數據回滾。
第五步寫入binlog時數據庫崩潰：這里和第四步一樣的邏輯，此時事務沒有commit，所以這里要進行數據回滾，會通過undo log進行數據回滾。
第六步事務提交時數據庫崩潰：如果數據庫在這個階段崩潰，那其實事務還是沒有提交成功，但是這里并不能像之前一樣對數據進行回滾，因為在提交事務前 binlog可能成功寫入磁盤了，所以這里要根據兩種情況來做決定。如果binlog存在事務記錄：那么就"認為"事務已經提交了，這里可以根據redo log對數據進行重做；如果binlog不存在事務記錄，那么這種情況事務還未提交成功，所以會對數據進行回滾。
（2）提供多版本控制(MVCC)【undo log實現多版本并發控制（MVCC）】
MVCC，即多版本控制。在MySQL數據庫InnoDB存儲引擎中，用undo Log來實現多版本并發控制(MVCC)。當讀取的某一行被其他事務鎖定時，它可以從undo log中分析出該行記錄以前的數據版本是怎樣的，從而讓用戶能夠讀取到當前事務操作之前的數據【快照讀】。
1.3、MySQL索引
1.3.1、什么是索引
在關系數據庫中，索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。
如果數據庫中沒有索引，此時查找的時候就需要把整個表遍歷一遍。比如如果想找id為1的圖書信息，沒有索引的查找過程就相當于一個"順序表查找"，數據量少的時候查詢效率差異倒不是很明顯，一旦數據量比較大，達到千萬億級別的時候，查詢就會特別的慢。而如果加了索引，查詢效率可以提升幾倍設置成百上千倍，當然這也不是完全絕對的，還取決于開發人員編寫的SQL語句性能。
1.3.2、MySQL有哪些索引類型
（1）普通索引【INDEX】
普通索引是最基本的索引，它沒有任何限制，一張表可以創建多個普通索引，一個普通索引可以包含多個字段，允許數據重復，允許 NULL 值插入。普通索引僅加速查詢。
（2）唯一索引【UNIQUE INDEX】和主鍵索引【PRIMARY KEY】
唯一索引與普通索引類似，不同的就是：索引列的值必須唯一，這里要注意的是，索引值是否允許為空，要分情況說明，多數可允許有空值，但主鍵索引是一類特殊的唯一索引，不可為空。如果是組合索引，則列值的組合必須唯一。簡單來說：唯一索引是加速查詢 + 列值唯一。
（3）組合索引
組合索引(也叫復合索引或者聯合索引)，即一個索引包含多個列。組合索引指在多個字段上創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。使用組合索引時遵循最左前綴集合。可以說：組合索引是多列值組成的一個索引，專門用于組合搜索，其效率大于索引合并。
嚴格來說，組合索引不算是單獨的一類，因為組合索引也可以是普通索引或者唯一索引，只要索引建立在多列上，普通索引和唯一索引都是可以在多列上建立組合索引的。
（4）全文索引【FULLTEXT】
全文索引主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較。fulltext索引跟其他索引大不相同，它更像是一個搜索引擎，而不是簡單的where語句的參數匹配。fulltext索引配合match against操作使用，而不是一般的where語句加like。它可以在create table，alter table ，create index使用，不過目前只有char、varchar、text 列上可以創建全文索引。值得一提的是，在數據量較大的時候，先將數據放入一個沒有全局索引的表中，然后再用CREATE index創建fulltext索引，要比先為一張表建立fulltext然后再將數據寫入的速度快很多。
1.3.3、MySQL如何分析索引是否生效
最簡單的方式就是通過客戶端連接管理工具，使用它的解釋功能，查看possible_keys 字段值。
或者直接在查詢語句前面添加關鍵詞explain，對于explain查詢出來的結果，還是看possible_keys 用到的索引是不是查詢字段的索引。
1.3.4、MySQL索引為什么加快數據的檢索速度
很多人知道使用索引可以提高查詢效率，但是可能不太了解為什么可以加快數據的檢索速度。MySQL索引默認使用的數據結構是B+樹，B+樹可以理解是扁且寬的（矮胖）。也就是層數少，每層的節點數目很多。但是每層的節點多歸多，卻不存儲數據，只起到索引效果，所有的數據都存在葉子節點上。MySQL的數據是直接存儲在磁盤上的，而對于從磁盤查找數據來說，需要經歷尋道、尋址、數據傳輸三個階段，使用B+樹，層數少，只在葉子節點存數據的特點就能極大的保證磁盤IO次數少，從而效率高。
有一些人可能會有疑問，為什么設計的是B+樹，而不是B樹或者哈希表？我們舉一個例子說明，在需要根據范圍查詢時，比如我需要查詢某一段時間內的數據，使用B樹和哈希表在實現范圍查詢時效率都比較低。B+樹的葉子節點使用了指針順序（鏈表）從小到大地連接在一起，B+樹葉節點兩兩相連可大大增加區間訪問性，只要遍歷葉子節點就可以實現整棵樹的遍歷，而B樹的葉子節點是相互獨立的，每個節點 key（索引）和 data 在一起，則無法查找區間；再說哈希表，哈希表是一種以key-value存儲數據的結構，所以多個數據在存儲關系上是完全沒有任何順序關系的，所以，對于區間查詢是無法直接通過索引查詢的，就需要全表掃描。
雖然索引有它的優勢，但不是創建的越多越好，比如我們就不適合把每一列字段都建立索引，創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。另外索引需要占用物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間。
1.4、MySQL存儲過程和函數
1.4.1、什么是存儲過程和函數
存儲過程和函數是事先經過編譯并存儲在數據庫中的一段SQL語句的集合，調用存儲過程和函數可以簡化開發，減少數據在數據庫和應用服務器之間的傳輸，可以提高數據處理效率。
1.4.2、存儲過程和函數的區別
存儲過程和函數的區別在于函數必須有返回值，而存儲過程沒有；存儲過程的參數可以使用IN、OUT、INPUT類型，而函數的參數只有IN類型。如果有函數從其他類型的數據庫遷移到MySQL，那么就可能需要將函數改造成存儲過程。
1.4.3、為什么使用存儲過程和函數
在完成一個邏輯操作時，有時會執行多條SQL語句，此外這些SQL語句的執行順序也不是固定的，它會根據條件的變化而變化。在執行過程中，這些需要根據前面SQL語句的執行結果有選擇的執行后面的SQL語句。為了解決該問題，MySQL軟件提供了數據庫對象存儲過程和函數。
我們使用存儲過程，往往是一些比較復雜的業務，并且把業務使用存儲過程來實現，也方便后期的維護。
1.4.4、如何編寫存儲過程
（1）創建存儲過程
基本語法：
create procedure sp_name()
begin
.........
end
（2）調用存儲過程：
基本語法：call sp_name()
注意：存儲過程名稱后面必須加括號，哪怕該存儲過程沒有參數傳遞
（3）刪除存儲過程
基本語法：drop procedure sp_name
注意事項：不能在一個存儲過程中刪除另一個存儲過程，只能調用另一個存儲過程
（4）顯示所有存儲過程基本信息
基本語法：show procedure status
該語句會顯示數據庫中所有存儲的存儲過程基本信息，包括所屬數據庫，存儲過程名稱，創建時間等。
（5）顯示某一個mysql存儲過程的詳細信息
基本語法：show create procedure sp_name
我們來看一個示例：
1.4.5、項目中如何調用存儲過程
我們以dao層和MyBatis中XML交互為例（其他層如controller和service跟正常的業務沒有任何差異）。
dao層可以傳map作為 in 的請求參數：void call_myp2(Map map);
mybatis中編寫xml語句調用存儲過程，入參使用in，出參使用out。
1.5、MySQL字符編碼
這里把字符編碼單獨拿出來講解，也是因為曾經踩過坑，所以拿出來說明避免更多的人入坑。我們常用的字符集是latin1、gbk/gb2312、utf8/utf8mb4。
為了避免亂碼問題，我們一般會選用utf8/utf8mb4，而這兩者一般情況下都是沒有差異的，都能正常使用。但小編遇到一個問題，使用utf8mb4可以但utf8不行，就是存儲含有表情包（如火星文,moji表情等）的昵稱時，使用utf8會拋出異常，表情包被識別為xF0x9Fx8Dx89Ch。
后面分析得知，moji表情是字符,不是圖片,屬于unicode，發生上面的異常也是數據庫根本不認識'xF0x9Fx8Dx89Ch...',在字符集中沒有匹配到,導致'xF0x9Fx8Dx89Ch...'直接入庫,發生異常。
所以我們要配置一個合適的字符集,使數據庫支持這個字符，我們從字符的長度來進行分析，UTF8使用可變長度字節來存儲 Unicode字符，目前可見字符集都只需要3個字節，包含了所有字符。但是問題出在unicode6系列編碼上，它們需要4個字節，這部分就是有名的emoji。如果我們的數據庫使用utf8，是無法存儲emoji表情的。正好utf8mb4一個字符最多能存4字節，相對于utf-8來說能支持更多的字符集，也能滿足存儲moji表情的需求。
特別注意的是從MySQL 5.5.3 版本才開始提供了utf8mb4字符集，支持四字節的字符。另外JDBC驅動版本要求mysql-connector版本高于5.1.13。
1.6、MySQL視圖和表
視圖和表是MySQL數據庫中兩個需要掌握的基礎知識，它們相似又有很大差異，表是真實存在的，占用內存。而視圖是虛擬的，不占數據庫空間。視圖是數據庫中的查詢sql的語句，可以理解為保存后的查詢結果，它保存的是“真實表”經查詢后的結果。
比如我們在上面建了兩張表：用戶表（user）和崗位表（post），表中存儲的數據是真實存在與磁盤當中的（我們也可以反向思考如果表中的數據不是真實存在的，那數據庫就沒有意義了）。那為什么有了表還需要視圖呢？當我們需要對數據庫中的一張或多張表的某些字段進行查詢時，需要編寫一些關聯SQL語句把我們需要的字段單獨列出來，但我們又不想每次都去輸入查詢的SQL語句，那我們就可以定義一個視圖，保留這個查詢的SQL語句，下次就可以直接使用，注意是知識保留SQL語句并不保留SQL語句查詢出來的結果數據。如果表中的數據發生了變化，從視圖中查詢出的數據也會隨之發生改變。同樣使用視圖語句進行增刪改也會影響表中的數據。
基本語法：
創建視圖：create view view_name as select * from table_name;
查詢視圖：select * from view_name;
刪除視圖：drop view view_name;
舉個例子：我們使用語句創建視圖：create view myview as select user_id,user_name from user;然后再查詢視圖 select * from myview;就可以看到數據。
1.7、MySQL數據分區
1.7.1、數據分區的概念
數據分區是一種物理數據庫的設計技術，它的目的是為了在特定的SQL操作中減少數據讀寫的總量以縮減響應時間。分區并不是生成新的數據表，而是將表的數據均衡分攤到不同的硬盤，表實際上還是一張表。分區可以做到將表的數據均衡到不同的地方，提高數據檢索的效率，降低數據庫的頻繁IO壓力值。
我們可以理解是將同一張表中不同規則的記錄分配到不同的物理文件中。比如我們在表中某一字段設置按日分區，在按照日期進行查詢時，就會到對應的分區表中查找數據，縮小了查詢范圍，大大提升了查詢效率。
1.7.2、如何進行分區
我們要注意在創建分區要在剛開始創建表的時候同時創建分區，我們以按年分區為例：
2、常用函數
2.1、CONCAT函數
CONCAT(str1,str2) 函數用于將多個字符串連接成一個字符串，返回結果為連接參數產生的字符串。如有任何一個參數為NULL ，則返回值為 NULL。
比如我們執行：select CONCAT('My', 'S', 'QL'); 返回結果 MySQL。我們執行：select CONCAT('My', NULL, 'QL');返回結果是 NULL
2.2、 REPLACE函數
REPLACE(str,str1,str2)，把str字符串中出現字符串str1的全部替換為字符串str2。注意MySQL里面全部替換是REPLACE，而不是REPLACEALL，不要弄混。
2.3、ISNULL函數
ISNULL(expr) 函數判斷expr是否為空，如expr為null，那么isnull()的返回值為1，否則返回值為0。注意isnull('')和isnull("")是返回0的。
2.4、IFNULL函數
ifnull(expr1,expr2)表達式，表示如果expr1不為NULL，則IFNULL()的返回值為expr1; 否則其返回值為expr2。
2.5、group by(分組)、having （過濾）和order by（排序）
group by(分組)、having （過濾）和order by（排序）這三個函數我們單獨使用時，是非常好理解的，我們這里主要討論它們一起聯合使用，很多人容易弄混淆。我們常常將它們組合在一起用,完成分組+排序的功能。
我們首先了解幾個知識點：having一般是和group by一起使用；在sql命令格式使用的先后順序上，group by 先于 order by；order by不會對group by 內部進行排序，如果group by后只有一條記錄，那么order by 將無效。要查出group by中最大的或最小的某一字段使用 max或min函數；group by、having、order by的使用順序：group by 、having、order by。
我們來寫個例子，查詢user表中按照年齡（user_age）分組，重復次數大于1，并且按照重復次數由大到小排列：
3、MySQL常用場景
3.1、MySQL的批量插入
這個在之前的一篇文章《MyBatis 批量插入使用 foreach 循環插入的優化，使用分片多線程》中已經詳細的介紹過了。通過foreach 循環插入，大量數據時配合分片批量插入。我們再延伸一個，在插入時，已存在的數據進行更新不再重復插入，使用MySQL的 ON DUPLICATE KEY UPDATE。
3.2、MySQL中刪除重復數據只保留一條
我們以user表為例，刪除user_name同名的數據，只保留一條。簡單的處理方式就是通過HAVING查詢重復的，然后保留id最大的那一條數據。
3.3、mysql 多表關聯更新
對于多表關聯更新的操作需要慎重，建議在更新前，先使用 SELECT 語句查詢驗證更新的數據與自己期望的是否一致。多表聯合更新有很多種方式，可以使用INNER JOIN、LEFT JOIN，也可以使用子查詢，下面就編寫一個示例。
UPDATE USER u
INNER JOIN post p ON u.user_id = p.user_id
SET p.post_name = u.user_name;
4、MySQL常用技巧
4.1、數據庫執行腳本生成實體類信息
示例：
4.2、獲取漢字首拼音（包含特殊符號）函數
4.3、MySQL生成數據字典
示例：
4.4、Excel生成insert語句
有時候需要將Excel中數據導入到Mysql數據庫中，此時，可以利用insert into腳本，整理sql數據，直接將數據插入到mysql數據庫中。
示例：在excel單元格中對應的行填入公式：="INSERT INTO user(user_name, user_age, user_phone) VALUES ('"&A1&"', '"&B1&"','"&C1&"');"
主鍵user_id為自增，創建時間create_time默認取系統當前時間，所以這兩個字段不需要賦值。
