萬字長文，最常問的 MySQL 面試題集合

除了基礎題部分，本文還收集整理的 MySQL 面試題還包括如下知識點或題型：

• MySQL 高性能索引

• SQL 語句

• MySQL 查詢優化

• MySQL 高擴展高可用

• MySQL 安全性

問題 1：char、varchar 的區別是什麼？
varchar 是變長而 char 的長度是固定的。如果你的內容是固定大小的，你會得到更好的性能。

問題 2: TRUNCATE 和 DELETE 的區別是什麼？
DELETE 命令從一個表中刪除某一行，或多行，TRUNCATE 命令永久地從表中刪除每一行。

問題 3：什麼是觸發器，MySQL 中都有哪些觸發器？
觸發器是指一段代碼，當觸發某個事件時，自動執行這些代碼。在 MySQL 數據庫中有如下六種觸發器：

• 1、Before Insert

• 2、After Insert

• 3、Before Update

• 4、After Update

• 5、Before Delete

• 6、After Delete

問題 4：FLOAT 和 DOUBLE 的區別是什麼？

• FLOAT 類型數據可以存儲至多 8 位十進制數，並在內存中佔 4 字節。

• DOUBLE 類型數據可以存儲至多 18 位十進制數，並在內存中佔 8 字節。

問題 5：如何在 MySQL 種獲取當前日期？

SELECT CURRENT_DATE();

問題 6：如何查詢第 n 高的工資？

SELECT DISTINCT(salary) from employee ORDER BY salary DESC LIMIT n-1,1

(提示：代碼可以左右滑動)

問題 7：請寫出下面 MySQL 數據類型表達的意義（int(0)、char(16)、varchar(16)、datetime、text）

知識點分析

此題考察的是 MySQL 數據類型。MySQL 數據類型屬於 MySQL 數據庫基礎，由此延伸出的知識點還包括如下內容：

• MySQL 基礎操作

• MySQL 存儲引擎

• MySQL 鎖機制

• MySQL 事務處理、存儲過程、觸發器

數據類型考點：

• 1、整數類型，包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示 1 字節、2 字節、3 字節、4 字節、8 字節整數。任何整數類型都可以加上 UNSIGNED 屬性，表示數據是無符號的，即非負整數。
  長度：整數類型可以被指定長度，例如：INT(11) 表示長度爲 11 的 INT 類型。長度在大多數場景是沒有意義的，它不會限制值的合法範圍，只會影響顯示字符的個數，而且需要和 UNSIGNED ZEROFILL 屬性配合使用纔有意義。
  例子，假定類型設定爲 INT(5)，屬性爲 UNSIGNED ZEROFILL，如果用戶插入的數據爲 12 的話，那麼數據庫實際存儲數據爲 00012。

• 2、實數類型，包括 FLOAT、DOUBLE、DECIMAL。
  DECIMAL 可以用於存儲比 BIGINT 還大的整型，能存儲精確的小數。
  而 FLOAT 和 DOUBLE 是有取值範圍的，並支持使用標準的浮點進行近似計算。
  計算時 FLOAT 和 DOUBLE 相比 DECIMAL 效率更高一些，DECIMAL 你可以理解成是用字符串進行處理。

• 3、字符串類型，包括 VARCHAR、CHAR、TEXT、BLOB
  VARCHAR 用於存儲可變長字符串，它比定長類型更節省空間。
  VARCHAR 使用額外 1 或 2 個字節存儲字符串長度。列長度小於 255 字節時，使用 1 字節表示，否則使用 2 字節表示。
  VARCHAR 存儲的內容超出設置的長度時，內容會被截斷。
  CHAR 是定長的，根據定義的字符串長度分配足夠的空間。
  CHAR 會根據需要使用空格進行填充方便比較。
  CHAR 適合存儲很短的字符串，或者所有值都接近同一個長度。
  CHAR 存儲的內容超出設置的長度時，內容同樣會被截斷。

