索引相關(guān)

索引類型

主鍵索引：數(shù)據(jù)列不允許重復(fù)颖低，不允許為NULL拷窜。一個表只能有一個主鍵索引俭茧。InnoDB的主鍵索引為聚簇索引咆疗，而MyISAM的主鍵索引為非聚簇索引。
創(chuàng)建：ALTER TABLE table_name ADD PRIMARY KEY (column);

唯一索引：數(shù)據(jù)列不允許重復(fù)母债，允許為NULL午磁，一個表中允許創(chuàng)建多個唯一索引。唯一索引可以用作業(yè)務(wù)防重毡们。
創(chuàng)建：ALTER TABLE table_name ADD UNIQUE (column1, column2);

普通索引：基本的索引類型漓踢，沒有唯一性的限制，允許為NULL值漏隐。
創(chuàng)建：ALTER TABLE table_name ADD KEY (column1, column2)

全文索引：是目前搜索引擎使用的一種關(guān)鍵技術(shù)喧半，InnoDB不支持全文索引，MyISAM支持全文索引青责。
創(chuàng)建：ALTER TABLE table_name ADD FULLTEXT (column);

最左前綴原則

聯(lián)合索引從左邊的列到右邊的列依次匹配挺据，聯(lián)合索引的最左前綴原則如下：
對于聯(lián)合索引：index(co1, col2, col3)

1. where col1=1，where col1=1 and col2=2脖隶，where col1=1 and col2=2 and col3=3都會走這個索引扁耐。第一個語句使用 col1，第二個語句使用 col1,col2产阱，第三個使用 col1,col2,col3婉称；
2. where col2=2，where col2=2 and col3=3不會走索引;
3. where col3=3 and col2=2 and col1=1未經(jīng)查詢優(yōu)化的話不會走索引构蹬，但是經(jīng)過查詢優(yōu)化后會走索引...王暗；
4. where col1=1 and col3=3 僅使用 col1；
5. where col1=1 and col2>2 and col3=3 僅使用 col1,col2庄敛；
6. where col1=1 and col2 like 'xx%' 使用 col1,col2俗壹；
7. where col1=1 and col2 like '%xx'' 僅使用 col1。

索引算法（B-Tree 和 HASH）

B-Tree索引：

B-Tree索引是一棵多路查找平衡樹藻烤，InnoDB 和 MyISAM 存儲引擎都支持 B-Tree 索引绷雏。InnoDB 支持聚簇索引，InnoDB 默認(rèn)會為主鍵創(chuàng)建聚簇索引怖亭，聚簇索引的非葉子節(jié)點(diǎn)不保存數(shù)據(jù)涎显，只有葉子節(jié)點(diǎn)會保存數(shù)據(jù)，聚簇索引的葉子節(jié)點(diǎn)還會保存相鄰的后一個節(jié)點(diǎn)的指針兴猩。非聚簇索引葉子節(jié)點(diǎn)不保存數(shù)據(jù)期吓，只保存主鍵索引。

HASH索引：

Hash 索引檢索效率高峭跳，只需要一次定位膘婶，不像 B-Tree 索引需要從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)，最后才能訪問到頁節(jié)點(diǎn)這樣多次的IO訪問蛀醉。
Hash索引雖然檢索效率高悬襟，但是也有很多的不足之處：

1. Hash 索引僅僅能滿足”=”,”IN”和”<=>”查詢，不能使用范圍查詢拯刁。
   由于 Hash 索引比較的是進(jìn)行 Hash 運(yùn)算之后的 Hash 值脊岳，所以它只能用于等值的過濾，不能用于基于范圍的過濾垛玻，因?yàn)榻?jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系割捅，并不能保證和Hash運(yùn)算前完全一樣。

2. Hash 索引無法被用來避免數(shù)據(jù)的排序操作帚桩。
   由于 Hash 索引中存放的是經(jīng)過 Hash 計(jì)算之后的 Hash 值亿驾，而且 Hash 值的大小關(guān)系并不一定和 Hash 運(yùn)算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運(yùn)算账嚎；

