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
- InnoDB只查找版本早于當前事務(wù)版本的數(shù)據(jù)行位仁,這樣可以確保事務(wù)讀取的行,要么是事務(wù)開始前已經(jīng)存在的方椎,要么是事務(wù)自身插入或者修改過的聂抢。
- 行的刪除版本號要么未定義,要么大于當前事務(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ù)依賴于鍵碼固逗。
- 范式化的好處:
- 范式化的更新操作通常比反范式化要快
- 當數(shù)據(jù)較好地范式化時浅蚪,就只有很少或者沒有重復(fù)數(shù)據(jù),所以只需要修改更少的數(shù)據(jù)烫罩。
- 范式化的表通常更小惜傲,可以更好地放在內(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 域記錄著主鍵的值榨惠,因此在使用輔助索引進行查找時,需要先查找到主鍵值盛霎,然后再到主索引中進行查找赠橙。 - 限制
- 如果不是按照索引的最左列開始查找,則無法開始索引愤炸。
- 不能跳過索引中的列期揪。
- 如果查詢中有某個列的范圍查詢,則其右邊所有的列都無法使用索引優(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)查詢方式
- 切分大查詢
一個大查詢?nèi)绻淮涡詧?zhí)行的話居兆,可能一次鎖住很多數(shù)據(jù)、占滿整個事務(wù)日志竹伸、耗盡系統(tǒng)資源泥栖、阻塞很多小的但重要的查詢 - 分解大連接查詢
對每一個表進行一次單表查詢,然后將結(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í)行語句荆虱。
- 創(chuàng)建表
create table tb (
# 非空,自增
id int not null auto_increment,
name varchar(255) null,
state int not null default 0,
primary key (`id`)
);
- 修改表
#添加列
alter table tb add col char(20);
#刪除列
alter table tb drop column col;
#刪除表
drop table tb;
- 插入
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;
- 更新
update tb set name=val where id=1;
- 刪除
delete from tb where id=2;
#清空表
truncate table tb;
- 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;
- 排序
#默認為升序ASC怀读,DESC為降序
select * from tb order by state desc;
- 通配符
# %匹配>=0個任意字符
# _匹配==1個任意字符
# 【】可以匹配集合內(nèi)的字符
# ^表示否定
#表示不以a和b開頭的任意文本
select * from tb where name like '[^ab]'诉位;
- CONCAT
#CONCAT()用于連接兩個字段,使用TRIM()可以去除首尾空格
select concat(trim(name),':',trim(state)) as nameState from tb;
- 分組
#group by出現(xiàn)在where之后愿吹,出現(xiàn)在order by之前
select col , count(*) as num from tb group by col;
- 子查詢
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;
- 連接
- 內(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;
- 組合查詢
#使用UNION來組合兩個查詢
select col from tb1 where col=1 union selct col from tb2 where col=0;