mysql數據庫面試題大全

• 第03章 數據庫
• • 項目經理(作者)：張明星
• 基礎
• • SQL語言共分為四大類
  • 數據查詢語言DQL
  • 數據操縱語言DML
  • 數據定義語言DDL
  • 數據控制語言DCL
  • where、jion、limit、group by、having等執行先后順序?
  • 觸發器的作用？
  • 什么是存儲過程？用什么來調用？**
  • 存儲過程的優缺點？
  • 什么叫視圖？游標是什么？
  • 視圖的優缺點
  • drop、truncate、 delete區別
  • count(\*)、count(1)、count(column)的區別
  • 什么是臨時表，臨時表什么時候刪除?
  • like %和-的區別
  • 非關系型數據庫和關系型數據庫區別，優勢比較?
  • 數據庫范式，根據某個場景設計數據表?
  • 什么是 內連接、外連接、交叉連接、笛卡爾積等?**
  • MySQL 的邏輯架構了解嗎？
  • MVCC 是什么？
  • 查詢執行流程是什么？
• 存儲引擎
• • InnoDB、MyISAM、MEMORY的區別?
  • MyISAM與InnoDB如何選擇
  • MyISAM與InnoDB的特征
  • 談一談 InnoDB
  • 談一談 MyISAM
  • 談一談 Memory
• 事務
• • 事務是什么?
  • 事務有什么特性？
  • 事務四大特性（ACID）原子性、一致性、隔離性、持久性?
  • 事務的并發?事務隔離級別，每個級別會引發什么問題，MySQL默認是哪個級別?
  • MySQL 的隔離級別有哪些？
  • 事務傳播行為
  • 嵌套事務
• 數據類型
• • VARCHAR 和 CHAR 的區別？
  • varchar和char的使用場景?
  • DATETIME 和 TIMESTAMP 的區別？
  • 數據類型有哪些優化策略？
• 數據庫鎖
• • 談一談 MySQL 的讀寫鎖
  • MySQL 的鎖策略有什么？
  • 數據庫死鎖如何解決？
  • **mysql都有什么鎖，死鎖判定原理和具體場景，死鎖怎么解決?**
  • 有哪些鎖？select 時怎么加排它鎖?
  • 數據庫的主從復制
  • 數據庫主從復制分析的 7 個問題?
  • mysql 高并發環境解決方案?
  • 數據庫崩潰時事務的恢復機制（REDO日志和UNDO日志）?
• 索引
• • 什么是索引？
  • 索引的作用？它的優點缺點是什么？
  • 索引有什么作用？
  • 索引的優缺點？
  • 哪些列適合建立索引、哪些不適合建索引？
  • 什么樣的字段適合建索引
  • 主鍵、外鍵、超鍵、候選鍵
  • **為什么用自增列作為主鍵**
  • MySQL B+Tree索引和Hash索引的區別?
  • B樹和B+樹的區別
  • 為什么說B+比B樹更適合實際應用中操作系統的文件索引和數據庫索引？
  • 談一談 MySQL 的 B-Tree 索引
  • 了解 Hash 索引嗎？
  • 什么是自適應哈希索引？
  • 什么是空間索引？
  • 什么是全文索引？
  • 什么是聚簇索引？
  • 什么是覆蓋索引？
  • 你知道哪些索引使用原則？
  • **最左前綴原則**
  • 索引失效的情況有哪些？
  • **聚集索引和非聚集索引區別?**
• 優化
• • 如何定位低效 SQL？
  • SHOW PROFILE 的作用？
  • trace 是干什么的？
  • EXPLAIN 的字段有哪些，具有什么含義？
  • **使用explain優化sql和索引?**
  • 有哪些優化 SQL 的策略？
  • MySQL 主從復制的作用？
  • MySQL 主從復制的步驟？

第03章 數據庫

項目經理(作者)：張明星

基礎

SQL語言共分為四大類

• 數據查詢語言DQL
• 數據操縱語言DML
• 數據定義語言DDL
• 數據控制語言DCL。

數據查詢語言DQL

數據查詢語言DQL基本結構是由SELECT子句，FROM子句，WHERE子句組成的查詢塊：

SELECT FROM WHERE

數據操縱語言DML

數據操縱語言DML主要有三種形式：

插入：INSERT

更新：UPDATE

刪除：DELETE

數據定義語言DDL

數據定義語言DDL用來創建數據庫中的各種對象-----表、視圖、索引、同義詞、聚簇等如：

• CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

• DDL操作是隱性提交的！不能rollback

數據控制語言DCL

數據控制語言DCL用來授予或回收訪問數據庫的某種特權，并控制數據庫操縱事務發生的時間及效果，對數據庫實行監視等。

如：

GRANT：授權。

ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一點?；貪L—ROLLBACK；回滾命令使數據庫狀態回到上次最后提交的狀態。其格式為：
SQL>ROLLBACK;

COMMIT [WORK]：提交。

在數據庫的插入、刪除和修改操作時，只有當事務在提交到數據庫時才算完成。

在事務提交前，只有操作數據庫的這個人才能有權看到所做的事情，別人只有在最后提交完成后才可以看到。

提交數據有三種類型：顯式提交、隱式提交及自動提交。

下面分別說明這三種類型。

• (1) 顯式提交
  • 用COMMIT命令直接完成的提交為顯式提交。
  • 其格式為：SQL>COMMIT；
• (2) 隱式提交
  • 用SQL命令間接完成的提交為隱式提交。這些命令是：
    ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
    EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
• (3) 自動提交
  • 若把AUTOCOMMIT設置為ON，則在插入、修改、刪除語句執行后，系統將自動進行提交，這就是自動提交。
  • 其格式為：SQL>SET AUTOCOMMIT ON；

where、jion、limit、group by、having等執行先后順序?

1.查詢中用到的關鍵詞主要包含六個，并且他們的順序依次為 select–from–where–group by–having–order by

其中select和from是必須的，其他關鍵詞是可選的，這六個關鍵詞的執行順序 與sql語句的書寫順序并不是一樣的，而是按照下面的順序來執行

**from:**需要從哪個數據表檢索數據

**where:**過濾表中數據的條件

**group by:**如何將上面過濾出的數據分組

**having:**對上面已經分組的數據進行過濾的條件

**select:**查看結果集中的哪個列，或列的計算結果

**order by 😗*按照什么樣的順序來查看返回的數據

2.from后面的表關聯，是自右向左解析 而where條件的解析順序是自下而上的。

• 也就是說，在寫SQL語句的時候，盡量把數據量小的表放在最右邊來進行關聯（用小表去匹配大表），而把能篩選出小量數據的條件放在where語句的最左邊 （用小表去匹配大表）

觸發器的作用？

觸發器是一種特殊的存儲過程，主要是通過事件來觸發而被執行的。

它可以強化約束，來維護數據的完整性和一致性，可以跟蹤數據庫內的操作從而不允許未經許可的更新和變化。

可以聯級運算。

• 如，某表上的觸發器上包含對另一個表的數據操作，而該操作又會導致該表觸發器被觸發。

什么是存儲過程？用什么來調用？**

存儲過程是一個預編譯的SQL語句，優點是允許模塊化的設計，就是說只需創建一次，以后在該程序中就可以調用多次。如果某次操作需要執行多次SQL，使用存儲過程比單純SQL語句執行要快。

調用：

• 1）可以用一個命令對象來調用存儲過程。
• 2）可以供外部程序調用，比如：java程序。

存儲過程的優缺點？

優點：

• 1）存儲過程是預編譯過的，執行效率高。
• 2）存儲過程的代碼直接存放于數據庫中，通過存儲過程名直接調用，減少網絡通訊。
• 3）安全性高，執行存儲過程需要有一定權限的用戶。
• 4）存儲過程可以重復使用，可減少數據庫開發人員的工作量。

缺點：

• 移植性差

什么叫視圖？游標是什么？

視圖：

• 是一種虛擬的表，具有和物理表相同的功能?？梢詫σ晥D進行增，改，查，操作，試圖通常是有一個表或者多個表的行或列的子集。對視圖的修改會影響基本表。它使得我們獲取數據更容易，相比多表查詢。

游標：

• 是對查詢出來的結果集作為一個單元來有效的處理。游標可以定在該單元中的特定行，從結果集的當前行檢索一行或多行。可以對結果集當前行做修改。一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。

視圖的優缺點

優點：

• 1對數據庫的訪問，因為視圖可以有選擇性的選取數據庫里的一部分。
• 2)用戶通過簡單的查詢可以從復雜查詢中得到結果。
• 3)維護數據的獨立性，試圖可從多個表檢索數據。
• 4)對于相同的數據可產生不同的視圖。

缺點：

• 查詢視圖時，必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個復雜的多表查詢所定義，那么，那么就無法更改數據

drop、truncate、 delete區別

最基本：

• drop直接刪掉表。
• truncate刪除表中數據，再插入時自增長id又從1開始。
• delete刪除表中數據，可以加where字句。

（1） DELETE語句執行刪除的過程是每次從表中刪除一行，并且同時將該行的刪除操作作為事務記錄在日志中保存以便進行進行回滾操作。TRUNCATE TABLE 則一次性地從表中刪除所有的數據并不把單獨的刪除操作記錄記入日志保存，刪除行是不能恢復的。并且在刪除的過程中不會激活與表有關的刪除觸發器。執行速度快。

（2） 表和索引所占空間。當表被TRUNCATE 后，這個表和索引所占用的空間會恢復到初始大小，而DELETE操作不會減少表或索引所占用的空間。drop語句將表所占用的空間全釋放掉。

（3） 一般而言，drop > truncate > delete

（4） 應用范圍。TRUNCATE 只能對TABLE；DELETE可以是table和view

（5） TRUNCATE 和DELETE只刪除數據，而DROP則刪除整個表（結構和數據）。

（6） truncate與不帶where的delete ：只刪除數據，而不刪除表的結構（定義）drop語句將刪除表的結構被依賴的約束（constrain),觸發器（trigger)索引（index);依賴于該表的存儲過程/函數將被保留，但其狀態會變為：invalid。

（7） delete語句為DML（data maintain Language),這個操作會被放到 rollback segment中,事務提交后才生效。如果有相應的 tigger,執行的時候將被觸發。

（8） truncate、drop是DLL（data define language),操作立即生效，原數據不放到 rollback segment中，不能回滾。

（9） 在沒有備份情況下，謹慎使用 drop 與 truncate。要刪除部分數據行采用delete且注意結合where來約束影響范圍?；貪L段要足夠大。要刪除表用drop;若想保留表而將表中數據刪除，如果于事務無關，用truncate即可實現。如果和事務有關，或老師想觸發trigger,還是用delete。

