mysql數據庫知識點6,mysql數據庫考點

本文目錄一覽：

• 1、MySQL知識點總結
• 2、mysql數據庫
• 3、mysql基本操作有哪些
• 4、mysql 核心內容-上
• 5、mysql的特點是什麼?

MySQL知識點總結

只要字段值還可以繼續拆分，就不滿足第一範式。

範式設計得越詳細，對某些實際操作可能會更好，但並非都有好處，需要對項目的實際情況進行設定。

在滿足第一範式的前提下，其他列都必須完全依賴於主鍵列。 如果出現不完全依賴，只可能發生在聯合主鍵的情況下：

實際上，在這張訂單表中，product_name 只依賴於 product_id ，customer_name 只依賴於 customer_id。也就是說，product_name 和 customer_id 是沒用關係的，customer_name 和 product_id 也是沒有關係的。

這就不滿足第二範式：其他列都必須完全依賴於主鍵列！

拆分之後，myorder 表中的 product_id 和 customer_id 完全依賴於 order_id 主鍵，而 product 和 customer 表中的其他字段又完全依賴於主鍵。滿足了第二範式的設計！

在滿足第二範式的前提下，除了主鍵列之外，其他列之間不能有傳遞依賴關係。

表中的 customer_phone 有可能依賴於 order_id 、 customer_id 兩列，也就不滿足了第三範式的設計：其他列之間不能有傳遞依賴關係。

修改後就不存在其他列之間的傳遞依賴關係，其他列都只依賴於主鍵列，滿足了第三範式的設計！

查詢每門課的平均成績。

查詢 score 表中至少有 2 名學生選修，並以 3 開頭的課程的平均分數。

分析表發現，至少有 2 名學生選修的課程是 3-105 、3-245 、6-166 ，以 3 開頭的課程是 3-105 、3-245。也就是說，我們要查詢所有 3-105 和 3-245 的 degree 平均分。

查詢所有學生的 name，以及該學生在 score 表中對應的 c_no 和 degree 。

通過分析可以發現，只要把 score 表中的 s_no 字段值替換成 student 表中對應的 name 字段值就可以了，如何做呢？

查詢所有學生的 no 、課程名稱 ( course 表中的 name ) 和成績 ( score 表中的 degree ) 列。

只有 score 關聯學生的 no ，因此只要查詢 score 表，就能找出所有和學生相關的 no 和 degree ：

然後查詢 course 表：

只要把 score 表中的 c_no 替換成 course 表中對應的 name 字段值就可以了。

查詢所有學生的 name 、課程名 ( course 表中的 name ) 和 degree 。

只有 score 表中關聯學生的學號和課堂號，我們只要圍繞着 score 這張表查詢就好了。

只要把 s_no 和 c_no 替換成 student 和 srouse 表中對應的 name 字段值就好了。

首先把 s_no 替換成 student 表中的 name 字段：

再把 c_no 替換成 course 表中的 name 字段：

查詢 95031 班學生每門課程的平均成績。

在 score 表中根據 student 表的學生編號篩選出學生的課堂號和成績：

這時只要將 c_no 分組一下就能得出 95031 班學生每門課的平均成績：

查詢在 3-105 課程中，所有成績高於 109 號同學的記錄。

首先篩選出課堂號為 3-105 ，在找出所有成績高於 109 號同學的的行。

查詢所有成績高於 109 號同學的 3-105 課程成績記錄。

查詢所有和 101 、108 號學生同年出生的 no 、name 、birthday 列。

查詢 ‘張旭’ 教師任課的學生成績表。

首先找到教師編號：

通過 sourse 表找到該教師課程號：

通過篩選出的課程號查詢成績表：

查詢某選修課程多於5個同學的教師姓名。

首先在 teacher 表中，根據 no 字段來判斷該教師的同一門課程是否有至少5名學員選修：

查看和教師編號有有關的表的信息：

我們已經找到和教師編號有關的字段就在 course 表中，但是還無法知道哪門課程至少有5名學生選修，所以還需要根據 score 表來查詢：

根據篩選出來的課程號，找出在某課程中，擁有至少5名學員的教師編號：

在 teacher 表中，根據篩選出來的教師編號找到教師姓名：

查詢 「計算機系」 課程的成績表。

思路是，先找出 course 表中所有 計算機系 課程的編號，然後根據這個編號查詢 score 表。

查詢 計算機系 與 電子工程系 中的不同職稱的教師。

查詢課程 3-105 且成績 至少 高於 3-245 的 score 表。

查詢課程 3-105 且成績高於 3-245 的 score 表。

查詢某課程成績比該課程平均成績低的 score 表。

查詢所有任課 ( 在 course 表裡有課程 ) 教師的 name 和 department 。

查詢 student 表中至少有 2 名男生的 class 。

查詢 student 表中不姓 “王” 的同學記錄。

查詢 student 表中每個學生的姓名和年齡。

查詢 student 表中最大和最小的 birthday 值。

以 class 和 birthday 從大到小的順序查詢 student 表。

查詢 “男” 教師及其所上的課程。

查詢最高分同學的 score 表。

