|
# MySQL的存儲引擎
`MyISAM`（默認表類型）：非事務的存儲引擎，基于傳統(tǒng)的`ISAM`（有索引的順序訪問方法）類型，是存儲記錄和文件的標準方法，不是事務安全，不支持外鍵，適用于頻繁的查詢。表鎖，不會出現(xiàn)死鎖，適合小數(shù)據(jù)和小并發(fā)。
- 為什么不會出死鎖？（沒有事務就不會繼續(xù)持有鎖）
答：因為`MyISAM`再查詢的時候，會同時鎖定這個`sql`里面所有用到的表（獲取鎖的順序是一致的），不局限與一張表，再寫鎖又重疊時，就得等待。
**注意：【`MySQL5.5`之前默認的是`MyISAM`引擎了，5.5之后的版本默認都是`innodb`作為存儲引擎】**
`innodb`：支持事務安全的存儲引擎，適用于插入和更新，支持外鍵，行鎖，事務。適合大數(shù)據(jù)，大并發(fā)。特別是針對多個并發(fā)和`QPS`較高的情況。
- `QPS：`就是每秒查詢率，`QPS`是對一個特定服務器再規(guī)定時間內能處理多少流量的衡量標準。
- `TPS：`就是每秒傳輸處理的事務個數(shù)。
- `innodb`的行鎖模式：共享鎖，排他鎖，意向共享鎖（表鎖），意向排他鎖（表鎖），間隙鎖。（注意：如果`sql`語句沒有使用索引，`innodb`不能確定操作的行時，使用意向鎖（表鎖））。
- 死鎖問題
- 什么是死鎖？
死鎖就是當倆個事務都需要獲取對方持有的排他鎖才能完成事務的時候，就導致了循環(huán)鎖等待，常見的死鎖類型。
- 解決辦法
1. 數(shù)據(jù)庫參數(shù)
2. 盡量約定程序讀取表的順序
3. 在處理一個表時，盡量對處理的順序排序
4. 調整事務隔離級別（避免倆個事務同時操作一行不存在的數(shù)據(jù)，容易發(fā)生死鎖）
存儲引擎還有：
- `MERGE：`將多個類似的`MyISAM`表分組為一個表，可以處理非事務性表，默認情況下包括這些表。
- `MEMORY：`提供內存中的表，以前稱為堆。它在RAM中處理所有數(shù)據(jù)，以便比在磁盤上存儲數(shù)據(jù)更快地訪問。用于快速查找引用和其他相同的數(shù)據(jù)。
- `EXAMPLE：`可以使用此引擎創(chuàng)建表，但不能存儲或獲取數(shù)據(jù)。這樣做的目的是教開發(fā)人員如何編寫新的存儲引擎。
- `ARCHIVE：`用于存儲大量數(shù)據(jù)，不支持索引。
- `CSV：`在文本文件中以逗號分隔值格式存儲數(shù)據(jù)。
- `BLACKHOLE：`受要存儲的數(shù)據(jù)，但始終返回空。
- `FEDERATED：`將數(shù)據(jù)存儲在遠程數(shù)據(jù)庫中。
# 數(shù)據(jù)表的類型
`MyISAM`，`InnoDB`，`MEMORY`，`HEAP`，`BOB`，`ARCHIVE`，`CSV`等
- `MYISAM：`成熟穩(wěn)定，易于管理，快速讀取。表級鎖。
- `Innodb：`數(shù)據(jù)行鎖。占用空間大，不支持全文索引。
# `MySQL`作為發(fā)布系統(tǒng)的儲存，一天五萬條以上的增量，怎么優(yōu)化？
1. 設計良好的數(shù)據(jù)庫結構，允許部分數(shù)據(jù)冗余，盡量避免join查詢，提高效率。
2. 選擇合適的表字段類型和存儲引擎，適當添加索引。
3. `MySQL`庫主從讀寫分離。
4. 找規(guī)律分表，減少表單中的數(shù)據(jù)量，提高查詢速度。
5. 添加緩存機制?？梢允褂胉Redis`緩存。
6. 不經(jīng)常改動的頁面，生成靜態(tài)頁面。
7. 寫高效率的`sql`語句。如：`SELECT * FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE`。
為什么要避免使用join查詢?
答：減少消耗。
# 對于大流量網(wǎng)站，如何解決各頁面統(tǒng)計訪問量問題？
1. 確認服務器是否能支撐當前訪問量。
2. 優(yōu)化數(shù)據(jù)庫訪問。
3. 禁止外部訪問，如圖片盜鏈。
4. 控制文件下載。
5. 使用不同主機進行分流。
6. 使用瀏覽統(tǒng)計軟件，了解訪問量，有針對性的進行優(yōu)化。
# 如何進行`SQL`優(yōu)化？
1. 選擇正確的存儲引擎。
每個引擎都有利有弊，比如`MyISAM`，適用于大量查詢，對大量寫操作并不是很好，`update`一個字段都會把整個表鎖起來，而I`nnodb`，對一些小的應用，它比`MyISAM`慢，但它支持行鎖，再寫操作的時候，很優(yōu)秀，它還支持更多的高級應用。
2. 優(yōu)化字段的數(shù)據(jù)類型
一個原則，越小的越快，如果一個表只有幾列，那我們就不用用`INT`來做主鍵，可以使用`MEDIUMINT`，`SMALLINT`或是更小的`TINYINT`會更經(jīng)濟一些，如果不需要記錄時間，使用`DATE`要比`DATETIME`好的多，也要留夠足夠的空間進行擴展。
3. 為搜索字段添加索引
索引不一定只添加給主鍵或唯一的字段，如果在表中有某個字段經(jīng)常用來做搜索，那就為它建立索引，如果要搜索的字段是大的文本字段，那應該為它建立全文索引。
4. 避免使用`select *`因為從數(shù)據(jù)庫讀出的數(shù)據(jù)越多，那么查詢就會越慢。如果數(shù)據(jù)庫服務和WEB服務器在不同的機器上的話，還會增加網(wǎng)絡傳輸?shù)呢撦d。即使要查詢表的所有字段，也盡量不要用`*`通配符。
5. 使用`ENUM`而不是`VARCHAR`
`ENUM`類型是非常快和緊湊的，它保存的是`TINYINT`，但外表上顯示的是字符串，做一些選項列表很好，比如：性別，民族，部門，狀態(tài)之類的字段，取值有限而且固定。
6. 盡可能使用`NOT NULL`
`NULL`其實也需要額外空間的，在進行比較的時候，程序也會變得復雜，并不是并不可以用`NULL`，在現(xiàn)實的復雜情況下，依然會有些情況需要使用`NULL`值。
7. 固定長度的表會更快
如果表中的所有字段都是固定長度的，那整個表會被認為是`“static”`或“`fixed-lenght”`。例如表中沒有`VARCHAR`，`TEXT`，`BLOB`，只要表中其中一個字段是這些類型，那么這個表就不是“固定長度靜態(tài)表”了，這樣的話`MySQL`引擎會用另一種方法來處理。
固定長度的表也容易被緩存和重建，唯一的副作用就是，固定長度的字段會浪費一些空間，因為固定長度的字段無論用不用，都會分配那么多的空間。
# 如何設計一個高并發(fā)的系統(tǒng)
1. 數(shù)據(jù)庫優(yōu)化，喝的事務隔離級別，`SQL`語句，索引優(yōu)化。
2. 使用緩存，盡量減少數(shù)據(jù)庫`IO`操作。
3. 分布式數(shù)據(jù)庫，分布式緩存。
4. 服務器負載均衡。
# 什么情況下設置了索引卻無法使用
1. 以%開頭`LIKE`，模糊匹配。
2. `OR`語句前后沒有同時使用索引。
3. 數(shù)據(jù)類型出現(xiàn)隱式轉化，如`varchar`不加單引號可能會轉換為`int`型。
# `SQL`注入的主要特點
1. 變種極多，攻擊簡單，危害極大。
2. 未經(jīng)授權操作數(shù)據(jù)庫的數(shù)據(jù)。
3. 惡意篡改網(wǎng)頁。
4. 網(wǎng)頁掛木馬。
5. 私自添加系統(tǒng)賬號或是數(shù)據(jù)庫使用者賬號。
# 優(yōu)化數(shù)據(jù)庫的方法
1. 選取最適合的字段屬性，盡可能減少定義字段寬度，盡量把字段設成`NOT NULL`。
2. 使用`exists`替代`in`，用`not exists`替代`not in`。
3. 使用連接`（JOIN）`來替代子查詢。
4. 適用聯(lián)合`（NUION）`來代替手動創(chuàng)建的臨時表。
5. 事務處理。
6. 鎖定表，優(yōu)化事務處理。
7. 適當用外鍵，優(yōu)化鎖定表。
8. 建立索引。
9. 優(yōu)化查詢語句。
# 數(shù)據(jù)庫中的事務是什么
事務作為一個單元的一組有序的數(shù)據(jù)操作，如果組中的所有操作都完成，則認定事務成功，即使只有一個失敗，事務也不成功。如果所有操作完成，事務則進行提交，其修改將作用于所有其他數(shù)據(jù)庫進程。如果一個操作失敗，則事務將回滾，該事務所有的操作的影響都會取消。
- `ACID`四大特性
- 原子性：不可分割，事務要么全部被執(zhí)行，要么全部不執(zhí)行。
- 一致性：事務的執(zhí)行使得數(shù)據(jù)庫從一種正確的狀態(tài)轉換成另一種正確的狀態(tài)。
- 隔離性：在事務正確提交前，不允許把該事務對數(shù)據(jù)的任何改變提供給任何其他事務。
- 持久性：事務正確提交后，將結果永久保存到數(shù)據(jù)庫中，即使在事務提交后，有了其他故障，事務處理結果也會得到保存。
# 索引的目的是什么？
1. 快速訪問數(shù)據(jù)表中特定信息，提高檢索速度。
2. 創(chuàng)建唯一性索引，保證每一行數(shù)據(jù)的唯一性。
3. 加速表和表之間的連接。
4. 使用分組和排序子句進行數(shù)據(jù)檢索時，可顯著的減少分組和排序的時間。
# 索引對數(shù)據(jù)庫系統(tǒng)的負面影響是什么？
創(chuàng)建索引和維護索引需要消耗時間，這個時間會隨著數(shù)據(jù)量的增加而增加，索引需要占用物理空間。當對表進行增刪改查的時候索引也需要動態(tài)維護，這樣就降低了數(shù)據(jù)的維護速度。
# 為數(shù)據(jù)表建立索引的原則
1. 頻繁使用的，用以縮小查詢范圍的字段上建立索引。
2. 頻繁使用的，需要排序的字段上建立索引。
# 什么情況下不宜建立索引
對于查詢中涉及很少的列，或是重復值較多的列，不宜建立索引。
一些特殊的數(shù)據(jù)類型，不宜就建立索引。如`text`文本字段。
# 左連接和右連接的區(qū)別
左連接：
- 左連接會讀取左表中的全部數(shù)據(jù)，即使右表中沒有對應的數(shù)據(jù)（如果倆個表有相同的數(shù)據(jù)，只會顯示一個），用`NULL`填充。
右連接：
- 右連接會讀取右表的全部數(shù)據(jù)，即使左表中沒有對應的數(shù)據(jù)（如果倆個表有相同的數(shù)據(jù)，只會顯示一個），用`NULL`填充。
# 什么是鎖？
數(shù)據(jù)庫是一個多用戶使用的共享資源，當多個用戶并發(fā)的存取數(shù)據(jù)時，在數(shù)據(jù)庫中就會產(chǎn)生多個事務同時存取同一個數(shù)據(jù)的情況，若對并發(fā)操作不加控制可能就會讀取和儲存不正確的數(shù)據(jù)，破壞數(shù)據(jù)庫的一致性。
# 什么是存儲過程，用什么來調用？
存儲過程就是一個預編譯的`SQL`語句，優(yōu)點是允許模塊化設計，只需要創(chuàng)建一次，就可以在該程序中多次調用，如果某次操作需要執(zhí)行多次`SQL`，使用存儲過程比單純的`SQL`語句要快。可以使用一個命令對象進行調用。
# 索引的作用，和它的優(yōu)缺點
索引就是一種特殊的查詢表，數(shù)據(jù)庫引擎可以用它加速對數(shù)據(jù)的檢索，索引是唯一的，在創(chuàng)建時可以以指定單個列或是多個列。缺點是它減慢了數(shù)據(jù)錄入的速度，同時也增加了數(shù)據(jù)庫的尺寸大小。
# 主鍵，外鍵，索引的區(qū)別？
主鍵：
- 唯一標識一條記錄，不可重復，不可為`NULL`。
- 用來保證數(shù)據(jù)的完整性。
- 只能有一個。
外鍵：
- 表的外鍵是另一個表的主鍵，外鍵可以重復，可以為空。
- 用來和其他表建立聯(lián)系。
- 一個表可以有多個外鍵。
索引：
- 該字段沒有重復值，可以有一個是空值。
- 提高查詢效率排序速度。
- 一個表可以有多個唯一索引。
# 對`SQL`語句的優(yōu)化方法
1. 避免在索引列上使用計算。
2. 避免在索引列上使用`IS NULL`和`IS NOT NULL`。
3. 對查詢進行優(yōu)化，盡量避免全表掃描，首先因該考慮在`where`和`order by`涉及的列上建立索引。
4. 避免在`where`子句對字段進行null值判斷，這件導致引擎放棄使用索引而進行全表掃描。
5. 避免在`where`子句中對字段進行表達式操作，也會導致引擎放棄使用索引而進行全表掃描。
# `SQL`語句中“相關子查詢”和“非相關子查詢”有什么區(qū)別
如果你想加載一篇你寫過的.md文件，在上方工具欄可以選擇導入功能進行對應擴展名的文件導入，
繼續(xù)你的創(chuàng)作。
子查詢：嵌套在其他查詢中的查詢。
非相關子查詢：
- 非相關子查詢是獨立于外部查詢的子查詢，子查詢總共執(zhí)行一次，執(zhí)行完畢后將值傳遞給外部的查詢。
相關子查詢：
- 相關子查詢的執(zhí)行依賴于外部的查詢數(shù)據(jù)，外部查詢執(zhí)行一次，子查詢就會執(zhí)行一次。
【所以非相關子查詢比相關子查詢效率高】
# `char`和`varchar`的區(qū)別
- char`類型的數(shù)據(jù)列里，每個值都占`M`個字節(jié)，如果長度小于`M`，就會在它的右邊用空格字符進行補足（在檢索操作中填補出來的空格符將會被去掉）。
- `vachar`類型的數(shù)據(jù)列里，每個值只占用剛好夠用的字節(jié)再加上一個用來記錄長度的字節(jié)，所以總長度為`L+1`字節(jié)。
# `SQL`問題
- 臟讀
- 在一個事務處理過程中讀取到了另一個未提交事務中的數(shù)據(jù)。
【例子】
A在一個轉賬事務中，轉了100給B，此時B讀到了這個轉賬的數(shù)據(jù)，然后做了一些操作（給A發(fā)貨，或是其他），可是這個時候A的事務并沒有提交，如果A回滾了事務，那這就是臟讀。
- 不可重復讀
- 對數(shù)據(jù)庫中的某個數(shù)據(jù)，一個事務范圍內多次查詢卻返回了不同的數(shù)據(jù)值，是由于在查詢間隔，被另一個事務修改并提交了。
【例子】
事務A在讀取某一數(shù)據(jù)，而事務B立馬修改了這個數(shù)據(jù)并且提交了事務到數(shù)據(jù)庫，事務A再次讀取就得到了不同的結果。發(fā)生了不重復讀。
- 幻讀
- 事務非獨立執(zhí)行時發(fā)生的一種現(xiàn)象。
【例子】
事務A對一個表中所有的行的某個數(shù)據(jù)項做了從“1”修改為“2”的操作，這時事務B又對這個表中插入了一行數(shù)據(jù)項，這個數(shù)據(jù)的數(shù)值還是“1”并且提給了數(shù)據(jù)庫，如果事務A查看剛剛修改的數(shù)據(jù)，會發(fā)現(xiàn)還有一數(shù)據(jù)沒有修改，而這行數(shù)據(jù)時事務B中添加的，就像產(chǎn)生的幻覺一樣。發(fā)生了幻讀。
# `MySQL`事務隔離級別
1. `read uncmmited`：讀到未提交數(shù)據(jù)
- 最低級別，無法保證任情況
2. `read commited`：讀已提交
- 可避免臟讀
3. `repeatable read`：可重復讀
- 可避免臟讀、不可重復讀
4. `serializable`：串行事務
- 可避免臟讀、不可重復讀、幻讀
**【`MySQL`默認事務隔離級別為`Repeatable Read`（可重復讀）】**
# `MySQL`臨時表
什么是臨時表：臨時表是`MySQL`用于存儲中間結果集的表，臨時表只在當前連接可看，當連接關閉時會自動刪除表并釋放所有空間。
為什么會產(chǎn)生臨時表：一般是因為復雜的`SQL`導致臨時表被大量創(chuàng)建
- 進行`union`查詢時
- 用到`temptable`算法或者是`union`查詢中的視圖
- `ORDER BY`和`GROUP BY`的子句不一樣時
- 表連接中，`ORDER BY`的列不是驅動表中的
- `DISTINCT`查詢并且加上`ORDER BY`時
- `SQL`中用到`SLQ_SMALL_RESULT`選項時
- `RROM`中的子查詢
臨時表分為倆種：
- 內存臨時表
- 采用的是`memory`存儲引擎
- 磁盤臨時表
- 菜用的是`myisam`存儲引擎
# 什么是視圖，游標是什么？
視圖：視圖是一種虛擬表，具有和物理表相同的功能?？梢詫σ晥D表進行增刪改查操作，視圖通常是有一個表或者多個表的子集。對視圖的修改不會影響基本表。
- 【使得我們獲取數(shù)據(jù)更容易，相比多表查詢】
游標：是對查詢出來的結果集作為一個單元來有效的處理。游標可以定在該單元的特定行，從結果集的當前行檢索一行或多行?？梢詫Y果集當前行進行修改。
- 【一般不會使用，但需要逐條處理數(shù)據(jù)的時候，游標顯得十分重要】
|
