事務及索引

事務

事務的原則：ACID 原則： 原子性，一致性，隔離性，永續性

1. 原子性（Atomicity）：要麼成功要麼失敗
2. 一致性（Consistency）：事務前後的資料完整性要一致
3. 隔離性（Isolation）：事務的隔離性是多個使用者併發訪問資料庫時，資料庫為每一個使用者開啟的事務，不能被其他事務的運算元據所干擾，多個併發事務之間要相互隔離
4. 永續性（Durability）：一個事務一旦被提交，它對資料庫中資料的改變就是永久性的，之後即使資料庫出現故障也不應該對其有影響

事務的隔離級別

• 髒讀：只一個事務讀取了另外一個事務未提交的資料
• 不可重複讀：在一個事務內讀取表中的某一行資料，多次讀取結果不同。
• 虛讀(幻讀)：是指在一個事務內讀取到了別的事務插入的資料，導致前後讀取不一致。（一般是行影響，多了一行）

格式

MySQL是預設開啟事務自動提交的

-- 事務
-- mysql是預設開啟事務自動提交的
SET autocommit = 0  -- 關閉
SET autocommit = 1  -- 開啟（預設）

-- 手動處理事務
SET autocommit = 0  -- 關閉自動提交

-- 事務開啟
START TRANSACTION  -- 標記一個事務的開始，從這個之後的sql都在同一個事務內

INSERT **
INSERT **

-- 提交：持久化（成功）
COMMIT 
-- 回滾：回到原來的樣子（失敗）
ROLLBACK

-- 事務結束
SET autocommit = 1  -- 開啟自動提交

-- 瞭解
SAVEPOINT  儲存點名 -- 設定一個事務的儲存點
ROLLBACK TO SAVEPOINT  儲存點名  -- 回滾到儲存點
RELEASE SAVEPOINT  儲存點名  -- 撤銷儲存點 

使用事務模擬轉賬

-- 事務：轉賬
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci  -- 建立一個shop資料庫
USE shop  -- 轉到shop資料庫

-- 建立account表
CREATE TABLE `account`(
	`id` INT(10) 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 ('zhang',2000.00),('liu',10000.00),('shi',100000000)

-- 模擬轉賬：事務
SET autocommit = 0  -- 關閉自動提交
START TRANSACTION  -- 開啟一個事務（一組事務）

UPDATE account SET money = money - 500 WHERE `name` = 'liu'  -- liu減去500
UPDATE account SET money = money + 500 WHERE `name` = 'zhang'  -- zhang加500

COMMIT;  -- 提交事務，提交後被持久化
ROLLBACK;  -- 回滾

SET autocommit = 1;  -- 回覆預設值

索引

本質：索引是資料結構。

索引的分類

• 主鍵索引：（PRIMARY KEY）
  • 唯一的標識，主鍵不可重複，只能有一個列作為主鍵
• 唯一索引：（UNIQUE KEY）
  • 避免重複的列出現，唯一索引可以重複，多個列都可以標識為唯一索引
• 常規索引：（KEY / INDEX）
  • 預設的，通過index、key關鍵字設定
• 全文索引：（FullText）
  • 在特定的資料庫引擎下才有：MyISAM
  • 快速定位資料

SHOW INDEX FROM student  -- 顯示所有的索引資訊

基礎語法

-- 顯示所有的索引資訊
SHOW INDEX FROM student

-- 增加一個全文索引  （索引名）  列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql執行的狀況
EXPLAIN SELECT * FROM student;  -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('劉')

-- 給表中的某個欄位新增索引：CREATE INDEX 索引名 ON 表(欄位); 
CREATE INDEX id_app_user_name ON app_user(`name`);  -- 給app_user表中的name欄位新增索引id_app_user_name

索引原則

• 索引不是越多越好
• 不要對程式變動資料加索引
• 小資料量的表不需要加索引
• 索引一般加在常用來查詢的欄位上

索引的資料結構

Hash型別的索引

Btree：InnoDB的預設資料結構