摘要：索引實驗實驗目的了解索引對于全列匹配，最左前綴匹配范圍查詢的影響。因此在中要謹慎地區分多值匹配和范圍匹配，否則會對的行為產生困惑。事務隔離層級實驗實驗目的了解中事務隔離級別以及什么是臟讀，幻讀，不可重復讀。

實驗目的：了解索引對于全列匹配，最左前綴匹配、范圍查詢的影響。實驗所用數據庫見文章最底部連接。

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

show index from `employees`.`titles`

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

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

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

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

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

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

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

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

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

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

只有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";

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

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

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

由于不是最左前綴，索引這樣的查詢顯然用不到索引。

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

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

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

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

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

可以看到索引對第二個范圍索引無能為力。這里特別要說明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";

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

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

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

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

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

顯然這個查詢等價于查詢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`;

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

前綴索引

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

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

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

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

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

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

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

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

加索引

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

前綴索引兼顧索引大小和查詢速度，但是其缺點是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即當索引本身包含查詢所需全部數據時，不再訪問數據文件本身）。

實驗目的：了解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的數據，這就是所謂的臟讀。

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

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

1.設置事物隔離級別。

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

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;

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

例如銀行想查詢A帳戶余額，第一次查詢A帳戶為200元，此時A向帳戶內存了100元并提交了，銀行接著又進行了一次查詢，此時A帳戶為300元了。銀行兩次查詢不一致，可能就會很困惑，不知道哪次查詢是準的。
　 不可重復讀和臟讀的區別是，臟讀是讀取前一事務未提交的臟數據，不可重復讀是重新讀取了前一事務已提交的數據。
　　很多人認為這種情況就對了，無須困惑，當然是后面的為準。我們可以考慮這樣一種情況，比如銀行程序需要將查詢結果分別輸出到電腦屏幕和寫到文件中，結果在一個事務中針對輸出的目的地，進行的兩次查詢不一致，導致文件和屏幕中的結果不一致，銀行工作人員就不知道以哪個為準了。

開啟連接查詢值。

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

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;

未提交讀：第一個事務還未提交，另一個事務就可以讀取，導致臟讀。

提交讀（不可重復讀）：一個事務未提交對其他事務不可見，但是會產生幻讀和不可重復讀。

可重復讀(mysql默認隔離級別)：保證同一個事務下多次讀取的結果一致，但是會產生幻讀。

可串行化：嚴格的串行阻塞，并發能力不好。

1.走進mysql基礎
2.MySQL索引背后的數據結構及算法原理
3.datacharmer/test_db