一條語句的執(zhí)行

img

(以上圖片來自極客時間 歡迎購買對應(yīng)專欄)

索引

目的

• 減少掃描的數(shù)據(jù)量
• 把隨機(jī)IO變成順序IO
• 避免分組、排序等操作時，生成臨時表

B+樹

• 多路絕對平衡查找樹，深度低
• 磁盤讀寫能力更強(qiáng)(相鄰讀取 IO次數(shù)減少)
• 遍歷更加方便

表現(xiàn)形式

MyISAM: 葉子節(jié)點(diǎn)上放的是數(shù)據(jù)地址

InnoDB: 聚簇索引，放的是全部數(shù)據(jù)，聚簇索引就是表 -> 普通索引需要回查 -> 覆蓋索引

索引原則

• 離散性：
• 最左匹配原則
• 聯(lián)合索引
  • 經(jīng)常使用的列優(yōu)先(最左匹配)
  • 選擇性高的列優(yōu)先(離散性)
  • 寬度小的列優(yōu)先(最少空間原則)

索引失效的常見誤區(qū)

• 匹配列前綴可用到索引 like 9999%， like %9999%、 like %9999用不到索引；
• Where 條件中 not in 和 <>操作無法使用索引
• 匹配范圍值， order by 也可用到索引
• 聯(lián)合索引中如果不是按照索引最左列開始查找， 無法使用索引
• 聯(lián)合索引中如果查詢中有某個列的范圍查詢， 則其右邊的所有列都無法使用索引

外鍵

為了保證數(shù)據(jù)完整性而設(shè)計(jì)的

• 兩個表都必須是InnoDb表

• 外鍵列必須建立了索引

• 外鍵關(guān)系的列，必須數(shù)據(jù)類型相似或一致，比如 tinyint 和 int可以，但是 char 和int不行

• 使用外鍵以后，如果子表試圖創(chuàng)建一個在父表中不存在的外鍵值，InnoDB會拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外鍵值，最終動作取決于外鍵約束定義中的ON UPDATE和ON DELETE選項(xiàng)；

• 外鍵約束使用最多的兩種情況：

  1）父表更新時子表也更新，父表刪除時如果子表有匹配的項(xiàng)，刪除失?。?/p>

  2）父表更新時子表也更新，父表刪除時子表匹配的項(xiàng)也刪除。

存儲引擎

Mysql 采用的是插拔式的插件方式，是指定在表之上的，即一個庫中的每一個表都可以指定專用的存儲引擎。

不管表采用什么樣的存儲引擎，都會生成一個frm(表結(jié)構(gòu)定義描述文件)

InnoDB

InnoDB采用MVCC來支持高并發(fā)，并且實(shí)現(xiàn)了四個標(biāo)準(zhǔn)的隔離級別，其默認(rèn)級別是REPEATBALE READ(可重復(fù)讀), 并通過間隙鎖(next-key locking)策略防止幻讀的出現(xiàn)。

間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會對索引的間隙進(jìn)行鎖定，防止幻影行的插入。

InnoDB標(biāo)的基于聚簇索引建立的，其索引結(jié)構(gòu)和Mysql的其他存儲引擎有很大的不同，聚簇索引對主鍵查詢有很高的性能，不過它的二級索引必須包含主鍵列，因此主鍵應(yīng)該盡可能的小。

InnoDB內(nèi)部做了許多優(yōu)化，包括從磁盤讀取數(shù)據(jù)時采用的可預(yù)測性預(yù)讀，能夠自動在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引以及能夠加速插入操作的插入緩沖區(qū)

InnoDB的數(shù)據(jù)存儲在表空間中，表空間是由InnoDB管理的一個黑盒子，由一系列的數(shù)據(jù)文件組成。

優(yōu)勢：

