mysql分頻段存_哒哒哒之MySQL

知識點

事務

———————

**事務是是指一組sql語句組成的資料庫邏輯處理單元，在這組的sql操作中，要麼全部執行成功，要麼全部執行失敗。

--事務的特性 (ACID)

1. 原子性 (Atomicity)：所有操作全部完成或全部不完成，最小的執行機關。undo log

2. 一緻性 (Consistency)：事務執行前後都處于一緻性狀态。redo log

3. 隔離性 (Isolation)：并發執行的各個事務之間不能互相幹擾。S/X鎖+MVCC

4. 持久性 (Durability)：事務執行完對資料的修改是永久的。

--Redo/Undo機制

1. Redo log記錄被修改後的值，用來恢複未寫入 data file 的更新成功的資料；

2. Undo log記錄資料更新前的值，保證資料更新失敗能夠復原。

--事務隔離級别

**預設隔離級别: （select @@transaction_isolation;）

1. 讀未送出(Read Uncommitted)：一個事務讀到另一個事務未送出讀資料。讀不加鎖，寫加排它鎖。

2. 讀已送出(Read Commited)：一個事務修改的資料送出後才能被另外一個事務讀取。每次select生成MVCC版本。

3. 可重複讀(Repeatable Read)：保證一個事務相同條件下前後兩次擷取的資料是一緻的。第一次select生成MVCC版本。

4. 串行化(Serializable)：每次讀操作都會加鎖，快照讀失效，事務串行執行。讀加共享鎖，寫加排它鎖

--為什麼選讀已送出作為事務隔離級别

1. 可重複讀存在間隙鎖，導緻出現死鎖的幾率比RC大的多！

2. 可重複讀條件列未命中索引會鎖表！而在RC隔離級别下，隻鎖行

3. 在RC隔離級别下，半一緻性讀(semi-consistent)特性增加了update操作的并發性！

--并發(事務)問題

1. 髒讀：讀取被更新但沒送出的字段

2. 不可重複讀：T1讀取了字段，然後T2更新UPDATE了該字段并送出之後，T1再次提取同一個字段，值便不相等

3. 幻讀：事務T1從表中讀取資料，然後T2進行了INSERT操作并送出，當T1再次讀取的時候，結果不一緻的情況發生

--鎖

1. 共享鎖/讀鎖：SELECT ... LOCK IN SHARE MODE;

2. 排它鎖/寫鎖：SELECT ... FOR UPDATE;

3. 間隙鎖：Gap Locks和Next-Key Locks

4. 行鎖 5. 表鎖 6. 悲觀鎖 7. 樂觀鎖

**MySQL 把行鎖和間隙鎖合并在一起，解決并發寫和幻讀的問題，這個鎖叫做 Next-Key鎖

**串行化時，讀的時候加共享鎖，寫的時候加排它鎖

**讀未送出：讀不加任何鎖，寫加排它鎖

存儲引擎

———————

MyISAM/InnoDB

1. myisam隻支援表級鎖，不支援事務、外鍵，支援全文索引；非聚簇索引(葉子節點存指針)

2. Innodb支援行級鎖、事務、外鍵，不支援全文索引；聚簇索引。通過MVCC(多版本并發控制)解決了幻讀問題。

3. myisam本身存儲了表的總資料行，Innodb沒有存儲，查總行數myisam更快，如果加了查詢條件兩者就沒有差別了

InnoDB四大特性：1. 插入緩沖；2. 二次寫；3. 自适應哈希索引；4. 預讀；

索引

————————————

**索引是一種資料結構。索引中是包含一個表中列的值和它的實體位址的值，并且這些值存儲在一個資料結構中。

--優點（檢索快，減少I/O次數；加快分組和排序；保證資料唯一性、表間參照完整性）

1. 加快資料檢索 （定位快）

2. 保證資料的唯一性 （唯一限制）

3. 實作表與表之間的參照完整性（進行外鍵限制參照的列）

4. 在使用group by、order by 字句進行查詢時，利用索引可以減少排序和分組的時間

--缺點（時間空間，減慢增删改）

1. 建立索引會需要一定的時間和資料空間。

2. 雖然建立索引增加了查詢的速度，但是減慢了增删改的速度

**B樹索引/哈希索引

1. B樹索引具有範圍查找和字首查找的能力，複雜度為O(LogN)。

2. 哈希索引隻能做等值查找，複雜度都是O(1)。

