面試復(fù)習(xí)-Mysql數(shù)據(jù)庫

1.事務(wù)

事務(wù)是指滿足ACID特性的一組操作。

  • 原子性(Atomicity):事務(wù)是不可分割的最小單元,事務(wù)內(nèi)的語句疏橄,要么全部執(zhí)行成功,要么全部執(zhí)行失敗略就。
  • 一致性(Consistency):數(shù)據(jù)庫在事務(wù)執(zhí)行前后都保持一致性狀態(tài)捎迫。在一致性狀態(tài)下,所有事務(wù)對一個數(shù)據(jù)的讀取結(jié)果都是相同的表牢。
  • 隔離性(Isolation):一個事務(wù)所做的修改在最終提交以前窄绒,對其他事務(wù)是不可見的。
  • 持久性(Durability):一旦事務(wù)提交崔兴,則其所做的修改將會永遠保存在數(shù)據(jù)庫中彰导。
    MYSQL默認采用自動提交模式浊闪。

2.并發(fā)一致性

  • 丟失修改
    T1和T2兩個事務(wù)都對一個數(shù)據(jù)進行修改,T1先修改螺戳,T2后修改搁宾,則T2的修改會覆蓋T1。
  • 讀臟數(shù)據(jù)
    T1修改一個數(shù)據(jù)倔幼,T2隨后讀取這個數(shù)據(jù)盖腿。如果T1撤銷了這次修改,則T2讀到的是臟數(shù)據(jù)损同。
  • 不可重復(fù)讀
    T2讀取一個數(shù)據(jù)翩腐,T1對該數(shù)據(jù)做了修改,此時T2再次讀取這個數(shù)據(jù)會和第一次讀取的結(jié)果不同膏燃。
  • 幻影讀
    T1讀取某個范圍的數(shù)據(jù)茂卦,T2在這個范圍內(nèi)插入了新的數(shù)據(jù),T1再次讀取和第一次的結(jié)果不同组哩。

3.多版本并發(fā)控制(MVCC)

InnoDB的MVCC等龙,是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的。這兩個列伶贰,一個保存了行的創(chuàng)建時間蛛砰,一個保存了行的過期時間。存儲的不是實際的時間值黍衙,而是系統(tǒng)的版本號泥畅。MVCC只要可重復(fù)讀和提交讀兩個隔離級別下工作。
在可重復(fù)讀的隔離級別下琅翻,MVCC的操作:

  • SELECT
  1. InnoDB只查找版本早于當前事務(wù)版本的數(shù)據(jù)行位仁,這樣可以確保事務(wù)讀取的行,要么是事務(wù)開始前已經(jīng)存在的方椎,要么是事務(wù)自身插入或者修改過的聂抢。
  2. 行的刪除版本號要么未定義,要么大于當前事務(wù)的版本號辩尊,這可以保證事務(wù)讀取到的行涛浙,在事務(wù)開始前未被刪除。
  • INSERT
    為插入的每一行保存當前系統(tǒng)版本號作為行版本號摄欲。
  • DELETE
    為刪除的每一行保存當前系統(tǒng)版本號為刪除版本號轿亮。
  • UPDATE
    修改后的數(shù)據(jù)行保存當前系統(tǒng)版本號為創(chuàng)建版本號,同時保存當前系統(tǒng)版本號作為原來的行的刪除版本號胸墙。

4.事務(wù)的隔離級別

  • 未提交讀:事務(wù)中的修改我注,即使沒有提交,對其他事務(wù)也是可見的迟隅。
    問題:丟失修改但骨、讀臟數(shù)據(jù)励七、不可重復(fù)度、幻影讀
  • 提交讀:一個事務(wù)只能讀取已經(jīng)提交的事務(wù)所做的修改奔缠。
    解決:讀臟數(shù)據(jù)掠抬。
  • 可重復(fù)讀:保證在同一個事務(wù)中多次讀取同樣數(shù)據(jù)的結(jié)果是一樣的。
    解決:讀臟數(shù)據(jù)校哎、不可重復(fù)讀

