事務(wù)

什么是事務(wù)

• 事務(wù)就是將一組SQL語句放在同一批次內(nèi)去執(zhí)行

• 如果一個SQL語句出錯,則該批次內(nèi)的所有SQL都將被取消執(zhí)行

• MySQL事務(wù)處理只支持InnoDB和BDB數(shù)據(jù)表類型

事務(wù)的ACID原則 百度 ACID

原子性(Atomic)

整個事務(wù)中的所有操作，要么全部完成姐霍，要么全部不完成，不可能停滯在中間某個環(huán)節(jié)亿卤。事務(wù)在執(zhí)行過程中發(fā)生錯誤夸赫，會被回滾（ROLLBACK）到事務(wù)開始前的狀態(tài)载绿，就像這個事務(wù)從來沒有執(zhí)行過一樣斋射。

一致性(Consist)

一個事務(wù)可以封裝狀態(tài)改變（除非它是一個只讀的）咒吐。事務(wù)必須始終保持系統(tǒng)處于一致的狀態(tài)，不管在任何給定的時間并發(fā)事務(wù)有多少娜谊。也就是說：如果事務(wù)是并發(fā)多個确买，系統(tǒng)也必須如同串行事務(wù)一樣操作。其主要特征是保護性和不變性(Preserving an Invariant)纱皆，以轉(zhuǎn)賬案例為例湾趾，假設(shè)有五個賬戶，每個賬戶余額是100元派草，那么五個賬戶總額是500元搀缠，如果在這個5個賬戶之間同時發(fā)生多個轉(zhuǎn)賬，無論并發(fā)多少個近迁，比如在A與B賬戶之間轉(zhuǎn)賬5元艺普，在C與D賬戶之間轉(zhuǎn)賬10元，在B與E之間轉(zhuǎn)賬15元鉴竭，五個賬戶總額也應(yīng)該還是500元衷敌，這就是保護性和不變性。

隔離性(Isolated)

隔離狀態(tài)執(zhí)行事務(wù)拓瞪，使它們好像是系統(tǒng)在給定時間內(nèi)執(zhí)行的唯一操作缴罗。如果有兩個事務(wù)，運行在相同的時間內(nèi)祭埂，執(zhí)行相同的功能面氓，事務(wù)的隔離性將確保每一事務(wù)在系統(tǒng)中認為只有該事務(wù)在使用系統(tǒng)。這種屬性有時稱為串行化蛆橡，為了防止事務(wù)操作間的混淆舌界，必須串行化或序列化請求，使得在同一時間僅有一個請求用于同一數(shù)據(jù)泰演。

持久性(Durable)

在事務(wù)完成以后呻拌，該事務(wù)對數(shù)據(jù)庫所作的更改便持久的保存在數(shù)據(jù)庫之中，并不會被回滾睦焕。

基本語法

-- 使用set語句來改變自動提交模式
SET autocommit = 0;   /*關(guān)閉*/
SET autocommit = 1;   /*開啟*/

-- 注意:
--- 1.MySQL中默認是自動提交
--- 2.使用事務(wù)時應(yīng)先關(guān)閉自動提交

-- 開始一個事務(wù),標(biāo)記事務(wù)的起始點
START TRANSACTION  

-- 提交一個事務(wù)給數(shù)據(jù)庫
COMMIT

-- 將事務(wù)回滾,數(shù)據(jù)回到本次事務(wù)的初始狀態(tài)
ROLLBACK

-- 還原MySQL數(shù)據(jù)庫的自動提交
SET autocommit =1;

-- 保存點
SAVEPOINT 保存點名稱 -- 設(shè)置一個事務(wù)保存點
ROLLBACK TO SAVEPOINT 保存點名稱 -- 回滾到保存點
RELEASE SAVEPOINT 保存點名稱 -- 刪除保存點

測試

