MySQL的索引與事務(wù)、存儲引擎MyISA和InnoDB

MySQL索引與事務(wù)，視圖，存儲引擎MylSAM和lnnoDB

索引的概念

數(shù)據(jù)庫中的索引與書籍中的目錄類似：

1.一本書中，無須閱讀整本書,利用目錄就可以快速查找所需信息
2.書中的目錄是一個詞語列表，其中注明了包含各個詞的頁碼
站在用戶的角度思考問題，與客戶深入溝通，找到武義網(wǎng)站設(shè)計與武義網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、國際域名空間、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋武義地區(qū)。

數(shù)據(jù)庫索引：

1.在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對整個表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)
2.數(shù)據(jù)庫中的索弓|是某個表中一列或者若干列值的集合，以及物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單

索引的作用：

1.設(shè)置了合適的索引之后，數(shù)據(jù)庫利用各種快速的定位技術(shù)，能夠大大加快查詢速率
2.特別是當(dāng)表很大時，或者查詢涉及到多個表時，使用索引可使查詢加快成干倍
3.可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本
4.通過創(chuàng)建唯一性索引保證數(shù)據(jù)表數(shù)據(jù)的唯一性
5.可以加快表與表之間的連接
6.在使用分組和排序時，可大大減少分組和排序時間

索引的分類

普通索引：

是最基本的索引類型，而且它沒有唯一性之類的限制

唯一性索引：

這種索引和前面的“普通索引”基本相同，但有一個區(qū)別:索引列的所有值都只能出現(xiàn)一次，即必須唯一

主鍵：

主鍵是一種唯一性索引，但它必須指定為”PRIMARY KEY"

全文索引：

MySQL從3.23.23版開始支持全文索引和全文檢索。在MySQL中全文索引的索引類型為FULLTEXT,全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建

單列索引與多列索引：

索引可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引

創(chuàng)建索引的原則依據(jù)

1.表的主鍵、外鍵必須有索引
2.數(shù)據(jù)量超過300行的表應(yīng)該有索引
3.經(jīng)常與其他表進(jìn)行連接的表，在連接字段.上應(yīng)該建立索引
4.唯一性太差的字段不適合建立索引
5.更新太頻繁地字段不適合創(chuàng)建索引6.經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段,應(yīng)該建立索引
7.索引應(yīng)該建在選擇性高的字段上
8.索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引

事務(wù)的概念

1.事務(wù)是一種機(jī)制、一個操作序列，包含了一組數(shù)據(jù)庫操作命令，并且把所有的命令作為-一個整體一起向系統(tǒng)提交或撤銷操作請求，即這一組數(shù)據(jù)庫命令要么都執(zhí)行,要么都不執(zhí)行
2.事務(wù)是一個不可分割的工作邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時，事務(wù)是最小的控制單元
3.適用于多用戶同時操作的數(shù)據(jù)庫系統(tǒng)的場景，如銀行、保險公司及證券交易系統(tǒng)等等
4.通過事務(wù)的整體性以保證數(shù)據(jù)的一致性

事務(wù)的ACID特點(diǎn)

原子性(Atomicity)：

1.事務(wù)是一個完整的操作，事務(wù)的各元素是不可分的(原子的)
2.事務(wù)中的所有元素必須作為一個整體提交或回滾
3.如果事務(wù)中的任何元素失敗，則整個事務(wù)將失敗

一致性(Consistency)：

當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài):在事務(wù)開始之前，數(shù)據(jù)庫中存儲的數(shù)據(jù)處于一致狀態(tài);在正在進(jìn)行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài);當(dāng)事務(wù)成功完成時，數(shù)據(jù)必須再次回到已知的一致狀態(tài)

隔離性(Isolation)：

1.對數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)
2.修改數(shù)據(jù)的事務(wù)可以在另一個使用相同數(shù)據(jù)的事務(wù)開始之前訪問這些數(shù)據(jù)，或者在另一個使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問這些數(shù)據(jù)

持久性(Durability)：

1.事務(wù)持久性指不管系統(tǒng)是否發(fā)生故障，事務(wù)處理的結(jié)果都是永久的
2.一旦事務(wù)被提交，事務(wù)的效果會被永久地保留在數(shù)據(jù)庫中

事務(wù)的操作

1.默認(rèn)情況下MySQL的事務(wù)是自動提交的，當(dāng)sql語句提交時事務(wù)便自動提交

2.手動對事務(wù)進(jìn)行控制的方法：

①事務(wù)處理命令控制
②使用set設(shè)置事務(wù)處理方式

3.事務(wù)處理命令控制事務(wù)：

①begin：開始一個事務(wù)
②commit：提交一個事務(wù)
②rollback：回滾一個事務(wù)