查詢和 “李軍” 同性別的所有同學 name 。

查詢和 “李軍” 同性別且同班的同學 name 。

查詢所有選修 “計算機導論” 課程的 “男” 同學成績表。

需要的 “計算機導論” 和性別為 “男” 的編號可以在 course 和 student 表中找到。

建立一個 grade 表代表學生的成績等級，並插入數據：

查詢所有學生的 s_no 、c_no 和 grade 列。

思路是，使用區間 ( BETWEEN ) 查詢，判斷學生的成績 ( degree ) 在 grade 表的 low 和 upp 之間。

準備用於測試連接查詢的數據：

分析兩張表發現，person 表並沒有為 cardId 字段設置一個在 card 表中對應的 id 外鍵。如果設置了的話，person 中 cardId 字段值為 6 的行就插不進去，因為該 cardId 值在 card 表中並沒有。

要查詢這兩張表中有關係的數據，可以使用 INNER JOIN ( 內連接 ) 將它們連接在一起。

完整顯示左邊的表 ( person ) ，右邊的表如果符合條件就顯示，不符合則補 NULL 。

完整顯示右邊的表 ( card ) ，左邊的表如果符合條件就顯示，不符合則補 NULL 。

完整顯示兩張表的全部數據。

在 MySQL 中，事務其實是一個最小的不可分割的工作單元。事務能夠 保證一個業務的完整性 。

比如我們的銀行轉賬：

在實際項目中，假設只有一條 SQL 語句執行成功，而另外一條執行失敗了，就會出現數據前後不一致。

因此，在執行多條有關聯 SQL 語句時， 事務 可能會要求這些 SQL 語句要麼同時執行成功，要麼就都執行失敗。

在 MySQL 中，事務的 自動提交 狀態默認是開啟的。

自動提交的作用 ：當我們執行一條 SQL 語句的時候，其產生的效果就會立即體現出來，且不能 回滾 。

什麼是回滾？舉個例子：

可以看到，在執行插入語句後數據立刻生效，原因是 MySQL 中的事務自動將它 提交 到了數據庫中。那麼所謂 回滾 的意思就是，撤銷執行過的所有 SQL 語句，使其回滾到 最後一次提交 數據時的狀態。

在 MySQL 中使用 ROLLBACK 執行回滾：

由於所有執行過的 SQL 語句都已經被提交過了，所以數據並沒有發生回滾。那如何讓數據可以發生回滾？

將自動提交關閉後，測試數據回滾：

那如何將虛擬的數據真正提交到數據庫中？使用 COMMIT :

事務的實際應用 ，讓我們再回到銀行轉賬項目：

這時假設在轉賬時發生了意外，就可以使用 ROLLBACK 回滾到最後一次提交的狀態：

這時我們又回到了發生意外之前的狀態，也就是說，事務給我們提供了一個可以反悔的機會。假設數據沒有發生意外，這時可以手動將數據真正提交到數據表中：COMMIT 。

事務的默認提交被開啟 ( @@AUTOCOMMIT = 1 ) 後，此時就不能使用事務回滾了。但是我們還可以手動開啟一個事務處理事件，使其可以發生回滾：

仍然使用 COMMIT 提交數據，提交後無法再發生本次事務的回滾。

事務的四大特徵：

事務的隔離性可分為四種 ( 性能從低到高 ) ：

查看當前數據庫的默認隔離級別：

修改隔離級別：

測試 READ UNCOMMITTED ( 讀取未提交 ) 的隔離性：

由於小明的轉賬是在新開啟的事務上進行操作的，而該操作的結果是可以被其他事務（另一方的淘寶店）看見的，因此淘寶店的查詢結果是正確的，淘寶店確認到賬。但就在這時，如果小明在它所處的事務上又執行了 ROLLBACK 命令，會發生什麼？

這就是所謂的 臟讀 ，一個事務讀取到另外一個事務還未提交的數據。這在實際開發中是不允許出現的。

把隔離級別設置為 READ COMMITTED ：

這樣，再有新的事務連接進來時，它們就只能查詢到已經提交過的事務數據了。但是對於當前事務來說，它們看到的還是未提交的數據，例如：

但是這樣還有問題，那就是假設一個事務在操作數據時，其他事務干擾了這個事務的數據。例如：

雖然 READ COMMITTED 讓我們只能讀取到其他事務已經提交的數據，但還是會出現問題，就是 在讀取同一個表的數據時，可能會發生前後不一致的情況。* 這被稱為* 不可重複讀現象 ( READ COMMITTED ) 。

將隔離級別設置為 REPEATABLE READ ( 可被重複讀取 ) :

測試 REPEATABLE READ ，假設在兩個不同的連接上分別執行 START TRANSACTION :

當前事務開啟後，沒提交之前，查詢不到，提交後可以被查詢到。但是，在提交之前其他事務被開啟了，那麼在這條事務線上，就不會查詢到當前有操作事務的連接。相當於開闢出一條單獨的線程。

無論小張是否執行過 COMMIT ，在小王這邊，都不會查詢到小張的事務記錄，而是只會查詢到自己所處事務的記錄：

