MySQL索引與事務

標籤：constrain   元素   行記錄   rpo   建立   機制   nal   區分   資料庫操作   

MySQL索引與事務技能目標

• 理解索引的概念並掌握索引的建立
• 理解事務的蓋簾並掌握事務的操作

索引的概念及分類1：索引的概念

• 索引是一種特殊的檔案，包含著對資料表中所有記錄的引用指標

2：索引的作用

• 建立索引的目的是加快對錶中記錄的尋找或順序

(1) 設定了適合的索引之後，資料庫利用各種快速的定位技術，可以大大加快查詢速度，這也是建立索引的主要元嬰(2) 當表很大時，或者涉及多個表時，使用索引可使查詢速度加快成千倍(3) 可以降低資料庫中IO成本並且索引還可以降低資料庫的排序成本(4) 通過建立唯一索引，可以保證資料庫表中每一行資料的唯一性(5) 可以加快表與表中的串連(6) 使用分組和排序子句進行資料查詢時，可以顯著減少查詢中分組和排序的時間3：索引的分類MySQL的索引分為以下幾類：

• 普通索引，這是最基本的索引類型，而且沒有唯一性的限制
• 唯一性索引，這與前面的普通索引基本相同唯一不同在於索引的列所有值只能出現一次，不允許有空值
• 主鍵索引，主鍵索引是一種唯一性的索引，但它必須指定“PRIMARY KEY”。
• 全文索引，索引類型為FULLTEXT，全文索引可以在CHAR VARCHAR或者TEXT類型的列上建立
• 單列多列索引，索引是單列上建立的索引，也可以是在多列上建立的索引

索引詳細資料

顯示參數	描述
Table	表的名稱
Non_unique	索引值的唯一性，0表示唯一性，1表示非唯一
Key_name	索引的名稱
Seq_in_index	索引中的序號從1開始
Column_name	列名稱

實驗1：建立索引的方法建立普通索引的命令格式ceate index<索引的名字>no tablename(列的列表)

• 準備了一個資料庫（學員資訊）裡面有裡面建立了（班級學員資訊表）

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || 學員資訊           || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)mysql> use 學院資訊;mysql> create table 班級學員資訊(學號 int,名字 varchar(10),分數 int,愛好 varchar(10)); mysql> select * from 班級學員資訊;+--------+--------+--------+--------+| 學號   | 名字   | 分數   | 愛好   |+--------+--------+--------+--------+|      1 | 張三   |     88 | 1      ||      2 | 李四   |     88 | 2      ||      3 | 王五   |     88 | 1      |+--------+--------+--------+--------+3 rows in set (0.00 sec)mysql> create index name_index on 班級學員資訊(學號); #建立普通索引執行個體Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from 班級學員資訊; #查看索引資訊+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table              | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| 班級學員資訊       |          1 | name_index |            1 | 學號        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)

• key_name對應的是索引名字(名字為name_index),Non_unique對應值是1表示不是唯一索引

建立唯一索引的命令格式create unique index<索引的名字>on tablename(列的列表)

• 該索引比普通索引多了一個unique關鍵字，下面對名字建立唯一性索引

mysql> create unique index name_unique_index on 班級學員資訊(名字);Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from 班級學員資訊;+--------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table              | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| 班級學員資訊       |          0 | name_unique_index |            1 | 名字        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |+--------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)

• 名為name_unique_index的索引已建立，他的Non_unique為0，表示唯一性索引

建立主鍵索引有兩種方式1)：在建立表的同時建立主鍵，2)：在已經建立的表中沒有指定主鍵，然後修改表加入主鍵，主鍵索引會自動建立建立主鍵索引的命令格式1)：建立表格的同時指定主鍵create table tablename([...],primary key(列的列表));

mysql> show tables;                    #在學員資訊裡面有三個表現在我重新建立一個新表在建立新編的同時定義主鍵+------------------------+| Tables_in_學員資訊     |+------------------------+| 學生愛好               || 愛好                   || 班級學員資訊           |+------------------------+3 rows in set (0.00 sec)mysql> create table 學生興趣 (編號 int(4)primary key, 愛好 varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> desc 學生興趣;               #建立了主鍵的表看一下兩者區別建立主鍵的表Key裡面多一個PRI+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| 編號   | int(4)      | NO   | PRI | NULL    |       || 愛好   | varchar(10) | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc 愛好;                #原有的表沒有建立主鍵的表沒有建立主鍵的表Key裡面是空的+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| 編號   | int(4)      | YES  |     | NULL    |       || 愛好   | varchar(10) | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