4.使用set命令進(jìn)行控制：

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

存儲引擎概念介紹

1.MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件中,每一種技術(shù)都使用不同的存儲機(jī)制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL中稱為存儲引擎

2.存儲引擎就是MySQL將數(shù)據(jù)存儲在文件系統(tǒng)中的存儲方式或者存儲格式
3.目前MySQL常用的兩種存儲引擎：

①M(fèi)yISAM（適合讀）
②InnoDB（適合寫）

4.MySQL存儲引擎是MySQL數(shù)據(jù)庫服務(wù)器中的組件，負(fù)責(zé)為數(shù)據(jù)庫執(zhí)行實際的數(shù)據(jù)I/O操作

5.使用特殊存儲引擎的主要優(yōu)點(diǎn)之一在于，僅需提供特殊應(yīng)用所需的特性，數(shù)據(jù)庫中的系統(tǒng)開銷較小，具有更有效和更高的數(shù)據(jù)庫性能

6.MySQL系統(tǒng)中，存儲引擎處于文件系統(tǒng)之.上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鎯σ?，之后按照各個存儲引擎的存儲格式進(jìn)行存儲

MyISAM的介紹

1.MyISAM存儲引擎是MySQ關(guān)系數(shù)據(jù)庫系統(tǒng)5.5版本之前默認(rèn)的存儲引擎，前身是ISAM

2.ISAM是一個定義明確且歷經(jīng)時間考驗的數(shù)據(jù)表格管理方法，在設(shè)計之時就考慮到數(shù)據(jù)庫被查詢的次數(shù)要遠(yuǎn)大于更新的次數(shù)
3.SAM的特點(diǎn)：

①ISAM執(zhí)行讀取操作的速度很快,
②它不支持事務(wù)處理
③而且不占用大量的內(nèi)存和存儲資源
④不能夠容錯

MyISAM適用的生產(chǎn)場景舉例：

1.公司業(yè)務(wù)不需要事務(wù)的支持
2.一般單方面讀取數(shù)據(jù)比較多的業(yè)務(wù)，或單方面寫入數(shù)據(jù)比較多的業(yè)務(wù)
3.MyISAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合
4.使用讀寫并發(fā)訪問相對較低的業(yè)務(wù)
5.數(shù)據(jù)修改相對較少的業(yè)務(wù)
6.對數(shù)據(jù)業(yè)務(wù)一致性要求不是非常高的業(yè)務(wù)
7.服務(wù)器硬件資源相對比較差

InnoDB特點(diǎn)介紹

1.支持事務(wù):支持4個事務(wù)隔離級別
2.行級鎖定，但是全表掃描仍然會是表級鎖定
3.讀寫阻塞與事務(wù)隔離級別相關(guān)
4.具有非常高效的緩存特性:能緩存索引，也能緩存數(shù)據(jù)
5.表與主鍵以簇的方式存儲
6.支持分區(qū)、表空間，類似oracle數(shù)據(jù)庫
7.支持外鍵約束，5.5以前不支持全文索引，5.5版本以后支持全文索引
8.對硬件資源要求還是比較高的場合

InnoDB適用生產(chǎn)場景分析

1.業(yè)務(wù)需要事務(wù)的支持
2.行級鎖定對高并發(fā)有很好的適應(yīng)能力，但需確保查詢是通過索引來完成
3.業(yè)務(wù)數(shù)據(jù)更新較為頻繁的場景，如:論壇，微博等
4.業(yè)務(wù)數(shù)據(jù)一致性要求較高，例如:銀行業(yè)務(wù)
5.硬件設(shè)備內(nèi)存較大，利用Innodb較好的緩存能力來提高內(nèi)存利用率，減少磁盤IO的壓力

配置存儲引擎

1.在企業(yè)中選擇好合適的存儲引擎之后，就可以進(jìn)行修改了
2.修改步驟：

①查看數(shù)據(jù)庫可配置的存儲引擎
②查看表正在使用的存儲引擎
③配置存儲引擎為所選擇的類型

3.使用show engines查看系統(tǒng)支持的存儲引擎查看表使用的存儲引擎

方法1: show table status from庫名where name='表名;
方法2: show create table表名

索引實操

1，進(jìn)入數(shù)據(jù)庫，創(chuàng)建數(shù)據(jù)庫，創(chuàng)建表

[root@master2 ~]# mysql -uroot -p     ##進(jìn)入數(shù)據(jù)庫
Enter password:     ##輸入密碼

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database school;    ##創(chuàng)建數(shù)據(jù)庫school
Query OK, 1 row affected (0.00 sec)