使用策略：
對於經常變更的數據來說，CHAR 比 VARCHAR 更好，因爲 CHAR 不容易產生碎片。
對於非常短的列，CHAR 比 VARCHAR 在存儲空間上更有效率。
使用時要注意只分配需要的空間，更長的列排序時會消耗更多內存。
儘量避免使用 TEXT/BLOB 類型，查詢時會使用臨時表，導致嚴重的性能開銷。

• 4、枚舉類型（ENUM），把不重複的數據存儲爲一個預定義的集合。
  有時可以使用 ENUM 代替常用的字符串類型。
  ENUM 存儲非常緊湊，會把列表值壓縮到一個或兩個字節。
  ENUM 在內部存儲時，其實存的是整數。
  儘量避免使用數字作爲 ENUM 枚舉的常量，因爲容易混亂。
  排序是按照內部存儲的整數

• 5、日期和時間類型，儘量使用 timestamp，空間效率高於 datetime，
  用整數保存時間戳通常不方便處理。
  如果需要存儲微妙，可以使用 bigint 存儲。
  看到這裏，這道真題是不是就比較容易回答了。

答：int(0) 表示數據是 INT 類型，長度是 0、char(16) 表示固定長度字符串，長度爲 16、varchar(16) 表示可變長度字符串，長度爲 16、datetime 表示時間類型、text 表示字符串類型，能存儲大字符串，最多存儲 65535 字節數據）

MySQL 基礎操作：

常見操作

MySQL 的連接和關閉：mysql -u -p -h -P

-u：指定用戶名
-p：指定密碼
-h：主機
-P：端口

進入 MySQL 命令行後：G、c、q、s、h、d

G：打印結果垂直顯示
c：取消當前 MySQL 命令
q：退出 MySQL 連接
s：顯示服務器狀態
h：幫助信息
d：改變執行符

MySQL 存儲引擎：

1、InnoDB 存儲引擎，

• 默認事務型引擎，最重要最廣泛的存儲引擎，性能非常優秀。

• 數據存儲在共享表空間，可以通過配置分開。也就是多個表和索引都存儲在一個表空間中，可以通過配置文件改變此配置。

• 對主鍵查詢的性能高於其他類型的存儲引擎。

• 內部做了很多優化，從磁盤讀取數據時會自動構建 hash 索引，插入數據時自動構建插入緩衝區。

• 通過一些機制和工具支持真正的熱備份。

• 支持崩潰後的安全恢復。

• 支持行級鎖。

• 支持外鍵。

2、MyISAM 存儲引擎，

• 擁有全文索引、壓縮、空間函數。

• 不支持事務和行級鎖、不支持崩潰後的安全恢復。

• 表存儲在兩個文件，MYD 和 MYI。

• 設計簡單，某些場景下性能很好，例如獲取整個表有多少條數據，性能很高。

• 全文索引不是很常用，不如使用外部的 ElasticSearch 或 Lucene。

3、其他表引擎，
Archive、Blackhole、CSV、Memory

使用策略
在大多數場景下建議使用 InnoDB 存儲引擎。

MySQL 鎖機制

表鎖是日常開發中的常見問題，因此也是面試當中最常見的考察點，當多個查詢同一時刻進行數據修改時，就會產生併發控制的問題。共享鎖和排他鎖，就是讀鎖和寫鎖。

• 共享鎖，不堵塞，多個用戶可以同時讀一個資源，互不干擾。

• 排他鎖，一個寫鎖會阻塞其他的讀鎖和寫鎖，這樣可以只允許一個用戶進行寫入，防止其他用戶讀取正在寫入的資源。

鎖的粒度

• 表鎖，系統開銷最小，會鎖定整張表，MyIsam 使用表鎖。

• 行鎖，最大程度的支持併發處理，但是也帶來了最大的鎖開銷，InnoDB 使用行鎖。

MySQL 事務處理

• MySQL 提供事務處理的表引擎，也就是 InnoDB。

