MySQL近日學(xué)習(xí)總結(jié)

架構(gòu)

MySQL架構(gòu)可以分為兩部分：Server層和存儲引擎
Server層包括連接器，查詢引擎招盲，解析器，優(yōu)化器嘉冒，涵蓋了MySQL的大多數(shù)核心服務(wù)功能曹货，以及所有的內(nèi)置函數(shù)。
存儲引擎負(fù)責(zé)數(shù)據(jù)的存儲和提取讳推。架構(gòu)是插件式的顶籽，可以根據(jù)不同的需求選擇不同的存儲引擎。最常用的是MyISAM和InnoDB银觅。

索引(Index)

定義：是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)(排好序的快速查找數(shù)據(jù)結(jié)構(gòu))
優(yōu)點(diǎn)：提高了檢索效率礼饱，降低IO成本；降低排序成本究驴，降低CPU的消耗
缺點(diǎn)：降低表的更新速度(Insert update delete)镊绪；索引也是一張表，也會占用空間
分類：1. 單值索引 2. 唯一索引(索引列的值必須唯一洒忧，可為null) 3. 復(fù)合索引
基本語法：
創(chuàng)建：create index [索引名] ON [表名(字段,字段...)] / alter [表名]
add INDEX ON [表名(字段,字段...)]
刪除：drop index [indexName] on [表名]
查看：show index from [表名]
數(shù)據(jù)結(jié)構(gòu)：Hash索引 / B+Tree(InnoDB默認(rèn)索引)
什么時(shí)候需要建立索引：
1. 主鍵自動(dòng)建立唯一索引
2. 頻繁作為查詢條件的字段
3. 與其他表關(guān)聯(lián)的字段蝴韭，外鍵關(guān)系
4. 排序的字段
5. 統(tǒng)計(jì)或分組字段
什么時(shí)候不需要建立索引：
1. 表記錄太少
2. 經(jīng)常增刪的表
3. 數(shù)據(jù)重復(fù)且分布平均的表
什么時(shí)候索引失效
1. 違背最左前綴法則。最左前綴法則：查詢從索引的最左前列開始并且不跳過索引中的列
2. 在索引列上計(jì)算熙侍、函數(shù)榄鉴、類型轉(zhuǎn)換
3. 不能使用索引中范圍條件右邊的列
4. 使用!= <>
5. 使用is null履磨，is not null
6. like以通配符開頭(%abc)
7. 字符串不加單引號
8. 使用or
聚簇索引
定義：在同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行。因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方庆尘，所以一個(gè)表只能有一個(gè)聚簇索引剃诅。如果沒有定義主鍵，InnoDB會選擇一個(gè)唯一的非空索引來代替驶忌。如果沒有矛辕，會隱式地定義一個(gè)主鍵來作為聚簇索引
覆蓋索引，回表查詢
參考這篇文章: https://www.cnblogs.com/myseries/p/11265849.html

EXPLAIN指令

使用：explain + SQL語句
組成:
id：id相同從上到下位岔，id不同如筛，從id大到id小
select_type：simple primary subquery derived union union result
table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
type：查詢使用了什么類型

system：表中只有一條記錄
const：通過索引一次就找到了
eq_ref：唯一性索引掃描。常見于唯一索引掃描
ref：非唯一性索引掃描抒抬，返回某個(gè)單獨(dú)值的所有行
range：檢索給定范圍的行
index：Full Index Scan
all：Full Table Scan

possible_keys: 可能應(yīng)用在這張表上的索引
keys: 應(yīng)用在這張表上的索引
key_len: 索引中使用的字節(jié)數(shù)
ref：索引的哪一行被使用了
rows：大致估算出所需的記錄所需要讀取的行數(shù)
Extra：包含不合適在其他列但是和重要的信息

索引數(shù)據(jù)結(jié)構(gòu)
1. B+Tree
  - 和B-Tree有什么不同杨刨？
    1. B-Tree每個(gè)節(jié)點(diǎn)都存放數(shù)據(jù)，B+Tree只有葉子節(jié)點(diǎn)存放數(shù)據(jù)
    2. B-Tree的葉子節(jié)點(diǎn)沒有指向右邊的指針擦剑，B+Tree有妖胀，方便范圍查詢
2. Hash
  哈希索引能以 O(1) 時(shí)間進(jìn)行查找，但是失去了有序性惠勒。無法用于排序與分組赚抡、只支持精確查找，無法用于部分查找和范圍查找纠屋。select * from student where id > 3;

鎖

分類：

按鎖的粒度：
- 表鎖
  1. 偏向MyISAM存儲引擎涂臣，開銷小，加鎖快售担，無死鎖赁遗，鎖的粒度大，發(fā)生鎖沖突的幾率大族铆，并發(fā)度低
  2. SQL：手動(dòng)加鎖：lock table [表名] read/write
    查詢哪些表加了鎖：show open tables
- 行鎖：
  1. 偏向InnoDB存儲引擎岩四，開銷大，加鎖慢哥攘，會出現(xiàn)死鎖剖煌，發(fā)生鎖沖突的幾率小，并發(fā)度高
  2. 行鎖是通過索引項(xiàng)來實(shí)現(xiàn)的逝淹，只有通過索引來檢索項(xiàng)才會開啟行鎖耕姊。并且索引不能失效，索引失效的話栅葡，會從行鎖變成表鎖＼岳肌！妥畏！
  3. InnoDB實(shí)現(xiàn)了兩種類型的行鎖
    - 共享鎖(讀鎖)
      允許一個(gè)事務(wù)去讀一行數(shù)據(jù)邦邦。阻止其他事務(wù)活動(dòng)相同數(shù)量集的排他鎖
    - 排他鎖(寫鎖)
      允許一個(gè)事務(wù)去更新、讀取這一行數(shù)據(jù)醉蚁。阻止其他事務(wù)獲取這個(gè)數(shù)量集的任何鎖(包括共享鎖和排他鎖)
    - 幾種上鎖的情況：
      delete燃辖、update、insert會自動(dòng)給涉及到的數(shù)據(jù)加上排他鎖网棍，直接使用select是不上任何鎖的黔龟，但是select....for update會上排他鎖、select...lock in share mode會上共享鎖滥玷。所以加上排他鎖之后不是其他事務(wù)就不能查詢該列的數(shù)據(jù)了氏身，可以用select直接查詢。
  4. 間隙鎖：用范圍條件檢索數(shù)據(jù)的時(shí)候惑畴，并請求共享或者排他鎖時(shí)蛋欣，InnoDB會給復(fù)合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖，對于鍵值在條件范圍內(nèi)但并不存在的記錄如贷，叫間隙(Gap)陷虎。InnoDB也會對這個(gè)間隙加鎖，叫間隙鎖杠袱。
按對數(shù)據(jù)的操作類型

讀鎖(共享鎖)：針對同一份數(shù)據(jù)尚猿，多個(gè)讀操作可以同時(shí)進(jìn)行而不會互相影響
寫鎖(排他鎖)：當(dāng)前寫操作沒有完成前，他會阻斷其他寫鎖和讀鎖