--聚簇索引/非聚簇索引

1. 聚簇索引：葉子節點中存放整行資料（資料頁），一般是主鍵索引或非空的唯一索引

2. 非聚簇索引（輔助索引）：存放的是索引（主鍵值），指向專門的資料頁的資料

--聚簇索引的優缺點

優點：

1. 資料通路更快：因為聚簇索引将索引和資料儲存在同一個B+樹中

2. 聚簇索引對于主鍵的排序查找和範圍查找速度非常快

缺點：

1. 插入速度嚴重依賴于插入順序：按照主鍵的順序插入是最快的方式，否則将會出現頁分裂，嚴重影響性能。是以，對于InnoDB表，我們一般都會定義一個自增的ID列為主鍵

2. 更新主鍵的代價很高，因為将會導緻被更新的行移動。是以，對于InnoDB表，我們一般定義主鍵為不可更新。

3. 二級索引通路需要兩次索引查找，第一次找到主鍵值，第二次根據主鍵值找到行資料。

--邏輯角度

1. 普通索引：僅加速查詢

2. 唯一索引：加速查詢 + 列值唯一（可以有null）

3. 主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中隻有一個（聚簇索引）

4. 組合索引：多列值組成一個索引，專門用于組合搜尋，其效率大于索引合并

5. 外鍵索引：保證資料的一緻性、完整性和實作級聯操作

6. 全文索引：用大文本對象的列建構的索引

--不推薦使用索引（沒必要用）

1. 資料唯一性差（一個字段的取值隻有幾種時，比如性别）

2. 頻繁更新的字段

3. 字段不在where語句

4. 使用不等于(<>)

--索引失效的情況（沒用）

1. 條件中有or：要想使用or，又想讓索引生效，隻能将or條件中的每個列都加上索引

2. 組合索引：未使最左邊的字段（最左字首原則）

3. like查詢以%開頭，前導模糊查詢不能利用索引

4. 存在索引列的資料類型隐形轉換。（1. 列類型是字元串未用引号；2. 索引列上有數學運算；3.使用函數）

5. mysql估計全表掃描更快 （ 比如資料量極少）

**索引失效分析工具：explain指令加在sql語句前面，在執行結果中檢視key這一列的值，如果為NULL，說明沒有使用索引

--主鍵和唯一索引的差別

**主鍵是一種限制，而唯一索引是一種索引，是表的備援資料結構，兩者有本質的差别

1. 主鍵一定會建立一個唯一索引，但是有唯一索引的列不一定是主鍵；

2. 主鍵不允許為空值，唯一索引列允許空值；

3. 一個表隻能有一個主鍵，但是可以有多個唯一索引；

4. 主鍵可以被其他表引用為外鍵，唯一索引列不可以；

--主鍵、外鍵和索引

1. 主鍵：唯一辨別一條記錄，不能有重複的，不允許為空；用來保證資料完整性；主鍵隻能有一個

2. 外鍵：表的外鍵是另一表的主鍵, 外鍵可以有重複的, 可以是空值；用來和其他表建立聯系用的，保證一緻性；可以有多個外鍵

3. 索引：該字段沒有重複值，但可以有一個空值；是提高查詢排序的速度；一個表可以有多個惟一索引

**覆寫索引：索引包含所有滿足查詢需要的資料，即不需要回表操作，可以優化Limit分頁查詢的效率

範式

————————————

1. 第一範式：原子性，字段不可分；資料庫表中的任何屬性都具有原子性的，不可再分解

2. 第二範式：唯一性，有主鍵且非主鍵字段依賴主鍵；對記錄的惟一性限制，要求記錄有惟一辨別，即實體的惟一性

3. 第三範式：非主鍵字段不能互相依賴；對字段備援性的限制，即任何字段不能由其他字段派生出來，它要求字段沒有備援

視圖

————————————

**視圖本質上是一種虛拟表。

--優點（簡化sql查詢，提高開發效率）

1. 簡單化，資料所見即所得

2. 安全性，使用者隻能查詢或修改他們所能見到得到的資料

3. 邏輯獨立性，可以屏蔽真實表結構變化帶來的影響

--缺點

1. 性能相對較差，簡單的查詢也會變得稍顯複雜

2. 修改不友善，特變是複雜的聚合視圖基本無法修改

查詢語言分類

————————————