這是 因為小王在此之前開啟了一個新的事務 ( START TRANSACTION ) * ，那麼* 在他的這條新事務的線上，跟其他事務是沒有聯繫的 ，也就是說，此時如果其他事務正在操作數據，它是不知道的。

然而事實是，在真實的數據表中，小張已經插入了一條數據。但是小王此時並不知道，也插入了同一條數據，會發生什麼呢？

報錯了，操作被告知已存在主鍵為 6 的字段。這種現象也被稱為 幻讀，一個事務提交的數據，不能被其他事務讀取到 。

顧名思義，就是所有事務的 寫入操作 全都是串行化的。什麼意思？把隔離級別修改成 SERIALIZABLE :

還是拿小張和小王來舉例：

此時會發生什麼呢？由於現在的隔離級別是 SERIALIZABLE ( 串行化 ) ，串行化的意思就是：假設把所有的事務都放在一個串行的隊列中，那麼所有的事務都會按照 固定順序執行 ，執行完一個事務後再繼續執行下一個事務的 寫入操作 ( 這意味着隊列中同時只能執行一個事務的寫入操作 ) 。

根據這個解釋，小王在插入數據時，會出現等待狀態，直到小張執行 COMMIT 結束它所處的事務，或者出現等待超時。

轉載：

mysql數據庫

MySQL數據庫一般指MySQL，MySQL是一個關係型數據庫管理系統，由瑞典MySQL AB 公司開發。

mysql是目前網站以及APP應用上用得較多的一個開源的關係型數據庫系統，可以對數據進行保存，分段化的數據保存，也可以對其數據進行檢索，查詢等功能的數據庫。

默認的mysql數據庫中存有一個庫這個就是mysql的系統數據庫，可以對其保存系統的數據包括mysql數據庫的信息，數據庫root賬號，普通賬號，以及數據庫的名稱，還有數據庫的一些表還有一些數字型的數據類型結構都會有所保存。

mysql數據庫的優點

（1）MySQL數據庫是用C和C++語言編寫的，並且使用了多種編輯器進行測試，以保證源碼的可移植性。

（2）支持多個操作系統例如：Windows、Linux、Mac OS等等。

（3）支持多線程，可以充分的利用CPU資源。

（4）為多種編程語言提供API，包括C語言、Java、PHP、Python語言等。

（5）MySQL優化了SQL算法，有效的提高了查詢速度。

（6）MySQL內提供了用於管理，檢查以及優化數據庫操作的管理工具。

（7）它能夠作為一個單獨的應用程序應用在客戶端服務器網絡環境中，也可以作為一個庫嵌入到其他的軟件中並提供多種語言支持。

mysql基本操作有哪些

mysql基本操作有：

命令行

1、顯示當前數據庫服務器中的數據庫列表：mysql SHOW DATABASES;

2、建立數據庫：mysql CREATE DATABASE 庫名;

3、建立數據表：mysql USE 庫名;mysql CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

4、刪除數據庫：mysql DROP DATABASE 庫名;

5、刪除數據表：mysql DROP TABLE 表名；

6、將表中記錄清空：mysql DELETE FROM 表名;

7、往表中插入記錄：mysql INSERT INTO 表名 VALUES (“hyq”,”M”);

8、更新表中數據：mysql- UPDATE 表名 SET 字段名1=’a’,字段名2=’b’ WHERE 字段名3=’c’;

9、用文本方式將數據裝入數據表中：mysql load data local infile “d:/mysql.txt” into table 表名;

10、導入.sql文件命令：mysql USE 數據庫名;mysql source d:/mysql.sql;

11、命令行修改root密碼：mysql update mysql.user set password=password(‘新密碼’) where user=’root’;mysql flush privileges;

12.修改密碼的三種方法:mysqlupdate user set password=password(‘123456′) where user=’joy_pen’;mysqlflush privileges;mysqlset password for ‘joy_oen’=password(‘123456’);mysqlgrant usage on *.* to ‘joy_pen’ identified by ‘123456’;

1、創建數據庫

命令：create database 數據庫名 例如：建立一個名為xhkdb的數據庫mysql create database xhkdb;

2、顯示所有的數據庫

命令：show databases （注意：最後有個s）mysql show databases;

3、刪除數據庫

命令：drop database 數據庫名 例如：刪除名為 xhkdb的數據庫mysql drop database xhkdb;

4、連接數據庫

命令： use 數據庫名 例如：如果xhkdb數據庫存在，嘗試存取它：mysql use xhkdb; 屏幕提示：Database changed

5、當前選擇（連接）的數據庫mysql select database();

6、當前數據庫包含的表信息：mysql show tables; （注意：最後有個s）

三、表操作，操作之前應連接某個數據庫

1、建表

命令：create table 表名 ( 字段名1 類型1 [,..字段名n 類型n]);

mysql create table MyClass(

id int(4) not null primary key auto_increment,

name char(20) not null,

sex int(4) not null default ”0”,

degree double(16,2));

2、獲取表結構

命令： desc 表名，或者show columns from 表名

mysqlDESCRIBE MyClass

