MySQL索引与事务、存储引擎MyISAM和InnoDB （理论实践篇）

索引的概念

数据库中的索引与书籍中的目录类似

在一本书中，无须阅读整本书，利用目录就可以快速查找所需信息
书中的目录是一个词语列表，其中注明了包含各个词的页码

数据库索引

在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据
数据库中的索引|是某个表中一-列或者若干列值的集合，以及物理标识这些值的数据页的逻辑指针清单

索引的的作用

设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率
特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成3 F倍
可以降低数据库的I0成本，并且索引还可以降低数据库的排序成本
通过创建唯一性索引保证数据表数据的唯- -性可以加快表与表之间的连接
在使用分组和排序时，可大大减少分组和排序时间

索引的分类

普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制

唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列表的所有值都只能出现一次，即必须唯一

主键

主键是一种唯一索引，但它必须指定为“PRIMARY KEY"

全文索引

MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT, 全文索引可以在VARCHAR或者TEXT类型的列上创建

单列索引与多列索引

索引可以是单列上创建的索引，也可以是在多列上创建的索引

创建索引的原则依据

表的主键、外键必须有索引
数据量超过300行的表应该有索引
经常与其他表进行连接的表，在连接字段上应该建立索引
唯一性太差的字段不适合建立索引
更新太频繁的字段不适合创建索引
经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
索引应该建立在选择性高的字段上
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

创建索引的方法

根据企业需求选择了合适的索引之后，可以使用CREATE INDEX创建索引
CREATE INDEX 加上各个索引的关键字便可以创建各个类型的索引

创建普通索引

CREATE INDEX <索引的名字> ON tablename（列的列表）；

普通索引实例

CREATE INDEX salary index ON IT salary(薪资);

创建唯一性索引

CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);

唯一性索引实例

CREATE UNIQUE INDEX salary_unique_index ON IT_salary(姓名);

创建主键索引

CREATE TABLE tablename ( [..], PRIMARY KEY (列的列表) );
ALTER TABLE tablename ADD PRIMARY KEY (列的列表); //添加表结构的方式创建主键索引

创建主键索引实例

ALTER TABLE IT_ salary ADD PRIMARY KEY (员工ID);

查看索引

SHOW INDEX FROM tablename;
SHOW KEYS FROM tablename;

查看索引实例

SHOW INDEX FROM IT_salary;
SHOW KEYS FROM IT_salary;

事务的概念

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即一组数据库命令要么都执行，要么都不执行
事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
通过事务的整体性以保证数据的一致性

事务的ACID特点

原子性(Atomicity)

事务是一个完整的操作，事务的各元素是不可分的(原子的)
事务中的所有元素必须作为一个整体提交或回滚
如果事务中的任何元素失败，则整个事务将失败

一致性(Consistency)

当事务完成时，数据必须处于一致状态:在事务开始之前，数据库中存储的数据处于一致状态;在正在进行的事务中，数据可能处于不一致的状态;当事务成功完成时，数据必须再次回到已知的一致状态

隔离性. (Isolation)

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

持久性(Durability)

事务持久性指不管系统是否发生故障，事务处理的结果都是永久的
一旦事务被提交，事务的效果会被永久地保留在数据库中

事务的操作

认情况下MySQL的事务是自动提交的，当sq|语句提交时事务便自动提交
手动对事务进行控制的方法
- 事务处理命令控制
- 使用set设置事务处理方式

事务处理命令控制事务

begin:开始一个事务
commit:提交一个事务
savepoint <标记名> 打标记/存档
rollback:回滚一个事务

使用set命令进行控制

et autocommit=0:禁止自动提交
set autocommit=1:开启自动提交

存储引擎概念介绍

MySQL中的数据用各种不同的技术存储在文件中，每种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL 中称为存储引擎
存储引擎就是MySQL将数据存储在文件系统中的存储方式或者存储格式
目前MySQL常用的两种存储引擎
- MyISAM（轻量级）
- InnoDB
MySQL存储引擎是MySQL数据库服务器中的组件，负责为数据库执行实际的数据1/0操作
使用特殊存储引擎的主要优点之一在于，仅需提供特殊应用所需的特性，数据库中的系统开销较小，具有更有效和更高的数据库性能
MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

MyISAM的介绍