為什么不能解決幻讀:
快照讀:使用MVCC讀取的是快照中的數(shù)據(jù)两波,可以減少加鎖帶來的開銷
當前讀:讀取的是最新的數(shù)據(jù),需要加鎖闷哆。
當執(zhí)行select操作時innodb默認會執(zhí)行快照讀腰奋,會記錄下這次select后的結(jié)果,之后select的時候就會返回這次快照的數(shù)據(jù)抱怔。當A事務(wù)執(zhí)行第一次select的時候劣坊,沒有任何問題,此時事務(wù)B insert 了一條數(shù)據(jù)然后commit屈留,這時候A再次執(zhí)行select局冰,那么返回的數(shù)據(jù)中會有B添加的那條數(shù)據(jù),因為快照已經(jīng)生成了绕沈。
對于會對數(shù)據(jù)修改的操作都是采取當前讀的模式锐想。

  • 可串行化:強制事務(wù)串行執(zhí)行帮寻。
    解決:讀臟數(shù)據(jù)乍狐、不可重復(fù)讀、幻影讀

5.范式

  • 第一范式:屬性不可分
  • 第二范式:每個非主屬性完全函數(shù)依賴于鍵碼
  • 第三范式:非主屬性不傳遞函數(shù)依賴于鍵碼固逗。
  • 范式化的好處:
  1. 范式化的更新操作通常比反范式化要快
  2. 當數(shù)據(jù)較好地范式化時浅蚪,就只有很少或者沒有重復(fù)數(shù)據(jù),所以只需要修改更少的數(shù)據(jù)烫罩。
  3. 范式化的表通常更小惜傲,可以更好地放在內(nèi)存里,所以執(zhí)行操作會更快贝攒。
  • 范式化的缺點:
    通常需要關(guān)聯(lián)盗誊,代價昂貴 ,也可能使一些索引策略無效隘弊。

6.索引

  • B+Tree索引
    B+Tree索引能夠加快訪問數(shù)據(jù)的速度哈踱,因為存儲引擎不再需要進行全表掃描來獲取需要的數(shù)據(jù),取而代之的是從索引的根節(jié)點開始搜索梨熙。B+Tree是有序的开镣,所以適用于排序和分組。
    B+Tree索引適用于全鍵值咽扇、鍵值范圍和鍵前綴查找邪财,其中鍵前綴查找只適用于根據(jù)最左前綴查找陕壹。B+Tree索引分為主索引和輔助索引。主索引的葉子節(jié)點 data 域記錄著完整的數(shù)據(jù)記錄树埠,這種索引方式被稱為聚簇索引糠馆。因為無法把數(shù)據(jù)行存放在兩個不同的地方,所以一個表只能有一個聚簇索引怎憋。輔助索引的葉子節(jié)點的 data 域記錄著主鍵的值榨惠,因此在使用輔助索引進行查找時,需要先查找到主鍵值盛霎,然后再到主索引中進行查找赠橙。
  • 限制
  1. 如果不是按照索引的最左列開始查找,則無法開始索引愤炸。
  2. 不能跳過索引中的列期揪。
  3. 如果查詢中有某個列的范圍查詢,則其右邊所有的列都無法使用索引優(yōu)化查找规个。
  • 哈希索引
    基于哈希表實現(xiàn)凤薛,只有精確匹配所有列的查詢才有效。只有Memory引擎顯式支持哈希索引诞仓。哈希索引無法用于排序和分組缤苫。InnoDB 存儲引擎有一個特殊的功能叫“自適應(yīng)哈希索引”,當某個索引值被使用的非常頻繁時墅拭,會在 B+Tree 索引之上再創(chuàng)建一個哈希索引活玲,這樣就讓 B+Tree 索引具有哈希索引的一些優(yōu)點,比如快速的哈希查找谍婉。
  • 空間數(shù)據(jù)索引
    MyISAM表支持空間索引舒憾,可以用作地理數(shù)據(jù)存儲。這類索引無須前綴查詢穗熬,會從所有維度索引數(shù)據(jù)镀迂。
  • 全文索引
    全文索引是一種特殊類型的索引,它查找的是文本中的關(guān)鍵詞唤蔗,而不是直接比較索引中的值探遵。
  • 索引的優(yōu)點
    1.大大減少了服務(wù)器需要掃描的數(shù)據(jù)量
    2.可以幫助服務(wù)器避免排序和臨時表
    3.可以將隨機I/O變?yōu)轫樞騃/O

