MySQL數據庫的索引和事務

一、前言

本文將要詳細介紹MySQL索引與事務的概念及原理，并初步了解MySQL數據庫視圖概念，簡述數據庫的存儲過程。

二、索引

2.1索引的概念——什么是索引?

一般來說，一篇論文，或者說一本書，都有其目錄，而目錄一般是所有章節的概述，或者說是要點核心，而索引的概念其實也與之類似。

索引，顧名思義，就是一個方便用戶搜索所需資源的引導，只不過在數據庫中，索引一般被認為是一種特殊的文件，尤其在Linux系統中（"一切皆文件"）。從專業術語上解釋其含義就是“代表記錄的引用指針”。

2.2索引的作用

• 加快查詢速度，提高工作效率；
• 降低i/o成本，提供基礎排序；
• 加快多表之間的連接；
• 支持唯一性索引（下面會介紹什么是唯一性索引），保證數據表中數據的唯一性性；

2.3索引的分類

2.3.1普通索引

就是一般的索引，只是為了區別于其他特殊索引的一個統稱

2.3.2唯一性索引

與普通索引基本類同，區別在于，唯一性索引的列中的所有值都不相同，即“唯一”。

簡單舉例來說，學生數據表，年齡可以是普通索引，但不可以是唯一性索引，但是詳細住址可以是。

2.3.3主鍵索引

本質上也是一種唯一性索引，但必須指定為“primary key”，該索引要求主鍵中的每個值都唯一。上篇文章中，我們提及到了主鍵的概念，其特點也是“非空唯一”。

2.3.4全文索引

索引類型為FULLTEXT，全文索引可以在char、vachar或者text類型的列上創建。

2.3.5單列索引與多列索引

可以在單列或多列上創建索引。多列索引一般用于區分其中一列可能有相同值的行。

2.4創建索引的原則：建立在表上的（索引依賴于表）

索引可以提升數據庫的查詢速度，但并非所有的數據表都需要創建索引。因為索引本身也是需要占用系統資源的，或許一般情況下這個問題不會很突出，因為服務器的資源在一定程度上還是能夠正常支持的，但是如果索引文件過大，其大小可能達到操作系統允許的最大文件限制。

并且，如果說索引使用不當也會造成數據庫的負擔。因此，數據庫創建索引也是有其原則的。

2.4.1創建索引的原則依據

1. 表的主鍵、外鍵必須有索引；
2. 達到一定量的表（300行記錄）應該有索引；
3. 表之間的連接字段上應該建立索引；
4. 不能使用唯一性太差的字段作為索引；
5. 更新頻繁的字段也不適合作為索引；
6. 小字段適合建立索引，長的字段則不適合建立索引；

2.5索引的優缺點

優點：快速查詢所需資源

缺點：占用空間以及資源

2.6創建及查看索引

首先我們需要保證數據庫中有表，且表內有數據；

mysql> select * from fruit_info;                               
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
+----+-------+---------+
3 rows in set (0.00 sec)

2.6.1創建普通索引

命令格式：create index <索引名> on  數據表 （列名）；

實例：

