摘要：索引實(shí)驗(yàn)實(shí)驗(yàn)?zāi)康牧私馑饕龑?duì)于全列匹配，最左前綴匹配范圍查詢(xún)的影響。因此在中要謹(jǐn)慎地區(qū)分多值匹配和范圍匹配，否則會(huì)對(duì)的行為產(chǎn)生困惑。事務(wù)隔離層級(jí)實(shí)驗(yàn)實(shí)驗(yàn)?zāi)康牧私庵惺聞?wù)隔離級(jí)別以及什么是臟讀，幻讀，不可重復(fù)讀。

實(shí)驗(yàn)?zāi)康模毫私馑饕龑?duì)于全列匹配，最左前綴匹配、范圍查詢(xún)的影響。實(shí)驗(yàn)所用數(shù)據(jù)庫(kù)見(jiàn)文章最底部連接。

實(shí)驗(yàn)軟件版本：5.7.19-0ubuntu0.16.04.1-log (Ubuntu)
實(shí)驗(yàn)存儲(chǔ)引擎：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)按照索引中所有列進(jìn)行精確匹配（這里精確匹配指“=”或“IN”匹配）時(shí)，索引可以被用到。這里有一點(diǎn)需要注意，理論上索引對(duì)順序是敏感的，但是由于MySQL的查詢(xún)優(yōu)化器會(huì)自動(dòng)調(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ún)條件精確匹配索引的左邊連續(xù)一個(gè)或幾個(gè)列時(shí)，如或，所以可以被用到，但是只能用到一部分，即條件所組成的最左前綴。上面的查詢(xún)從分析結(jié)果看用到了PRIMARY索引，但是key_len為4，說(shuō)明只用到了索引的第一列前綴。

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

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

如果想讓from_date也使用索引而不是where過(guò)濾，可以增加一個(gè)輔助索引，此時(shí)上面的查詢(xún)會(huì)使用這個(gè)索引。除此之外，還可以使用一種稱(chēng)之為“隔離列”的優(yōu)化方法，將emp_no與from_date之間的“坑”填上。

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

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

只有7種。在這種成為“坑”的列值比較少的情況下，可以考慮用“IN”來(lái)填補(bǔ)這個(gè)“坑”從而形成最左前綴：

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，說(shuō)明索引被用全了，但是從type和rows看出IN實(shí)際上執(zhí)行了一個(gè)range查詢(xún)，這里檢查了7個(gè)key?？聪聝煞N查詢(xún)的性能比較：

“填坑”后性能提升了一點(diǎn)。如果經(jīng)過(guò)emp_no篩選后余下很多數(shù)據(jù)，則后者性能優(yōu)勢(shì)會(huì)更加明顯。當(dāng)然，如果title的值很多，用填坑就不合適了，必須建立輔助索引。

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

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

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

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

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

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

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

可以看到索引對(duì)第二個(gè)范圍索引無(wú)能為力。這里特別要說(shuō)明MySQL一個(gè)有意思的地方，那就是僅用explain可能無(wú)法區(qū)分范圍索引和多值匹配，因?yàn)樵趖ype中這兩者都顯示為range。同時(shí)，用了“between”并不意味著就是范圍查詢(xún)，例如下面的查詢(xún)：

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

看起來(lái)是用了兩個(gè)范圍查詢(xún)，但作用于emp_no上的“BETWEEN”實(shí)際上相當(dāng)于“IN”，也就是說(shuō)emp_no實(shí)際是多值精確匹配。可以看到這個(gè)查詢(xún)用到了索引全部三個(gè)列。因此在MySQL中要謹(jǐn)慎地區(qū)分多值匹配和范圍匹配，否則會(huì)對(duì)MySQL的行為產(chǎn)生困惑。

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

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

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

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

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

索引選擇性

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

Index Selectivity = Cardinality / #T

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

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

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

前綴索引

有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引，就是用列的前綴代替整個(gè)列作為索引key，當(dāng)前綴長(zhǎng)度合適時(shí)，可以做到既使得前綴索引的選擇性接近全列索引，同時(shí)因?yàn)樗饕齥ey變短而減少了索引文件的大小和維護(hù)開(kāi)銷(xiāo)。

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

因?yàn)閑mployees表只有一個(gè)索引，那么如果我們想按名字搜索一個(gè)人，就只能全表掃描了：

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

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加起來(lái)長(zhǎng)度為30，有沒(méi)有兼顧長(zhǎng)度和選擇性的辦法？可以考慮用first_name和last_name的前幾個(gè)字符建立索引，例如，看看其選擇性：

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));

前綴索引兼顧索引大小和查詢(xún)速度，但是其缺點(diǎn)是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即當(dāng)索引本身包含查詢(xún)所需全部數(shù)據(jù)時(shí)，不再訪問(wèn)數(shù)據(jù)文件本身）。

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

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

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

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

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

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

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

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

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

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

2.新開(kāi)一個(gè)連接

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

3.回到第一步的窗口，查詢(xún)數(shù)據(jù)。

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

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

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

開(kāi)啟連接查詢(xún)值。

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

2.新開(kāi)一個(gè)連接修改emp_no為100001的title的值。

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

3.回到第一步的連接再次查詢(xún)

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

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

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

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

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

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