mysql desc MyClass;

mysql show columns from MyClass;

3、刪除表

命令：drop table 表名

例如：刪除表名為 MyClass 的表 mysql drop table MyClass;

4、插入數據

命令：insert into 表名 [( 字段名1[,..字段名n ])] values ( 值1 )[, ( 值n )]

例如，往表 MyClass中插入二條記錄, 這二條記錄表示：編號為1的名為Tom的成績為96.45, 編號為2 的名為Joan 的成績為82.99，編號為3 的名為Wang 的成績為96.5.

mysql insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (2,’Wang’, 96.59);

5、查詢表中的數據

1)、查詢所有行

命令： select 字段1，字段2，… from 表名 where 表達式

例如：查看錶 MyClass 中所有數據 mysql select * from MyClass;

2）、查詢前幾行數據

例如：查看錶 MyClass 中前2行數據

mysql select * from MyClass order by id limit 0,2;

6、刪除表中數據

命令：delete from 表名 where 表達式

例如：刪除表 MyClass中編號為1 的記錄

mysql delete from MyClass where id=1;

7、修改表中數據：update 表名 set 字段=新值,… where 條件

mysql update MyClass set name=”Mary” where id=1;

8、在表中增加字段：

命令：alter table 表名 add 字段 類型 其他;

例如：在表MyClass中添加了一個字段passtest，類型為int(4)，默認值為0

mysql alter table MyClass add passtest int(4) default ”0”

9、更改表名：

命令：rename table 原表名 to 新表名;

例如：在表MyClass名字更改為YouClass

mysql rename table MyClass to YouClass;

更新字段內容

update 表名 set 字段名 = 新內容

update 表名 set 字段名 = replace(字段名,”舊內容”,”新內容”);

mysql 核心內容-上

1、SQL語句執行流程

MySQL大體上可分為Server層和存儲引擎層兩部分。

Server層：

連接器：TCP握手後服務器來驗證登陸用戶身份，A用戶創建連接後，管理員對A用戶權限修改了也不會影響到已經創建的鏈接權限，必須重新登陸。

查詢緩存：查詢後的結果存儲位置，MySQL8.0版本以後已經取消，因為查詢緩存失效太頻繁，得不償失。

分析器：根據語法規則，判斷你輸入的這個SQL語句是否滿足MySQL語法。

優化器：多種執行策略可實現目標，系統自動選擇最優進行執行。

執行器：判斷是否有權限，將最終任務提交到存儲引擎。

存儲引擎層

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

SQL執行順序

2、BinLog、RedoLog、UndoLog

BinLog

BinLog是記錄所有數據庫表結構變更（例如create、alter table）以及表數據修改(insert、update、delete)的二進制日誌，主從數據庫同步用到的都是BinLog文件。BinLog日誌文件有三種模式。

STATEMENT 模式

內容：binlog 記錄可能引起數據變更的 sql 語句

優勢：該模式下，因為沒有記錄實際的數據，所以日誌量很少 IO 都消耗很低，性能是最優的

劣勢：但有些操作並不是確定的，比如 uuid() 函數會隨機產生唯一標識，當依賴 binlog 回放時，該操作生成的數據與原數據必然是不同的，此時可能造成無法預料的後果。

ROW 模式

內容：在該模式下，binlog 會記錄每次操作的源數據與修改後的目標數據，StreamSets就要求該模式。

優勢：可以絕對精準的還原，從而保證了數據的安全與可靠，並且複製和數據恢復過程可以是並發進行的

劣勢：缺點在於 binlog 體積會非常大，同時，對於修改記錄多、字段長度大的操作來說，記錄時性能消耗會很嚴重。閱讀的時候也需要特殊指令來進行讀取數據。

MIXED 模式

內容：是對上述STATEMENT 跟 ROW 兩種模式的混合使用。

細節：對於絕大部分操作，都是使用 STATEMENT 來進行 binlog 沒有記錄，只有以下操作使用 ROW 來實現：表的存儲引擎為 NDB，使用了uuid() 等不確定函數，使用了 insert delay 語句，使用了臨時表

主從同步流程：

1、主節點必須啟用二進制日誌，記錄任何修改了數據庫數據的事件。

2、從節點開啟一個線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協議，請求主節點的二進制日誌文件中的事件 。

3、主節點啟動一個線程（dump Thread），檢查自己二進制日誌中的事件，跟對方請求的位置對比，如果不帶請求位置參數，則主節點就會從第一個日誌文件中的第一個事件一個一個發送給從節點。

4、從節點接收到主節點發送過來的數據把它放置到中繼日誌（Relay log）文件中。並記錄該次請求到主節點的具體哪一個二進制日誌文件內部的哪一個位置（主節點中的二進制文件會有多個）。

5、從節點啟動另外一個線程（sql Thread ），把 Relay log 中的事件讀取出來，並在本地再執行一次。

mysql默認的複製方式是異步的，並且複製的時候是有並行複製能力的。主庫把日誌發送給從庫後不管了，這樣會產生一個問題就是假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫後，日誌就丟失了。由此產生兩個概念。

全同步複製

