mysql資料庫中一些常見問題（mysql資料庫中一些常見問題有哪些）

本文目錄一覽：

• 1、Mysql資料庫的基本問題
• 2、MYSQL使用基礎、進階分享
• 3、資料庫老師會問哪些問題？
• 4、「春招系列」MySQL面試核心25問（附答案）

Mysql資料庫的基本問題

親，你看看你這句COUNT(id) as num,SUM(num) as zongshu

你確定，num是你資料庫里的欄位？如果是欄位你為什麼要 as別名成num

如果num是別名，你SUM(num) as zongshu 。就會提示錯誤

因為num根本就不是欄位，而只是個你臨時命名的別名，所以報錯是理所當然的。

能理解嗎？不懂繼續追問

MYSQL使用基礎、進階分享

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發，屬於Oracle旗下產品，是最流行的關係型資料庫管理系統之一。

埠是3306。

表很多時，使用linux腳本，需要根據需要修改一下：

和創建一樣，可以加上 if exists

可兩篇文章：

如：

用於在已有的表中添加、刪除或修改列。

添加 ADD

或

默認是添加到最後，但可以指定位置。 FIRST ：添加最前

AFTER 欄位名 ：添加指定欄位之後

例子：

刪除 DROP

修改 MODIFY 主要修改原列的類型或約束條件 同樣可以用 FIRST 和 AFTER 欄位名 ，代表的是修改到哪裡。

修改欄位名 CHANGE

可以把表2的數據複製到表1中，但 不能複製約束性條件 。

單行

多行，注意 只有一個VALUES ：

不寫 (行1, 行2…) 這一部分的話，默認一一對應

除了以上方法外，還可以用SET為每一行附上相應的值。

假如沒有篩選的話，就給全部都修改了。可以用 WHERE 篩選。

假如 沒有篩選的話，就給全部刪除了 。相當於清空。

清空

先把表刪除，然後再建一個。與 DELETE FROM 相比， TRUNCATE 的效率更快，因為 DELETE FROM 是把記錄逐條刪除的。

查詢執行的順序

FROM — WHERE — SELECT — GROUP BY — HAVING — ORDER BY — LIMIT

注意

當數據很大，上百萬的時候，使用LIMIT … OFFSET ..的方式進行分頁十分浪費資源且耗時長。最好是結合WHERE使用，如：

REGEXP 使用正則表達進行匹配。 查詢時，需要搭配WHERE或HAVING使用 。

兩個表之間有交集且要用到兩個表的數據時，可以使用內連接查詢。

LEFT JOIN 關鍵字從左表（table1）返回所有的行，即使右表（table2）中沒有匹配。如果右表中沒有匹配，則結果為 NULL。

用法：

RIGHT JOIN 關鍵字從右表（table2）返回所有的行，即使左表（table1）中沒有匹配。如果左表中沒有匹配，則結果為 NULL。 把LEFT JOIN的表1、表2調換順序，就是REGHT JOIN 。

FULL OUTER JOIN 關鍵字只要左表（table1）和右表（table2）其中一個表中存在匹配，則返回行. 相當於結合了 LEFT JOIN 和 RIGHT JOIN 的結果。

但 MySQL中不支持 FULL OUTER JOIN 。

即SELECT嵌套。

IN 一個查詢結果作為另一個查詢的條件。 如：

EXISTS 用於判斷查詢子句是否有記錄，如果有一條或多條記錄存在返回 True，否則返回 False。True時執行。 如：

索引的本質是一種排好序的數據結構。利用索引可以提高查詢速度。

常見的索引有：

MySQL通過外鍵約束來保證表與表之間的數據的完整性和準確性。 外鍵的使用條件：

外鍵的好處：可以使得兩張表關聯，保證數據的一致性和實現一些級聯操作。

對已有的兩個表增加外鍵 比如：主表為A，子表為B，外鍵為aid，外鍵約束名字為a_fk_b

為子表添加一個欄位，當做外鍵

為子表添加外鍵約束條件

假如刪除記錄報錯： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (…)

這是因為MySQL中設置了foreign key關聯，造成無法更新或刪除數據。可以通過設置 FOREIGN_KEY_CHECKS 變數來避免這種情況。 第一步：禁用外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=0; 第二步：刪除數據 第三步：啟動外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=1; 查看當前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS;