7.高性能的索引策略

  • 獨立的列
    索引列不能是表達式的一部分,也不能是函數(shù)的參數(shù)妓柜。
  • 前綴索引和索引選擇性
    當需要索引很長的字符列時箱季,會讓索引變得大且慢,通沉旌纾可以索引開始的部分字符规哪,這樣可以大大節(jié)約索引空間,從而提高索引效率塌衰,但這也會降低索引的選擇性诉稍。對于BLOB\TEXT\或者很長的VARCHAR類型的列蝠嘉,必須使用前綴索引。前綴的長度需要根據(jù)索引選擇性來確定杯巨。(索引的選擇性:不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值)
  • 多列索引
    在需要使用多個列作為條件進行查詢時蚤告,使用多列索引比使用多個單列索引性能更好
  • 選擇合適的索引列順序
    讓選擇性強的索引列放在前面
  • 覆蓋索引
    如果一個索引包含所有需要查詢的字段的值,就稱為覆蓋索引服爷。如果索引的葉子節(jié)點中已經(jīng)包含要查詢的數(shù)據(jù)杜恰,那么就不再需要回表查詢了。
    優(yōu)點:
    1.索引條目通常遠小于數(shù)據(jù)行大小仍源,Mysql會極大地減少數(shù)據(jù)訪問量心褐。
    2.因為索引是按照列值順序存儲的,所以對于I/O密集型的范圍查詢會比隨機從磁盤讀取每一行數(shù)據(jù)的I/O要少得多笼踩。
    3.對于 InnoDB 引擎逗爹,若輔助索引能夠覆蓋查詢,則無需訪問主索引嚎于。
  • 使用索引掃描來做排序
  • 壓縮索引
    MyISAM使用前綴壓縮來減少索引的大小掘而,從而讓更多的索引可以放入內(nèi)存中,這在某些情況下能極大地提高性能于购。

8.查詢優(yōu)化

查詢性能低下的最基本原因是訪問的數(shù)據(jù)太多袍睡。

  • 是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)
    有些查詢請求超過實際需要的數(shù)據(jù),而這些多余的數(shù)據(jù)會被應(yīng)用程序丟棄肋僧。這會給Mysql服務(wù)器帶來額外的負擔(dān)斑胜,并增加網(wǎng)絡(luò)開銷,另外也會消耗應(yīng)用服務(wù)器的cpu和內(nèi)存資源色瘩。所以我們需要:
    1.只返回必要的列:最好不要用select * 進行查詢
    2.只返回必要的行:使用limit來限制返回的數(shù)據(jù)
    3.緩存重復(fù)查詢的數(shù)據(jù)

  • 是否在掃描額外的記錄
    可以使用下面的方法優(yōu)化:
    1.使用索引覆蓋掃描伪窖,把所有需要用到的列都放在索引中,這樣存儲引擎無須回表獲取對應(yīng)行就可以返回結(jié)果
    2.改變庫表結(jié)構(gòu)
    3.重構(gòu)查詢方式

  • 重構(gòu)查詢方式

  1. 切分大查詢
    一個大查詢?nèi)绻淮涡詧?zhí)行的話居兆,可能一次鎖住很多數(shù)據(jù)、占滿整個事務(wù)日志竹伸、耗盡系統(tǒng)資源泥栖、阻塞很多小的但重要的查詢
  2. 分解大連接查詢
    對每一個表進行一次單表查詢,然后將結(jié)果在應(yīng)用程序中進行關(guān)聯(lián)勋篓。這樣可以讓緩存的效率更高吧享,執(zhí)行單個查詢可以減少鎖的競爭,在應(yīng)用層做關(guān)聯(lián)譬嚣,可以更容易對數(shù)據(jù)庫進行拆分钢颂,更容易做到高性能和可擴展,查詢本身的效率也可能會有所提升拜银,可以減少冗余的數(shù)據(jù)殊鞭。

