mysql鎖+索引

mysql鎖

按鎖的粒度分類

表級鎖（Table - level locks）
- 特點：對整張表進行鎖定，實現簡單，加鎖和釋放鎖的速度快，但并發度較低。當一個事務對表加表級鎖后，其他事務對該表的讀寫操作都可能被阻塞。
- 應用場景：適用于執行大量數據的批量操作，如?ALTER TABLE?、LOCK TABLES?等語句。
- 類型：
  - 共享鎖（Shared Locks，簡稱 S 鎖）：也叫讀鎖，多個事務可以同時獲取一個表的共享鎖來讀取數據，但不能同時對該表進行寫操作。
  - 排他鎖（Exclusive Locks，簡稱 X 鎖）：也叫寫鎖，一個事務獲取排他鎖后，其他事務不能再獲取該表的任何鎖，直到持有排他鎖的事務釋放鎖。
行級鎖（Row - level locks）
- 特點：只鎖定表中的某一行數據，并發度高，但加鎖和釋放鎖的開銷相對較大。InnoDB 存儲引擎支持行級鎖。
- 應用場景：適用于高并發的 OLTP（聯機事務處理）系統，對同一表中的不同行進行并發讀寫操作。
- 類型：
  - 共享鎖（S 鎖）：允許事務讀取一行數據，多個事務可以同時對同一行加共享鎖。
  - 排他鎖（X 鎖）：阻止其他事務讀取和修改該行數據。
  - 意向鎖（Intention Locks）：包括意向共享鎖（IS 鎖）和意向排他鎖（IX 鎖），用于表示事務在表級別上的意圖。意向鎖是表級別的鎖，主要用于在表級鎖和行級鎖之間進行協調，提高加鎖操作的效率。
頁級鎖（Page - level locks）
- 特點：鎖定粒度介于表級鎖和行級鎖之間，鎖定的是數據頁。它的開銷和并發度也介于兩者之間。BDB 存儲引擎支持頁級鎖，InnoDB 存儲引擎主要使用行級鎖和表級意向鎖，基本不涉及頁級鎖。

共享鎖區別

表級共享鎖：你給整個筆記本加了一把 “只讀鎖”，自己可以看，別人也能拿過去一起看，但誰都不能在筆記本上涂改（寫操作）。哪怕別人只想改其中一個人的信息，也得等你把鎖解開。
- 不過，別人可以隨便看或改其他行（比如第 6 行李四的信息），互不影響。
- 行級共享鎖：你只給筆記本里某一行（比如第 5 行，張三的信息）加了 “只讀鎖”，你可以看張三的信息，別人也能看張三的信息（也可以加行級共享鎖）。但別人想改張三的信息就不行，得等你解鎖。
- 表級共享鎖 “管得寬”，整個表都受限制，適合全表讀但不寫的場景；
  行級共享鎖 “管得細”，只限制某一行，其他行該咋操作咋操作，適合多個人同時操作不同行的場景。

排他鎖區別

????????????????表級排他鎖：
你把整個筆記本鎖起來了，鑰匙只有你有。這時候：

你既能看筆記本里的內容，也能隨便涂改（讀寫都行）。
其他人既不能看，也不能改，連碰都碰不了，只能等你把鎖打開。

????????????????行級排他鎖：
你只鎖了筆記本里的某一行（比如第 5 行張三的信息），鑰匙只有你有。這時候

你能看、能改這一行的內容。
其他人既不能看這一行，也不能改這一行，得等你解鎖。
但其他人可以隨便看、隨便改其他行（比如第 6 行李四的信息），互不影響。

區別核心：
表級排他鎖是 “一鍋端”，整個表都被你獨占；
行級排他鎖是 “精準打擊”，只獨占某一行，其他行大家該咋用咋用。

意向鎖：

假設你要給筆記本的某一行加鎖（行鎖），但在加行鎖之前，系統會先在整本筆記本的封面上貼一個小標簽，標簽上寫著 “我打算鎖某一行哦”—— 這個標簽就是意向鎖。