mysql> use school;       ##使用數(shù)據(jù)庫
Database changed
mysql> create table info(        ##創(chuàng)建表
        -> id int(4) not null primary key auto_increment,   ##設(shè)置主鍵，自動增加
        -> name varchar(10) not null,       ##名字類型varchar不為空
        -> address varchar(50) default 'nanjing',    ##默認(rèn)南京
        -> age int(3) not null);
Query OK, 0 rows affected (0.02 sec)

2，向表中插入數(shù)據(jù)

mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22); 
##插入數(shù)據(jù)
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;   ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> desc info;               ##查看表結(jié)構(gòu)
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(4)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| address | varchar(50) | YES  |     | nanjing |                |
| age     | int(3)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3，使用create，alter，創(chuàng)建表直接定義三種方式創(chuàng)建索引（普通索引，唯一性索引）

mysql> create index index_age on info (age);   ##創(chuàng)建普通索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;   ##查看表中的索引
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> drop index index_age on info;   ##刪除表中的索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;        ##查看表中的索引         
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

mysql> create unique index unique_name on info (name); ##創(chuàng)建唯一性索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;  ##查看表中索引
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | unique_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> drop index unique_name on info;  ##刪除表中的索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;          ##查看表中的索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> alter table info add unique index index_name (name);   ##使用alter插入表索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;   ##查看表中的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

4，創(chuàng)建兩個表，進(jìn)行關(guān)聯(lián)，多表查詢

