數據庫-MySQL

MySQL主從、集群模式簡單介紹

主從模式、集群模式，都是在一個項目中使用多個mysql節點進行存儲和讀取數據。

當單機模式部署，不滿足安全性、高可用、高并發等需求的時候，就需要考慮主從模式或者集群模式部署。

1、主從模式 Replication

主從模式，或者是叫主從架構、主從復制，有以下幾種常見方案：一主一從、一主多從、多主一從、互為主備、級聯復制等。
主數據庫必須開啟binary log（二進制）功能，因為主從同步所有的操作都是基于二進制文件來完成的。
數據同步模式有：

• 異步模式：主庫將事務binlog事件寫入到binlog文件中，此時主庫只會通知一下dump線程發送這些新的binlog，然后主庫就會繼續處理提交操作，而此時不會保證這些binlog傳到任何一個從庫節點上。
• 半同步模式：主庫只需要等待至少一個從庫節點收到并且Flush binlog到relay-log文件即可，主庫不需要等待所有從庫給主庫反饋。同時，這里只是一個收到的反饋，而不是已經完全執行并且提交的反饋。
• 全同步模式：當主庫提交事務之后，所有的從庫節點必須全部收到，APPLY并且提交這些事務，然后主庫線程才能繼續做后續操作。

1、主從復制指的是當主數據庫中進行了update、insert、delete操作導致數據發生改變時，變化會實時同步到一個或者多個從數據庫（slave）中。

2、默認情況下異步復制、無需維持長連接。

3、通過配置可以選擇想要同步的庫和表。

2、集群模式

集群最大的優點就是數據實時同步，高可用，每個節點的數據都是同步一致的，不像主從，有時會出現數據不一致，而高可用，任何一個節點宕機都不會影響業務。

集群模式有以下集中常見部署方式：

• 讀寫分離的集群模式：集群中有的節點只進行寫入操作，有的節點只進行讀取操作，每個節點的數據都是完全一致的。
• 分片（分庫分表）集群模式：集群中所有的節點表結構一致，每個節點存儲的數據不一樣。分片算法主要有兩種，一種是范圍法（1-100條數據在節點A，101-200條數據在節點B），另一種是HASH法（對每條數據按照一定的算法分配到不同的存儲節點）。
• 讀寫分離和分片模式組合應用，先進行分片模式部署，然后對每個分片進行讀寫分離模式部署。

3、主從模式部署注意事項

常用命令（執行命令之前stop服務，執行完再start）：

• 查看主節點狀態：show master status\G;
• 查看從節點狀態：show slave status\G;
• 停止同步：stop slave;
• 開啟同步：start slave;
• 修改Master_Log_File：CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000026’, MASTER_LOG_POS=0;
• 修改master節點信息：CHANGE MASTER TO MASTER_HOST=‘192.168.203.141’, MASTER_PORT=33060,MASTER_USER=‘root’, MASTER_PASSWORD=‘123456’;
• 刪除當前節點的 binlog 文件：PURGE BINARY LOGS TO ‘binlog.000001’;
• 數據的操作日志位置：SHOW BINARY LOGS;
• 數據的真實具體位置：SHOW GLOBAL VARIABLES LIKE “%datadir%”;

每個節點的slave_sql_running、Slave_IO_Running兩個字段都是YES，集群狀態才正常
主服務器查看主節點狀態，顯示的 File 字段，和從服務器查看從節點狀態，顯示的 Master_Log_File 字段，必須保持一致。
slave_sql_running為No的話，可能是主從庫數據不同步，可以同步一下數據。

數據導出命令（在mysql服務器執行，不需要登錄數據庫）：

• mysqldump -u[用戶名] -h[ip] -p[密碼] -P[端口號] --databases 數據庫名 --tables 表名 >導出的文件名.sql

數據庫導入命令（導入的時候需要指定數據庫，保證指定的數據庫存在）：

• mysqldump -u[用戶名] -h[ip] -p[密碼] -P[端口號] < 導入的文件名.sql

UNION 和 UNION ALL 區別

一、區別1：取結果的交集

1、union: 對兩個結果集進行并集操作, 不包括重復行,相當于distinct, 同時進行默認規則的排序;

2、union all: 對兩個結果集進行并集操作, 包括重復行, 即所有的結果全部顯示, 不管是不是重復;

二、區別2：獲取結果后的操作

1、union: 會對獲取的結果進行排序操作

2、union all: 不會對獲取的結果進行排序操作

三、總結

union all只是合并查詢結果，并不會進行去重和排序操作，在沒有去重的前提下，使用union all的執行效率要比union高

分庫分表

鏈接： MySQL-如何分庫分表？一看就懂_mysql分庫分表怎么實現-CSDN博客

一、為什么要分庫分表
如果一個網站業務快速發展，那這個網站流量也會增加，數據的壓力也會隨之而來，比如電商系統來說雙十一大促對訂單數據壓力很大，Tps十幾萬并發量，如果傳統的架構（一主多從），主庫容量肯定無法滿足這么高的Tps，業務越來越大，單表數據超出了數據庫支持的容量，持久化磁盤IO,傳統的數據庫性能瓶頸，產品經理業務·必須做，改變程序，數據庫刀子切分優化。數據庫連接數不夠需要分庫，表的數據量大，優化后查詢性能還是很低，需要分。

二、什么是分庫分表
分庫分表方案是對關系型數據庫數據存儲和訪問機制的一種補充。
分庫：將一個庫的數據拆分到多個相同的庫中，訪問的時候訪問一個庫
分表：把一個表的數據放到多個表中，操作對應的某個表就行

三、分庫分表的幾種方式

1.垂直拆分

(1) 數據庫垂直拆分

根據業務拆分，如圖，電商系統，拆分成訂單庫，會員庫，商品庫

(2)表垂直拆分

根據業務去拆分表，如圖，把user表拆分成user_base表和user_info表，use_base負責存儲登錄，user_info負責存儲基本用戶信息

垂直拆分特點
1.每個庫（表）的結構都不一樣
2.每個庫（表）的數據至少一列一樣
3.每個庫（表）的并集是全量數據

垂直拆分優缺點

優點：
1.拆分后業務清晰（專庫專用按業務拆分）
2.數據維護簡單，按業務不同，業務放到不同機器上