1. 資料查詢語言DQL：SELECT子句，FROM子句，WHERE子句

2. 資料操縱語言DML：INSERT，UPDATE，DELETE

3. 資料定義語言DDL：表、視圖、索引、同義詞、聚簇等，DDL是隐性送出的，不能rollback，create, drop

4. 資料控制語言DCL：設定或者更改資料庫使用者或權限的語句，包括GRANT、DENY、REVOKE

删除表

————————————

1. delete : 僅删除表資料，支援條件過濾，支援復原。記錄日志。是以比較慢。

2. truncate: 僅删除所有資料，不支援條件過濾，不支援復原。不記錄日志，效率高于delete。

3. drop:删除表資料同時删除表結構。将表所占的空間都釋放掉。删除效率最高。

in/not in/exists/not exists

————————————

1. not exist會對主子查詢都會使用索引

2. exist會針對子查詢的表使用索引

3. in與子查詢一起使用的時候,隻針對主查詢使用索引

4. not in則不會使用任何索引

**子查詢表大的用exists，子查詢表小的用in；not exists比not in 快

--遊标

遊标是系統為使用者開設的一個資料緩沖區，存放SQL語句的執行結果，每個遊标區都有一個名字。使用者可以通過遊标逐一擷取記錄并賦給主變量，交由主語言進一步處理。

--存儲過程

存儲過程是一個預編譯的SQL語句，優點是允許子產品化的設計，就是說隻需要建立一次，以後在該程式中就可以調用多次。如果某次操作需要執行多次SQL，使用存儲過程比單純SQL語句執行要快。

--觸發器

觸發器是使用者定義在關系表上的一類由事件驅動的特殊的存儲過程。觸發器是指一段代碼，當觸發某個事件時，自動執行這些代碼。

binlog

————————————

--錄入格式

1. statement：儲存修改資料的sql語句

2. row：儲存修改的記錄

3. mixed

技巧

--sql執行順序

1. from 2. join……on 3. where 4. group by 5. avg,sum.... 6. having

7. select 8. distinct 9. order by 10. limit

--[LIMIT N][ OFFSET M]=limit m,n。limit是在order之後執行的。offset m不包括m，既limit 0,1=limit 1 offset 0=limit 1。

--去重，distinct。group by 可替代distinct。

--inner join...on... 輸出兩表共同的，可用where直接代替

--在 SQL 中增加 HAVING 子句原因是，WHERE 關鍵字無法與聚合函數（group by）一起使用。HAVING 子句可以讓我們篩選分組後的各組資料。

--精确比對采用=，範圍請用in/not in

--sum(amount) over(partition by name order by mon rows between unbounded preceding and current row)

--排序

1. rank()：并列，占位

2. dense_rank()：并列，不占位

3. row_number() over()：不并列，row_number() over([partition by sex] order by age desc) as rank

--求top1/topN

1. 自連結（推薦）

SELECT a.*,b.max_score from sc a

INNER JOIN (

SELECT CId,max(score) max_score

from sc

GROUP BY CId ) b

on a.CId= b.CId and a.score= b.max_score;（每門課程中成績最好的人）

2. 自查詢（資料量不能大）

SELECT * from sc a

where score = (SELECT max(score) from sc where a.CId=CId )

3. EXISTS

SELECT * from sc a where

not EXISTS

(SELECT 1 from sc where a.score <score and a.CId= CId)

4. 視窗函數（推薦)

SELECT SId ,CId,score from (

SELECT SId,CId,score,dense_rank() over (partition by CId ORDER BY score desc) as ranking from sc ) b

where ranking=N; (更改此處條件可随性求top N, top1-N等)

--字元串連接配接

1. concat(s1, s2, ...)

2. concat_ws(x, s1, s2, ...) 有分割符x

3. group_concat(): 連接配接字段，多個值顯示為一行；group_concat( [DISTINCT] 連接配接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )

--字元串截取

1. left(str, length)

2. right(str, length)

3. substring(str, index[, len]) index>0從左邊開始, index<0從右邊開始；（sql索引從1開始）

4.

--查詢使用者的最長連續登陸天數

select uid, max(continuous_days) as maxday from

(

select uid, date_sub(date1,sort) as login_group, count(*) as continuoous_days from

(

select uid,udate, row_number() over(patition by uid order by udate) as sort from user_login

) a

group by uid, login_group

) b

group by uid;

版權所有，翻版不究。