Java面試題(二)--MySQL

来源:https://www.cnblogs.com/xy1857/archive/2022/07/04/16444398.html
-Advertisement-
Play Games

1 存儲引擎 1、簡單描述一個Mysql的內部結構？ MySQL的基本架構示意圖： 大體來說，MySQL可以分為server層和存儲引擎層兩部分。 ① server層包括連接器、查詢緩存、分析器、優化器、執行器等，涵蓋MySQL的大多數核心服務功能 ② 存儲引擎層：存儲引擎層負責數據的存儲和提取。其 ...


1 存儲引擎

1、簡單描述一個Mysql的內部結構？

MySQL的基本架構示意圖：

大體來說，MySQL可以分為server層和存儲引擎層兩部分。

① server層包括連接器、查詢緩存、分析器、優化器、執行器等，涵蓋MySQL的大多數核心服務功能

② 存儲引擎層：存儲引擎層負責數據的存儲和提取。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎

連接器：連接器負責跟客戶端建立連接、獲取許可權、維持和管理連接。

查詢緩存：連接建立完成後，你就可以執行select語句了，此時會先進行查詢緩存(緩存是key-value格式；key是sql語句，value是sql語句的查詢結果)。

分析器：

​ 1、詞法分析： MySQL需要識別出裡面的字元串分別是什麼，代表什麼。

​ 2、語法分析：根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個SQL語句是否滿足MySQL語法。

優化器：優化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。

執行器：調用存儲引擎介面，執行sql語句，得到結果

2、資料庫存儲引擎有哪些？(高頻)

MySQL提供了插件式的存儲引擎架構。所以MySQL存在多種存儲引擎，可以根據需要使用相應引擎，或者編寫存儲引擎。存儲引擎是基於表的，而不是基於庫的。所以存儲引擎也可被稱為表類型。MySQL5.0支持的存儲引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中InnoDB和BDB提供事務安全表，其他存儲引擎是非事務安全表。

3、InnoDB與MyISAM的區別？(高頻)

1、InnoDB支持事務，MyISAM不支持，對於InnoDB每一條SQL語言都預設封裝成事務，自動提交，這樣會影響速度，所以最好把多條SQL語言放在begin和commit之間，組成一個事務；

2、InnoDB支持外鍵，而MyISAM不支持。

3、InnoDB是支持表鎖和行級鎖，MyISAM只支持表鎖

4、如何選擇存儲引擎？

如果沒有特別的需求，使用預設的 Innodb 即可。

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

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

5、存儲引擎常用命令？

show engines;  查看MySQL提供的所有存儲引擎


創建新表時如果不指定存儲引擎，那麼系統就會使用預設的存儲引擎，MySQL5.5之前的預設存儲引擎是MyISAM，5.5之後就改為了InnoDB。

show variables like '%storage_engine%';  查看mysql預設的存儲引擎


show table status like "table_name"\G 	查看表的存儲引擎

2 索引

6、什麼是索引？(高頻)

MySQL官方對索引的定義為：索引（index）是幫助MySQL高效獲取數據的數據結構（有序）。在數據之外，資料庫系統還維護者滿足特定查找演算法的數據結構，這些數據結構以某種方式引用（指向）數據， 這樣就可以在這些數據結構上實現高級查找演算法，這種數據結構就是索引。如下麵的示意圖所示 :

左邊是數據表，一共有兩列七條記錄，最左邊的是數據記錄的物理地址（註意邏輯上相鄰的記錄在磁碟上也並不是一定物理相鄰的）。為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找快速獲取到相應數據。一般來說索引本身也很大，不可能全部存儲在記憶體中，因此索引往往以索引文件的形式存儲在磁碟上。索引是資料庫中用來提高性能的最常用的工具。

導入資料中提供的sql腳本文件，已經準備了1000W條數據。

A. 根據ID查詢

select * from tb_sku where id = 1999\G ;


查詢速度很快， 接近0s ， 主要的原因是因為id為主鍵， 有索引；

查看執行計劃：

B. 根據 name進行精確查詢

select * from tb_sku where name = '華為Meta1999'\G ;