缺點:
1.如果單表的數據量，寫讀壓力大
2.受某種業務決定，或者被限制，也就是說一個業務往往會影響到數據庫的瓶頸（性能問題，如雙十一搶購）
3.部分業務無法關聯join，只能通過java程序接口去調用，提高了開發復雜度

2、水平拆分

(1) 數據庫水平拆分

如圖，按會員庫拆分，拆分成會員1庫，會員2庫，以userId拆分，userId尾號0-5為1庫
6-9為2庫，還有其他方式，進行取模，偶數放到1庫，奇數放到2庫

(2) 表水平拆分

如圖把users表拆分成users1表和users2表，以userId拆分，進行取模，偶數放到users1表，奇數放到users2表

水平拆分的其他方式

range來分,每個庫一段連續的數據，這個一般是按比如時間范圍來的，但是這種一般較少用，因為很容易產生熱點問題，大量的流量都打在最新的數據上了,優點：擴容的時候，就很容易，因為你只要預備好，給每個月都準備一個庫就可以了，到了一個新的月份的時候，自然而然，就會寫新的庫了 缺點：大部分的 請求，都是訪問最新的數據。實際生產用range，要看場景，你的用戶不是僅僅訪問最新的數據，而是均勻的訪問現在的數據以及歷史的數據
hash分發,優點：可以平均分配每個庫的數據量和請求壓力 缺點：擴容起來比較麻煩，會有一個數據遷移的這么一個過程
水平拆分特點
1.每個庫（表）的結構都一樣
2.每個庫（表）的數據都不一樣
3.每個庫（表）的并集是全量數據

水平拆分優缺點

優點：
1.單庫/單表的數據保持在一定量（減少），有助于性能提高
2.提高了系統的穩定性和負載能力
3.拆分表的結構相同，程序改造較少。

缺點:
1.數據的擴容很有難度維護量大
2.拆分規則很難抽象出來
3.分片事務的一致性問題部分業務無法關聯join，只能通過java程序接口去調用

四、分庫分表帶來的問題
分布式事務
跨庫join查詢
分布式全局唯一id
開發成本 對程序員要求高

五、分庫分表技術如何選型
分庫分表的開源框架
jdbc 直連層：shardingsphere、tddl
proxy 代理層：mycat，mysql-proxy（360）

jdbc直連層

jdbc直連層又叫jdbc應用層,是因為所有分片規則，所有分片邏輯，包括處理分布式事務
所有這些問題它都是在應用層，所有項目都是由war包構成的，所有分片都寫成了jar包，放到了war包里面，java需要虛擬機去運行的，虛擬機運行的時候就會把war包里面的字節文件進行classLoder加載到jvm內存中，所有分片邏輯都是基于內存方進行操作的

proxy代理層

如圖,proxy代理層，所有分片規則，所有分片邏輯，包括處理分布式事務都在mycat寫好了,所有分片邏輯都是基于mycat方進行操作

jdbc直連層和proxy代理層優缺點

jdbc直連層性能高，只支持java語言，支持跨數據庫
proxy代理層開發成本低，支持跨語言，不支持跨數據庫

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存儲。
看到這里，這道真題是不是就比較容易回答了。

MySQL中varchar與char有哪些區別？

1、固定長度 & 可變長度

VARCHAR
??VARCHAR類型用于存儲可變長度字符串，是最常見的字符串數據類型。它比固定長度類型更節省空間，因為它僅使用必要的空間(根據實際字符串的長度改變存儲空間)。
??有一種情況例外，如果MySQL表使用ROW_FORMAT=FIXED創建的話，每一行都會使用定長存儲。

CHAR
??CHAR類型用于存儲固定長度字符串：MySQL總是根據定義的字符串長度分配足夠的空間。當存儲CHAR值時，MySQL會刪除字符串中的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是這樣實現的——也就是說這些版本中CHAR和VARCHAR在邏輯上是一樣的，區別只是在存儲格式上)。
??同時，CHAR值會根據需要采用空格進行剩余空間填充，以方便比較和檢索。但正因為其長度固定，所以會占據多余的空間，也是一種空間換時間的策略；

2、存儲方式

VARCHAR
??VARCHAR需要使用1或2個額外字節記錄字符串的長度：如果列的最大長度小于或等于255字節，則只使用1個字節表示，否則使用2個字節。假設采用latinl字符集，一個VARCHAR(10)的列需要11個字節的存儲空間。VARCHAR(1000)的列則需要1002 個字節，因為需要2個字節存儲長度信息。

VARCHAR節省了存儲空間，所以對性能也有幫助。但是，由于行是變長的，在UPDATE時可能使行變得比原來更長，這就導致需要做額外的工作。如果一個行占用的空間增長，并且在頁內沒有更多的空間可以存儲，在這種情況下，不同的存儲引擎的處理方式是不一樣的。例如，MylSAM會將行拆成不同的片段存儲，InnoDB則需要分裂頁來使行可以放進頁內。

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

3、存儲容量

CHAR
??對于char類型來說，最多只能存放的字符個數為255，和編碼無關，任何編碼最大容量都是255。

VARCHAR
??MySQL行默認最大65535字節，是所有列共享（相加）的，所以VARCHAR的最大值受此限制。

表中只有單列字段情況下，varchar一般最多能存放(65535 - 3)個字節，varchar的最大有效長度通過最大行數據長度和使用的字符集來確定，通常的最大長度是65532個字符（當字符串中的字符都只占1個字節時，能達到65532個字符）；

為什么是65532個字符？算法如下（有余數時向下取整）：

最大長度(字符數) = （行存儲最大字節數 - NULL標識列占用字節數 - 長度標識字節數） / 字符集單字符最大字節數

NULL標識列占用字節數：允許NULL時，占一字節
長度標識字節數：記錄長度的標識，長度小于等于255（28）時，占1字節；小于65535時（216）,占2字節
VARCHAR類型在4.1和5.0版本發生了很大的變化，使得情況更加復雜。從MySQL 4.1開始，每個字符串列可以定義自己的字符集和排序規則。這些東西會很大程度上影響性能。

4.0版本及以下，MySQL中varchar長度是按字節展示，如varchar(20)，指的是20字節；
5.0版本及以上，MySQL中varchar長度是按字符展示。如varchar(20)，指的是20字符。
當然，行總長度還是65535字節，而字符和字節的換算，則與編碼方式有關，不同的字符所占的字節是不同的。編碼劃分如下：