3. Hash 索引不能利用部分索引鍵查詢莫瞬。
   對于組合索引，Hash 索引在計(jì)算 Hash 值的時(shí)候是組合索引鍵合并后再一起計(jì)算 Hash 值郭蕉，而不是單獨(dú)計(jì)算 Hash 值疼邀，所以通過組合索引的前面一個或幾個索引鍵進(jìn)行查詢的時(shí)候，Hash 索引也無法被利用召锈。

4. Hash 索引在任何時(shí)候都不能避免表掃描旁振。
   前面已經(jīng)知道，Hash 索引是將索引鍵通過 Hash 運(yùn)算之后涨岁，將 Hash運(yùn)算結(jié)果的 Hash 值和所對應(yīng)的行指針信息存放于一個 Hash 表中拐袜，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù)梢薪，也無法從 Hash 索引中直接完成查詢阻肿，還是要通過訪問表中的實(shí)際數(shù)據(jù)進(jìn)行相應(yīng)的比較，并得到相應(yīng)的結(jié)果沮尿。

5. Hash 索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高丛塌。
   對于選擇性比較低的索引鍵，如果創(chuàng)建 Hash 索引畜疾，那么將會存在大量記錄指針信息存于同一個 Hash 值相關(guān)聯(lián)赴邻。這樣要定位某一條記錄時(shí)就會非常麻煩，會浪費(fèi)多次表數(shù)據(jù)的訪問啡捶，而造成整體性能低下姥敛。

B-Tree vs. HASH索引

索引設(shè)計(jì)原則

1. 為經(jīng)常需要排序与帆、分組和聯(lián)合的字段建立索引
2. 為經(jīng)常作為 where 條件查詢的字段建立索引
3. 在選擇性好的列上建立索引，所謂選擇性好是指篩選出來的數(shù)據(jù)少墨榄，例如性別的選擇性就比較差玄糟，而姓名的選擇性就高
4. 聯(lián)合索引需要注意索引的順序，將選擇性高的放在前面
5. 索引雖然能加快查詢袄秩，但是索引也是有代價(jià)的阵翎，首先索引會占用存儲空間。其次索引的維護(hù)也是有消耗的之剧。因此郭卫，索引不是越多越好。

數(shù)據(jù)類型

數(shù)字類型

整數(shù): tinyint背稼、smallint贰军、mediumint、int蟹肘、bigint

浮點(diǎn)數(shù): float谓形、double、real疆前、decimal

FLOAT容易造成精度丟失

定點(diǎn)數(shù)DECIMAL

1. 高精度的數(shù)據(jù)類型，常用來存儲交易相關(guān)的數(shù)據(jù)
2. DECIMAL(M,N).M代表總精度胸完，N代表小數(shù)點(diǎn)右側(cè)的位數(shù)（標(biāo)度）1 < M < 254, 0 < N < 60;
3. 存儲空間變長

日期和時(shí)間

date书释、time、datetime赊窥、timestamp爆惧、year

• MySQL在5.6.4版本之后，TIMESTAMP和DATETIME支持到微秒锨能。
• TIMESTAMP會根據(jù)系統(tǒng)時(shí)區(qū)進(jìn)行轉(zhuǎn)換扯再，DATETIME則不會
• 存儲范圍的區(qū)別
  • TIMESTAMP存儲范圍：1970-01-01 00::00:01 to 2038-01-19 03:14:07
  • DATETIME的存儲范圍：1000-01-01 00:00:00 to 9999-12-31 23:59:59
• 一般使用TIMESTAMP國際化
• 如存時(shí)間戳使用數(shù)字類型BIGINT

字符串類型

字符串: char、varchar
文本: tinytext址遇、text熄阻、mediumtext、longtext