使用 UNION 來組合兩個查詢，如果第一個查詢返回 M 行，第二個查詢返回 N 行，那麼組合查詢的結果一般為 M+N 行。

每個查詢必須包含相同的列、表達式和聚集函數。

默認會去除相同行，如果需要 保留 相同行，使用 UNION ALL 。

只能包含一個 ORDER BY 子句，並且必須位於語句的最後 。

內置函數很多, 見: MySQL 函數

我們一般使用 START TRANSACTION 或 BEGIN 開啟事務, COMMIT 提交事務中的命令, SAVEPOINT : 相當於設置一個還原點, ROLLBACK TO : 回滾到某個還原點下

一般的使用格式如下:

開啟事務時, 默認加鎖

根據類型可分為共享鎖（SHARED LOCK）和排他鎖（EXCLUSIVE LOCK）或者叫讀鎖（READ LOCK）和寫鎖（WRITE LOCK）。

根據粒度劃分又分表鎖和行鎖。表鎖由資料庫伺服器實現，行鎖由存儲引擎實現。

除此之外,我們可以顯示加鎖

加鎖時, 如果沒有索引，會鎖表，如果加了索引，就會鎖行

InnoDB默認支持行鎖，獲取鎖是分步的，並不是一次性獲取所有的鎖，因此在鎖競爭的時候就會出現死鎖的情況

解決方法:

即ACID特性:

由於並發事務會引發上面這些問題, 我們可以設置事務的隔離級別解決上面的問題.

MySQL的默認隔離級別（可重複讀）

查看當前會話隔離級別

方式1

方式2

設置隔離級別

主從集群的示意圖如下:

主要涉及三個線程： binlog 線程、 I/O 線程和 SQL 線程。

同步流程:

由於MySQL主從集群只會從主節點同步到從節點, 不會反過來同步, 所以需要讀寫分離

讀寫分離需要在業務層面實現 , 寫數據只能在主節點上完成, 而讀數據可以在主節點或從節點上完成

索引是幫助MySQL高效獲取數據的排好序的數據結構

MySQL的索引有

推薦兩個在線工具:

簡單來說, B樹是在紅黑樹(一個平衡二叉樹)的基礎上將一個節點存放多個值, 實現的, 降低了樹的高度, 每個節點都存放索引及對應數據指針, 同一層的節點是遞增的

而B+樹在B樹的基礎上進行優化, 非葉子節點存放 子節點的開始的索引, 葉子節點存放索引和數據的指針, 且葉子節點之間有雙向的指針

如下示意圖:

不同的引擎, 主鍵索引存放的數據也不一樣, 比如常見的 MyISAM 和 InnoDB

MyISAM 的B+樹葉子節點存放表數據的指針, InnoDB 的B+樹葉子節點存放處主鍵外的數據

其他的:

即多個列組成一個索引, 語法:

由於聯合索引的B+樹的結構, 根據列建立, 所以我們的查找條件也要根據索引列的順序( where column1=x, column2=y,columnN… ), 否則會全表掃描

如果你對列進行了 （+，-，*，/，!） , 那麼都將不會走索引。

OR 引起的索引失效

OR 導致索引是在特定情況下的，並不是所有的 OR 都是使索引失效，如果OR連接的是 同 一個欄位，那麼索引 不會失效 ， 反之索引失效 。

這個我相信大家都明白，模糊搜索如果你前綴也進行模糊搜索，那麼不會走索引。

這兩種用法，也將使索引失效。另 IN 會走索引，但是當IN的取值範圍較大時會導致索引失效，走全表掃描, 見: MySQL中使用IN會不會走索引

不走索引。

走索引。

所以設計表的時候, 建議不可為空, 而是將默認值設置為 “” ( NOT NULL DEFAULT “” )

資料庫老師會問哪些問題？

1.MySQL 主鍵與索引的聯繫與區別

主鍵是為了標識資料庫記錄唯一性，不允許記錄重複，且鍵值不能為空，主鍵也是一個特殊索引。

數據表中只允許有一個主鍵，但是可以有多個索引。