GBK編碼：
一個英文字符占一個字節，中文2字節，單字符最大可占用2個字節。

UTF-8編碼：
一個英文字符占一個字節，中文3字節，單字符最大可占用3個字節。

utf8mb4編碼：
一個英文字符占一個字節，中文3字節，單字符最大占4個字節（如emoji表情4字節）。

假設當前還有6字節可以存放字符，按單字符占用最大字節數來算，可以存放3個GBK、或2個utf8、或1個utf8mb4。

Mysql的索引和主鍵的區別

1、主鍵一定是唯一性的索引，唯一性的索引不一定就是主鍵。

? 主鍵就是能夠唯一標識表中某一行的屬性或者是屬性組，一個表只能有一個主鍵，但可以有多個候選索引。因為主鍵可以唯一標識一行記錄，所以可以確保執行數據更新、刪除的時候不會出現錯誤的。主鍵還經常和外鍵構成參照完整性約束，防止出現數據不一致。數據庫管理系統對于主鍵自動生成唯一索引，所以主鍵也是一個特殊的索引。

? 2、一個表中可以有多個唯一索引，但是主鍵只能有一個。

? 3、主鍵列不允許為空值，而唯一性索引列允許空值。

? 4、主鍵也可以由多個字段組成，組成復合主鍵，同時主鍵也是唯一索引。

? 5、唯一索引表示索引值唯一，可以由一個或者幾個字段組成，一個表可以由多個唯一索引。

數據庫基礎知識

為什么要使用數據庫
數據保存在內存

優點： 存取速度快

缺點： 數據不能永久保存

數據保存在文件

優點： 數據永久保存

缺點：1）速度比內存操作慢，頻繁的IO操作。2）查詢數據不方便

數據保存在數據庫

1）數據永久保存

2）使用SQL語句，查詢方便效率高。

3）管理數據方便

什么是SQL？
結構化查詢語言(Structured Query Language)簡稱SQL，是一種數據庫查詢語言。

作用：用于存取數據、查詢、更新和管理關系數據庫系統。

什么是MySQL?
MySQL是一個關系型數據庫管理系統，由瑞典MySQL AB 公司開發，屬于 Oracle 旗下產品。MySQL 是最流行的關系型數據庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系數據庫管理系統) 應用軟件之一。在Java企業級開發中非常常用，因為 MySQL 是開源免費的，并且方便擴展。

數據庫的事務的基本特性

事務是并發控制的基本單位，保證事務ACID的特性是事務處理的重要任務，而并發操作有可能會破壞其ACID特性。

所以事務是針對并發而言的，即 對 數據 在并發操作時保駕護航。

**原子性：Atomicity **

**原子性：**在我理解看來是，事務中各項操作，要么全部成功要么全部失敗。很有江湖義氣一說，同生共死。

一致性：Consistency

**一致性：**我理解的是更側重結果，事務結束后系統狀態是一致的。

隔離性：Isolation

隔離性：并發執行的事務彼此無法看到對方的中間狀態。

持久性 ：Durability

持久性：當事務完成后，它對于數據的改變是永久性的，即使出現致命的系統故障也將一直保持。

在實際生產應用中 針對 事務的隔離性 又劃分出了幾種隔離級別

并發事務處理帶來的問題

• 更新丟失

當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丟失更新問題–最后的更新覆蓋了由其他事務所做的更新

• 臟讀：在一個事務處理過程中讀取了另一個未提交事務中的數據。

解讀：兩個事務 A 和 B，首先 A 事務對 數據 a 執行加 500 的操作 a = 1500，此時 B 事務讀取數據 a 的值 1500，后 A 事務 又對數據 a 執行減500 的操作 a = 1000 ，A 事務 commit 。

不可重復讀：事務 A 多次讀取同一數據，事務 B 在事務A多次讀取的過程中，對數據作了更新并提交，導致事務A多次讀取同一數據時，結果 不一致。

解讀：兩個事務 A 和 B，首先 A 事務對 數據 a 進行查詢 a = 1000，此時 B 事務對數據 a + 500 操作，并提交事。后 A 事務 又對 數據 a 進行查詢 a = 1500 。

幻讀：事務 A 將數據庫中所有數據類型從默認的 true 改成 false，但是事務 B 就在這個時候插入了一條新記錄，當事務 A改結束后發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。

小結：不可重復讀的和幻讀很容易混淆，不可重復讀側重于修改，幻讀側重于新增或刪除。解決不可重復讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表。

事務的四大特性(ACID)

4.1、原子性（Atomicity）

原子性是指事務是一個不可分割的工作單位，事務中的操作要么全部成功，要么全部失敗。比如在同一個事務中的SQL語句，要么全部執行成功，要么全部執行失敗

4.2、一致性（Consistency）

官網上事務一致性的概念是：事務必須使數據庫從一個一致性狀態變換到另外一個一致性狀態。以轉賬為例子，A向B轉賬，假設轉賬之前這兩個用戶的錢加起來總共是2000，那么A向B轉賬之后，不管這兩個賬戶怎么轉，A用戶的錢和B用戶的錢加起來的總額還是2000，這個就是事務的一致性。

4.3、隔離性（Isolation）

事務的隔離性是多個用戶并發訪問數據庫時，數據庫為每一個用戶開啟的事務，不能被其他事務的操作數據所干擾，多個并發事務之間要相互隔離。

4.4、持久性（Durability）

持久性是指一個事務一旦被提交，它對數據庫中數據的改變就是永久性的，接下來即使數據庫發生故障也不應該對其有任何影響

事務的四大特性中最麻煩的是隔離性，下面重點介紹一下事務的隔離級別

事務的隔離級別

多個線程開啟各自事務操作數據庫中數據時，數據庫系統要負責隔離操作，以保證各個線程在獲取數據時的準確性。

5.1、事務不考慮隔離性可能會引發的問題

如果事務不考慮隔離性，可能會引發如下問題：

1、臟讀

臟讀指一個事務讀取了另外一個事務未提交的數據。

這是非常危險的，假設Ａ向Ｂ轉帳100元，對應sql語句如下所示
　　　　　1.update account set money=money+100 where name=‘B’;
　　　　　2.update account set money=money-100 where name=‘A’;
　　 當第1條sql執行完，第2條還沒執行(A未提交時)，如果此時Ｂ查詢自己的帳戶，就會發現自己多了100元錢。如果A等B走后再回滾，B就會損失100元。