主庫寫入binlog後強制同步日誌到從庫，所有的從庫都執行完成後才返回給客戶端，但是很顯然這個方式的話性能會受到嚴重影響。

半同步複製

半同步複製的邏輯是這樣，從庫寫入日誌成功後返回ACK確認給主庫，主庫收到至少一個從庫的確認就認為寫操作完成。

還可以延伸到由於主從配置不一樣、主庫大事務、從庫壓力過大、網絡震蕩等造成主備延遲，如何避免這個問題？主備切換的時候用可靠性優先原則還是可用性優先原則？如何判斷主庫Crash了？互為主備的情況下如何避免主備循環複製？被刪庫跑路了如何正確恢復？( o )… 感覺越來越扯到DBA的活兒上去了。

RedoLog

可以先通過下面demo理解：

飯點記賬可以把賬單寫在賬本上也可以寫在粉板上。有人賒賬或者還賬的話，一般有兩種做法：

1、直接把賬本翻出來，把這次賒的賬加上去或者扣除掉。

2、先在粉板上記下這次的賬，等打烊以後再把賬本翻出來核算。

生意忙時選後者，因為前者太麻煩了。得在密密麻麻的記錄中找到這個人的賒賬總額信息，找到之後再拿出算盤計算，最後再將結果寫回到賬本上。

同樣在MySQL中如果每一次的更新操作都需要寫進磁盤，然後磁盤也要找到對應的那條記錄，然後再更新，整個過程IO成本、查找成本都很高。而粉板和賬本配合的整個過程就是MySQL用到的是Write-Ahead Logging 技術，它的關鍵點就是先寫日誌，再寫磁盤。此時賬本 = BinLog，粉板 = RedoLog。

1、 記錄更新時，InnoDB引擎就會先把記錄寫到RedoLog（粉板）裏面，並更新內存。同時，InnoDB引擎會在空閑時將這個操作記錄更新到磁盤裏面。

2、 如果更新太多RedoLog處理不了的時候，需先將RedoLog部分數據寫到磁盤，然後擦除RedoLog部分數據。RedoLog類似轉盤。

RedoLog有write pos 跟checkpoint

write pos ：是當前記錄的位置，一邊寫一邊後移，寫到第3號文件末尾後就回到0號文件開頭。

check point：是當前要擦除的位置，也是往後推移並且循環的，擦除記錄前要把記錄更新到數據文件。

write pos和check point之間的是粉板上還空着的部分，可以用來記錄新的操作。如果write pos追上checkpoint，表示粉板滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把checkpoint推進一下。

有了redo log，InnoDB就可以保證即使數據庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。 redolog兩階段提交：為了讓binlog跟redolog兩份日誌之間的邏輯一致。提交流程大致如下：

1 prepare階段 — 2 寫binlog — 3 commit

當在2之前崩潰時，重啟恢復後發現沒有commit，回滾。備份恢復：沒有binlog 。一致

當在3之前崩潰時，重啟恢複發現雖沒有commit，但滿足prepare和binlog完整，所以重啟後會自動commit。備份：有binlog. 一致

binlog跟redolog區別：

redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用。

redo log是物理日誌，記錄的是在某個數據頁上做了什麼修改；binlog是邏輯日誌，記錄的是這個語句的原始邏輯，比如給ID=2這一行的c字段加1。

redo log是循環寫的，空間固定會用完；binlog是可以追加寫入的。追加寫是指binlog文件寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

UndoLog

UndoLog 一般是邏輯日誌，主要分為兩種：

insert undo log

代表事務在insert新記錄時產生的undo log, 只在事務回滾時需要，並且在事務提交後可以被立即丟棄

update undo log

事務在進行update或delete時產生的undo log; 不僅在事務回滾時需要，在快照讀時也需要；所以不能隨便刪除，只有在快速讀或事務回滾不涉及該日誌時，對應的日誌才會被purge線程統一清除

3、MySQL中的索引

索引的常見模型有哈希表、有序數組和搜索樹。

哈希表：一種以KV存儲數據的結構，只適合等值查詢，不適合範圍查詢。

有序數組：只適用於靜態存儲引擎，涉及到插入的時候比較麻煩。可以參考Java中的ArrayList。

搜索樹：按照數據結構中的二叉樹來存儲數據，不過此時是N叉樹(B+樹)。廣泛應用在存儲引擎層中。

B+樹比B樹優勢在於：

B+ 樹非葉子節點存儲的只是索引，可以存儲的更多。B+樹比B樹更加矮胖，IO次數更少。

B+ 樹葉子節點前後管理，更加方便範圍查詢。同時結果都在葉子節點，查詢效率穩定。

B+樹中更有利於對數據掃描，可以避免B樹的回溯掃描。

索引的優點：

1、唯一索引可以保證每一行數據的唯一性

2、提高查詢速度

3、加速表與表的連接

4、顯著的減少查詢中分組和排序的時間

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

索引的缺點：

1、創建跟維護都需要耗時

2、創建索引時，需要對錶加鎖，在鎖表的同時，可能會影響到其他的數據操作