9.存儲引擎

  • InnoDB
    是MySQL默認的存儲引擎遭垛。實現(xiàn)了四個標準的隔離級別,默認是可重復(fù)讀操灿,通過MVCC和Next-Key Locking防止幻
    影讀锯仪。支持真正的在線熱備份,其他存儲引擎不支持在線熱備份趾盐。
    Record Locks:鎖定一個記錄上的索引庶喜,而不是記錄本身。
    Gap Locks:鎖定索引之間的間隙救鲤,而不是索引本身久窟。
    Next-key locks: InnoDB存儲引擎的一種鎖實現(xiàn),是Record Locks和Gap Locks的結(jié)合本缠。不僅鎖定索引瘸羡,也鎖定索引之間的間隙。
  • MyISAM
    設(shè)計簡單搓茬,數(shù)據(jù)以緊密格式存儲犹赖。提供了大量的特性,包括壓縮表卷仑、空間數(shù)據(jù)索引等峻村。不支持事務(wù),不支持行級鎖锡凝,只支持表級鎖粘昨,可以手工或者自動執(zhí)行檢查和修復(fù)操作,但可能導(dǎo)致一些數(shù)據(jù)丟失窜锯,而且修復(fù)操作非常慢张肾。

10.主從復(fù)制

  • binlog線程:負責(zé)將主服務(wù)器上的數(shù)據(jù)更改寫入到二進制日志中
  • I/O線程:負責(zé)從主服務(wù)器上讀取二進制日志,并寫入從服務(wù)器的中繼日志
  • SQL線程:負責(zé)讀取中繼日志锚扎,解析出主服務(wù)器已經(jīng)執(zhí)行的數(shù)據(jù)更改并在從服務(wù)器中重放

11.SQL

一條SQL語句的執(zhí)行過程:

  • 連接器查看當前用戶權(quán)限吞瞪,若擁有權(quán)限,建立連接驾孔。如果客戶端太長時間沒有動靜芍秆,連接器會自動斷開連接
  • 分析器分析SQL語句,包括語法分析(語法是否正確)翠勉,詞法分析(識別SQL語句)妖啥。
  • 優(yōu)化器優(yōu)化查詢,決定使用的索引等对碌。
  • 執(zhí)行器開始執(zhí)行語句荆虱。
  1. 創(chuàng)建表
create table tb (
  # 非空,自增
  id int not null auto_increment,
  name varchar(255) null,
  state int not null default 0,
  primary key (`id`)
);
  1. 修改表
#添加列
alter table tb add col char(20);

#刪除列
alter table tb drop column col;

#刪除表
drop table tb;
  1. 插入
insert into tb(name,state) values(val1,val2);

#插入檢索出來的數(shù)據(jù)
insert into tb(name,state) select name,state from tb1;

#將一個表的內(nèi)容插入到一個新表
create table tb2 as select * from tb;
  1. 更新
update tb set name=val where id=1;
  1. 刪除
delete from tb where id=2;

#清空表
truncate table tb;
  1. DISTINCT/LIMIT
#相同值只會出現(xiàn)一次
select distinct name,state from tb;

#限制返回的行數(shù)
select name,state from tb limit 5;

#從第2行開始返回,返回的行數(shù)為5
select name,state from tb limit 1,5;
  1. 排序
#默認為升序ASC怀读,DESC為降序
select * from tb order by state desc;
  1. 通配符
# %匹配>=0個任意字符
# _匹配==1個任意字符
# 【】可以匹配集合內(nèi)的字符
# ^表示否定

#表示不以a和b開頭的任意文本
select * from tb where name like '[^ab]'诉位;
  1. CONCAT