• 支持事務(wù)安裝：InnoDB 最重要的一點(diǎn)就是支持事務(wù)，可以說這是 InnoDB 成為 MySQL 中最流行的存儲引擎的一個非常重要的原因。InnoDB 還實(shí)現(xiàn)了 SQL92 標(biāo)準(zhǔn)所定義的 4 個隔離級別（READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ 和 SERIALIZABLE）。
• 災(zāi)難恢復(fù)性好：InnoDB 通過 commit、rollback、crash-recovery 來保障數(shù)據(jù)的安全。具體來說，crash-recovery 就是指如果服務(wù)器因?yàn)橛布蜍浖膯栴}而崩潰，不管當(dāng)時數(shù)據(jù)是怎樣的狀態(tài)，在重啟 MySQL 后，InnoDB 都會自動恢復(fù)到發(fā)生崩潰之前的狀態(tài)，并回到用戶離開的地方。使用行級鎖：
  • InnoDB 改變了 MyISAM 的鎖機(jī)制，實(shí)現(xiàn)了行鎖。雖然 InnoDB 的行鎖機(jī)制是通過索引來完成的，但畢竟在數(shù)據(jù)庫中 99%的 SQL 語句都要使用索引來檢索數(shù)據(jù)。行鎖定機(jī)制也為 InnoDB 在承受高并發(fā)壓力的環(huán)境下增強(qiáng)了不小的競爭力。
  • 在 SQL 查詢中可以自由地將 InnoDB 類型的表與其他類型的表混合起來，甚至在同一個查詢中也可以混合。
• 實(shí)現(xiàn)了緩沖處理：InnoDB 提供了專門的緩存池，實(shí)現(xiàn)了緩沖管理，不僅能緩沖索引也能緩沖數(shù)據(jù)，常用的數(shù)據(jù)可以直接從內(nèi)存中處理，比從磁盤獲取數(shù)據(jù)處理速度要快。相比之下，MyISAM 只是緩存了索引。
  • InnoDB 的表和索引在一個邏輯表空間中，表空間可以包含數(shù)個文件（或原始磁盤分區(qū)）。這與 MyISAM 表不同，比如在 MyISAM 表中每個表被保存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為 2GB 的操作系統(tǒng)上。
• 支持外鍵：InnoDB 支持外鍵約束，檢查外鍵、插入、更新和刪除，以確保數(shù)據(jù)的完整性。在存儲表中數(shù)據(jù)時每張表的存儲都按主鍵順序存放，如果沒有顯式地在定義表時指定主鍵，InnoDB 會為每一行生成一個 6 字節(jié)的 ROWID ，并以此作為主鍵。
  • InnoDB 實(shí)現(xiàn)外鍵引用這一重要特性，使在數(shù)據(jù)庫端控制部分?jǐn)?shù)據(jù)的完整性成為可能。雖然很多數(shù)據(jù)庫系統(tǒng)調(diào)優(yōu)專家都建議不要這樣做，但是對于不少用戶來說，大部分情況下，在數(shù)據(jù)庫端加外鍵控制仍然是成本最低的選擇。

四大特性

• 插入緩沖(insert buffer)

• 二次寫(double write)

• 自適應(yīng)哈希索引(ahi)：Innodb存儲引擎會監(jiān)控對表上二級索引的查找，如果發(fā)現(xiàn)某二級索引被頻繁訪問，二級索引成為熱數(shù)據(jù)，建立哈希索引可以帶來速度的提升

  經(jīng)常訪問的二級索引數(shù)據(jù)會自動被生成到hash索引里面去(最近連續(xù)被訪問三次的數(shù)據(jù))，自適應(yīng)哈希索引通過緩沖池的B+樹構(gòu)造而來，因此建立的速度很快。
  哈希（hash）是一種非?？斓牡戎挡檎曳椒ǎ谝话闱闆r下這種查找的時間復(fù)雜度為O(1),即一般僅需要一次查找就能定位數(shù)據(jù)。而B+樹的查找次數(shù)，取決于B+樹的高度，在生產(chǎn)環(huán)境中，B+樹的高度一般3-4層，故需要3-4次的查詢。

• 預(yù)讀(read ahead):預(yù)讀機(jī)制就是發(fā)起一個i/o請求，異步地在緩沖池中預(yù)先回遷若干頁面，預(yù)計(jì)將會用到回遷的頁面，這些請求在一個范圍內(nèi)引入所有頁面。InnoDB以64個page為一個extent

MyISAM

MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(shù)(GIS)等，但MyISAM不支持事務(wù)和行級鎖，而且有一個毫無疑問的缺陷是崩潰后無法安全恢復(fù)。