/*
課堂測試題目

A在線買一款價格為500元商品,網(wǎng)上銀行轉(zhuǎn)賬.
A的銀行卡余額為2000,然后給商家B支付500.
商家B一開始的銀行卡余額為10000

創(chuàng)建數(shù)據(jù)庫shop和創(chuàng)建表account并插入2條數(shù)據(jù)
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 轉(zhuǎn)賬實現(xiàn)
SET autocommit = 0; -- 關(guān)閉自動提交
START TRANSACTION;  -- 開始一個事務(wù),標(biāo)記事務(wù)的起始點
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事務(wù)
# rollback;
SET autocommit = 1; -- 恢復(fù)自動提交

索引

索引的作用

• 提高查詢速度

• 確保數(shù)據(jù)的唯一性

• 可以加速表和表之間的連接 , 實現(xiàn)表與表之間的參照完整性

• 使用分組和排序子句進行數(shù)據(jù)檢索時 , 可以顯著減少分組和排序的時間

• 全文檢索字段進行搜索優(yōu)化.

分類

• 主鍵索引 (Primary Key)

• 唯一索引 (Unique)

• 常規(guī)索引 (Index)

• 全文索引 (FullText)

主鍵索引

主鍵 : 某一個屬性組能唯一標(biāo)識一條記錄

特點 :

• 最常見的索引類型

• 確保數(shù)據(jù)記錄的唯一性

• 確定特定數(shù)據(jù)記錄在數(shù)據(jù)庫中的位置

唯一索引

作用 : 避免同一個表中某數(shù)據(jù)列中的值重復(fù)

與主鍵索引的區(qū)別

• 主鍵索引只能有一個

• 唯一索引可能有多個

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常規(guī)索引

作用 : 快速定位特定數(shù)據(jù)

注意 :

• index 和 key 關(guān)鍵字都可以設(shè)置常規(guī)索引

• 應(yīng)加在查詢找條件的字段

• 不宜添加太多常規(guī)索引,影響數(shù)據(jù)的插入,刪除和修改操作

CREATE TABLE `result`(
   -- 省略一些代碼
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 創(chuàng)建表時添加
)
-- 創(chuàng)建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
全文索引

百度搜索：全文索引

作用 : 快速定位特定數(shù)據(jù)

注意 :

只能用于MyISAM類型的數(shù)據(jù)表

只能用于CHAR , VARCHAR , TEXT數(shù)據(jù)列類型

適合大型數(shù)據(jù)集

/*
#方法一：創(chuàng)建表時
  　　CREATE TABLE 表名 (
               字段名1 數(shù)據(jù)類型 [完整性約束條件…],
               字段名2 數(shù)據(jù)類型 [完整性約束條件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(長度)] [ASC |DESC])
               );


#方法二：CREATE在已存在的表上創(chuàng)建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(長度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上創(chuàng)建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(長度)] [ASC |DESC]) ;
                           
                           
#刪除索引：DROP INDEX 索引名 ON 表名字;
#刪除主鍵索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#顯示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL語句執(zhí)行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通過 MATCH() 函數(shù)完成藐握。
-- 搜索字符串作為 against() 的參數(shù)被給定。搜索以忽略字母大小寫的方式執(zhí)行垃喊。對于表中的每個記錄行猾普，MATCH() 返回一個相關(guān)性值。即本谜，在搜索字符串與記錄行在 MATCH() 列表中指定的列的文本之間的相似性尺度初家。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
開始之前，先說一下全文索引的版本、存儲引擎溜在、數(shù)據(jù)類型的支持情況

MySQL 5.6 以前的版本陌知，只有 MyISAM 存儲引擎支持全文索引；
MySQL 5.6 及以后的版本掖肋，MyISAM 和 InnoDB 存儲引擎均支持全文索引;
只有字段的數(shù)據(jù)類型為 char仆葡、varchar、text 及其系列才可以建全文索引培遵。
測試或使用全文索引時，要先看一下自己的 MySQL 版本登刺、存儲引擎和數(shù)據(jù)類型是否支持全文索引籽腕。
*/

拓展：測試索引

建表app_user：

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用戶昵稱',
`email` varchar(50) NOT NULL COMMENT '用戶郵箱',
`phone` varchar(20) DEFAULT '' COMMENT '手機號',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性別（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密碼',
`age` tinyint(4) DEFAULT '0' COMMENT '年齡',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用戶表'

批量插入數(shù)據(jù)：100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用戶', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

索引效率測試

無索引

SELECT * FROM app_user WHERE name = '用戶9999'; -- 查看耗時
SELECT * FROM app_user WHERE name = '用戶9999';
SELECT * FROM app_user WHERE name = '用戶9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用戶9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

創(chuàng)建索引

CREATE INDEX idx_app_user_name ON app_user(name);

測試普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用戶9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)

索引準(zhǔn)則

• 索引不是越多越好

• 不要對經(jīng)常變動的數(shù)據(jù)加索引

• 小數(shù)據(jù)量的表建議不要加索引

• 索引一般應(yīng)加在查找條件的字段

索引的數(shù)據(jù)結(jié)構(gòu)

-- 我們可以在創(chuàng)建上述索引的時候纸俭，為其指定索引類型皇耗，分兩類
hash類型的索引：查詢單條快，范圍查詢慢
btree類型的索引：b+樹揍很，層數(shù)越多郎楼，數(shù)據(jù)量指數(shù)級增長（我們就用它，因為innodb默認支持它）

-- 不同的存儲引擎支持的索引類型也不一樣
InnoDB 支持事務(wù)窒悔，支持行級別鎖定呜袁，支持 B-tree、Full-text 等索引简珠，不支持 Hash 索引阶界；
MyISAM 不支持事務(wù)，支持表級別鎖定聋庵，支持 B-tree膘融、Full-text 等索引，不支持 Hash 索引祭玉；
Memory 不支持事務(wù)氧映，支持表級別鎖定，支持 B-tree脱货、Hash 等索引岛都，不支持 Full-text 索引；
NDB 支持事務(wù)振峻，支持行級別鎖定疗绣，支持 Hash 索引，不支持 B-tree铺韧、Full-text 等索引多矮；
Archive 不支持事務(wù)，支持表級別鎖定，不支持 B-tree塔逃、Hash讯壶、Full-text 等索引；