（10） Truncate table 表名 速度快,而且效率高,因為:?truncate table 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。DELETE 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表數據所用的數據頁來刪除數據，并且只在事務日志中記錄頁的釋放。

（11） TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其數據，請使用 DROP TABLE 語句。

（12） 對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發器。

count(*)、count(1)、count(column)的區別

• count(*)對行的數目進行計算,包含NULL
• count(column)對特定的列的值具有的行數進行計算,不包含NULL值。
• count()還有一種使用方式,count(1)這個用法和count(*)的結果是一樣的。

性能問題:

• 1.任何情況下SELECT COUNT(*) FROM tablename是最優選擇;
• 2.盡量減少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 這種查詢;
• 3.杜絕SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出現。
  • 如果表沒有主鍵,那么count(1)比count(*)快。
  • 如果有主鍵,那么count(主鍵,聯合主鍵)比count(*)快。
  • 如果表只有一個字段,count(*)最快。

count(1)跟count(主鍵)一樣,只掃描主鍵。count(*)跟count(非主鍵)一樣,掃描整個表。明顯前者更快一些。

什么是臨時表，臨時表什么時候刪除?

臨時表可以手動刪除：

• DROP TEMPORARY TABLE IF EXISTS temp_tb;

臨時表只在當前連接可見，當關閉連接時，MySQL會自動刪除表并釋放所有空間。因此在不同的連接中可以創建同名的臨時表，并且操作屬于本連接的臨時表。

創建臨時表的語法與創建表語法類似，不同之處是增加關鍵字TEMPORARY

如：

CREATE TEMPORARY TABLE tmp_table (NAME VARCHAR (10) NOT NULL,time date NOT NULL ); select * from tmp_table;

like %和-的區別

通配符的分類:

• **%百分號通配符:**表示任何字符出現任意次數(可以是0次).
• **_下劃線通配符:**表示只能匹配單個字符,不能多也不能少,就是一個字符.
• like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配進行比較.

注意: 如果在使用like操作符時,后面的沒有使用通用匹配符效果是和=一致的,

SELECT * FROM products WHERE products.prod_name like '1000';

• 只能匹配的結果為1000,而不能匹配像JetPack 1000這樣的結果.

%通配符使用:

• 匹配以"yves"開頭的記錄:(包括記錄"yves")
  • SELECT *FROM products WHERE products.prod_name like ‘yves%’;
• 匹配包含"yves"的記錄(包括記錄"yves") *
  • SELECT FROM products WHERE products.prod_name like ‘%yves%’;
• 匹配以"yves"結尾的記錄(包括記錄"yves",不包括記錄"yves ",也就是yves后面有空格的記錄,這里需要注意)
  • SELECT * FROM products WHERE products.prod_name like ‘%yves’;

*通配符使用:

• 匹配結果為: 像"yyves"這樣記錄
  • SELECT *FROM products WHERE products.prod_name like ‘_yves’;
• 匹配結果為: 像"yvesHe"這樣的記錄.(一個下劃線只能匹配一個字符,不能多也不能少)
• SELECT* FROM products WHERE products.prod*name like ‘yves**’;

注意事項:

• 注意大小寫,在使用模糊匹配時,也就是匹配文本時,mysql是可能區分大小的,也可能是不區分大小寫的,這個結果是取決于用戶對MySQL的配置方式.如果是區分大小寫,那么像YvesHe這樣記錄是不能被"yves__"這樣的匹配條件匹配的.
• 注意尾部空格,"%yves"是不能匹配"heyves "這樣的記錄的.
• 注意NULL,%通配符可以匹配任意字符,但是不能匹配NULL,也就是說SELECT * FROM products WHERE products.prod_name like '%;是匹配不到products.prod_name為NULL的的記錄.

技巧與建議:

正如所見， MySQL的通配符很有用。但這種功能是有代價的：通配符搜索的處理一般要比前面討論的其他搜索所花時間更長。

這里給出一些使用通配符要記住的技巧。

• 不要過度使用通配符。如果其他操作符能達到相同的目的，應該 使用其他操作符。
• 在確實需要使用通配符時，除非絕對有必要，否則不要把它們用 在搜索模式的開始處。把通配符置于搜索模式的開始處，搜索起 來是最慢的。
• 仔細注意通配符的位置。如果放錯地方，可能不會返回想要的數.

非關系型數據庫和關系型數據庫區別，優勢比較?

非關系型數據庫的優勢：

• **性能：**NOSQL是基于鍵值對的，可以想象成表中的主鍵和值的對應關系，而且不需要經過SQL層的解析，所以性能非常高。
• **可擴展性：**同樣也是因為基于鍵值對，數據之間沒有耦合性，所以非常容易水平擴展。

關系型數據庫的優勢：

• **復雜查詢：**可以用SQL語句方便的在一個表以及多個表之間做非常復雜的數據查詢。
• **事務支持：**使得對于安全性能很高的數據訪問要求得以實現。

其他：

• **1.**對于這兩類數據庫，對方的優勢就是自己的弱勢，反之亦然。
• **2.**NOSQL數據庫慢慢開始具備SQL數據庫的一些復雜查詢功能，比如MongoDB。
• **3.**對于事務的支持也可以用一些系統級的原子操作來實現例如樂觀鎖之類的方法來曲線救國，比如Redis set nx。

數據庫范式，根據某個場景設計數據表?

第一范式:

(確保每列保持原子性)所有字段值都是不可分解的原子值。

• 第一范式是最基本的范式。如果數據庫表中的所有字段值都是不可分解的原子值，就說明該數據庫表滿足了第一范式。
• 第一范式的合理遵循需要根據系統的實際需求來定。比如某些數據庫系統中需要用到“地址”這個屬性，本來直接將“地址”屬性設計成一個數據庫表的字段就行。
• 但是如果系統經常會訪問“地址”屬性中的“城市”部分，那么就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行存儲，這樣在對地址中某一部分操作的時候將非常方便。這樣設計才算滿足了數據庫的第一范式。

第二范式:

(確保表中的每列都和主鍵相關)在一個數據庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張數據庫表中。

• 第二范式在第一范式的基礎之上更進一層。
• 第二范式需要確保數據庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個數據庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張數據庫表中。
• 比如要設計一個訂單信息表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為數據庫表的聯合主鍵。

第三范式:

(確保每列都和主鍵列直接相關,而不是間接相關) 數據表中的每一列數據都和主鍵直接相關，而不能間接相關。

• 第三范式需要確保數據表中的每一列數據都和主鍵直接相關，而不能間接相關。
• 比如在設計一個訂單數據表的時候，可以將客戶編號作為一個外鍵和訂單表建立相應的關系。
• 而不可以在訂單表中添加關于客戶其它信息（比如姓名、所屬公司等）的字段。

**BCNF:**符合3NF，并且，主屬性不依賴于主屬性。

• 若關系模式屬于第二范式，且每個屬性都不傳遞依賴于鍵碼，則R屬于BC范式。
• 通常BC范式的條件有多種等價的表述：每個非平凡依賴的左邊必須包含鍵碼；每個決定因素必須包含鍵碼。
• BC范式既檢查非主屬性，又檢查主屬性。當只檢查非主屬性時，就成了第三范式。滿足BC范式的關系都必然滿足第三范式。
• 還可以這么說：若一個關系達到了第三范式，并且它只有一個候選碼，或者它的每個候選碼都是單屬性，則該關系自然達到BC范式。
• 一般，一個數據庫設計符合3NF或BCNF就可以了。

第四范式:

要求把同一表內的多對多關系刪除。

第五范式:

從最終結構重新建立原始結構。

什么是 內連接、外連接、交叉連接、笛卡爾積等?**

內連接: 只連接匹配的行

左外連接: 包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行

右外連接: 包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行

• 例如1：SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b ON a.username=b.username
• 例如2：SELECT a.*,b.* FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

全外連接: 包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們匹配的行。

交叉連接: 生成笛卡爾積－它不使用任何匹配或者選取條件，而是直接將一個數據源中的每個行與另一個數據源的每個行都一一匹配

• 例如：SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

MySQL 的邏輯架構了解嗎？

第一層是服務器層，主要提供連接處理、授權認證、安全等功能。

第二層實現了 MySQL 核心服務功能，包括查詢解析、分析、優化、緩存以及日期和時間等所有內置函數，所有跨存儲引擎的功能都在這一層實現，例如存儲過程、觸發器、視圖等。

第三層是存儲引擎層，存儲引擎負責 MySQL 中數據的存儲和提取。服務器通過 API 與存儲引擎通信，這些接口屏蔽了不同存儲引擎的差異，使得差異對上層查詢過程透明。除了會解析外鍵定義的 InnoDB 外，存儲引擎不會解析 SQL，不同存儲引擎之間也不會相互通信，只是簡單響應上層服務器請求。

MVCC 是什么？

MVCC 是多版本并發控制，在很多情況下避免加鎖，大都實現了非阻塞的讀操作，寫操作也只鎖定必要的行。

InnoDB 的MVCC 通過在每行記錄后面保存兩個隱藏的列來實現，這兩個列一個保存了行的創建時間，一個保存行的過期時間間。不過存儲的不是實際的時間值而是系統版本號，每開始一個新的事務系統版本號都會自動遞增，事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。

MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 兩個隔離級別下工作，因為 READ UNCOMMITTED 總是讀取最新的數據行，而不是符合當前事務版本的數據行，而 SERIALIZABLE 則會對所有讀取的行都加鎖。

查詢執行流程是什么？

簡單來說分為五步：

① 客戶端發送一條查詢給服務器。

② 服務器先檢查查詢緩存，如果命中了緩存則立刻返回存儲在緩存中的結果，否則進入下一階段。

③ 服務器端進行 SQL 解析、預處理，再由優化器生成對應的執行計劃。

④ MySQL 根據優化器生成的執行計劃，調用存儲引擎的 API 來執行查詢。

⑤ 將結果返回給客戶端。

存儲引擎

InnoDB、MyISAM、MEMORY的區別?

兩種存儲引擎的大致區別表現在：