使用主鍵會資料庫會自動創建主索引，也可以在非主鍵上創建索引，方便查詢效率。

索引可以提高查詢速度，它就相當於字典的目錄，可以通過它很快查詢到想要的結果，而不需要進行全表掃描。

主鍵索引外索引的值可以為空。

主鍵也可以由多個欄位組成，組成複合主鍵，同時主鍵肯定也是唯一索引。

唯一索引則表示該索引值唯一，可以由一個或幾個欄位組成，一個表可以有多個唯一索引。

2.資料庫索引是怎麼回事？用的啥數據結構 為什麼B+樹比B樹更合適

一個索引是存儲的表中一個特定列的值數據結構（最常見的是B-Tree）。索引是在表的列上創建。所以，要記住的關鍵點是索引包含一個表中列的值，並且這些值存儲在一個數據結構中。請記住記住這一點：索引是一種數據結構 。

什麼樣的數據結構可以作為索引？

B-Tree 是最常用的用於索引的數據結構。因為它們是時間複雜度低， 查找、刪除、插入操作都可以可以在對數時間內完成。另外一個重要原因存儲在B-Tree中的數據是有序的。資料庫管理系統（RDBMS）通常決定索引應該用哪些數據結構。但是，在某些情況下，你在創建索引時可以指定索引要使用的數據結構。

當我們利用索引查詢的時候，不可能把整個索引全部載入到內存，只能逐一載入每個磁碟頁，磁碟頁對應索引樹的節點。那麼Mysql衡量查詢效率的標準就是磁碟IO次數。如果我們利用二叉樹作為索引結構，那麼磁碟的IO次數和索引樹的高度是相關的。

那麼為了提高查詢效率，就需要減少磁碟IO數。為了減少磁碟IO的次數，就需要盡量降低樹的高度，需要把原來「瘦高」的樹結構變的「矮胖」，樹的每層的分叉越多越好，因此B樹正好符合我們的要求，這也是B-樹的特徵之一。

B樹 B樹的節點為關鍵字和相應的數據（索引等）

B+樹 B+樹是B樹的一個變形，非葉子節點只保存索引，不保存實際的數據，數據都保存在葉子節點中，B+樹的葉子節點為鏈表，鏈表放數據，非葉子節點是索引。

對比：

B樹和B+樹同樣適用於高度越低，查詢越快。

B樹查找節點，B+樹只需要查詢所有節點（索引），B樹查詢索引和數據。雖然可能第一個就找到，但在極端情況下，需要全查詢索引和數據，不如B+樹穩定。

B+樹和B樹比，B+樹的硬碟空間更少，io的讀寫代價更低。因為B+樹節點只有索引，佔位更少。在查詢的情況下硬碟指針移動更低

哈希表索引是怎麼工作的？

哈希表是另外一種你可能看到用作索引的數據結構-這些索引通常被稱為哈希索引。使用哈希索引的原因是，在尋找值時哈希表效率極高。所以，如果使用哈希索引，對於比較字元串是否相等的查詢能夠極快的檢索出的值。例如之前我們討論過的這個查詢(SELECT * FROM Employee WHERE Employee_Name = 『Jesus』) 就可以受益於創建在Employee_Name 列上的哈希索引。哈系索引的工作方式是將列的值作為索引的鍵值（key），和鍵值相對應實際的值（value）是指向該表中相應行的指針。因為哈希表基本上可以看作是關聯數組，一個典型的數據項就像「Jesus = 0x28939″，而0x28939是對內存中表中包含Jesus這一行的引用。在哈系索引的中查詢一個像「Jesus」這樣的值，並得到對應行的在內存中的引用，明顯要比掃描全表獲得值為「Jesus」的行的方式快很多。

哈希索引的缺點

哈希表是無順的數據結構，對於很多類型的查詢語句哈希索引都無能為力。舉例來說，假如你想要找出所有小於40歲的員工。你怎麼使用使用哈希索引進行查詢？這不可行，因為哈希表只適合查詢鍵值對-也就是說查詢相等的查詢（例：like 「WHERE name = 『Jesus』）。哈希表的鍵值映射也暗示其鍵的存儲是無序的。這就是為什麼哈希索引通常不是資料庫索引的默認數據結構-因為在作為索引的數據結構時，其不像B-Tree那麼靈活

