Mysql:常見問題

爱编程DE文兄發表於2024-11-06
MySql

欄位越多，查詢越慢嗎？為什麼

欄位越多，查詢通常會變慢。具體原因涉及資料庫內部的一些機制：

  1. 資料讀取：
    每個查詢都需要從硬碟或者記憶體中讀取資料。欄位越多，每行的資料量越大，意味著更多的資料需要被讀取到記憶體中進行處理。這增加了IO操作次數和時間。

  2. 記憶體使用：
    欄位越多，查詢的結果集越大，需要佔用更多的記憶體。對於複雜的查詢，資料庫可能需要在記憶體中儲存和處理大量資料，這會導致記憶體開銷增大，可能導致效能下降。

  3. 鎖和併發控制：
    在多使用者環境中，欄位多的查詢可能會持有鎖更長時間，特別是如果這些欄位分佈在不同的行或頁面上。這可能會導致鎖爭用和等待，從而影響整體效能。

在關聯查詢時，比如A和B表是一對多關係，select * from A inner join B on A.id = B.A_id，資料庫在原理上是怎麼去查詢A和B的資料的，請一步步說明

  1. 掃描表 A： MySQL 會先從表 A 開始掃描資料。如果select的欄位和表A關聯欄位是同一個或者id欄位，那麼則會遍歷索引，而不是全表掃描
  2. 連線表 B： 對於表 A 的每一行資料，MySQL 會使用連線條件 A.id = B.A_id 在表 B 中進行匹配。這裡的連線方式（join type）取決於查詢計劃，常見的連線方式有巢狀迴圈連線（Nested Loop Join）和雜湊連線（Hash Join）。

巢狀迴圈連線： 對於表 A 的每一行資料，MySQL 會掃描表 B，並根據連線條件 A.id = B.A_id 查詢匹配的資料行。當B表的A_id有索引時，可以透過索引快速查詢B表資料，而不是全表掃描
雜湊連線： MySQL 會先構建一個雜湊表，將表 B 的連線列（B.A_id）雜湊化，然後在掃描表 A 時，透過雜湊表進行快速匹配。

  1. 提取資料： 對於每一個匹配的行，MySQL 會提取需要的欄位資料，並將匹配結果返回給客戶端。在 SELECT * 的情況下，會提取 A 表和 B 表的所有列。

笛卡爾積查詢如何實現?

比如A和B表都有10條記錄，如下SQL：

select * from A,B

查詢的結果會有100條記錄，相當兩個表相乘

為什麼推薦小表來join大表？

其實絕大部分情況下，from A表是走全表的，join表很多時候能走索引，因此儘量from表是小資料量表，join是大資料量表也可以快速查詢

7種SQLjoin寫法？

image

SELECT employee_id, department_ name 
FROM employees e Inner JoIN departments d ON e.`department_id` = d. 'department_id `;

image

SELECT employee_id, department_name
FROM employees e
LEFT JoIN departments d ON e. `department_id` = d. department_id `;

image

SELECT employee_id, department_name
FROM employees e
RIGHT JoIN departments d ON e. `department_id` = d. department_id `;

image

SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e. `department_id` = d.'department_id`
WHERE d. department_id IS NUL;

image

SELECT employee_id, department name
FROM employees e 
RIGHT JOIN departments d ON e. `department_id = d. `department_id` 
WHERE e.'department_id`IS NULL;

image

SELECT employee_id, department_name
FROM employees e
LEFT JoIN departments d ON e. `department_id` = d. department_id `;
union all
SELECT employee_id, department name
FROM employees e 
RIGHT JOIN departments d ON e. `department_id = d. `department_id` 
WHERE e.'department_id`IS NULL;

image

SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e. `department_id` = d.'department_id`
WHERE d. department_id IS NUL;
union all
SELECT employee_id, department name
FROM employees e 
RIGHT JOIN departments d ON e. `department_id = d. `department_id` 
WHERE e.'department_id`IS NULL;

檢視的作用是什麼？為啥真實開發中很少見到建立檢視？

檢視並不是一張真實表，而是將各個真實表拼接展示的虛擬表，不過我們對檢視進行增加、更新、刪除，是能影響到實體表的。檢視的出現是為了讓一些人員不能看到真實表的某一些欄位，比如表A有工資等敏感欄位，不希望開發人員能看到這張表的全部資料，那麼就可以根據A建立檢視，讓開發人員看這張檢視即可

在中小型企業中，其實對資訊保安這塊不是很嚴格，往往開發人員是可以直接看到表的全部欄位資料的，所以就不需要建立檢視。但在一些大型企業，檢視還是挺常見到的

預設字符集？

在MysQL 8.0版本之前，預設字符集為latin1，utf8字符集指向的是utf8mb3。網站開發人員在資料庫設計的時候往往會將編碼修改為utf8字符集。如果遺忘修改預設的編碼，就會出現亂碼的問題。從MySQL 8.0開始，資料庫的預設編碼將改為utf8mb4，從而避免上述亂碼的問題。

utf8字符集是什麼？

utf8與utf8mb4

utf8字符集表示一個字元需要使用14個位元組，但是我們常用的一些字元使用13個位元組就可以表示了。而字符集表示一個字元所用的最大位元組長度，在某些方面會影響系統的儲存和效能，所以設計MysQL的設計者偷偷的定義了兩個概念:

utf8mb3︰閹割過的utf8字符集，只使用1~3個位元組表示字元。
utf8mb4 :正宗的utf8字符集，使用1～4個位元組表示字元。

在MysQL中utf8是utf8mb3的別名，所以之後在MysQL中提到utf8就意味著使用1~3個位元組來表示一個字元。如果大家有使用4位元組編碼一個字元的情況，比如儲存一些emoji表情，那請使用utf8mb4 。

比較規則是什麼？

image
image

Mysql，什麼SQL語句下會出現鎖表情況

  1. DDL 操作
    任何修改表結構的操作都會鎖住表，例如：
    ALTER TABLE：修改表結構，如增加或刪除列。
    DROP TABLE：刪除表。
    TRUNCATE TABLE：清空表資料。

總結：最好在資料庫低峰的時候進行ALTER TABLE操作。
2. 全表掃描的 DML 操作
如果在沒有索引的情況下對錶進行全表掃描的操作，可能會導致表鎖，例如：
UPDATE：對錶中所有行進行更新。
DELETE：刪除表中所有行。

總結：每次寫update語句時，保證where是有索引的。在事務開啟下，多次對同一張表執行相同的update語句不會鎖表，但會鎖行，因此儘量縮短事務時間。

  1. 大量資料操作
    在表上執行大規模資料操作也可能導致鎖表，例如：
    大批次的 INSERT 操作。

總結：一次性插入的數量不宜太多

  1. 行鎖升級為表鎖
    在 InnoDB 儲存引擎中，如果行鎖的數量過多，也可能會升級為表鎖，例如：
    多行更新操作 UPDATE，涉及的行數特別多時。

總結：儘量確保每次更新的資料行不會太多

相關文章