• 服務器層不管理事務，由下層的引擎實現，所以同一個事務中，使用多種引擎是不靠譜的。

• 需要注意，在非事務表上執行事務操作，MySQL 不會發出提醒，也不會報錯。

存儲過程

• 爲以後的使用保存的一條或多條 MySQL 語句的集合，因此也可以在存儲過程中加入業務邏輯和流程。

• 可以在存儲過程中創建表，更新數據，刪除數據等等。

使用策略

• 可以通過把 SQL 語句封裝在容易使用的單元中，簡化複雜的操作

• 可以保證數據的一致性

• 可以簡化對變動的管理

觸發器

提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程。
使用場景

• 可以通過數據庫中的相關表實現級聯更改。

• 實時監控某張表中的某個字段的更改而需要做出相應的處理。

• 例如可以生成某些業務的編號。

• 注意不要濫用，否則會造成數據庫及應用程序的維護困難。

• 大家需要牢記以上基礎知識點，重點是理解數據類型 CHAR 和 VARCHAR 的差異，表存儲引擎 InnoDB 和 MyISAM 的區別。

問題 8：請說明 InnoDB 和 MyISAM 的區別

• InnoDB 支持事務，MyISAM 不支持；

• InnoDB 數據存儲在共享表空間，MyISAM 數據存儲在文件中；

• InnoDB 支持行級鎖，MyISAM 只支持表鎖；

• InnoDB 支持崩潰後的恢復，MyISAM 不支持；

• InnoDB 支持外鍵，MyISAM 不支持；

• InnoDB 不支持全文索引，MyISAM 支持全文索引；

問題 9：innodb 引擎的特性

• 插入緩衝（insert buffer)

• 二次寫 (double write)

• 自適應哈希索引 (ahi)

• 預讀 (read ahead)

問題 10：請列舉 3 個以上表引擎
InnoDB、MyISAM、Memory

問題 11：請說明 varchar 和 text 的區別

• varchar 可指定字符數，text 不能指定，內部存儲 varchar 是存入的實際字符數 + 1 個字節（n<=255）或 2 個字節 (n>255)，text 是實際字符數 + 2 個字節。

• text 類型不能有默認值。

• varchar 可直接創建索引，text 創建索引要指定前多少個字符。varchar 查詢速度快於 text, 在都創建索引的情況下，text 的索引幾乎不起作用。

• 查詢 text 需要創建臨時表。

問題 12：varchar(50) 中 50 的含義
最多存放 50 個字符，varchar(50)和 (200) 存儲 hello 所佔空間一樣，但後者在排序時會消耗更多內存，因爲 order by col 採用 fixed_length 計算 col 長度(memory 引擎也一樣)。

問題 13：int(20) 中 20 的含義
是指顯示字符的長度，不影響內部存儲，只是當定義了 ZEROFILL 時，前面補多少個 0

問題 14：簡單描述 MySQL 中，索引，主鍵，唯一索引，聯合索引的區別，對數據庫的性能有什麼影響？

知識點分析

此真題主要考察的是 MySQL 索引的基礎和類型，由此延伸出的知識點還包括如下內容：

• MySQL 索引的創建原則

• MySQL 索引的注意事項

• MySQL 索引的原理

下面我們就來將這些知識一網打盡

索引的基礎

• 索引類似於書籍的目錄，要想找到一本數的某個特定主題，需要先查找書的目錄，定位對應的頁碼

• 存儲引擎使用類似的方式進行數據查詢，先去索引當中找到對應的值，然後根據匹配的索引找到對應的數據行。

創建索引的語法：

• 首先創建一個表：create table t1 (id int primary key,username varchar(20),password varchar(20));

• 創建單個索引的語法：CREATE INDEX 索引名 on 表名（字段名）

• 索引名一般是：表名_字段名

• 給 id 創建索引：CREATE INDEX t1_id on t1(id);

• 創建聯合索引的語法：CREATE INDEX 索引名 on 表名（字段名 1，字段名 2）