3.創建索引的注意事項

索引可以提高數據的訪問速度，但同時也增加了插入、更新和刪除操作的處理時間，解決此問題就是分析應用程序的業務處理、數據使用，為經常被用作查詢條件、或者被要求排序的欄位建立索引。索引是建立在資料庫表中的某些列的上面。因此，在創建索引的時候，應該仔細考慮在哪些列上可以創建索引，在哪些列上不能創建索引。

創建規則：

表的主鍵、外鍵必須有索引；

數據量超過300的表應該有索引；

經常與其他表進行連接的表，在連接欄位上應該建立索引；

經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引；

索引應該建在選擇性高的欄位上；

索引應該建在小欄位上，對於大的文本欄位甚至超長欄位，不要建索引；

複合索引的建立需要進行仔細分析；盡量考慮用單欄位索引代替

頻繁進行數據操作的表，不要建立太多的索引；

刪除無用的索引，避免對執行計劃造成負面影響；

創建索引需要注意的地方：

限制表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。索引雖說提高了訪問速度，但太多索引會影響數據的更新操作。

避免在取值朝一個方向增長的欄位（例如：日期類型的欄位）上，建立索引；對複合索引，避免將這種類型的欄位放置在最前面

對複合索引，按照欄位在查詢條件中出現的頻度建立索引

刪除不再使用，或者很少被使用的索引。

4.MYSQL事務特性和實現原理

ACID表示原子性（atomicity）、一致性（consistency）、隔離性（isolation）和持久性（durability）。一個很好的事務處理系統，必須具備這些標準特性：

原子性（atomicity）

一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性

是利用Innodb的undo log。undo log名為回滾日誌，是實現原子性的關鍵，當事務回滾時能夠撤銷所有已經成功執行的sql語句，他需要記錄你要回滾的相應日誌信息。

一致性（consistency）

資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。（在前面的例子中，一致性確保了，即使在執行第三、四條語句之間時系統崩潰，支票賬戶中也不會損失200美元，因為事務最終沒有提交，所以事務中所做的修改也不會保存到資料庫中。）

資料庫通過原子性、隔離性、持久性來保證一致性

隔離性（isolation）

通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。（在前面的例子中，當執行完第三條語句、第四條語句還未開始時，此時有另外的一個賬戶匯總程序開始運行，則其看到支票帳戶的餘額並沒有被減去200美元。）

利用的是鎖和MVCC機制。MVCC,即多版本並發控制(Multi Version Concurrency Control),一個行記錄數據有多個版本對快照數據，這些快照數據在undo log中。如果一個事務讀取的行正在做DELELE或者UPDATE操作，讀取操作不會等行上的鎖釋放，而是讀取該行的快照版本。

持久性（durability）

一旦事務提交，則其所做的修改會永久保存到資料庫。（此時即使系統崩潰，修改的數據也不會丟失。持久性是個有占模糊的概念，因為實際上持久性也分很多不同的級別。有些持久性策略能夠提供非常強的安全保障，而有些則未必，而且不可能有能做到100%的持久性保證的策略。）

是利用Innodb的redo log。當做數據修改的時候，不僅在內存中操作，還會在redo log中記錄這次操作。當事務提交的時候，會將redo log日誌進行刷盤(redo log一部分在內存中，一部分在磁碟上)。當資料庫宕機重啟的時候，會將redo log中的內容恢復到資料庫中，再根據undo log和binlog內容決定回滾數據還是提交數據。redo log體積小，刷盤快。redo log是一直往末尾進行追加，屬於順序IO。效率顯然比隨機IO來的快

5.redis的原理和優點

redis是一個key-value存儲系統.和Memcached類似，它支持存儲的value類型相對更多，包括string(字元串)、list(鏈表)、set(集合)、zset(sorted set –有序集合)和hashs（哈希類型）

這些數據類型都支持push/pop、add/remove及取交集並集和差集及更豐富的操作，而且這些操作都是原子性的.

