国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

MySQL實(shí)驗(yàn): 實(shí)踐索引對(duì)全列匹配、最左前綴匹配、范圍查詢等條件的影響以及了解臟讀、幻讀等

lewinlee / 1861人閱讀

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

索引實(shí)驗(yàn)

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

實(shí)驗(yàn)軟件版本:5.7.19-0ubuntu0.16.04.1-log (Ubuntu)
實(shí)驗(yàn)存儲(chǔ)引擎:InnoDB

show index from `employees`.`titles`

實(shí)驗(yàn)一、全列匹配
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的查詢優(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";

實(shí)驗(yàn)二、最左前綴匹配
explain select * from `employees`.`titles` where `emp_no`="10001";

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

實(shí)驗(yàn)三、查詢條件用到了索引中列的精確匹配,但是中間某個(gè)條件未提供
explain select * from `employees`.`titles` where `emp_no`="10001" and `from_date` = "1986-06-26" ;

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

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

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

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

只有7種。在這種成為“坑”的列值比較少的情況下,可以考慮用“IN”來填補(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,說明索引被用全了,但是從type和rows看出IN實(shí)際上執(zhí)行了一個(gè)range查詢,這里檢查了7個(gè)key。看下兩種查詢的性能比較:

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

實(shí)驗(yàn)四:查詢條件沒有指定索引第一列
explain select * from `employees`.`titles` where `from_date` = "1986-06-26";

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

實(shí)驗(yàn)五:匹配某列的前綴字符串
explain select * from `employees`.`titles`where `emp_no` = "10001" and `title` like "Senior%";

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

實(shí)驗(yàn)六:范圍查詢
explain select * from `employees`.`titles` where `emp_no` < "10010" and `title` = "Senior Engineer";

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

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è)范圍索引無能為力。這里特別要說明MySQL一個(gè)有意思的地方,那就是僅用explain可能無法區(qū)分范圍索引和多值匹配,因?yàn)樵趖ype中這兩者都顯示為range。同時(shí),用了“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";

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

實(shí)驗(yàn)七:查詢條件中含有函數(shù)或表達(dá)式

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

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

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

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

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

索引選擇性與前綴索引
索引選擇性

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

Index Selectivity = Cardinality / #T

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

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

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

前綴索引

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

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加起來長度為30,有沒有兼顧長度和選擇性的辦法?可以考慮用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));

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

MySQL事務(wù)隔離層級(jí)實(shí)驗(yàn)

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

實(shí)驗(yàn)一:臟讀

定義:在兩個(gè)事務(wù)中,一個(gè)事務(wù)讀到了另一個(gè)事務(wù)未提交的數(shù)據(jù)。因?yàn)閿?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.然后新建一個(gè)連接 可以看到讀到了另一個(gè)事物還未被commit的數(shù)據(jù),這就是所謂的臟讀。

實(shí)驗(yàn)二:幻讀

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

如丙存款100元未提交,這時(shí)銀行做報(bào)表統(tǒng)計(jì)account表中所有用戶的總額為500元,然后丙提交了,這時(shí)銀行再統(tǒng)計(jì)發(fā)現(xiàn)帳戶為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.新開一個(gè)連接

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;

實(shí)驗(yàn)三:不可重復(fù)讀

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

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

開啟連接查詢值。

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

2.新開一個(gè)連接修改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事務(wù)隔離級(jí)別

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

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

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

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

隔離級(jí)別 臟讀 不可重復(fù)讀 幻讀
Read Uncommitted ? ? ?
Read Committed ? ? ?
Repeatable Read (默認(rèn)) ? ? ?
Serializable ? ? ?
參考資料

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

文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址:http://specialneedsforspecialkids.com/yun/29525.html

相關(guān)文章

  • 數(shù)據(jù)庫兩個(gè)神器【索引和鎖】

    摘要:索引需要占物理和數(shù)據(jù)空間。本質(zhì)上就是把鍵值換算成新的哈希值,根據(jù)這個(gè)哈希值來定位。,索引列不能參與計(jì)算,盡量保持列干凈。 前言 只有光頭才能變強(qiáng) 索引和鎖在數(shù)據(jù)庫中可以說是非常重要的知識(shí)點(diǎn)了,在面試中也會(huì)經(jīng)常會(huì)被問到的。 本文力求簡單講清每個(gè)知識(shí)點(diǎn),希望大家看完能有所收獲 聲明:如果沒有說明具體的數(shù)據(jù)庫和存儲(chǔ)引擎,默認(rèn)指的是MySQL中的InnoDB存儲(chǔ)引擎 一、索引 在之前,我對(duì)索...

    TZLLOG 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

lewinlee

|高級(jí)講師

TA的文章

閱讀更多
最新活動(dòng)
閱讀需要支付1元查看
<