1.mysql中數(shù)據(jù)庫(kù)與數(shù)據(jù)庫(kù)實(shí)例的區(qū)別
mysql性能的處理方式：
異步（日志記錄），備份（從庫(kù)），版本號(hào)（事務(wù)），標(biāo)識(shí)（鎖），多線程（鏈接池），批量處理（批量排序按索引查詢(xún)），內(nèi)存（各種buffer）,排序（索引），盡可能的記錄標(biāo)識(shí)（異常重啟之后根據(jù)標(biāo)識(shí)判斷分情況還原處理）
遺忘的概念：
1.鎖
全局鎖：是給整個(gè)庫(kù)加鎖
表鎖：給整個(gè)表加鎖
行鎖：給掃描到的所有行加鎖（一個(gè)沒(méi)有索引的update會(huì)給整個(gè)表現(xiàn)有的行加鎖）
行鎖加鎖和釋放的原則：
? ? ? 讀已提交隔離級(jí)別下
? ? ? select * from t where d = 5 for update (d列沒(méi)索引)(就算d列有索引根據(jù)掃描的過(guò)程也可能掃描到多行)
? ? ? sql執(zhí)行過(guò)程中：掃描全表，這時(shí)掃描到的行都加鎖 掃描到的間歇也加間歇鎖 就是next_key lock
? ? ? sql執(zhí)行完之后：不滿(mǎn)足條件的行 d != 5 的行 next_key lock都會(huì)被去掉
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? d = 5 的行還是得等到事務(wù)提交時(shí)行鎖才釋放
間歇鎖：給掃描到的索引區(qū)間加鎖
行鎖用來(lái)鎖定涉及到的行（保證一致性），間歇鎖用來(lái)鎖定涉及到的區(qū)間（防止幻讀）可以根據(jù)業(yè)務(wù)場(chǎng)景反推為什么這么用鎖
2.mvcc處理事務(wù)，是根據(jù)版本號(hào)對(duì)比處理，不一樣的事務(wù)加版本號(hào)的時(shí)機(jī)不一樣
3.事務(wù)的隔離級(jí)別是就當(dāng)前事務(wù)而言的
4. MVCC控制事務(wù)的讀取的可見(jiàn)性，鎖是用來(lái)控制讀寫(xiě)造成的沖突，鎖和mvcc共同實(shí)現(xiàn)事務(wù)的效果
5.索引是一種數(shù)據(jù)結(jié)構(gòu)，數(shù)據(jù)行都在主鍵索引下邊，普通索引下是主鍵id
數(shù)據(jù)行 數(shù)據(jù)頁(yè)的概念
6.可重復(fù)讀級(jí)別下會(huì)觸發(fā)"間歇鎖"
7.只有當(dāng)前讀才有幻讀的問(wèn)題? 有了間歇鎖當(dāng)前讀也不會(huì)出現(xiàn)幻讀 因?yàn)閿?shù)據(jù)insert不了
普通的讀mvcc就能解決，當(dāng)前讀需要next_key lock解決
8.可重復(fù)讀就解決了幻度，串行化是通過(guò)表鎖解決的
三范式? 表設(shè)計(jì)? 字段屬性選用? 索引設(shè)計(jì)
了解HBASE
分庫(kù) 分表的利弊
一、Mysql的結(jié)構(gòu)
1.mysql中數(shù)據(jù)庫(kù)與數(shù)據(jù)庫(kù)實(shí)例的區(qū)別
數(shù)據(jù)庫(kù)是文件的集合，是依照某種數(shù)據(jù)模型組織起來(lái)存放于二級(jí)存儲(chǔ)器中的數(shù)據(jù)的集合
數(shù)據(jù)庫(kù)實(shí)例是程序，所有對(duì)數(shù)據(jù)庫(kù)的操作必須在數(shù)據(jù)庫(kù)實(shí)例下進(jìn)行
2.mysql數(shù)據(jù)庫(kù)的組成
連接池、管理服務(wù)和工具的組件、sql接口組件、查詢(xún)分析器、優(yōu)化器、緩沖組件、存儲(chǔ)引擎、物理文件
存儲(chǔ)引擎：是基于底層物理結(jié)構(gòu)的實(shí)現(xiàn)，是基于表的不是數(shù)據(jù)庫(kù)
3.innodb的介紹
特點(diǎn)：支持行鎖設(shè)計(jì)、mvcc、外鍵、提供一致性非鎖定讀、能有效利用內(nèi)存和cpu
3.1innodb的架構(gòu)
包括：多個(gè)后臺(tái)線程、和內(nèi)存池（緩存）
后臺(tái)線程保證內(nèi)存池中的數(shù)據(jù)和緩存中的數(shù)據(jù)一致，將緩存中修改過(guò)的數(shù)據(jù)刷新到數(shù)據(jù)庫(kù)，同時(shí)將數(shù)據(jù)最新的數(shù)據(jù)同步到緩存
3.1.1后臺(tái)線程
Master Thread 將緩存池中的數(shù)據(jù)異步刷新到磁盤(pán)，保證數(shù)據(jù)一致性
IO Thread innodb中大量使用AIO來(lái)操作數(shù)據(jù)庫(kù)讀寫(xiě)，write、read、insertBuffer、logThread
Purge Thread
3.1.2innodb內(nèi)存
innodb的緩存
為了彌補(bǔ)磁盤(pán)操作較慢的問(wèn)題，innodb對(duì)數(shù)據(jù)庫(kù)加了一步緩存操作
1.讀取數(shù)據(jù)，在數(shù)據(jù)庫(kù)讀取頁(yè)的操作時(shí)，首先將從磁盤(pán)讀到的頁(yè)放到緩存池當(dāng)中，這個(gè)過(guò)程稱(chēng)將頁(yè)“FIX”在緩存池中，下次讀取相同的頁(yè)時(shí)先判斷該頁(yè)是否在緩存池中。若在緩存池中則命中直接讀取該頁(yè)，若沒(méi)有則從磁盤(pán)中讀取。
2.修改數(shù)據(jù)，首先修改在緩存池中的頁(yè)，然后再以一定的頻率刷到磁盤(pán)上，不是每次更新都會(huì)觸發(fā)緩存和磁盤(pán)的同步，而是通過(guò)一種Checkpoint的機(jī)制。
連接池
HikariCP：目前速度最快的連接池，優(yōu)化了代理和攔截器，簡(jiǎn)化了代碼 新增了fastList，適合做監(jiān)控
Durid：經(jīng)過(guò)阿里大規(guī)模數(shù)據(jù)的檢驗(yàn)比較可靠
DBCP/C3P0速度比價(jià)慢
按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count(*)
二、sql的執(zhí)行過(guò)程
2.1查詢(xún)sql的執(zhí)行過(guò)程
連接器（連接數(shù)據(jù)庫(kù)）、（緩存執(zhí)行請(qǐng)求先走緩存（8.0之前））、分析器（分析請(qǐng)求類(lèi)型、表、列 檢查sql的正確性）、優(yōu)化器（選擇高效率的執(zhí)行策略）、執(zhí)行器（調(diào)用存儲(chǔ)引擎的接口獲取數(shù)據(jù)）
2.2修改sql的執(zhí)行過(guò)程
update T set c=c+1 where id = 2? 數(shù)據(jù)表使用了innodb
同樣是經(jīng)歷 連接器 緩存 分析器 優(yōu)化器 執(zhí)行器
先從緩存或磁盤(pán)中取出id=2的數(shù)據(jù)，讓后將取出的c的值加一
將修改后的這條數(shù)據(jù)更新到innodb的內(nèi)存中同時(shí)將更新的內(nèi)容更新到redo日志中，此時(shí)redo是prepare狀態(tài)，然后告知執(zhí)行器執(zhí)行完了，隨時(shí)可以提交事務(wù)
然后執(zhí)行器生成這波操作的binlog日志），當(dāng)當(dāng)前這個(gè)事務(wù)提交后redo log和binlog同時(shí)完成寫(xiě)入
這里redo log 和 binlog是分兩階段提交的，先是準(zhǔn)備后是提交，兩個(gè)日志要么都寫(xiě)入成功要么都寫(xiě)入不成功
redo log 和 binlog的區(qū)別：
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。
redo log是物理日志(保證事務(wù)的持久性)，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”；binlog 是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。
redo log 是循環(huán)寫(xiě)的，空間固定會(huì)用完；binlog 是可以追加寫(xiě)入的。“追加寫(xiě)”是指 binlog 文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。
Binlog有兩種模式，statement 格式的話是記sql語(yǔ)句， row格式會(huì)記錄行的內(nèi)容，記兩條，更新前和更新后都有
測(cè)試：從redo log功能角度解釋一下innodb的緩存
check point 的觸發(fā)時(shí)機(jī)：redo log 要寫(xiě)滿(mǎn)了、緩存不足、空閑時(shí)間、MySQL關(guān)閉
2.3change buffer
使用場(chǎng)景：
1.當(dāng)要修改一條數(shù)據(jù)，
2.這條數(shù)據(jù)在內(nèi)存中不存在
3.先將修改的操作記錄在change buffer中
4.當(dāng)再次讀取這條數(shù)據(jù)時(shí)，先將數(shù)據(jù)讀取到內(nèi)存中再用change buffer中記錄的邏輯來(lái)修改內(nèi)存中的數(shù)據(jù)
如果內(nèi)存中存在就直接修改內(nèi)存了? 唯一索引因?yàn)楸仨氁x取數(shù)據(jù)判斷數(shù)據(jù)是否沖突所以不適用這種場(chǎng)景，change buffer 就是為了減少磁盤(pán)的讀取io? rodo log是為了減少寫(xiě)io
思考問(wèn)題點(diǎn)：
1.innodb中 緩存，change buffer，redo log ，磁盤(pán)的關(guān)系
2.change buffer的使用場(chǎng)景（普通索引，寫(xiě)操作大于讀操作）
2.4查詢(xún)中的排序算法（臨時(shí)表）
核心：通過(guò)sort_buffer將order by的列存起來(lái)然后做排序 再根據(jù)排序好的列來(lái)取數(shù)據(jù)拼成結(jié)果集
查詢(xún)的列很少 走內(nèi)存臨時(shí)表
select word from t where time <2020-05-06 order by word;
內(nèi)存臨時(shí)表的儲(chǔ)存引擎不是innodb所以需要“位置"這種數(shù)據(jù)
如果有分頁(yè)，會(huì)將符合條件的數(shù)據(jù)都查出然后計(jì)算需要返回多少行到多少行
如果where條件和要排序的列屬于同一個(gè)索引那就不需要排序，因?yàn)槟玫降臄?shù)據(jù)就是有序的
查詢(xún)的列很多就根據(jù)索引走磁盤(pán)
select city,name,age,id_card, address,time from t where city = "杭州" order by name;
大查詢(xún)會(huì)不會(huì)把MySQL內(nèi)存打爆？
不會(huì)
mysql采用的策略是“邊讀邊發(fā)”，mysql引入netBuffer的概念，一塊內(nèi)存空間，每次將讀取到的數(shù)據(jù)放入netBuffer中netBuffer滿(mǎn)了之后調(diào)用網(wǎng)絡(luò)接口將數(shù)據(jù)發(fā)送出去然后將netBuffer清空進(jìn)入下一輪的讀取，如果本地網(wǎng)絡(luò)棧寫(xiě)滿(mǎn)了就等待直到網(wǎng)絡(luò)棧重新可寫(xiě)再次發(fā)送
三、數(shù)據(jù)庫(kù)的事務(wù)
讀未提交 讀已提交 可重復(fù)讀 可重復(fù)讀是站在當(dāng)前事務(wù)的角度來(lái)說(shuō)的
事務(wù)的屬性：acid 原子性? 一致性 隔離性 持久性
事務(wù)解決的問(wèn)題：臟讀 不可重復(fù)讀 幻讀
四個(gè)隔離級(jí)別：
讀未提交：可以讀取到未交的數(shù)據(jù)
讀已提交：可以讀取到提交后的數(shù)據(jù)
可重復(fù)讀：在A事務(wù)中對(duì)某條數(shù)據(jù)讀取了兩次，這兩次中間B事務(wù)對(duì)該條數(shù)據(jù)做了修改，但是A事務(wù)兩次讀取到的還是一樣的數(shù)據(jù)（MySQL默認(rèn)的隔離級(jí)別）
串行化：讀加讀鎖，寫(xiě)加寫(xiě)鎖
我們來(lái)看看在不同的隔離級(jí)別下，事務(wù) A 會(huì)有哪些不同的返回結(jié)果，也就是圖里面 V1、V2、V3 的返回值分別是什么。
若隔離級(jí)別是“讀未提交”， 則 V1 的值就是 2。這時(shí)候事務(wù) B 雖然還沒(méi)有提交，但是結(jié)果已經(jīng)被 A 看到了。因此，V2、V3 也都是 2。
若隔離級(jí)別是“讀提交”，則 V1 是 1，V2 的值是 2。事務(wù) B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
若隔離級(jí)別是“可重復(fù)讀”，則 V1、V2 是 1，V3 是 2。之所以 V2 還是 1，遵循的就是這個(gè)要求：事務(wù)在執(zhí)行期間看到的數(shù)據(jù)前后必須是一致的。
若隔離級(jí)別是“串行化”，則在事務(wù) B 執(zhí)行“將 1 改成 2”的時(shí)候，會(huì)被鎖住。直到事務(wù) A 提交后，事務(wù) B 才可以繼續(xù)執(zhí)行。所以從 A 的角度看， V1、V2 值是 1，V3 的值是 2。
隔離級(jí)別的實(shí)現(xiàn)方式：視圖（read-view）和鎖、回滾日志
數(shù)據(jù)庫(kù)里會(huì)創(chuàng)建一個(gè)視圖，訪問(wèn)的時(shí)候以視圖的邏輯為準(zhǔn)
讀未提交：直接返回記錄上的最新值
讀已提交：在SQL語(yǔ)句執(zhí)行的時(shí)候創(chuàng)建
可重復(fù)讀：在事務(wù)開(kāi)始的時(shí)候創(chuàng)建
串行化：直接通過(guò)加鎖的方式來(lái)避免并行訪問(wèn)，當(dāng)讀寫(xiě)鎖沖突的時(shí)候，后一個(gè)事務(wù)等前一個(gè)事務(wù)執(zhí)行完在執(zhí)行
回滾日志：
MySQL的每次變更都會(huì)記錄一條對(duì)應(yīng)的回滾操作，記錄上的最新值可以通過(guò)回滾操作得到前一個(gè) 狀態(tài)的值
當(dāng)一個(gè)事務(wù)回滾時(shí)，可一個(gè)通過(guò)回滾操作恢復(fù)數(shù)據(jù)
當(dāng)沒(méi)有視圖需要回滾日志時(shí)，回滾日志就會(huì)刪除
事務(wù)的詳解
請(qǐng)仔細(xì)看一遍：第八講事務(wù)到底是隔離的還是不隔離的
MVCC一行數(shù)據(jù)只有一個(gè)記錄不是有多個(gè)紀(jì)錄，但是會(huì)記錄當(dāng)前最新的版本號(hào)，如果想看到之前的版本需要根據(jù)undo log 來(lái)計(jì)算
行的版本號(hào)和事務(wù)的id是一致的
可重復(fù)讀的事務(wù)里有修改語(yǔ)句修改語(yǔ)句是當(dāng)前讀
MVCC適用于“可重復(fù)讀”，思考 讀未提交，讀已提交，insert語(yǔ)句 是怎么處理可見(jiàn)性的
長(zhǎng)事務(wù)對(duì)MySQL的影響？
一個(gè)事務(wù)的執(zhí)行時(shí)間過(guò)長(zhǎng)，在整個(gè)事務(wù)中回滾段一直未被清理，大量的回滾日志占用很多磁盤(pán)空間
同時(shí)還占用鎖資源，極大加重了整個(gè)庫(kù)的壓力
幻讀的概念
讀到了“新插入的數(shù)據(jù)行”
同一個(gè)事務(wù)中兩次查詢(xún)第二次查到了新插入的數(shù)據(jù)行（普通的查詢(xún)是“快照讀”，“當(dāng)前讀”的情況下才會(huì)出現(xiàn)“幻讀”）
最后一行出現(xiàn)了幻讀
四、索引
InnoDB 的數(shù)據(jù)是保存在主鍵索引上的
平衡二叉樹(shù) B+樹(shù)? https://www.toutiao.com/a6624750730471277059/
索引覆蓋 ：想要查詢(xún)的結(jié)果集就是索引不用回表
索引最左原則：ABC索引 適合 A? AB ABC 也是適合 like "xx%"
索引下推 ：聯(lián)合索引 "name,age"? select * from custom where name like "張%" and age = 10;
為什么主鍵自增要好？
1.自增起始數(shù)據(jù)小占用空間小
2.索引采用的是B+樹(shù)，數(shù)據(jù)自增可以減少“分裂”和“合并”
? 分裂：當(dāng)出現(xiàn)中間數(shù)據(jù)時(shí)，B+樹(shù)會(huì)觸發(fā)分裂，MySQL也會(huì)申請(qǐng)新的數(shù)據(jù)將原來(lái)數(shù)據(jù)頁(yè)中分出的數(shù)據(jù)拷貝過(guò)去，這個(gè)過(guò)程比較消耗資源同時(shí)原來(lái)的數(shù)據(jù)頁(yè)未被使用完浪費(fèi)了空間
? 合并：當(dāng)相鄰的兩個(gè)數(shù)據(jù)頁(yè)刪除了數(shù)據(jù)，可能觸發(fā)合并是分裂的逆過(guò)程同樣消耗性能
為什么B+樹(shù)適合建索引？
1.B+樹(shù)非葉子節(jié)點(diǎn)沒(méi)有關(guān)鍵字指針只存有關(guān)鍵字，非葉子節(jié)點(diǎn)可以存放更多的數(shù)據(jù)，所有樹(shù)的高度比較低，查詢(xún)的速度更快
2.非葉子節(jié)點(diǎn)存放的都是索引，數(shù)據(jù)都存放在葉子節(jié)點(diǎn)，方便掃庫(kù)也適合區(qū)間查詢(xún)，像B樹(shù)非葉子節(jié)點(diǎn)也存放有數(shù)據(jù)，找到具體的數(shù)據(jù)需要進(jìn)行中序遍歷才可以
普通索引和唯一索引在查詢(xún)性能上的差異大不大？
不大
1.唯一索引在索引樹(shù)上讀到符合條件的數(shù)據(jù)就不再讀取
2.普通索引是讀取到符合條件的數(shù)據(jù)之后會(huì)接著讀取下一條，直到讀到不符合條件的數(shù)據(jù)為止
3.innodb數(shù)據(jù)的讀取是以頁(yè)為單位來(lái)讀取的，一次會(huì)將一頁(yè)數(shù)據(jù)從磁盤(pán)中全部讀出一頁(yè)默認(rèn)大小16kb
所以差別不大
字符串索引的三個(gè)小技巧：
1.采用前綴索引，將字符串的前幾個(gè)值作為索引，可以減少空間占用
2.像身份證號(hào)這種數(shù)據(jù) 前面的數(shù)字都相同后邊幾位不同，存儲(chǔ)的時(shí)候可以倒著存儲(chǔ)然后用前綴索引
3.將長(zhǎng)字符串取hash值通過(guò)hash值建立索引
where條件中有函數(shù)不走索引：
select * from tradelog where tradeid=110717;（tradeild列是varchar類(lèi)型有索引，但是這條語(yǔ)句卻沒(méi)走索引）
select * from tradelog where? CAST(tradid AS signed int) = 110717;（因?yàn)閠ardeild是varchar類(lèi)型所以?xún)?yōu)化器需要將110717轉(zhuǎn)換為字符串類(lèi)型，然后再執(zhí)行，轉(zhuǎn)換使用了函數(shù)所以不適用索引）
兩個(gè)表的字符集不同，一個(gè)是 utf8，一個(gè)是 utf8mb4，所以做表關(guān)聯(lián)查詢(xún)的時(shí)候用不上關(guān)聯(lián)字段的索引
兩個(gè)表字符集不同在優(yōu)化器會(huì)使用函數(shù)修改sql將關(guān)聯(lián)的那一列的數(shù)據(jù)的字符集修改為一樣的，因?yàn)槭褂昧撕瘮?shù)所以不走索引
五、數(shù)據(jù)庫(kù)鎖
全局鎖：FTWRL將整個(gè)庫(kù)設(shè)置成只讀狀態(tài)，增刪改，建表，修改表的操作都不能執(zhí)行，影響范圍很大，同時(shí)也影響主從同步；如果是innodb可以使用事務(wù)（可重復(fù)讀）也可以實(shí)現(xiàn)多次讀是一樣的數(shù)據(jù)
表鎖：
顯示加鎖，lock tables T read/write
非顯示加鎖，MDL 當(dāng)訪問(wèn)表時(shí)（增刪改查）數(shù)據(jù)庫(kù)默認(rèn)加上表鎖讀鎖，當(dāng)對(duì)數(shù)據(jù)表有結(jié)構(gòu)修改時(shí)默認(rèn)加上表鎖寫(xiě)鎖，讀鎖之間不互斥可以有多個(gè)，讀鎖和讀鎖，寫(xiě)鎖和讀鎖之間互斥
修改表結(jié)構(gòu)導(dǎo)致鎖表的問(wèn)題 就是因?yàn)樽x寫(xiě)鎖互斥導(dǎo)致的，寫(xiě)鎖沒(méi)有獲取到會(huì)一直阻塞后邊的請(qǐng)求，當(dāng)寫(xiě)鎖獲取到后 執(zhí)行了修改表結(jié)構(gòu)的語(yǔ)句 后邊的業(yè)務(wù)語(yǔ)句才能執(zhí)行
如何安全的給表加字段？
1.執(zhí)行alter table語(yǔ)句之前確定沒(méi)有長(zhǎng)事務(wù)
2.執(zhí)行語(yǔ)句中加入等待時(shí)間，在等待時(shí)間內(nèi)沒(méi)有獲取到寫(xiě)鎖，先不執(zhí)行 讓業(yè)務(wù)語(yǔ)句執(zhí)行；
行鎖：給掃描到的所有行加鎖，行鎖是存儲(chǔ)引擎層面的概念不同的存儲(chǔ)引擎有不同的行鎖實(shí)現(xiàn)方式，MyISAM沒(méi)有行鎖
事務(wù)中的行鎖，在sql執(zhí)行時(shí)創(chuàng)建在事務(wù)提交后釋放
行鎖死鎖的例子：
如何解決死鎖問(wèn)題？
1.設(shè)置鎖的等待時(shí)間 innodb_lock_wait_timeout 超過(guò)時(shí)間 第一個(gè)被鎖住的線程要過(guò) 50s默認(rèn) 才會(huì)超時(shí)退出，然后其他線程才有可能繼續(xù)執(zhí)行
2.設(shè)置死鎖檢測(cè) 參數(shù) innodb_deadlock_detect 設(shè)置為 on 發(fā)現(xiàn)死鎖后，主動(dòng)回滾死鎖鏈條中的某一個(gè)事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行
3.也可以從數(shù)據(jù)表的設(shè)計(jì)上避免，像進(jìn)行賬號(hào)金額的操作，將一個(gè)賬號(hào)改為多個(gè)賬號(hào)，每次需要處理余額可以隨機(jī)選取一個(gè)賬號(hào)來(lái)處理，最終所有的賬號(hào)余額的和為總賬號(hào)的余額，這樣減少了行死鎖的概率；
當(dāng)前讀的概念：
修改語(yǔ)句是當(dāng)前讀，是先查詢(xún)?cè)傩薷?/p>
即使一個(gè)修改語(yǔ)句在可重復(fù)讀的事務(wù)里，也會(huì)去讀取最新的已提交的數(shù)據(jù)
修改語(yǔ)句同時(shí)會(huì)給涉及的行加寫(xiě)鎖，（沒(méi)有事務(wù)的情況下）語(yǔ)句執(zhí)行完鎖釋放
select語(yǔ)句后加for update也是同樣的操作（當(dāng)前讀 加寫(xiě)鎖）
mysql是如何運(yùn)用行鎖、間歇鎖解決幻讀的？
update t set c = 5 where d = 2;
update語(yǔ)句會(huì)添加寫(xiě)鎖
假如d列沒(méi)有索引，就會(huì)觸發(fā)全局掃描，mysql會(huì)給所有掃描到的所有行加鎖
insert into t (id , c , d),(1,4,2);
新插入一條d=5的數(shù)據(jù)，新插入的數(shù)據(jù)沒(méi)有被掃描到不會(huì)加鎖（容易引發(fā)幻讀）
間歇鎖：將涉及到的行的區(qū)間給加鎖（包括兩端）
下圖中間歇鎖 鎖定的區(qū)間有（-負(fù)無(wú)窮，0]，(0,5]，(5,10]，(10,15]，(15,20]，(20,25]，(25,正無(wú)窮]
有了間歇鎖就能解決幻讀的問(wèn)題
間歇鎖的兩原則，兩優(yōu)化，一個(gè)bug
原則 1：加鎖的基本單位是 next-key lock。希望你還記得，next-key lock 是前開(kāi)后閉區(qū)間。
原則 2：查找過(guò)程中訪問(wèn)到的對(duì)象才會(huì)加鎖。
優(yōu)化 1：索引上的等值查詢(xún)，給唯一索引加鎖的時(shí)候，next-key lock 退化為行鎖。
優(yōu)化 2：索引上的等值查詢(xún)，向右遍歷時(shí)且最后一個(gè)值不滿(mǎn)足等值條件的時(shí)候，next-key lock 退化為間隙鎖。
一個(gè) bug：唯一索引上的范圍查詢(xún)會(huì)訪問(wèn)到不滿(mǎn)足條件的第一個(gè)值為止。
間歇鎖鎖住行的范圍更大，更容易導(dǎo)致死鎖
六、表空間
1.innodb中表數(shù)據(jù)的存儲(chǔ)方式
表結(jié)構(gòu)：MySQL8.0前是存放在單獨(dú)的文件里，8.0之后是存儲(chǔ)在系統(tǒng)數(shù)據(jù)表里
表數(shù)據(jù)：可以通過(guò)配置選擇 一種是存儲(chǔ)在共享表空間，一種是一張表一個(gè)文件的形式存儲(chǔ)
為什么刪除了表數(shù)據(jù)表空間大小沒(méi)變？
mysql中數(shù)據(jù)是以數(shù)據(jù)頁(yè)的形式存儲(chǔ)
當(dāng)刪除一行數(shù)據(jù)時(shí)，會(huì)將這個(gè)行標(biāo)記為可復(fù)用
當(dāng)刪除也一頁(yè)數(shù)據(jù)時(shí)會(huì)將這個(gè)頁(yè)標(biāo)記為可復(fù)用
所以刪除數(shù)據(jù)后占用的表空間不會(huì)變
如果真正的想收縮表空間可以通過(guò)重建表的方式實(shí)現(xiàn)，重建的過(guò)程中會(huì)重建索引重新分配內(nèi)存
? ? ? 命令：alter table A engine=InnoDB? 這是一個(gè)DDL的過(guò)程全程不能用寫(xiě)和修改的操作
? ? ? mysql5.7之后有online DDL的操作 重建表的過(guò)程中允許添加和修改，它會(huì)把修改和添加的操作記錄到日志文件中，重建完成之后將日志里的邏輯執(zhí)行一遍
MDL：表級(jí)鎖
DML：指的是增刪改查的操作
DDL：指的是修改表結(jié)構(gòu)的操作
七、性能分析
一條簡(jiǎn)單的查詢(xún)語(yǔ)句查詢(xún)速度很慢
mysql> CREATE TABLE `t` (
? `id` int(11) NOT NULL,
? `c` int(11) DEFAULT NULL,
? PRIMARY KEY (`id`)
) ENGINE=InnoDB;
select * from t where id=1;
1.等DML鎖
show processlist 可以查看當(dāng)前sql語(yǔ)句的執(zhí)行情況
是表被鎖住了，找到鎖表的線程直接kill掉
2.等f(wàn)lush
flush tables t;
本身flush執(zhí)行速度很快，可是flush如果被其他事物阻塞了，flush就會(huì)阻塞新的查詢(xún)語(yǔ)句
其他事務(wù)中對(duì)t表有操作，flush會(huì)等待其他事務(wù)執(zhí)行完再對(duì)t表進(jìn)行flush
這個(gè)時(shí)候還是通過(guò) show processlist命令查看當(dāng)前sql執(zhí)行情況
3.等待行鎖
select * from t where id = 2; select語(yǔ)句默認(rèn)不加讀鎖
select * from t where id = 2lock in share mode;顯式加讀鎖
當(dāng)前的行在其他事務(wù)中有寫(xiě)鎖，這條語(yǔ)句就會(huì)等待寫(xiě)鎖釋放才能讀取
4.數(shù)據(jù)量很大也沒(méi)有索引
5.一致性讀，讀取的是老版本的數(shù)據(jù)，其他事務(wù)做了大量修改，需要執(zhí)行大量的回滾日志才能看到老數(shù)據(jù)；
短連接風(fēng)暴
一般的短連接模式就是，連接數(shù)據(jù)庫(kù)之后執(zhí)行很少的sql后就斷開(kāi)，再次需要的時(shí)候再次連接
風(fēng)險(xiǎn)：一旦數(shù)據(jù)庫(kù)處理得慢一些，連接數(shù)就會(huì)暴漲超過(guò)了max_connections其他業(yè)務(wù)就不能連接數(shù)據(jù)庫(kù)
弊端：MySQL 建立連接的過(guò)程，成本是很高的。除了正常的網(wǎng)絡(luò)連接三次握手外，還需要做登錄權(quán)限判斷和獲得這個(gè)連接的數(shù)據(jù)讀寫(xiě)權(quán)限。
處理：show prossicelist 查看空閑的連接直接kill掉
? ? ? ? ? 為設(shè)置wait timeout
慢查詢(xún)性能問(wèn)題
在 MySQL 中，會(huì)引發(fā)性能問(wèn)題的慢查詢(xún)，大體有以下三種可能：
索引沒(méi)有設(shè)計(jì)好；創(chuàng)建索引，修改表結(jié)構(gòu)會(huì)觸犯表鎖 但是現(xiàn)在執(zhí)行 online DDL，同時(shí)可以采用在備份庫(kù)中執(zhí)行alter table 然后切換主備? 再在另外一個(gè)庫(kù)中執(zhí)行alter table
SQL 語(yǔ)句沒(méi)寫(xiě)好； sql優(yōu)化、行鎖、表鎖、flush
MySQL 選錯(cuò)了索引。 可以強(qiáng)制使用某個(gè)索引
如何判斷一個(gè)數(shù)據(jù)庫(kù)還能否正常使用？
select 1 判斷進(jìn)程是否正常
select * from t 判斷查詢(xún)是否正常
查看updateTime（類(lèi)型timestamp）字段 判斷修改是否正常
可以通過(guò)命令查看磁盤(pán)io時(shí)間，如果頻繁出現(xiàn)超時(shí)就是有性能問(wèn)題
庫(kù)里的表越多連接是否就越慢？
否
連接的過(guò)程中服務(wù)端就三個(gè)功能：TCP握手，用戶(hù)校驗(yàn)，獲取權(quán)限
這三個(gè)功能點(diǎn)和表的數(shù)據(jù)量無(wú)關(guān)所以不會(huì)影響速度
客戶(hù)端會(huì)有補(bǔ)全表名，補(bǔ)全庫(kù)名的功能，這個(gè)功能需要需要構(gòu)建本地哈希表 這個(gè)和表的數(shù)量有關(guān)
sql優(yōu)化
1.查詢(xún)一個(gè)大表沒(méi)有索引
建立索引
2.關(guān)聯(lián)查詢(xún)的是一個(gè)冷數(shù)據(jù)大表沒(méi)有索引
t2表數(shù)據(jù)行超過(guò)200萬(wàn)行，b字段沒(méi)有索引
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
改為：
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);
大表索引占用空間較大，又是冷數(shù)據(jù)所以沒(méi)必要建立索引
可以建立臨時(shí)表，給臨時(shí)表建立索引
讓sql可以走BKA算法
3.保潔商家打查詢(xún)案例BAK
查詢(xún)封裝商家基本信息和商家其他信息
crm_custom? 商家基本信息表
custom_id
crm_custom_sign? 商家簽約表
custom_id
service_id 201
crm_custom_level? 商家等級(jí)表
custom_id
crm_custom_category_status 商家狀態(tài)表
custom_id
其他表
原始sql：
select * from crm_custom a left join crm_custom_sign b on a.custom_id = b.custom_id
left join crm_custom_level c on a.custom_id = c.custom_id left join crm_custom_category_status d on a.custom_id = d.custom_id
where b.service_id = 201 limit0,30;
一百多萬(wàn)數(shù)據(jù)得執(zhí)行八個(gè)小時(shí)
優(yōu)化后：
customIds =
select a.custom_id from crm_custom a left join crm_custom_sign b where a.custom_id = b.custom_id where b.service_id = 201 order by a.custom_id desc limit 0,30 (crm_custom_sign 的custom_id有索引)? 這一步走了索引覆蓋
customBeans=
select * from crm_custom from custom_id in (customIds);
這一步走了主鍵索引而且參數(shù)是有序的 BKA
for循環(huán)customBeans 根據(jù)customId去各個(gè)表中獲取想要列?
這一步走了普通索引
優(yōu)化后這個(gè)流程十分鐘
4.分庫(kù)分表后使用臨時(shí)表優(yōu)化查詢(xún)
t 表通f列做key分發(fā)到24個(gè)庫(kù)上
select * from t where a > M order by b limit 0,100;
由于a不是key所以會(huì)去24個(gè)庫(kù)分別執(zhí)行這個(gè)sql
正常思路：在24個(gè)庫(kù)執(zhí)行完sql拿到 24*100 = 2400條數(shù)據(jù) 然后通過(guò)程序按照b字段進(jìn)行排序，然后取出前100條
使用臨時(shí)表的方式：
在24個(gè)庫(kù)中執(zhí)行sql取出2400條數(shù)據(jù)
選擇一個(gè)庫(kù)創(chuàng)建臨時(shí)表 create temporaty t_temp
將查詢(xún)到的2400放入臨時(shí)表
在臨時(shí)表上執(zhí)行sql select * from t_temp where a>M order by b limit 0,100;
這種場(chǎng)景使用臨時(shí)表的好處：
分庫(kù)后如果數(shù)據(jù)量較大又有 join order by 這樣的復(fù)雜查詢(xún) 如果使用程序處理對(duì)內(nèi)存 CPU要求很高
如果使用臨時(shí)表能夠直接使用mysql來(lái)處理 mysql本身有很好的優(yōu)化方案
5.盡量使用普通索引不使用唯一索引
6.limit分頁(yè)參數(shù)過(guò)大為什么很慢？
原因：select * from t limit 300000,10; 需要掃描 300000+10行
? ? ? ? ? select * from t limit 0,10; 需要掃描10行
解決方法：
記錄上次查詢(xún)的最大id：a
select * from t where id > a order by id asc limit 0,10;
八、主備一致和讀寫(xiě)分離
A庫(kù)：主庫(kù)? B庫(kù)：備庫(kù)（m-s結(jié)構(gòu)）
核心是通過(guò)binlog來(lái)實(shí)現(xiàn)主備一致
1.在備庫(kù)B上通過(guò)change master命令，設(shè)置主庫(kù)A的IP、端口、用戶(hù)名、密碼以及從哪個(gè)位置開(kāi)始請(qǐng)求binlog日志，這個(gè)位置包含文件名和日志偏移量
2.在備庫(kù)上執(zhí)行startslave命令這時(shí)候會(huì)啟動(dòng)兩個(gè)線程，io_thread 和 sql_thread，其中io_thread負(fù)責(zé)與主庫(kù)建立連接
3.主庫(kù)校驗(yàn)完用戶(hù)名和密碼之后通過(guò)備份庫(kù)B傳過(guò)來(lái)的位置，從本讀取binlog發(fā)送給B
4.備份庫(kù)B拿到binlog后開(kāi)始寫(xiě)本地文件，叫"中轉(zhuǎn)日志"relay log
5.sql_threda讀取中轉(zhuǎn)日志，解析日志里的命令然后執(zhí)行
A和B庫(kù)互為主備關(guān)系（雙M結(jié)構(gòu)）
MySQL 在 binlog 中記錄了這個(gè)命令第一次執(zhí)行時(shí)所在實(shí)例的 server id
從節(jié)點(diǎn) A 更新的事務(wù)，binlog 里面記的都是 A 的 server id；
傳到節(jié)點(diǎn) B 執(zhí)行一次以后，節(jié)點(diǎn) B 生成的 binlog 的 server id 也是 A 的 server id；
再傳回給節(jié)點(diǎn) A，A 判斷到這個(gè) server id 與自己的相同，就不會(huì)再處理這個(gè)日志。所以，死循環(huán)在這里就斷掉了。
一般情況下將從庫(kù)設(shè)置為only read，但是主備同步使用的是super 用戶(hù)，只讀對(duì)super用戶(hù)無(wú)效
binlog記錄日志的三種形式
1.statement 記錄執(zhí)行的sql
2.row 根據(jù)主鍵記錄數(shù)據(jù)的變化
3.mixed是statement和row的綜合（根據(jù)情況選擇使用statement或者row）
三種方式的比較：
安全：
delete from t where a = 1 and b > "2020.02.02" limit 1;（a有索引，b也有索引）
如果采用statement 同樣一條sql在不一樣的庫(kù)中可能刪除了不一樣的行，因?yàn)樽吡瞬灰粯拥乃饕?/p>
如果采用row就不會(huì)出現(xiàn)這樣的問(wèn)題
如果采用mixed數(shù)據(jù)庫(kù)會(huì)根據(jù)安全情況主動(dòng)選擇row
性能：
statement：只需要記錄相關(guān)sql就好了
row：需要記錄所有涉及到的行 如果一條sql涉及十萬(wàn)行就得記錄十萬(wàn)行
主備延遲的原因
備庫(kù)的同步在一段時(shí)間內(nèi)完全被堵住
1.主庫(kù)起了一個(gè)大事務(wù)（包括大表的DDL、or一個(gè)事務(wù)操作很多行）
2.備庫(kù)起了一個(gè)長(zhǎng)事務(wù)，長(zhǎng)時(shí)間占用線程
未完全堵住延遲時(shí)間較長(zhǎng)
1.主庫(kù)是多線程，從庫(kù)是單線程，從庫(kù)跟不上更新的速度
2.還有就是一些主從復(fù)制的策略也會(huì)影響
在主從的基礎(chǔ)上可以實(shí)現(xiàn)讀寫(xiě)分離
如何解決讀寫(xiě)分離中從庫(kù)延遲的問(wèn)題？（數(shù)據(jù)同步的延遲）
1.強(qiáng)制走主庫(kù)
對(duì)應(yīng)一致要求特別高的數(shù)據(jù)讀寫(xiě)都走主庫(kù)
2.sleep
讀取從庫(kù)的數(shù)據(jù)前先sleep一下保證數(shù)據(jù)已經(jīng)同步完畢
3.判斷主備無(wú)延遲方案
第一種方法：每次從庫(kù)執(zhí)行查詢(xún)請(qǐng)求前，先判斷 seconds_behind_master 是否已經(jīng)等于 0。如果還不等于 0 ，那就必須等到這個(gè)參數(shù)變?yōu)?0 才能執(zhí)行查詢(xún)請(qǐng)求（不完全準(zhǔn) 單位是秒）
第二種方法，對(duì)比位點(diǎn)確保主備無(wú)延遲：保證binlog同步完也都執(zhí)行了
Master_Log_File 和 Read_Master_Log_Pos，表示的是讀到的主庫(kù)的最新位點(diǎn)；
Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是備庫(kù)執(zhí)行的最新位點(diǎn)。
如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 這兩組值完全相同，就表示接收到的日志已經(jīng)同步完成。
第三種方法，對(duì)比 GTID 集合確保主備無(wú)延遲：確認(rèn)binlog都同步完也都執(zhí)行了
Auto_Position=1 ，表示這對(duì)主備關(guān)系使用了 GTID 協(xié)議。
Retrieved_Gtid_Set，是備庫(kù)收到的所有日志的 GTID 集合；
Executed_Gtid_Set，是備庫(kù)所有已經(jīng)執(zhí)行完成的 GTID 集合。
如果這兩個(gè)集合相同，也表示備庫(kù)接收到的日志都已經(jīng)同步完成。
(第二種、第三種方法比一種準(zhǔn) 但是還不夠 因?yàn)橹鲙?kù)執(zhí)行完的日志可能還沒(méi)完全提交過(guò)來(lái))
4.配合semi-sync
簽收機(jī)制
事務(wù)提交的時(shí)候，主庫(kù)把 binlog 發(fā)給從庫(kù)；
從庫(kù)收到 binlog 以后，發(fā)回給主庫(kù)一個(gè) ack，表示收到了；
主庫(kù)收到這個(gè) ack 以后，才能給客戶(hù)端返回“事務(wù)完成”的確認(rèn)。
判斷主備無(wú)延遲方案? 配合semi-sync? 配合使用可以保證主從的一致
5.GTID方案
數(shù)據(jù)庫(kù)開(kāi)啟GTID模式
等待最新的事務(wù)同步完再執(zhí)行查詢(xún)
九、join語(yǔ)句的執(zhí)行過(guò)程
select * from t1 straight_join t2 on (t1.a=t2.a);? t2 的a字段不是主鍵但是有索引
sql執(zhí)行過(guò)程
1.從t1表中讀取一行數(shù)據(jù)R
2.從數(shù)據(jù)行R中取出字段a到t2表中去找
3.找到符合條件的行，和R組成一行作為結(jié)果集的一部分
4.重復(fù)執(zhí)行1到3知道t1表的結(jié)尾循環(huán)結(jié)束
分析：
t1表是驅(qū)動(dòng)表? t2是被驅(qū)動(dòng)表
1.對(duì)t1表做了全表掃描 全表行數(shù)h1
2.而對(duì)每一行R，根據(jù)R中的a去t2表搜索走了索引，假如a在t2表中不重復(fù)，那么t2表業(yè)掃描了h1行
3.所以整個(gè)流程掃描了2h1行數(shù)據(jù)
上邊這個(gè)sql中假如t1表有N行? t2表有M行
時(shí)間復(fù)雜度為? o(n) = N + N*2*log2M;
N對(duì)時(shí)間復(fù)雜度的影響是主要的 所有需要小表驅(qū)動(dòng)大表
select * from t1 straight_join t2 on (t1.a=t2.b); t2的b字段不是主鍵也沒(méi)有索引
t1表N行，t2表M行
sql執(zhí)行過(guò)程
最笨的思路：全局掃描t1表，逐行讀取R，取出R中的a值，全局掃描t2表逐條取出R2將R2中的b值和a值比較符合則拼裝結(jié)果集,這樣t2表需要全局掃描N次? 總共掃描的行為 N+N*M；
mysql真正的思路：
1.全局掃描t1表將t1表全部放入線程級(jí)的內(nèi)存join_buffer
2.全局掃描t2表，逐條和join_buffer中的數(shù)據(jù)做對(duì)比得出符合條件的數(shù)據(jù)
兩張表都只掃描一次 總共掃描的行數(shù)是：N+M
如果t1表太大join_buffer一次裝不下怎么辦？
答：那就分多次裝
每多裝一次t2表就需要多做一次全局掃描，用t2表中的所有數(shù)據(jù)和每個(gè)join_buffer做對(duì)比
總共掃描的行：N+k*M
可以表示為：N + λ*N*M
所以N對(duì)行數(shù)的影響還是最大還是應(yīng)該：小表驅(qū)動(dòng)大表
如果不強(qiáng)制性指定驅(qū)動(dòng)關(guān)系，優(yōu)化器會(huì)自動(dòng)設(shè)置小表驅(qū)動(dòng)大表
進(jìn)一步的優(yōu)化BKA?
十、臨時(shí)表的解釋
內(nèi)存表的概念：
1.數(shù)據(jù)都保存在內(nèi)存中，系統(tǒng)重啟時(shí)數(shù)據(jù)會(huì)被清空但是表結(jié)構(gòu)還在
2.內(nèi)存表只能使用memory存儲(chǔ)引擎
其他屬性和正常的數(shù)據(jù)表沒(méi)有區(qū)別
臨時(shí)表的概念：
1.臨時(shí)表是相對(duì)于事務(wù)而言的，一個(gè)臨時(shí)表只對(duì)一個(gè)事務(wù)可見(jiàn)，兩個(gè)事務(wù)中有臨時(shí)表的名稱(chēng)相同是互不影響的， session 結(jié)束的時(shí)候，會(huì)自動(dòng)刪除臨時(shí)表
2.臨時(shí)表數(shù)據(jù)是寫(xiě)在磁盤(pán)上的，可以使用各種存儲(chǔ)引擎
3.臨時(shí)表可以與普通表同名。
4.session A 內(nèi)有同名的臨時(shí)表和普通表的時(shí)候，show create 語(yǔ)句，以及增刪改查語(yǔ)句訪問(wèn)的是臨時(shí)表。
5.show tables 命令不顯示臨時(shí)表
十一、分表
1.分表的key選取
1.按時(shí)間字段分表，方便擴(kuò)張
2.按全局主鍵分表，方便查詢(xún)
3.結(jié)合可以預(yù)估范圍的字段具體分析，根據(jù)業(yè)務(wù)有的也可按地域?qū)傩詣澐?/p>
分區(qū)分表：
CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);
我在表 t 中初始化插入了兩行記錄，按照定義的分區(qū)規(guī)則，這兩行記錄分別落在 p_2018 和 p_2019 這兩個(gè)分區(qū)上。
可以看到，這個(gè)表包含了一個(gè).frm 文件和 4 個(gè).ibd 文件，每個(gè)分區(qū)對(duì)應(yīng)一個(gè).ibd 文件。也就是說(shuō)：
對(duì)于引擎層來(lái)說(shuō)，這是 4 個(gè)表；
對(duì)于 Server 層來(lái)說(shuō)，這是 1 個(gè)表
分表后鎖性能的分析：
間隙鎖會(huì)被阻斷
表鎖也只能作用于掃描到的分區(qū)（有多個(gè)分區(qū)相當(dāng)于有多個(gè)表）
分區(qū)的應(yīng)用場(chǎng)景
1.較大的歷史記錄表，可以按時(shí)間分區(qū)
2.分區(qū)之后業(yè)務(wù)代碼更簡(jiǎn)潔
分庫(kù)分表
訂單系統(tǒng)分庫(kù)分表拆分案例
整體大的步驟:
1.原來(lái)的庫(kù)保持不同，業(yè)務(wù)上雙寫(xiě)
2.引入es同步數(shù)據(jù)庫(kù)表數(shù)據(jù)
3.非key字段為條件的查詢(xún)?nèi)绾翁幚?/p>
? 1）實(shí)時(shí)性要求不高的走es查詢(xún)
? 2）數(shù)據(jù)一致性要求高的走原來(lái)的全量的庫(kù)