對于只讀的數(shù)據(jù)，或者表比較小，可以忍受修復(fù)操作，則依然可以繼續(xù)使用MyISAM

MyISAM會將表存儲在兩個文件中：數(shù)據(jù)文件和索引文件，分別以.MYD和.MYI為擴(kuò)展名。

特性：

• 加鎖與并發(fā)
  • MyISAM對整張表加鎖，而不是針對行。讀取時會對所有用到的表加共享鎖，寫入則加排他鎖。但是有讀取的時候，可以往表中插入新的記錄(并發(fā)插入)

區(qū)別

優(yōu)化

EXPLAIN語句

select_type 查詢類型

查詢的類型， 主要是用于區(qū)分普通查詢、 聯(lián)合查詢、 子查詢等

• SIMPLE： 簡單的select查詢， 查詢中不包含子查詢或者union
• PRIMARY： 查詢中包含子部分， 最外層查詢則被標(biāo)記為primary
• SUBQUERY/MATERIALIZED： SUBQUERY表示在select 或 where列表中包含了子查詢
  MATERIALIZED表示where 后面in條件的子查詢
• UNION： 若第二個select出現(xiàn)在union之后， 則被標(biāo)記為union；
• UNION RESULT： 從union表獲取結(jié)果的select

table

涉及到的表名

type 訪問類型

結(jié)果值從好到壞依次是：
system > const > eq_ref > ref > range > index > ALL

system： 表只有一行記錄（等于系統(tǒng)表） ， const類型的特例， 基本不會出現(xiàn)， 可以忽略不計(jì)
const： 表示通過索引一次就找到了， const用于比較primary key 或者 unique索引
eq_ref： 唯一索引掃描， 對于每個索引鍵， 表中只有一條記錄與之匹配。 常見于主鍵 或 唯一索引掃描
ref： 非唯一性索引掃描， 返回匹配某個單獨(dú)值的所有行， 本質(zhì)是也是一種索引訪問
range： 只檢索給定范圍的行， 使用一個索引來選擇行
index： Full Index Scan， 索引全表掃描， 把索引從頭到尾掃一遍
ALL： Full Table Scan， 遍歷全表以找到匹配的行

possible_keys

可能用到的索引

key

實(shí)際用到的索引，為NULL未使用索引

rows

根據(jù)表統(tǒng)計(jì)信息或者索引選用情況， 大致估算出找到所需的記錄所需要讀取的行數(shù)

filtered

它指返回結(jié)果的行占需要讀到的行(rows列的值)的百分比
表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比， filtered的值越大越好

Extra 額外信息

• using filesort: 使用外部文件內(nèi)容進(jìn)行排序
• using temporary: 查詢中使用了臨時表，常見于order by 或 group by
• using index: 使用了覆蓋索引
• using where: 使用了where過濾條件
• select tables optimized away: 基于索引優(yōu)化MIN/MAX操作或者M(jìn)yISAM存儲引擎優(yōu)化COUNT(*)操作， 不必等到執(zhí)行階段在進(jìn)行計(jì)算， 查詢執(zhí)行計(jì)劃生成的階段即可完成優(yōu)化

事務(wù)

事務(wù)是數(shù)據(jù)庫操作的最小工作單元，是作為單個邏輯工作單元執(zhí)行的一系列操作；
事務(wù)是一組不可再分割的操作集合（工作邏輯單元）

ACID

• 原子性(Atomicity)：最小工作單元
• 一致性(Consistency)：事務(wù)的執(zhí)行不能破壞數(shù)據(jù)庫數(shù)據(jù)的完整性和一致性，一個事務(wù)在執(zhí)行之前和執(zhí)行之后，數(shù)據(jù)庫都必須處于一致性狀態(tài)
• 隔離性(Isolation): 并發(fā)的事務(wù)相互隔離
• 持久性(Durability): 事務(wù)提交后，數(shù)據(jù)的變更將永久的保存到數(shù)據(jù)庫中

四種隔離級別

InnoDB的鎖