2、不可重復讀

不可重復讀指在一個事務內讀取表中的某一行數據，多次讀取結果不同。
　　例如銀行想查詢A帳戶余額，第一次查詢A帳戶為200元，此時A向帳戶內存了100元并提交了，銀行接著又進行了一次查詢，此時A帳戶為300元了。銀行兩次查詢不一致，可能就會很困惑，不知道哪次查詢是準的。
　　不可重復讀和臟讀的區別是，臟讀是讀取前一事務未提交的臟數據，不可重復讀是重新讀取了前一事務已提交的數據。
　　很多人認為這種情況就對了，無須困惑，當然是后面的為準。我們可以考慮這樣一種情況，比如銀行程序需要將查詢結果分別輸出到電腦屏幕和寫到文件中，結果在一個事務中針對輸出的目的地，進行的兩次查詢不一致，導致文件和屏幕中的結果不一致，銀行工作人員就不知道以哪個為準了。

3、虛讀(幻讀)

虛讀(幻讀)是指在一個事務內讀取到了別的事務插入的數據，導致前后讀取不一致。
　　如丙存款100元未提交，這時銀行做報表統計account表中所有用戶的總額為500元，然后丙提交了，這時銀行再統計發現帳戶為600元了，造成虛讀同樣會使銀行不知所措，到底以哪個為準。

5.2、事務隔離性的設置語句

MySQL數據庫共定義了四種隔離級別：

1. Serializable(串行化)：可避免 **臟讀、不可重復讀、虛讀（幻讀）**情況的發生。
2. Repeatable read(可重復讀) - 默認的隔離級別：可避免臟讀、不可重復讀情況的發生。
3. Read committed(讀已提交)：可避免 臟讀情況發生。
4. Read uncommitted(讀未提交)：最低級別，以上情況均無法保證。

mysql數據庫查詢當前事務隔離級別：select @@tx_isolation

*例如：*

mysql數據庫默認的事務隔離級別是：Repeatable read(可重復讀)

mysql數據庫設置事務隔離級別：set transaction isolation level 隔離級別名

在java代碼中使用：

        Connection connect = JdbcUtils.getConnection();//獲取當前數據庫的隔離級別System.out.println(connect.getTransactionIsolation());//設置數據庫的隔離級別connect.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//取消事務的自動提交connect.setAutoCommit(false);

事務隔離級別如何在java代碼中使用

鏈接：【JDBC】ACID、四種隔離級別與Java代碼實現【附源碼】_java代碼如何查看oracle當前事務隔離級別-CSDN博客

用戶AA為用戶BB轉賬，如果未考慮事務，可能會導致數據的不一致狀態。
（1）未考慮事務之前的轉賬操作：
（此時若沒有異常，則轉賬會成功，若有異常出現，轉賬操作就會在還沒成功之前被迫結束，導致不一致）