mysql> create index id_index on fruit_info(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from fruit_info;  #查看索引語句也可以將index換成“keys”
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fruit_info |          1 | id_index |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

其中Non_unique為1，表示不是唯一性索引；Key_name 對應的是索引名稱，這里就是id_index；

2.6.2創建唯一性索引

命令格式：create unique index <索引名稱> on  數據表 （列名）；

實例：

mysql> create unique index type_index on fruit_info(newtype);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from fruit_info;
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fruit_info |          0 | type_index |            1 | newtype     | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| fruit_info |          1 | id_index   |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

這里的索引名稱為type_index，與之對應的Non_unique的值為0，表示其為唯一性索引。并且唯一性索引的值都不一樣。

那么我們考慮一個問題：數據庫中的表的字段是否既可以是普通索引，又可以是唯一性索引？

我們來實操驗證一下：

mysql> create unique index id_index_new on fruit_info(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from fruit_info;
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fruit_info |          0 | id_index_new |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| fruit_info |          0 | type_index   |            1 | newtype     | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| fruit_info |          1 | id_index     |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

事實證明是可以的，但是我們需要知道唯一性索引與普通索引的區別就在于“唯一性”上。如果創建了唯一性索引，那么在插入數據記錄的時候就需要注意字段匹配時的唯一性。

2.6.3創建主鍵索引

命令格式:（1）創建表的時候創建主鍵：create table 表名 （[ ... ]，primary key(列的列表)）；

（2）修改表結構加入主鍵：alter table 表名 add primary key；

實例：

mysql> create table student (id int not null,sex char(2),age int not null,hobby varchar(20),primary key(id,hobby));
Query OK, 0 rows affected (0.01 sec)

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | PRIMARY  |            2 | hobby       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

一個表里面只能有一個主鍵，但一個主鍵可以由多個字段組成。

mysql> alter table fruit_info add primary key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from fruit_info;
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fruit_info |          0 | PRIMARY      |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| fruit_info |          0 | id_index_new |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| fruit_info |          0 | type_index   |            1 | newtype     | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| fruit_info |          1 | id_index     |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

主鍵索引是設置主鍵后自動創建的，無需指定名稱，系統自動生成名字“primary”。主鍵索引與唯一性索引區別就在于唯一性索引可以為null，而主鍵索引為not null，所以可以簡單用公式理解：primary index = not null + unique index；

2.6.4全文索引

全文索引可以建立的字段類型在前面已經提及了，命令格式如下：

1、create table 表名 （列名 text，FULLTEXT（列名））engine=MyISAM；

2、alter table 表名 add FULLTEXT（列名）；

實例：

查看數據庫的存儲引擎類型：（存儲引擎我們下一篇文章會講解）

mysql> show table status from fruit where name='student'\G
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-01-06 19:12:24
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> alter table student add fulltext(hobby);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> mysql> show keys from student;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | PRIMARY     |            2 | hobby       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | hobby       |            1 | hobby       | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

2.6.5多列索引

在創建索引時指定多列即可

命令格式：create index 索引名 on 表名（字段1，字段2）；

實例：

mysql> create index mo_index on student(id,hobby)
    -> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | PRIMARY     |            2 | hobby       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | mo_index    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | mo_index    |            2 | hobby       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | hobby       |            1 | hobby       | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

2.7刪除索引

命令格式：

2.7.1直接刪除索引

格式：drop index 索引名 on 表名；

實例：

mysql> drop index mo_index on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | PRIMARY     |            2 | hobby       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | hobby       |            1 | hobby       | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

2.7.2修改表是刪除索引

格式：alter table 表名 drop index 索引名；

實例：

mysql> alter table student drop index hobby;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | PRIMARY  |            2 | hobby       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2.7.3刪除主鍵索引

格式：alter table 表名 drop primary key；

實例：

mysql> alter table student drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
Empty set (0.00 sec)

當然如果在修改表結構的時候，刪除了包含索引的列，那么對應的索引也會被刪除。

三、事務

3.1事務的概念

將多個命令作為整體執行，要么都成功要么都不執行，如銀行轉賬；

如果執行事務的時候，前面的一部分成功了，而最后的一部分失敗了，那么就會實行“回滾“機制，將執行操作回到事務的起點位置，數據沒有發生結果性變化。

3.2事務的ACID特點（4個）

3.2.1原子性

事務是不可分割的工作邏輯單元，事務可以包含多個sql語句，但整個事務是一個完整的操作，不可分割；（比如轉賬，轉賬過程中一但出現error，那么就會回滾到起初狀態，二者資產不會有任何變化）

3.2.2一致性

事務執行前和執行后數據必須處于一致狀態，但是執行過程中是動態變化的；（比如轉賬，轉賬前和轉賬后雙方資產的總和是不變的（不考慮手續費等其他費用的情況））

3.2.3隔離性

并發事務是彼此隔離的，事務之間必須是獨立（比如打電話，甲和乙打電話不會影響丙和丁打電話）

3.2.4持久性

事務結果都是永久的并且是不可逆的（比如轉賬的結果，如果發生糾紛再次處理就是另一個事務了。）

3.3事務的操作

3.3.1自動提交

默認的情況下是自動提交的，就是輸入了sql語句就自動提交執行該命令，但一般來說這是不安全的；

3.3.2手動提交

在生產環境中，該模式使用比較多，這是因為手動提交可以通過緩存，內存中的數據顯示的結果查看是否出錯，錯了即實行回滾操作（rollback）（一般會設置回滾點）。

3.3.3使用事務命令控制事務（4個）

begin：表示開始一個事務，后面接多個sql語句；0

commit：表示提交一個事務，對應前面的begin

rollback：表示回滾一個事務，在begin和rollback之間，錯誤的時候可以回滾。

savepoint：表示設置回滾點配合rollback命令使用。

實例：

首先我們有一個如下的數據表：

mysql> desc fruit_info;   #表結構
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(4)       | NO   | PRI | NULL    |       |
| price   | decimal(3,2) | NO   |     | NULL    |       |
| newtype | varchar(6)   | YES  | UNI | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from fruit_info; #表數據
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql> begin; #開始一個事務的標志
Query OK, 0 rows affected (0.00 sec)

mysql> insert into fruit_info values(5,4,'pear');  #插入一個記錄
Query OK, 1 row affected (0.00 sec)

mysql> select * from fruit_info; #此時只是放入緩存中使用rollback可以回到最初狀態；
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
|  5 |  4.00 | pear    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> rollback;             
Query OK, 0 rows affected (0.00 sec)

mysql> select * from fruit_info;
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql> insert into fruit_info values(5,4,'pear');
Query OK, 1 row affected (0.00 sec)

mysql> commit;  #提交之后無法使用rollback回到最初狀態；
Query OK, 0 rows affected (0.00 sec)

mysql> select * from fruit_info;
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
|  5 |  4.00 | pear    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from fruit_info;
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
|  5 |  4.00 | pear    |
+----+-------+---------+
5 rows in set (0.01 sec)

設置斷點——“回滾點”

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into fruit_info values(5,4,'pear');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
mysql> insert into fruit_info values(6,4,'grape');
Query OK, 1 row affected (0.00 sec)

mysql> savepoint s1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into fruit_info values(7,4,'cherry');
Query OK, 1 row affected (0.00 sec)

mysql> savepoint s2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from fruit_info;
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
|  5 |  4.00 | pear    |
|  6 |  4.00 | grape   |
|  7 |  4.00 | cherry  |
+----+-------+---------+
7 rows in set (0.00 sec)

mysql> rollback to savepoint s2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from fruit_info;
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
|  5 |  4.00 | pear    |
|  6 |  4.00 | grape   |
|  7 |  4.00 | cherry  |
+----+-------+---------+
7 rows in set (0.00 sec)

mysql> rollback to savepoint s1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from fruit_info;
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
|  5 |  4.00 | pear    |
|  6 |  4.00 | grape   |
+----+-------+---------+
6 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from fruit_info;
+----+-------+---------+
| id | price | newtype |
+----+-------+---------+
|  1 |  2.50 | banana  |
|  2 |  5.50 | apple   |
|  3 |  6.00 | peach   |
|  4 |  6.00 | orange  |
|  5 |  4.00 | pear    |
+----+-------+---------+
5 rows in set (0.00 sec)

mysql> rollback to savepoint s1;
ERROR 1305 (42000): SAVEPOINT s1 does not exist

根據以上的結果我們可以得到如下結論：

使用事務命令控制事務是可以實現回滾機制的；

在使用savepoint結合rollback命令時，回滾的位置是根據你執行的命令的最終位置；

如果直接使用rollback命令是直接回到最初狀態，且無法回到其他回滾節點。

3.3.4set命令

set  autocommit=0：禁止自動提交 ——就相當于begin；

set autocommit-=1：開啟自動提交

四、總結

本文主要是對MySQL數據庫中的索引和事務的概念進行詳細的介紹，這里的概念和原理以及對應的使用場景需要我們結合實例進行詳細理解。索引的分類以及主鍵索引與唯一性索引的區別，事務的4大特點和事務的回滾機制。