MySQL學習06（事務和索引）

事務

概述

什么是事務

• 事務就是將一組SQL陳述句放在同一批次內去執行
• 如果一個SQL陳述句出錯,則該批次內的所有SQL都將被取消執行
• MySQL事務處理只支持InnoDB和BDB資料表型別

事務的ACID原則

原子性(Atomic)
整個事務中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個環節，事務在執行程序中發生錯誤，會被回滾（ROLLBACK）到事務開始前的狀態，就像這個事務從來沒有執行過一樣，
一致性(Consist)
一個事務可以封裝狀態改變（除非它是一個只讀的），事務必須始終保持系統處于一致的狀態，不管在任何給定的時間并發事務有多少，也就是說：如果事務是并發多個，系統也必須如同串行事務一樣操作，其主要特征是保護性和不變性(Preserving an Invariant)，以轉賬案例為例，假設有五個賬戶，每個賬戶余額是100元，那么五個賬戶總額是500元，如果在這個5個賬戶之間同時發生多個轉賬，無論并發多少個，比如在A與B賬戶之間轉賬5元，在C與D賬戶之間轉賬10元，在B與E之間轉賬15元，五個賬戶總額也應該還是500元，這就是保護性和不變性，
隔離性(Isolated)
隔離狀態執行事務，使它們好像是系統在給定時間內執行的唯一操作，如果有兩個事務，運行在相同的時間內，執行相同的功能，事務的隔離性將確保每一事務在系統中認為只有該事務在使用系統，這種屬性有時稱為串行化，為了防止事務操作間的混淆，必須串行化或序列化請求，使得在同一時間僅有一個請求用于同一資料，
持久性(Durable)
在事務完成以后，該事務對資料庫所作的更改便持久的保存在資料庫之中，并不會被回滾，

事務實作

基本語法

-- 使用set陳述句來改變自動提交模式
SET autocommit = 0;  /*關閉*/
SET autocommit = 1;  /*開啟*/

-- 注意:
-- 1.MySQL中默認是自動提交
-- 2.使用事務時應先關閉自動提交

-- 開始一個事務,標記事務的起始點
START TRANSACTION 

-- 提交一個事務給資料庫
COMMIT

-- 將事務回滾,資料回到本次事務的初始狀態
ROLLBACK

-- 還原MySQL資料庫的自動提交
SET autocommit =1;

-- 保存點
SAVEPOINT 保存點名稱 -- 設定一個事務保存點
ROLLBACK TO SAVEPOINT 保存點名稱 -- 回滾到保存點
RELEASE SAVEPOINT 保存點名稱 -- 洗掉保存點

索引

索引分類

索引的作用

• 提高查詢速度
• 確保資料的唯一性
• 可以加速表和表之間的連接 , 實作表與表之間的參照完整性
• 使用分組和排序子句進行資料檢索時 , 可以顯著減少分組和排序的時間
• 全文檢索欄位進行搜索優化.

分類

• 主鍵索引 (Primary Key)
• 唯一索引 (Unique)
• 常規索引 (Index)
• 全文索引 (FullText)

主鍵索引

主鍵 : 某一個屬性組能唯一標識一條記錄

特點 :

• 最常見的索引型別
• 確保資料記錄的唯一性
• 確定特定資料記錄在資料庫中的位置

唯一索引

作用 : 避免同一個表中某資料列中的值重復

與主鍵索引的區別

• 主鍵索引只能有一個
• 唯一索引可能有多個

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

常規索引

作用 : 快速定位特定資料

注意 :

• index 和 key 關鍵字都可以設定常規索引
• 應加在查詢找條件的欄位
• 不宜添加太多常規索引,影響資料的插入,洗掉和修改操作

CREATE TABLE `result`(
  -- 省略一些代碼
 INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 創建表時添加
)

-- 創建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

作用 : 快速定位特定資料

注意 :

• 只能用于MyISAM型別的資料表
• 只能用于CHAR , VARCHAR , TEXT資料列型別
• 適合大型資料集

-- 方法一：創建表時
CREATE TABLE 表名 (
    欄位名1 資料型別 [完整性約束條件…],
    欄位名2 資料型別 [完整性約束條件…],
    [UNIQUE | FULLTEXT | SPATIAL ]  INDEX | KEY
    [索引名] (欄位名[(長度)] [ASC |DESC])
);

-- 方法二：CREATE在已存在的表上創建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (欄位名[(長度)] [ASC |DESC]) ;

-- 方法三：ALTER TABLE在已存在的表上創建索引
 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陳述句執行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通過 MATCH() 函式完成，
-- 搜索字串做為 against() 的引數被給定，搜索以忽略字母大小寫的方式執行，對于表中的每個
記錄行，MATCH() 回傳一個相關性值，即，在搜索字串與記錄行在 MATCH() 串列中指定的列的文
本之間的相似性尺度，
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

注意：MySQL 5.6 以前的版本，只有 MyISAM 存盤引擎支持全文索引；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存盤引擎均支持全文索引;只有欄位的資料型別為 char、varchar、text 及其系列才可以建全文索引，測驗或使用全文索引時，要先看一下自己的 MySQL 版本、存盤引擎和資料型別是否支持全文索引，

索引準則

• 索引不是越多越好
• 不要對經常變動的資料加索引
• 小資料量的表建議不要加索引
• 索引一般應加在查找條件的欄位

索引的資料結構

