摘要：實(shí)現(xiàn)事務(wù)的原子性，要支持回滾操作，在某個(gè)操作失敗后，回滾到事務(wù)執(zhí)行之前的狀態(tài)。一致性事務(wù)使得系統(tǒng)從一個(gè)一致的狀態(tài)轉(zhuǎn)換到另一個(gè)一致狀態(tài)。

本文作者 TomorrowWu,原創(chuàng)文章,轉(zhuǎn)載注明出處,博客地址 https://segmentfault.com/u/to... 第一時(shí)間看后續(xù)精彩文章。覺得好的話,順手分享到朋友圈吧,感謝支持。

筆者最近在準(zhǔn)備面試,覺得學(xué)習(xí)最好的方式就是把知道的東西通過博客寫出來,一方面考察自己對某個(gè)知識點(diǎn)的理解,一方面督促自己查閱更多資料深入學(xué)習(xí)

我會總結(jié)出我在網(wǎng)上看到的面試題以及相應(yīng)的答案,并且盡可能的講原理,有錯(cuò)誤的地方希望有大神基給予指正,讀者如果有好的題目,也可以評論中提出,我將后續(xù)更新上去,謝謝

當(dāng)然學(xué)習(xí)MySQL不僅僅是看一些面試題,最好還是看一些相關(guān)的書籍,比如比較好的就是<<高性能MySQL>>中文版,很詳細(xì),很厚,還未看完,還有簡朝陽先生的<>一書,當(dāng)初泛讀了一遍,等待深入研究中

1.常用的方法是explainSQL查看執(zhí)行計(jì)劃,根據(jù)查詢計(jì)劃知道是否使用了索引,以及是否進(jìn)行全表掃描,以及查詢的順序等等全過程,依次我們可以建立適當(dāng)?shù)乃饕瓦B接查詢調(diào)優(yōu)、SQL語句拆分等

2.開啟慢查詢,記錄執(zhí)行時(shí)間長的SQL語句

通常會在where、join on、order by等使用到的字段上加上索引

避免查詢時(shí)判斷null,否則可能會導(dǎo)致全表掃描,無法使用索引;

解決方案:
在創(chuàng)建表時(shí),字段盡量指定默認(rèn)值,或者設(shè)置not null,不要給數(shù)據(jù)庫留null

避免使用or來連接查詢條件,如果一個(gè)字段有索引，一個(gè)字段沒有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,可以改用union或union all

優(yōu)化案例

select id from t where num=10 or Name = "admin"
//優(yōu)化后
select id from t where num = 10 union all select id from t where Name = "admin"

避免like查詢,否則可能導(dǎo)致全表掃描,可以考慮使用全文索引

前置模糊索引 like "%abc" 勢必會進(jìn)行全表掃描; 2. like "abc%"依舊有可能進(jìn)行全表掃描,當(dāng)部分DBMD中返回結(jié)果超過該表的80%時(shí),就失去使用索引的意義數(shù)據(jù)庫會自動改用全表掃描.(例如: where mobile like "1%")

應(yīng)盡量避免在 where 子句中使用 != 或<>操作符，否則導(dǎo)致全表掃描

不使用select *,只查詢必須字段,避免加載無用數(shù)據(jù)

能用union all的時(shí)候就不用union,union過濾重復(fù)數(shù)據(jù)要耗費(fèi)更多的CPU資源

where子句中使用變量參數(shù),導(dǎo)致全表掃描

原理:SQL只有在運(yùn)行時(shí)才會解析局部變量,優(yōu)化程序必須在編譯時(shí)選擇訪問計(jì)劃,但是編譯時(shí)變量值還未知,因此無法作為索引選擇的輸入項(xiàng)

//全表掃描
select id from t where num = @num
//強(qiáng)制使用索引
select id from t with(index(索引名)) where num = @num

避免在where子句中對字段進(jìn)行表達(dá)式操作,導(dǎo)致放棄索引進(jìn)行全表掃描

select id from t where num/2 = 100
//修改為
select id from t where num = 100*2

避免在where子句中對字段進(jìn)行函數(shù)操作,導(dǎo)致放棄索引進(jìn)行全表掃描

select id from t where substring(name,1,3) = ’abc’
//修改后
select id from t where name like "abc%"

索引并不是越多越好,索引可以提高查詢效率,但插入和修改時(shí)可能會重建索引;一個(gè)表的索引數(shù)量最好不要超過6個(gè)

字段類型盡量使用數(shù)字,不要設(shè)計(jì)成字符串,會降低性能,并增加存儲開銷

引擎在逐個(gè)比較字符串中每一個(gè)字符,對于數(shù)字只需要比較一次

避免使用游標(biāo),效率很差,如果游標(biāo)操作的數(shù)據(jù)超過1萬行,就應(yīng)該考慮改寫