• 給 username 和 password 創建聯合索引：CREATE index t1_username_password ON t1(username,password)

• 其中 index 還可以替換成 unique，primary key，分別代表唯一索引和主鍵索引

• 刪除索引：DROP INDEX t1_username_password ON t1

索引對性能的影響：

• 大大減少服務器需要掃描的數據量。

• 幫助服務器避免排序和臨時表。

• 將隨機 I/O 變順序 I/O。

• 大大提高查詢速度。

• 降低寫的速度（不良影響）。

• 磁盤佔用（不良影響）。

索引的使用場景：

• 對於非常小的表，大部分情況下全表掃描效率更高。

• 中到大型表，索引非常有效。

• 特大型的表，建立和使用索引的代價會隨之增大，可以使用分區技術來解決。

索引的類型：
索引很多種類型，是在 MySQL 的存儲引擎實現的。

• 普通索引：最基本的索引，沒有任何約束限制。

• 唯一索引：和普通索引類似，但是具有唯一性約束。

• 主鍵索引：特殊的唯一索引，不允許有空值。

索引的區別：

• 一個表只能有一個主鍵索引，但是可以有多個唯一索引。

• 主鍵索引一定是唯一索引，唯一索引不是主鍵索引。

• 主鍵可以與外鍵構成參照完整性約束，防止數據不一致。

• 聯合索引：將多個列組合在一起創建索引，可以覆蓋多個列。（也叫複合索引，組合索引）

• 外鍵索引：只有 InnoDB 類型的表纔可以使用外鍵索引，保證數據的一致性、完整性、和實現級聯操作（基本不用）。

• 全文索引：MySQL 自帶的全文索引只能用於 MyISAM，並且只能對英文進行全文檢索 （基本不用）

MySQL 索引的創建原則

• 最適合創建索引的列是出現在 WHERE 或 ON 子句中的列，或連接子句中的列而不是出現在 SELECT 關鍵字後的列。

• 索引列的基數越大，數據區分度越高，索引的效果越好。

• 對於字符串進行索引，應該制定一個前綴長度，可以節省大量的索引空間。

• 根據情況創建聯合索引，聯合索引可以提高查詢效率。

• 避免創建過多的索引，索引會額外佔用磁盤空間，降低寫操作效率。

• 主鍵儘可能選擇較短的數據類型，可以有效減少索引的磁盤佔用提高查詢效率。

MySQL 索引的注意事項
1、聯合索引遵循前綴原則

KEY(a,b,c) WHERE a = 1 AND b = 2 AND c = 3 WHERE a = 1 AND b = 2 WHERE a = 1 #以上SQL語句可以用到索引 WHERE b = 2 AND c = 3 WHERE a = 1 AND c = 3 #以上SQL語句用不到索引

2、LIKE 查詢，% 不能在前

WHERE name LIKE "%wang%" #以上語句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。

3、列值爲空（NULL）時是可以使用索引的，但 MySQL 難以優化引用了可空列的查詢, 它會使索引、索引統計和值更加複雜。可空列需要更多的儲存空間，還需要在 MySQL 內部進行特殊處理。

4、如果 MySQL 估計使用索引比全表掃描更慢，會放棄使用索引，例如：
表中只有 100 條數據左右。對於 SQL 語句 WHERE id > 1 AND id < 100，MySQL 會優先考慮全表掃描。

5、如果關鍵詞 or 前面的條件中的列有索引，後面的沒有，所有列的索引都不會被用到。

6、列類型是字符串，查詢時一定要給值加引號，否則索引失效，例如：
列 name varchar(16)，存儲了字符串 "100"
WHERE name = 100;
以上 SQL 語句能搜到，但無法用到索引。

MySQL 索引的原理

• MySQL 索引是用一種叫做聚簇索引的數據結構實現的，下面我們就來看一下什麼是聚簇索引。

• 聚簇索引是一種數據存儲方式，它實際上是在同一個結構中保存了 B + 樹索引和數據行，InnoDB 表是按照聚簇索引組織的（類似於 Oracle 的索引組織表）。