3、 索引需要磁盤的空間進行存儲，磁盤佔用也很快。

4、當對表中的數據進行CRUD的時，也會觸發索引的維護，而維護索引需要時間，可能會降低數據操作性能

索引設計的原則不應該：

1、索引不是越多越好。索引太多，維護索引需要時間跟空間。

2、 頻繁更新的數據，不宜建索引。

3、數據量小的表沒必要建立索引。

應該：

1、重複率小的列建議生成索引。因為重複數據少，索引樹查詢更有效率，等價基數越大越好。

2、數據具有唯一性，建議生成唯一性索引。在數據庫的層面，保證數據正確性

3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率

4、經常用於查詢條件的字段建議生成索引。通過索引查詢，速度更快

索引失效的場景

1、模糊搜索：左模糊或全模糊都會導致索引失效，比如’%a’和’%a%’。但是右模糊是可以利用索引的，比如’a%’ 。

2、隱式類型轉換：比如select * from t where name = xxx , name是字符串類型，但是沒有加引號，所以是由MySQL隱式轉換的，所以會讓索引失效 3、當語句中帶有or的時候：比如select * from t where name=『sw』 or age=14

4、不符合聯合索引的最左前綴匹配：(A,B,C)的聯合索引，你只where了C或B或只有B,C

關於索引的知識點：

主鍵索引：主鍵索引的葉子節點存的是整行數據信息。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）。主鍵自增是無法保證完全自增的哦，遇到唯一鍵衝突、事務回滾等都可能導致不連續。

唯一索引：以唯一列生成的索引，該列不允許有重複值，但允許有空值(NULL)

普通索引跟唯一索引查詢性能：InnoDB的數據是按數據頁為單位來讀寫的，默認每頁16KB，因此這兩種索引查詢數據性能差別微乎其微。

change buffer：普通索引用在更新過程的加速，更新的字段如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數據讀入內存來確保不違背唯一性，所以盡量用普通索引。

非主鍵索引：非主鍵索引的葉子節點內容是主鍵的值。在InnoDB里，非主鍵索引也被稱為二級索引（secondary index）

回表：先通過數據庫索引掃描出數據所在的行，再通過行主鍵id取出索引中未提供的數據，即基於非主鍵索引的查詢需要多掃描一棵索引樹。

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

聯合索引：相對單列索引，組合索引是用多個列組合構建的索引，一次性最多聯合16個。

最左前綴原則：對多個字段同時建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯合索引) 以聯合索引(a,b,c)為例，建立這樣的索引相當於建立了索引a、ab、abc三個索引。另外組合索引實際還是一個索引，並非真的創建了多個索引，只是產生的效果等價於產生多個索引。

索引下推：MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表字數。

索引維護：B+樹為了維護索引有序性涉及到頁分裂跟頁合併。增刪數據時需考慮頁空間利用率。

自增主鍵：一般會建立與業務無關的自增主鍵，不會觸發葉子節點分裂。

延遲關聯：通過使用覆蓋索引查詢返回需要的主鍵，再根據主鍵關聯原表獲得需要的數據。

InnoDB存儲: * .frm文件是一份定義文件，也就是定義數據庫表是一張怎麼樣的表。*.ibd文件則是該表的索引，數據存儲文件，既該表的所有索引樹，所有行記錄數據都存儲在該文件中。

MyISAM存儲：* .frm文件是一份定義文件，也就是定義數據庫表是一張怎麼樣的表。* .MYD文件是MyISAM存儲引擎表的所有行數據的文件。* .MYI文件存放的是MyISAM存儲引擎表的索引相關數據的文件。MyISAM引擎下，表數據和表索引數據是分開存儲的。

MyISAM查詢：在MyISAM下，主鍵索引和輔助鍵索引都屬於非聚簇索引。查詢不管是走主鍵索引，還是非主鍵索引，在葉子結點得到的都是目的數據的地址，還需要通過該地址，才能在數據文件中找到目的數據。

PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引

4、SQL事務隔離級別

ACID的四個特性

原子性（Atomicity）：把多個操作放到一個事務中，保證這些操作要麼都成功，要麼都不成功

一致性（Consistency）：理解成一串對數據進行操作的程序執行下來，不會對數據產生不好的影響，比如憑空產生，或消失

隔離性（Isolation，又稱獨立性）：隔離性的意思就是多個事務之間互相不干擾，即使是並發事務的情況下，他們只是兩個並發執行沒有交集，互不影響的東西；當然實現中，也不一定需要這麼完整隔離性，即不一定需要這麼的互不干擾，有時候還是允許有部分干擾的。所以MySQL可以支持4種事務隔離性

持久性（Durability）：當某個操作操作完畢了，那麼結果就是這樣了，並且這個操作會持久化到日誌記錄中

PS：ACID中C與CAP定理中C的區別

ACID的C着重強調單數據庫事務操作時，要保證數據的完整和正確性，數據不會憑空消失跟增加。CAP 理論中的C指的是對一個數據多個備份的讀寫一致性

事務操作可能會出現的數據問題

1、臟讀(dirty read)：B事務更改數據還未提交，A事務已經看到並且用了。B事務如果回滾，則A事務做錯了