具體來說：

如果你想給某一行加共享鎖（讀鎖），系統會先加一個 “意向共享鎖”（IS 鎖）的標簽。
如果你想給某一行加排他鎖（寫鎖），系統會先加一個 “意向排他鎖”（IX 鎖）的標簽。

這么做的目的是提高效率：
比如有人想給整個筆記本加表鎖時，不用逐行檢查有沒有行鎖，只看封面上的標簽就知道：“哦，里面已經有人打算鎖行了，我暫時加不了表鎖”，省去了逐行排查的麻煩。

簡單說，意向鎖就是 “提前打招呼”，告訴系統 “我接下來要鎖某一行”，避免后續操作做無用功

按鎖的使用方式分類

樂觀鎖（Optimistic Locks）
- 原理：它假設多用戶并發的事務在處理時不會彼此干擾，只有在提交數據更新時，才會正式對數據的沖突與否進行檢測。一般通過在表中增加一個版本號（version）字段或時間戳（timestamp）字段來實現。在更新數據前，先讀取數據的版本號，在提交更新時，將讀取到的版本號與數據庫中當前記錄的版本號進行比較，如果相同則更新，并將版本號加 1；如果不同則說明數據已被其他事務修改，放棄本次更新或進行其他處理。
- 應用場景：適用于讀多寫少，沖突概率較低的場景。
悲觀鎖（Pessimistic Locks）
- 原理：它假設多用戶并發的事務在處理時會彼此干擾，所以在數據處理前，先對數據進行加鎖，確保在自己處理數據的過程中，其他事務無法對數據進行修改。前面提到的共享鎖、排他鎖都屬于悲觀鎖的范疇。
- 應用場景：適用于寫多讀少，數據沖突概率較高的場景。

樂觀鎖和悲觀鎖區別：

悲觀鎖：假設一定會沖突，先下手為強

????????就像你去圖書館借書，擔心別人也會借這本，所以拿到書后直接把它鎖在自己的座位上（全程獨占），直到你看完還回去，別人才能碰。
????????核心邏輯：認為并發操作時肯定會出現沖突，所以在操作數據前，先 “上鎖” 防止別人修改，確保自己操作時數據不會被干擾。
????????例子：

數據庫里的行級排他鎖、表級排他鎖，就是典型的悲觀鎖。比如你修改一條訂單數據時，先給這行加排他鎖，別人既不能讀也不能改，直到你改完提交。
Java 中的synchronized關鍵字、ReentrantLock，也是悲觀鎖的實現。

????????適合場景：寫操作頻繁、沖突概率高的場景（比如秒殺時修改庫存），避免頻繁重試帶來的開銷。

樂觀鎖：假設不會沖突，事后再檢查

????????還是借書的例子，你覺得大概率沒人和你搶，所以直接拿書去看，看完準備還的時候，才檢查一下：“這段時間有沒有人動過這本書？” 如果沒人動過，就正常還；如果被人改過，就重新拿一本再看。
????????核心邏輯：認為并發操作時沖突很少發生，所以操作時不加鎖，而是在提交修改前，檢查數據是否被別人動過。如果沒被修改，就正常提交；如果被修改了，就放棄或重試。
????????例子：

數據庫中常用 “版本號” 實現樂觀鎖：給每條數據加一個version字段，讀取時記下版本號，修改后提交時，檢查當前版本號是否和讀取時一致。一致就更新（同時版本號 + 1），不一致就重試。
比如：update 商品 set 庫存=10, version=version+1 where id=1 and version=5（如果 version 還是 5，說明沒人改，就更新）。
Java 中的AtomicInteger（原子類），通過 CAS（比較并交換）機制實現樂觀鎖。

適合場景：讀操作頻繁、沖突概率低的場景（比如商品詳情查詢），避免加鎖帶來的性能損耗。

總結