注： B+ 樹是一種樹數據結構，是一個n叉排序樹，每個節點通常有多個孩子，一棵B+樹包含根節點、內部節點和葉子節點。根節點可能是一個葉子節點，也可能是一個包含兩個或兩個以上孩子節點的節點。 B+ 樹通常用於數據庫和操作系統的文件系統中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系統都在使用B+樹作爲元數據索引。B+ 樹的特點是能夠保持數據穩定有序，其插入與修改擁有較穩定的對數時間複雜度。B+ 樹元素自底向上插入。

InnoDB 通過主鍵聚簇數據，如果沒有定義主鍵，會選擇一個唯一的非空索引代替，如果沒有這樣的索引，會隱式定義個主鍵作爲聚簇索引。
下圖形象說明了聚簇索引表 (InnoDB) 和普通的堆組織表 (MyISAM) 的區別：

最常問的 MySQL 面試題三——每個開發人員都應該知道
對於普通的堆組織表來說（右圖），表數據和索引是分別存儲的，主鍵索引和二級索引存儲上沒有任何區別。
而對於聚簇索引表來說（左圖），表數據是和主鍵一起存儲的，主鍵索引的葉結點存儲行數據，二級索引的葉結點存儲行的主鍵值。
聚簇索引表最大限度地提高了 I/O 密集型應用的性能，但它也有以下幾個限制：

• 1）插入速度嚴重依賴於插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響性能。因此，對於 InnoDB 表，我們一般都會定義一個自增的 ID 列爲主鍵。

• 2）更新主鍵的代價很高，因爲將會導致被更新的行移動。因此，對於 InnoDB 表，我們一般定義主鍵爲不可更新。

• 3）二級索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據主鍵值找到行數據。

二級索引的葉節點存儲的是主鍵值，而不是行指針，這是爲了減少當出現行移動或數據頁分裂時二級索引的維護工作，但會讓二級索引佔用更多的空間。

解題方法

在一些 MySQL 索引基礎考題中，我們可以輕鬆的通過索引基礎和類型來解決此類問題，對於一些索引創建注意事項方面的考點，我們可以通過索引創建原則和注意事項來解決。

問題 14：創建 MySQL 聯合索引應該注意什麼？
需遵循前綴原則

問題 15：列值爲 NULL 時，查詢是否會用到索引？
在 MySQL 裏 NULL 值的列也是走索引的。當然，如果計劃對列進行索引，就要儘量避免把它設置爲可空，MySQL 難以優化引用了可空列的查詢, 它會使索引、索引統計和值更加複雜。

問題 16：以下語句是否會應用索引：SELECT FROM users WHERE YEAR(adddate) < 2007;*
不會，因爲只要列涉及到運算，MySQL 就不會使用索引。

問題 17：MyISAM 索引實現？
MyISAM 存儲引擎使用 B+Tree 作爲索引結構，葉節點的 data 域存放的是數據記錄的地址。MyISAM 的索引方式也叫做非聚簇索引的，之所以這麼稱呼是爲了與 InnoDB 的聚簇索引區分。

問題 18：MyISAM 索引與 InnoDB 索引的區別？

• InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。

• InnoDB 的主鍵索引的葉子節點存儲着行數據，因此主鍵索引非常高效。

• MyISAM 索引的葉子節點存儲的是行數據地址，需要再尋址一次才能得到數據。

• InnoDB 非主鍵索引的葉子節點存儲的是主鍵和其他帶索引的列數據，因此查詢時做到覆蓋索引會非常高效。

問題 19：以下三條 sql 如何建索引，只建一條怎麼建？

WHERE a=1 AND b=1 WHERE b=1 WHERE b=1 ORDER BY time DESC

以順序 b,a,time 建立聯合索引，CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因爲最新 MySQL 版本會優化 WHERE 子句後面的列順序，以匹配聯合索引順序。