• 1.InnoDB支持事務，MyISAM不支持， **這一點是非常之重要。**事務是一種高級的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM就不可以了。
• 2.MyISAM適合查詢以及插入為主的應用。
• 3.InnoDB適合頻繁修改以及涉及到安全性較高的應用。
• 4.InnoDB支持外鍵，MyISAM不支持。
• 5.從MySQL5.5.5以后，InnoDB是默認引擎。
• 6.InnoDB不支持FULLTEXT類型的索引。
• 7.InnoDB中不保存表的行數，如select count() from table時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count()語句包含where條件時MyISAM也需要掃描整個表。
• 8.對于自增長的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯合索引。
• 9.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的 刪除，效率非常慢。MyISAM則會重建表。
• 10.InnoDB支持行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like ‘%lee%’。

MyISAM與InnoDB如何選擇

MySQL有多種存儲引擎，每種存儲引擎有各自的優缺點，可以擇優選擇使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

雖然MySQL里的存儲引擎不只是MyISAM與InnoDB這兩個，但常用的就是兩個。
關于MySQL數據庫提供的兩種存儲引擎，MyISAM與InnoDB選擇使用：

• 1.INNODB會支持一些關系數據庫的高級功能，如事務功能和行級鎖，MyISAM不支持。
• 2.MyISAM的性能更優，占用的存儲空間少，所以，選擇何種存儲引擎，視具體應用而定。

如果你的應用程序一定要使用事務，毫無疑問你要選擇INNODB引擎。但要注意，INNODB的行級鎖是有條件的。在where條件沒有使用主鍵時，照樣會鎖全表。比如DELETE FROM mytable這樣的刪除語句。

如果你的應用程序對查詢性能要求較高，就要使用MyISAM了。MyISAM索引和數據是分開的，而且其索引是壓縮的，可以更好地利用內存。所以它的查詢性能明顯優于INNODB。壓縮后的索引也能節約一些磁盤空間。MyISAM擁有全文索引的功能，這可以極大地優化LIKE查詢的效率。

有人說MyISAM只能用于小型應用，其實這只是一種偏見。如果數據量比較大，這是需要通過升級架構來解決，比如分表分庫，而不是單純地依賴存儲引擎。

現在一般都是選用innodb了，主要是MyISAM的全表鎖，讀寫串行問題，并發效率鎖表，效率低，MyISAM對于讀寫密集型應用一般是不會去選用的。
MEMORY存儲引擎

MEMORY是MySQL中一類特殊的存儲引擎。它使用存儲在內存中的內容來創建表，而且數據全部放在內存中。這些特性與前面的兩個很不同。
每個基于MEMORY存儲引擎的表實際對應一個磁盤文件。該文件的文件名與表名相同，類型為frm類型。該文件中只存儲表的結構。而其數據文件，都是存儲在內存中，這樣有利于數據的快速處理，提高整個表的效率。值得注意的是，服務器需要有足夠的內存來維持MEMORY存儲引擎的表的使用。如果不需要了，可以釋放內存，甚至刪除不需要的表。

MEMORY默認使用哈希索引。速度比使用B型樹索引快。當然如果你想用B型樹索引，可以在創建索引時指定。

注意，MEMORY用到的很少，因為它是把數據存到內存中，如果內存出現異常就會影響數據。如果重啟或者關機，所有數據都會消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

MyISAM與InnoDB的特征

事務處理上方面

• MyISAM：強調的是性能，每次查詢具有原子性,其執行數度比InnoDB類型更快，但是不提供事務支持。
• InnoDB：提供事務支持事務，外部鍵等高級數據庫功能。 具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

鎖級別

• MyISAM：只支持表級鎖，用戶在操作MyISAM表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發的情況下，可以在表的尾部插入新的數據。
• InnoDB：支持事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多用戶并發操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。

談一談 InnoDB

InnoDB 是 MySQL 的默認事務型引擎，用來處理大量短期事務。InnoDB 的性能和自動崩潰恢復特性使得它在非事務型存儲需求中也很流行，除非有特別原因否則應該優先考慮 InnoDB。

InnoDB 的數據存儲在表空間中，表空間由一系列數據文件組成。MySQL4.1 后 InnoDB 可以將每個表的數據和索引放在單獨的文件中。

InnoDB 采用 MVCC 來支持高并發，并且實現了四個標準的隔離級別。其默認級別是 REPEATABLE READ，并通過間隙鎖策略防止幻讀，間隙鎖使 InnoDB 不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定防止幻行的插入。

InnoDB 表是基于聚簇索引建立的，InnoDB 的索引結構和其他存儲引擎有很大不同，聚簇索引對主鍵查詢有很高的性能，不過它的二級索引中必須包含主鍵列，所以如果主鍵很大的話其他所有索引都會很大，因此如果表上索引較多的話主鍵應當盡可能小。

InnoDB 的存儲格式是平***立的，可以將數據和索引文件從一個平臺復制到另一個平臺。

InnoDB 內部做了很多優化，包括從磁盤讀取數據時采用的可預測性預讀，能夠自動在內存中創建加速讀操作的自適應哈希索引，以及能夠加速插入操作的插入緩沖區等。

談一談 MyISAM

MySQL5.1及之前，MyISAM 是默認存儲引擎，MyISAM 提供了大量的特性，包括全文索引、壓縮、空間函數等，但不支持事務和行鎖，最大的缺陷就是崩潰后無法安全恢復。對于只讀的數據或者表比較小、可以忍受修復操作的情況仍然可以使用 MyISAM。

MyISAM 將表存儲在數據文件和索引文件中，分別以 .MYD 和 .MYI 作為擴展名。MyISAM 表可以包含動態或者靜態行，MySQL 會根據表的定義決定行格式。MyISAM 表可以存儲的行記錄數一般受限于可用磁盤空間或者操作系統中單個文件的最大尺寸。

MyISAM 對整張表進行加鎖，讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排它鎖。但是在表有讀取查詢的同時，也支持并發往表中插入新的記錄。

對于MyISAM 表，MySQL 可以手動或自動執行檢查和修復操作，這里的修復和事務恢復以及崩潰恢復的概念不同。執行表的修復可能導致一些數據丟失，而且修復操作很慢。

對于 MyISAM 表，即使是 BLOB 和 TEXT 等長字段，也可以基于其前 500 個字符創建索引。MyISAM 也支持全文索引，這是一種基于分詞創建的索引，可以支持復雜的查詢。

MyISAM 設計簡單，數據以緊密格式存儲，所以在某些場景下性能很好。MyISAM 最典型的性能問題還是表鎖問題，如果所有的查詢長期處于 Locked 狀態，那么原因毫無疑問就是表鎖。

談一談 Memory

如果需要快速訪問數據且這些數據不會被修改，重啟以后丟失也沒有關系，那么使用 Memory 表是非常有用的。Memory 表至少要比 MyISAM 表快一個數量級，因為所有數據都保存在內存，不需要磁盤 IO，Memory 表的結構在重啟后會保留，但數據會丟失。

Memory 表適合的場景：查找或者映射表、緩存周期性聚合數據的結果、保存數據分析中產生的中間數據。

Memory 表支持哈希索引，因此查找速度極快。雖然速度很快但還是無法取代傳統的基于磁盤的表，Memory 表使用表級鎖，因此并發寫入的性能較低。它不支持 BLOB 和 TEXT 類型的列，并且每行的長度是固定的，所以即使指定了 VARCHAR 列，實際存儲時也會轉換成CHAR，這可能導致部分內存的浪費。

如果 MySQL 在執行查詢的過程中需要使用臨時表來保持中間結果，內部使用的臨時表就是 Memory 表。如果中間結果太大超出了Memory 表的限制，或者含有 BLOB 或 TEXT 字段，臨時表會轉換成 MyISAM 表。

事務

事務是什么?

事務是一組原子性的 SQL 查詢，或者說一個獨立的工作單元。如果數據庫引擎能夠成功地對數據庫應用該組查詢的全部語句，那么就執行該組查詢。

如果其中有任何一條語句因為崩潰或其他原因無法執行，那么所有的語句都不會執行。也就是說事務內的語句要么全部執行成功，要么全部執行失敗。

事務有什么特性？

原子性 atomicity

一個事務在邏輯上是必須不可分割的最小工作單元，整個事務中的所有操作要么全部提交成功，要么全部失敗回滾，對于一個事務來說不可能只執行其中的一部分。

一致性 consistency

數據庫總是從一個一致性的狀態轉換到另一個一致性的狀態。

隔離性 isolation

針對并發事務而言，隔離性就是要隔離并發運行的多個事務之間的相互影響，一般來說一個事務所做的修改在最終提交以前，對其他事務是不可見的。

持久性 durability

一旦事務提交成功，其修改就會永久保存到數據庫中，此時即使系統崩潰，修改的數據也不會丟失。

事務四大特性（ACID）原子性、一致性、隔離性、持久性?

原子性（Atomicity）:

• 原子性是指事務包含的所有操作要么全部成功，要么全部失敗回滾，因此事務的操作如果成功就必須要完全應用到數據庫，如果操作失敗則不能對數據庫有任何影響。

一致性（Consistency）:

• 事務開始前和結束后，數據庫的完整性約束沒有被破壞。
• 比如A向B轉賬，不可能A扣了錢，B卻沒收到。

隔離性（Isolation）:

• 隔離性是當多個用戶并發訪問數據庫時，比如操作同一張表時，數據庫為每一個用戶開啟的事務，不能被其他事務的操作所干擾，多個并發事務之間要相互隔離。
• 同一時間，只允許一個事務請求同一數據，不同的事務之間彼此沒有任何干擾。
• 比如A正在從一張銀行卡中取錢，在A取錢的過程結束前，B不能向這張卡轉賬。

持久性（Durability）:

• 持久性是指一個事務一旦被提交了，那么對數據庫中的數據的改變就是永久性的，即便是在數據庫系統遇到故障的情況下也不會丟失提交事務的操作。

事務的并發?事務隔離級別，每個級別會引發什么問題，MySQL默認是哪個級別?

從理論上來說, 事務應該彼此完全隔離, 以避免并發事務所導致的問題，然而, 那樣會對性能產生極大的影響, 因為事務必須按順序運行， 在實際開發中, 為了提升性能, 事務會以較低的隔離級別運行， 事務的隔離級別可以通過隔離事務屬性指定。
事務的并發問題

• **1、臟讀：**事務A讀取了事務B更新的數據，然后B回滾操作，那么A讀取到的數據是臟數據
• **2、不可重復讀：**事務 A 多次讀取同一數據，事務 B 在事務A多次讀取的過程中，對數據作了更新并提交，導致事務A多次讀取同一數據時，結果因此本事務先后兩次讀到的數據結果會不一致。
• **3、幻讀：**幻讀解決了不重復讀，保證了同一個事務里，查詢的結果都是事務開始時的狀態（一致性）。
  • 例如：事務T1對一個表中所有的行的某個數據項做了從“1”修改為“2”的操作 這時事務T2又對這個表中插入了一行數據項，而這個數據項的數值還是為“1”并且提交給數據庫。 而操作事務T1的用戶如果再查看剛剛修改的數據，會發現還有跟沒有修改一樣，其實這行是從事務T2中添加的，就好像產生幻覺一樣，這就是發生了幻讀。
