1佃迄、幾種主要的數(shù)據(jù)庫(kù)引擎的區(qū)別
MyISAM讀取操作速度很快派桩,而且不占用大量的內(nèi)存和存儲(chǔ)資源构诚,但是不支持事務(wù)和外鍵,也不支持行級(jí)鎖铆惑,寫操作會(huì)鎖定整個(gè)表范嘱,效率便低了一些,最大的缺陷是崩潰后無法安全恢復(fù)员魏,適合對(duì)事務(wù)完整性沒有要求并以訪問為主的應(yīng)用
InnoDB是一個(gè)事務(wù)性引擎丑蛤,支持行級(jí)鎖和外鍵,提供了具有提交撕阎、回滾和崩潰恢復(fù)能力的事務(wù)安全存儲(chǔ)引擎受裹,而且在READ COMMITTED和REPEATABLE READ隔離級(jí)別下支持MVCC,即多版本并發(fā)控制虏束,大大提高了事務(wù)的并發(fā)能力名斟。
MVCC:
MVCC,即多版本并發(fā)控制魄眉,在概念上砰盐,通過在行記錄上隱世的增加兩個(gè)版本號(hào)字段,一個(gè)創(chuàng)建版本號(hào)和一個(gè)刪除版本號(hào)坑律。使用MVCC可以提高減少數(shù)據(jù)的加鎖操作岩梳,大大提高了事務(wù)的并發(fā)能力囊骤。
2、事務(wù)的特征
原子性:事務(wù)是一個(gè)完整的操作冀值,要么全部執(zhí)行也物,要么都不執(zhí)行
一致性:當(dāng)事務(wù)完成時(shí),數(shù)據(jù)必須處于一致狀態(tài)
隔離性:對(duì)數(shù)據(jù)修改的所有并發(fā)事務(wù)都是彼此隔離的
持久性:事務(wù)一旦提交列疗,對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的修改便是永久的
3滑蚯、MySQL的隔離級(jí)別
串行化(Serializable):可避免臟讀、幻讀和不可重復(fù)讀
可重復(fù)讀(Repeatable read):可避免臟讀和不可重復(fù)讀
讀已提交(Read committed):可避免臟讀
讀未提交(read uncommitted):最低級(jí)別抵栈,任何情況都無法保證
臟讀
讀取了另一個(gè)未提交事務(wù)中的數(shù)據(jù)
不可重復(fù)讀和幻讀
都是讀取了另一個(gè)已提交事務(wù)中的數(shù)據(jù)告材,區(qū)別在于不可重復(fù)讀重點(diǎn)在于更新和刪除,幻讀重點(diǎn)在于插入
在可重復(fù)讀中古劲,第一次讀取數(shù)據(jù)時(shí)斥赋,就將這些數(shù)據(jù)加索,其他事務(wù)就無法修改這些數(shù)據(jù)产艾,就實(shí)現(xiàn)可重復(fù)讀了疤剑。但是無法鎖住插入的數(shù)據(jù),這個(gè)事務(wù)就發(fā)現(xiàn)莫名奇妙多了一個(gè)數(shù)據(jù)闷堡,這就是幻讀隘膘。
4、鎖的級(jí)別
共享鎖:可以并發(fā)讀取數(shù)據(jù)杠览,但任何事務(wù)都不能獲取排它鎖弯菊,直到共享鎖釋放
排它鎖:對(duì)數(shù)據(jù)A加上排它鎖后,其他事務(wù)不能再對(duì)A加上任何類型的鎖倦零,獲取排它鎖的事務(wù)可以對(duì)數(shù)據(jù)進(jìn)行讀取和修改操作
5、鎖的粒度
行級(jí)鎖:粒度最小吨悍,并發(fā)度最高
頁級(jí)鎖:一次鎖定一頁扫茅,
表級(jí)鎖:粒度大,并發(fā)度低
6育瓜、悲觀鎖和樂觀鎖
悲觀鎖:每次獲取數(shù)據(jù)的時(shí)候都會(huì)認(rèn)為別人會(huì)修改數(shù)據(jù)葫隙,所以每次拿數(shù)據(jù)時(shí)都加上鎖
樂觀鎖:每次獲取數(shù)據(jù)時(shí)都會(huì)認(rèn)為別人不會(huì)修改數(shù)據(jù),只是在提交數(shù)據(jù)更新的時(shí)候才會(huì)去檢查在此期間有沒有其他人去更新這個(gè)數(shù)據(jù)躏仇,如果發(fā)生沖突恋脚,則返回用戶錯(cuò)誤數(shù)據(jù),讓用戶決定如何處理焰手,一般實(shí)現(xiàn)樂觀鎖的方式就是記錄數(shù)據(jù)版本號(hào)
7糟描、explain詳解
id:標(biāo)識(shí)select查詢的序列號(hào),標(biāo)識(shí)查詢select子句的執(zhí)行順序
select_type:查詢類型:simple(簡(jiǎn)單子查詢)书妻、primary(包含union或子查詢船响,最外層的部分標(biāo)記為primary)、subquery(查詢中的子查詢)、derived(派生表见间,從子查詢派生出來的聊闯,位于from中的紫釵性能)
table:表示正在訪問的表名,也可能是臨時(shí)表或者union合并結(jié)果集
derivedN米诉,表示使用了id為N的查詢產(chǎn)生的衍生表
union n1,n2菱蔬,n1,n2表示參與union的id
type:類型
all:全表掃描
index:全索引掃描
range:index的范圍限制
index_subquery:在某些in查詢使用此種類型,查詢的是非唯一索引
unique_subquery:在某些in查詢使用此種類型
index_merge:索引合并優(yōu)化被使用了
ref_or_null:MySQL必須在初次查找的結(jié)果找到null條目史侣,然后進(jìn)行二次查找
ref:使用了非唯一性索引進(jìn)行查找
eq_ref:唯一性索引
const:主鍵出現(xiàn)再where后面作為條件查詢拴泌,MySQL優(yōu)化器會(huì)把此次查詢優(yōu)化轉(zhuǎn)化為一個(gè)常量
system:表中只有一行
possible_keys:顯示可能用到的索引
keys:MySQL使用了哪個(gè)索引
rows:大致估算出找出所需的記錄需要讀取的行數(shù)
extra:包含了一些十分重要的額外信息
using filesort:說明MySQL無法利用索引進(jìn)行排序,只能用排序算法進(jìn)行排序抵窒,這會(huì)額外消耗資源弛针,說明這個(gè)SQL需要優(yōu)化,需要重新建立索引等
using temporay:建立了臨時(shí)表保存中間結(jié)果李皇,查詢完之后又把臨時(shí)表刪除削茁,出現(xiàn)這個(gè)說明sql需要優(yōu)化
using index:表明當(dāng)前查詢時(shí)覆蓋索引的,直接從索引中讀取數(shù)據(jù)掉房,而不用訪問數(shù)據(jù)表
using where:使用where進(jìn)行條件過濾
using join buffer:使用了連接緩存
impossible where:where語句總是false
distinct:優(yōu)化distinct操作茧跋,在找到第一匹配的元組后即停止找同樣值的動(dòng)作
重點(diǎn):
type、possible_keys卓囚、key瘾杭、rows、extra
8哪亿、索引原理
什么是索引
索引是一種數(shù)據(jù)結(jié)構(gòu)粥烁,幫助我們快速檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)。
索引的優(yōu)勢(shì)
加快檢索速度蝇棉,減少I/O次數(shù)
索引的劣勢(shì)
索引會(huì)占用存儲(chǔ)空間讨阻,構(gòu)建索引會(huì)降低數(shù)據(jù)表修改的效率
索引類型
從存儲(chǔ)結(jié)構(gòu)上來劃分:BTree索引(B-Tree或B+Tree索引),Hash索引篡殷,full-index全文索引钝吮,R-Tree索引。這里所描述的是索引存儲(chǔ)時(shí)保存的形式
從應(yīng)用層次來分:普通索引板辽,唯一索引奇瘦,復(fù)合索引
根據(jù)中數(shù)據(jù)的物理順序與鍵值的邏輯(索引)順序關(guān)系:聚集索引,非聚集索引
聚簇索引:葉子節(jié)點(diǎn)保存的就是數(shù)據(jù)節(jié)點(diǎn)
非聚簇索引:葉子節(jié)點(diǎn)保存的是指向?qū)?yīng)數(shù)據(jù)塊的指針
MyISAM和InnoDB存儲(chǔ)引擎下索引有什么區(qū)別
MyISAM引擎采用B+tree作為索引結(jié)構(gòu)劲弦,索引文件和數(shù)據(jù)文件是分離的耳标,葉節(jié)點(diǎn)的data域保存的是數(shù)據(jù)記錄的地址。
主鍵索引和輔助索引在結(jié)構(gòu)上沒有什么區(qū)別邑跪,只是主鍵索引要求key必須是唯一的麻捻,而輔助索引則可以重復(fù)
InnoDB引擎也采用B+tree作為索引結(jié)構(gòu)纲仍,主鍵索引也就是聚簇索引中,葉節(jié)點(diǎn)的data域保存了完整的數(shù)據(jù)記錄贸毕;而輔助索引的葉節(jié)點(diǎn)的data域保存了主鍵
9郑叠、如何優(yōu)化MySQL
MySQL優(yōu)化大致可以分為三部分:索引的優(yōu)化、SQL語句優(yōu)化和表的優(yōu)化
索引優(yōu)化可以遵循以下幾個(gè)原則:
聯(lián)合索引最左前綴匹配原則
盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)(因?yàn)樽侄卧叫∶鞴鳎豁摯鎯?chǔ)的數(shù)據(jù)量越大乡革,IO性能也就越好)
order by 有多個(gè)列排序的,應(yīng)該建立聯(lián)合索引
對(duì)于頻繁的查詢優(yōu)先考慮使用覆蓋索引
前導(dǎo)模糊查詢不會(huì)使用索引摊腋,比如說Like '%aaa%'這種
負(fù)向條件不會(huì)使用索引沸版,如!=,<>兴蒸,not like视粮,not in,not exists
索引應(yīng)該建立在區(qū)分度比較高的字段上? 一般區(qū)分度在80%以上的時(shí)候就可以建立索引橙凳,區(qū)分度可以使用 count(distinct(列名))/count(*)
對(duì)于where子句中經(jīng)常使用的列蕾殴,最好設(shè)置索引
SQL語句優(yōu)化,可以通過explain查看SQL的執(zhí)行計(jì)劃岛啸,優(yōu)化語句原則可以有:
在where和order by涉及的列上建立合適的索引钓觉,避免全表掃描
任何查詢都不要使用select * ,而是用具體的字段列表代替
多表連接時(shí)坚踩,盡量小表驅(qū)動(dòng)大表荡灾,即小表join大表
用exists代替in
盡量避免在where字句中對(duì)字段進(jìn)行函數(shù)操作
數(shù)據(jù)庫(kù)表優(yōu)化
表字段盡可能用not null
字段長(zhǎng)度固定表查詢會(huì)更快
將數(shù)據(jù)庫(kù)大表按照時(shí)間或者一些標(biāo)志拆分成小表
水平拆分:將記錄散列到不同的表中,每次從分表查詢
垂直拆分:將表中的大字段單獨(dú)拆分到另一張表瞬铸,形成一對(duì)一的關(guān)系
10批幌、為什么索引結(jié)構(gòu)默認(rèn)使用B+Tree,而不是Hash嗓节,二叉樹荧缘,紅黑樹?
B+tree是一種多路平衡查詢樹赦政,節(jié)點(diǎn)是天然有序的胜宇,非葉子節(jié)點(diǎn)包含多個(gè)元素窝稿,不保存數(shù)據(jù)美莫,只用來索引桐汤,葉子節(jié)點(diǎn)包含完整數(shù)據(jù)和帶有指向下一個(gè)節(jié)點(diǎn)的指針,形成一個(gè)有序鏈表掰派,有助于范圍和順序查找。因?yàn)榉侨~子節(jié)點(diǎn)不保存數(shù)據(jù)左痢,所以同樣大小的磁盤頁可以容納更多的元素靡羡,同樣能數(shù)據(jù)量的情況下系洛,B+tree相比B-tree高度更低,因此查詢時(shí)IO會(huì)更少
B-tree不管葉子節(jié)點(diǎn)還是非葉子節(jié)點(diǎn)略步,都會(huì)保存數(shù)據(jù)描扯,這樣導(dǎo)致在非葉子節(jié)點(diǎn)中能保存的指針數(shù)量變少(有些資料也稱為扇出),指針少的情況下要保存大量數(shù)據(jù)趟薄,只能增加樹的高度绽诚,導(dǎo)致IO操作變多,查詢性能變低杭煎;
Hash索引底層是基于哈希表恩够,就是以key-value存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu),多個(gè)數(shù)據(jù)在存儲(chǔ)關(guān)系上是沒有任何順序關(guān)系的羡铲。只適合等值查詢蜂桶,不適合范圍查詢,而且也無法利用索引完成排序也切,不支持聯(lián)合索引的最左匹配原則扑媚,如果有大量重復(fù)鍵值的情況下,哈希索引效率會(huì)很低贾费,因?yàn)榇嬖诠E鲎病?/p>
二叉樹:樹的高度不均勻钦购,不能自平衡,查找效率跟數(shù)據(jù)有關(guān)(樹的高度)褂萧,并且IO代價(jià)高押桃。
紅黑樹:樹的高度隨著數(shù)據(jù)量增加而增加,IO代價(jià)高导犹。
11唱凯、主鍵索引查詢只會(huì)查一次,而非主鍵索引需要回表查詢多次谎痢。是所有情況都是這樣的嗎磕昼?非主鍵索引一定會(huì)查詢多次嗎?
如果我們使用了覆蓋索引节猿,也就是當(dāng)一條查詢語句符合覆蓋索引條件時(shí)票从,MySQL只需要通過查詢索引就可以返回需要的數(shù)據(jù),避免了查到索引后再返回表操作滨嘱,減少I/O提高效率
12峰鄙、聯(lián)合索引最左前綴匹配原則
在創(chuàng)建多列的聯(lián)合索引下,MySQL索引查詢會(huì)遵循最左前綴匹配原則太雨,即檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配吟榴。例如,當(dāng)創(chuàng)建了一個(gè)聯(lián)合索引(key1,key2,key3)相當(dāng)于創(chuàng)建了(key1)囊扳,(key1,key2)吩翻,(key1,key2,key3)三個(gè)索引
13兜看、MySQL5.6中對(duì)索引做了哪些優(yōu)化
索引下推技術(shù)
例如有索引(key1,key2),SQL語句中where key1 = 'XXX' and key2 like '%XXX%'
如果沒有使用索引下推技術(shù)狭瞎,MySQL會(huì)通過key1 = 'XXX'從存儲(chǔ)引擎返回對(duì)應(yīng)的數(shù)據(jù)至MySQL服務(wù)端细移,服務(wù)端再基于key2 like 判斷是否符合條件。
如果使用了索引下推技術(shù)熊锭,MySQL首先返回key1='XXX'的索引葫哗,再根據(jù)key2 like 判斷索引是否符合條件,如果符合則通過索引定位數(shù)據(jù)球涛,如果不符合則直接reject掉劣针。有了索引下推優(yōu)化,可以在有l(wèi)ike條件查詢的情況下亿扁,減少回表次數(shù)捺典。
14、那什么情況下會(huì)發(fā)生明明創(chuàng)建了索引从祝,但是執(zhí)行的時(shí)候并沒有通過索引呢
在一條單表查詢語句真正執(zhí)行之前襟己,MySQL的查詢優(yōu)化器會(huì)找出執(zhí)行該語句所有可能使用的方案,對(duì)比之后找出成本最低的方案牍陌。
這個(gè)成本最低的方案就是所謂的執(zhí)行計(jì)劃擎浴。優(yōu)化過程大致如下:
1、根據(jù)搜索條件毒涧,找出所有可能使用的索引?
2贮预、計(jì)算全表掃描的代價(jià)?
3、計(jì)算使用不同索引執(zhí)行查詢的代價(jià)?
4契讲、對(duì)比各種執(zhí)行方案的代價(jià)仿吞,找出成本最低的那一個(gè)
15、紅黑樹的五個(gè)性質(zhì)
節(jié)點(diǎn)是黑色或者紅色
根節(jié)點(diǎn)是黑色
所有的葉子節(jié)點(diǎn)都是黑色捡偏,葉子是nil節(jié)點(diǎn)
每個(gè)紅色節(jié)點(diǎn)唤冈,必須有兩個(gè)黑色的子節(jié)點(diǎn)
從任一節(jié)點(diǎn)到其每個(gè)葉子的所有簡(jiǎn)單路徑都包含相同數(shù)目的黑色節(jié)點(diǎn)
16、MySQL一條查詢語句是如何執(zhí)行的
MySQL的基本架構(gòu)分為兩個(gè)層次:server層和存儲(chǔ)引擎層
其中server層包括:連接器银伟、查詢緩存你虹、分析器、優(yōu)化器彤避、執(zhí)行器
存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和讀取
連接器負(fù)責(zé)與客戶端建立鏈接傅物、權(quán)限驗(yàn)證和管理鏈接
當(dāng)向server發(fā)送sql請(qǐng)求時(shí),如果開啟了查詢緩存忠藤,則去查詢緩存中看是否有挟伙,如果有則直接返回楼雹,否則再走分析器進(jìn)行詞法分析和語法分析
分析器工作完成后模孩,將語句傳遞到優(yōu)化器進(jìn)行優(yōu)化尖阔,確定優(yōu)化執(zhí)行方案,優(yōu)化器會(huì)根據(jù)自己的優(yōu)化算法選擇效率最好的一個(gè)方案
之后就會(huì)交給執(zhí)行器進(jìn)行執(zhí)行榨咐,執(zhí)行之前還會(huì)進(jìn)行權(quán)限驗(yàn)證介却,然后調(diào)用數(shù)據(jù)庫(kù)存儲(chǔ)引擎接口,返回引擎執(zhí)行結(jié)果