問題 20：有 A(id,sex,par,c1,c2),B(id,age,c1,c2) 兩張表，其中 A.id 與 B.id 關聯，現在要求寫出一條 SQL 語句，將 B 中 age>50 的記錄的 c1,c2 更新到 A 表中同一記錄中的 c1,c2 字段中
考點分析
這道題主要考察的是 MySQL 的關聯 UPDATE 語句
延伸考點：

• MySQL 的關聯查詢語句

• MySQL 的關聯 UPDATE 語句

針對剛纔這道題，答案可以是如下兩種形式的寫法：

UPDATE A,B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id UPDATE A INNER JOIN B ON A.id=B.id SET A.c1 = B.c1,A.c2=B.c2 _--再加上B中age>50的條件：_ UPDATE A,B set A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id and B.age > 50; UPDATE A INNER JOIN B ON A.id = B.id set A.c1 = B.c1,A.c2 = B.c2 WHERE B.age > 50

MySQL 的關聯查詢語句

六種關聯查詢

• 交叉連接（CROSS JOIN）

• 內連接（INNER JOIN）

• 外連接（LEFT JOIN/RIGHT JOIN）

• 聯合查詢（UNION 與 UNION ALL）

• 全連接（FULL JOIN）

• 交叉連接（CROSS JOIN）

SELECT * FROM A,B(,C)或者 SELECT * FROM A CROSS JOIN B (CROSS JOIN C) #沒有任何關聯條件，結果是笛卡爾積，結果集會很大，沒有意義，很少使用 內連接（INNER JOIN） SELECT * FROM A,B WHERE A.id=B.id或者 SELECT * FROM A INNER JOIN B ON A.id=B.id 多表中同時符合某種條件的數據記錄的集合，INNER JOIN可以縮寫爲JOIN

內連接分爲三類

• 等值連接：ON A.id=B.id

• 不等值連接：ON A.id > B.id

• 自連接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外連接（LEFT JOIN/RIGHT JOIN）

• 左外連接：LEFT OUTER JOIN, 以左表爲主，先查詢出左表，按照 ON 後的關聯條件匹配右表，沒有匹配到的用 NULL 填充，可以簡寫成 LEFT JOIN

• 右外連接：RIGHT OUTER JOIN, 以右表爲主，先查詢出右表，按照 ON 後的關聯條件匹配左表，沒有匹配到的用 NULL 填充，可以簡寫成 RIGHT JOIN

聯合查詢（UNION 與 UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION ...

• 就是把多個結果集集中在一起，UNION 前的結果爲基準，需要注意的是聯合查詢的列數要相等，相同的記錄行會合並

• 如果使用 UNION ALL，不會合並重復的記錄行

• 效率 UNION 高於 UNION ALL

全連接（FULL JOIN）

• MySQL 不支持全連接

• 可以使用 LEFT JOIN 和 UNION 和 RIGHT JOIN 聯合使用

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id

嵌套查詢
用一條 SQL 語句得結果作爲另外一條 SQL 語句得條件，效率不好把握
SELECT * FROM A WHERE id IN (SELECT id FROM B)

解題方法

根據考題要搞清楚表的結果和多表之間的關係，根據想要的結果思考使用那種關聯方式，通常把要查詢的列先寫出來，然後分析這些列都屬於哪些表，才考慮使用關聯查詢

問題 21：
爲了記錄足球比賽的結果，設計表如下：
team：參賽隊伍表
match：賽程表
其中，match 賽程表中的 hostTeamID 與 guestTeamID 都和 team 表中的 teamID 關聯，查詢 2006-6-1 到 2006-7-1 之間舉行的所有比賽，並且用以下形式列出：拜仁 2:0 不萊梅 2006-6-21

首先列出需要查詢的列：

• 表 team

• teamID teamName

• 表 match

• match ID

• hostTeamID

• guestTeamID

• matchTime matchResult

其次列出結果列：

• 主隊 結果 客對 時間