類型	核心思路	優點	缺點	典型場景
悲觀鎖	先上鎖，再操作	避免沖突，操作簡單	加鎖開銷大，并發效率低	寫多讀少、沖突頻繁
樂觀鎖	先操作，提交時檢查沖突	無鎖開銷，并發效率高	沖突時需重試，邏輯復雜	讀多寫

1. 行鎖（Row Lock）

鎖定范圍：只鎖表中實際存在的某一行數據。
例子：當你更新?id=20?的記錄時，InnoDB 會給這一行加行鎖。這時：
- 其他事務不能修改?id=20?這一行（會被阻塞）。
- 但可以修改?id=10、id=30?或其他行，互不影響。

2. 間隙鎖（Gap Lock）

鎖定范圍：鎖的是兩個記錄之間的 “空隙”（不包含實際記錄本身），防止其他事務在這個空隙中插入新數據。
例子：表中已有 id=10、20、30，當你操作?id>10 and id<20?的范圍時（比如查詢或更新），InnoDB 可能會對 10~20 之間的間隙加鎖。這時：
- 其他事務不能在這個間隙中插入?id=15?這樣的新記錄（會被阻塞）。
- 但不影響已存在的 10、20 行的修改（如果沒加行鎖的話）。

3. 臨鍵鎖（Next-Key Lock）

鎖定范圍：行鎖 + 間隙鎖的組合，既鎖實際存在的某一行，又鎖這一行前面的間隙。
例子：表中已有 id=10、20、30，當你操作?id=20?時，臨鍵鎖會鎖定?id=20?這一行，以及 10~20 之間的間隙。這時：
- 其他事務不能修改?id=20?這一行。
- 也不能在 10~20 之間插入新記錄（比如 id=15）。
這是 InnoDB 默認的行級鎖算法（當查詢條件是范圍或非唯一索引時會觸發），目的是防止幻讀（同一事務中，兩次查詢出現不同的新數據）。

總結

鎖類型	鎖定范圍	作用	觸發場景舉例
行鎖	單個實際存在的行	防止同一行數據被并發修改	通過唯一索引精準修改某一行
間隙鎖	兩個記錄之間的空隙	防止在間隙中插入新數據，避免幻讀	范圍查詢（如?`where id between 10 and 20`）
臨鍵鎖	某一行 + 該行前面的間隙	同時防止行修改和間隙插入，徹底避免幻讀	默認情況下的行級鎖（非唯一索引操作）

簡單說：行鎖鎖 “已有行”，間隙鎖鎖 “空隙”，臨鍵鎖是兩者的結合，是 InnoDB 解決幻讀的核心機制。

mysql索引

索引（index）是幫助MySQL高效獲取數據的數據結構（有序）。

索引是在數據庫表的字段上添加的，是為了提高查詢效率存在的一種機制

索引是在MySQL的存儲引擎層中實現的，而不是在服務器層實現的

1.什么時候建立

1.針對于數據量較大且查詢比較頻繁的表，建立索引。

2.針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。

2.建立什么索引

1.盡量選擇區分度高的列作為索引，盡量建立唯一索引（區分度越高，使用索引的效率越高）

2.字符串類型的字段的長度較長，建立前綴索引。

3.盡量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆蓋索引，節省存儲空間，避免回表，提高查詢效率。

索引結構組織：

我們平常所說的索引，如果沒有特別指明，都是指B+樹（多路搜索樹，并不一定是二叉的）結構組織的索引。其中聚集索引、復合索引、前綴索引、唯一索引默認都是使用 B+tree 索引，統稱為索引。

對比維度	B + 樹	哈希（Hash）
有序性	葉子節點按鍵值有序排列，支持范圍查詢	數據無序，無法直接做范圍查詢
查詢方式	支持精確查詢、范圍查詢（>、<、between 等）、排序	僅支持精確查詢（=），不支持范圍和排序
查詢效率	穩定，復雜度為 O (log n)（層級查詢）	理想情況 O (1)（直接定位），但哈希沖突時可能退化
適用數據類型	所有可比較的數據類型（數字、字符串等）	適合做哈希計算的類型（避免頻繁沖突）
空間開銷	較高（需要存儲索引結構和指針）	較低（主要存儲哈希表和數據）
動態維護	插入 / 刪除時需調整樹結構，保持平衡	插入 / 刪除可能觸發哈希表擴容或沖突處理

