1 數(shù)據(jù)庫(kù)
存放數(shù)據(jù)的倉(cāng)庫(kù)菠发。例如你的賬號(hào)信息强窖，訂單記錄等。
2 SQL
Structured Query Language肠槽，用于訪問(wèn)和處理關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)的計(jì)算機(jī)語(yǔ)言。
按照功能又可分為四大類奢啥；
-
DQL
查詢語(yǔ)言秸仙，基本語(yǔ)句 SELECT；
-
DML
操縱語(yǔ)言扫尺，主要有三種形式筋栋，INSERT、UPDATE 和 DELETE正驻；
-
DDL
定義語(yǔ)言，創(chuàng)建表抢腐、視圖姑曙、索引等，CREATE TABLE迈倍；
-
DCL
控制語(yǔ)言伤靠，用來(lái)授權(quán)或回收某種特權(quán)，基本形式有 GRANT啼染、 COMMIT 和 ROLLBACK宴合；
3 NoSQL
Not Only SQL，泛指非關(guān)系型的數(shù)據(jù)庫(kù)迹鹅，通常以鍵值對(duì)或者文檔形式存儲(chǔ)卦洽。例如 Redis、MongoDB斜棚。
關(guān)系型數(shù)據(jù)庫(kù)（MySQL）能通過(guò)外鍵建立表之間的聯(lián)系阀蒂，且相比 NoSQL 而言该窗，還具備 ACID 特性。
但 NoSQL 操作無(wú)須 SQL 解析蚤霞，讀寫性能較高酗失，相比關(guān)系型數(shù)據(jù)庫(kù)來(lái)說(shuō)，不用預(yù)設(shè)存儲(chǔ)結(jié)構(gòu)昧绣，且天然支持分布式存儲(chǔ)规肴。
4 范式
數(shù)據(jù)庫(kù)滿足一定要求的條件稱為數(shù)據(jù)庫(kù)范式。又能根據(jù)程度的不同夜畴，簡(jiǎn)稱為第 N 范式奏纪。
-
第一范式 1NF
所有屬性不可再分，例如屬性 product 就不能分為 title 和 price斩启，可以單獨(dú)設(shè)置兩個(gè)屬性 productTitle序调、productPrice；
-
第二范式 2NF
每張表都有一個(gè)屬性作為唯一標(biāo)識(shí)兔簇，其他屬性完全依賴該標(biāo)識(shí)发绢，例如自增主鍵ID；
-
第三范式 3NF
所有的非主屬性不依賴于其他的非主屬性垄琐。例如訂單表中可以關(guān)聯(lián)商品ID边酒，但不應(yīng)該關(guān)聯(lián)商品非主屬性 title 和 price 等；
為了提高查詢效率狸窘，通常會(huì)添加冗余字段墩朦，這也就違背了 3NF，也稱之為反三范式翻擒。
5 MySQL
MySQL 是一個(gè) Oracle 旗下的關(guān)系型數(shù)據(jù)庫(kù)氓涣，使用 SQL 語(yǔ)言進(jìn)行增刪改查操作。
開源免費(fèi)陋气，性能也比較好劳吠，和 PHP、Java 等 Web 開發(fā)語(yǔ)言完美配合巩趁，在中小型企業(yè)應(yīng)用非常廣泛痒玩。
后續(xù)內(nèi)容都是基于 MySQL 數(shù)據(jù)庫(kù)的前提下。
6 存儲(chǔ)引擎
常見的有 MyISAM 和 InnoDB 引擎议慰；
| 引擎 | 默認(rèn)版本 | 外鍵 | 鎖粒度 | count(*) | 事務(wù) |
|---|---|---|---|---|---|
| MyISAM | < 5.5 | 不支持 | 表鎖 | 變量存儲(chǔ) | 不支持 |
| InnoDB | >= 5.5 | 支持 | 行鎖 | 全表掃描 | 支持 |
7 事務(wù)
一條或多條 SQL 組成一個(gè)事務(wù)（transaction）蠢古，具備 ACID 四個(gè)特性；
-
Atomicity 原子性
一個(gè)事務(wù)內(nèi)的所有操作别凹，要么全部完成草讶，要么全部失敗番川；
-
Consistency 一致性
事務(wù)開始前后結(jié)束后不會(huì)破壞數(shù)據(jù)庫(kù)的完整性到涂，也就是說(shuō)寫入或修改的結(jié)構(gòu)需要符合預(yù)設(shè)的規(guī)則脊框；
-
Isolation 隔離性
防止事務(wù)交叉執(zhí)行時(shí)導(dǎo)致數(shù)據(jù)的不一致。根據(jù)隔離程度分為 read uncommitted践啄、read committed浇雹、repeatable read 和 serializable；
-
Durability 持久性
事務(wù)結(jié)束后屿讽，對(duì)數(shù)據(jù)的修改是永久的昭灵；
事務(wù)交叉執(zhí)行可能會(huì)造成“臟讀”、“幻讀” 和 “不可重復(fù)讀”伐谈；
-
臟讀
一個(gè)事務(wù)讀取到另外一個(gè)事務(wù)還未提交的數(shù)據(jù)烂完；
-
不可重復(fù)讀
一個(gè)事務(wù)內(nèi)，多次讀取同一數(shù)據(jù)返回結(jié)果不同诵棵；由于在此期間在數(shù)據(jù)被其他事務(wù)修改并已提交抠蚣；
-
幻讀
一個(gè)事務(wù)內(nèi)，多次讀取履澳，返回不存在的記錄嘶窄；由于在此期間有其他事務(wù)寫入數(shù)據(jù)；
| read uncommitted | read committed | repeatable read | serializable | |
|---|---|---|---|---|
| 臟讀 | √ | × | × | × |
| 不可重復(fù)讀 | √ | √ | × | × |
| 幻讀 | √ | √ | √ | × |
8 索引
數(shù)據(jù)庫(kù)的“目錄”距贷，在數(shù)據(jù)量較大的情況下柄冲，可以極大地提高查詢效率。
常見的索引數(shù)據(jù)結(jié)構(gòu)有 B+ 樹忠蝗、Hash现横。以最常用的 B+ 樹為例；
按照 B+ 樹存儲(chǔ)方式可以把索引分為兩大類阁最；
-
聚簇索引戒祠；
葉子節(jié)點(diǎn)存放了一整行的信息；
-
非聚簇索引闽撤；
葉子節(jié)點(diǎn)存放的是對(duì)應(yīng)那行數(shù)據(jù)的主鍵得哆，和該索引的值；
為什么是 B+ 樹哟旗？
- 磁盤代價(jià)低；
- 查詢更加穩(wěn)定栋操；
- 便于遍歷闸餐；
- 支持范圍查詢；
一張結(jié)構(gòu)為 id矾芙，groupId舍沙，name 的 t_user 表，id 為主鍵（聚簇索引）剔宪，groupId 為普通索引（非聚簇索引）拂铡。
select name from t_user where groupId = 123;
先在葉子節(jié)點(diǎn)上得到對(duì)應(yīng)的主鍵 id壹无，然后再根據(jù)主鍵 id 得到 name 的值，這種行為稱之為回表感帅。
select groupId from t_user where groupId = 123;
直接在葉子節(jié)點(diǎn)上就能得到 groupId 的值斗锭，不用回表操作，這種索引也被稱為覆蓋索引失球。
按照功能類型又可以把索引分為三大類岖是；
-
普通索引；
最基本的索引類型实苞，沒有限制條件豺撑；
-
唯一索引；
保證索引字段的值唯一黔牵，允許有 NULL聪轿；主鍵是一種特殊的唯一索引，不允許有 NULL猾浦；
-
聯(lián)合索引陆错；
多個(gè)字段組成一個(gè)索引，具有“最左前綴”的原則跃巡；
什么是最左前綴危号？
a、b素邪、c 三個(gè)字段組成聯(lián)合索引外莲，那么生效的列為 a、ab兔朦、abc偷线、ac。（等值判斷時(shí)順序可交換沽甥，范圍查詢時(shí)會(huì)停止匹配）
9 鎖
宏觀來(lái)看声邦，鎖分為兩種；行鎖可歸納為兩類摆舟；
-
共享鎖（S）
share亥曹，又稱為讀鎖，已有 S 鎖恨诱，可以加其他 S 鎖媳瞪，但不能加 X 鎖；
-
排他鎖（X）
exclusive照宝，又稱為寫鎖蛇受，X 與其他任何鎖互斥；
InnoDB 是通過(guò)給索引項(xiàng)加鎖實(shí)現(xiàn)的行鎖厕鹃，可分為三種類型兢仰；
-
record lock
行級(jí)鎖乍丈，鎖定對(duì)應(yīng)索引項(xiàng)骚勘；
-
gap lock
間隙鎖庶橱，鎖定索引項(xiàng)之間的間隙，左開右閉被饿；
-
next-key lock
前兩種的結(jié)合秸弛；
如果不通過(guò)索引項(xiàng)檢索數(shù)據(jù)铭若，會(huì)鎖住整個(gè)表。
InnoDB 加鎖方法：
- 對(duì)于 UPDATE递览、DELETE叼屠、INSERT 自動(dòng)加 X 鎖；
- 對(duì)于普通 SELECT 不會(huì)加任何鎖绞铃；
- SELECT ... LOCK IN SHARE MODE 顯示加 S 鎖镜雨；
- SELECT ... FOR UPDATE 顯示加 X 鎖；
查詢當(dāng)前數(shù)據(jù)庫(kù)鎖狀態(tài)儿捧；
select * from information_schema.innodb_locks;
對(duì)于不同類型的索引荚坞，加鎖的方式也不一樣；
-
普通索引
加 next-key lock菲盾；
-
唯一索引
加 record lock颓影；
由于普通索引葉子節(jié)點(diǎn)存儲(chǔ)了主鍵，所以加鎖的字段是：普通索引 + 主鍵索引懒鉴；
假設(shè)有如下數(shù)據(jù)表 t_ lock诡挂，其中 id 為主鍵，xid 為 普通索引临谱；
+-----+----+
| xid | id |
+-----+----+
| 1 | 10 |
| 3 | 20 |
| 5 | 30 |
| 8 | 40 |
| 11 | 50 |
+-----+----+
給 (8, 40) 這條記錄加 X 鎖璃俗；
select * from t_lock where xid = 8 for update;
那么根據(jù) next-key lock 的定義，鎖住區(qū)間為 (5, 30) 到 (8, 40)悉默，(8, 40) 到 (11, 50) 這兩個(gè)區(qū)間城豁；
便于理解我會(huì)合并為一個(gè)區(qū)間 (5, 30) 到 (11, 50)。
按照所以排序規(guī)則抄课，假設(shè)另插入 (xid, id) 記錄唱星，那么總是滿足以下條件；
- xid < 5跟磨；id 無(wú)限制魏颓；（正常）
- xid = 5；id < 30吱晒；（正常）
- xid = 5；id > 30沦童；（阻塞）
- xid > 5 && xid < 11仑濒；id 無(wú)限制叹话；（阻塞）
- xid = 11；id < 50墩瞳；（阻塞）
- xid = 11驼壶；id > 50；（正常）
- xid > 11喉酌；id 無(wú)限制热凹；（正常）
簡(jiǎn)單圖示，當(dāng)插入的數(shù)據(jù)落在這個(gè)區(qū)間則會(huì)阻塞泪电，反之亦然般妙；
10 RR 幻讀
上面事務(wù)章節(jié)描述 RR 會(huì)導(dǎo)致幻讀，MySQL 在 RR 下通過(guò)如下兩點(diǎn)規(guī)避掉了相速；
-
MVCC
Multi-Version Concurrency Control碟渺，多版本并發(fā)控制。在普通 SELECT （快照度）時(shí)引入版本突诬，同一個(gè)事務(wù)中只能讀取不大于當(dāng)前版本的數(shù)據(jù)快照苫拍；
-
next-key lock
需要加 X 鎖的操作（當(dāng)前讀），加 next-key lock 可以有效避免產(chǎn)生幻讀旺隙；
11 SQL 執(zhí)行順序
根據(jù)創(chuàng)建時(shí)間升序绒极，查找支付成功超過(guò) 3 單的用戶，需要去重蔬捷；
select distinct t1.nickname
from t1 inner join t2
on t1.uid = t2.uid
where t2.pay_time > 0
group by t1.uid, t1.nickname
having count(*) > 3
order by t2.create_time
limit 10
- from
- on
- join
- where
- group
- having
- order
- select
- distinct
- limit
12 binlog
binlog 是 MySQL 最重要的日志垄提，記錄了所有的 DDL 和 DML 語(yǔ)句，主要目的是抠刺；
-
主從復(fù)制塔淤；
在 Master 開啟 binlog，并傳遞到 Slave 節(jié)點(diǎn)來(lái)達(dá)到 Master-Slave 數(shù)據(jù)一致性速妖；
-
數(shù)據(jù)恢復(fù)高蜂；
通過(guò) mysqlbinlog 恢復(fù)數(shù)據(jù)；
檢查 binlog 是否開啟罕容；
show variables like 'log_bin';
編輯 mysql 配置文件/etc/mysql/mysql.conf.d/mysqld.cnf备恤，開啟 binlog 功能；
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin
常用幾個(gè)命令锦秒；
- show master status;
- show binary logs;
- mysqlbinlog -v --start-position 2755 --stop-position 3076 mysql-bin.000003露泊；
例如誤刪除了某條記錄；
- 通過(guò) mysqlbinlog 定位到誤操作的 position旅择；
- 通過(guò) mysqlbinlog 定位到誤刪之前最早入庫(kù)的 position惭笑；
- 截取中間 binlog 日志， echo > db.sql 輸出到可執(zhí)行 SQL 文件中；
- 執(zhí)行恢復(fù)數(shù)據(jù)即可沉噩；
可直接流式執(zhí)行：mysqlbinlog -v --start-position 2432 --stop-position 2533 mysql-bin.000003 | mysql -uroot -p
mysqldump 是用來(lái)備份數(shù)據(jù)庫(kù)的捺宗，例如備份 db_test 數(shù)據(jù)庫(kù)；
mysqldump -h127.0.0.1 -uroot -p123456 db_test > db.sql
13 性能優(yōu)化
-
索引川蒙；
給經(jīng)常用作查詢條件蚜厉，且區(qū)分度較高的字段建立索引；
-
分頁(yè)查詢畜眨；
where id >
{size}昼牛，提高大表分頁(yè)效率；
-
批量操作康聂；
批量插入用 insert into xxx values (xxx...), (xxx...)贰健，批量更新用 case when id；
-
not null早抠；
null 會(huì)額外占用空間霎烙，且 count(xxx) 不會(huì)參與統(tǒng)計(jì)，若是索引列 is not null 也會(huì)失效蕊连；