盡量避免大事務(wù)操作,提高系統(tǒng)并發(fā)能力

拆分大的 DELETE 或 INSERT 語句。因?yàn)檫@兩個(gè)操作是會鎖表的，別的操作都進(jìn)不來了，有時(shí)候用for循環(huán)來一個(gè)個(gè)執(zhí)行這些操作。

獲取唯一行時(shí)使用limit 1

引擎在找到第一個(gè)記錄后就停止掃描記錄,而不是遍歷整個(gè)表或索引

優(yōu)先使用enum

少量state使用enum,方便遷移數(shù)據(jù)庫以及維護(hù),使用int和varchar

表字段設(shè)置為固定長度(靜態(tài))的表更快

使用procedure analyse()獲取建議,優(yōu)化表結(jié)構(gòu)

使用orm
優(yōu)點(diǎn):代碼量少,延遲加載,多個(gè)查詢批處理到事務(wù)中,操作速度比單個(gè)查詢快很多

缺點(diǎn):級聯(lián)查詢

InnoDB,MyISAM,Archive,Blackhole,CSV,Federated,Memory,Merge,NDB集群引擎等,還有一些第三方存儲引擎

not null的字段不能插入null,只能插入"空值"

空值是不占用空間的,null其實(shí)不是空值,而是要占用空間

null會參與字段比較,對效率有一部分影響

對表的索引,不會存儲null值,如果索引的字段可以為null,索引的效率會下降很多

例如電話號碼等字段,空值表示不知道對方的手機(jī)號碼,空字符表示后來取消了這個(gè)號碼,等等

簡單來說,避免數(shù)據(jù)量大時(shí)掃描過多的記錄
解決方案:

優(yōu)化案例:

select * from news order by id desc limit 1000000,10
耗時(shí)7.28秒

//方案1 0.365秒
select * from news 
where id >  (select id from news order by id desc  limit 1000000, 1)
order by id desc 
limit 0,10

方案2 ,適合id連續(xù)的系統(tǒng),速度極快
select * from news 
where id  between 1000000 and 1000010 
order by id desc

案例:

SELECT
  *
FROM
  table_A USE INDEX (index_A)
WHERE
  A = xxx
  AND B = xxx
  AND C IN (xxx)
ORDER BY
  D DESC
LIMIT
  33380, 11

KEY `index_A` (`A`,`B`,`D`,`C`)

// 延遲加載后:
1.
SELECT
  table_A.id
FROM
  table_A USE INDEX (index_A)
WHERE
  A = xxx
  AND B = xxx
  AND C IN (xxx)
ORDER BY
  D DESC
LIMIT
  33380, 11;

2.
Select ＊ from table_A where id in (ids)

慢查詢?nèi)罩?/p> 什么是ACID(原子性,一致性,隔離性,持久性)原則

原子性

一個(gè)事務(wù)包含多個(gè)操作，這些操作要么全部執(zhí)行，要么全都不執(zhí)行。實(shí)現(xiàn)事務(wù)的原子性，要支持回滾操作，在某個(gè)操作失敗后，回滾到事務(wù)執(zhí)行之前的狀態(tài)。

一致性

事務(wù)使得系統(tǒng)從一個(gè)一致的狀態(tài)轉(zhuǎn)換到另一個(gè)一致狀態(tài)。事務(wù)的一致性決定了一個(gè)系統(tǒng)設(shè)計(jì)和實(shí)現(xiàn)的復(fù)雜度

隔離性

并發(fā)事務(wù)之間互相影響的程度，比如一個(gè)事務(wù)會不會讀取到另一個(gè)未提交的事務(wù)修改的數(shù)據(jù)

持久性
事務(wù)提交后，對系統(tǒng)的影響是永久的

最常見的一個(gè)性能問題

舉個(gè)例子，我們數(shù)據(jù)庫中有兩張表，一個(gè)是Customers，一個(gè)是Orders。Orders中含有一個(gè)外鍵customer_id，指向了Customers的主鍵id,想要得到所有Customer以及其分別對應(yīng)的Order

// N+1方式
SELECT * FROM Customers;
SELECT * FROM Orders WHERE Orders.customer_id = #{customer.id}

//left join
 SELECT * FROM Customers LEFT JOIN Orders on Customers.id = Orders.customer_id;

前者的讀寫責(zé)任分離,責(zé)任是根據(jù)具體業(yè)務(wù)來的,讀不僅僅是指的數(shù)據(jù)庫意義上的讀操作,而是根據(jù)業(yè)務(wù)需求,為復(fù)雜業(yè)務(wù)時(shí)的讀操作,專門建立數(shù)據(jù)庫以供直接讀取去展示界面;后者讀寫分離是針對數(shù)據(jù)庫層次的,主數(shù)據(jù)庫寫,從數(shù)據(jù)庫讀