摘要：索引實驗實驗?zāi)康牧私馑饕龑τ谌衅ヅ洌钭笄熬Y匹配范圍查詢的影響。因此在中要謹(jǐn)慎地區(qū)分多值匹配和范圍匹配，否則會對的行為產(chǎn)生困惑。事務(wù)隔離層級實驗實驗?zāi)康牧私庵惺聞?wù)隔離級別以及什么是臟讀，幻讀，不可重復(fù)讀。

實驗?zāi)康模毫私馑饕龑τ谌衅ヅ?，最左前綴匹配、范圍查詢的影響。實驗所用數(shù)據(jù)庫見文章最底部連接。

實驗軟件版本：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";

很明顯，當(dāng)按照索引中所有列進行精確匹配（這里精確匹配指“=”或“IN”匹配）時，索引可以被用到。這里有一點需要注意，理論上索引對順序是敏感的，但是由于MySQL的查詢優(yōu)化器會自動調(diào)整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";

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

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

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

如果想讓from_date也使用索引而不是where過濾，可以增加一個輔助索引，此時上面的查詢會使用這個索引。除此之外，還可以使用一種稱之為“隔離列”的優(yōu)化方法，將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實際上執(zhí)行了一個range查詢，這里檢查了7個key?？聪聝煞N查詢的性能比較：

“填坑”后性能提升了一點。如果經(jīng)過emp_no篩選后余下很多數(shù)據(jù)，則后者性能優(yōu)勢會更加明顯。當(dāng)然，如果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%";

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

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可能無法區(qū)分范圍索引和多值匹配，因為在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”實際上相當(dāng)于“IN”，也就是說emp_no實際是多值精確匹配。可以看到這個查詢用到了索引全部三個列。因此在MySQL中要謹(jǐn)慎地區(qū)分多值匹配和范圍匹配，否則會對MySQL的行為產(chǎn)生困惑。

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

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

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

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

顯然這個查詢等價于查詢emp_no為10001的函數(shù)，但是由于查詢條件是一個表達(dá)式，MySQL無法為其使用索引。因此在寫查詢語句時盡量避免表達(dá)式出現(xiàn)在查詢中，而是先手工私下代數(shù)運算，轉(zhuǎn)換為無表達(dá)式的查詢語句。

索引選擇性

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

Index Selectivity = Cardinality / #T

顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價值越大，這是由B+Tree的性質(zhì)決定的。例如，上文用到的employees.titles表，如果title字段經(jīng)常被多帶帶查詢，是否需要建索引，我們看一下它的選擇性：

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

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

前綴索引

有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引，就是用列的前綴代替整個列作為索引key，當(dāng)前綴長度合適時，可以做到既使得前綴索引的選擇性接近全列索引，同時因為索引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（即當(dāng)索引本身包含查詢所需全部數(shù)據(jù)時，不再訪問數(shù)據(jù)文件本身）。

實驗?zāi)康模毫私釳ySQL中事務(wù)隔離級別以及什么是臟讀，幻讀，不可重復(fù)讀。

定義：在兩個事務(wù)中，一個事務(wù)讀到了另一個事務(wù)未提交的數(shù)據(jù)。因為數(shù)據(jù)可能被回滾，不符合隔離性的定義。

1.新建數(shù)據(jù)庫連接執(zhí)行一下操作

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

注意還沒有執(zhí)行 commit

2.然后新建一個連接 可以看到讀到了另一個事物還未被commit的數(shù)據(jù)，這就是所謂的臟讀。

定義：一個事務(wù)批量讀取了一批數(shù)據(jù)時，另一個事務(wù)提交了新的數(shù)據(jù)，當(dāng)之前的事務(wù)再次讀取時，會產(chǎn)生幻影行。

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

1.設(shè)置事物隔離級別。

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.回到第一步的窗口，查詢數(shù)據(jù)。

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

定義：不可重復(fù)讀指在一個事務(wù)內(nèi)讀取表中的某一行數(shù)據(jù)，多次讀取結(jié)果不同。

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

開啟連接查詢值。

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;

未提交讀：第一個事務(wù)還未提交，另一個事務(wù)就可以讀取，導(dǎo)致臟讀。

提交讀（不可重復(fù)讀）：一個事務(wù)未提交對其他事務(wù)不可見，但是會產(chǎn)生幻讀和不可重復(fù)讀。

可重復(fù)讀(mysql默認(rèn)隔離級別)：保證同一個事務(wù)下多次讀取的結(jié)果一致，但是會產(chǎn)生幻讀。

可串行化：嚴(yán)格的串行阻塞，并發(fā)能力不好。

1.走進mysql基礎(chǔ)
2.MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
3.datacharmer/test_db