2)：建立表格之後沒有建立主鍵修改該表格後加入主鍵格式主鍵索引會自動建立：alter table tablename add primary key(列的列表);

mysql> create table 學生愛好 (編號 int(4), 愛好 varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> desc 學生愛好;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| 編號   | int(4)      | YES  |     | NULL    |       || 愛好   | varchar(10) | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table 學生愛好 add constraint pk_id primary key 學生愛好 (編號); Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc 學生愛好;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| 編號   | int(4)      | NO   | PRI | NULL    |       || 愛好   | varchar(10) | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

建立多列索引只需要在建立索引的時候制定多列即可命令格式為create index duolie_index on 班級學員資訊(學號，名字);

mysql> create index duolie_index on 班級學員資訊(學號,名字);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from 班級學員資訊;+--------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table              | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| 班級學員資訊       |          1 | duolie_index |            1 | 學號        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               || 班級學員資訊       |          1 | duolie_index |            2 | 名字        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |+--------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)mysql> alter table 班級學員資訊 drop column 學號; #刪除多列索引中其中一個索引Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0

不需要索引時刪除命令（大寫為固定格式再mysql中不區分大小寫）

• DROP INDEX index_name ON table_name; #大寫為固定格式index_name為索引名稱table_name為刪除哪張表格中的索引
• ALTER TABLE table_name DROP INDEX index_name #刪除那張表格的哪一個索引
• ALTER TABLE table_name DORP PRIMARY KEY; #刪除主鍵

查看索引放法

• SHOW KEY FROM table_name
• SHOW INDEX FROM table_name #在後面加上\G豎向顯示索引資訊即為：SHOW INDEX FROM table_name\G

主鍵和外鍵(1) 主鍵

• 資料表中每行記錄都必須是唯一的，而不允許出現完全相同的記錄，通過定義主鍵，可以保證記錄（實體）的唯一性
• 鍵，即是關鍵字，他是關聯式模式中非常重要的一個元素
• 如果一個屬效能有唯一的標識，沒有多餘的屬性，那麼這個屬性集稱為候選索引鍵
• 從表中的主鍵是主表中的外鍵且數值唯一不可為空

(2)外鍵

• 一個關聯式資料庫通常包含多個表，通過外鍵可以使這些表關起來
• 主表中的外鍵是從表中的主鍵且數值唯一不可為空

實驗下面有兩張表上面一個是主表編號是主鍵愛好是外鍵下面一個是從表編號是主鍵

mysql> select * from 六年級二班學生資料表;+--------+--------+--------+--------+| 編號   | 姓名   | 學分   | 愛好   |+--------+--------+--------+--------+|      1 | 張三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 趙六   |     99 |      2 |+--------+--------+--------+--------+4 rows in set (0.00 sec)mysql> select * from 六年級二班興趣愛好表;+--------+-----------+| 編號   | 愛好      |+--------+-----------+|      1 | 看書      ||      2 | 玩遊戲    ||      3 | 遊玩      ||      4 | 打球      |+--------+-----------+4 rows in set (0.00 sec)

把上面兩張表關聯查詢編號、姓名、學分、愛好，用一張表的形式去顯示實際這張表不存在的

mysql> select i.編號,i.姓名,i.學分,h.愛好 from 六年級二班學生資料表 i join 六年級二班興趣愛好表 h on i.愛好=+--------+--------+--------+-----------+| 編號   | 姓名   | 學分   | 愛好      |+--------+--------+--------+-----------+|      1 | 張三   |     80 | 看書      ||      2 | 李四   |     81 | 玩遊戲    ||      4 | 趙六   |     99 | 玩遊戲    ||      3 | 王五   |     81 | 遊玩      |+--------+--------+--------+-----------+4 rows in set (0.00 sec)

MySQL 資料庫事務2：事物的概念及特點事務是一種機制，一個操作序列，包含了一組資料庫操作命令，並且把所有命令作為一個整體一起向系統提交或撤銷操作請求，即這一組命令要麼執行要麼撤銷。事務是一個不可分割的邏輯單元，在資料庫系統上執行並行作業時事務是做小的控制單元。事務的ACID特性