• 小結：不可重復讀的和幻讀很容易混淆，不可重復讀側重于修改，幻讀側重于新增或刪除。解決不可重復讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表。

事務的隔離級別

• **讀未提交：**另一個事務修改了數據，但尚未提交，而本事務中的SELECT會讀到這些未被提交的數據臟讀
• **不可重復讀：**事務 A 多次讀取同一數據，事務 B 在事務A多次讀取的過程中，對數據作了更新并提交，導致事務A多次讀取同一數據時，結果因此本事務先后兩次讀到的數據結果會不一致。
• **可重復讀：**在同一個事務里，SELECT的結果是事務開始時時間點的狀態，因此，同樣的SELECT操作讀到的結果會是一致的。但是，會有幻讀現象
• **串行化：**最高的隔離級別，在這個隔離級別下，不會產生任何異常。并發的事務，就像事務是在一個個按照順序執行一樣

特別注意：

• MySQL默認的事務隔離級別為repeatable-read
• MySQL 支持 4 中事務隔離級別.
• 事務的隔離級別要得到底層數據庫引擎的支持, 而不是應用程序或者框架的支持.
• Oracle 支持的 2 種事務隔離級別：READ_COMMITED , SERIALIZABLE
• SQL規范所規定的標準，不同的數據庫具體的實現可能會有些差異
• MySQL中默認事務隔離級別是“可重復讀”時并不會鎖住讀取到的行
  • **事務隔離級別：**未提交讀時，寫數據只會鎖住相應的行。
  • **事務隔離級別為：**可重復讀時，寫數據會鎖住整張表。
  • **事務隔離級別為：**串行化時，讀寫數據都會鎖住整張表。
• 隔離級別越高，越能保證數據的完整性和一致性，但是對并發性能的影響也越大，魚和熊掌不可兼得啊。對于多數應用程序，可以優先考慮把數據庫系統的隔離級別設為Read Committed，它能夠避免臟讀取，而且具有較好的并發性能。盡管它會導致不可重復讀、幻讀這些并發問題，在可能出現這類問題的個別場合，可以由應用程序采用悲觀鎖或樂觀鎖來控制。

MySQL 的隔離級別有哪些？

未提交讀 READ UNCOMMITTED

在該級別事務中的修改即使沒有被提交，對其他事務也是可見的。事務可以讀取其他事務修改完但未提交的數據，這種問題稱為臟讀。這個級別還會導致不可重復讀和幻讀，性能沒有比其他級別好很多，很少使用。

提交讀 READ COMMITTED

多數數據庫系統默認的隔離級別。提交讀滿足了隔離性的簡單定義：一個事務開始時只能"看見"已經提交的事務所做的修改。換句話說，一個事務從開始直到提交之前的任何修改對其他事務都是不可見的。也叫不可重復讀，因為兩次執行同樣的查詢可能會得到不同結果。

可重復讀 REPEATABLE READ（MySQL默認的隔離級別）

可重復讀解決了不可重復讀的問題，保證了在同一個事務中多次讀取同樣的記錄結果一致。但還是無法解決幻讀，所謂幻讀指的是當某個事務在讀取某個范圍內的記錄時，會產生幻行。InnoDB 存儲引擎通過多版本并發控制MVCC 解決幻讀的問題。

可串行化 SERIALIZABLE

最高的隔離級別，通過強制事務串行執行，避免幻讀?？纱谢瘯谧x取的每一行數據上都加鎖，可能導致大量的超時和鎖爭用的問題。實際應用中很少用到這個隔離級別，只有非常需要確保數據一致性且可以接受沒有并發的情況下才考慮該級別。

事務傳播行為

**1.PROPAGATION_REQUIRED：**如果當前沒有事務，就創建一個新事務，如果當前存在事務，就加入該事務，該設置是最常用的設置。

**2.PROPAGATION_SUPPORTS：**支持當前事務，如果當前存在事務，就加入該事務，如果當前不存在事務，就以非事務執行。

**3.PROPAGATION_MANDATORY：**支持當前事務，如果當前存在事務，就加入該事務，如果當前不存在事務，就拋出異常。

**4.PROPAGATION_REQUIRES_NEW：**創建新事務，無論當前存不存在事務，都創建新事務。

**5.PROPAGATION_NOT_SUPPORTED：**以非事務方式執行操作，如果當前存在事務，就把當前事務掛起。

**6.PROPAGATION_NEVER：**以非事務方式執行，如果當前存在事務，則拋出異常。

**7.PROPAGATION_NESTED：**如果當前存在事務，則在嵌套事務內執行。如果當前沒有事務，則執行與PROPAGATION_REQUIRED類似的操作。

嵌套事務

什么是嵌套事務？

• 嵌套是子事務套在父事務中執行，子事務是父事務的一部分，在進入子事務之前，父事務建立一個回滾點，叫save point，然后執行子事務，這個子事務的執行也算是父事務的一部分，然后子事務執行結束，父事務繼續執行。重點就在于那個save point。看幾個問題就明了了：

如果子事務回滾，會發生什么？

• 父事務會回滾到進入子事務前建立的save point，然后嘗試其他的事務或者其他的業務邏輯，父事務之前的操作不會受到影響，更不會自動回滾。

如果父事務回滾，會發生什么？

• 父事務回滾，子事務也會跟著回滾！為什么呢，因為父事務結束之前，子事務是不會提交的，我們說子事務是父事務的一部分，正是這個道理。那么：

事務的提交，是什么情況？

• 是父事務先提交，然后子事務提交，還是子事務先提交，父事務再提交？答案是第二種情況，還是那句話，子事務是父事務的一部分，由父事務統一提交。

數據類型

VARCHAR 和 CHAR 的區別？

VARCHAR 用于存儲可變字符串，是最常見的字符串數據類型。它比 CHAR 更節省空間，因為它僅使用必要的空間。VARCHAR 需要 1 或 2 個額外字節記錄字符串長度，如果列的最大長度不大于 255 字節則只需要 1 字節。VARCHAR 不會刪除末尾空格。

• VARCHAR 適用場景：字符串列的最大長度比平均長度大很多、列的更新很少、使用了 UTF8 這種復雜字符集，每個字符都使用不同的字節數存儲。

CHAR 是定長的，根據定義的字符串長度分配足夠的空間。CHAR 會刪除末尾空格。

• CHAR 適合存儲很短的字符串，或所有值都接近同一個長度，例如存儲密碼的 MD5 值。對于經常變更的數據，CHAR 也比 VARCHAR更好，因為定長的 CHAR 不容易產生碎片。對于非常短的列，CHAR 在存儲空間上也更有效率，例如用 CHAR 來存儲只有 Y 和 N 的值只需要一個字節，但是 VARCHAR 需要兩個字節，因為還有一個記錄長度的額外字節。

varchar和char的使用場景?

**1.**char的長度是不可變的，而varchar的長度是可變的。

• 定義一個char[10]和varchar[10]。
• 如果存進去的是‘csdn’,那么char所占的長度依然為10，除了字符‘csdn’外，后面跟六個空格，varchar就立馬把長度變為4了，取數據的時候，char類型的要用trim()去掉多余的空格，而varchar是不需要的。

**2.**char的存取數度還是要比varchar要快得多，因為其長度固定，方便程序的存儲與查找。

• char也為此付出的是空間的代價，因為其長度固定，所以難免會有多余的空格占位符占據空間，可謂是以空間換取時間效率。
  varchar是以空間效率為首位。

**3.**char的存儲方式是：對英文字符（ASCII）占用1個字節，對一個漢字占用兩個字節。

• varchar的存儲方式是：對每個英文字符占用2個字節，漢字也占用2個字節。

**4.**兩者的存儲數據都非unicode的字符數據。

DATETIME 和 TIMESTAMP 的區別？

DATETIME 能保存大范圍的值，

• 從 1001~9999 年，精度為秒。把日期和時間封裝到了一個整數中，與時區無關，使用 8 字節存儲空間。

TIMESTAMP 和 UNIX 時間戳相同，

• 只使用 4 字節的存儲空間，范圍比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依賴于時區。

數據類型有哪些優化策略？

更小的通常更好

• 一般情況下盡量使用可以正確存儲數據的最小數據類型，更小的數據類型通常也更快，因為它們占用更少的磁盤、內存和 CPU 緩存。

盡可能簡單

• 簡單數據類型的操作通常需要更少的 CPU 周期，例如整數比字符操作代價更低，因為字符集和校對規則使字符相比整形更復雜。
• 應該使用 MySQL 的內建類型 date、time 和 datetime 而不是字符串來存儲日期和時間，另一點是應該使用整形存儲 IP 地址。

盡量避免 NULL

• 通常情況下最好指定列為 NOT NULL，除非需要存儲 NULL值。因為如果查詢中包含可為 NULL 的列對 MySQL 來說更難優化，可為 NULL 的列使索引、索引統計和值比較都更復雜，并且會使用更多存儲空間。

• 當可為 NULL 的列被索引時，每個索引記錄需要一個額外字節，在MyISAM 中還可能導致固定大小的索引變成可變大小的索引。

• 如果計劃在列上建索引，就應該盡量避免設計成可為 NULL 的列。

數據庫鎖

談一談 MySQL 的讀寫鎖

在處理并發讀或寫時，可以通過實現一個由兩種類型組成的鎖系統來解決問題。這兩種類型的鎖通常被稱為共享鎖和排它鎖，也叫讀鎖和寫鎖。讀鎖是共享的，相互不阻塞，多個客戶在同一時刻可以同時讀取同一個資源而不相互干擾。寫鎖則是排他的，也就是說一個寫鎖會阻塞其他的寫鎖和讀鎖，確保在給定時間內只有一個用戶能執行寫入并防止其他用戶讀取正在寫入的同一資源。

在實際的數據庫系統中，每時每刻都在發生鎖定，當某個用戶在修改某一部分數據時，MySQL 會通過鎖定防止其他用戶讀取同一數據。寫鎖比讀鎖有更高的優先級，一個寫鎖請求可能會被插入到讀鎖隊列的前面，但是讀鎖不能插入到寫鎖前面。

MySQL 的鎖策略有什么？

