1、哪些字段適合加索引

1. 經(jīng)常被查詢(xún)的字段
2. 字段長(zhǎng)度太長(zhǎng)不適合建立索引
3. 經(jīng)常更新的字段不適合建立索引，因?yàn)楦逻€會(huì)更新索引文件
4. 粒度較大的字段不適合建立索引，比如性別字段
5. 幾個(gè)字段一起查詢(xún)可以建立組合索引，組合索引的建立順序要按照字段的頻度來(lái)確定
6. 對(duì)長(zhǎng)字符串字段建立索引采用最左邊n個(gè)字符建立索引：index left(address,8)

2、索引的選擇

如果有查詢(xún)條件中有多個(gè)字段，并且每個(gè)字段都有單獨(dú)是索引，那么mysql只會(huì)選擇其中一個(gè)索引執(zhí)行篩選，并且這個(gè)索引選擇性最強(qiáng)的，即篩選后數(shù)據(jù)最少；比如：

 select * from tb where  grade = '一年級(jí)' and class = '3班' and student ='張三' 

如果grade、class、student三個(gè)字段都有索引，那么mysql只會(huì)用到student這一個(gè)選擇性最強(qiáng)的索引；

查詢(xún)條件的順序?qū)Σ樵?xún)效率沒(méi)有影響

where grade = '一年級(jí)' and class = '3班' and student ='張三'
等價(jià)于
where class = '3班' and student ='張三' and grade = '一年級(jí)'

組合索引的選擇

MySQL遵循最左前綴原理，當(dāng)查詢(xún)條件匹配聯(lián)合索引的前面幾列時(shí)，可以使用聯(lián)合索引；否則，不會(huì)使用聯(lián)合索引。

3、索引的實(shí)現(xiàn)方式

索引是一個(gè)排序的列表，在這個(gè)列表中存儲(chǔ)著索引的值和包含這個(gè)值的數(shù)據(jù)所在行的物理地址，在數(shù)據(jù)十分龐大的時(shí)候，索引可以大大加快查詢(xún)的速度，這是因?yàn)槭褂盟饕罂梢圆挥脪呙枞韥?lái)定位某行的數(shù)據(jù)，而是先通過(guò)索引表找到該行數(shù)據(jù)對(duì)應(yīng)的物理地址然后訪問(wèn)相應(yīng)的數(shù)據(jù)。

哈希索引

由InnoDB根據(jù)情況自動(dòng)生成，不能人為生成，只有memory（內(nèi)存）存儲(chǔ)引擎支持哈希索引，哈希索引用索引列的值計(jì)算該值的hashCode，然后在hashCode相應(yīng)的位置存執(zhí)該值所在行數(shù)據(jù)的物理位置，哈希索引不支持范圍查找和排序的功能。

全文索引

對(duì)于文本的大對(duì)象，或者較大的CHAR類(lèi)型的數(shù)據(jù)，如果使用普通索引，那么匹配文本前幾個(gè)字符還是可行的，但是想要匹配文本中間的幾個(gè)單詞，那么就要使用LIKE %word%來(lái)匹配，這樣需要很長(zhǎng)的時(shí)間來(lái)處理，響應(yīng)時(shí)間會(huì)大大增加，這種情況，就可使用時(shí)FULLTEXT索引了，在生成FULLTEXT索引時(shí)，會(huì)為文本生成一份單詞的清單，在索引時(shí)及根據(jù)這個(gè)單詞的清單來(lái)索引。

全文索引的查詢(xún)也有自己特殊的語(yǔ)法，而不能使用LIKE %查詢(xún)字符串%的模糊查詢(xún)語(yǔ)法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查詢(xún)字符串');

在MySQL中，全文索引支隊(duì)英文有用，目前對(duì)中文還不支持

BTree索引和B+Tree索引

B+樹(shù)：所有記錄節(jié)點(diǎn)按照鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上；
B樹(shù)：記錄會(huì)也會(huì)存在非葉子節(jié)點(diǎn)上

B+樹(shù)優(yōu)勢(shì)：

• 高扇出（一個(gè)非葉子節(jié)點(diǎn)16k可存儲(chǔ)更多的索引），意味著樹(shù)的高度低（一般最多3層），也就意味著查找所用的IO次數(shù)少
• 葉子節(jié)點(diǎn)通過(guò)指針來(lái)連接，范圍查找方便

4、InnoDB存儲(chǔ)表數(shù)據(jù)的物理結(jié)構(gòu)

image.png

• 表空間可以對(duì)應(yīng)一個(gè)表或者多個(gè)表
• 真實(shí)表數(shù)據(jù)只有一份，索引只存儲(chǔ)數(shù)據(jù)的主鍵，通過(guò)主鍵值找到對(duì)應(yīng)數(shù)據(jù)（聚集索引除外）

5、B+ 樹(shù)

B+樹(shù)的插入、刪除

引用
引用

聚集索引

聚集索引 = 全表數(shù)據(jù) + 基于主鍵大小構(gòu)建的B+樹(shù)
以主鍵為標(biāo)準(zhǔn)，在物理磁盤(pán)儲(chǔ)存為順序，一個(gè)表只有一個(gè)聚集索引

非聚集索引（輔助索引）

輔助索引 = 主鍵數(shù)據(jù) + 基于某列（或多列）數(shù)據(jù)構(gòu)建的B+樹(shù)
非葉子節(jié)點(diǎn)存儲(chǔ)的是某列（或多列）的值的索引，而葉子節(jié)點(diǎn)不存儲(chǔ)完整行數(shù)據(jù)，而是主鍵值

6、數(shù)據(jù)表的水平擴(kuò)容

引用

背景

