MySQL實驗: 實踐索引對全列匹配、最左字首匹配、範圍查詢等條件的影響以及瞭解髒讀、幻讀等

xx19941215發表於2018-10-14
MySql索引

索引實驗

實驗目的：瞭解索引對於全列匹配，最左字首匹配、範圍查詢的影響。實驗所用資料庫見文章最底部連線。

實驗軟體版本：5.7.19-0ubuntu0.16.04.1-log (Ubuntu)
實驗儲存引擎：InnoDB

show index from `employees`.`titles`

clipboard.png

實驗一、全列匹配

explain select * from `employees`.`titles` where `emp_no`=`10001` and title=`Senior Engineer` and `from_date`=`1986-06-26`;

clipboard.png

很明顯，當按照索引中所有列進行精確匹配（這裡精確匹配指“=”或“IN”匹配）時，索引可以被用到。這裡有一點需要注意，理論上索引對順序是敏感的，但是由於MySQL的查詢優化器會自動調整where子句的條件順序以使用適合的索引。

explain select * from `employees`.`titles` where `from_date`=`1986-06-26` and `emp_no`=`10001` and title=`Senior Engineer`;

clipboard.png

實驗二、最左字首匹配

explain select * from `employees`.`titles` where `emp_no`=`10001`;

clipboard.png

當查詢條件精確匹配索引的左邊連續一個或幾個列時，如<emp_no>或<emp_no, title>，所以可以被用到，但是隻能用到一部分，即條件所組成的最左字首。上面的查詢從分析結果看用到了PRIMARY索引，但是key_len為4，說明只用到了索引的第一列字首。

實驗三、查詢條件用到了索引中列的精確匹配，但是中間某個條件未提供

explain select * from `employees`.`titles` where `emp_no`=`10001` and `from_date` = `1986-06-26` ;

clipboard.png

此時索引使用情況和實驗二相同，因為title未提供，所以查詢只用到了索引的第一列，而後面的from_date雖然也在索引中，但是由於title不存在而無法和左字首連線，因此需要對結果進行掃描過濾from_date（這裡由於emp_no唯一，所以不存在掃描）。

如果想讓from_date也使用索引而不是where過濾，可以增加一個輔助索引<emp_no, from_date>，此時上面的查詢會使用這個索引。除此之外，還可以使用一種稱之為“隔離列”的優化方法，將emp_no與from_date之間的“坑”填上。

看下title一共有幾種不同的值。

select distinct(title) from `employees`.`titles`;

clipboard.png

只有7種。在這種成為“坑”的列值比較少的情況下，可以考慮用“IN”來填補這個“坑”從而形成最左字首：

explain select * from `employees`.`titles`
where `emp_no` = `10001`
and `title` IN (`Senior Engineer`, `Staff`, `Engineer`, `Senior Staff`, `Assistant Engineer`, `Technique Leader`, `Manager`)
and `from_date` = `1986-06-26`;

clipboard.png

這次key_len為59，說明索引被用全了，但是從type和rows看出IN實際上執行了一個range查詢，這裡檢查了7個key。看下兩種查詢的效能比較：

clipboard.png

“填坑”後效能提升了一點。如果經過emp_no篩選後餘下很多資料，則後者效能優勢會更加明顯。當然，如果title的值很多，用填坑就不合適了，必須建立輔助索引。

實驗四：查詢條件沒有指定索引第一列

explain select * from `employees`.`titles` where `from_date` = `1986-06-26`;

clipboard.png

由於不是最左字首，索引這樣的查詢顯然用不到索引。

實驗五：匹配某列的字首字串

explain select * from `employees`.`titles`where `emp_no` = `10001` and `title` like `Senior%`;

clipboard.png

此時可以用到索引。如果配符%不出現在開頭，則可以用到索引，但根據具體情況不同可能只會用其中一個字首。

實驗六：範圍查詢

explain select * from `employees`.`titles` where `emp_no` < `10010` and `title` = `Senior Engineer`;

clipboard.png