2、 不可重複讀(non-repeatable read)：不可重複讀的重點是修改: 同樣的條件, 你讀取過的數據, 再次讀取出來發現值不一樣了，只需要鎖住滿足條件的記錄

3、 幻讀(phantom read)：事務A先修改了某個表的所有紀錄的狀態字段為已處理，未提交；事務B也在此時新增了一條未處理的記錄，並提交了；事務A隨後查詢記錄，卻發現有一條記錄是未處理的造成幻讀現象，幻讀僅專指新插入的行。幻讀會造成語義上的問題跟數據一致性問題。

4、 在可重複讀RR隔離級別下，普通查詢是快照讀，是不會看到別的事務插入的數據的。因此，幻讀在當前讀下才會出現。要用間隙鎖解決此問題。

在說隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL標準的事務隔離級別由低到高如下： 上圖從上到下的模式會導致系統的並行性能依次降低，安全性依次提高。

讀未提交：別人改數據的事務尚未提交，我在我的事務中也能讀到。

讀已提交(Oracle默認)：別人改數據的事務已經提交，我在我的事務中才能讀到。

可重複讀(MySQL默認)：別人改數據的事務已經提交，我在我的事務中也不去讀，以此保證重複讀一致性。

串行：我的事務尚未提交，別人就別想改數據。

標準跟實現：上面都是關於事務的標準，但是每一種數據庫都有不同的實現，比如MySQL InnDB 默認為RR級別，但是不會出現幻讀。因為當事務A更新了所有記錄的某個字段，此時事務A會獲得對這個表的表鎖，因為事務A還沒有提交，所以事務A獲得的鎖沒有釋放，此時事務B在該表插入新記錄，會因為無法獲得該表的鎖，則導致插入操作被阻塞。只有事務A提交了事務後，釋放了鎖，事務B才能進行接下去的操作。所以可以說 MySQL的RR級別的隔離是已經實現解決了臟讀，不可重複讀和幻讀的。

5、MySQL中的鎖

無論是Java的並發編程還是數據庫的並發操作都會涉及到鎖，研發人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設計思想。

悲觀鎖：

優點：適合在寫多讀少的並發環境中使用，雖然無法維持非常高的性能，但是在樂觀鎖無法提更好的性能前提下，可以做到數據的安全性

缺點：加鎖會增加系統開銷，雖然能保證數據的安全，但數據處理吞吐量低，不適合在讀書寫少的場合下使用

樂觀鎖：

優點：在讀多寫少的並發場景下，可以避免數據庫加鎖的開銷，提高DAO層的響應性能，很多情況下ORM工具都有帶有樂觀鎖的實現，所以這些方法不一定需要我們人為的去實現。

缺點：在寫多讀少的並發場景下，即在寫操作競爭激烈的情況下，會導致CAS多次重試，衝突頻率過高，導致開銷比悲觀鎖更高。

實現：數據庫層面的樂觀鎖其實跟CAS思想類似， 通數據版本號或者時間戳也可以實現。

數據庫並發場景主要有三種：

讀-讀：不存在任何問題，也不需要並發控制

讀-寫：有隔離性問題，可能遇到臟讀，幻讀，不可重複讀

寫-寫：可能存更新丟失問題，比如第一類更新丟失，第二類更新丟失

兩類更新丟失問題：

第一類更新丟失：事務A的事務回滾覆蓋了事務B已提交的結果 第二類更新丟失：事務A的提交覆蓋了事務B已提交的結果

為了合理貫徹落實鎖的思想，MySQL中引入了雜七雜八的各種鎖：

鎖分類

MySQL支持三種層級的鎖定，分別為

表級鎖定

MySQL中鎖定粒度最大的一種鎖，最常使用的MYISAM與INNODB都支持表級鎖定。

頁級鎖定

是MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。

行級鎖定

Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大行級鎖不一定比表級鎖要好：鎖的粒度越細，代價越高，相比表級鎖在表的頭部直接加鎖，行級鎖還要掃描找到對應的行對其上鎖，這樣的代價其實是比較高的，所以表鎖和行鎖各有所長。

MyISAM中的鎖

雖然MySQL支持表，頁，行三級鎖定，但MyISAM存儲引擎只支持表鎖。所以MyISAM的加鎖相對比較開銷低，但數據操作的並發性能相對就不高。但如果寫操作都是尾插入，那還是可以支持一定程度的讀寫並發

從MyISAM所支持的鎖中也可以看出，MyISAM是一個支持讀讀並發，但不支持通用讀寫並發，寫寫並發的數據庫引擎，所以它更適合用於讀多寫少的應用場合，一般工程中也用的較少。

InnoDB中的鎖

該模式下支持的鎖實在是太多了，具體如下：

共享鎖和排他鎖 (Shared and Exclusive Locks)

意向鎖（Intention Locks）

記錄鎖（Record Locks）

間隙鎖（Gap Locks）

臨鍵鎖 （Next-Key Locks）

插入意向鎖（Insert Intention Locks）

主鍵自增鎖 (AUTO-INC Locks)