• 共享鎖（行鎖）：Shared Locks
• 排它鎖（行鎖）：Exclusive Locks
• 意向鎖共享鎖（表鎖）：Intention Shared Locks
• 意向鎖排它鎖（表鎖）：Intention Exclusive Locks
• 自增鎖：AUTO-INC Locks

行鎖的算法

• 記錄鎖 Record Locks
• 間隙鎖 Gap Locks
• 臨鍵鎖 Next-key Locks

共享鎖(Shared Locks)和排他鎖（Exclusive Locks)

共享鎖：

• 共享鎖又稱為讀鎖，簡稱S鎖， 顧名思義， 共享鎖就是多個事務(wù)對于同一數(shù)據(jù)可以共享一把鎖，
  都能訪問到數(shù)據(jù)， 但是只能讀不能修改;
• 加鎖釋鎖方式：
  select * from users WHERE id=1 LOCK IN SHARE MODE;
  commit/rollback

排他鎖：

• 又稱為寫鎖， 簡稱X鎖， 排他鎖不能與其他鎖并存， 如一個事務(wù)獲取了一個數(shù)據(jù)行的排他鎖， 其他事務(wù)就不能再獲取該行的鎖（共享鎖、 排他鎖） ， 只有該獲取了排他鎖的事務(wù)是可以對數(shù)據(jù)行進(jìn)行讀取和修改， （其他事務(wù)要讀取數(shù)據(jù)可來自于快照）
• 加鎖釋鎖方式：
  delete / update / insert 默認(rèn)加上X鎖
  SELECT * FROM table_name WHERE ... FOR UPDATE
  commit/rollback

數(shù)據(jù)庫的增刪改操作默認(rèn)都會加排他鎖，而查詢不會加任何鎖

InnoDB的行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的

即只有通過索引條件進(jìn)行數(shù)據(jù)檢索，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖（鎖住索引的所有記錄）

意向鎖（innodb特有）分意向共享鎖和意向排他鎖。

• 意向共享鎖：表示事務(wù)獲取行共享鎖時，必須先得獲取該表的意向共享鎖；
• 意向排他鎖：表示事務(wù)獲取行排他鎖時，必須先得獲取該表的意向排他鎖；

臨鍵鎖&間隙鎖&記錄鎖

• 臨鍵鎖(Next-key locks)： 鎖住記錄+區(qū)間(左開右閉) ；

  • 當(dāng)sql執(zhí)行按照索引進(jìn)行數(shù)據(jù)的檢索時,查詢條件為范圍查找（ between and、 <、 >等） 并有數(shù)據(jù)命中則此時SQL語句加上的鎖為Next-key locks， 鎖住索引的記錄+區(qū)間（ 左開右閉 )

  • 為InnoDB行鎖的默認(rèn)算法, 目的是防止幻讀

    原理： 因?yàn)镮nnodb底層的B+Tree結(jié)構(gòu)，底層是用了聚簇索引，底層葉子節(jié)點(diǎn)是有順序的（根據(jù)主鍵的順序）。臨鍵鎖鍵命中數(shù)據(jù)的區(qū)間+next區(qū)間鎖住的目的就是為了不然其他事務(wù)在這連個區(qū)間插入數(shù)據(jù)，這樣就解決了幻讀的問題，這也就是為什么 Innodb的RR隔離級別能解決幻讀問題。注意：Innodb的默認(rèn)隔離級別是RR級別的（可以解決幻讀問題）

• 間隙鎖(Gap locks) :鎖住數(shù)據(jù)不存在的區(qū)間（ 左開右開）

  • 當(dāng)sql執(zhí)行按照索引進(jìn)行數(shù)據(jù)的檢索時， 查詢條件的數(shù)據(jù)不存在， 這時SQL語句加上的鎖即為
    Gap locks， 鎖住索引不存在的區(qū)間（ 左開右開）

• 記錄鎖(Record locks):鎖住具體的索引項(xiàng)

  • 當(dāng)sql執(zhí)行按照唯一性（ Primary key、 Unique key） 索引進(jìn)行數(shù)據(jù)的檢索時， 查詢條件等值匹配且查詢的數(shù)據(jù)是存在， 這時SQL語句加上的鎖即為記錄鎖Record locks， 鎖住具體的索引項(xiàng)