//未考慮事務的轉賬操作，用戶AA給用戶BB轉賬100
@Test
public void test01(){String sql = "update user_table set balance = balance - 100 where user = ? ";updateTable(sql,"AA");
//        System.out.println(10/0);//模擬轉賬過程中出現的異常String sql1 = "update user_table set balance = balance + 100 where user = ? ";updateTable(sql1,"BB");
}
public void updateTable(String sql,Object...args) {Connection connect = null;PreparedStatement ps = null;try {//1.獲取數據庫的連接connect = JdbcUtils.getConnection();//2.預編譯sql語句，返回prepareStatement的實例ps = connect.prepareStatement(sql);//3.填充占位符for(int i=0;i<args.length;i++){ps.setObject(i+1,args[i]);}//4.執行sqlps.execute();} catch (Exception e) {e.printStackTrace();} finally {//5.關閉資源JdbcUtils.closeConnection(ps,connect);}
}

（2）考慮事務之后的轉賬操作：
（我們取消數據庫的默認提交操作，當轉賬操作成功結束時，對結果手動進行提交；若轉賬未結束時出現異常，我們可以對轉賬事務進行回滾操作，不管成功與否，數據總是一致的）

public void updateTable(Connection connect, String sql,Object...args) {PreparedStatement ps = null;try {//1.預編譯sql語句，返回prepareStatement的實例ps = connect.prepareStatement(sql);//2.填充占位符for(int i=0;i<args.length;i++){ps.setObject(i+1,args[i]);}//3.執行sqlps.execute();} catch (Exception e) {e.printStackTrace();} finally {//4.關閉資源JdbcUtils.closeConnection(ps,null);}
}
//考慮事務之后的轉賬操作，用戶AA給用戶BB轉賬100
@Test
public void test02(){Connection connect = null;try {connect = JdbcUtils.getConnection();String sql = "update user_table set balance = balance - 100 where user = ? ";//1.取消事務的自動提交connect.setAutoCommit(false);updateTable(connect,sql,"AA");System.out.println(10/0);//模擬事務處理過程中出現的異常String sql1 = "update user_table set balance = balance + 100 where user = ? ";updateTable(connect,sql1,"BB");//2.事務正常結束后的提交connect.commit();} catch (Exception e) {e.printStackTrace();try {//3.事務操作過程中出現異常時的回滾操作connect.rollback();} catch (SQLException throwables) {throwables.printStackTrace();}} finally {if (connect!=null){try {connect.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}
}

若此時Connection沒有被關閉，還可能被重復使用，則需要恢復其自動提交狀態，主要針對數據庫連接池時的操作。
setAutoCommit(true)。尤其是在使用數據庫連接池技術時，執行close()方法前，建議恢復自動提交狀態。

Java代碼演示及隔離級別的設置

數據庫中事務的隔離級別設置結束后，若重啟數據庫的服務，則所有的數據庫設置都會變成默認。

/*** @author wds* @date 2021-12-14 9:38*/
public class TransactionTest01 {//模擬事務A對數據庫中數據的查詢操作@Testpublic void testTransactionSelect() throws Exception{Connection connect = JdbcUtils.getConnection();//獲取當前數據庫的隔離級別System.out.println(connect.getTransactionIsolation());//設置數據庫的隔離級別connect.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//取消事務的自動提交connect.setAutoCommit(false);String sql = "select user,password,balance from  user_table where user = ?";PreparedStatement ps = connect.prepareStatement(sql);User user = queryForTable(connect, User.class, sql, "CC");System.out.println(user);System.out.println(connect.getTransactionIsolation());}//模擬事務B對數據庫中數據的修改操作@Testpublic void testTransactionUpdate() throws Exception {Connection connect = JdbcUtils.getConnection();//取消事務的自動提交connect.setAutoCommit(false);String sql = "update user_table set balance = ? where user = ?";updateTable(connect,sql,50000,"CC");//線程休眠15秒Thread.sleep(15000);System.out.println("修改結束...");}//考慮事務之后的通用的增刪改操作public void updateTable(Connection connect, String sql,Object...args) {PreparedStatement ps = null;try {//1.預編譯sql語句，返回prepareStatement的實例ps = connect.prepareStatement(sql);//2.填充占位符for(int i=0;i<args.length;i++){ps.setObject(i+1,args[i]);}//3.執行sqlps.execute();} catch (Exception e) {e.printStackTrace();} finally {//4.關閉資源JdbcUtils.closeConnection(ps,null);}}//考慮事務之后不同數據表的通用的單行查詢操作public <T> T queryForTable(Connection connect, Class<T> clazz, String sql,Object...args) {PreparedStatement ps = null;ResultSet resultSet = null;try {//1.預編譯Sqlps = connect.prepareStatement(sql);for(int i=0;i<args.length;i++){ps.setObject(i+1,args[i]);}//2.執行sql操作resultSet = ps.executeQuery();//查詢結果集的元數據ResultSetMetaData metaData = resultSet.getMetaData();//查詢結果集的列數int columnCount = metaData.getColumnCount();if(resultSet.next()){//newInstance()只能調用無參構造方法，創建當前類的對象T t = clazz.newInstance();//返回結果集中的每一個列for(int i=0;i<columnCount;i++){//獲取每個列的列值，通過resultSetObject columnValue = resultSet.getObject(i + 1);//通過ResultSetMetaData//獲取每個列的列名String columnName = metaData.getColumnName(i + 1);//獲取每個列的別名String columnLabel = metaData.getColumnLabel(i + 1);//通過反射，將對象指定名getColumnName的屬性設置為指定的屬性值：columnValueField field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t,columnValue);}return t;}} catch (Exception e) {e.printStackTrace();} finally {JdbcUtils.closeConnection(ps,null,resultSet);}return null;}
}

5.3、使用MySQL數據庫演示不同隔離級別下的并發問題

同時打開兩個窗口模擬2個用戶并發訪問數據庫

1、當把事務的隔離級別設置為read uncommitted時，會引發臟讀、不可重復讀和虛讀

A窗口
　　　　set transaction isolation level read uncommitted; --設置A用戶的數據庫隔離級別為Read uncommitted(讀未提交)
　　　　start transaction; --開啟事務
　　　　select * from account; --查詢A賬戶中現有的錢，轉到B窗口進行操作
　　　　select * from account --發現a多了100元，這時候A讀到了B未提交的數據（臟讀）

B窗口
　　　　start transaction; --開啟事務
　　　　update account set money=money+100 where name=‘A’;–不要提交，轉到A窗口查詢

2、當把事務的隔離級別設置為read committed時，會引發不可重復讀和虛讀，但避免了臟讀

A窗口
　　　　set transaction isolation level read committed;
　　　　start transaction;
　　　　select * from account;–發現a帳戶是1000元，轉到b窗口
　　　　select * from account;–發現a帳戶多了100,這時候，a讀到了別的事務提交的數據，兩次讀取a帳戶讀到的是不同的結果（不可重復讀）
　　B窗口
　　　　start transaction;
　　　　update account set money=money+100 where name=‘aaa’;
　　　　commit;–轉到a窗口

3、當把事務的隔離級別設置為repeatable read(mysql默認級別)時，會引發虛讀，但避免了臟讀、不可重復讀

A窗口
　　　　set transaction isolation level repeatable read;
　　　　start transaction;
　　　　select * from account;–發現表有4個記錄，轉到b窗口
　　　　select * from account;–可能發現表有5條記錄，這時候發生了a讀取到另外一個事務插入的數據（虛讀）
　　B窗口
　　　　start transaction;
　　　　insert into account(name,money) values(‘ggg’,1000);
　　　　commit;–轉到a窗口

4、當把事務的隔離級別設置為Serializable時，會避免所有問題

A窗口
　　　　set transaction isolation level Serializable;
　　　　start transaction;
　　　　select * from account; --轉到b窗口

B窗口
　　　　start transaction;
　　　　insert into account(name,money) values(‘ggg’,1000); --發現不能插入，只能等待a結束事務才能插入

數據庫三大范式是什么

第一范式：每個列都不可以再拆分。

第二范式：在第一范式的基礎上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。

第三范式：在第二范式的基礎上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵。

在設計數據庫結構的時候，要盡量遵守三范式，如果不遵守，必須有足夠的理由。比如性能。事實上我們經常會為了性能而妥協數據庫的設計。

mysql有關權限的表都有哪幾個

MySQL服務器通過權限表來控制用戶對數據庫的訪問，權限表存放在mysql數據庫里，由mysql_install_db腳本初始化。這些權限表分別user，db，table_priv，columns_priv和host。下面分別介紹一下這些表的結構和內容：

user權限表：記錄允許連接到服務器的用戶帳號信息，里面的權限是全局級的。
db權限表：記錄各個帳號在各個數據庫上的操作權限。
table_priv權限表：記錄數據表級的操作權限。
columns_priv權限表：記錄數據列級的操作權限。
host權限表：配合db權限表對給定主機上數據庫級操作權限作更細致的控制。這個權限表不受GRANT和REVOKE語句的影響。

MySQL的binlog有有幾種錄入格式？分別有什么區別？

有三種格式，statement，row和mixed。

statement模式下，每一條會修改數據的sql都會記錄在binlog中。不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能。由于sql的執行是有上下文的，因此在保存的時候需要保存相關的信息，同時還有一些使用了函數之類的語句無法被記錄復制。

row 級別下，不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。記錄單元為每一行的改動，基本是可以全部記下來但是由于很多操作，會導致大量行的改動(比如alter table)，因此這種模式的文件保存的信息太多，日志量太大。

mixed，一種折中的方案，普通操作使用statement記錄，當無法使用statement的時候使用row。
此外，新版的MySQL中對row級別也做了一些優化，當表結構發生變化的時候，會記錄語句而不是逐行記錄。

數據類型

MySQL存儲引擎MyISAM與InnoDB區別

存儲引擎Storage engine：MySQL中的數據、索引以及其他對象是如何存儲的，是一套文件系統的實現。

常用的存儲引擎有以下：

Innodb引擎：Innodb引擎提供了對數據庫ACID事務的支持。并且還提供了行級鎖和外鍵的約束。它的設計的目標就是處理大數據容量的數據庫系統。
MyIASM引擎(原本Mysql的默認引擎)：不提供事務的支持，也不支持行級鎖和外鍵。
MEMORY引擎：所有的數據都在內存中，數據的處理速度快，但是安全性不高。

MyISAM索引與InnoDB索引的區別？
InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主鍵索引的葉子節點存儲著行數據，因此主鍵索引非常高效。
MyISAM索引的葉子節點存儲的是行數據地址，需要再尋址一次才能得到數據。
InnoDB非主鍵索引的葉子節點存儲的是主鍵和其他帶索引的列數據，因此查詢時做到覆蓋索引會非常高效。
InnoDB引擎的4大特性
插入緩沖（insert buffer)

二次寫(double write)

自適應哈希索引(ahi)

預讀(read ahead)

存儲引擎選擇
如果沒有特別的需求，使用默認的Innodb即可。

MyISAM：以讀寫插入為主的應用程序，比如博客系統、新聞門戶網站。

Innodb：更新（刪除）操作頻率也高，或者要保證數據的完整性；并發量高，支持事務和外鍵。比如OA自動化辦公系統。

MYsql如何查詢到一條數據的，簡述其查詢原理，BufferPool緩存機制事務

鏈接：【mysql學習篇】InnoDB存儲引擎事務的實現和BufferPool緩存機制詳解-CSDN博客

Mysql的InnoDB是如何使用索引的，其索引的原理是什么

鏈接：Mysql(InnoDB)索引原理及的使用_innodb 索引空間、索引數據-CSDN博客

索引是存儲引擎實現的，InnoDB的索引結構是B+樹，大部分的引擎也都支持B+樹索引

B+樹索引的優點

• 層級少，2200W的數據只有3層
• 葉子節點存數據+指針(利于排序)
• 支持范圍查詢
• 支持排序(區間指針)
• 索引全部出現在最下層，即便是他們作為了上層分支

InnoDB到底支持Hash索引嗎？
InnoDB在特定條件下會自適應Hash索引，用戶不能手動創建，可以理解為“索引的索引”，可以加快索引查詢速度

相比B+樹的優缺點：

不支持范圍查詢，但指定查詢效率更高(一般只需一次檢索)
哈希沖突問題使用鏈表解決
無法排序，哈希值計算結果沒有順序關系

MySQL-索引，innoDB,B+樹索引

鏈接： (5條消息) MySQL索引常見面試題（2022版）_mysql索引優化_未來很長，別只看眼前的博客-CSDN博客

鏈接2： (6條消息) 數據庫MySQL-索引（含常見面試題）_mysql索引面試題_懶羊羊z的博客-CSDN博客

MySQL 索引失效如何解決？

以下是索引失效的原因：

為什么要建立索引？

? 當在非常大的表中進行查詢，如果數據庫進行全表遍歷的話那么速度是會非常慢的，而我們的索引則可以建立一個b+樹的結構，可以自上而下的去進行查詢（有點像二分查找），可以在一定程度避免走全表查詢，這樣查詢的速度是非常快的；

①一般情況下掃描索引的速度是遠遠大于掃描全表的速度的；

②索引是天然有序的，具備B+樹的快速檢索（類似二分查找）

③索引天然聚合（存儲的數據是去重了的），在一些操作(分組，排序等)中不會再產生中間表；

哪些情況適合建立索引？

? 對于查詢占主要的應用來說，索引顯得尤為重要。很多時候性能問題很簡單的就是因為我們忘了添加索引而造成的，或者說沒有添加更為有效的索引導致。如果不加索引的話，那么查找任何哪怕只是一條特定的數據都會進行一次全表掃描，如果一張表的數據量很大而符合條件的結果又很少，那么不加索引會引起致命的性能下降。但是也不是什么情況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什么優勢，還會影響到更新速度，這被稱為過度索引。

?

? 那么哪些情況下適合建立索引呢？：

1. 頻繁作為where條件語句查詢的字段

? 這是因為在頻繁查詢的字段列創建索引可以避免查詢數據的時候走全表掃描，這樣查詢的速度就會大大增加；

2. 關聯字段需要建立索引

? 關聯的字段一般都是通過主鍵來進行兩張表的關聯，主鍵大部分情況下都是主鍵；如果關聯的兩個主鍵都沒有索引，那么我們一般優先考慮在被驅動表中的字段建立索引，因為在外連接的查詢中被驅動表是需要被多次重復掃描的，那么讓它走索引查詢是會快很多的，可以避免更多次數的全表掃描；

3. 排序字段可以建立索引

? 這是因為B+樹結構的索引是天然有序的！

4.分組字段可以建立索引，因為分組的前提是排序

5.統計字段可以建立索引，例如count(),max()

? 這是因為索引是天然聚合的，就是存放在b+樹的數據是已經去重的數據了，存儲的數據還是比較緊湊的，那么通過B+樹的雙向指針可以更快的找到要統計的數據，而且在加了索引的列的統計的時候MySQL是不會產生中間表來專門去重了，可以減少不必要的性能開銷；（在沒有索引的列的統計，分組 的SQL語句中，MySQL都是會創建臨時表來存儲數據的）

哪些情況不適合建立索引？

1.頻繁更新的字段不適合建立索引 (因為數據比較大的表的索引的創建是非常耗時的，而且如果一個字段被頻繁更新那么我們還需要頻繁的維護這個樹的結構，這個開銷是非常大的)

2.參與列計算的列不適合建索引，因為計算后的列的值最后不一定是有序的，不是有序的 那么就會導致索引失效 。

3.表數據可以確定比較少的不需要建索引

4.數據重復且分布比較均勻的的字段不適合建索引，因為說不定你對這種索引字段的查詢的速度還沒有全表掃描快，例如性別，真假值；

5.where條件中用不到的字段不適合建立索引，因為索引是可以幫助我們在查詢的時候大大的提高查詢效率，但是在增加，刪除操作確實異常消耗性能的，因為需要不斷的維護B+樹的結構（有序你就需要維護），你查詢的時候都不需要使用到這個字段了，那還建立這個字段的索引列干啥？等著吃你系統的性能嘛？

為什么索引使用的是 B+ 樹？——重點

①因為B+樹是把數據都存放在葉子節點中的（在innodb存儲引擎中一個b+樹的節點是 一頁(16k)），那么在固定大小的容量中 B+樹的非葉子節點中就可以存放更多的索引列數據，也就意味著B+樹的非葉子節點存儲的數據的范圍就會更大，那么樹的層次就會更少，IO次數也就會更少；

②B+樹的葉子節點維護了一個雙向鏈表，它更有利于范圍查詢。

③B+樹中的葉子節點和非葉子節點的數據都是分開存儲的，分別存放在葉子節點段和非葉子節點段，那么進行全表掃描的時候，就可以不用再掃描非葉子節點的數據了，并且這是一個順序讀取數據的過程（順序讀比隨機讀的速度要快很多很多），掃描的速度也會大大提高；

鏈接：聚簇索引和非聚簇索引到底有什么區別？_聚簇索引和非聚簇索引區別_Linux小百科的博客-CSDN博客

索引分為哪幾類？

從大類來分：分為聚簇索引和非聚簇索引；

從具體的種類來分有：

主鍵索引: 也簡稱主鍵。它可以提高查詢效率，并提供唯一性約束。一張表中只能有一個主鍵。

普通索引：就是普普通通的索引。

唯一索引：索引的值不能重復。

復合索引：在工作中用得比較頻繁的一個索引；

當有多個查詢條件時，我們推薦使用復合索引。比如：我們經常按照 A列 B列 C列進行查詢時，通常的做法是建立一個由三個列共同組成的復合索引而不是對每一個列建立普通索引。

創建方式： 復合索引中的索引的順序是非常重要的；

alert table test add idx_a1_a2_a3 table (a1,a2,a3) 

使用復合索引可以極大的減少回表的帶來的性能開銷；（體現在 復合索引可以進行更多的索引覆蓋（因為你索引的個數明顯更加多了呀），即便是回表也是攜帶更少的主鍵進行回表查詢（與MySQL5.7后的索引下推有關））

復合索引是基于第一個索引的，比如你建立了一個（a,b,c）的復合索引，那么你不能跳過a索引直接去查詢b索引，因為在建立（a,b,c）這個復合索引的時候，是會創建（a），（a,b），（a,b,c）這三個索引的，你會發現它們都是基于a索引的； (并不會單獨的創建(a,c)這個索引)

hash索引：hash天然快（最快o(1),最慢o(n),樹化(lon(n))），但是天然無序；

空間索引；

全文索引

聚簇索引和非聚簇索引的區別

什么是聚簇索引？（重點）

聚簇索引就是將數據(一行一行的數據)跟索引結構放到一塊，InnoDB存儲引擎使用的就是聚簇索引；

注意點：

1、InnoDB使用的是聚簇索引（聚簇索引默認使用主鍵作為其索引），將主鍵組織到一棵B+樹中，而行數據就儲存在葉子節點上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節點，之后獲得行數據。

2、若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可獲取整行數據。（重點在于通過其他鍵需要建立輔助索引）

聚簇索引具有唯一性，由于聚簇索引是將數據(一行一行的數據)跟索引結構放到一塊，因此一個表僅有一個聚簇索引，其他輔助索引可能是只有幾個列的數據和索引放在一起！

表中行的物理順序和索引中行的物理順序是相同的，在創建任何非聚簇索引之前創建聚簇索引，這是因為聚簇索引改變了表中行的物理順序，數據行 按照一定的順序排列，并且自動維護（有序就一定需要維護）這個順序；

聚簇索引中的索引默認是主鍵，如果表中沒有定義主鍵，InnoDB 會選擇一個唯一且非空的索引代替。如果沒有這樣的索引，InnoDB 會隱式定義一個6個字節大小的row_id來作為主鍵，這個主鍵會作為聚簇索引中的索引。如果已經設置了主鍵為聚簇索引又希望再單獨設置聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復設置主鍵即可。

非聚簇索引

非聚簇索引在 InnoDB 引擎中，也叫二級索引

在 MySQL 的 InnoDB 引擎中，每個索引都會對應一顆 B+ 樹，而聚簇索引和非聚簇索引最大的區別在于葉子節點存儲的數據不同，聚簇索引葉子節點存儲的是行數據，因此通過聚簇索引可以直接找到真正的行數據；而非聚簇索引葉子節點存儲的是主鍵信息，所以使用非聚簇索引還需要回表查詢，因此我們可以得出

聚簇索引和非聚簇索引的區別主要有以下幾個：

• 聚簇索引葉子節點存儲的是行數據；而非聚簇索引葉子節點存儲的是聚簇索引（通常是主鍵 ID）。

• 聚簇索引查詢效率更高，而非聚簇索引需要進行回表查詢，因此性能不如聚簇索引。

• 聚簇索引一般為主鍵索引，而主鍵一個表中只能有一個，因此聚簇索引一個表中也只能有一個，而非聚簇索引則沒有數量上的限制。

什么叫回表？（重點）

如果一個查詢是先走輔助索引（聚簇索引外的索引都叫輔助索引）的，那么通過這個輔助索引（innodb中的輔助索引的data存儲的是主鍵）沒有獲取到我們想要的全部數據，那么MySQL就會拿著輔助索引查詢出來的主鍵去聚簇索引中進行查詢，這個過程就是叫回表；

MySQL索引失效的幾種情況（重點）

①like查詢以%開頭，因為會導致查詢出來的結果無序；

②類型轉換，列計算也會可能會讓索引失效，因為結果可能是無序的，也可能是有序的；

③在一些查詢的語句中，MySQL認為走全表掃描比索引更加快也會導致索引失效；

④如果條件中有or并且or連接的字段中有列沒有索引，那么即使其中有條件帶索引也不會使用索引 (這是因為MySQL判斷即便你開始走了索引查詢，但是它發現查詢中有Or ，也就是說or 后面的還是需要走全表掃描（因為or會導致后面的數據是無序的），所以MySQL還不如一開始就直接走全表掃描，這也是為什么盡量少用or的原因)要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引，當檢索條件有or但是所有的條件都有索引時，索引不失效，可以走【兩個索引】，這叫索引合并（取二者的并集）;

⑤復合索引不滿足最左原則就不能使用全部索引

MySQL索引優化手段有哪些？

① 盡可能的使用復合索引而不是索引的組合；

②創建索引盡量讓輔助索引進行索引覆蓋 而不是回表；

③在可以使用主鍵id的表中，盡量使用自增主鍵id，這樣可以避免頁分裂；

④查詢的時候盡量不要使用select * ，這樣可以避免大量的回表；

⑤盡量少使用子查詢，能使用外連接就使用外連接，這樣可以避免產生笛卡爾集；

⑥能使用短索引就是用短索引，這樣可以在非葉子節點存儲更多的索引列降低樹的層高，并且減少空間的開銷；

什么叫回表？（重點）

如果一個查詢是先走輔助索引（聚簇索引外的索引都叫輔助索引）的，那么通過這個輔助索引（innodb中的輔助索引的data存儲的是主鍵）沒有獲取到我們想要的全部數據，那么MySQL就會拿著輔助索引查詢出來的主鍵去聚簇索引中進行查詢，這個過程就是叫回表；

什么叫索引覆蓋？（重點）

如果一個查詢是先走輔助索引的，那么通過這個輔助索引就直接獲取到我們想要的全部數據了，不需要進行回表，這個過程就叫做索引覆蓋；

索引-索引-索引

什么是索引？

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。

索引是一種數據結構。數據庫索引，是數據庫管理系統中一個排序的數據結構，以協助快速查詢、更新數據庫表中數據。索引的實現通常使用B樹及其變種B+樹。

更通俗的說，索引就相當于目錄。為了方便查找書中的內容，通過對內容建立索引形成目錄。索引是一個文件，它是要占據物理空間的。

索引有哪些優缺點？

索引的優點

可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。
通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
索引的缺點

時間方面：創建索引和維護索引要耗費時間，具體地，當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；
空間方面：索引需要占物理空間。

索引有哪幾種類型？

從大類來分：分為聚簇索引和非聚簇索引；

從具體的種類來分有：

主鍵索引: 數據列不允許重復，不允許為NULL，一個表只能有一個主鍵。

唯一索引: 數據列不允許重復，允許為NULL值，一個表允許多個列創建唯一索引。

可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創建唯一索引

可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創建唯一組合索引

普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。

可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引

可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創建組合索引

全文索引： 是目前搜索引擎使用的一種關鍵技術。

可以通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引
索引的數據結構（b樹，hash）
索引的數據結構和具體存儲引擎的實現有關，在MySQL中使用較多的索引有Hash索引，B+樹索引等，而我們經常使用的InnoDB存儲引擎的默認索引實現為：B+樹索引。對于哈希索引來說，底層的數據結構就是哈希表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引。

1）B樹索引

mysql通過存儲引擎取數據，基本上90%的人用的就是InnoDB了，按照實現方式分，InnoDB的索引類型目前只有兩種：BTREE（B樹）索引和HASH索引。B樹索引是Mysql數據庫中使用最頻繁的索引類型，基本所有存儲引擎都支持BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實際是用B+樹實現的，因為在查看表索引時，mysql一律打印BTREE，所以簡稱為B樹索引）

創建索引的三種方式，刪除索引
第一種方式：在執行CREATE TABLE時創建索引

CREATE TABLE user_index2 (id INT auto_increment PRIMARY KEY,first_name VARCHAR (16),last_name VARCHAR (16),id_card VARCHAR (18),information text,KEY name (first_name, last_name),FULLTEXT KEY (information),UNIQUE KEY (id_card)
);

第二種方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用來創建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。

索引名index_name可自己命名，缺省時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時創建多個索引。

第三種方式：使用CREATE INDEX命令創建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可對表增加普通索引或UNIQUE索引。（但是，不能創建PRIMARY KEY索引）

刪除索引

根據索引名刪除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

刪除主鍵索引：alter table 表名 drop primary key（因為主鍵只有一個）。這里值得注意的是，如果主鍵自增長，那么不能直接執行此操作（自增長依賴于主鍵索引）：

需要取消自增長再行刪除：

alter table user_index

– 重新定義字段

MODIFY id int,
drop PRIMARY KEY

但通常不會刪除主鍵，因為設計主鍵一定與業務邏輯無關。

創建索引時需要注意什么？
非空字段：應該指定列為NOT NULL，除非你想存儲NULL。在mysql中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值；
取值離散大的字段：（變量各個取值之間的差異程度）的列放到聯合索引的前面，可以通過count()函數查看字段的差異值，返回值越大說明字段的唯一值越多字段的離散程度高；
索引字段越小越好：數據庫的數據存儲以頁為單位一頁存儲的數據越多一次IO操作獲取的數據越大效率越高。
使用索引查詢一定能提高查詢的性能嗎？為什么
通常，通過索引查詢數據比全表掃描要快。但是我們也必須注意到它的代價。

索引需要空間來存儲，也需要定期維護， 每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。 這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁盤I/O。 因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢性能，索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:
基于一個范圍的檢索，一般查詢返回結果集小于表中記錄數的30%
基于非唯一性索引的檢索
百萬級別或以上的數據如何刪除
關于索引：由于索引需要額外的維護成本，因為索引文件是單獨存在的文件,所以當我們對數據的增加,修改,刪除,都會產生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執行效率。所以，在我們刪除數據庫百萬級別數據的時候，查詢MySQL官方手冊得知刪除數據的速度和創建的索引數量是成正比的。

所以我們想要刪除百萬數據的時候可以先刪除索引（此時大概耗時三分多鐘）
然后刪除其中無用數據（此過程需要不到兩分鐘）
刪除完成后重新創建索引(此時數據較少了)創建索引也非常快，約十分鐘左右。
與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。

MySQL有哪些鎖

鏈接：【精選】MySQL 有哪些鎖？_mysql的鎖有幾種-CSDN博客

全面鏈接：mysql 常見鎖的類型（一）_mysql鎖的分類_IT社團的博客-CSDN博客