#CONCAT()用于連接兩個字段,使用TRIM()可以去除首尾空格
select concat(trim(name),':',trim(state)) as nameState from tb;
  1. 分組
#group by出現(xiàn)在where之后愿吹,出現(xiàn)在order by之前
select col , count(*) as num from tb group by col;
  1. 子查詢
select * from tb where name in ( select name from tb2 );

select name, (select count(*) from tb2 where tb2.id = tb.id) as num from tb order by name;
  1. 連接
  • 內(nèi)連接
select a.val , b.val from t1 as a inner join t2 as b on a.key = b.key;
  • 自連接:內(nèi)連接的一種不从,只是連接的表是自身
select a.name from tb as a inner join tb as b on a.state=b.state and b.name="lug";
  • 自然連接:將自動連接所有的同名列
select a.val, b.val from tb1 as a natural join tb2 as b;
  • 外連接:保留了沒有關(guān)聯(lián)的行。分為左外連接犁跪,右外連接和全外連接椿息。
select a.val, b.val from tb1 left join tb2 on tb1.id=tb2.id;
  1. 組合查詢
#使用UNION來組合兩個查詢
select col from tb1 where col=1 union selct col from tb2 where col=0;
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市坷衍,隨后出現(xiàn)的幾起案子寝优,更是在濱河造成了極大的恐慌,老刑警劉巖枫耳,帶你破解...
    沈念sama閱讀 223,002評論 6 519
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件乏矾,死亡現(xiàn)場離奇詭異,居然都是意外死亡迁杨,警方通過查閱死者的電腦和手機钻心,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,357評論 3 400
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來铅协,“玉大人捷沸,你說我怎么就攤上這事『罚” “怎么了痒给?”我有些...
    開封第一講書人閱讀 169,787評論 0 365
  • 文/不壞的土叔 我叫張陵,是天一觀的道長骏全。 經(jīng)常有香客問我苍柏,道長,這世上最難降的妖魔是什么姜贡? 我笑而不...
    開封第一講書人閱讀 60,237評論 1 300
  • 正文 為了忘掉前任试吁,我火速辦了婚禮,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘。我一直安慰自己遣铝,他們只是感情好冻河,可當我...
    茶點故事閱讀 69,237評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著棒动,像睡著了一般糙申。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上船惨,一...
    開封第一講書人閱讀 52,821評論 1 314
  • 那天柜裸,我揣著相機與錄音缕陕,去河邊找鬼。 笑死疙挺,一個胖子當著我的面吹牛扛邑,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播铐然,決...
    沈念sama閱讀 41,236評論 3 424
  • 文/蒼蘭香墨 我猛地睜開眼蔬崩,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了搀暑?” 一聲冷哼從身側(cè)響起沥阳,我...
    開封第一講書人閱讀 40,196評論 0 277
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎自点,沒想到半個月后桐罕,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,716評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡桂敛,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,794評論 3 343
  • 正文 我和宋清朗相戀三年功炮,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片术唬。...
    茶點故事閱讀 40,928評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡薪伏,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出碴开,到底是詐尸還是另有隱情毅该,我是刑警寧澤,帶...
    沈念sama閱讀 36,583評論 5 351
  • 正文 年R本政府宣布潦牛,位于F島的核電站眶掌,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏巴碗。R本人自食惡果不足惜朴爬,卻給世界環(huán)境...
    茶點故事閱讀 42,264評論 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望橡淆。 院中可真熱鬧召噩,春花似錦、人聲如沸逸爵。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,755評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽师倔。三九已至构韵,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背疲恢。 一陣腳步聲響...
    開封第一講書人閱讀 33,869評論 1 274
  • 我被黑心中介騙來泰國打工凶朗, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人显拳。 一個月前我還...
    沈念sama閱讀 49,378評論 3 379
  • 正文 我出身青樓棚愤,卻偏偏與公主長得像,于是被迫代替她去往敵國和親杂数。 傳聞我的和親對象是個殘疾皇子宛畦,可洞房花燭夜當晚...
    茶點故事閱讀 45,937評論 2 361