• TEXT在MySQL內(nèi)部大多存儲格式為溢出頁，效率不如CHAR
• Mysql默認(rèn)為utf-8钾军，那么在英文模式下1個字符=1個字節(jié)鳄袍，在中文模式下1個字符=3個字節(jié)。

二進(jìn)制(可用來存儲圖片吏恭、音樂等)

tinyblob拗小、blob、mediumblob砸泛、longblob

InnoDB和MyISAM的區(qū)別

• InnoDB支持事務(wù)，MyISAM不支持蛆封。
• MyISAM適合查詢以及插入為主的應(yīng)用唇礁，InnoDB適合頻繁修改以及涉及到安全性較高的應(yīng)用。
• InnoDB支持外鍵惨篱，MyISAM不支持盏筐。
• 從MySQL5.5.5以后，InnoDB是默認(rèn)引擎砸讳。
• InnoDB不支持FULLTEXT類型的索引琢融。
• InnoDB中不保存表的行數(shù)，如select count() from table時(shí)簿寂，InnoDB需要掃描一遍整個表來計(jì)算有多少行漾抬，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。主要的是常遂，當(dāng)count()語句包含where條件時(shí)MyISAM也需要掃描整個表纳令。
• 對于自增長的字段，InnoDB中必須包含只有該字段的索引克胳，但是在MyISAM表中可以和其他字段一起建立聯(lián)合索引平绩。
• 清空整個表時(shí)，InnoDB是一行一行的刪除漠另，效率非常慢捏雌。MyISAM則會重建表。
• InnoDB支持行鎖（某些情況下還是會鎖整個表笆搓，如update table set a = 1 where user like '%lee%'）

MVCC

MVCC的實(shí)現(xiàn)性湿，是通過保存數(shù)據(jù)在某個時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的，根據(jù)事務(wù)開始時(shí)間的不同满败，每個事務(wù)對于同一張表窘奏，同一時(shí)刻看到的數(shù)據(jù)可能是不同的。

InnoDB的 MVCC 葫录，是通過在每行記錄的后面保存兩個隱藏的列來實(shí)現(xiàn)的着裹。這兩個列，一個保存了行的創(chuàng)建時(shí)間，一個保存了行的過期時(shí)間（或刪除時(shí)間）骇扇， 當(dāng)然存儲的并不是實(shí)際的時(shí)間值摔竿，而是系統(tǒng)版本號。

可以認(rèn)為MVCC是行級鎖的一個變種少孝，但是它在很多情況下避免了加鎖操作继低，因此開銷更低。雖然實(shí)現(xiàn)機(jī)制有所不同稍走，但大都實(shí)現(xiàn)了非阻塞的讀操作袁翁，寫操作也只鎖定必要的行。

在MVCC協(xié)議下婿脸，每個讀操作會看到一個一致性的 snapshot粱胜，并且可以實(shí)現(xiàn)非阻塞的讀。MVCC 允許數(shù)據(jù)具有多個版本狐树，這個版本可以是時(shí)間戳或者是全局遞增的事務(wù) ID焙压，在同一個時(shí)間點(diǎn)，不同的事務(wù)看到的數(shù)據(jù)是不同的抑钟。

• select：滿足以下兩個條件innodb會返回該行數(shù)據(jù)：（1）該行的創(chuàng)建版本號小于等于當(dāng)前版本號涯曲，用于保證在select操作之前所有的操作已經(jīng)執(zhí)行落地。（2）該行的刪除版本號大于當(dāng)前版本或者為空在塔。刪除版本號大于當(dāng)前版本意味著有一個并發(fā)事務(wù)將該行刪除了幻件。
• insert：將新插入的行的創(chuàng)建版本號設(shè)置為當(dāng)前系統(tǒng)的版本號。
• delete：將要刪除的行的刪除版本號設(shè)置為當(dāng)前系統(tǒng)的版本號蛔溃。
• update：不執(zhí)行原地update傲武，而是轉(zhuǎn)換成insert + delete。將舊行的刪除版本號設(shè)置為當(dāng)前版本號城榛，并將新行insert同時(shí)設(shè)置創(chuàng)建版本號為當(dāng)前版本號揪利。