在此基礎上，redis支持各種不同方式的排序.與memcached一樣，為了保證效率，數據都是緩存在內存中.區別的是redis會周期性的把更新的數據寫入磁碟或者把修改操作寫入追加的記錄文件，並且在此基礎上實現了master-slave(主從)同步.

Redis的優點:

性能極高 – Redis能支持超過 100K+ 每秒的讀寫頻率。

豐富的數據類型 – Redis支持二進位案例的 Strings, Lists, Hashes, Sets 及 Ordered Sets 數據類型操作。

原子 – Redis的所有操作都是原子性的，同時Redis還支持對幾個操作全並後的原子性執行。

豐富的特性 – Redis還支持 publish/subscribe, 通知, key 過期等等特性。

6.Mysql中的鎖機制

Mysql用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。這些鎖統稱為悲觀鎖

MySQL的鎖機制比較簡單，其最 顯著的特點是不同的存儲引擎支持不同的鎖機制。比如，MyISAM和MEMORY存儲引擎採用的是表級鎖（table-level locking）；BDB存儲引擎採用的是頁面鎖（page-level locking），但也支持表級鎖；InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是採用行級鎖。

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低。

行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高。

頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般

從上述特點可見，很難籠統地說哪種鎖更好，只能就具體應用的特點來說哪種鎖更合適！僅從鎖的角度 來說：表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用；而行級鎖則更適合於有大量按索引條件並發更新少量不同數據，同時又有 並發查詢的應用，如一些在線事務處理（OLTP）系統。

7.ABC聯合索引生效問題

對於複合索引：Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 .當最左側欄位是常量引用時，索引就十分有效。

對於複合索引：Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 .當最左側欄位是常量引用時，索引就十分有效。

「春招系列」MySQL面試核心25問（附答案）

篇幅所限本文只寫了MySQL25題，像其他的Redis，SSM框架，演算法，計網等技術棧的面試題後面會持續更新，個人整理的1000餘道面試八股文會放在文末給大家白嫖，最近有面試需要刷題的同學可以直接翻到文末領取。

如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序添加到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁。如果使用非自增主鍵（如果身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置， 頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZE TABLE（optimize table）來重建表並優化填充頁面。

Server層按順序執行sql的步驟為：

簡單概括：

可以分為服務層和存儲引擎層兩部分，其中：

服務層包括連接器、查詢緩存、分析器、優化器、執行器等 ，涵蓋MySQL的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。

存儲引擎層負責數據的存儲和提取 。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎。現在最常用的存儲引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認的存儲引擎。

Drop、Delete、Truncate都表示刪除，但是三者有一些差別：

Delete 用來刪除表的全部或者一部分數據行，執行Delete之後，用戶需要提交(commmit)或者回滾(rollback)來執行刪除或者撤銷刪除，會觸發這個表上所有的delete觸發器。

Truncate 刪除表中的所有數據，這個操作不能回滾，也不會觸發這個表上的觸發器，TRUNCATE比Delete更快，佔用的空間更小。

Drop 命令從資料庫中刪除表，所有的數據行，索引和許可權也會被刪除，所有的DML觸發器也不會被觸發，這個命令也不能回滾。

因此，在不再需要一張表的時候，用Drop；在想刪除部分數據行時候，用Delete；在保留表而刪除所有數據的時候用Truncate。

隔離級別臟讀不可重複讀幻影讀 READ-UNCOMMITTED 未提交讀 READ-COMMITTED 提交讀 REPEATABLE-READ 重複讀 SERIALIZABLE 可串列化讀

MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ （可重讀）

這裡需要注意的是 ：與 SQL 標準不同的地方在於InnoDB 存儲引擎在 REPEATABLE-READ（可重讀）事務隔離級別 下使用的是 Next-Key Lock 鎖 演算法，因此可以避免幻讀的產生，這與其他資料庫系統(如 SQL Server)是不同的。所以 說InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀） 已經可以完全保證事務的隔離性要 求，即達到了 SQL標準的SERIALIZABLE(可串列化)隔離級別。

因為隔離級別越低，事務請求的鎖越少，所以大部分資料庫系統的隔離級別都是READ-COMMITTED(讀取提交內 容):，但是你要知道的是InnoDB 存儲引擎默認使用 REPEATABLE-READ（可重讀）並不會有任何性能損失 。