初步寫一個基礎的 SQL：

SELECT hostTeamID,matchResult,matchTime guestTeamID from match where matchTime between "2006-6-1" and "2006-7-1";

通過外鍵聯表，完成最終 SQL：

select t1.teamName,m.matchResult,t2.teamName,m.matchTime from match as m left join team as t1 on m.hostTeamID = t1.teamID, left join team t2 on m.guestTeamID=t2.guestTeamID where m.matchTime between "2006-6-1" and "2006-7-1"

問題 22：UNION 與 UNION ALL 的區別？

• 如果使用 UNION ALL，不會合並重復的記錄行

• 效率 UNION 高於 UNION ALL

問題 23：一個 6 億的表 a，一個 3 億的表 b，通過外鍵 tid 關聯，你如何最快的查詢出滿足條件的第 50000 到第 50200 中的這 200 條數據記錄。

• 1、如果 A 表 TID 是自增長, 並且是連續的, B 表的 ID 爲索引

select * from a,b where a.tid = b.id and a.tid>50000 limit 200;

• 2、如果 A 表的 TID 不是連續的, 那麼就需要使用覆蓋索引. TID 要麼是主鍵, 要麼是輔助索引, B 表 ID 也需要有索引。

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

問題 24：拷貝表 (拷貝數據, 源表名：a 目標表名：b)

insert into b(a, b, c) select d,e,f from a;

