主頁 > 資料庫 > Java面試題(二)--MySQL

Java面試題(二)--MySQL

2022-07-14 08:16:46 資料庫

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 什么是聚簇索引和非聚簇索引？(高頻)

聚簇索引：將資料和索引放在一起，并且是按照一定順序組織的，找到索引也就找到了資料，一般情況下主鍵就是默認的聚簇索引，

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

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

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

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;

InnoDB 的行鎖模式

InnoDB 實作了以下兩種型別的行鎖，

  • 共享鎖（S）：又稱為讀鎖，簡稱S鎖，共享鎖就是多個事務對于同一資料可以共享一把鎖，都能訪問到資料，但是只能讀不能修改，

  • 排他鎖（X）：又稱為寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖并存，如一個事務獲取了一個資料行的排他鎖，其他事務就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務是可以對資料就行讀取和修改，

對于UPDATE、DELETE和INSERT陳述句，InnoDB會自動給涉及資料集(行)加排他鎖（X)；對于普通SELECT陳述句，InnoDB不會加任何鎖；

可以通過以下陳述句顯示給記錄集加共享鎖或排他鎖 ，

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

本文來自博客園，作者：{Orator-xy}，轉載請注明原文鏈接：{https://www.cnblogs.com/xy1857/}

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499107.html

標籤：MySQL

上一篇：windows安裝mysql8.0.29（ZIP解壓安裝版本）

下一篇：mysql拆分字串做條件查詢

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