一痊远、MySQL
1. MySQL索引
-
什么是索引
高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，相當(dāng)于書的目錄冒版，使用B+樹結(jié)構(gòu)逞姿，索引是存儲(chǔ)在磁盤文件中的（可能單獨(dú)的索引文件中，也可能和數(shù)據(jù)一起存儲(chǔ)在數(shù)據(jù)文件中）
-
索引的分類
- 單列索引
普通索引：MySQL中基本索引類型栋烤，沒有什么限制猎贴，允許在定義索引的列中插入重復(fù)值和空值她渴，純粹為了查詢數(shù) 據(jù)更快一點(diǎn)。
唯一索引：索引列中的值必須是唯一的沉唠，但是允許為空值。
主鍵索引：是一種特殊的唯一索引苛败，不允許有空值
- 組合索引
在表中的多個(gè)字段組合上創(chuàng)建的一個(gè)索引
組合索引的使用满葛，需要遵循最左前綴原則（最左匹配原則）。
-
索引的優(yōu)缺點(diǎn)
優(yōu)點(diǎn)：快速數(shù)據(jù)的查詢速度
缺點(diǎn)：空間換時(shí)間罢屈，索引也需要占空間
創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間嘀韧，并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會(huì)增加
2. MySQL存儲(chǔ)引擎
| 存儲(chǔ)引擎 | 優(yōu)點(diǎn) | 缺點(diǎn) |
|---|---|---|
| InnoDB | 5.5版本后MySQL默認(rèn)數(shù)據(jù)庫(kù)，支持事務(wù)缠捌，比MyISAM處理速度稍慢 | 非常復(fù)雜锄贷，性能較一些簡(jiǎn)單的引擎要差一點(diǎn)兒÷拢空間占用比較多谊却。 |
| MyISAM | 高速引擎哑芹，擁有極高的插入炎辨，查詢速度 | 不支持事務(wù)，不支持行鎖聪姿、崩潰后數(shù)據(jù)不容易修復(fù) |
| Memory | 內(nèi)存存儲(chǔ)引擎碴萧，擁有極高的插入，更新和查詢效率 | 占用和數(shù)據(jù)量成正比的內(nèi)存空間咳燕，只在內(nèi)存上保存數(shù)據(jù)勿决，意味著數(shù)據(jù)可能會(huì)丟失 |
最常用的是InnoDB和MyISAM，InnoDB和MyISAM存儲(chǔ)引擎區(qū)別
| 類別 | InnoDB | MyISAM |
|---|---|---|
| 存儲(chǔ)文件 | .frm 表定義文件 .idb 數(shù)據(jù)文件和索引文件 | .frm 表定義文件 .myd 數(shù)據(jù)文件 .myi 索引文件 |
| 鎖 | 表鎖招盲、行鎖 | 表鎖 |
| 事務(wù) | 支持 | 不支持 |
| 索引結(jié)構(gòu) | B+ Tree（聚簇索引，葉子節(jié)點(diǎn)存儲(chǔ)主鍵嘉冒，所以有回表操作） | B+ Tree（非聚簇索引曹货，葉子結(jié)點(diǎn)存儲(chǔ)地址的指針） |
3. 并發(fā)事務(wù)帶來的問題咆繁？
臟讀（Dirty read）: 當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù)并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫(kù)中顶籽，這時(shí)另外一個(gè)事務(wù)也訪問了這個(gè)數(shù)據(jù)玩般，然后使用了這個(gè)數(shù)據(jù)。因?yàn)檫@個(gè)數(shù)據(jù)是還沒有提交的數(shù)據(jù)礼饱，那么另外一個(gè)事務(wù)讀到的這個(gè)數(shù)據(jù)是“臟數(shù)據(jù)”坏为，依據(jù)“臟數(shù)據(jù)”所做的操作可能是不正確的。
丟失修改（Lost to modify）: 指在一個(gè)事務(wù)讀取一個(gè)數(shù)據(jù)時(shí)镊绪，另外一個(gè)事務(wù)也訪問了該數(shù)據(jù)匀伏，那么在第一個(gè)事務(wù)中修改了這個(gè)數(shù)據(jù)后，第二個(gè)事務(wù)也修改了這個(gè)數(shù)據(jù)蝴韭。這樣第一個(gè)事務(wù)內(nèi)的修改結(jié)果就被丟失够颠，因此稱為丟失修改。 例如：事務(wù)1讀取某表中的數(shù)據(jù)A=20榄鉴，事務(wù)2也讀取A=20履磨，事務(wù)1修改A=A-1，事務(wù)2也修改A=A-1庆尘，最終結(jié)果A=19剃诅，事務(wù)1的修改被丟失。
不可重復(fù)讀（Unrepeatableread）: 指在一個(gè)事務(wù)內(nèi)多次讀同一數(shù)據(jù)驶忌。在這個(gè)事務(wù)還沒有結(jié)束時(shí)矛辕，另一個(gè)事務(wù)也訪問該數(shù)據(jù)。那么位岔，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間如筛，由于第二個(gè)事務(wù)的修改導(dǎo)致第一個(gè)事務(wù)兩次讀取的數(shù)據(jù)可能不太一樣。這就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的情況抒抬，因此稱為不可重復(fù)讀杨刨。
幻讀（Phantom read）: 幻讀與不可重復(fù)讀類似。它發(fā)生在一個(gè)事務(wù)（T1）讀取了幾行數(shù)據(jù)擦剑，接著另一個(gè)并發(fā)事務(wù)（T2）插入了一些數(shù)據(jù)時(shí)妖胀。在隨后的查詢中，第一個(gè)事務(wù)（T1）就會(huì)發(fā)現(xiàn)多了一些原本不存在的記錄惠勒，就好像發(fā)生了幻覺一樣赚抡，所以稱為幻讀。
4. 事務(wù)的隔離級(jí)別
READ-UNCOMMITTED(讀取未提交)： 最低的隔離級(jí)別纠屋，允許讀取尚未提交的數(shù)據(jù)變更涂臣，可能會(huì)導(dǎo)致臟讀、幻讀或不可重復(fù)讀。
READ-COMMITTED(讀取已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)赁遗，可以阻止臟讀署辉，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。
REPEATABLE-READ(可重復(fù)讀)： 對(duì)同一字段的多次讀取結(jié)果都是一致的岩四，除非數(shù)據(jù)是被本身事務(wù)自己所修改哭尝，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生剖煌。
SERIALIZABLE(可串行化)： 最高的隔離級(jí)別材鹦，完全服從ACID的隔離級(jí)別。所有的事務(wù)依次逐個(gè)執(zhí)行耕姊，這樣事務(wù)之間就完全不可能產(chǎn)生干擾桶唐，也就是說，該級(jí)別可以防止臟讀箩做、不可重復(fù)讀以及幻讀莽红。
MySQL InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是 REPEATABLE-READ（可重讀）。
5. 事務(wù)的四大特性
事務(wù)是邏輯上的一組操作邦邦，要么都執(zhí)行安吁，要么都不執(zhí)行。
原子性（Atomicity）： 事務(wù)是最小的執(zhí)行單位燃辖，不允許分割鬼店。事務(wù)的原子性確保動(dòng)作要么全部完成，要么完全不起作用黔龟；
一致性（Consistency）： 執(zhí)行事務(wù)前后妇智，數(shù)據(jù)保持一致，多個(gè)事務(wù)對(duì)同一個(gè)數(shù)據(jù)讀取的結(jié)果是相同的氏身；
隔離性（Isolation）： 并發(fā)訪問數(shù)據(jù)庫(kù)時(shí)巍棱，一個(gè)用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫(kù)是獨(dú)立的蛋欣；
持久性（Durability）： 一個(gè)事務(wù)被提交之后航徙。它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫(kù)發(fā)生故障也不應(yīng)該對(duì)其有任何影響陷虎。
6. 大表優(yōu)化
限定數(shù)據(jù)的范圍
讀/寫分離（主庫(kù)負(fù)責(zé)寫到踏，從庫(kù)負(fù)責(zé)讀；）
垂直分區(qū)（數(shù)據(jù)表列的拆分尚猿，把一張列比較多的表拆分為多張表窝稿。）
水平分區(qū)（保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變，通過某種策略存儲(chǔ)數(shù)據(jù)分片凿掂。這樣每一片數(shù)據(jù)分散到不同的表或者庫(kù)中伴榔，）
7. 分庫(kù)分表之后,id 主鍵如何處理？
UUID：不適合作為主鍵，因?yàn)樘L(zhǎng)了潮梯，并且無序不可讀骗灶，查詢效率低惨恭。比較適合用于生成唯一的名字的標(biāo)示比如文件的名字秉馏。
數(shù)據(jù)庫(kù)自增 id : 兩臺(tái)數(shù)據(jù)庫(kù)分別設(shè)置不同步長(zhǎng)，生成不重復(fù)ID的策略來實(shí)現(xiàn)高可用脱羡。這種方式生成的 id 有序萝究，但是需要獨(dú)立部署數(shù)據(jù)庫(kù)實(shí)例，成本高锉罐，還會(huì)有性能瓶頸帆竹。
利用 redis 生成 id : 性能比較好，靈活方便脓规，不依賴于數(shù)據(jù)庫(kù)栽连。但是，引入了新的組件造成系統(tǒng)更加復(fù)雜侨舆，可用性降低秒紧，編碼更加復(fù)雜，增加了系統(tǒng)成本挨下。
Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake熔恢。
美團(tuán)的Leaf分布式ID生成系統(tǒng) ：Leaf 是美團(tuán)開源的分布式ID生成器，能保證全局唯一性臭笆、趨勢(shì)遞增叙淌、單調(diào)遞增、信息安全愁铺，里面也提到了幾種分布式方案的對(duì)比
8. 一條SQL語(yǔ)句在MySQL中如何執(zhí)行的
MySQL 主要分為 Server 層和引擎層鹰霍，Server 層主要包括連接器、查詢緩存茵乱、分析器茂洒、優(yōu)化器、執(zhí)行器似将，同時(shí)還有一個(gè)日志模塊（binlog）获黔，這個(gè)日志模塊所有執(zhí)行引擎都可以共用,redolog 只有 InnoDB 有。
引擎層是插件式的在验，目前主要包括玷氏，MyISAM,InnoDB,Memory 等。
SQL 等執(zhí)行過程分為兩類腋舌，一類對(duì)于查詢等過程如下：權(quán)限校驗(yàn)---》查詢緩存---》分析器---》優(yōu)化器---》權(quán)限校驗(yàn)---》執(zhí)行器---》引擎
對(duì)于更新等語(yǔ)句執(zhí)行流程如下：分析器----》權(quán)限校驗(yàn)----》執(zhí)行器---》引擎---redo log prepare---》binlog---》redo log commit
9. MySQL高性能優(yōu)化
-
數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范
所有表必須使用 Innodb 存儲(chǔ)引擎
數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一使用 UTF8盏触，避免轉(zhuǎn)化造成索引失效（emoji 表情的需要，字符集需要采用 utf8mb4 字符集。）
盡量控制單表數(shù)據(jù)量的大小,建議控制在 500 萬以內(nèi)赞辩。
謹(jǐn)慎使用 MySQL 分區(qū)表
禁止在數(shù)據(jù)庫(kù)中存儲(chǔ)圖片,文件等大的二進(jìn)制數(shù)據(jù)
禁止在線上做數(shù)據(jù)庫(kù)壓力測(cè)試
-
數(shù)據(jù)庫(kù)字段設(shè)計(jì)規(guī)范
優(yōu)先選擇符合存儲(chǔ)需要的最小的數(shù)據(jù)類型
避免使用 TEXT,BLOB 數(shù)據(jù)類型雌芽，最常見的 TEXT 類型可以存儲(chǔ) 64k 的數(shù)據(jù)
盡可能把所有列定義為 NOT NULL
同財(cái)務(wù)相關(guān)的金額類數(shù)據(jù)必須使用 decimal 類型
-
索引設(shè)計(jì)規(guī)范
限制每張表上的索引數(shù)量
禁止給表中的每一列都建立單獨(dú)的索引
每個(gè) Innodb 表必須有個(gè)主鍵
常見索引列建議（出現(xiàn)在 SELECT、多表 join 的關(guān)聯(lián)列）
-
科學(xué)選擇索引列的順序
區(qū)分度最高的放在聯(lián)合索引的最左側(cè)
盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)
使用最頻繁的列放到聯(lián)合索引的左側(cè)
-
數(shù)據(jù)庫(kù)開發(fā)規(guī)范
避免數(shù)據(jù)類型的隱式轉(zhuǎn)換
充分利用表上已經(jīng)存在的索引
禁止使用 SELECT * 必須使用 SELECT <字段列表> 查詢
避免使用子查詢辨嗽，可以把子查詢優(yōu)化為 join 操作
避免使用 JOIN 關(guān)聯(lián)太多的表
拆分復(fù)雜的大 SQL 為多個(gè)小 SQL
Explain優(yōu)化SQL語(yǔ)句