mysql> create table user(     ##創(chuàng)建user表
        -> id int(4) not null primary key auto_increment,   ##設(shè)置主鍵和自動增加
        -> name varchar(10) not null,
        -> score decimal not null,
        -> hobby int(2) not null default '1',  ##默認(rèn)1
        -> index index_score (score));    ##設(shè)置索引score
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;  ##查看表結(jié)構(gòu)
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(4)        | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)   | NO   |     | NULL    |                |
| score | decimal(10,0) | NO   | MUL | NULL    |                |
| hobby | int(2)        | NO   |     | 1       |                |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3); 
##向表中插入數(shù)據(jù)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;    ##查看表內(nèi)容
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  1 | test01 |    88 |     1 |
|  2 | stu01  |    99 |     2 |
|  3 | wangwu |    77 |     3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> create table hob(     ##創(chuàng)建hob表
        -> id int(2) not null primary key,
        -> hob_name varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc hob;   ##查看表結(jié)構(gòu)
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(2)      | NO   | PRI | NULL    |       |
| hob_name | varchar(10) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into hob (id,hob_name) values (1,'看書'),(2,'運(yùn)動'),(3,'聽歌');   ##插入表數(shù)據(jù)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from hob;   ##查看表內(nèi)容
+----+----------+
| id | hob_name |
+----+----------+
|  1 | 看書     |
|  2 | 運(yùn)動     |
|  3 | 聽歌     |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from user inner join hob on user.hobby=hob.id;   ##關(guān)聯(lián)user和hob兩張表
+----+--------+-------+-------+----+----------+
| id | name   | score | hobby | id | hob_name |
+----+--------+-------+-------+----+----------+
|  1 | test01 |    88 |     1 |  1 | 看書     |
|  2 | stu01  |    99 |     2 |  2 | 運(yùn)動     |
|  3 | wangwu |    77 |     3 |  3 | 聽歌     |
+----+--------+-------+-------+----+----------+
3 rows in set (0.00 sec)

mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id;
##去除其他內(nèi)容顯示name和hob_name內(nèi)容
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看書     |
| stu01  | 運(yùn)動     |
| wangwu | 聽歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; ##設(shè)置簡易名稱
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看書     |
| stu01  | 運(yùn)動     |
| wangwu | 聽歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby
##創(chuàng)建視圖
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_user;  ##查看視圖
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看書     |
| stu01  | 運(yùn)動     |
| wangwu | 聽歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> update user set hobby=3 where name='test01';  ##修改user表中內(nèi)容
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_user;   ##查看視圖，即視圖就是表的一個鏈接
+--------+----------+
| name   | hob_name |
+--------+----------+
| stu01  | 運(yùn)動     |
| test01 | 聽歌     |
| wangwu | 聽歌     |
+--------+----------+
3 rows in set (0.00 sec)

5，全文索引，組合索引

mysql> select * from info;     ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> show index from info;    ##查看表的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> create fulltext index full_addr on info (address);      ##以address創(chuàng)建全文索引
Query OK, 0 rows affected, 1 warning (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index from info;  ##查看表索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | full_addr  |            1 | address     | NULL      |           2 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from user;     ##查看user表內(nèi)容
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  1 | test01 |    88 |     3 |
|  2 | stu01  |    99 |     2 |
|  3 | wangwu |    77 |     3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> create index index_name_score on user (name,score);    ##創(chuàng)建name和score的組合索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user;   ##查看表索引
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY          |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_score      |            1 | score       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name_score |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name_score |            2 | score       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

事務(wù)實操

1，開啟事務(wù)，往表中插入數(shù)據(jù)

mysql> select * from info;           ##查看表內(nèi)容數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> insert into info (name,address,age) values ('wangwu','hangzhou',30)；        ##插入數(shù)據(jù)

mysql> begin;                             ##開啟事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31);  ##插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> savepoint a;                 ##設(shè)置保存節(jié)點(diǎn)a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;         ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> insert into info (name,address,age) values ('tianqi','hangzhou',32);  ##繼續(xù)插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> savepoint b;                  ##設(shè)置保存節(jié)點(diǎn)b
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('heiba','hangzhou',32);      ##繼續(xù)插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;           ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  32 |
|  6 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
6 rows in set (0.00 sec)

2，利用另一個終端查看是否成功插入

[root@master2 ~]# mysql -uroot -p         ##進(jìn)入數(shù)據(jù)庫
Enter password:                                      ##輸入密碼

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;   ##使用數(shù)據(jù)庫
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;   ##查看表內(nèi)容，此時并沒有提交
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

3，使用回滾，返回保存的節(jié)點(diǎn)

mysql> rollback to b;    ##利用回滾到保存節(jié)點(diǎn)b
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  32 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

mysql> rollback to a;  ##回滾到保存節(jié)點(diǎn)a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> rollback;     ##回滾到初始，退出事務(wù)狀態(tài)
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;  ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

4，使用commit提交事務(wù)

mysql> begin;    ##開啟事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('heiba','hangzhou',32);  ##插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> commit;   ##提交事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;  ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

5，使用另一個終端查看

mysql> select * from info;   ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

6，另一種事務(wù)的操作方式

mysql> set autocommit=0;   ##設(shè)置不自動提交事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> update info set address='beijing' where name='heiba';  ##修改表數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;   ##查看表信息
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | beijing  |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

##另一個終端查看
mysql> select * from info;  ##查看表信息，并沒有修改
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> set autocommit=1;   ##開啟自動提交事務(wù)
Query OK, 0 rows affected (0.00 sec)

##另一個終端查看
mysql> select * from info;   ##查看表數(shù)據(jù)，此時就已經(jīng)修改
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | beijing  |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

存儲引擎MyLSAM和InnoDB

1，查看系統(tǒng)默認(rèn)存儲引擎

mysql> show engines;   ##查看默認(rèn)存儲引擎innodb
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

mysql> show create table info;   ##查看創(chuàng)建的表的存儲引擎innodb

| info  | CREATE TABLE "info" (
    "id" int(4) NOT NULL AUTO_INCREMENT,
    "name" varchar(10) NOT NULL,
    "address" varchar(50) DEFAULT 'nanjing',
    "age" int(3) NOT NULL,
    PRIMARY KEY ("id"),
    UNIQUE KEY "index_name" ("name"),
    FULLTEXT KEY "full_addr" ("address")
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

2，修改MySQL配置文件，設(shè)置默認(rèn)的存儲引擎

[root@localhost ~]# vim /etc/my.cnf ##修改配置文件

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysql.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
default-storage-engine=Myisam   ##添加默認(rèn)存儲引擎為Myisam

[root@master2 ~]# systemctl restart mysqld.service ##重啟MySQL服務(wù)

3，進(jìn)入數(shù)據(jù)庫

[root@master2 ~]# mysql -uroot -p   ##進(jìn)入數(shù)據(jù)庫
Enter password:    ##輸入密碼

mysql> use school;    ##使用數(shù)據(jù)庫
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table a ( id int );    ##創(chuàng)建一個a表
Query OK, 0 rows affected (0.00 sec)

mysql> show create table a;   ##查看表默認(rèn)的存儲引擎Myisam
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
    "id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table a engine=innodb;  ##修改表的存儲引擎為innodb
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table a;  ##查看表的存儲引擎innodb
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
    "id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

分享文章：MySQL的索引與事務(wù)、存儲引擎MyISA和InnoDB
地址分享：http://aaarwkj.com/article2/gjceic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、品牌網(wǎng)站建設(shè)、用戶體驗、外貿(mào)建站、、搜索引擎優(yōu)化

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

欧美一级特黄大片做受成人-亚洲成人一区二区电影-激情熟女一区二区三区-日韩专区欧美专区国产专区

MySQL的索引與事務(wù)、存儲引擎MyISA和InnoDB

MySQL索引與事務(wù)，視圖，存儲引擎MylSAM和lnnoDB