MyISAM存储引擎是MySQL关系数据库系统5.5版本之前默认的存储引擎，前身是ISAM
ISAM是一个定义明确且历经时间考验的数据表格管理方法，在设计之时就考虑到数据库被查询的次数要远大于更新的次数
ISAM的特点
- ISAM执行读取操作的速度很快,
- 它不支持事务处理
- 而且不占用大量的内存和存储资源
- 不能够容错
数据存储位置：
- 表定义文件
- 表数据存储文件
- 表索引文件

MyISAM的特点

不支持事务
表级锁定形式，数据在更新时锁定整个表
数据库在读写过程中相互阻塞
- 会在数据写入的过程阻塞用户数据的读取
- 也会在数据读取的过程中阻塞用户的数据写入
可通过key_buffer_size来设置缓存索引，提高访问性能，减少磁盘IO的压力
- 但缓存只会缓存索引文件，不会缓存数据
采用MyISAM存储引擎数据单独写入或读取，速度过程较快且占用资源相对少
MyISAM存储引擎它不支持外键约束，只支持全文索引
每个MyISAM在磁盘.上存储成三个文件，每- -个文件的名字以表的名字开始，扩展名指出文件类型
MyISAM在磁盘上存储的文件
- frm文件存储表定义
- 数据文件的扩展名为.MYD (MYData)
- 索引文件的扩展名是.MYI (MYIndex)

MyISAM适用的生产场景举例

公司业务不需要事务的支持
一般单方面读取数据比较多的业务，或单方面写入数据比较多的业务
MyISAM存储引擎数据读写都比较频繁场景不适合使用读写并发访问相对较低的业务
数据修改相对较少的业务
对数据业务-致性要求不是非常高的业务服务器硬件资源相对比较差

InnoDB特点介绍

支持事务:支持4个事务隔离级别
行级锁定，但是全表扫描仍然会是表级锁定读写阻塞与事务隔离级别相关
具有非常高效的缓存特性:能缓存索引，也能缓存数据
表与主键以簇的方式存储3
支持分区、表空间，类似oracle数据库
支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引
对硬件资源要求还是比较高的场合

InnoDB适用生产场景分析

业务需要事务的支持
行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
业务数据更新较为频繁的场景，如:论坛,微博等
业务数据一致性要求较高，例如:银行业务
硬件设备内存较大，利用Innodb较好的缓存能力来提高内存利用率，减少磁盘I0的压力

企业选择存储引擎依据

需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景
支持的字段和数据类型
- 所有引|擎都支持通用的数据类型
- 但不是所有的引擎都支持其它的字段类型，如二进制对象
锁定类型:不同的存储引擎支持不同级别的锁定
- 表锁定
- 行锁定

配置存储引擎

在企业中选择好合适的存储引擎之后，就可以进行修改了
修改步骤
- 查看数据库可配置的存储引擎
- 查看表正在使用的存储引擎
- 配置存储引擎为所选择的类型
使用show engines查看系统支持的存储引擎
查看表使用的存储引擎
- 方法1: show table status from 库名 where name=‘表名;
- 方法2: show create table 表名;

修改存储引擎

方法1: alter table修改;
- alter table table_ name engine=引擎;
方法2:修改my.cnf,指定默认存储引擎并重启服务
- default-storage-engine=InnDB
方法3: create table创建表时指定存储引擎
- create table 表名 (字段) engine= 引擎
方法4: Mysql convert table format 转化存储引擎
- Mysql convert table format -user=root -password=密码
- sock=/tmp/mysql.sock-engine=引擎库名表名

实例

创建索引

mysql> create index index_age on info (age);   ##创建普通索引
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); ##创建唯一性索引
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)

全文索引、组合索引

mysql> select * from info;     ##查看表内容
 ---- ---------- ---------- ----- 
| 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创建全文索引
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表内容
 ---- -------- ------- ------- 
| 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);    ##创建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)

两个表，进行关联，多表查询

mysql> create table user(     ##创建user表
        -> id int(4) not null primary key auto_increment,   ##设置主键和自动增加
        -> name varchar(10) not null,
        -> score decimal not null,
        -> hobby int(2) not null default ‘1‘,  ##默认1
        -> index index_score (score));    ##设置索引score
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;  ##查看表结构
 ------- --------------- ------ ----- --------- ---------------- 
| 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); 
##向表中插入数据
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;    ##查看表内容
 ---- -------- ------- ------- 
| 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(     ##创建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;   ##查看表结构
 ---------- ------------- ------ ----- --------- ------- 
| 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,‘运动‘),(3,‘听歌‘);   ##插入表数据
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from hob;   ##查看表内容
 ---- ---------- 