3. 各自的優點

B + 樹的優點

支持范圍查詢和排序：
由于葉子節點有序且鏈表連接，能高效處理?where id > 10 and id < 100?這類范圍查詢，以及?order by?排序操作（直接遍歷葉子節點鏈表）。
查詢穩定：
無論數據位置如何，查詢復雜度都是 O (log n)，不會因數據分布不均導致性能波動。
適合大數據量：
多叉結構減少了樹的高度（通常 3-4 層），減少磁盤 IO 次數，適合磁盤存儲的數據庫場景。
支持聯合索引：
可基于多個字段構建索引（如?(name, age)），通過前綴匹配高效查詢。

哈希（Hash）的優點

精確查詢速度快：
理想情況下，一次哈希計算就能定位數據，比 B + 樹的多層查詢更快，適合高頻次的精確匹配（如?where id = 123）。
實現簡單：
數據結構和查詢邏輯簡單，內存開銷較小（相比 B + 樹的復雜結構）。

4. 適用場景

B + 樹：
適合需要范圍查詢、排序、聯合索引的場景，是關系型數據庫（如 MySQL、PostgreSQL）的默認索引結構（如 InnoDB 的主鍵索引）。
哈希（Hash）：
適合僅需精確查詢的場景，如 NoSQL 數據庫（如 Redis 的哈希表）、內存數據庫中的臨時索引，或某些特定業務的高頻精確查詢。

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

BTREE 索引:最常見的索引類型，大部分索引都支持"B樹"索引。
HASH 索引：只有Memory引擎支持，使用場景簡單。
R-tree 索引（空間索引）:空間索引是MyISAM引擎的一個特殊索引類型，主要用于地理空間數據類型，通常使用較少，不做特別介紹。
Full-text （全文索引）:全文索引也是MyISAM的一個特殊索引類型，主要用于全文索引，InnoDB從Mysql5.6版本開始支持全文索引。
MyISAM、InnoDB、Memory三種存儲引擎對各種索引類型的支持
索引 InnoDB引擎 MyISAM引擎 Memory引擎
BTREE索引 支持 支持 支持
HASH 索引不支持不支持 支持
R-tree 索引不支持支持不支持
Full-text 5.6版本之后支持支持不支持

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

常見索引類型

1）主鍵索引 :是數據庫中一種特殊的索引，用于唯一標識表中的每一條記錄。具有唯一性、非空性、自動索引（定義主鍵自動添加索引）、優化查詢、數據完整性等特性

2）單值索引：即一個索引只包含單個列，一個表可以有多個單列索引

3）唯一索引：索引列的值必須唯一，但允許有空值

4）復合索引：即一個索引包含多個列，與單值索引（只在一個列上創建）不同，復合索引能夠提高基于多個列的查詢效率

索引語法

1.創建索引

示例：為city表中的city_name字段創建單值索引;

create index idx_city_name on city(city_name);

1.普通索引（INDEX）普通索引是最基本的索引類型，用于加速數據的檢索。

創建語法：CREATE INDEX 索引名 ON 表名(字段名);

示例：CREATE INDEX idx_name ON employees (last_name);

2.唯一索引（UNIQUE INDEX）唯一索引確保索引列中的所有值都唯一。它不僅加速查詢，還可以保證數據的唯一性。

創建語法：CREATE UNIQUE INDEX 索引名 ON 表名 (字段名);

示例：CREATE UNIQUE INDEX idx_email ON users (email);

3.主鍵索引（PRIMARY KEY）主鍵索引是唯一索引的一種，它不僅確保唯一性，還作為表的主鍵標識每一行數據。每個表只能有一個主鍵索引。

創建語法：ALTER TABLE表名 ADD PRIMARY KEY (字段名);