當(dāng)我們?cè)谠O(shè)計(jì)一個(gè)表的時(shí)候，考慮到后續(xù)的數(shù)據(jù)增加，會(huì)用到分庫(kù)分表水平擴(kuò)展，比如將訂單表水平擴(kuò)展為5個(gè)，根據(jù)訂單號(hào)除以5取模來(lái)決定放到哪個(gè)表，但是如果數(shù)據(jù)增長(zhǎng)很快，當(dāng)一個(gè)表的數(shù)據(jù)超過(guò)500萬(wàn)時(shí)，對(duì)mysql性能影響很大，所以必須要繼續(xù)擴(kuò)容；

水平擴(kuò)容的難點(diǎn)

第一個(gè)就是規(guī)則的變化，意味著要改程序代碼
第二個(gè)就是數(shù)據(jù)遷移，因?yàn)閿?shù)據(jù)量已經(jīng)很大，如果做全量遷移的話(huà)，可能會(huì)面臨停機(jī)的問(wèn)題；

解決方案

• 按段分表
  在設(shè)計(jì)表的時(shí)候，根據(jù)id的值（自增長(zhǎng)，或者用時(shí)間）的范圍決定放到哪一張表，比如 [0 - 5000000]放在第一張表，[5000001 - 10000000] 放在第二張表，當(dāng)未來(lái)數(shù)據(jù)增長(zhǎng)到1500萬(wàn)的時(shí)候再添加第三張表，等等...
  但是這樣有個(gè)問(wèn)題：我們做分表就是為了減輕單表的壓力，需要將壓力分?jǐn)偟蕉鄠€(gè)地方提升性能？
• 分庫(kù)分表
  比如分3個(gè)庫(kù)，用id % 3確定放到哪個(gè)庫(kù)，這樣就將壓力分?jǐn)偟搅巳齻€(gè)數(shù)據(jù)庫(kù)實(shí)例；每個(gè)庫(kù)初始一張表，當(dāng)數(shù)據(jù)快超過(guò)500萬(wàn)時(shí)，創(chuàng)建第二張水平擴(kuò)展表；規(guī)則為：id [0 -1.5kw] 存每個(gè)庫(kù)的第一張表， [1.5kw - 3kw] 存第二張表 .....

7、Mysql 事務(wù)

引用

7.1 重做日志（redo log）

確保事務(wù)的持久性。防止在發(fā)生故障的時(shí)間點(diǎn)，尚有臟頁(yè)未寫(xiě)入磁盤(pán)，在重啟mysql服務(wù)的時(shí)候，根據(jù)redo log進(jìn)行重做，從而達(dá)到事務(wù)的持久性這一特性。

7.2 回滾日志（undo log）

保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個(gè)版本，可以用于回滾，同時(shí)可以提供多版本并發(fā)控制下的讀（MVCC），也即非鎖定讀

7.3 二進(jìn)制日志（binlog）：

用于復(fù)制，在主從復(fù)制中，從庫(kù)利用主庫(kù)上的binlog進(jìn)行重播，實(shí)現(xiàn)主從同步。
用于數(shù)據(jù)庫(kù)的基于時(shí)間點(diǎn)的還原。

8、鎖

引用

行鎖

InnoDB實(shí)現(xiàn)了兩種類(lèi)型的行鎖

• 共享鎖（讀鎖）：
  允許其他事務(wù)讀取數(shù)據(jù)，阻止其他事務(wù)獲取排它鎖（寫(xiě)鎖）
• 排它鎖（寫(xiě)鎖）：
  阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排它鎖；

加鎖觸發(fā)條件

mysql所有的寫(xiě)操作，都會(huì)在寫(xiě)之前去獲取排他鎖，如果獲取不到則等待，操作完了釋放鎖（如果在事務(wù)里，則需要事務(wù)commit了才會(huì)釋放鎖）；
如果所操作行能被索引到(explain時(shí)使用了索引)，則加行排他鎖，其他寫(xiě)操作此行都需要等待（不管用的哪個(gè)索引定位到此行）；否則會(huì)加表排他鎖，一旦加了表鎖，這個(gè)表的所有行數(shù)據(jù)的寫(xiě)操作都會(huì)阻塞；

加鎖方式

1. 對(duì)于寫(xiě)操作（inset、update、delete），innodb會(huì)自動(dòng)給要操作的數(shù)據(jù)集加排它鎖；
2. 對(duì)于普通的SELECT語(yǔ)句，innoDb不會(huì)加任何鎖
3. 如果在事務(wù)中，為了保證select數(shù)據(jù)的有效性，可以顯式的給數(shù)據(jù)集加共享鎖或排他鎖：
   3.1. 共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 。 其他 session 仍然可以查詢(xún)記錄，并也可以對(duì)該記錄加 share mode 的共享鎖。但是如果當(dāng)前事務(wù)需要對(duì)該記錄進(jìn)行更新操作，則很有可能造成死鎖。
   3.2. 排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查詢(xún)?cè)撚涗?，但是不能?duì)該記錄加共享鎖或排他鎖，而是等待獲得鎖

行鎖實(shí)現(xiàn)方式

InnoDB的行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，意味著只有通過(guò)索引條件檢索數(shù)據(jù)（explain時(shí)使用了索引），InnoDB才會(huì)使用行鎖，否則InnoDB將使用表鎖

MySQL樂(lè)觀鎖和悲觀鎖

• 悲觀鎖
  共享鎖、排他鎖 等都是悲觀鎖
• 樂(lè)觀鎖
  手動(dòng)實(shí)現(xiàn)，在表中加入version字段，每次查的時(shí)候取出，update的時(shí)候比較是否相等；