MySQL-事務+索引
事務*
事務(Transaction)原則：ACID原則：原子性（Atomicity）、一致性（consistency）、隔離性（isolation）、持久性（durability）
原子大小，目標一致，一入即隔離，持久有力（臟讀，幻讀。。。）
原子性：針對同一事務。
要么都成功，要么都失敗。
一致性：事務查找前后狀態一致。最終一致性。
事務前后的數據完整性要保證一致。
隔離性：針對多個用戶同時操作，主要排除其他事務對本次事務的影響，有一個隔離的關系。
為每一個用戶開啟一個事務，事務間互不干擾。
持久性：事務結束后的數據不隨外界原因導致數據丟失。事務沒有提交就恢復原狀，事務提交，按提交之后為準。
事務一但提交不可逆，被持久化到數據庫中(事務提交)。
**問題：**隔離失敗–隔離級別：
臟讀：一個事務讀取了另一個事務未提交的數據。
數據沒提交
不可重復讀：一個事務讀取數據時，多次讀取結果不同。
數據變了
虛讀(幻讀)：一個事務內讀取到別的事務插入的數據，導致前后讀取不一致。多了一行。
數據多了
不可重復讀都是已有的數據發生改變，幻讀是指讀入了另一個事務新插入的數據。
Mysql是默認開啟事務提交的。
SET autocommit = 0; # 自動提交事務關閉close SET autocommit = 1; # 自動提交事務開啟open(默認default) # 手動處理 SET autocommit = 0; # 事務開啟 START TRANSACTION # 標記一個事務的開始，從這個之后的sql都在同一個事務內 INSERT xx INSERT xx # 提交 持久化（成功success） COMMIT # 回滾 回到原來的驗證（失敗fail） ROLLBACK # 事務結束 SET autocommit = 1; SAVEPOINT 保存點名 # 設置一個事務的保存點save Archive ROLLBACK TO SAVEPOINT 保存點名 # 回滾到保存點Archive # commit或者rollback代表事務結束 RELEASE SAVEPOINT 保存點名 #release釋放，撤銷保存點 delete Archive
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
實踐轉賬
create database[if not exists] shop CHARACTER SET utf8 COLLATE utf8_general_ci; # COLLATE校對字符集 USE shop CREATE TABLE `accout`( `id` INT(3) NOT NULL AUTO_INCREMENT, `name` VARCHAR(30) NOT NULL, `money` DECIMAL(9,2) NOT NULL, PRIMARY KEY (`id`) )Engine=INNODB DEFAULT CHARSET=utf8; INSERT INTO account(`name`, `money`) VALUES ('A',2000.00),('B',3300.00); #轉賬 SET autocommit = 0; #關閉自動提交 START TRANSACTION;# 開啟事務 UPDATE account SET money=money-500 WHERE `name` = 'A'; UPDATE account SET money=money+500 WHERE `name` = 'B'; COMMIT; # 提交事務,被持久化了 ROLLBACK; # 回滾沒有用了 SET autocommit = 1; #關閉自動提交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
模擬實現不同隔離級別下產生的一致性相關問題
select @@tx_isolation; # 查看隔離級別 # 更改隔離級別 set [global | session] transaction isolation level read uncommitted; # 其中global是全局會話，session是當前會話 # Read Uncommited,在READ-UNCOMMITED下第二個會話可以查看到第一個會話的未提交的內容 set [global | session] transaction isolation level read committed; # READ COMMITTED（讀已提交）,該隔離級別下解決了未提交數據不能查看的問題。在該隔離級別下，如果進行update操作會導致前后查詢的結果不一致，即產生臟數據 set [global | session] transaction isolation level REPEATABLE READ; # REPEATABLE READ（可重復讀），解決了臟讀的問題。在Mysql的RR級別下也解決了幻讀的問題（兩次查詢的行數不同）
1
2
3
4
5
6
7
8
9
MySQL的鎖機制
1）隔離級別越高，性能越差，這是由于在MySQL中是通過鎖機制來解決存在的問題。
2）鎖機制：共享鎖（讀鎖 Shared Lock)、排它鎖（寫鎖 Exclusive Lock）、間隙鎖（Record Lock)、表鎖
按照粒度：行鎖（Innodb支持）、表鎖
3)串行化的實現
讀的時候加共享鎖，不能寫
寫的時候加排他鎖，阻塞其它事務的讀取和寫入
4)讀已提交和可重復讀
底層采用MVCC（多版本并發控制協議）
5)間隙鎖則分為兩種：Gap Locks和Next-Key Locks。Gap Locks會鎖住兩個索引之間的區間，比如select * from User where id>3 and id<5 for update，就會在區間（3，5）之間加上Gap Locks。(解決幻讀問題）
6)增刪改加排它鎖，查詢不會加鎖
只能通過在select語句后顯式加lock in share mode或者for update來加共享鎖或者排它鎖。
索引
獲取數據的數據結構，通過索引可以更快的獲取sql里面的結果。
那么為什么mysql索引要用b+樹原因如下：
B+樹能顯著減少IO次數，提高效率
B+樹的查詢效率更加穩定，因為數據放在葉子節點
B+樹能提高范圍查詢的效率，因為葉子節點指向下一個葉子節點
分類
主鍵索引（PRIMARY KEY）
唯一標識，主鍵不可重復，只能有一個，可以多個列共同組成聯合主鍵，但是不建議聯合主鍵。
PRIMARY KEY (列名)
唯一索引（UNIQUE KEY）
避免重復的列出現，唯一索引可以重復，多個列都可以作為標識 唯一索引。一個列(字段)中的避免有重復數據
UNIQUE KEY 索引名 (列名)
常規索引（KEY/INDEX）
默認的，index，key關鍵字設置
KEY 索引名 (列名)
全文索引（FullText）
在特定的數據庫引擎下采樣，MyISAM
快速定位數據
# 顯示所有索引信息 SHOW INDEX FROM student; # 添加全文索引 ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`); # 在 school數據庫下的student表里面添加全文索引字段 # EXPLAIN 分析sql執行的狀況 EXPLAIN SELECT * FROM student; # 非全文索引 SELECT * FROM student WHERE MATCH(studentName) AGAINST('xx'); # 全文索引
1
2
3
4
5
6
7
8
9
10
11
EXPLAIN
索引添加
# 給表中的字段添加索引 # CREATE INDEX 索引名 on 表(字段) CREATE INDEX id_app_user_name ON apper_user(`name`); # 給每一個用戶插入唯一索引
1
2
3
索引原則
索引不是越多越好
不要對進程變動數據加索引
小數據量的表不需要加索引
索引加在常用來查詢的字段上
索引數據結構
Btree
MySQL
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
相關文章