其中，寫操作（insert狠持、delete和update）執(zhí)行時(shí)疟位，需要將系統(tǒng)版本號遞增。
由于舊數(shù)據(jù)并不真正的刪除喘垂，所以必須對這些數(shù)據(jù)進(jìn)行清理甜刻，innodb會開啟一個后臺線程執(zhí)行清理工作，具體的規(guī)則是將刪除版本號小于當(dāng)前系統(tǒng)版本的行刪除正勒，這個過程叫做purge得院。
通過MVCC很好的實(shí)現(xiàn)了事務(wù)的隔離性，可以達(dá)到repeated read級別章贞，要實(shí)現(xiàn)serializable還必須加鎖祥绞。

MVCC只在READ COMMITED 和 REPEATABLE READ 兩個隔離級別下工作。READ UNCOMMITTED總是讀取最新的數(shù)據(jù)行，而不是符合當(dāng)前事務(wù)版本的數(shù)據(jù)行蜕径。而SERIALIZABLE 則會對所有讀取的行都加鎖两踏。

InnoDB間隙鎖

Record lock

單條索引記錄上加鎖，InnoDB 的行鎖是通過索引實(shí)現(xiàn)的兜喻，如沒有索引梦染，則鎖住的不是記錄行而是整個表。

Gap lock

間隙鎖朴皆，鎖定一個范圍帕识，但不包括記錄本身。GAP鎖的目的遂铡，是為了防止同一事務(wù)的兩次當(dāng)前讀肮疗，出現(xiàn)幻讀的情況。

Next-key lock

Next-key lock 就是 Record lock + Gap lock 的組合忧便，它鎖定的是一個范圍族吻，并且鎖定記錄本身帽借。對于行的查詢珠增，都是采用該方法，主要目的是解決幻讀的問題砍艾。

間隙鎖（Next-key lock）的條件

mysql的事務(wù)隔離級別是可重復(fù)讀（RR蒂教，Repeatable Read），并且 innodb_locks_unsafe_for_binlog 參數(shù)設(shè)置為0

事務(wù)ACID

1. 原子性（Atomicity）：不可分割的操作單元脆荷，事務(wù)中所有操作凝垛，要么全部成功；要么撤回到執(zhí)行事務(wù)之前的狀態(tài)
2. 一致性（Consistency）：如果在執(zhí)行事務(wù)之前數(shù)據(jù)庫是一致的蜓谋，那么在執(zhí)行事務(wù)之后數(shù)據(jù)庫也還是一致的梦皮；
3. 隔離性（Isolation）：事務(wù)操作之間彼此獨(dú)立和透明互不影響。事務(wù)獨(dú)立運(yùn)行桃焕。這通常使用鎖來實(shí)現(xiàn)剑肯。一個事務(wù)處理后的結(jié)果，影響了其他事務(wù)观堂，那么其他事務(wù)會撤回让网。事務(wù)的100%隔離，需要犧牲速度师痕。
4. 持久性（Durability）：事務(wù)一旦提交溃睹，其結(jié)果就是永久的。即便發(fā)生系統(tǒng)故障胰坟，也能恢復(fù)因篇。

delete drop truncate區(qū)別

truncate 和 delete只刪除數(shù)據(jù)，不刪除表結(jié)構(gòu) ,drop刪除表結(jié)構(gòu)，并且釋放所占的空間惜犀。

刪除數(shù)據(jù)的速度铛碑，一般來說: drop> truncate > delete

delete屬于DML語言，需要事務(wù)管理虽界，commit之后才能生效汽烦。drop和truncate屬于DDL語言，操作立刻生效莉御，不可回滾

使用場合：