表鎖是MySQL中最基本的鎖策略，并且是開銷最小的策略。表鎖會鎖定整張表，一個用戶在對表進行寫操作前需要先獲得寫鎖，這會阻塞其他用戶對該表的所有讀寫操作。只有沒有寫鎖時，其他讀取的用戶才能獲取讀鎖，讀鎖之間不相互阻塞。

行鎖可以最大程度地支持并發，同時也帶來了最大開銷。InnoDB 和 XtraDB 以及一些其他存儲引擎實現了行鎖。行鎖只在存儲引擎層實現，而服務器層沒有實現。

數據庫死鎖如何解決？

死鎖是指多個事務在同一資源上相互占用并請求鎖定對方占用的資源而導致惡性循環的現象。當多個事務試圖以不同順序鎖定資源時就可能會產生死鎖，多個事務同時鎖定同一個資源時也會產生死鎖。

為了解決死鎖問題，數據庫系統實現了各種死鎖檢測和死鎖超時機制。越復雜的系統，例如InnoDB 存儲引擎，越能檢測到死鎖的循環依賴，并立即返回一個錯誤。這種解決方式很有效，否則死鎖會導致出現非常慢的查詢。還有一種解決方法，就是當查詢的時間達到鎖等待超時的設定后放棄鎖請求，這種方式通常來說不太好。InnoDB 目前處理死鎖的方法是將持有最少行級排它鎖的事務進行回滾。

死鎖發生之后，只有部分或者完全回滾其中一個事務，才能打破死鎖。對于事務型系統這是無法避免的，所以應用程序在設計時必須考慮如何處理死鎖。大多數情況下只需要重新執行因死鎖回滾的事務即可。

mysql都有什么鎖，死鎖判定原理和具體場景，死鎖怎么解決?

**MySQL有三種鎖的級別：**頁級、表級、行級。

• **表級鎖：**開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高,并發度最低。
• **行級鎖：**開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高。
• **頁面鎖：**開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般
  什么情況下會造成死鎖?

什么是死鎖？

• 死鎖: 是指兩個或兩個以上的進程在執行過程中。因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去。此時稱系統處于死鎖狀態或系統產生了死鎖,這些永遠在互相等竺的進程稱為死鎖進程。
• 表級鎖不會產生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB。
• **死鎖的關鍵在于：**兩個(或以上)的Session加鎖的順序不一致。
• 那么對應的解決死鎖問題的關鍵就是：讓不同的session加鎖有次序。
• 死鎖的解決辦法?
  • 1.查出的線程殺死 kill
    SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;
  • 2.設置鎖的超時時間
    Innodb 行鎖的等待時間，單位秒?？稍跁捈墑e設置，RDS 實例該參數的默認值為 50（秒）。
  • 生產環境不推薦使用過大的 innodb_lock_wait_timeout參數值
    該參數支持在會話級別修改，方便應用在會話級別單獨設置某些特殊操作的行鎖等待超時時間，如下：
    set innodb_lock_wait_timeout=1000; —設置當前會話 Innodb 行鎖等待超時時間，單位秒。
  • 3.指定獲取鎖的順序

有哪些鎖？select 時怎么加排它鎖?

悲觀鎖（Pessimistic Lock）:

• **悲觀鎖特點:**先獲取鎖，再進行業務操作。

• 即“悲觀”的認為獲取鎖是非常有可能失敗的，因此要先確保獲取鎖成功再進行業務操作。通常所說的**“一鎖二查三更新”即指的是使用悲觀鎖。**

• 通常來講在數據庫上的悲觀鎖需要數據庫本身提供支持，即通過常用的select … for update操作來實現悲觀鎖。當數據庫執行select for update時會獲取被select中的數據行的行鎖，因此其他并發執行的select for update如果試圖選中同一行則會發生排斥（需要等待行鎖被釋放），因此達到鎖的效果。

• select for update獲取的行鎖會在當前事務結束時自動釋放，因此必須在事務中使用。

補充：
不同的數據庫對select for update的實現和支持都是有所區別的，

• oracle支持select for update no wait，表示如果拿不到鎖立刻報錯，而不是等待，MySQL就沒有no wait這個選項。
• MySQL還有個問題是select for update語句執行中所有掃描過的行都會被鎖上，這一點很容易造成問題。因此如果在MySQL中用悲觀鎖務必要確定走了索引，而不是全表掃描。

樂觀鎖（Optimistic Lock）:

• **1.**樂觀鎖，也叫樂觀并發控制，它假設多用戶并發的事務在處理時不會彼此互相影響，各事務能夠在不產生鎖的情況下處理各自影響的那部分數據。在提交數據更新之前，每個事務會先檢查在該事務讀取數據后，有沒有其他事務又修改了該數據。如果其他事務有更新的話，那么當前正在提交的事務會進行回滾。
• **2.****樂觀鎖的特點先進行業務操作，不到萬不得已不去拿鎖。**即“樂觀”的認為拿鎖多半是會成功的，因此在進行完業務操作需要實際更新數據的最后一步再去拿一下鎖就好。
  樂觀鎖在數據庫上的實現完全是邏輯的，不需要數據庫提供特殊的支持。
• 3.一般的做法是在需要鎖的數據上增加一個版本號，或者時間戳，

實現方式舉例如下：

樂觀鎖（給表加一個版本號字段） 這個并不是樂觀鎖的定義，給表加版本號，是數據庫實現樂觀鎖的一種方式。

SELECT data AS old_data, version AS old_version FROM …;

根據獲取的數據進行業務操作，得到new_data和new_version

UPDATE SET data = new_data, version = new_version WHERE version = old_version

if (updated row > 0) {

// 樂觀鎖獲取成功，操作完成

} else {

// 樂觀鎖獲取失敗，回滾并重試

}

注意：

• 樂觀鎖在不發生取鎖失敗的情況下開銷比悲觀鎖小，但是一旦發生失敗回滾開銷則比較大，因此適合用在取鎖失敗概率比較小的場景，可以提升系統并發性能
• 樂觀鎖還適用于一些比較特殊的場景，例如在業務操作過程中無法和數據庫保持連接等悲觀鎖無法適用的地方。

總結：
悲觀鎖和樂觀鎖是數據庫用來保證數據并發安全防止更新丟失的兩種方法，例子在select … for update前加個事務就可以防止更新丟失。悲觀鎖和樂觀鎖大部分場景下差異不大，一些獨特場景下有一些差別，一般我們可以從如下幾個方面來判斷。

• 響應速度： 如果需要非常高的響應速度，建議采用樂觀鎖方案，成功就執行，不成功就失敗，不需要等待其他并發去釋放鎖?！?/li>
• 沖突頻率： 如果沖突頻率非常高，建議采用悲觀鎖，保證成功率，如果沖突頻率大，樂觀鎖會需要多次重試才能成功，代價比較大。
• 重試代價： 如果重試代價大，建議采用悲觀鎖。

數據庫的主從復制

主從復制的幾種方式:

• 同步復制:
  • 所謂的同步復制，意思是master的變化，必須等待slave-1,slave-2,…,slave-n完成后才能返回。 這樣，顯然不可取，也不是MySQL復制的默認設置。比如，在WEB前端頁面上，用戶增加了條記錄，需要等待很長時間。
• 異步復制:
  • 如同AJAX請求一樣。master只需要完成自己的數據庫操作即可。至于slaves是否收到二進制日志，是否完成操作，不用關心,MySQL的默認設置。
• 半同步復制:
  • master只保證slaves中的一個操作成功，就返回，其他slave不管。 這個功能，是由google為MySQL引入的。

數據庫主從復制分析的 7 個問題?

**問題1：**master的寫操作，slaves被動的進行一樣的操作，保持數據一致性，那么slave是否可以主動的進行寫操作？

假設slave可以主動的進行寫操作，slave又無法通知master，這樣就導致了master和slave數據不一致了。因此slave不應該進行寫操作，至少是slave上涉及到復制的數據庫不可以寫。實際上，這里已經揭示了讀寫分離的概念。

**問題2：**主從復制中，可以有N個slave,可是這些slave又不能進行寫操作，要他們干嘛？

實現數據備份:
類似于高可用的功能，一旦master掛了，可以讓slave頂上去，同時slave提升為master。

**異地容災:**比如master在北京，地震掛了，那么在上海的slave還可以繼續。
主要用于實現scale out,分擔負載,可以將讀的任務分散到slaves上。
【很可能的情況是，一個系統的讀操作遠遠多于寫操作，因此寫操作發向master，讀操作發向slaves進行操作】

**問題3：**主從復制中有master,slave1,slave2,…等等這么多MySQL數據庫，那比如一個JAVA WEB應用到底應該連接哪個數據庫?

我們在應用程序中可以這樣，insert/delete/update這些更新數據庫的操作，用connection(for master)進行操作，

select用connection(for slaves)進行操作。那我們的應用程序還要完成怎么從slaves選擇一個來執行select，例如使用簡單的輪循算法。

這樣的話，相當于應用程序完成了SQL語句的路由，而且與MySQL的主從復制架構非常關聯，一旦master掛了，某些slave掛了，那么應用程序就要修改了。能不能讓應用程序與MySQL的主從復制架構沒有什么太多關系呢？
找一個組件，application program只需要與它打交道，用它來完成MySQL的代理，實現SQL語句的路由。
MySQL proxy并不負責，怎么從眾多的slaves挑一個？可以交給另一個組件(比如haproxy)來完成。

這就是所謂的MySQL READ WRITE SPLITE，MySQL的讀寫分離。

**問題4：**如果MySQL proxy , direct , master他們中的某些掛了怎么辦？

總統一般都會弄個副總統，以防不測。同樣的，可以給這些關鍵的節點來個備份。

**問題5：**當master的二進制日志每產生一個事件，都需要發往slave，如果我們有N個slave,那是發N次，還是只發一次？如果只發一次，發給了slave-1，那slave-2,slave-3,…它們怎么辦？

顯 然，應該發N次。實際上，在MySQL master內部，維護N個線程，每一個線程負責將二進制日志文件發往對應的slave。master既要負責寫操作，還的維護N個線程，負擔會很重。可以這樣，slave-1是master的從，slave-1又是slave-2,slave-3,…的主，同時slave-1不再負責select。 slave-1將master的復制線程的負擔，轉移到自己的身上。這就是所謂的多級復制的概念。

**問題6：**當一個select發往MySQL proxy，可能這次由slave-2響應，下次由slave-3響應，這樣的話，就無法利用查詢緩存了。