示例：ALTER TABLE employees ADD PRIMARY KEY (employee_id);

4.全局唯一索引（UNIQUE KEY）類似于唯一索引，但在某些情況下可以在不同的表或分區中進行分布式唯一性檢查。

創建語法：CREATE UNIQUE INDEX 索引名?ON 表名 (字段名);

示例：CREATE UNIQUE INDEX idx_username ON users (username);

5.復合索引（COMPOSITE INDEX）復合索引是在多個列上創建的索引，用于加速基于多個列的查詢。

創建語法：CREATE INDEX 索引名 ON 表名 (字段1, 字段2, ...);

示例：CREATE INDEX idx_name_age ON employees (last_name, age);

6.全文索引（FULLTEXT INDEX）全文索引用于加速對文本數據的全文搜索（如在 TEXT 或 VARCHAR 列上進行搜索）。僅支持 MyISAM 和 InnoDB(5.7之后版本) 存儲引擎。

創建語法：CREATE FULLTEXT INDEX 索引名 ON 表名 (字段名);

示例：CREATE FULLTEXT INDEX idx_description ON articles (description);

7.空間索引（SPATIAL INDEX）空間索引用于加速對空間數據的查詢，如地理位置數據。僅支持 MyISAM 存儲引擎。

創建語法：CREATE SPATIAL INDEX 索引名ON 表名 (geometry_column);

括號里的?geometry_column?指的是表中存儲空間地理數據的列，這個列的數據類型必須是 MySQL 支持的空間數據類型（如?GEOMETRY、POINT、LINESTRING、POLYGON?等）。

如果你的表 places 中有一個 location 列，類型是 POINT（用于存儲經緯度坐標），那么創建空間索引時，括號里就填這個列名：
示例：CREATE SPATIAL INDEX idx_location ON places (location);

8.哈希索引（HASH INDEX）哈希索引是 MEMORY 存儲引擎專用的索引類型，使用哈希算法進行索引。適用于等值查詢，但不支持范圍查詢。

創建語法：CREATE INDEX 索引名 USING HASH ON 表名 (字段名);

示例：CREATE INDEX idx_name_hash USING HASH ON employees (last_name);

9.位圖索引（BITMAP INDEX）位圖索引不是 MySQL 的內置索引類型，但在某些數據庫系統中（如 Oracle）有類似的實現，主要用于處理低基數（少量唯一值）的列。

創建索引的其他注意事項：

索引選擇：根據查詢的特點選擇合適的索引類型。例如，范圍查詢使用 B 樹索引效果更好，而全文搜索則使用全文索引。

索引維護：創建索引會增加數據插入、更新和刪除的開銷。應根據實際需求平衡查詢性能和寫入性能。

覆蓋索引：創建覆蓋索引（即索引中包含所有查詢列）可以顯著提高查詢性能。

索引管理查看索引：可以使用 SHOW INDEX 或 SHOW KEYS 語句查看表中的索引。

SHOW INDEX FROM table_name;

刪除索引：可以使用 DROP INDEX 語句刪除不再需要的索引。

DROP INDEX index_name ON table_name;

優化索引：定期使用 ANALYZE TABLE 和 OPTIMIZE TABLE 命令優化表的索引。

2.查看索引

????????????????????????show 索引名 ?from ?表名;

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

show index from city\G;

3.刪除索引

????????????????????????DROP INDEX 索引名 ON 表名;

示例：想要刪除city表上的索引idx_city_name，可以操作如下：

drop index idx_city_name on city;

4.修改索引使用ALTER命令

# 添加一個主鍵，索引值必須是唯一的，且不能為NULL
alter  table  tb_name  add  primary  key(column_list);# 創建唯一索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）
alter  table  tb_name  add  unique index_name(column_list);# 添加普通索引， 索引值可以重復。
alter  table  tb_name  add  index index_name(column_list);# 該語句指定了索引為FULLTEXT， 用于全文索引
alter  table  tb_name  add  fulltext  index_name(column_list);