# 一、安裝說明
首先學習數據庫，當然是安裝軟件。千里之行，始于足下，如果連安裝都不會，如何進行后續的入門學習。可是對于安裝也有不同方式，比如 RPM 和源碼編譯安裝呢。
## 1.1 RPM 安裝包和 Tar 安裝包的區別？
RPM 直接安裝，Tar 屬于源碼安裝，可以設置更多的參數，可以和系統進行更緊密的優化。舉個例子，RPM 是 180/96、185/100 之類的標準版型，Tar 源碼安裝類似于私人定制，量體裁衣的。其實個人覺得 RPM 安裝適合小白入門，簡單了解下 MySQL，不用做過多安裝上的了解，然而源碼編譯安裝適合比如經常跟 MySQL 打交道的工程師，比如 web 研發人員、c++ 工程師等，總不能只是知道如何簡單的使用和 curd，其實對于技術人員的縱向知識體系打造是不好的，其實源碼編譯安裝，還可能自己嘗試些具體參數的配置。關于 MySQL 官方下載地址：
https://dev.mysql.com/downloads/mysql/
## 1.2 安裝后需要配置哪些內容？
不管 RPM 還是源碼編譯安裝后，有些東西必須要設置：
root 初始密碼問題：必須設置密碼，如果是自己玩還好，如果是線上系統必須設置密碼，要不然就是裸跑系統。
默認安裝后會在指定文件中生成，如果忘記或找不到可以對 root 密碼進行強制修改：
mysqld_safe –skip-grant-tables 2&
用戶遠程訪問問題：從安全性角度默認不允許遠程訪問，可以進行配置，允許遠程訪問，但是要注意安全性規范。
grant all privileges on . to ‘root’@’%’ identified by’Password’;
flush privileges;
UTF-8 編碼問題： 關于字符集的問題，可能有些技術人員初次學習數據庫時或者初次從事研發工作時，偶爾會碰到，為什么前端信息是正常錄入，寫入數據庫時，變成了亂碼？查看數據庫的編碼方式命令為：
show variables like 'character%';
參數說明：
character_set_client為客戶端編碼方式；
character_set_connection為建立連接使用的編碼；
character_set_database數據庫的編碼；
character_set_results結果集的編碼；
character_set_server數據庫務器的編碼；
## 1.3 my.cnf 文件初始需要配置哪些內容？
數據文件位置： 確保數據不會把磁盤空間寫滿，如果有 ssd，可以充分利用 IO 優勢。
日志文件位置： 快速定位錯誤日志的位置，根據日志排除錯誤的能力，是程序員的第一生產力。
其他基礎參數
Myisam系列參數（表級鎖）：事務--鎖--？
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
InnoDB系統參數（行級鎖？）：
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
其他參數
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 10240
[mysqldump]
max_allowed_packet = 32M
[mysqld_safe]
log-error=/data/mysql_err.log
pid-file=/data/mysqld.pid
常見的 my.cnf 文件類型
my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
## 1.4 MySQL 的版本選擇
5.6 更成熟、更穩定，缺乏一些5.7開始支持的新特性。
5.7 支持更多新特性，支持 MGR、JSON 字段格式等。從 5.7 開始，MySQL 對 SQL 語法的檢查變為嚴格，之前一些存在潛在問題和錯誤的 SQL 會無法執行。
8.0 擁有很多新的功能，包括 SQL 方面、JSON 方面以及 DevOps 方面，據說性能提升長達 15 倍。
1.5 MySQL 之外的選擇
Oracle： 免費下載，但是商業用途收費（按 CPU 收費），功能和穩定性更佳，免費和收費的培訓資料很多。由于 Oracle 的系統架構較老，代碼難以進行整體顛覆性的修改，所以只能在每個版本中進行較小的改進。
PostgreSQL：（國內有依托阿里德哥推廣的強大知識分享社區） 和 MySQL 一樣，社區版代碼開源，SQL 風格和 Oracle 更加接近，功能和性能也比 MySQL 更加強大，支持 MPP（Greenplum）、LLVM、GIS、列式存儲、圖計算等特性。普及率相對較低，文檔和資料比 Oracle、MySQL 要少。
# 二、MySQL 引擎選擇和表設計上的優化
大多 Web 工程師，使用更多的引擎選擇和表設計，并且隨著業務量發展，會進行不同類型或程度上的優化。5.7 之后默認存儲引擎為 InnoDB，主要 InnoDB 能應用絕大數場景。
## 2.1 Myisam 和 InnoDB 的區別？
其實關于這兩個最常用的存儲引擎，無非就是看場景，其實沒有絕對的好與壞，不要教條主義，適合自己業務的就是最好的。
Myisam：表級鎖，不支持事務，讀性能更好，讀寫分離中做讀（從）節點。老版本 MySQL 的默認存儲引擎。表的存儲分為三個文件：frm表格式，MYD/MYData 數據文件，myi 索引文件。
InnoDB：有條件的行級鎖，支持事務，更適合作為讀寫分離中的寫（主）節點。新版本 MySQL（5.7開始）的默認存儲引擎。
## 2.2 其他的引擎介紹
XtraDB：XtraDB 是一個 MySQL 的存儲引擎，由 Percona 公司對于 InnoDB 存儲引擎進行改進加強后的產品，其設計的主要目的是用以替代現在的 InnoDB。XtraDB 兼容 InnoDB 的所有特性，并且在 IO 性能，鎖性能，內存管理等多個方面進行了增強。
TokuDB：TokuDB 是一個高性能、支持事務處理的 MySQL 和 MariaDB 的存儲引擎。TokuDB 的主要特點則是對高寫壓力的支持。
# 三、MySQL SQL 語句的優化
關于 SQL 優化，對于基本大多的 Web 研發人員，注意一個核心點：減少 IO 請求，網絡傳輸。
1.應盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描
2.應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。如：
```
select id from t where ais null
```
可以在 a 上設置默認值 0，確保表中 a 列沒有 null 值，然后這樣查詢：
```
select id from t where a=0
```
3.盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：
```
select id from t where a=10 or a=20
```
可以這樣查詢：
```
select id from t where a=10
union all
select id from t where a=20
```
4.下面的查詢也將導致全表掃描：
```
select id from t where name like‘%c%’
```
下面走索引
```
select id from t where name like‘c%’
```
若要提高效率，可以考慮全文檢索。
5.in 和 not in 也要慎用，否則會導致全表掃描，如：
```
select id from t where a in(1,2,3)
```
對于連續的數值，能用 between 就不要用 in 了：
```
select id from t where a between 1 and 3
```
如果在 where 子句中使用參數，也會導致全表掃描。因為 SQL 只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：
```
select id from t where a=@a
```
可以改為強制查詢使用索引：
```
select id from t with(index(索引名)) where a=@a
```
6.應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：
```
select id from t where a/2=100
```
應改為：
```
select id from t where a=100*2
```
7.應盡量避免在 where 子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：
```
select id from t where substring(name,1,3)='abc' name以abc開頭的id
select id from t where datediff(day,createdate,'2005-11-30')= 0 '2005-11-30'生成的id
```
應改為:
```
select id from t where name like‘abc%’
select id from t where createdate>='2005-11-30′ and createdate<'2005-12-1′
```
8.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
9.在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。
10.很多時候用 exists 代替 in 是一個好的選擇：
```
select num from a where num in (select num from b)
```
用下面的語句替換：
```
select num from a where exists (select 1 from b where num=a.num)
```
11.并不是所有索引對查詢都有效，SQL 是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL 查詢可能不會去利用索引，如一表中有字段 sex，male、female 幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。
12.索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。
13.應盡可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。
14.盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會 逐個比較字符串中每一個字符，而對于數字型而言只需要比較一次就夠了。
15.盡可能的使用 varchar 代替 char，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。
16.任何地方都不要使用 select * from t ，用具體的字段列表代替 *，不要返回用不到的任何字段。這里就是典型的減少網絡傳輸，尤其大多數業務中，用戶優惠券列表，如果全是*，如果用戶數據過多，程序在網絡傳輸過程中會超時。
17.盡量使用表變量來代替臨時表。如果表變量包含大量數據，請注意索引非常有限（只有主鍵索引）。
18.避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。
19.臨時表并不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重復引用大型表或常用表中的某個數據集時。但是，對于一次性事件，最好使 用導出表。
20.在新建臨時表時，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。
21.如果使用到了臨時表，在存儲過程的最后務必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統表的較長時間鎖定。
22.當只要一行數據時使用 Limit 1。當查詢表已經知道結果只會有一條結果，在這種情況下，加上 Limit 1 可以增加性能。MySQ L數據庫引擎會在找到一條數據后停止搜索，而不是繼續往后查少下一條符合記錄的數據。
23.如果應用程序有很多 Join 查詢，應該確認兩個表中 Join 的字段是被建過索引的。這樣，MySQL 內部會啟動優化 Join 的 SQL 語句的機制。這些被用來 Join 的字段，應該是相同的類型的。例如：如果要把 DECIMAL 字段和一個 INT 字段 Join 在一起，MySQL 就無法使用它們的索引。對于那些 STRING 類型，還需要有相同的字符集才行。
24.固定長度的表會更快，如果表中的所有字段都是 “固定長度” 的，整個表會被認為是 static 或 fixed-length。例如，表中沒有如下類型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一個這些字段，那么這個表就不是 “固定長度靜態表” 了，這樣，MySQL 引擎會用另一種方法來處理。固定長度的表會提高性能，因為 MySQL 搜尋得會更快一些，因為這些固定的長度是很容易計算下一個數據的偏移量的，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，需要程序找到主鍵。并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要分配那么多的空間。
25.盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。
26.盡量避免大事務操作，提高系統并發能力。
27.不同數據庫的 SQL 執行順序的差別。
28.MySQL Explain 執行計劃 type 類型區別：
性能從好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，all。
除了 all 之外，其他的 type 都可以使用到索引，除了 index_merge 之外，其他的 type 只可以用到一個索引。
* system：表中只有一行數據或者是空表，且只能用于 myisam 和 memory 表。如果是 InnoDB 引擎表，type 列在這個情況通常都是 all 或者 index
* const：使用唯一索引或者主鍵，返回記錄一定是 1 行記錄的等值 where 條件時，通常 type 是 const。也叫做唯一索引掃描。
* eq_ref：出現在要連接多個表的查詢計劃中，驅動表只返回一行數據，且這行數據是第二個表的主鍵或者唯一索引，且必須為 not null，唯一索引和主鍵是多列時，只有所有的列都用作比較時才會出現 eq_ref。
* ref：不像 eq_ref 那樣要求連接順序，也沒有主鍵和唯一索引的要求，只要使用相等條件檢索時就可能出現。常見與輔助索引的等值查找，或者多列主鍵、唯一索引中，使用第一個列之外的列作為等值查找也會出現，總之，返回數據不唯一的等值查找就可能出現。
* fulltext：全文索引檢索，要注意，全文索引的優先級很高，若全文索引和普通索引同時存在時，MySQL不管代價，優先選擇使用全文索引。
* ref_or_null：與 ref 方法類似，只是增加了 null 值的比較。實際用的不多。
* unique_subquery：用于 where 中的 in 形式子查詢，子查詢返回不重復值唯一值
* index_subquery：用于 in 形式子查詢使用到了輔助索引或者 in 常數列表，子查詢可能返回重復值，可以使用索引將子查詢去重。
* range：索引范圍掃描，常見于使用 >,<,is null,between ,in ,like 等運算符的查詢中。
* index_merge：表示查詢使用了兩個以上的索引，最后取交集或者并集，常見 and、or 的條件使用了不同的索引，官方排序這個在 ref_or_null之后，但是實際上由于要讀取所個索引，性能可能大部分時間都不如 range
* index：索引全表掃描，把索引從頭到尾掃一遍，常見于使用索引列就可以處理不需要讀取數據文件的查詢、可以使用索引排序或者分組的查詢。
* all：這個就是全表掃描數據文件，然后再在 server 層進行過濾返回符合要求的記錄。
# 四、MySQL 的缺陷與不足
1. 不支持 hash join，大表之間不適合做 joi n操作，沒辦法滿足復雜的OLAP要求。
2. MySQL 不支持函數索引，也不支持并行更新
3. MySQL 連接的 8 小時問題，相對于使用 Oracle 數據庫，使用MySQL需要注意更多的細節問題。
4. 對于 SQL 批處理和預編譯，支持程度不如 Oracle 數據庫。
5. MySQL 優化器還是比較欠缺，不及 Oracle 數據庫。
# 五、MySQL 的優點
1. 互聯網領域使用較多，文檔資料豐富，使用案例非常多，對潛在的問題比較容提前做出應對方案。
2. 由于 MySQL 是開源的數據庫，因此很多互聯網公司都根據自己的業務需求，開發出了自己的 MySQL 版本，例如阿里云上的 RDS、騰訊云、美團云等。
3. MySQL 相關的開源解決方案眾多，無需重復造輪子既可以獲得包括讀寫分離、分庫分表等高級特性，例如 Mycat、Sharding-JDBC 等。同時，MySQL 官方的解決方案也越來越豐富，例如 MySQL-Router 等。
# 六、MySQL 讀寫分離、分庫分表
1. 讀寫分離的數據復制延遲問題 MySQL 通過 binlog 實現數據的復制，也就是主從節點間的數據同步。由于 binlog 復制默認是異步的，因此主從節點之間的數據存在延遲。
2. 分庫分表帶來的復雜性，難以執行全局的排序、聚合等操作？ ==> 由于同一個表的數據被寫到了不同的表，不同的數據庫（有可能在不同的服務器節點上），因此如果需要一個同一個表進行聚合操作或者全局的排序，會非常困難，且性能較差。如果是對多個表進行 join 操作，由于每個表都可能存儲在多個服務器節點上，因此 join 操作的復雜度會變得很高，需要借助 MPP 的引擎才能完成 join 操作。
3. 目前市面最常用的 MySQL 中間件，無非 mycat（基于阿里 cobar 二次開發）、onesql（業界大牛樓方鑫基于 MySQL 官方，用 c/c++ 二次開發，不過是收費版，功能很強大，不過好像作者重回阿里了）、360 基于 MySQL 分支 Atlas 等。
# 七、MySQL 高可用
當大部分優化或者簡單架構設計完成后，再就剩下數據的高可用，畢竟不能由于數據庫的不可用導致業務的不可用，并且業務的不可用必然會導致企業損失大量用戶，然而這也是技術人員最不愿意看到的，也是技術人員成長過程中的痛點。
在考慮 MySQL 數據庫的高可用的架構時，主要要考慮如下幾方面：
* 如果數據庫發生了宕機或者意外中斷等故障，能盡快恢復數據庫的可用性，盡可能的減少停機時間，保證業務不會因為數據庫的故障而中斷
* 用作備份、只讀副本等功能的非主節點的數據應該和主節點的數據實時或者最終保持一致。
當業務發生數據庫切換時，切換前后的數據庫內容應當一致，不會因為數據缺失或者數據不一致而影響業務。
關于MySQL高可用，常用架構方案如下：
## 7.1 主從或主主半同步復制
主從架構基本是基于 binlog，最核心的就是 SQL 線程和 IO 線程。
## 7.2 半同步復制優化
普通的 replication，即 MySQL 的異步復制，依靠 MySQL 二進制日志也即 binary log 進行數據復制。比如兩臺機器，一臺主機 (master)，另外一臺是從機 (slave)。
* 正常的復制為：事務一（t1）寫入 binlog buffer；dumper 線程通知 slave 有新的事務 t1；binlog buffer 進行 checkpoint；slave 的 io 線程接收到 t1 并寫入到自己的的 relay log；slave 的 sql 線程寫入到本地數據庫。 這時，master 和 slave 都能看到這條新的事務，即使 master 掛了，slave 可以提升為新的 master。
* 異常的復制為：事務一（t1）寫入 binlog buffer；dumper 線程通知 slave 有新的事務 t1；binlog buffer 進行 checkpoint；slave 因為網絡不穩定，一直沒有收到 t1；master 掛掉，slave 提升為新的 master，t1 丟失。
* 很大的問題是：主機和從機事務更新的不同步，就算是沒有網絡或者其他系統的異常，當業務并發上來時，slave 因為要順序執行 master 批量事務，導致很大的延遲。
為了彌補以上幾種場景的不足，MySQL 從 5.5 開始推出了半同步。即在 master 的 dumper 線程通知 slave 后，增加了一個 ack，即是否成功收到 t1 的標志碼。也就是 dumper 線程除了發送 t1 到 slave，還承擔了接收 slave 的 ack 工作。如果出現異常，沒有收到 ack，那么將自動降級為普通的復制，直到異常修復。
## 7.3 高可用架構（MHA + 多節點集群）
MHA Manager 會定時探測集群中的 master 節點，當 master 出現故障時，它可以自動將最新數據的 slave 提升為新的 master，然后將所有其他的 slave 重新指向新的 master，整個故障轉移過程對應用程序完全透明。
## 7.4 zookeeper+proxy
Zookeeper 使用分布式算法保證集群數據的一致性，使用 zookeeper 可以有效的保證 proxy 的高可用性，可以較好的避免網絡分區現象的產生。
## 7.5 共享存儲（SAN 共享儲存、DRBD 磁盤復制）
共享存儲實現了數據庫服務器和存儲設備的解耦，不同數據庫之間的數據同步不再依賴于 MySQL 的原生復制功能，而是通過磁盤數據同步的手段，來保證數據的一致性。
基本上，上述三類架構是最常用的了，對于中小型公司，然而筆者公司也就是用到了上述三種了。
基本，講述到這里，基本上從 MySQL 基本的安裝到引擎選擇乃至性能優化及高可用架構等，都捎帶詳細普及了下，想要 hold 住大部分的性能優化，要考慮的東西還是很多的。畢竟性能優化是個整體概念，宏觀層面系統優化：應用服務器，數據庫層面：MySQL、Oracle、PG 等。