應該找一個共享式的緩存，比如memcache來解決。將slave-2,slave-3,…這些查詢的結果都緩存至mamcache中。

**問題7：**隨著應用的日益增長，讀操作很多，我們可以擴展slave，但是如果master滿足不了寫操作了，怎么辦呢？

scale on ?更好的服務器？ 沒有最好的，只有更好的，太貴了。。。
scale out ? 主從復制架構已經滿足不了。
可以分庫【垂直拆分】，分表【水平拆分】。

mysql 高并發環境解決方案?

MySQL 高并發環境解決方案： 分庫 分表 分布式 增加二級緩存。。。。。

• **需求分析：**互聯網單位 每天大量數據讀取，寫入，并發性高。
• **現有解決方式：**水平分庫分表，由單點分布到多點數據庫中，從而降低單點數據庫壓力。
• **集群方案：**解決DB宕機帶來的單點DB不能訪問問題。
• **讀寫分離策略：**極大限度提高了應用中Read數據的速度和并發量。無法解決高寫入壓力。

數據庫崩潰時事務的恢復機制（REDO日志和UNDO日志）?

Undo Log:

• Undo Log是為了實現事務的原子性，在MySQL數據庫InnoDB存儲引擎中，還用了Undo Log來實現多版本并發控制(簡稱：MVCC)。
• 事務的原子性(Atomicity)事務中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在執行的過程中發生了錯誤，要回滾(Rollback)到事務開始前的狀態，就像這個事務從來沒有執行過。
  原理Undo Log的原理很簡單，為了滿足事務的原子性，在操作任何數據之前，首先將數據備份到一個地方（這個存儲數據備份的地方稱為UndoLog）。然后進行數據的修改。如果出現了錯誤或者用戶執行了ROLLBACK語句，系統可以利用Undo Log中的備份將數據恢復到事務開始之前的狀態。
• 之所以能同時保證原子性和持久化，是因為以下特點：
• 更新數據前記錄Undo log。
  為了保證持久性，必須將數據在事務提交前寫到磁盤。只要事務成功提交，數據必然已經持久化。
  Undo log必須先于數據持久化到磁盤。如果在G,H之間系統崩潰，undo log是完整的， 可以用來回滾事務。
  如果在A-F之間系統崩潰,因為數據沒有持久化到磁盤。所以磁盤上的數據還是保持在事務開始前的狀態。
• **缺陷：**每個事務提交前將數據和Undo Log寫入磁盤，這樣會導致大量的磁盤IO，因此性能很低。
  如果能夠將數據緩存一段時間，就能減少IO提高性能。但是這樣就會喪失事務的持久性。因此引入了另外一種機制來實現持久化，即Redo Log。

Redo Log:

• 原理和Undo Log相反，Redo Log記錄的是新數據的備份。在事務提交前，只要將Redo Log持久化即可，不需要將數據持久化。當系統崩潰時，雖然數據沒有持久化，但是Redo Log已經持久化。
• 系統可以根據Redo Log的內容，將所有數據恢復到最新的狀態。

索引

什么是索引？

何為索引：

• 數據庫索引，是數據庫管理系統中一個排序的數據結構，索引的實現通常使用B樹及其變種B+樹。
• 在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找算法。這種數據結構，就是索引。

索引的作用？它的優點缺點是什么？

索引作用：

• 協助快速查詢、更新數據庫表中數據。

• 為表設置索引要付出代價的：

  • 一是增加了數據庫的存儲空間

  • 二是在插入和修改數據時要花費較多的時間(因為索引也要隨之變動)。

索引有什么作用？

索引也叫鍵，是存儲引擎用于快速找到記錄的一種數據結構。索引對于良好的性能很關鍵，尤其是當表中數據量越來越大時，索引對性能的影響愈發重要。在數據量較小且負載較低時，不恰當的索引對性能的影響可能還不明顯，但數據量逐漸增大時，性能會急劇下降。

索引大大減少了服務器需要掃描的數據量、可以幫助服務器避免排序和臨時表、可以將隨機 IO 變成順序 IO。但索引并不總是最好的工具，對于非常小的表，大部分情況下會采用全表掃描。對于中到大型的表，索引就非常有效。但對于特大型的表，建立和使用索引的代價也隨之增長，這種情況下應該使用分區技術。

在MySQL中，首先在索引中找到對應的值，然后根據匹配的索引記錄找到對應的數據行。索引可以包括一個或多個列的值，如果索引包含多個列，那么列的順序也十分重要，因為 MySQL 只能使用索引的最左前綴。

索引的優缺點？

創建索引可以大大提高系統的性能（優點）：

1.通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

2.可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。

3.可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

4.在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

5.通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

增加索引也有許多不利的方面(缺點)：

1.創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。

2.索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。

3.當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

哪些列適合建立索引、哪些不適合建索引？

索引是建立在數據庫表中的某些列的上面。在創建索引的時候，應該考慮在哪些列上可以創建索引，在哪些列上不能創建索引。

一般來說，應該在這些列上創建索引：

• （1）在經常需要搜索的列上，可以加快搜索的速度；
• （2）在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；
• （3）在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
• （4）在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；
• （5）在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
• （6）在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

對于有些列不應該創建索引：

• （1）對于那些在查詢中很少使用或者參考的列不應該創建索引。

  • 這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求。

• （2）對于那些只有很少數據值的列也不應該增加索引。

  • 這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數據行占了表中數據行的很大比例，即需要在表中搜索的數據行的比例很大。增加索引，并不能明顯加快檢索速度。

• （3）對于那些定義為text, image和bit數據類型的列不應該增加索引。

  • 這是因為，這些列的數據量要么相當大，要么取值很少。

• （4）當修改性能遠遠大于檢索性能時，不應該創建索引。

  • 這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創建索引。

什么樣的字段適合建索引

唯一、不為空、經常被查詢的字段

主鍵、外鍵、超鍵、候選鍵

超鍵：在關系中能唯一標識元組的屬性集稱為關系模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。

候選鍵：是最小超鍵，即沒有冗余元素的超鍵。

主鍵：數據庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

為什么用自增列作為主鍵

如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會選擇主鍵作為聚集索引。

如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引。

如果也沒有這樣的唯一索引，則InnoDB會選擇內置6字節長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

數據記錄本身被存于主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節點內（大小為一個內存頁或磁盤頁）的各條數據記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB默認為15/16），則開辟一個新的頁（節點）。

如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節點的后續位置，當一頁寫滿，就會自動開辟一個新的頁

如果使用非自增主鍵（如果身份證號或學號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數據，甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過OPTIMIZE TABLE來重建表并優化填充頁面。

MySQL B+Tree索引和Hash索引的區別?

Hash索引和B+樹索引的特點：

• Hash索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位;
• B+樹索引需要從根節點到枝節點，最后才能訪問到頁節點這樣多次的IO訪問;

為什么不都用Hash索引而使用B+樹索引？

Hash索引僅僅能滿足"=",“IN"和”"查詢，不能使用范圍查詢,因為經過相應的Hash算法處理之后的Hash值的大小關系，并不能保證和Hash運算前完全一樣；

Hash索引無法被用來避免數據的排序操作，因為Hash值的大小關系并不一定和Hash運算前的鍵值完全一樣；

Hash索引不能利用部分索引鍵查詢，對于組合索引，Hash索引在計算Hash值的時候是組合索引鍵合并后再一起計算Hash值，而不是單獨計算Hash值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash索引也無法被利用；

Hash索引在任何時候都不能避免表掃描，由于不同索引鍵存在相同Hash值，所以即使取滿足某個Hash鍵值的數據的記錄條數，也無法從Hash索引中直接完成查詢，還是要回表查詢數據；

Hash索引遇到大量Hash值相等的情況后性能并不一定就會比B+樹索引高。

補充：

MySQL中，只有HEAP/MEMORY引擎才顯示支持Hash索引。

常用的InnoDB引擎中默認使用的是B+樹索引，它會實時監控表上索引的使用情況，如果認為建立哈希索引可以提高查詢效率，則自動在內存中的“自適應哈希索引緩沖區”建立哈希索引（在InnoDB中默認開啟自適應哈希索引），通過觀察搜索模式，MySQL會利用index key的前綴建立哈希索引，如果一個表幾乎大部分都在緩沖池中，那么建立一個哈希索引能夠加快等值查詢。
B+樹索引和哈希索引的明顯區別是：

如果是等值查詢，那么哈希索引明顯有絕對優勢，因為只需要經過一次算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然后再根據鏈表往后掃描，直到找到相應的數據；

如果是范圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過哈希算法后，有可能變成不連續的了，就沒辦法再利用索引完成范圍查詢檢索；
同理，哈希索引沒辦法利用索引完成排序，以及like ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是范圍查詢）；

哈希索引也不支持多列聯合索引的最左匹配規則；

B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重復鍵值情況下，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題。

在大多數場景下，都會有范圍查詢、排序、分組等查詢特征，用B+樹索引就可以了。

B樹和B+樹的區別

B樹，每個節點都存儲key和data，所有節點組成這棵樹，并且葉子節點指針為nul，葉子結點不包含任何關鍵字信息。

B+樹，所有的葉子結點中包含了全部關鍵字的信息，及指向含有這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大的順序鏈接，所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵字。 (而B 樹的非終節點也包含需要查找的有效信息)

為什么說B+比B樹更適合實際應用中操作系統的文件索引和數據庫索引？

B+的磁盤讀寫代價更低

• B+的內部結點并沒有指向關鍵字具體信息的指針。因此其內部結點相對B樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。

B+tree的查詢效率更加穩定

• 由于非終結點并不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。

談一談 MySQL 的 B-Tree 索引

大多數 MySQL 引擎都支持這種索引，但底層的存儲引擎可能使用不同的存儲結構，例如 NDB 使用 T-Tree，而 InnoDB 使用 B+ Tree。

• B-Tree 通常意味著所有的值都是按順序存儲的，并且每個葉子頁到根的距離相同。
• B-Tree 索引能夠加快訪問數據的速度，因為存儲引擎不再需要進行全表掃描來獲取需要的數據，取而代之的是從索引的根節點開始進行搜索。
• 根節點的槽中存放了指向子節點的指針，存儲引擎根據這些指針向下層查找。
• 通過比較節點頁的值和要查找的值可以找到合適的指針進入下層子節點，這些指針實際上定義了子節點頁中值的上限和下限。
• 最終存儲引擎要么找到對應的值，要么該記錄不存在。
• 葉子節點的指針指向的是被索引的數據，而不是其他的節點頁。

B-Tree索引的限制：