hash型別的索引：查詢單條快，范圍查詢慢
btree型別的索引：b+樹，層數越多，資料量指數級增長（我們就用它，因為innodb默認支持它）

不同的存盤引擎支持的索引型別也不一樣

• InnoDB 支持事務，支持行級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• MyISAM 不支持事務，支持表級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• Memory 不支持事務，支持表級別鎖定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
• NDB 支持事務，支持行級別鎖定，支持Hash 索引，不支持 B-tree、Full-text 等索引；
• Archive 不支持事務，支持表級別鎖定，不支持 B-tree、Hash、Full-text 等索引；

權限管理

用戶管理

基本命令

/* 用戶和權限管理 */ 
用戶資訊表：mysql.user

-- 重繪權限
FLUSH PRIVILEGES

-- 增加用戶 CREATE USER chen IDENTIFIED BY '123456'
CREATE USER 用戶名 IDENTIFIED BY [PASSWORD] 密碼(字串)
 - 必須擁有mysql資料庫的全域CREATE USER權限，或擁有INSERT權限，
 - 只能創建用戶，不能賦予權限，
 - 用戶名，注意引號：如 'user_name'@'192.168.1.1'
 - 密碼也需引號，純數字密碼也要加引號
 - 要在純文本中指定密碼，需忽略PASSWORD關鍵詞，要把密碼指定為由PASSWORD()函式回傳的
混編值，需包含關鍵字PASSWORD

-- 重命名用戶 RENAME USER chen TO chen1
RENAME USER old_user TO new_user

-- 設定密碼
SET PASSWORD = PASSWORD('密碼')   -- 為當前用戶設定密碼
SET PASSWORD FOR 用戶名 = PASSWORD('密碼')   -- 為指定用戶設定密碼

-- 洗掉用戶 DROP USER chen1
DROP USER 用戶名

-- 分配權限/添加用戶
GRANT 權限串列 ON 表名 TO 用戶名 [IDENTIFIED BY [PASSWORD] 'password']
 - all privileges 表示所有權限
 - *.* 表示所有庫的所有表
 - 庫名.表名 表示某庫下面的某表
 
-- 查看權限  SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用戶名

  -- 查看當前用戶權限
 SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR
CURRENT_USER();

-- 撤消權限
REVOKE 權限串列 ON 表名 FROM 用戶名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用戶名   -- 撤銷所有權限

權限解釋

-- 權限串列
ALL [PRIVILEGES]   -- 設定除GRANT OPTION之外的所有簡單權限
ALTER   -- 允許使用ALTER TABLE
ALTER ROUTINE   -- 更改或取消已存盤的子程式
CREATE   -- 允許使用CREATE TABLE
CREATE ROUTINE   -- 創建已存盤的子程式
CREATE TEMPORARY TABLES     -- 允許使用CREATE TEMPORARY TABLE
CREATE USER     -- 允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL
PRIVILEGES，
CREATE VIEW     -- 允許使用CREATE VIEW
DELETE   -- 允許使用DELETE
DROP   -- 允許使用DROP TABLE
EXECUTE     -- 允許用戶運行已存盤的子程式
FILE   -- 允許使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX   -- 允許使用CREATE INDEX和DROP INDEX
INSERT   -- 允許使用INSERT
LOCK TABLES     -- 允許對您擁有SELECT權限的表使用LOCK TABLES
PROCESS   -- 允許使用SHOW FULL PROCESSLIST
REFERENCES   -- 未被實施
RELOAD   -- 允許使用FLUSH
REPLICATION CLIENT   -- 允許用戶詢問從屬服務器或主服務器的地址
REPLICATION SLAVE   -- 用于復制型從屬服務器（從主服務器中讀取二進制日志事件）
SELECT   -- 允許使用SELECT
SHOW DATABASES   -- 顯示所有資料庫
SHOW VIEW   -- 允許使用SHOW CREATE VIEW
SHUTDOWN   -- 允許使用mysqladmin shutdown
SUPER   -- 允許使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL陳述句，
mysqladmin debug命令；允許您連接（一次），即使已達到max_connections，
UPDATE   -- 允許使用UPDATE
USAGE   -- “無權限”的同義詞
GRANT OPTION   -- 允許授予權限

-- 分析和存盤表的關鍵字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 檢查一個或多個表是否有錯誤
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理資料檔案的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

MySQL備份

資料庫備份必要性

• 保證重要資料不丟失
• 資料轉移

MySQL資料庫備份方法

• mysqldump備份工具
• 資料庫管理工具,如SQLyog
• 直接拷貝資料庫檔案和相關組態檔

mysqldump客戶端

作用 :

• 轉儲資料庫
• 搜集資料庫進行備份
• 將資料轉移到另一個SQL服務器,不一定是MySQL服務器

-- 匯出
1. 匯出一張表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用戶名 -p密碼 庫名 表名 > 檔案名(D:/a.sql)
2. 匯出多張表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用戶名 -p密碼 庫名 表1 表2 表3 > 檔案名(D:/a.sql)
3. 匯出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用戶名 -p密碼 庫名 > 檔案名(D:/a.sql)
4. 匯出一個庫 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用戶名 -p密碼 -B 庫名 > 檔案名(D:/a.sql)

-- 匯入
1. 在登錄mysql的情況下： -- source D:/a.sql
source 備份檔案
2. 在不登錄的情況下
mysql -u用戶名 -p密碼 庫名 < 備份檔案

標籤：其他

上一篇：牛年伊始，你的mysql密碼安全嗎？

下一篇：SQL基礎陳述句