InnoDB 存儲引擎在分散式事務 的情況下一般會用到SERIALIZABLE(可串列化)隔離級別。

主要原因：B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷，而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節點，效率太低。

文件與資料庫都是需要較大的存儲，也就是說，它們都不可能全部存儲在內存中，故需要存儲到磁碟上。而所謂索引，則為了數據的快速定位與查找，那麼索引的結構組織要盡量減少查找過程中磁碟I/O的存取次數，因此B+樹相比B樹更為合適。資料庫系統巧妙利用了局部性原理與磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入，而紅黑樹這種結構，高度明顯要深的多，並且由於邏輯上很近的節點(父子)物理上可能很遠，無法利用局部性。

最重要的是，B+樹還有一個最大的好處：方便掃庫。

B樹必須用中序遍歷的方法按序掃庫，而B+樹直接從葉子結點挨個掃一遍就完了，B+樹支持range-query非常方便，而B樹不支持，這是資料庫選用B+樹的最主要原因。

B+樹查找效率更加穩定，B樹有可能在中間節點找到數據，穩定性不夠。

B+tree的磁碟讀寫代價更低：B+tree的內部結點並沒有指向關鍵字具體信息的指針(紅色部分)，因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一塊盤中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多，相對來說IO讀寫次數也就降低了；

B+tree的查詢效率更加穩定：由於內部結點並不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引，所以，任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當；

視圖是一種虛擬的表，通常是有一個表或者多個表的行或列的子集，具有和物理表相同的功能 游標是對查詢出來的結果集作為一個單元來有效的處理。一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。

而在 MySQL 中，恢復機制是通過回滾日誌（undo log）實現的，所有事務進行的修改都會先記錄到這個回滾日誌中，然後在對資料庫中的對應行進行寫入。當事務已經被提交之後，就無法再次回滾了。

回滾日誌作用：1)能夠在發生錯誤或者用戶執行 ROLLBACK 時提供回滾相關的信息 2) 在整個系統發生崩潰、資料庫進程直接被殺死後，當用戶再次啟動資料庫進程時，還能夠立刻通過查詢回滾日誌將之前未完成的事務進行回滾，這也就需要回滾日誌必須先於數據持久化到磁碟上，是我們需要先寫日誌後寫資料庫的主要原因。

InnoDB

MyISAM

總結

資料庫並發會帶來臟讀、幻讀、丟棄更改、不可重複讀這四個常見問題，其中：

臟讀 ：在第一個修改事務和讀取事務進行的時候，讀取事務讀到的數據為100，這是修改之後的數據，但是之後該事務滿足一致性等特性而做了回滾操作，那麼讀取事務得到的結果就是臟數據了。

幻讀 ：一般是T1在某個範圍內進行修改操作（增加或者刪除），而T2讀取該範圍導致讀到的數據是修改之間的了，強調範圍。

丟棄修改 ：兩個寫事務T1 T2同時對A=0進行遞增操作，結果T2覆蓋T1，導致最終結果是1 而不是2，事務被覆蓋

不可重複讀 ：T2 讀取一個數據，然後T1 對該數據做了修改。如果 T2 再次讀取這個數據，此時讀取的結果和第一次讀取的結果不同。

第一個事務首先讀取var變數為50，接著準備更新為100的時，並未提交，第二個事務已經讀取var為100，此時第一個事務做了回滾。最終第二個事務讀取的var和資料庫的var不一樣。

T1 讀取某個範圍的數據，T2 在這個範圍內插入新的數據，T1 再次讀取這個範圍的數據，此時讀取的結果和和第一次讀取的結果不同。

T1 和 T2 兩個事務都對一個數據進行修改，T1 先修改，T2 隨後修改，T2 的修改覆蓋了 T1 的修改。例如：事務1讀取某表中的數據A=50，事務2也讀取A=50，事務1修改A=A+50，事務2也修改A=A+50，最終結果A=100，事務1的修改被丟失。

T2 讀取一個數據，T1 對該數據做了修改。如果 T2 再次讀取這個數據，此時讀取的結果和第一次讀取的結果不同。

