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`)
);

2. 修改表

#添加列
alter table tb add col char(20);

#刪除列
alter table tb drop column col;

#刪除表
drop table tb;

3. 插入

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;

4. 更新

update tb set name=val where id=1;

5. 刪除

delete from tb where id=2;

#清空表
truncate table tb;

6. 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;

7. 排序

#默認為升序ASC怀读，DESC為降序
select * from tb order by state desc;

8. 通配符

# %匹配>=0個任意字符
# _匹配==1個任意字符
# 【】可以匹配集合內(nèi)的字符
# ^表示否定

#表示不以a和b開頭的任意文本
select * from tb where name like '[^ab]'诉位；

9. CONCAT

#CONCAT()用于連接兩個字段，使用TRIM()可以去除首尾空格
select concat(trim(name),':',trim(state)) as nameState from tb;

10. 分組

#group by出現(xiàn)在where之后愿吹，出現(xiàn)在order by之前
select col , count(*) as num from tb group by col;

11. 子查詢

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;

12. 連接

• 內(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;

13. 組合查詢

#使用UNION來組合兩個查詢
select col from tb1 where col=1 union selct col from tb2 where col=0;