查詢速度太慢了，幾乎使用了9s才完成數據的查詢。

查看執行計劃：

7 什麼是聚簇索引和非聚簇索引？(高頻)

聚簇索引：將數據和索引放在一起，並且是按照一定順序組織的，找到索引也就找到了數據。一般情況下主鍵就是預設的聚簇索引。

優缺點：使用聚簇索引進行行數據查詢效率較高，更新數據的效率較低，同時會占用的存儲空間較大。

非聚簇索引：葉子結點不存儲數據，存儲的是行的物理地址，在進行行數據查詢的時候，需要根據物理地址值從資料庫表中再次進行查詢【回表】

image-20220608120219915

優缺點：使用非聚簇索引進行行數據查詢效率較低，更新數據的效率較高，同時占用的存儲空間較小。

8、如何創建索引？(高頻)

為了提升上述查詢效率，可以對name欄位創建索引。創建索引有兩種方式：

1、方式一：在創建表的時候創建索引

-- 語法結構
CREATE TABLE  表名( 屬性名 數據類型[完整性約束條件], 
    屬性名 數據類型[完整性約束條件], 
    ...... 
    屬性名 數據類型  
    [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX | KEY 
    [ 別名]  ( 屬性名1  [(長度)]  [ ASC | DESC] ) 
);

示例：

-- 示例代碼
CREATE TABLE `index1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `sex` tinyint(1) DEFAULT NULL, 
  KEY `index1_id` (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、方式二：使用create index語句進行索引創建

語法：

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)   // 如果指定的列的名稱是多個，那麼這個索引我們將其稱之為複合索引

示例：

create index idx_name on tb_sku(name) ;


再次進行查詢：

通過explain ， 查看執行計劃，執行SQL時使用了剛纔創建的索引

9、常見的索引約束有哪些？(高頻)

1、UNIQUE：唯一索引

​ 表示唯一的，不允許重覆的索引，如果該欄位信息保證不會重覆例如身份證號用作索引時，可設置為UNIQUE。

2、FULLTEXT: 全文索引

​ 表示全文搜索，在檢索長文本的時候，效果最好，短文本建議使用普通索引,但是在檢索的時候數據量比較大的時候，現將數據放入一個沒有全局索引的表中，然後在用Create Index創建的Full Text索引，要比先為一張表建立Full Text然後在寫入數據要快的很多。FULLTEXT 用於搜索很長一篇文章的時候，效果最好。用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以。

3、SPATIAL: 空間索引

​ 空間索引是對空間數據類型的欄位建立的索引，MYSQL中的空間數據類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL關鍵字進行擴展，使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MYISAM的表中創建。如果沒有指定索引約束，此時創建的索引就是普通索引。而一般情況下只需要創建普通索引。

4、普通索引：如果沒有指定索引約束，此時創建的索引就是普通索引。而一般情況下只需要創建普通索引。

10、常見的索引類型有哪些？(高頻)

索引是在MySQL的存儲引擎層中實現的，而不是在伺服器層實現的。所以每種存儲引擎的索引都不一定完全相同，也不是所有的存儲引擎都支持所有的索引類型的。

MySQL目前提供了以下4種索引：

各種存儲引擎對索引的支持：

索引 InnoDB引擎 MyISAM引擎 Memory引擎
BTREE索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-tree 索引 不支持 支持 不支持
Full-text 5.6版本之後支持 支持 不支持

我們平常所說的索引，如果沒有特別指明，都是指B+樹（多路搜索樹，並不一定是二叉的）結構組織的索引。

11、怎麼看到為表格定義的所有索引？

語法：

show index  from  table_name;

示例：查看tb_sku表中的索引信息；

show index from tb_sku ;


註意：主鍵自動創建索引

12、唯一索引比普通索引快嗎, 為什麼？

唯一索引不一定比普通索引快, 還可能慢。

1、查詢時, 在未使用 limit 1 的情況下, 在匹配到一條數據後, 唯一索引即返回, 普通索引會繼續匹配下一條數據, 發現不匹配後返回. 如此看來唯一索引少了一次匹配, 但實際上這個消耗微乎其微。

2、更新時, 這個情況就比較複雜了. 普通索引將記錄放到 change buffer 中語句就執行完畢了。而對唯一索引而言, 它必須要校驗唯一性, 因此, 必須將數據頁讀入記憶體確定沒有衝突, 然後才能繼續操作。

對於寫多讀少的情況 , 普通索引利用 change buffer 有效減少了對磁碟的訪問次數, 因此普通索引性能要高於唯一索引.

13、索引的優缺點？

1、優點

  • 提高數據檢索的效率，降低資料庫的 IO 成本。
  • 通過索引列對數據進行排序，降低數據排序的成本，降低了 CPU 的消耗。

2、缺點

  • 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行 INSERT、UPDATE 和DELETE。因為更新表時，MySQL 不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引信息。

  • 實際上索引也是一張表，該表保存了主鍵與索引欄位，並指向實體表的記錄，所以索引列也是要占用空間的。

14、什麼情況下設置了索引但無法使用？(高頻)

環境準備

建表語句：

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;

初始化數據sql：

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿裡巴巴','阿裡小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程式員','黑馬程式員','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

創建索引：

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

全職匹配查詢：對索引中所有列都指定具體值。該情況下，索引生效，執行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

① 違背了最左首碼法則

如果索引了多列，要遵守最左首碼法則。指的是查詢從索引的最左前列開始，並且不跳過索引中的列。

匹配最左首碼法則，走索引：

違法最左首碼法則 ， 索引失效：
![[MySQL面試題.png]]

如果符合最左法則，但是出現跳躍某一列，只有最左列索引生效：

② 範圍查詢: 範圍查詢右邊的列，不能使用索引 。

根據前面的兩個欄位name ， status 查詢是走索引的， 但是最後一個條件address 沒有用到索引。

>= 不走索引
> 走索引

③ 列運算：不要在索引列上進行運算操作， 索引將失效。

④ 字元串：字元串不加單引號，造成索引失效。

由於，在查詢時沒有對字元串加單引號，MySQL的查詢優化器，會自動的進行類型轉換，造成索引失效。

⑤ 模糊查詢：以%開頭的like模糊查詢，索引失效。如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

解決方案 ：

通過覆蓋索引來解決

15、在建立索引的時候,都有哪些需要考慮的因素呢？

① 建立索引的時候一般要考慮到欄位的使用頻率,經常作為條件進行查詢的欄位比較適合。

② 如果需要建立聯合索引的話,還需要考慮聯合索引中的順序。

③ 此外也要考慮其他方面,比如防止過多的索引對錶造成太大的壓力

16、創建的索引有沒有被使用到？或者說怎麼才可以知道這條語句運行很慢的原因？

MySQL提供了explain命令來查看語句的執行計劃,MySQL在執行某個語句之前,會將該語句過一遍查詢優化器,之後會拿到對語句的分析,也就是執行計劃,其中包含了許多信息. 可以通過其中和索引有關的信息來分析是否命中了索引,例如possilbe_key,key,key_len等欄位,分別說明瞭此語句可能會使用的索引,實際使用的索引以及使用的索引長度.

3 SQL優化

17、關心過業務系統裡面的sql耗時嗎？統計過慢查詢嗎？對慢查詢都怎麼優化過？(高頻)

在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們。

慢查詢的優化首先要搞明白慢的原因是什麼?

① 是查詢條件沒有命中索引?

② 是load了不需要的數據列?

③ 還是數據量太大?

所以優化也是針對這三個方向來的：

1、分析語句的執行計劃,然後獲得其使用索引的情況,之後修改語句或者修改索引,使得語句可以儘可能的命中索引。

2、分析語句，看看是否load了額外的數據，可能是查詢了多餘的行並且拋棄掉了，可能是載入了許多結果中並不需要的列，對語句進行分析以及重寫。

3、如果是表中的數據量是否太大導致查詢慢，可以進行橫向或者縱向的分表.

MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是運行10S以上的語句。預設情況下，Mysql資料庫並不啟動慢查詢日誌，需要我們手動來設置這個參數，當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日誌會或多或少帶來一定的性能影響。慢查詢日誌支持將日誌記錄寫入文件，也支持將日誌記錄寫入資料庫表。

慢查詢的配置：

# 是否開啟慢查詢日誌，1表示開啟，0表示關閉
slow_query_log=1

# 舊版（5.6以下版本）MySQL資料庫慢查詢日誌存儲路徑。可以不設置該參數，系統則會預設給一個預設的文件host_name-slow.log
log_slow_queries=/var/lib/mysql/mysql_slow.log

# 新版（5.6及以上版本）MySQL資料庫慢查詢日誌存儲路徑。可以不設置該參數，系統則會預設給一個預設的文件host_name-slow.log
slow_query_log_file=/var/lib/mysql/mysql_slow.log

# 慢查詢閾值，當查詢時間大於設定的閾值時，記錄日誌。
long_query_time = 1

# 未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。
log_queries_not_using_indexes=0

# 日誌存儲方式。log_output='FILE'表示將日誌存入文件，預設值是'FILE'。log_output='TABLE'表示將日誌存入資料庫，這樣日誌信息就會被寫入到mysql.slow_log表中。MySQL資料庫支持同時兩種日誌存儲方式，配置的時候以逗號隔開即可，如：log_output='FILE,TABLE'。日誌記錄到系統的專用日誌表中，要比記錄到文件耗費更多的系統資源，因此對於需要啟用慢查詢日誌，又需要能夠獲得更高的系統性能，那麼建議優先記錄到文件。
log_output='FILE,TABLE'

添加如上配置重啟服務，產生慢查詢日誌：

慢查詢日誌文件內容：

執行如下sql語句模擬慢查詢：

-- 不會記錄到慢查詢日誌中
select sleep(0.2) ;

-- 會記錄到慢查詢日誌中
select sleep(2) ;

18、如何優化SQL？(高頻)

① SQL語句中IN包含的值不應過多

MySQL對於IN做了相應的優化，即將IN中的常量全部存儲在一個數組裡面，而且這個數組是排好序的。但是如果數值較多，產生的消耗也是比較大的。再例如：

select id from table_name where numin(1,2,3) 對於連續的數值，能用between 就不要用in了。

② SELECT語句務必指明欄位名稱

SELECT *增加很多不必要的消耗（cpu、io、記憶體、網路帶寬）；增加了使用覆蓋索引的可能性；當表結構發生改變時，前斷也需要更新。所以要求直接在select後面接上欄位名。

③ 如果排序欄位沒有用到索引，就儘量少排序

④ 如果限制條件中其他欄位沒有索引，儘量少用or

or兩邊的欄位中，如果有一個不是索引欄位，而其他條件也不是索引欄位，會造成該查詢不走索引的情況。很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果

or查詢：

(1) or兩邊放聯合索引，不觸發索引(如果兩邊是單列索引另算)

(2) or兩邊是單列索引，查詢走索引

(3) or兩邊只要有一個不是索引就不啟用索引查詢

單例索引演示：

複合索引演示：

(4) or兩邊一個是聯合索引的最左索引一個是單例索引才生效，否則失效

示例：

-- 創建單列索引
 create index idx_nickname on tb_seller(nickname) ;

使用索引：
![[MySQL面試題-1.png]]
索引失效：

⑤ 不建議使用%首碼模糊查詢：例如LIKE “%name”或者LIKE “%name%”，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE“name%”。

19、超大分頁怎麼處理？(高頻)

一般分頁查詢時，通過創建覆蓋索引能夠比較好地提高性能。一個常見又非常頭疼的問題就是 limit 1000000 , 10，此時需要MySQL排序前1000010 記

錄，僅僅返回1000000 - 1000010 的記錄，其他記錄丟棄，查詢排序的代價非常大 。

示例：

explain select * from tb_sku limit 1000000 , 10 ;


執行查詢耗時：

優化思路一：在索引上完成排序分頁操作，最後根據主鍵關聯回原表查詢所需要的其他列內容。

示例：

explain select * from tb_sku s , (select id from tb_sku order by id limit 1000000 , 10 ) t where t.id = s.id ;


執行查詢耗時：

優化思路二：該方案適用於主鍵自增的表，可以把limit 查詢轉換成某個位置的查詢 。

示例：

 explain select * from tb_sku where id > 1000000 limit 10 ;


執行查詢耗時：

20、MySQL資料庫作發佈系統的存儲，一天五萬條以上的增量，預計運維三年,怎麼優化？

1、設計良好的資料庫結構， 允許部分數據冗餘， 儘量避免join查詢， 提高效率。

2、選擇合適的表欄位數據類型和存儲引擎， 適當的添加索引。

3、MySQL 庫主從讀寫分離。

4、找規律分表， 減少單表中的數據量 ，提高查詢速度。

5、添加緩存機制， 比如 memcached， redis等。

6、不經常改動的頁面， 生成靜態頁面。

7、書寫高效率的SQL。比如 SELECT * FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE。

4 事務

21、什麼是事務？(高頻)

概述：由多個操作組成的一個邏輯單元，組成這個邏輯單元的多個操作要麼都成功，要麼都失敗。

舉例：轉賬

22、ACID是什麼？可以詳細說一下嗎？(高頻)

A=Atomicity原子性：就是上面說的,要麼全部成功,要麼全部失敗，不可能只執行一部分操作。

C=Consistency一致性：系統(資料庫)總是從一個一致性的狀態轉移到另一個一致性的狀態,不會存在中間狀態。

I=Isolation隔離性: 通常來說:一個事務在完全提交之前,對其他事務是不可見的.註意前面的通常來說加了紅色,意味著有例外情況。

D=Durability持久性：一旦事務提交,那麼就永遠是這樣子了,哪怕系統崩潰也不會影響到這個事務的結果。

23、併發事務帶來哪些問題？(高頻)

在典型的應用程式中，多個事務併發運行，經常會操作相同的數據來完成各自的任務（多個用戶對同一數據進行操作）。併發雖然是必須的，但可能會導致以下的問題。

臟讀（Dirty read）: 當一個事務正在訪問數據並且對數據進行了修改，而這種修改還沒有提交到資料庫中，這時另外一個事務也訪問了這個數據，然後使用了這個數據。因為這個數據是還沒有提交的數據，那麼另外一個事務讀到的這個數據是“臟數據”，依據“臟數據”所做的操作可能是不正確的。

丟失修改（Lost to modify）：指在一個事務讀取一個數據時，另外一個事務也訪問了該數據，那麼在第一個事務中修改了這個數據後，第二個事務也修改了這個數據。這樣第一個事務內的修改結果就被丟失，因此稱為丟失修改。 例如：事務1讀取某表中的數據A=20，事務2也讀取A=20，事務1修改A=A-1，事務2也修改A=A-1，最終結果A=19，事務1的修改被丟失。

不可重覆讀（Unrepeatableread）：指在一個事務內多次讀同一數據。在這個事務還沒有結束時，另一個事務也訪問該數據。那麼，在第一個事務中的兩次讀數據之間，由於第二個事務的修改導致第一個事務兩次讀取的數據可能不太一樣。這就發生了在一個事務內兩次讀到的數據是不一樣的情況，因此稱為不可重覆讀。

幻讀（Phantom read）：幻讀與不可重覆讀類似。它發生在一個事務（T1）讀取了幾行數據，接著另一個併發事務（T2）插入了一些數據時。在隨後的查詢中，第一個事務（T1）就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，所以稱為幻讀。

24、怎麼解決這些問題呢？MySQL的預設隔離級別是？(高頻)

解決方案：對事務進行隔離

MySQL的四種隔離級別如下:

未提交讀(READ UNCOMMITTED)：這個隔離級別下,其他事務可以看到本事務沒有提交的部分修改。因此會造成臟讀的問題(讀取到了其他事務未提交的部分,而之後該事務進行了回滾)。這個級別的性能沒有足夠大的優勢,但是又有很多的問題,因此很少使用.

sql演示：

# 插入數據
insert into goods_innodb(name) values('華為');
insert into goods_innodb(name) values('小米');

# 會話一
set session transaction isolation level read uncommitted ;		# 設置事務的隔離級別為read uncommitted
start transaction ;												# 開啟事務
select * from goods_innodb ;									# 查詢數據

# 會話二
set session transaction isolation level read uncommitted ;		# 設置事務的隔離級別為read uncommitted
start transaction ;												# 開啟事務
update goods_innodb set name = '中興' where id = 10 ;			   # 修改數據

# 會話一
select * from goods_innodb ;									# 查詢數據

已提交讀(READ COMMITTED)：其他事務只能讀取到本事務已經提交的部分。這個隔離級別有不可重覆讀的問題，在同一個事務內的兩次讀取,拿到的結果竟然不一樣,因為另外一個事務對數據進行了修改。

sql演示：

# 會話一
set session transaction isolation level read committed ;		# 設置事務的隔離級別為read committed
start transaction ;												# 開啟事務
select * from goods_innodb ;									# 查詢數據

# 會話二
set session transaction isolation level read committed ;		# 設置事務的隔離級別為read committed
start transaction ;												# 開啟事務
update goods_innodb set name = '中興' where id = 1 ;			   # 修改數據

# 會話一
select * from goods_innodb ;									# 查詢數據

# 會話二
commit;															# 提交事務

# 會話一
select * from goods_innodb ;									# 查詢數據

REPEATABLE READ(可重覆讀)：可重覆讀隔離級別解決了上面不可重覆讀的問題(看名字也知道)，但是不能完全解決幻讀。MySql預設的事務隔離級別就是：

REPEATABLE READ

select @@tx_isolation;

sql演示(解決不可重覆讀)：

# 會話一
start transaction ;												# 開啟事務
select * from goods_innodb ;									# 查詢數據

# 會話二
start transaction ;												# 開啟事務
update goods_innodb set name = '榮耀' where id = 1 ;			   # 修改數據

# 會話一
select * from goods_innodb ;									# 查詢數據

# 會話二
commit;															# 提交事務

# 會話一
select * from goods_innodb ;									# 查詢數據

sql演示(測試不會出現幻讀的情況)：

# 會話一
start transaction ;												# 開啟事務
select * from goods_innodb ;									# 查詢數據

# 會話二
start transaction ;												# 開啟事務
insert into goods_innodb(name) values('小米');			   	   # 插入數據
commit;															# 提交事務

# 會話一
select * from goods_innodb ;									# 查詢數據

sql演示(測試出現幻讀的情況)：

# 表結構進行修改
ALTER TABLE goods_innodb ADD version int(10) NULL ;

# 會話一
start transaction ;												# 開啟事務
select * from goods_innodb where version = 1;					# 查詢一條不滿足條件的數據

# 會話二
start transaction ;												# 開啟事務
insert into goods_innodb(name, version) values('vivo', 1);	    # 插入一條滿足條件的數據 
commit;															# 提交事務

# 會話一
update goods_innodb set name = '金立' where version = 1; 		   # 將version為1的數據更改為'金立'
select * from goods_innodb where version = 1;					# 查詢一條不滿足條件的數據

SERIALIZABLE(可串列化)：這是最高的隔離級別,可以解決上面提到的所有問題,因為他強制將所以的操作串列執行,這會導致併發性能極速下降,因此也不是很常用。

5 鎖

25、MySQL中有哪幾種鎖？

從對數據操作的粒度分 ：

1） 表鎖：操作時，會鎖定整個表。

2） 行鎖：操作時，會鎖定當前操作行。

3） 頁面鎖：會鎖定一部分的數據

從對數據操作的類型分：

1） 讀鎖（共用鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。

2） 寫鎖（排它鎖）：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。

各存儲引擎對鎖的支持情況：

存儲引擎 表級鎖 行級鎖 頁面鎖
MyISAM 支持 不支持 不支持
InnoDB 支持 支持 不支持
MEMORY 支持 不支持 不支持
BDB 支持 不支持 支持

MySQL這2種鎖的特性可大致歸納如下 ：

鎖類型 特點
表級鎖 偏向MyISAM 存儲引擎，開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高,併發度最低。
行級鎖 偏向InnoDB 存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,併發度也最高。
頁面鎖 開銷和加鎖時間界於表鎖和行鎖之間； 會出現死鎖； 鎖定粒度界於表鎖和行鎖之間， 併發度一般。

從鎖的角度來說：表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web 應用；而行級鎖則更適合於有大量按索引條件併發更新少量

不同數據，同時又有並查詢的應用，如一些線上事務處理（OLTP）系統。

MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT 等）前，會自動給涉及的表加

寫鎖，這個過程並不需要用戶干預，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。

顯示加表鎖語法：

加讀鎖 ： lock table table_name read;
加寫鎖 ： lock table table_name write；
解鎖:     unlock tables;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 利用MySQL實現分散式鎖，涉及到樂觀鎖和悲觀鎖的思想
    對於一些併發量不是很高的場景，使用MySQL來實現會比較精簡且巧妙。 下麵就一個小例子，針對不加鎖、樂觀鎖以及悲觀鎖這三種方式來實現。 主要是一個用戶表，它有一個年齡的欄位，然後併發地對其加一，看看結果是否正確。 ...
  • 聊聊Netty那些事兒之從內核角度看IO模型
    從內核角度介紹了經常容易混淆的阻塞與非阻塞，同步與非同步的概念。以這個作為鋪墊，我們通過一個C10K的問題，引出了五種IO模型，隨後在IO多路復用中以技術演進的形式介紹了select,poll,epoll的原理和它們綜合的對比。最後我們介紹了兩種IO線程模型以及netty中的Reactor模型。 ...
  • 三菱M70巨集變數讀取三菱M80公共變數採集三菱CNC變數讀取採集三菱CNC遠程刀補三菱機床線上刀補三菱數控線上測量
    本章將和大家分享在ASP.NET Core中如何使用UseMiddleware擴展方法註冊自定義中間件及其實現原理。 ...
  • ps 2022 mac最新
    Photoshop 2022中文Mac版是一款高效、專業、實用的圖像處理軟體，Photoshop是以其強悍的編輯和調整、繪圖等功能，從而受到了廣泛的應用，不管是在各種圖片的調整和圖畫繪製以及圖像的修複、調色等工具都是數不勝數的，用戶可以從自己照片修飾到海報、包裝、橫幅的製作以及照片的處理，只要是你想 ...
  • VMware Tools和open-vm-tools的安裝與使用：解決虛擬機不全屏和無法傳輸文件的問題
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 在VMware Workstation上新建了一個虛擬機，安裝了Ubuntu 10.04版本的iso，創建好後發現了兩個問題： (1) 虛擬機界面並不能填充滿整個屏幕，使用起來十分反人類。 (2) 主機和虛擬機不能隨意相互複製粘貼，無論是文件還是 ...
  • docker-registry 私有倉庫鏡像 之 查看與刪除
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 查看私有倉庫有哪些鏡像 如果私有倉庫帶有認證，在使用 curl 命令的時候需要帶上 -u 參數 使用方法： curl -XGET -u <倉庫用戶名>:<用戶名密碼> http://<倉庫ip地址>:<倉庫埠>/v2/_catalog curl ...
  • Linux通配符與正則表達式
    一、通配符 匹配參數，匹配文件/目錄名字 ： *.txt *.sh lidao{1,4}.txt | * | 所有 | | | | | {} | 生成序列 | | [] | 【a-z】匹配小寫字母，一個中括弧相當於一個字元 | | [^] | 取反排除 | | ? | 任何一個字元 | 1. 通配符 ...
  • 讓你的Nginx支持分散式追蹤opentracing
    Background NGINX 是一個通用且流行的應用程式。也是最流行的 Web 伺服器，它可用於提供靜態文件內容，但也通常與其他服務一起用作分散式系統中的組件，在其中它用作反向代理、負載均衡 或 API 網關。 分散式追蹤 distributed tracing 是一種可用於分析與監控應用程式的 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類