• 如果不是按照索引的最左列開始查找，則無法使用索引。
• 不能跳過索引中的列，例如索引為 (id,name,sex)，不能只使用 id 和 sex 而跳過 name。
• 如果查詢中有某個列的范圍查詢，則其右邊的所有列都無法使用索引。

了解 Hash 索引嗎？

哈希索引基于哈希表實現，只有精確匹配索引所有列的查詢才有效。對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼，哈希碼是一個較小的值，并且不同鍵值的行計算出的哈希碼也不一樣。

哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數據行的指針。

只有 Memory 引擎顯式支持哈希索引，這也是 Memory 引擎的默認索引類型。

因為索引自身只需存儲對應的哈希值，所以索引的結構十分緊湊，這讓哈希索引的速度非?？?#xff0c;但它也有一些限制：

• 哈希索引數據不是按照索引值順序存儲的，無法用于排序。
• 哈希索引不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內容來計算哈希值的。例如在數據列(a,b)上建立哈希索引，如果查詢的列只有a就無法使用該索引。
• 哈希索引只支持等值比較查詢，不支持任何范圍查詢。

什么是自適應哈希索引？

自適應哈希索引是 InnoDB 引擎的一個特殊功能，當它注意到某些索引值被使用的非常頻繁時，會在內存中基于 B-Tree 索引之上再創鍵一個哈希索引，這樣就讓 B-Tree 索引也具有哈希索引的一些優點，比如快速哈希查找。

這是一個完全自動的內部行為，用戶無法控制或配置，但如果有必要可以關閉該功能。

什么是空間索引？

MyISAM 表支持空間索引，可以用作地理數據存儲。和 B-Tree 索引不同，這類索引無需前綴查詢?？臻g索引會從所有維度來索引數據，查詢時可以有效地使用任意維度來組合查詢。

必須使用 MySQL 的 GIS 即地理信息系統的相關函數來維護數據，但 MySQL 對 GIS 的支持并不完善，因此大部分人都不會使用這個特性。

什么是全文索引？

通過數值比較、范圍過濾等就可以完成絕大多數需要的查詢，但如果希望通過關鍵字匹配進行查詢，就需要基于相似度的查詢，而不是精確的數值比較，全文索引就是為這種場景設計的。

MyISAM 的全文索引是一種特殊的 B-Tree 索引，一共有兩層。第一層是所有關鍵字，然后對于每一個關鍵字的第二層，包含的是一組相關的"文檔指針"。全文索引不會索引文檔對象中的所有詞語，它會根據規則過濾掉一些詞語，例如停用詞列表中的詞都不會被索引。

什么是聚簇索引？

聚簇索引不是一種索引類型，而是一種數據存儲方式。InnoDB 的聚簇索引實際上在同一個結構中保存了 B-Tree 索引和數據行。當表有聚餐索引時，它的行數據實際上存放在索引的葉子頁中，因為無法同時把數據行存放在兩個不同的地方，所以一個表只能有一個聚簇索引。

優點：

• ① 可以把相關數據保存在一起。
• ② 數據訪問更快，聚簇索引將索引和數據保存在同一個 B-Tree 中，因此獲取數據比非聚簇索引要更快。
• ③ 使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值。

缺點：

• ① 聚簇索引最大限度提高了 IO 密集型應用的性能，如果數據全部在內存中將會失去優勢。
• ② 更新聚簇索引列的代價很高，因為會強制每個被更新的行移動到新位置。
• ③ 基于聚簇索引的表插入新行或主鍵被更新導致行移動時，可能導致頁分裂，表會占用更多磁盤空間。
• ④ 當行稀疏或由于頁分裂導致數據存儲不連續時，全表掃描可能很慢。

什么是覆蓋索引？

覆蓋索引指一個索引包含或覆蓋了所有需要查詢的字段的值，不再需要根據索引回表查詢數據。覆蓋索引必須要存儲索引列的值，因此 MySQL 只能使用 B-Tree 索引做覆蓋索引。

優點：

① 索引條目通常遠小于數據行大小，可以極大減少數據訪問量。

② 因為索引按照列值順序存儲，所以對于 IO 密集型防偽查詢回避隨機從磁盤讀取每一行數據的 IO 少得多。

③ 由于 InnoDB 使用聚簇索引，覆蓋索引對 InnoDB 很有幫助。InnoDB 的二級索引在葉子節點保存了行的主鍵值，如果二級主鍵能覆蓋查詢那么可以避免對主鍵索引的二次查詢。

你知道哪些索引使用原則？

建立索引

• 對查詢頻次較高且數據量比較大的表建立索引。索引字段的選擇，最佳候選列應當從 WHERE 子句的條件中提取，如果 WHERE 子句中的組合比較多，應當挑選最常用、過濾效果最好的列的組合。業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。

使用前綴索引

• 索引列開始的部分字符，索引創建后也是使用硬盤來存儲的，因此短索引可以提升索引訪問的 IO 效率。對于 BLOB、TEXT 或很長的 VARCHAR 列必須使用前綴索引，MySQL 不允許索引這些列的完整長度。前綴索引是一種能使索引更小更快的有效方法，但缺點是 MySQL 無法使用前綴索引做 ORDER BY 和 GROUP BY，也無法使用前綴索引做覆蓋掃描。

選擇合適的索引順序

• 當不需要考慮排序和分組時，將選擇性最高的列放在前面。索引的選擇性是指不重復的索引值和數據表的記錄總數之比，索引的選擇性越高則查詢效率越高，唯一索引的選擇性是 1，因此也可以使用唯一索引提升查詢效率。

刪除無用索引

• MySQL 允許在相同列上創建多個索引，重復的索引需要單獨維護，并且優化器在優化查詢時也需要逐個考慮，這會影響性能。重復索引是指在相同的列上按照相同的順序創建的相同類型的索引，應該避免創建重復索引。如果創建了索引 (A,B) 再創建索引 (A) 就是冗余索引，因為這只是前一個索引的前綴索引，對于 B-Tree 索引來說是冗余的。解決重復索引和冗余索引的方法就是刪除這些索引。除了重復索引和冗余索引，可能還會有一些服務器永遠不用的索引，也應該考慮刪除。

最左前綴原則

多列索引：

• ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

• 為了提高搜索效率，我們需要考慮運用多列索引,由于索引文件以B－Tree格式保存，所以我們不用掃描任何記錄，即可得到最終結果。

注：

• 在mysql中執行查詢時，只能使用一個索引，如果我們在lname,fname,age上分別建索引,執行查詢時，只能使用一個索引，mysql會選擇一個最嚴格(獲得結果集記錄數最少)的索引。

最左前綴原則：

• 顧名思義，就是最左優先，上例中我們創建了lname_fname_age多列索引,相當于創建了(lname)單列索引，(lname,fname)組合索引以及(lname,fname,age)組合索引。

索引失效的情況有哪些？

如果索引列出現了隱式類型轉換，則 MySQL 不會使用索引。

• 常見的情況是在 SQL 的 WHERE 條件中字段類型為字符串，其值為數值，如果沒有加引號那么 MySQL 不會使用索引。

如果 WHERE 條件中含有 OR，除非 OR 前使用了索引列而 OR 之后是非索引列，索引會失效。

MySQL 不能在索引中執行 LIKE 操作，這是底層存儲引擎 API 的限制，最左匹配的 LIKE 比較會被轉換為簡單的比較操作，但如果是以通配符開頭的 LIKE 查詢，存儲引擎就無法做比較。這種情況下 MySQL 只能提取數據行的值而不是索引值來做比較。

如果查詢中的列不是獨立的，則 MySQL 不會使用索引。獨立的列是指索引列不能是表達式的一部分，也不能是函數的參數。

對于多個范圍條件查詢，MySQL 無法使用第一個范圍列后面的其他索引列，對于多個等值查詢則沒有這種限制。

如果 MySQL 判斷全表掃描比使用索引查詢更快，則不會使用索引。

索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。

聚集索引和非聚集索引區別?

聚合索引(clustered index):

• 聚集索引**表記錄的排列順序和索引的排列順序一致，所以查詢效率快，**只要找到第一個索引值記錄，其余就連續性的記錄在物理也一樣連續存放。聚集索引對應的缺點就是修改慢，因為為了保證表中記錄的物理和索引順序一致，在記錄插入的時候，會對數據頁重新排序。
• 聚集索引類似于新華字典中用拼音去查找漢字，拼音檢索表于書記順序都是按照a~z排列的，就像相同的邏輯順序于物理順序一樣，當你需要查找a,ai兩個讀音的字，或是想一次尋找多個傻(sha)的同音字時，也許向后翻幾頁，或緊接著下一行就得到結果了。

非聚合索引(nonclustered index):

• 非聚集索引**指定了表中記錄的邏輯順序，但是記錄的物理和索引不一定一致，**兩種索引都采用B+樹結構，非聚集索引的葉子層并不和實際數據頁相重疊，而采用葉子層包含一個指向表中的記錄在數據頁中的指針方式。非聚集索引層次多，不會造成數據重排。
• 非聚集索引類似在新華字典上通過偏旁部首來查詢漢字，檢索表也許是按照橫、豎、撇來排列的，但是由于正文中是a~z的拼音順序，所以就類似于邏輯地址于物理地址的不對應。同時適用的情況就在于分組，大數目的不同值，頻繁更新的列中，這些情況即不適合聚集索引。

根本區別：

• 聚集索引和非聚集索引的根本區別是表記錄的排列順序和與索引的排列順序是否一致。

優化

如何定位低效 SQL？

可以通過兩種方式來定位執行效率較低的 SQL 語句。

• 一種是通過慢查詢日志定位，可以通過慢查詢日志定位那些已經執行完畢的 SQL 語句。
• 另一種是使用 SHOW PROCESSLIST 查詢，慢查詢日志在查詢結束以后才記錄，所以在應用反應執行效率出現問題的時候查詢慢查詢日志不能定位問題，此時可以使用 SHOW PROCESSLIST 命令查看當前 MySQL 正在進行的線程，包括線程的狀態、是否鎖表等，可以實時查看 SQL 的執行情況，同時對一些鎖表操作進行優化。找到執行效率低的 SQL 語句后，就可以通過 SHOW PROFILE、EXPLAIN 或 trace 等豐富來繼續優化語句。

SHOW PROFILE 的作用？

通過 SHOW PROFILE 可以分析 SQL 語句性能消耗，例如查詢到 SQL 會執行多少時間，并顯示 CPU、內存使用量，執行過程中系統鎖及表鎖的花費時間等信息。