• 原子性：事務是一個完整的操作，個元素是不可分的，即原子的
• 一致性：當事務完成時事務必須處於同一個狀態
• 隔離性：對事物進行修改的所有並發事務是彼此隔離的，每個事物都是單獨存在的
• 持久性：指不管系統是否發生故障，交易處理的結果都是永久的

使用事務命令控制事務

• begin 表示開始一個事務，後面會有多條資料動作陳述式執行
• commit 表示提交事務，對應前面begin操作
• rollback 表示復原一個事務，再begin和commit之間如果某一個資料庫動作陳述式出現錯誤，執行rollback復原資料回到begin之前的狀態

實驗用begin在學院班級息裡面六年級二班學生資料表中再添加一個人員

mysql> create database 資料;Query OK, 1 row affected (0.00 sec)mysql> use 資料;Database changedmysql> use 學院班級資訊;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>insert    -> into 六年級二班學生資料表 values (5,‘田七‘,2,1); #在表中插入這段資訊Query OK, 1 row affected (0.00 sec)mysql> select    -> * from 六年級二班學生資料表;+--------+--------+--------+--------+| 編號   | 姓名   | 學分   | 愛好   |+--------+--------+--------+--------+|      1 | 張三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 趙六   |     99 |      2 ||      5 | 田七   |      2 |      1 |+--------+--------+--------+--------+5 rows in set (0.00 sec)  #查看錶中資料有田七這個人員資訊#我們另開一個終端登入資料庫再查看錶的資訊mysql> select * from 六年級二班學生資料表;+--------+--------+--------+--------+| 編號   | 姓名   | 學分   | 愛好   |+--------+--------+--------+--------+|      1 | 張三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 趙六   |     99 |      2 |+--------+--------+--------+--------+4 rows in set (0.00 sec)#從另一個終端的資料庫看不到田七資料因為begin只是開始一個事務並沒有提交#想要提交事務用commit提交 如果怕後面出錯可以在這邊進行一個留檔mysql> savepoint s1;Query OK, 0 rows affected (0.00 sec)mysql> insert into 六年級二班學生資料表 values (6,‘黑八‘,77,4);Query OK, 1 row affected (0.00 sec)mysql> savepoint s2;Query OK, 0 rows affected (0.00 sec)mysql> select * from 六年級二班學生資料表;+--------+--------+--------+--------+| 編號   | 姓名   | 學分   | 愛好   |+--------+--------+--------+--------+|      1 | 張三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 趙六   |     99 |      2 ||      5 | 田七   |      2 |      1 ||      6 | 黑八   |     77 |      4 |+--------+--------+--------+--------+6 rows in set (0.00 sec)#如果再第二個留檔中出錯了用rollback to savepoint s1;mysql> rollback to savepoint s1;Query OK, 0 rows affected (0.00 sec)mysql> select * from 六年級二班學生資料表;+--------+--------+--------+--------+| 編號   | 姓名   | 學分   | 愛好   |+--------+--------+--------+--------+|      1 | 張三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 趙六   |     99 |      2 ||      5 | 田七   |      2 |      1 |+--------+--------+--------+--------+5 rows in set (0.00 sec)#此時留檔二中的黑八就叫已經不存在了，註：復原到上一個s1的留檔的位置那麼從s1下面所有操作的事務全都不存在了

用set sutocommit=0關閉自動認可

mysql> set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> insert into 六年級二班學生資料表 values (6,‘黑八‘,77,4);Query OK, 1 row affected (0.00 sec)mysql> select * from 六年級二班學生資料表;+--------+--------+--------+--------+| 編號   | 姓名   | 學分   | 愛好   |+--------+--------+--------+--------+|      1 | 張三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 趙六   |     99 |      2 ||      6 | 黑八   |     77 |      4 |+--------+--------+--------+--------+5 rows in set (0.00 sec)#上表關閉了自動認可功能然後操作事務不會自動認可相當於執行了begin同樣在另一個終端登入的時候不會顯示出來#如果set autocommit=1開啟自動認可那麼以上進行中的事務自動認可相當於執行了begin語句中的commit

本次實驗總結

• 資料庫索引分為：普通索引，唯一索引，主鍵索引，全文索引，多列索引
• 資料庫索引可以協助快速查詢表中資料，但並不是任何欄位都是和索引（資料至少不少於1000行才能看出效果）
• 資料庫事物的ACID特性
• MySQL事務命令有begin、rollback、commit、savrpoint。

MySQL索引與事務