空間索引斷言鎖（Predicate Locks for Spatial Indexes）

舉個栗子，比如行鎖里的共享鎖跟排它鎖：lock in share modle 共享讀鎖：

為了確保自己查到的數據沒有被其他的事務正在修改，也就是說確保查到的數據是最新的數據，並且不允許其他人來修改數據。但是自己不一定能夠修改數據，因為有可能其他的事務也對這些數據使用了 in share mode 的方式上了S 鎖。如果不及時的commit 或者rollback 也可能會造成大量的事務等待。

for update排它寫鎖:

為了讓自己查到的數據確保是最新數據，並且查到後的數據只允許自己來修改的時候，需要用到for update。相當於一個 update 語句。在業務繁忙的情況下，如果事務沒有及時的commit或者rollback 可能會造成其他事務長時間的等待，從而影響數據庫的並發使用效率。

Gap Lock間隙鎖：

1、行鎖只能鎖住行，如果在記錄之間的間隙插入數據就無法解決了，因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是左右開區間。間隙鎖之間不會衝突。

2、間隙鎖和行鎖合稱NextKeyLock，每個NextKeyLock是前開後閉區間。

間隙鎖加鎖原則(學完忘那種)：

1、加鎖的基本單位是 NextKeyLock，是前開後閉區間。

2、查找過程中訪問到的對象才會加鎖。

3、索引上的等值查詢，給唯一索引加鎖的時候，NextKeyLock退化為行鎖。

4、索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，NextKeyLock退化為間隙鎖。

5、唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。

mysql的特點是什麼?

一、MySQL數據庫的特點和優勢:

(1)功能強大

MySQL 中提供了多種數據庫存儲引擎，各引擎各有所長，適用於不同的應用場合，用戶可以選擇最合適的引擎以得到最高性能，可以處理每天訪問量超過數億的高強度的搜索 Web 站點。MySQL5 支持事務、視圖、存儲過程、觸發器等。

(2)支持跨平台

MySQL 支持至少 20 種以上的開發平台，包括 Linux、Windows、FreeBSD 、IBMAIX、AIX、FreeBSD 等。這使得在任何平台下編寫的程序都可以進行移植，而不需要對程序做任何的修改。

(3)運行速度快

高速是 MySQL 的顯著特性。在 MySQL 中，使用了極快的 B 樹磁盤表(MyISAM)和索引壓縮;通過使用優化的單掃描多連接，能夠極快地實現連接;SQL 函數使用高度優化的類庫實現，運行速度極快。

(4)支持面向對象

PHP 支持混合編程方式。編程方式可分為純粹面向對象、純粹面向過程、面句對象與面向過程混合 3 種方式。

(5)安全性高

靈活和安全的權限與密碼系統，允許基本主機的驗證。連接到服務器時，所有的密碼傳輸均採用加密形式，從而保證了密碼的安全。

(6)成本低

MySQL 數據庫開放源代碼且無版權制約，是一種完全免費的產品，用戶可以直接通過網絡下載，自主性及使用成本低。體積小，安裝方便。歷史悠久，用戶使用活躍，遇到問題可以尋求幫助，易於維護。

(7)支持各種開發語言

MySQL 為各種流行的程序設計語言提供支持，為它們提供了很多的 API 函數，包括 PHP、ASP.NET、Java、Eiffel、Python、Ruby、Tcl、C、C++、Perl 語言等。

(8)數據庫存儲容量大

MySQL 數據庫的最大有效表尺寸通常是由操作系統對文件大小的限制決定的，而不是由 MySQL 內部限制決定的。InnoDB 存儲引擎將 InnoDB 表保存在一個表空間內，該表空間可由數個文件創建，表空間的最大容量為 64TB，可以輕鬆處理擁有上千萬條記錄的大型數據庫。

(9)支持強大的內置函數

PHP 中提供了大量內置函數，幾乎涵蓋了 Web 應用開發中的所有功能。它內置了數據庫連接、文件上傳等功能，MySQL 支持大量的擴展庫，如 MySQLi 等，可以為快速開發 Web 應用提供便利。

二、相比其他數據庫的特點和優勢:

(1) 對事務的提交

MySQL默認是自動提交，不需要在寫commit指令或者點擊commit按鈕。

(2) 分頁查詢

MySQL是直接在SQL語句中寫”select… from …where…limit  m, n”,有limit就可以實現分頁。PHP里還可以用SEEK定位到結果集的位置。

(3) 事務隔離級別

MySQL是read commited的隔離級別。

一個session讀取數據時，其他session不能更改數據，但可以在表最後插入數據。session更新數據時，要加上排它鎖，其他session無法訪問數據。

(4) 複製簡單

MySQL複製服務器配置簡單。

(5) 自動增長的數據類型處理

MYSQL有自動增長的數據類型，插入記錄時不用操作此字段，會自動獲得數據值。

(6) 單引號的處理

MYSQL里可以用雙引號包起字符串。

(7) 日期字段的處理

MYSQL日期字段分DATE和TIME兩種。

(8) 空字符的處理

MYSQL的非空字段也有空的內容，NULL或空字符。