例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY N 分別查詢 id 為 N 的 SQL 語句的 CPU、內存以及 IO 的消耗情況。

trace 是干什么的？

從 MySQL5.6 開始，可以通過 trace 文件進一步獲取優化器是是如何選擇執行計劃的，在使用時需要先打開設置，然后執行一次 SQL，最后查看 information_schema.optimizer_trace 表而都內容，該表為聯合i表，只能在當前會話進行查詢，每次查詢后返回的都是最近一次執行的 SQL 語句。

EXPLAIN 的字段有哪些，具有什么含義？

執行計劃是 SQL 調優的一個重要依據，可以通過 EXPLAIN 命令查看 SQL 語句的執行計劃，如果作用在表上，那么該命令相當于 DESC。EXPLAIN 的指標及含義如下：

指標名含義

id	表示 SELECT 子句或操作表的順序，執行順序從大到小執行，當 id 一樣時，執行順序從上往下。
select_type	表示查詢中每個 SELECT 子句的類型，例如 SIMPLE 表示不包含子查詢、表連接或其他復雜語法的簡單查詢，PRIMARY 表示復雜查詢的最外層查詢，SUBQUERY 表示在 SELECT 或 WHERE 列表中包含了子查詢。
type	表示訪問類型，性能由差到好為：ALL 全表掃描、index 索引全掃描、range 索引范圍掃描、ref 返回匹配某個單獨值得所有行，常見于使用非唯一索引或唯一索引的非唯一前綴進行的查找，也經常出現在 join 操作中、eq_ref 唯一性索引掃描，對于每個索引鍵只有一條記錄與之匹配、const 當 MySQL 對查詢某部分進行優化，并轉為一個常量時，使用這些訪問類型，例如將主鍵或唯一索引置于 WHERE 列表就能將該查詢轉為一個 const、system 表中只有一行數據或空表，只能用于 MyISAM 和 Memory 表、NULL 執行時不用訪問表或索引就能得到結果。SQL 性能優化的目標：至少要達到 range 級別，要求是 ref 級別，如果可以是consts 最好。
possible_keys	表示查詢時可能用到的索引，但不一定使用。列出大量可能索引時意味著備選索引數量太多了。
key	顯示 MySQL 在查詢時實際使用的索引，如果沒有使用則顯示為 NULL。
key_len	表示使用到索引字段的長度，可通過該列計算查詢中使用的索引的長度，對于確認索引有效性以及多列索引中用到的列數目很重要。
ref	表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。
rows	表示 MySQL 根據表統計信息及索引選用情況，估算找到所需記錄所需要讀取的行數。
Extra	表示額外信息，例如 Using temporary 表示需要使用臨時表存儲結果集，常見于排序和分組查詢。Using filesort 表示無法利用索引完成的文件排序，這是 ORDER BY 的結果，可以通過合適的索引改進性能。Using index 表示只需要使用索引就可以滿足查詢表得要求，說明表正在使用覆蓋索引。

使用explain優化sql和索引?

對于復雜、效率低的sql語句，我們通常是使用explain sql 來分析sql語句，這個語句可以打印出，語句的執行。這樣方便我們分析，進行優化

• **table：**顯示這一行的數據是關于哪張表的
• **type：**這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、index和ALL
• **all:**full table scan ;MySQL將遍歷全表以找到匹配的行；
• index: index scan; index 和 all的區別在于index類型只遍歷索引；
• **range：**索引范圍掃描，對索引的掃描開始于某一點，返回匹配值的行，常見與between ，等查詢；
• **ref：**非唯一性索引掃描，返回匹配某個單獨值的所有行，常見于使用非唯一索引即唯一索引的非唯一前綴進行查找；
• **eq_ref：**唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常用于主鍵或者唯一索引掃描；
• **const，system：**當MySQL對某查詢某部分進行優化，并轉為一個常量時，使用這些訪問類型。如果將主鍵置于where列表中，MySQL就能將該查詢轉化為一個常量。
• **possible_keys：**顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
• key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MySQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MySQL忽略索引
• **key_len：**使用的索引的長度。在不損失精確性的情況下，長度越短越好
• **ref：**顯示索引的哪一列被使用了，如果可能的話，是一個常數
• **rows：**MySQL認為必須檢查的用來返回請求數據的行數
• **Extra：**關于MySQL如何解析查詢的額外信息。將在表4.3中討論，但這里可以看到的壞的例子是Using temporary和Using filesort，意思MySQL根本不能使用索引，結果是檢索會很慢。

有哪些優化 SQL 的策略？

優化 COUNT 查詢

• COUNT 是一個特殊的函數，它可以統計某個列值的數量，在統計列值時要求列值是非空的，不會統計 NULL 值。如果在 COUNT 中指定了列或列的表達式，則統計的就是這個表達式有值的結果數，而不是 NULL。

• COUNT 的另一個作用是統計結果集的行數，當 MySQL 確定括號內的表達式不可能為 NULL 時，實際上就是在統計行數。當使用 COUNT(*) 時，* 不會擴展成所有列，它會忽略所有的列而直接統計所有的行數。

• 某些業務場景并不要求完全精確的 COUNT 值，此時可以使用近似值來代替，EXPLAIN 出來的優化器估算的行數就是一個不錯的近似值，因為執行 EXPLAIN 并不需要真正地執行查詢。

• 通常來說 COUNT 都需要掃描大量的行才能獲取精確的結果，因此很難優化。在 MySQL 層還能做的就只有覆蓋掃描了，如果還不夠就需要修改應用的架構，可以增加匯總表或者外部緩存系統。

優化關聯查詢

• 確保 ON 或 USING 子句中的列上有索引，在創建索引時就要考慮到關聯的順序。

• 確保任何 GROUP BY 和 ORDER BY 的表達式只涉及到一個表中的列，這樣 MySQL 才有可能使用索引來優化這個過程。

• 在 MySQL 5.5 及以下版本盡量避免子查詢，可以用關聯查詢代替，因為執行器會先執行外部的 SQL 再執行內部的 SQL。

優化 GROUP BY

• 如果沒有通過 ORDER BY 子句顯式指定要排序的列，當查詢使用 GROUP BY 時，結果***自動按照分組的字段進行排序，如果不關心結果集的順序，可以使用 ORDER BY NULL 禁止排序。

優化 LIMIT 分頁

• 在偏移量非常大的時候，需要查詢很多條數據再舍棄，這樣的代價非常高。要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。最簡單的辦法是盡可能地使用覆蓋索引掃描，而不是查詢所有的列，然后根據需要做一次關聯操作再返回所需的列。

• 還有一種方法是從上一次取數據的位置開始掃描，這樣就可以避免使用 OFFSET。其他優化方法還包括使用預先計算的匯總表，或者關聯到一個冗余表，冗余表只包含主鍵列和需要做排序的數據列。

優化 UNION 查詢

• MySQL 通過創建并填充臨時表的方式來執行 UNION 查詢，除非確實需要服務器消除重復的行，否則一定要使用 UNION ALL，如果沒有 ALL 關鍵字，MySQL 會給臨時表加上 DISTINCT 選項，這會導致對整個臨時表的數據做唯一性檢查，這樣做的代價非常高。

使用用戶自定義變量

• 在查詢中混合使用過程化和關系化邏輯的時候，自定義變量可能會非常有用。用戶自定義變量是一個用來存儲內容的臨時容器，在連接 MySQL 的整個過程中都存在，可以在任何可以使用表達式的地方使用自定義變量。例如可以使用變量來避免重復查詢剛剛更新過的數據、統計更新和插入的數量等。

優化 INSERT

• 需要對一張表插入很多行數據時，應該盡量使用一次性插入多個值的 INSERT 語句，這種方式將縮減客戶端與數據庫之間的連接、關閉等消耗，效率比多條插入單個值的 INSERT 語句高。也可以關閉事務的自動提交，在插入完數據后提交。當插入的數據是按主鍵的順序插入時，效率更高。

MySQL 主從復制的作用？

復制解決的基本問題是讓一臺服務器的數據與其他服務器保持同步，一臺主庫的數據可以同步到多臺備庫上，備庫本身也可以被配置成另外一臺服務器的主庫。主庫和備庫之間可以有多種不同的組合方式。

• MySQL 支持兩種復制方式：基于行的復制和基于語句的復制，基于語句的復制也稱為邏輯復制，從 MySQL 3.23 版本就已存在，基于行的復制方式在 5.1 版本才被加進來。這兩種方式都是通過在主庫上記錄二進制日志、在備庫重放日志的方式來實現異步的數據復制。因此同一時刻備庫的數據可能與主庫存在不一致，并且無法包裝主備之間的延遲。

• MySQL 復制大部分是向后兼容的，新版本的服務器可以作為老版本服務器的備庫，但是老版本不能作為新版本服務器的備庫，因為它可能無法解析新版本所用的新特性或語法，另外所使用的二進制文件格式也可能不同。

• 復制解決的問題：數據分布、負載均衡、備份、高可用性和故障切換、MySQL 升級測試。

MySQL 主從復制的步驟？

① 在主庫上把數據更改記錄到二進制日志中。

② 備庫將主庫的日志復制到自己的中繼日志中。

③ 備庫讀取中繼日志中的事件，將其重放到備庫數據之上。

第一步是在主庫上記錄二進制日志，每次準備提交事務完成數據更新前，主庫將數據更新的事件記錄到二進制日志中。MySQL 會按事務提交的順序而非每條語句的執行順序來記錄二進制日志，在記錄二進制日志后，主庫會告訴存儲引擎可以提交事務了。

下一步，備庫將主庫的二進制日志復制到其本地的中繼日志中。備庫首先會啟動一個工作的 IO 線程，IO 線程跟主庫建立一個普通的客戶端連接，然后在主庫上啟動一個特殊的二進制轉儲線程，這個線程會讀取主庫上二進制日志中的事件。它不會對事件進行輪詢。如果該線程追趕上了主庫將進入睡眠狀態，直到主庫發送信號量通知其有新的事件產生時才會被喚醒，備庫 IO 線程會將接收到的事件記錄到中繼日志中。

備庫的 SQL 線程執行最后一步，該線程從中繼日志中讀取事件并在備庫執行，從而實現備庫數據的更新。當 SQL 線程追趕上 IO 線程時，中繼日志通常已經在系統緩存中，所以中繼日志的開銷很低。SQL 線程執行的時間也可以通過配置選項來決定是否寫入其自己的二進制日志中。

總結

以上是生活随笔為你收集整理的mysql数据库面试题大全（持续更新）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。