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

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

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

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

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

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

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

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

避免查詢時判斷null,否則可能會導致全表掃描,無法使用索引;

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

避免使用or來連接查詢條件,如果一個字段有索引，一個字段沒有索引，將導致引擎放棄使用索引而進行全表掃描,可以改用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查詢,否則可能導致全表掃描,可以考慮使用全文索引

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

應盡量避免在 where 子句中使用 != 或<>操作符，否則導致全表掃描

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

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

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

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

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

避免在where子句中對字段進行表達式操作,導致放棄索引進行全表掃描

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

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

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

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

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

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

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

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

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

獲取唯一行時使用limit 1

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

優(yōu)先使用enum

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

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

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

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

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

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

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

空值是不占用空間的,null其實不是空值,而是要占用空間

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

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

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

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

優(yōu)化案例:

select * from news order by id desc limit 1000000,10
耗時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)

慢查詢日志

原子性

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

一致性

事務使得系統(tǒng)從一個一致的狀態(tài)轉換到另一個一致狀態(tài)。事務的一致性決定了一個系統(tǒng)設計和實現(xiàn)的復雜度

隔離性

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

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

最常見的一個性能問題

舉個例子，我們數(shù)據(jù)庫中有兩張表，一個是Customers，一個是Orders。Orders中含有一個外鍵customer_id，指向了Customers的主鍵id,想要得到所有Customer以及其分別對應的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;

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