問題 25： Student(S#,Sname,Sage,Ssex) 學生表 Course(C#,Cname,T#) 課程表 SC(S#,C#,score) 成績表 Teacher(T#,Tname) 教師表 查詢沒學過 “葉平” 老師課的同學的學號、姓名

select Student.S#,Student.Sname from Student where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’葉平’);

問題 26：隨機取出 10 條數據

SELECT * FROM users WHERE id >= ((SELECT MAX(id) FROM users)-(SELECT MIN(id) FROM users)) * RAND() + (SELECT MIN(id) FROM users) LIMIT 10 #此方法效率比直接用SELECT * FROM users order by rand() LIMIT 10高很多

問題 27：請簡述項目中優化 SQL 語句執行效率的方法，從哪些方面，SQL 語句性能如何分析？
考點分析：
這道題主要考察的是查找分析 SQL 語句查詢速度慢的方法

延伸考點：

• 優化查詢過程中的數據訪問

• 優化長難的查詢語句

• 優化特定類型的查詢語句

如何查找查詢速度慢的原因
記錄慢查詢日誌，分析查詢日誌，不要直接打開慢查詢日誌進行分析，這樣比較浪費時間和精力，可以使用 pt-query-digest 工具進行分析

使用 show profile

set profiling=1;``開啓，服務器上所有執行語句會記錄執行時間，存到臨時表中 show profiles show profile for query 臨時表ID

使用 show status

show status 會返回一些計數器，show global status 會查看所有服務器級別的所有計數
有時根據這些計數，可以推測出哪些操作代價較高或者消耗時間多

show processlist

觀察是否有大量線程處於不正常的狀態或特徵

最常問的 MySQL 面試題五——每個開發人員都應該知道

使用 explain

分析單條 SQL 語句

優化查詢過程中的數據訪問

• 訪問數據太多導致查詢性能下降

• 確定應用程序是否在檢索大量超過需要的數據，可能是太多行或列

• 確認 MySQL 服務器是否在分析大量不必要的數據行

• 避免犯如下 SQL 語句錯誤

• 查詢不需要的數據。解決辦法：使用 limit 解決

• 多表關聯返回全部列。解決辦法：指定列名

• 總是返回全部列。解決辦法：避免使用 SELECT *

• 重複查詢相同的數據。解決辦法：可以緩存數據，下次直接讀取緩存

• 是否在掃描額外的記錄。解決辦法：

• 使用 explain 進行分析，如果發現查詢需要掃描大量的數據，但只返回少數的行，可以通過如下技巧去優化：

• 使用索引覆蓋掃描，把所有的列都放到索引中，這樣存儲引擎不需要回表獲取對應行就可以返回結果。

• 改變數據庫和表的結構，修改數據表範式

• 重寫 SQL 語句，讓優化器可以以更優的方式執行查詢。

優化長難的查詢語句

• 一個複雜查詢還是多個簡單查詢

• MySQL 內部每秒能掃描內存中上百萬行數據，相比之下，響應數據給客戶端就要慢得多

• 使用盡可能小的查詢是好的，但是有時將一個大的查詢分解爲多個小的查詢是很有必要的。

• 切分查詢

• 將一個大的查詢分爲多個小的相同的查詢

• 一次性刪除 1000 萬的數據要比一次刪除 1 萬，暫停一會的方案更加損耗服務器開銷。

• 分解關聯查詢，讓緩存的效率更高。

• 執行單個查詢可以減少鎖的競爭。

• 在應用層做關聯更容易對數據庫進行拆分。

• 查詢效率會有大幅提升。

• 較少冗餘記錄的查詢。

優化特定類型的查詢語句

• count(*) 會忽略所有的列，直接統計所有列數，不要使用 count(列名)

• MyISAM 中，沒有任何 where 條件的 count(*) 非常快。

• 當有 where 條件時，MyISAM 的 count 統計不一定比其它引擎快。

• 可以使用 explain 查詢近似值，用近似值替代 count(*)

• 增加彙總表

• 使用緩存

優化關聯查詢

• 確定 ON 或者 USING 子句中是否有索引。

• 確保 GROUP BY 和 ORDER BY 只有一個表中的列，這樣 MySQL 纔有可能使用索引。

優化子查詢

• 用關聯查詢替代

• 優化 GROUP BY 和 DISTINCT

• 這兩種查詢據可以使用索引來優化，是最有效的優化方法

• 關聯查詢中，使用標識列分組的效率更高

• 如果不需要 ORDER BY，進行 GROUP BY 時加 ORDER BY NULL，MySQL 不會再進行文件排序。

• WITH ROLLUP 超級聚合，可以挪到應用程序處理

優化 LIMIT 分頁

• LIMIT 偏移量大的時候，查詢效率較低

• 可以記錄上次查詢的最大 ID，下次查詢時直接根據該 ID 來查詢

優化 UNION 查詢

• UNION ALL 的效率高於 UNION

優化 WHERE 子句

解題方法

對於此類考題，先說明如何定位低效 SQL 語句，然後根據 SQL 語句可能低效的原因做排查，先從索引着手，如果索引沒有問題，考慮以上幾個方面，數據訪問的問題，長難查詢句的問題還是一些特定類型優化的問題，逐一回答。

SQL 語句優化的一些方法？

• 1. 對查詢進行優化，應儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
• 2. 應儘量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null``可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：``select id from t where num=

• 3. 應儘量避免在 where 子句中使用!= 或 <> 操作符，否則引擎將放棄使用索引而進行全表掃描。
• 4. 應儘量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20``可以這樣查詢：``select id from t where num=10 union all select id from t where num=20

• 5.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3) ``對於連續的數值，能用 between 就不要用 in 了：``select id from t where num between 1 and 3

• 6. 下面的查詢也將導致全表掃描：select id from t where name like ‘% 李 %’若要提高效率，可以考慮全文檢索。
• 7. 如果在 where 子句中使用參數，也會導致全表掃描。因爲 SQL 只有在運行時纔會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作爲索引選擇的輸入項。如下面語句將進行全表掃描：

select id from t where num=@num``可以改爲強制查詢使用索引：``select id from t with(index(索引名)) where num=@num

• 8. 應儘量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100``應改爲:``select id from t where num=100*2

• 9. 應儘量避免在 where 子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)=’abc’ ，name``以abc開頭的id應改爲: select id from t where name like ‘abc%’

• 10. 不要在 where 子句中的 “=” 左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/sEhvinIua6wdTNjnwB7N0g