MySQL索引，事務及存盤引擎

索引

一.索引的概念

1. 是一個排序的串列，存盤著索引值和這個值所對應的物理地址
2. 無需對整個表進行掃描，通過物理地址就可以找到所需資料
3. 是表中一列或者若干列值排序的方法
4. 需要額外的磁盤空間,伴隨著表直接存在

二.索引的作用

1.資料庫利用各種快速定位技術，能夠大大加快查詢速率
2.當表很大或查詢涉及到多個表時，可以成千上萬倍地提高查詢速度
3.可以降低資料庫的IO成本，并且還可以降低資料庫的排序成本， IO：輸入（寫入、更改資料），輸出（讀取資料）
4.通過創建唯一性索引保證資料表資料的唯一性
5.可以加快表與表之間的連接
6.在使用分組和排序時，可大大減少分組和排序時間

三.創建索引的原則

1、表的主鍵、外鍵必須有索引
2、資料量超過300行的表應該有索引
3、經常與其他表進行連接的表，在連接欄位上應該建立索引
4、唯一性太差的欄位不適合建立索引
5、更新太頻繁地欄位不適合創建索引
6、經常出現在 Where子句中的欄位，特別是大表的欄位，應該建立索引
7、索引應該建在選擇性高的欄位上
8、索引應該建在小欄位上，對于大的文本欄位甚至超長欄位，不要建索引

四.索引的分類

普通索引

最基本的索引，沒有唯一性之類的限制
創建方式：
直接創建

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

創建表結構時創建

mysql> create table nice(id int(3) primary key auto_increment,name varchar(10),money int(255),index index_id (id));
Query OK, 0 rows affected (0.00 sec)

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

修改表結構時創建

mysql> alter nice add index index_name(name);
show index from nice;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| nice  |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| nice  |          1 | index_id   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| nice  |          1 | index_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

主鍵索引

是一種特殊的唯一索引，指定為primary key，一個表只能有一個主鍵，與唯一索引的區別是不允許有空值（非空且唯一）
創建表結構時創建

create table xiu(id int(3) primary key auto_increment,name varchar(10),money int(255));

修改表結構時創建

mysql> create table vip(id int(2),name varchar(10));
Query OK, 0 rows affected (0.00 sec)

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

唯一性索引

與普通索引的區別是索引列的所有值只能出現一次，即必須唯一
直接創建