| id | hob_name |
 ---- ---------- 
|  1 | 看书     |
|  2 | 运动     |
|  3 | 听歌     |
 ---- ---------- 
3 rows in set (0.00 sec)

mysql> select * from user inner join hob on user.hobby=hob.id;   ##关联user和hob两张表
 ---- -------- ------- ------- ---- ---------- 
| id | name   | score | hobby | id | hob_name |
 ---- -------- ------- ------- ---- ---------- 
|  1 | test01 |    88 |     1 |  1 | 看书     |
|  2 | stu01  |    99 |     2 |  2 | 运动     |
|  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;
##去除其他内容显示name和hob_name内容
 -------- ---------- 
| name   | hob_name |
 -------- ---------- 
| test01 | 看书     |
| stu01  | 运动     |
| 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; ##设置简易名称
 -------- ---------- 
| name   | hob_name |
 -------- ---------- 
| test01 | 看书     |
| stu01  | 运动     |
| 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
##创建视图
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_user;  ##查看视图
 -------- ---------- 
| name   | hob_name |
 -------- ---------- 
| test01 | 看书     |
| stu01  | 运动     |
| wangwu | 听歌     |
 -------- ---------- 
3 rows in set (0.00 sec)

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

mysql> select * from view_user;   ##查看视图，即视图就是表的一个链接
 -------- ---------- 
| name   | hob_name |
 -------- ---------- 
| stu01  | 运动     |
| test01 | 听歌     |
| wangwu | 听歌     |
 -------- ---------- 
3 rows in set (0.00 sec)

事务实例

开启事务，往表中插入数据

mysql> select * from info;   ##查看表内容数据
 ---- ---------- ---------- ----- 
| 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)；##插入数据

mysql> begin;   ##开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values (‘zhaoliu‘,‘hangzhou‘,31);  ##插入数据
Query OK, 1 row affected (0.00 sec)

mysql> savepoint a;   ##设置保存节点a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表数据
 ---- ---------- ---------- ----- 
| 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);  ##继续插入数据
Query OK, 1 row affected (0.00 sec)

mysql> savepoint b;   ##设置保存节点b
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values (‘heiba‘,‘hangzhou‘,32);      ##继续插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;  ##查看表内容
 ---- ---------- ---------- ----- 
| 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)

利用另一个终端查看是否成功插入

[[email protected] ~]# mysql -uroot -p  ##进入数据库
Enter password:     ##输入密码

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

mysql> use school;   ##使用数据库
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;   ##查看表内容，此时并没有提交
 ---- ---------- ---------- ----- 
| id | name     | address  | age |
 ---- ---------- ---------- ----- 
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
 ---- ---------- ---------- ----- 
3 rows in set (0.00 sec)

使用回滚，返回保存的节点

mysql> rollback to b;    ##利用回滚到保存节点b
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表数据
 ---- ---------- ---------- ----- 
| 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;  ##回滚到保存节点a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表数据
 ---- ---------- ---------- ----- 
| 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;     ##回滚到初始，退出事务状态
Query OK, 0 rows affected (0.00 sec)

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

使用commit提交事务

mysql> begin;    ##开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values (‘heiba‘,‘hangzhou‘,32);  ##插入数据
Query OK, 1 row affected (0.00 sec)

mysql> commit;   ##提交事务
Query OK, 0 rows affected (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> 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=0;   ##设置不自动提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> update info set address=‘beijing‘ where name=‘heiba‘;  ##修改表数据
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;   ##开启自动提交事务
Query OK, 0 rows affected (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    | beijing  |  32 |
 ---- ---------- ---------- ----- 
4 rows in set (0.00 sec)

存储引擎

查看系统默认存储引擎

mysql> show engines;   ##查看默认存储引擎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;   ##查看创建的表的存储引擎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

修改MySQL配置文件，设置默认的存储引擎

[[email protected] ~]# 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   ##添加默认存储引擎为Myisam

[[email protected] ~]# systemctl restart mysqld.service ##重启MySQL服务

进入数据库

[[email protected] ~]# mysql -uroot -p   ##进入数据库
Enter password:    ##输入密码

mysql> use school;    ##使用数据库
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 );    ##创建一个a表
Query OK, 0 rows affected (0.00 sec)

mysql> show create table a;   ##查看表默认的存储引擎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索引与事务、存储引擎MyISAM和InnoDB （理论 实践篇）