範圍列可以用到索引（必須是最左字首），但是範圍列後面的列無法用到索引。同時，索引最多用於一個範圍列，因此如果查詢條件中有兩個範圍列則無法全用到索引。

explain select * from `employees`.`titles`
where `emp_no` < `10010`
and `title` = `Senior Engineer`
and `from_date` between `1986-01-01` and `1986-12-11`;

clipboard.png

可以看到索引對第二個範圍索引無能為力。這裡特別要說明MySQL一個有意思的地方，那就是僅用explain可能無法區分範圍索引和多值匹配，因為在type中這兩者都顯示為range。同時，用了“between”並不意味著就是範圍查詢，例如下面的查詢：

explain select * from `employees`.`titles`
where `emp_no` between `10001` and `10010`
and `title` = `Senior Enginee`
and `from_date` between `1986-01-01` and `1986-12-31`;

clipboard.png

看起來是用了兩個範圍查詢，但作用於emp_no上的“BETWEEN”實際上相當於“IN”，也就是說emp_no實際是多值精確匹配。可以看到這個查詢用到了索引全部三個列。因此在MySQL中要謹慎地區分多值匹配和範圍匹配，否則會對MySQL的行為產生困惑。

實驗七：查詢條件中含有函式或表示式

如果查詢條件中含有函式或表示式，則MySQL不會為這列使用索引（雖然某些在數學意義上可以使用）。例如：

explain select * from `employees`.`titles` where `emp_no` = `10001` and left(`title`, 6) = `Senior`;

clipboard.png

雖然這個查詢和實驗五中功能相同，但是由於使用了函式left，則無法為title列應用索引，而實驗五中用LIKE則可以。再如：

explain select * from `employees`.`titles` where `emp_no` - 1 = `10000`;

clipboard.png

顯然這個查詢等價於查詢emp_no為10001的函式，但是由於查詢條件是一個表示式，MySQL無法為其使用索引。因此在寫查詢語句時儘量避免表示式出現在查詢中，而是先手工私下代數運算，轉換為無表示式的查詢語句。

索引選擇性與字首索引

索引選擇性

所謂索引的選擇性（Selectivity），是指不重複的索引值（也叫基數，Cardinality）與表記錄數（#T）的比值：

Index Selectivity = Cardinality / #T

顯然選擇性的取值範圍為(0, 1]，選擇性越高的索引價值越大，這是由B+Tree的性質決定的。例如，上文用到的employees.titles表，如果title欄位經常被單獨查詢，是否需要建索引，我們看一下它的選擇性：

select count(distinct(title))/count(*) as selectivity from `employees`.`titles`;

clipboard.png

title的選擇性不足0.0001（精確值為0.00001579），所以實在沒有什麼必要為其單獨建索引。

字首索引

有一種與索引選擇性有關的索引優化策略叫做字首索引，就是用列的字首代替整個列作為索引key，當字首長度合適時，可以做到既使得字首索引的選擇性接近全列索引，同時因為索引key變短而減少了索引檔案的大小和維護開銷。

explain select * from `employees`.`employees` where `first_name` = `Eric` and `last_name` = `Anido`;

因為employees表只有一個索引<emp_no>，那麼如果我們想按名字搜尋一個人，就只能全表掃描了：

clipboard.png

如果頻繁按名字搜尋員工，這樣顯然效率很低，因此我們可以考慮建索引。有兩種選擇，建<first_name>或<first_name, last_name>，看下兩個索引的選擇性：

select count(distinct(first_name))/count(*) as selectivity from `employees`.`employees`;

clipboard.png

select count(distinct(concat(first_name, last_name)))/count(*) as selectivity from `employees`.`employees`;

clipboard.png

<first_name>顯然選擇性太低，<first_name, last_name>選擇性很好，但是first_name和last_name加起來長度為30，有沒有兼顧長度和選擇性的辦法？可以考慮用first_name和last_name的前幾個字元建立索引，例如<first_name, left(last_name, 4)>，看看其選擇性：

select count(distinct(concat(first_name, left(last_name, 4))))/count(*) as selectivity from `employees`.`employees`;

clipboard.png

加索引

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