• 當(dāng)你不再需要該表時(shí)撇吞，用 drop;
• 當(dāng)你仍要保留該表，但要刪除所有記錄時(shí)礁叔， 用 truncate;
• 當(dāng)你要刪除部分記錄時(shí)（always with a where clause), 用 delete.

Mysql explain

explain 用于查看 SQL 語句執(zhí)行計(jì)劃牍颈，其結(jié)果主要包含以下幾個重要參數(shù)：id、select_type琅关、table煮岁、type、possible_keys涣易、key画机、key_len、ref新症、rows步氏、Extra,下面對這些字段出現(xiàn)的可能進(jìn)行解釋。

select_type

表示查詢中每個select子句的類型

• SIMPLE(簡單SELECT,不使用UNION或子查詢等)
• PRIMARY(查詢中若包含任何復(fù)雜的子部分,最外層的select被標(biāo)記為PRIMARY)
• UNION(UNION中的第二個或后面的SELECT語句)
• DEPENDENT UNION(UNION中的第二個或后面的SELECT語句徒爹，取決于外面的查詢)
• UNION RESULT(UNION的結(jié)果)
• SUBQUERY(子查詢中的第一個SELECT)
• DEPENDENT SUBQUERY(子查詢中的第一個SELECT荚醒，取決于外面的查詢)
• DERIVED(派生表的SELECT, FROM子句的子查詢)
• UNCACHEABLE SUBQUERY(一個子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行)

type

表示MySQL在表中找到所需行的方式隆嗅，又稱“訪問類型”界阁。

常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）

• ALL：Full Table Scan胖喳， MySQL將遍歷全表以找到匹配的行
• index: Full Index Scan泡躯，index與ALL區(qū)別為index類型只遍歷索引樹
• range:只檢索給定范圍的行，使用一個索引來選擇行
• ref: 表示上述表的連接匹配條件禀晓，即哪些列或常量被用于查找索引列上的值
• eq_ref: 類似ref精续，區(qū)別就在使用的索引是唯一索引，對于每個索引鍵值粹懒，表中只有一條記錄匹配重付，簡單來說，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件
• const凫乖、system: 當(dāng)MySQL對查詢某部分進(jìn)行優(yōu)化确垫，并轉(zhuǎn)換為一個常量時(shí)弓颈，使用這些類型訪問。如將主鍵置于where列表中删掀，MySQL就能將該查詢轉(zhuǎn)換為一個常量,system是const類型的特例翔冀，當(dāng)查詢的表只有一行的情況下，使用system
• NULL: MySQL在優(yōu)化過程中分解語句披泪，執(zhí)行時(shí)甚至不用訪問表或索引纤子，例如從一個索引列里選取最小值可以通過單獨(dú)索引查找完成。

possible_keys

指出MySQL能使用哪個索引在表中找到記錄款票，查詢涉及到的字段上若存在索引控硼，則該索引將被列出，但不一定被查詢使用
該列完全獨(dú)立于EXPLAIN輸出所示的表的次序艾少。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用卡乾。
如果該列是NULL，則沒有相關(guān)的索引缚够。在這種情況下幔妨，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣谍椅，創(chuàng)造一個適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

Key

key列顯示MySQL實(shí)際決定使用的鍵（索引）
如果沒有選擇索引误堡，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引毯辅，在查詢中使用FORCE INDEX埂伦、USE INDEX或者IGNORE INDEX煞额。

explain 總結(jié)

• EXPLAIN不會告訴你關(guān)于觸發(fā)器思恐、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時(shí)所作的優(yōu)化工作
• 部分統(tǒng)計(jì)信息是估算的，并非精確值
• EXPALIN只能解釋SELECT操作膊毁，其他操作要重寫為SELECT后查看執(zhí)行計(jì)劃胀莹。

參考資料

https://ningyu1.github.io/site/post/50-mysql-gap-lock/
MySQL 的 MVCC 原理
MySQL - 索引詳解