mysql> create unique index index_id on vip(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

創建表結構時創建

mysql> create table svip (id int(2) ,name varchar(10),money int(255),unique index index_id(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show index from svip;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| svip  |          0 | index_id |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

組合索引

可以是單列上創建的索引，也可以是在多列上創建的索引，遵循最左原則，從左往右依次執行，

mysql> create table vvip (id int(2) ,name varchar(10),money int(255), index index_id(id,name));
Query OK, 0 rows affected (0.01 sec)

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

全文索引

創建表時創建全文索引(必須為字符型欄位)

mysql> create table ssk (id int(2) ,name varchar(10),money int(255),fulltext(name));
Query OK, 0 rows affected (0.02 sec)

mysql> show index from ssk;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ssk   |          1 | name     |            1 | name        | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

修改表的方式添加全文索引

mysql> alter table vip add fulltext index_ab(name);
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 1

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

五.洗掉索引

方法一：
mysql> drop index index_ab on vip;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
方法二：
mysql> alter table svip drop index index_id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

事務

事務的概念

• 事務是一種機制、一個操作序列，包含了一組資料庫操作命令，并且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組資料庫命令要么都執行，要么都不執行
• 事務是一個不可分割的作業邏輯單元，在資料庫系統上執行并發操作時，事務是最小的控制單元
• 適用于多用戶同時操作的資料庫系統的場景，如銀行、保險公司及證券交易系統等等
• 通過事務的整體性以保證資料的一致性
• 如果事務成功了一部分，一部分未成功，則執行回滾，回到事務的起點，重新開始操作

事務的特點

• 原子性（Atomicity）
  事務是一個完整的操作，事務的各元素是不可分的
  事務中的所有元素必須作為一個整體提交或回滾
  如果事務中的任何元素失敗，則整個事務將失敗
• 一致性（Consistency）
  當事務完成時，資料必須處于一致狀態
  在事務開始前，資料庫中存盤的資料處于一致狀態
  在正在進行的事務中，資料可能處于不一致的狀態
  當事務成功完成時，資料必須再回到已知的一致狀態
• 隔離性（Isolation）
  對資料進行修改的所有并發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務
  修改資料的事務可在另一個使用相同資料的事務開始之前訪問這些資料，或者在另一個使用相同資料的事務結束之后訪問這些資料
• 持久性
  指不管系統是否發生故障，事務處理的結果都是永久的
  一旦事務被提交，事務的效果會被永久地保留在資料庫中

事務控制陳述句

MySQL事務默認是自動提交的，當SQL陳述句提交時事務便自動提交

BEGIN或START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT identifier
RELEASE SAVEPOINT identifier
ROLLBACK TO identifier
SET TRANSACTION

事務的控制方法

事務處理命令控制事務

 BEGIN：開始一個事務
 COMMIT：提交一個事務
 ROLLBACK：回滾一個事務

使用set命令進行控制

 set autocommit=0：禁止自動提交
 set autocommit=1：開啟自動提交

事務的操作

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

mysql> insert into vip (id,name,money) values(3,'xiye',169);
Query OK, 1 row affected (0.00 sec)

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

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

存盤引擎

存盤引擎概念

• MySQL中的資料用各種不同的技術存盤在檔案中，每一種技術都使用不同的存盤機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術以及配套的功能在 MySQL中稱為存盤引擎，
• 存盤引擎就是 MySQL將資料存盤在檔案系統中的存盤方式或者存盤格式
  MySQL常用存盤引擎：MyISAM InnoDB

存盤引擎的作用和作業原理

MySQL存盤引擎是 MySQL資料庫服務器中的組件，負責為資料庫執行實際的資料I/O操作

使用特殊存盤引擎的主要優點之一在于：
僅需提供特殊應用所需的特性；
資料庫中的系統開銷較小；
具有更有效和更高的資料庫性能，

MySQL系統中，存盤引擎處于檔案系統之上，在資料保存到資料檔案之前會傳輸到存盤引擎，之后按照各個存盤引擎的存盤格式進行存盤

MyISAM的介紹

MyISAM不支持事務，也不支持外鍵

訪問速度快

對事務完整性沒有要求

MyISAM在磁盤.上存盤成三個檔案
●.frm檔案存盤表定義
●資料檔案的擴展名為.MYD (MYData)
●索引檔案的擴展名是.MYI (MYIndex)

表級鎖定形式，資料在更新時鎖定整個表

資料庫在讀寫程序中相互阻塞 會在資料寫入的程序阻塞用戶資料的讀取 也會在資料讀取的程序中阻塞用戶的資料寫入

資料單獨寫入或讀取，速度程序較快且占用資源相對

MyIAM支持的存盤格式
●靜態表
●動態表
●壓縮表

適用場景：

公司業務不需要事務的支持
單方面讀取或寫入資料比較多的業務
MyISAM存盤引擎資料讀寫都比較頻繁場景不適合
使用讀寫并發訪問相對較低的業務
資料修改相對較少的業務
對資料業務-致性要求不是非常高的業務
服務器硬體資源相對比較差

innodb引擎

特點

• 支持4個事務隔離級別
• 行級（讀寫分離）鎖定，但是全表掃描仍然會是表級鎖定
• 讀寫阻塞與事務隔離級別相關
• 具有非常高效的快取特性：能快取索引，也能快取資料
• 表與主鍵以簇的方式存盤
• 支持外鍵約束，5.5以前不支持全文索引，5.5版本以后支持全文索引
• 對硬體資源要求還是比較高的場合
  適用場景：
• 業務需要事務的支持
• 行級鎖定對高并發有很好的適應能力，但需確保查詢是通過索引來完成
• 業務資料更新較為頻繁的場景如：論壇、微博等
• 業務資料一致性要求較高如：銀行業務
• 硬體設備記憶體較大（因為事務都先放記憶體），利用innodb較好的快取能力來提高記憶體利用率，減少磁盤IO的壓力

企業選擇存盤引擎依據

• 需要考慮每個存盤引擎提供的核心功能及應用場景
• 支持的欄位和資料型別
  所有引擎都支持通用的資料型別，但不是所有的引擎都支持其它的欄位型別,如二進制物件
• 鎖定型別:不同的存盤引擎支持下同級別的鎖定
  表鎖定
  行鎖定
• 索引的支持
  建立索引在搜索和恢復資料庫中的資料時能顯著提高性能
  不同的存盤弓|擎提供不同的制作索引|的技術
  有些存盤引擎根本不支持索引
• 事務處理的支持
  提高在向表中更新和插入資訊期間的可靠性
  可根據企業業務是否要支持事務選擇存盤引擎

修改存盤引擎

方法一：alter table修改
mysql> alter table 庫名 engine=MyISAM;
方法二：修改my.cnf組態檔，指定默認存盤引擎并重啟服務
vim my.cnf
default-storage-engine=InnoDB
方法三：create table創建表時指定存盤引擎
mysql> create table engine moon(id int) engine=MyISAM;