悲觀鎖，先獲取鎖，再進行業務操作，一般就是利用類似 SELECT … FOR UPDATE 這樣的語句，對數據加鎖，避免其他事務意外修改數據。當資料庫執行SELECT … FOR UPDATE時會獲取被select中的數據行的行鎖，select for update獲取的行鎖會在當前事務結束時自動釋放，因此必須在事務中使用。

樂觀鎖，先進行業務操作，只在最後實際更新數據時進行檢查數據是否被更新過。Java 並發包中的 AtomicFieldUpdater 類似，也是利用 CAS 機制，並不會對數據加鎖，而是通過對比數據的時間戳或者版本號，來實現樂觀鎖需要的版本判斷。

分庫與分表的目的在於，減小資料庫的單庫單表負擔，提高查詢性能，縮短查詢時間。

通過分表 ，可以減少資料庫的單表負擔，將壓力分散到不同的表上，同時因為不同的表上的數據量少了，起到提高查詢性能，縮短查詢時間的作用，此外，可以很大的緩解表鎖的問題。分表策略可以歸納為垂直拆分和水平拆分:

水平分表 ：取模分表就屬於隨機分表，而時間維度分表則屬於連續分表。如何設計好垂直拆分，我的建議：將不常用的欄位單獨拆分到另外一張擴展表. 將大文本的欄位單獨拆分到另外一張擴展表, 將不經常修改的欄位放在同一張表中，將經常改變的欄位放在另一張表中。對於海量用戶場景，可以考慮取模分表，數據相對比較均勻，不容易出現熱點和並發訪問的瓶頸。

庫內分表 ，僅僅是解決了單表數據過大的問題，但並沒有把單表的數據分散到不同的物理機上，因此並不能減輕 MySQL 伺服器的壓力，仍然存在同一個物理機上的資源競爭和瓶頸，包括 CPU、內存、磁碟 IO、網路帶寬等。

分庫與分錶帶來的分散式困境與應對之策 數據遷移與擴容問題—-一般做法是通過程序先讀出數據，然後按照指定的分表策略再將數據寫入到各個分表中。分頁與排序問題—-需要在不同的分表中將數據進行排序並返回，並將不同分表返回的結果集進行匯總和再次排序，最後再返回給用戶。

不可重複讀的重點是修改，幻讀的重點在於新增或者刪除。

視圖是虛擬的表，與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢；不包含任何列或數據。使用視圖可以簡化複雜的 sql 操作，隱藏具體的細節，保護數據；視圖創建後，可以使用與表相同的方式利用它們。

視圖不能被索引，也不能有關聯的觸發器或默認值，如果視圖本身內有order by 則對視圖再次order by將被覆蓋。

創建視圖：create view xxx as xxxx

對於某些視圖比如未使用聯結子查詢分組聚集函數Distinct Union等，是可以對其更新的，對視圖的更新將對基表進行更新；但是視圖主要用於簡化檢索，保護數據，並不用於更新，而且大部分視圖都不可以更新。

B+tree的磁碟讀寫代價更低，B+tree的查詢效率更加穩定 資料庫索引採用B+樹而不是B樹的主要原因：B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷，而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節點，效率太低。

B+樹的特點

在最頻繁使用的、用以縮小查詢範圍的欄位,需要排序的欄位上建立索引。不宜：1）對於查詢中很少涉及的列或者重複值比較多的列 2）對於一些特殊的數據類型，不宜建立索引，比如文本欄位（text）等。

如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱 之為「覆蓋索引」。

我們知道在InnoDB存儲引 擎中，如果不是主鍵索引，葉子節點存儲的是主鍵+列值。最終還是要「回表」，也就是要通過主鍵再查找一次,這樣就 會比較慢。覆蓋索引就是把要查詢出的列和索引是對應的，不做回表操作！

舉例 ：

學號姓名性別年齡系別專業 20020612李輝男20計算機軟體開發 20060613張明男18計算機軟體開發 20060614王小玉女19物理力學 20060615李淑華女17生物動物學 20060616趙靜男21化學食品化學 20060617趙靜女20生物植物學

主鍵為候選鍵的子集，候選鍵為超鍵的子集，而外鍵的確定是相對於主鍵的。