看了這篇文章，發現自己不懂資料庫
前言
索引： 單個索引、聯合索引、主鍵索引
事務： 四個粒度, 分別是資料庫級、表級、記錄級 (行級) 和屬性級(欄位級)
鎖：行鎖，表鎖；樂觀鎖，悲觀鎖
mysql優化： 分表，sql優化
生產資料庫都要求資料庫引擎設置為innodb，因為考慮到並發的優勢！
正文
0：資料庫引擎介紹
myisam是mysql資料庫的默認引擎，不支持事務，使用的鎖是表級別鎖，一次獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖，由於鎖的粒度比較大，所以並發能力一般！
InnoDB存儲引擎由於實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體並發處理能力方面要遠遠優於MyISAM的表級鎖定的。當系統並發量較高的時候，InnoDB的整體性能和MyISAM相比就會有比較明顯的優勢了。但是，InnoDB的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓InnoDB的整體性能表現不僅不能比MyISAM高，甚至可能會更差。
在InnoDB的事務管理和鎖定機制中，容易發生死鎖，所以有一套專門檢測死鎖存在的機制，當兩個事務發生死鎖後，會終斷小的事務，讓大的事務運行（大小，依照事務涉及數據量來評估）
1： 索引
分類：
普通索引(單列索引)：最基本的索引，沒有任何限制@hxx 常加在where列 和join 列上
Select people.age, ##不使用索引
town.name##不使用索引
FROM peopleLEFTJOINtown ON people.townid=town.townid ##考慮使用索引
Where firstname="Mike"##考慮使用索引
AND lastname="Sullivan"##考慮使用索引
唯一索引：與 "普通索引" 類似，不同的就是：索引列的值必須唯一，但允許有空值。
主鍵索引：它 是一種特殊的唯一索引，不允許有空值。@hxx就是一種唯一性索引
全文索引：僅可用於 MyISAM 表，針對較大的數據，生成全文索引很耗時好空間。
組合索引：為了更多的提高 mysql 效率可建立組合索引，遵循」 最左前綴 「原則。
最左側原則：例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 .
建立多個單列索引和組合索引的區別?
比如：Select peopleid
FROM people
Where firstname="Mike"
ANDlastname="Sullivan"ANDage=17;
將三個列都建立獨立的單列索引，和建立 ADD INDEX fname_lname_age (firstname,lastname,age);
區別在哪？
三個單索引，相當於查三次索引，每次範圍都縮小一次，就是row的結果集依次減少，最後得出結果，但是row的減少並不是最優的！
而建立組合索引，相當於只有一個索引塊了，利用b+樹的特點，只需要查一個索引就能快速跳轉得到結果！
創建索引的sql：
create （unique，）index on Tablename(列的列表)
alter table TableName add index (列的列表)
原理：
b+樹：每次查找數據時把磁碟IO次數控制在一個很小的數量級，最好是常數數量級，這就是b+樹的優勢。
哪些sql語句會用到索引？
列做比較的表達式，如=, >, >=,
like語句的條件是不以通配符開頭的常量串@hxx LIKE "Mich%" 這個會使用索引
col_name的列建了索引，則形如"col_name is null"的SQL會用到索引
where條件不只1個條件，則MySQL會進行Index Merge優化來縮小候選集範圍
優化：
a：列的區分度比較高的，加上索引會比較好！@hxx 無腦的做法是為常用的where 語句的欄位都加上索引，如果此欄位識別度比較低（比如sex欄位），那麼查詢效率不會有太大的提升，並且還會影響更新和插入的性能
b：
@hxx 參見美團這篇文章https://tech.meituan.com/mysql-index.html
2：事務
捋一下思路
事務的隔離性 造成的影響： 臟讀，不可重複讀，幻影讀
事務一致性造成的影響： 不要用並發來回答這個問題，一致性指的是從一個狀態符合預期的變成另一個狀態，acid其餘三個屬性都是為了一致性服務的！ 並發只的是多線程引起的，而一個事務就是一個線程，所以並發應該算到隔離性中。
事務是如何實現的？@hxx 總覺得redo log 和undo log只要一個即可！
redo log：保存執行的每一條sql log，當客戶端執行每條SQL（更新語句）時，redo log會被首先寫入log buffer；當客戶端執行COMMIT命令時，log buffer中的內容會被視情況刷新到磁碟@hxx 1s刷一次
undo log：undo log是為回滾而用，具體內容就是copy事務前的資料庫內容（行）到undo buffer，在適合的時間把undo buffer中的內容刷新到磁碟。 @hxx 回退，注意是拷貝數據
rollback segment：可以認為undo log和回滾段是同一意思，@hxx 是undo log的物理存儲形式
鎖：行鎖，表鎖，下段介紹
隔離級別：四種級別
@hxx redo log 和undo log 的區別？ redolog 和 binlog 的區別？
redo log 可以防止掉電數據恢復，只要重新執行sql語句即可恢復（從Checkpoint開始執行），undo log 主要用於出錯rollback，它沒有一個單獨的log文件，而是以資料庫內部一個特殊的數據段存在的，undo段位於共享表空間內！
一個事務執行的操作：
begin->用排他鎖鎖定該行->記錄redo log->記錄undo log->修改當前行的值，寫事務編號，回滾指針指向undo log中的修改前的行@hxx 事務編號，是為了mvcc
@hxx MyISAM不支持事務，但是不是不支持鎖哈
3：鎖@hxx 表鎖和行鎖 像HashTable 和CocurrentHashMap，粒度不一樣
@hxx 記得之前在解釋事務的時候，「事務不是加鎖的嗎」筆記，拿過鎖做比較！！
事務： 就是acid，也可以說四個特性是acid@hxx 資料庫的事務就是acid，我一直以為是「要麼全成功要麼都失敗」，這是原子性
鎖： 實現acid中，consistent 一致性的方式！
鎖的分類： 有兩種分類方式，一種是按對象（粒度），一種按模式，注意是組合的哈
按對象@hxx 下方有個表格，根據不同的引擎劃分
行級：開銷大，加鎖慢，因為粒度最小，鎖衝突的概率小，並發度高， 會出現死鎖@hxx 其他引擎實現，InnoDB存儲引擎, 表鎖是默認引擎myisam實現的
頁級（mysql特有）： 中等@hxxBerkeleyDB
表級： 開銷小，加鎖快，並發度低， 死鎖概率最低@hxx 也有說是不會死鎖的，MyISAM表鎖是deadlock free的
按模式
排它鎖（悲觀鎖，x鎖）：阻塞一切事務讀寫， 事務獲取排它鎖後能讀寫，其他事務不能讀寫
共享鎖（樂觀鎖，s鎖）：只阻塞寫， 事務獲得共享鎖後能讀不能寫，只有獲得排它鎖才能寫（@hxx當然如果當前行沒有加排它鎖，當然可以寫啦）
~~~innodb獨有的鎖
意向鎖，InnoDB也同樣使用了意向鎖（表級鎖定）的概念，也就有了意向共享鎖和意向排他鎖這兩種。@hxx 意向鎖是innodb的表鎖定的叫法
間隙鎖（Next-Key鎖）：對於鍵值在條件範圍內但並不存在的記錄，叫做「間隙（GAP)」，InnoDB也會對這個「間隙」加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。@hxx 因為幻影讀是增刪，對預期增加的項加鎖了，你就插不進來了，哈哈
假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101，下面的SQL：
mysql> select * from emp where empid > 100 for update;
是一個範圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大於101（這些記錄並不存在）的「間隙」加鎖。
@hxx 鎖按對象和按模式，是組合的哈，不是對立的，比如 innodb 引擎 默認使用行級鎖，其行級鎖又有x鎖或s鎖，具體什麼時候用哪種，則
鎖與鎖之間的共存和互斥關係如下：
如何加鎖@hxx 按照引擎來
MyISAM：在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。@hxx myisam 是讀讀共享鎖，寫+其他 阻塞，並且默認是表鎖
@hxx 當一個讀事務過來，然後再來一個寫事務，myisam先執行哪個？
讀事務先進鎖等待隊列，寫事務插隊（優先順序高），會插到隊列前面，所以先執行寫 （非公平鎖）
InnoDB：行鎖是通過給索引上的索引項加鎖來實現的，只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖@hxx 兩次query涉及到同一行，不一定會觸碰到行鎖，必須要涉及到同一個索引鍵！
@hxx ，可否理解為，只要query中使用了索引，就一定觸碰到行鎖
答案不一定，當某個query對應的表的數據量非常小，mysql不一定會使用索引，因為全表掃描的代價可能更低，這個時候使用到的是表鎖
該如何選擇
當事務所涉及的數據比較大，並且希望小概率的出現死鎖，則選擇表鎖，表鎖主要是MyISAM@hxx 所以需要知道並發的情況，show status like "table%";查看mysql內部表被鎖的情況
大量按索引條件並發，選擇行鎖，主要是InnoDB存儲引擎
如何避免死鎖@hxx 下面所列的幾點都是針對innodb的，因為myisam不支持事務哈
通常來說，死鎖都是應用設計的問題，通過調整業務流程、資料庫對象設計、事務大小，以及訪問資料庫的SQL語句，絕大部分死鎖都可以避免。
具體的方式有：
a：控制事務執行的順序，比如控制表的串列訪問減少並行，
b： 在最開始就申請最嚴格的鎖，比如更新數據時，申請排它鎖，不要先申請共享鎖等到執行update再申請排它鎖@hxx 犧牲性能，換取避免死鎖
c：在REPEATABLE-READ隔離級別下：查詢的時候不準修改！ 即查詢會在當前行修改上加排它鎖，，這個時候如下case會出現死鎖，如果兩個事務，同時查詢過來，都加排它鎖，發現沒有記錄，則插入，這個時候互相等待對方的鎖，造成死鎖 ， 解決方案是，修改隔離級別為 read-commited， 即防止臟讀，即修改的時候對讀加排他鎖，而讀的時候並不會加鎖（會出現不可重複讀）@hxx 非常重要的點！
d：當隔離級別為READ COMMITTED時，還是上面的case，兩個事務同時過來，同時查詢記錄（不會加排它鎖），如果沒有記錄，則插入，加排它鎖的，只有一個能插入成功，另一個沒獲得鎖等待，一個插入成功另一個繼續插入，但是記錄存在了，報主鍵存在異常，如果異常後不釋放排它鎖，則第三個事務過來申請鎖的時候就會死鎖
，所以需要finally中釋放鎖！
4：MVCC@hxx innodb中的概念，Multiversion Concurrency Control， 只針對read repeatable
鎖機制可以控制並發操作,但是其系統開銷較大,而MVCC可以在大多數情況下代替行級鎖,使用MVCC,能降低其系統開銷。@hxx 注意是替代行鎖的！
mysql 如何實現mvcc？
下圖展示了db一條記錄，需要記錄的內容，與mvcc 相關的是 ，三個隱藏欄位 （標藍的）
6位元組的DATA_TRX_ID 標記了最新更新這條行記錄的transaction id，每處理一個事務，其值自動+1，@hxx 就是系統版本號的值，是自動增加的
DATA_ROLL_PTR是記錄undo日誌的位置指針，用於找到之前版本的數據回滾
DELETE BIT位用於標識該記錄是否被刪除，這裡的不是真正的刪除數據，而是標誌出來的刪除。真正意義的刪除是在commit的時候
@hxx 實現原理，在每一行數據中，多加了3列，事務版本號 和 刪除標記位，按照下面的操作寫入這兩個列的數據：
begin->用排他鎖鎖定該行->記錄redo log->記錄undo log->修改當前行的值，寫事務編號，回滾指針指向undo log中的修改前的行
mvcc 的具體過程：
SELECT： 比如具備兩個要求的數據才會被返回：
a： 查詢事務的版本號，大於或等於 row 中記錄版本號@hxx 意思就是只會查詢已存在的數據或者是本事務操作的數據
b： 行的刪除操作的版本一定是未定義的或者大於當前事務的版本號@hxx 保證沒有被刪除
insert：InnoDB為每個新增行記錄當前系統版本號作為創建ID@hxx 此時 刪除版本號為null
update：InnoDB複製了一行，有新老兩行數據啦。新行的事務id為系統版本號，新行的刪除id為null；老行的 事務id不變， 刪除id為當前系統版本號
delete：InnoDB為每個刪除行的記錄當前系統版本號作為行的刪除ID。
為何適用於REPEATABLE READ？
REPEATABLE READ，可重複讀，完全適用MVCC，只能讀取在它開始之前已經提交的事務對資料庫的修改,在它開始以後,所有其他事務對資料庫的修改對它來說均不可見
原因：當有事務a讀取row a， 事務b 修改了row a （commit後版本號change），事務a再讀取，不會得到a的最新數據； 如果事務b修改沒有commit，row a被a事務再次讀取的時候是不受影響的！ 這個過程中都是並發的，沒有鎖的影響，所以說mvcc 能提高性能！
5：常見優化手段
表設計優化：略
sql優化：略
sql查詢的過程：略
6： 常見設計思想
分表之後想讓一個 id 多個表是自增的@hxx 並不要求多個表是順序的遞增的
a：新建一個id表來管理id，做法是來一條數據，在插入對應表前，先從id表插入一個自增id，然後獲取最大的id，這個id連同數據即可插入了
劣勢： mysql 的auto increment 有鎖，新能可能比較低
b：redis 自增id功能來實現
c：隊列服務，在隊列中預分配一批id，實時監控隊列的數據長度，及時往隊列中put id保證充足@hxx 能控制隊列中id的規則， 比如qq的靚號放在一個另一個隊列中，實現盈利
d：mysql id 區間隔離 : 不同分庫設置不同的起始值和步長，比如 2 台 mysql，就可以設置一台只生成奇數，另一台生成偶數. 或者 1 台用 0~10 億，另一台用 10~20 億. 優勢：利用 mysql 自增 id 缺點：運維成本比較高，數據擴容時需要重新設置步長
TAG:蝙蝠俠Lee |