字首索引兼顧索引大小和查詢速度，但是其缺點是不能用於ORDER BY和GROUP BY操作，也不能用於Covering index（即當索引本身包含查詢所需全部資料時，不再訪問資料檔案本身）。

MySQL事務隔離層級實驗

實驗目的：瞭解MySQL中事務隔離級別以及什麼是髒讀，幻讀，不可重複讀。

實驗一：髒讀

定義：在兩個事務中，一個事務讀到了另一個事務未提交的資料。因為資料可能被回滾，不符合隔離性的定義。

1.新建資料庫連線執行一下操作

set global transaction isolation level read uncommitted;
set autocommit = 0;
begin;
update `employees`.`titles` set `title` = `Senior Engineer 1` where `emp_no` = 100001;

注意還沒有執行 commit

2.然後新建一個連線 可以看到讀到了另一個事物還未被commit的資料，這就是所謂的髒讀。
clipboard.png

實驗二：幻讀

定義：一個事務批量讀取了一批資料時，另一個事務提交了新的資料，當之前的事務再次讀取時，會產生幻影行。

如丙存款100元未提交，這時銀行做報表統計account表中所有使用者的總額為500元，然後丙提交了，這時銀行再統計發現帳戶為600元了，造成虛讀同樣會使銀行不知所措，到底以哪個為準。

1.設定事物隔離級別。

set global transaction isolation level read committed;
begin;
select * from `employees`.`titles` where `titles`.`from_date` = `1994-12-15`;

clipboard.png

2.新開一個連線

begin;
insert into `titles` values (499999, `Engineer`, `1994-12-15`, `1994-12-15`);
commit;

3.回到第一步的視窗，查詢資料。

select * from `employees`.`titles` where `titles`.`from_date` = `1994-12-15`;
commit;

clipboard.png

實驗三：不可重複讀

定義：不可重複讀指在一個事務內讀取表中的某一行資料，多次讀取結果不同。

例如銀行想查詢A帳戶餘額，第一次查詢A帳戶為200元，此時A向帳戶記憶體了100元並提交了，銀行接著又進行了一次查詢，此時A帳戶為300元了。銀行兩次查詢不一致，可能就會很困惑，不知道哪次查詢是準的。
　 不可重複讀和髒讀的區別是，髒讀是讀取前一事務未提交的髒資料，不可重複讀是重新讀取了前一事務已提交的資料。
　　很多人認為這種情況就對了，無須困惑，當然是後面的為準。我們可以考慮這樣一種情況，比如銀行程式需要將查詢結果分別輸出到電腦螢幕和寫到檔案中，結果在一個事務中針對輸出的目的地，進行的兩次查詢不一致，導致檔案和螢幕中的結果不一致，銀行工作人員就不知道以哪個為準了。

  1. 開啟連線查詢值。
begin;
select * from `employees`.`titles` where `emp_no` = 100001;
select * from `employees`.`titles` where `emp_no` = 100001;

clipboard.png

2.新開一個連線修改emp_no為100001的title的值。

begin;
update `employees`.`titles` set `title` = `Senior Engineer 1` where `emp_no` = 100001;
commit;

3.回到第一步的連線再次查詢

select * from `employees`.`titles` where `emp_no` = 100001;

clipboard.png

MySQL事務隔離級別

  • 未提交讀：第一個事務還未提交，另一個事務就可以讀取，導致髒讀。
  • 提交讀（不可重複讀）：一個事務未提交對其他事務不可見，但是會產生幻讀和不可重複讀。
  • 可重複讀(mysql預設隔離級別)：保證同一個事務下多次讀取的結果一致，但是會產生幻讀。
  • 可序列化：嚴格的序列阻塞，併發能力不好。
隔離級別 髒讀 不可重複讀 幻讀
Read Uncommitted ✅ ✅ ✅
Read Committed ❌ ✅ ✅
Repeatable Read (預設) ❌ ❌ ✅
Serializable ❌ ❌ ❌

參考資料

1.走進mysql基礎
2.MySQL索引背後的資料結構及演算法原理
3.datacharmer/test_db

相關文章