一、索引
MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的,索引可以大大提高M(jìn)ySQL的檢索速度。
打個(gè)比方,如果合理的設(shè)計(jì)且使用索引的MySQL是一輛蘭博基尼的話,那么沒有設(shè)計(jì)和使用索引的MySQL就是一個(gè)人力三輪車。
索引分單列索引和組合索引。單列索引,即一個(gè)索引只包含單個(gè)列,一個(gè)表可以有多個(gè)單列索引,但這不是組合索引。組合索引,即一個(gè)索引包含多個(gè)列。
創(chuàng)建索引時(shí),你需要確保該索引是應(yīng)用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。
實(shí)際上,索引也是一張表,該表保存了主鍵與索引字段,并指向?qū)嶓w表的記錄。
上面都在說使用索引的好處,但過多的使用索引將會(huì)造成濫用。因此索引也會(huì)有它的缺點(diǎn):雖然索引大大提高了查詢速度,同時(shí)卻會(huì)降低更新表的速度,如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí),MySQL不僅要保存數(shù)據(jù),還要保存一下索引文件。
建立索引會(huì)占用磁盤空間的索引文件。
多數(shù)情況下,我們知道索引能夠提高查詢效率,但應(yīng)該如何建立索引?索引的順序如何?許多人卻只知道大概。其實(shí)理解這些概念并不難,而且索引的原理遠(yuǎn)沒有想象的那么復(fù)雜。
索引的目的在于提高查詢效率,可以類比字典,如果要查“mysql”這個(gè)單詞,我們肯定需要定位到m字母,然后從下往下找到y(tǒng)字母,再找到剩下的sql。如果沒有索引,那么你可能需要把所有單詞看一遍才能找到你想要的,如果我想找到m開頭的單詞呢?或者ze開頭的單詞呢?是不是覺得如果沒有索引,這個(gè)事情根本無法完成?
除了詞典,生活中隨處可見索引的例子,如火車站的車次表、圖書的目錄等。它們的原理都是一樣的,通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果,同時(shí)把隨機(jī)的事件變成順序的事件,也就是我們總是通過同一種查找方式來鎖定數(shù)據(jù)。
數(shù)據(jù)庫也是一樣,但顯然要復(fù)雜許多,因?yàn)椴粌H面臨著等值查詢,還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對(duì)所有的問題呢?我們回想字典的例子,能不能把數(shù)據(jù)分成段,然后分段查詢呢?最簡(jiǎn)單的如果1000條數(shù)據(jù),1到100分成第一段,101到200分成第二段,201到300分成第三段……這樣查第250條數(shù)據(jù),只要找第三段就可以了,一下子去除了90%的無效數(shù)據(jù)。但如果是1千萬的記錄呢,分成幾段比較好?稍有算法基礎(chǔ)的同學(xué)會(huì)想到搜索樹,其平均復(fù)雜度是lgN,具有不錯(cuò)的查詢性能。但這里我們忽略了一個(gè)關(guān)鍵的問題,復(fù)雜度模型是基于每次相同的操作成本來考慮的,數(shù)據(jù)庫實(shí)現(xiàn)比較復(fù)雜,數(shù)據(jù)保存在磁盤上,而為了提高性能,每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來計(jì)算,因?yàn)槲覀冎涝L問磁盤的成本大概是訪問內(nèi)存的十萬倍左右,所以簡(jiǎn)單的搜索樹難以滿足復(fù)雜的應(yīng)用場(chǎng)景。
前面提到了訪問磁盤,那么這里先簡(jiǎn)單介紹一下磁盤IO和預(yù)讀,磁盤讀取數(shù)據(jù)靠的是機(jī)械運(yùn)動(dòng),每次讀取數(shù)據(jù)花費(fèi)的時(shí)間可以分為尋道時(shí)間、旋轉(zhuǎn)延遲、傳輸時(shí)間三個(gè)部分,尋道時(shí)間指的是磁臂移動(dòng)到指定磁道所需要的時(shí)間,主流磁盤一般在5ms以下;旋轉(zhuǎn)延遲就是我們經(jīng)常聽說的磁盤轉(zhuǎn)速,比如一個(gè)磁盤7200轉(zhuǎn),表示每分鐘能轉(zhuǎn)7200次,也就是說1秒鐘能轉(zhuǎn)120次,旋轉(zhuǎn)延遲就是1/120/2 = 4.17ms;傳輸時(shí)間指的是從磁盤讀出或?qū)?shù)據(jù)寫入磁盤的時(shí)間,一般在零點(diǎn)幾毫秒,相對(duì)于前兩個(gè)時(shí)間可以忽略不計(jì)。那么訪問一次磁盤的時(shí)間,即一次磁盤IO的時(shí)間約等于5+4.17 = 9ms左右,聽起來還挺不錯(cuò)的,但要知道一臺(tái)500 -MIPS的機(jī)器每秒可以執(zhí)行5億條指令,因?yàn)橹噶钜揽康氖请姷男再|(zhì),換句話說執(zhí)行一次IO的時(shí)間可以執(zhí)行40萬條指令,數(shù)據(jù)庫動(dòng)輒十萬百萬乃至千萬級(jí)數(shù)據(jù),每次9毫秒的時(shí)間,顯然是個(gè)災(zāi)難。下圖是計(jì)算機(jī)硬件延遲的對(duì)比圖,供大家參考:
考慮到磁盤IO是非常高昂的操作,計(jì)算機(jī)操作系統(tǒng)做了一些優(yōu)化,當(dāng)一次IO時(shí),不光把當(dāng)前磁盤地址的數(shù)據(jù),而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi),因?yàn)榫植款A(yù)讀性原理告訴我們,當(dāng)計(jì)算機(jī)訪問一個(gè)地址的數(shù)據(jù)的時(shí)候,與其相鄰的數(shù)據(jù)也會(huì)很快被訪問到。每一次IO讀取的數(shù)據(jù)我們稱之為一頁(page)。具體一頁有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān),一般為4k或8k,也就是我們讀取一頁內(nèi)的數(shù)據(jù)時(shí)候,實(shí)際上才發(fā)生了一次IO,這個(gè)理論對(duì)于索引的數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)非常有幫助。
前面講了生活中索引的例子,索引的基本原理,數(shù)據(jù)庫的復(fù)雜性,又講了操作系統(tǒng)的相關(guān)知識(shí),目的就是讓大家了解,任何一種數(shù)據(jù)結(jié)構(gòu)都不是憑空產(chǎn)生的,一定會(huì)有它的背景和使用場(chǎng)景,我們現(xiàn)在總結(jié)一下,我們需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么,其實(shí)很簡(jiǎn)單,那就是:每次查找數(shù)據(jù)時(shí)把磁盤IO次數(shù)控制在一個(gè)很小的數(shù)量級(jí),最好是常數(shù)數(shù)量級(jí)。那么我們就想到如果一個(gè)高度可控的多路搜索樹是否能滿足需求呢?就這樣,b+樹應(yīng)運(yùn)而生。
如上圖,是一顆b+樹,關(guān)于b+樹的定義可以參見B+樹,這里只說一些重點(diǎn),淺藍(lán)色的塊我們稱之為一個(gè)磁盤塊,可以看到每個(gè)磁盤塊包含幾個(gè)數(shù)據(jù)項(xiàng)(深藍(lán)色所示)和指針(黃色所示),如磁盤塊1包含數(shù)據(jù)項(xiàng)17和35,包含指針P1、P2、P3,P1表示小于17的磁盤塊,P2表示在17和35之間的磁盤塊,P3表示大于35的磁盤塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù),只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng),如17、35并不真實(shí)存在于數(shù)據(jù)表中。
如圖所示,如果要查找數(shù)據(jù)項(xiàng)29,那么首先會(huì)把磁盤塊1由磁盤加載到內(nèi)存,此時(shí)發(fā)生一次IO,在內(nèi)存中用二分查找確定29在17和35之間,鎖定磁盤塊1的P2指針,內(nèi)存時(shí)間因?yàn)榉浅6蹋ㄏ啾却疟P的IO)可以忽略不計(jì),通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存,發(fā)生第二次IO,29在26和30之間,鎖定磁盤塊3的P2指針,通過指針加載磁盤塊8到內(nèi)存,發(fā)生第三次IO,同時(shí)內(nèi)存中做二分查找找到29,結(jié)束查詢,總計(jì)三次IO。真實(shí)的情況是,3層的b+樹可以表示上百萬的數(shù)據(jù),如果上百萬的數(shù)據(jù)查找只需要三次IO,性能提高將是巨大的,如果沒有索引,每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO,那么總共需要百萬次的IO,顯然成本非常非常高。
1.通過上面的分析,我們知道IO次數(shù)取決于b+數(shù)的高度h,假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N,每個(gè)磁盤塊的數(shù)據(jù)項(xiàng)的數(shù)量是m,則有h=㏒(m+1)N,當(dāng)數(shù)據(jù)量N一定的情況下,m越大,h越?。欢鴐 = 磁盤塊的大小 / 數(shù)據(jù)項(xiàng)的大小,磁盤塊的大小也就是一個(gè)數(shù)據(jù)頁的大小,是固定的,如果數(shù)據(jù)項(xiàng)占的空間越小,數(shù)據(jù)項(xiàng)的數(shù)量越多,樹的高度越低。這就是為什么每個(gè)數(shù)據(jù)項(xiàng),即索引字段要盡量的小,比如int占4字節(jié),要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實(shí)的數(shù)據(jù)放到葉子節(jié)點(diǎn)而不是內(nèi)層節(jié)點(diǎn),一旦放到內(nèi)層節(jié)點(diǎn),磁盤塊的數(shù)據(jù)項(xiàng)會(huì)大幅度下降,導(dǎo)致樹增高。當(dāng)數(shù)據(jù)項(xiàng)等于1時(shí)將會(huì)退化成線性表。
2.當(dāng)b+樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu),比如(name,age,sex)的時(shí)候,b+數(shù)是按照從左到右的順序來建立搜索樹的,比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來檢索的時(shí)候,b+樹會(huì)優(yōu)先比較name來確定下一步的所搜方向,如果name相同再依次比較age和sex,最后得到檢索的數(shù)據(jù);但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時(shí)候,b+樹就不知道下一步該查哪個(gè)節(jié)點(diǎn),因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個(gè)比較因子,必須要先根據(jù)name來搜索才能知道下一步去哪里查詢。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時(shí),b+樹可以用name來指定搜索方向,但下一個(gè)字段age的缺失,所以只能把名字等于張三的數(shù)據(jù)都找到,然后再匹配性別是F的數(shù)據(jù)了, 這個(gè)是非常重要的性質(zhì),即索引的最左匹配特性。
關(guān)于MySQL索引原理是比較枯燥的東西,大家只需要有一個(gè)感性的認(rèn)識(shí),并不需要理解得非常透徹和深入。我們回頭來看看一開始我們說的慢查詢,了解完索引原理之后,大家是不是有什么想法呢?先總結(jié)一下索引的幾大基本原則
1.最左前綴匹配原則,非常重要的原則,mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引則都可以用到,a,b,d的順序可以任意調(diào)整。
2.=和in可以亂序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序,mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式
3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*),表示字段不重復(fù)的比例,比例越大我們掃描的記錄數(shù)越少,唯一鍵的區(qū)分度是1,而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0,那可能有人會(huì)問,這個(gè)比例有什么經(jīng)驗(yàn)值嗎?使用場(chǎng)景不同,這個(gè)值也很難確定,一般需要join的字段我們都要求是0.1以上,即平均1條掃描10條記錄
4.索引列不能參與計(jì)算,保持列“干凈”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很簡(jiǎn)單,b+樹中存的都是數(shù)據(jù)表中的字段值,但進(jìn)行檢索時(shí),需要把所有元素都應(yīng)用函數(shù)才能比較,顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);
5.盡量的擴(kuò)展索引,不要新建索引。比如表中已經(jīng)有a的索引,現(xiàn)在要加(a,b)的索引,那么只需要修改原來的索引即可
關(guān)于explain命令相信大家并不陌生,具體用法和字段含義可以參考官網(wǎng)explain-output,這里需要強(qiáng)調(diào)rows是核心指標(biāo),絕大部分rows小的語句執(zhí)行一定很快(有例外,下面會(huì)講到)。所以優(yōu)化語句基本上都是在優(yōu)化rows。
0.先運(yùn)行看看是否真的很慢,注意設(shè)置SQL_NO_CACHE
1.where條件單表查,鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起,單表每個(gè)字段分別查詢,看哪個(gè)字段的區(qū)分度最高
2.explain查看執(zhí)行計(jì)劃,是否與1預(yù)期一致(從鎖定記錄較少的表開始查詢)
3.order by limit 形式的sql語句讓排序的表優(yōu)先查
4.了解業(yè)務(wù)方使用場(chǎng)景
5.加索引時(shí)參照建索引的幾大原則
6.觀察結(jié)果,不符合預(yù)期繼續(xù)從0分析
即為全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不過目前只有 CHAR、VARCHAR ,TEXT 列上可以創(chuàng)建全文索引。
全文索引并不是和MyISAM一起誕生的,它的出現(xiàn)是為了解決WHERE name LIKE “%word%"這類針對(duì)文本的模糊查詢效率較低的問題。
BTREE索引就是一種將索引值按一定的算法,存入一個(gè)樹形的數(shù)據(jù)結(jié)構(gòu)中(二叉樹),每次查詢都是從樹的入口root開始,依次遍歷node,獲取leaf。這是MySQL里默認(rèn)和最常用的索引類型。
唯一索引:加速查詢 + 列值唯一(可以有null)
組合索引:多列值組成一個(gè)索引,專門用于組合搜索,其效率大于索引合并
CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m', 'f') not null,
key(last_name, first_name, dob)
);
其索引包含表中每一行的last_name、first_name和dob列。其結(jié)構(gòu)大致如下:
函數(shù)f( )
f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458
Slots是有序的,但是記錄不是有序的。當(dāng)你執(zhí)行
mysql> SELECT lname FROM testhash WHERE fname='Peter';
MySQL會(huì)計(jì)算’Peter’的hash值,然后通過它來查詢索引的行指針。因?yàn)閒('Peter') = 8784,MySQL會(huì)在索引中查找8784,得到指向記錄3的指針。
因?yàn)樗饕约簝H僅存儲(chǔ)很短的值,所以,索引非常緊湊。Hash值不取決于列的數(shù)據(jù)類型,一個(gè)TINYINT列的索引與一個(gè)長(zhǎng)字符串列的索引一樣大。
Hash索引有以下一些限制:
(1)由于索引僅包含hash code和記錄指針,所以,MySQL不能通過使用索引避免讀取記錄。但是訪問內(nèi)存中的記錄是非常迅速的,不會(huì)對(duì)性造成太大的影響。
(2)不能使用hash索引排序。
(3)Hash索引不支持鍵的部分匹配,因?yàn)槭峭ㄟ^整個(gè)索引值來計(jì)算hash值的。
(4)Hash索引只支持等值比較,例如使用=,IN( )和<=>。對(duì)于WHERE price>100并不能加速查詢。
2.1.3、空間(R-Tree)索引
MyISAM支持空間索引,主要用于地理空間數(shù)據(jù)類型,例如GEOMETRY。
2.1.4、全文(Full-text)索引
全文索引是MyISAM的一個(gè)特殊索引類型,主要用于全文檢索。
3、高性能的索引策略
3.1、聚簇索引(Clustered Indexes)
聚簇索引保證關(guān)鍵字的值相近的元組存儲(chǔ)的物理位置也相同(所以字符串類型不宜建立聚簇索引,特別是隨機(jī)字符串,會(huì)使得系統(tǒng)進(jìn)行大量的移動(dòng)操作),且一個(gè)表只能有一個(gè)聚簇索引。因?yàn)橛纱鎯?chǔ)引擎實(shí)現(xiàn)索引,所以,并不是所有的引擎都支持聚簇索引。目前,只有solidDB和InnoDB支持。
聚簇索引的結(jié)構(gòu)大致如下:
CREATE TABLE layout_test (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
);
假設(shè)主鍵的值位于1---10,000之間,且按隨機(jī)順序插入,然后用OPTIMIZE TABLE進(jìn)行優(yōu)化。col2隨機(jī)賦予1---100之間的值,所以會(huì)存在許多重復(fù)的值。
(1) MyISAM的數(shù)據(jù)布局
其布局十分簡(jiǎn)單,MyISAM按照插入的順序在磁盤上存儲(chǔ)數(shù)據(jù),如下:
注:MyISAM不支持聚簇索引,索引中每一個(gè)葉子節(jié)點(diǎn)僅僅包含行號(hào)(row number),且葉子節(jié)點(diǎn)按照col1的順序存儲(chǔ)。
來看看col2的索引結(jié)構(gòu):
注:聚簇索引中的每個(gè)葉子節(jié)點(diǎn)包含primary key的值,事務(wù)ID和回滾指針(rollback pointer)——用于事務(wù)和MVCC,和余下的列(如col2)。
相對(duì)于MyISAM,二級(jí)索引與聚簇索引有很大的不同。InnoDB的二級(jí)索引的葉子包含primary key的值,而不是行指針(row pointers),這減小了移動(dòng)數(shù)據(jù)或者數(shù)據(jù)頁面分裂時(shí)維護(hù)二級(jí)索引的開銷,因?yàn)镮nnoDB不需要更新索引的行指針。其結(jié)構(gòu)大致如下:
3.1.2、按primary key的順序插入行(InnoDB)
如果你用InnoDB,而且不需要特殊的聚簇索引,一個(gè)好的做法就是使用代理主鍵(surrogate key)——獨(dú)立于你的應(yīng)用中的數(shù)據(jù)。最簡(jiǎn)單的做法就是使用一個(gè)AUTO_INCREMENT的列,這會(huì)保證記錄按照順序插入,而且能提高使用primary key進(jìn)行連接的查詢的性能。應(yīng)該盡量避免隨機(jī)的聚簇主鍵,例如,字符串主鍵就是一個(gè)不好的選擇,它使得插入操作變得隨機(jī)。
3.2、覆蓋索引(Covering Indexes)
如果索引包含滿足查詢的所有數(shù)據(jù),就稱為覆蓋索引。覆蓋索引是一種非常強(qiáng)大的工具,能大大提高查詢性能。只需要讀取索引而不用讀取數(shù)據(jù)有以下一些優(yōu)點(diǎn):
(1)索引項(xiàng)通常比記錄要小,所以MySQL訪問更少的數(shù)據(jù);
(2)索引都按值的大小順序存儲(chǔ),相對(duì)于隨機(jī)訪問記錄,需要更少的I/O;
(3)大多數(shù)據(jù)引擎能更好的緩存索引。比如MyISAM只緩存索引。
(4)覆蓋索引對(duì)于InnoDB表尤其有用,因?yàn)镮nnoDB使用聚集索引組織數(shù)據(jù),如果二級(jí)索引中包含查詢所需的數(shù)據(jù),就不再需要在聚集索引中查找了。
覆蓋索引不能是任何索引,只有B-TREE索引存儲(chǔ)相應(yīng)的值。而且不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式都不同,并不是所有存儲(chǔ)引擎都支持覆蓋索引(Memory和Falcon就不支持)。
對(duì)于索引覆蓋查詢(index-covered query),使用EXPLAIN時(shí),可以在Extra一列中看到“Using index”。例如,在sakila的inventory表中,有一個(gè)組合索引(store_id,film_id),對(duì)于只需要訪問這兩列的查詢,MySQL就可以使用索引,如下:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: inventory
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 5007
Extra: Using index
1 row in set (0.17 sec)
mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G在大多數(shù)引擎中,只有當(dāng)查詢語句所訪問的列是索引的一部分時(shí),索引才會(huì)覆蓋。但是,InnoDB不限于此,InnoDB的二級(jí)索引在葉子節(jié)點(diǎn)中存儲(chǔ)了primary key的值。因此,sakila.actor表使用InnoDB,而且對(duì)于是last_name上有索引,所以,索引能覆蓋那些訪問actor_id的查詢,如:
-> FROM sakila.actor WHERE last_name = 'HOPPER'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 2
Extra: Using where; Using index
mysql> EXPLAIN SELECT actor_id, last_name
3.3、利用索引進(jìn)行排序
MySQL中,有兩種方式生成有序結(jié)果集:一是使用filesort,二是按索引順序掃描。利用索引進(jìn)行排序操作是非??斓?,而且可以利用同一索引同時(shí)進(jìn)行查找和排序操作。當(dāng)索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全部升序或者全部降序)時(shí),可以使用索引來排序。如果查詢是連接多個(gè)表,僅當(dāng)ORDER BY中的所有列都是第一個(gè)表的列時(shí)才會(huì)使用索引。其它情況都會(huì)使用filesort。
password varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY(actor_id),
KEY (name)
) ENGINE=InnoDB
insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');
name varchar(16) NOT NULL DEFAULT '',
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra:
mysql> explain select actor_id from actor order by password \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra:
mysql> explain select actor_id from actor order by name \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index
possible_keys: NULL
key: name
key_len: 18
ref: NULL
rows: 4
Extra:
mysql> explain select actor_id from actor order by actor_id \G(1)兩遍掃描算法(Two passes)
實(shí)現(xiàn)方式是先將須要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出,然后在設(shè)定的內(nèi)存(通過參數(shù)sort_buffer_size設(shè)定)中進(jìn)行排序,完成排序之后再次通過行指針信息取出所需的Columns。
注:該算法是4.1之前采用的算法,它需要兩次訪問數(shù)據(jù),尤其是第二次讀取操作會(huì)導(dǎo)致大量的隨機(jī)I/O操作。另一方面,內(nèi)存開銷較小。
(3) 一次掃描算法(single pass)
該算法一次性將所需的Columns全部取出,在內(nèi)存中排序后直接將結(jié)果輸出。
注:從 MySQL 4.1 版本開始使用該算法。它減少了I/O的次數(shù),效率較高,但是內(nèi)存開銷也較大。如果我們將并不需要的Columns也取出來,就會(huì)極大地浪費(fèi)排序過程所需要的內(nèi)存。在 MySQL 4.1 之后的版本中,可以通過設(shè)置 max_length_for_sort_data 參數(shù)來控制 MySQL 選擇第一種排序算法還是第二種。當(dāng)取出的所有大字段總大小大于 max_length_for_sort_data 的設(shè)置時(shí),MySQL 就會(huì)選擇使用第一種排序算法,反之,則會(huì)選擇第二種。為了盡可能地提高排序性能,我們自然更希望使用第二種排序算法,所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。
當(dāng)對(duì)連接操作進(jìn)行排序時(shí),如果ORDER BY僅僅引用第一個(gè)表的列,MySQL對(duì)該表進(jìn)行filesort操作,然后進(jìn)行連接處理,此時(shí),EXPLAIN輸出“Using filesort”;否則,MySQL必須將查詢的結(jié)果集生成一個(gè)臨時(shí)表,在連接完成之后進(jìn)行filesort操作,此時(shí),EXPLAIN輸出“Using temporary;Using filesort”。
3.4、索引與加鎖
索引對(duì)于InnoDB非常重要,因?yàn)樗梢宰尣樵冩i更少的元組。這點(diǎn)十分重要,因?yàn)镸ySQL 5.0中,InnoDB直到事務(wù)提交時(shí)才會(huì)解鎖。有兩個(gè)方面的原因:首先,即使InnoDB行級(jí)鎖的開銷非常高效,內(nèi)存開銷也較小,但不管怎么樣,還是存在開銷。其次,對(duì)不需要的元組的加鎖,會(huì)增加鎖的開銷,降低并發(fā)性。
InnoDB僅對(duì)需要訪問的元組加鎖,而索引能夠減少InnoDB訪問的元組數(shù)。但是,只有在存儲(chǔ)引擎層過濾掉那些不需要的數(shù)據(jù)才能達(dá)到這種目的。一旦索引不允許InnoDB那樣做(即達(dá)不到過濾的目的),MySQL服務(wù)器只能對(duì)InnoDB返回的數(shù)據(jù)進(jìn)行WHERE操作,此時(shí),已經(jīng)無法避免對(duì)那些元組加鎖了:InnoDB已經(jīng)鎖住那些元組,服務(wù)器無法解鎖了。
來看個(gè)例子:
password varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY(actor_id),
KEY (name)
) ENGINE=InnoDB
insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');
name varchar(16) NOT NULL DEFAULT '',
-> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql>
mysql> EXPLAIN SELECT actor_id FROM test.actor為了證明row 1已經(jīng)被鎖住,我們另外建一個(gè)連接,執(zhí)行如下操作:
SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE; 該查詢會(huì)被掛起,直到第一個(gè)連接的事務(wù)提交釋放鎖時(shí),才會(huì)執(zhí)行(這種行為對(duì)于基于語句的復(fù)制(statement-based replication)是必要的)。
如上所示,當(dāng)使用索引時(shí),InnoDB會(huì)鎖住它不需要的元組。更糟糕的是,如果查詢不能使用索引,MySQL會(huì)進(jìn)行全表掃描,并鎖住每一個(gè)元組,不管是否真正需要。
如對(duì)本文有疑問,請(qǐng)?zhí)峤坏浇涣髡搲?,廣大熱心網(wǎng)友會(huì)為你解答??! 點(diǎn)擊進(jìn)入論壇