数据库进阶，数据库的索引，事物与存储引擎-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_41257472/article/details/118574160

数据库的索引

索引的概念

数据库索引是一个排序的列表，储存着索引值和其对应的物理地址
搜索时无需对整个表进行扫描，通过物理地址就可以找到所需数据
是表中一列或若干列值排序的方法
他需要额外的磁盘空间

索引的作用

通过设置索引，能够大大较快查询速率
可以降低数据库的读写成本
可以通过创建唯一性索引保证数据的唯一性
可以加快表与表之间的连接
在分组和排序时，可以大大减少分组和排序时间

索引适合在什么情况下？

记录超过300行以上可以设置索引
经常与其他表进行连接的表，可以在连接字段上建立索引
主键外键可以设置索引，具有唯一性的字段适合建立索引
经常出现在where筛选中的字段，可以建立索引
经常变更的字段不适合做索引
索引应该建立在小字段上，超长的字段不适合做索引

索引的分类和创建

先创建一个表

mysql> select * from member;
+------+----------+--------+-------+---------+--------+
| id   | name     | cardid | phone | address | remark |
+------+----------+--------+-------+---------+--------+
|    1 | zhansgan | 123    | 1111  | 南京    | nan    |
|    3 | lisi     | 124    | 3333  | 北京    | nan    |
|    2 | wangwu   | 125    | 2222  | 北京    | nan    |
|    4 | zhaoliu  | 126    | 4444  | 上海    | nv     |
|    5 | zhanglei | 127    | 5555  | 杭州    | nv     |
+------+----------+--------+-------+---------+--------+
5 rows in set (0.00 sec)

普通索引

直接创建索引

mysql> create index phone_index on member (phone);	  	//create index 索引名 on 表名 （列名）;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table member;	//查看表结构或索引信息
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  KEY "phone_index" ("phone")	//这是我们设置的普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc member;		//查看字段属性
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| cardid  | varchar(18) | YES  |     | NULL    |       |
| phone   | varchar(11) | YES  | MUL | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改表结构方式创建索引

mysql> alter table member add index id_index (id);	//alter table  表名 add index 索引名 （列名）;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table member;
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  KEY "phone_index" ("phone"),
  KEY "id_index" ("id")		//这是我们用修改表结构方式创建的索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  | MUL | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| cardid  | varchar(18) | YES  |     | NULL    |       |
| phone   | varchar(11) | YES  | MUL | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

在创建表的时候直接指定索引

mysql> create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));	//在最后指定id为索引

mysql> show create table test;
| test  | CREATE TABLE "test" (
  "id" int(4) NOT NULL,
  "name" varchar(10) NOT NULL,
  "cardid" varchar(18) NOT NULL,
  KEY "id_index" ("id")		//能看到我们设置的普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(4)      | NO   | MUL | NULL    |       |
| name   | varchar(10) | NO   |     | NULL    |       |
| cardid | varchar(18) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

唯一性索引

唯一性索引普通索引的区别是唯一性索引的每个值都是唯一，如果列中有相同的记录就不能设置成索引，唯一性索引允许有空值，添加唯一键将自动创建唯一性索引
直接创建唯一性索引

mysql> select * from member;
+------+----------+--------+-------+---------+--------+
| id   | name     | cardid | phone | address | remark |
+------+----------+--------+-------+---------+--------+
|    1 | zhansgan | 123    | 1111  | 南京    | nan    |
|    3 | lisi     | 124    | 3333  | 北京    | nan    |
|    2 | wangwu   | 125    | 2222  | 北京    | nan    |
|    4 | zhaoliu  | 126    | 4444  | 上海    | nv     |
|    5 | zhanglei | 127    | 5555  | 杭州    | nv     |
+------+----------+--------+-------+---------+--------+
5 rows in set (0.00 sec)

mysql> create unique index address_index on member (address);
ERROR 1062 (23000): Duplicate entry '北京' for key 'address_index'		//因为address中有记录重复，所以不能设置成唯一性索引

mysql> create unique index name_index on member (name);	//create unique index 索引名 on 表名 （列名）；
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table member;
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  UNIQUE KEY "name_index" ("name")	//这个就是我们设置的唯一性索引，跟设置唯一键是同一个字段，只是多了一个索引名
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  | UNI | NULL    |       |
| cardid  | varchar(18) | YES  |     | NULL    |       |
| phone   | varchar(11) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改表结构方式创建唯一索引

mysql> alter table member add unique cardid_index (cardid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table member;
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  UNIQUE KEY "name_index" ("name"),
  UNIQUE KEY "cardid_index" ("cardid")		//这是修改表结构创建的唯一性索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  | UNI | NULL    |       |
| cardid  | varchar(18) | YES  | UNI | NULL    |       |
| phone   | varchar(11) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

创建表的时候指定唯一性索引

mysql> create table test2 (id int,name varchar(20),unique id_index (id));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2;
| test2 | CREATE TABLE "test2" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  UNIQUE KEY "id_index" ("id")	//这是指定出案件的唯一性索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键索引

主键索引是一种特殊的唯一索引，必须指定为"primary key"
一个表只能有一个主键，不允许空值，添加主键时自动创建主键索引
创建表的时候指定

mysql> create table test1 (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.36 sec)

mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table test1;
| test1 | CREATE TABLE "test1" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  PRIMARY KEY ("id") 	//这就是我们创建的主键索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

修改表结构创建索引

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

mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table test1;
| test1 | CREATE TABLE "test1" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

组合索引

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

mysql> create table test3 (id int not null,name varchar(20),cardid varchar(20),index index_test3 (id,name));		//最后设置index 组合索引名 （多个列名）;
Query OK, 0 rows affected (0.33 sec)

mysql> show create table test3;
| test3 | CREATE TABLE "test3" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  "cardid" varchar(20) DEFAULT NULL,
  KEY "index_test3" ("id","name")	//这就是设置的组合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

组合索引创建的字段顺序是其触发索引的查询顺序

mysql> show create table test3;
| test3 | CREATE TABLE "test3" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  "cardid" varchar(20) DEFAULT NULL,
  KEY "index_test3" ("id","name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

使用select id，name会触发索引
而select name，id则不会触发

全文索引

全文索引适合进行模糊查询的时候使用，全文索引可以在char，varchar，text类型的列上创建，且每个表只允许一个全文索引
直接创建全文索引

mysql> create fulltext index remark_index on member (remark);		//create fulltext index 索引名 on 表名 （列名）;
Query OK, 0 rows affected, 1 warning (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show create table member;
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  FULLTEXT KEY "remark_index" 	("remark") 	//这就是创建的全文索引字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| cardid  | varchar(18) | YES  |     | NULL    |       |
| phone   | varchar(11) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改表结构方式创建全文索引

mysql> alter table member add fulltext remark_index (remark);
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table member;
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  FULLTEXT KEY "remark_index" 	("remark")	//这就是修改表结构添加的全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| cardid  | varchar(18) | YES  |     | NULL    |       |
| phone   | varchar(11) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

创建表的时候指定全文索引

mysql>  create table test4 (id int(4),name varchar(20),address varchar(40),remark text,fulltext remark_index (remark));
Query OK, 0 rows affected (0.28 sec)

mysql> show create table test4;
| test4 | CREATE TABLE "test4" (
  "id" int(4) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "address" varchar(40) DEFAULT NULL,
  "remark" text,
  FULLTEXT KEY "remark_index" ("remark")		//创建表时创建的全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> desc test4;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
| remark  | text        | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

使用全文索引查询

mysql> select * from test4;
+------+----------+----------+--------+
| id   | name     | address  | remark |
+------+----------+----------+--------+
|    1 | zhangsan | shanghai | NULL   |
|    3 | wangwu   | anhui    | novip  |
|    2 | lisi     | shanghai | vip    |
+------+----------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from test4 where match(remark) against('vip')	//match指定列名，against指定内容
    -> ;
+------+------+----------+--------+
| id   | name | address  | remark |
+------+------+----------+--------+
|    2 | lisi | shanghai | vip    |
+------+------+----------+--------+
1 row in set (0.01 sec)

查看索引

mysql> show index from test4\G
*************************** 1. row ***************************
        Table: test4	//表名
   Non_unique: 1		//是否索引内容唯一，唯一为0，不唯一为1
     Key_name: remark_index		//索引名
 Seq_in_index: 1	//索引中列序号，从一开始表示第二列，跟数组下标一样
  Column_name: remark	//列名称
    Collation: NULL		//列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）
  Cardinality: 3	//索引中唯一值数目的估计值
     Sub_part: NULL	//如果列只是被部分地编入索引，则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引，则为 NULL
       Packed: NULL	//指定关键字如何被压缩，null表示没有被压缩
         Null: YES	//如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO
   Index_type: FULLTEXT	//用过的索引方法
      Comment: 	//备注
Index_comment: 	//索引备注
1 row in set (0.00 sec)

mysql> show keys from test3\G
*************************** 1. row ***************************
        Table: test3
   Non_unique: 1
     Key_name: index_test3
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: test3
   Non_unique: 1
     Key_name: index_test3
 Seq_in_index: 2
  Column_name: name
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

删除索引

直接删除索引

mysql> drop index remark_index on test4;		//drop index 索引名 on 表名
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test4;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> show create table test4;
| test4 | CREATE TABLE "test4" (
  "id" int(4) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "address" varchar(40) DEFAULT NULL,
  "remark" text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

修改表结构方式删除索引

mysql> desc test3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | MUL | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| cardid | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

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

mysql> desc test3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| cardid | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table test3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test3 | CREATE TABLE "test3" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  "cardid" varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除主键索引

mysql> desc test3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(6)      | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| cardid | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test3 drop primary key;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table test3;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test3 | CREATE TABLE "test3" (
  "id" int(6) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  "cardid" varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

数据库的事务

数据库事务的概念

事务是一种机制，一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求
事务是一个不可分割的工作逻辑单元，在执行并发操作时，事务是最小的控制单元
通过事务的整体性以保证数据的一致性

事务的ACID特点

原子性
事物的各元素不可分，是一个完整的操作，如果任何一个元素失败，整个事务就失败了
一致性
当事务完成时，数据必须处于一致状态，事务在进行时可以不一致
隔离性
对数据修改的所有并发事务是彼此隔离的
持久性
不管系统是否故障。事务处理的结果永久的

事务不一致产生的结果

脏读（读取未提交的数据）
脏读是指读到了别的事务回滚前的脏数据
不可重复读（前后多次读取，数据内容不一致）
一个事务前后多次读取，查询的内容却不同，是由于查询时系统中其他事务修改的提交而引起的
幻读（前后多次读取，数据总量不一致）
一个事务在一个表中进行数据修改，另一个事务向表中插入数据，前后读的行数不一致
丢失更新
两个事务同时读取同一条记录，a先修改，b后修改，b会覆盖a修改的内容

事务的隔离级别

read uncommitted：读取未提交的数据，不能解决脏读
read committed：读取已提交数据，可以解决脏读
repeatable read：重读读取，可以解决脏读和不可重复读
serializable：串行化，可以解决脏读，不可重复读和虚读
查询全局事务隔离级别

mysql> show global variables like '%isolation%'
    -> ;
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)

查询回话事务隔离级别

mysql> show session variables like '%isolation%'
    -> ;
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)

设置全局事务隔离级别

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.00 sec)

设置会话事务隔离级别

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.00 sec)

事务控制语句

测试提交事务

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


mysql> select * from qq;
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 70.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

mysql> update qq set score= score + 20 where name='zhangsan';		//修改某一字段中的记录数值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from qq;
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 90.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

mysql> commit;		//提交事务，将修改变为永久
Query OK, 0 rows affected (0.00 sec)

测试回滚事务

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

mysql> update qq set score= score - 40 where name='zhangsan';		//修改记录
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from qq;
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 50.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

mysql> rollback;		//使用回滚
Query OK, 0 rows affected (0.00 sec)

mysql> quit		
Bye
[root@mysql ~]# mysql -uroot -pabc123	//退出数据库重新进入

mysql> use blbl
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 qq;	//数值变回来了
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 90.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

测试多点回滚

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

mysql> update qq set score= score + 10 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint s1;	//设置一个回滚节点
Query OK, 0 rows affected (0.00 sec)

mysql> update qq set score= score + 10 where name='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint s2;	//设置第二个回滚节点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 100.00 | 地址不详     | NULL   |
|  2 | wangwu   |  80.00 | 上海         | 篮球   |
+----+----------+--------+--------------+--------+
2 rows in set (0.00 sec)

mysql> insert into qq values(3,'lisi',40,'南京','play');	//在插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 100.00 | 地址不详     | NULL   |
|  2 | wangwu   |  80.00 | 上海         | 篮球   |
|  3 | lisi     |  40.00 | 南京         | play   |
+----+----------+--------+--------------+--------+
3 rows in set (0.00 sec)

mysql> rollback to s1;	//回滚到第一个节点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 100.00 | 地址不详     | NULL   |
|  2 | wangwu   |  70.00 | 上海         | 篮球   |
+----+----------+--------+--------------+--------+
2 rows in set (0.00 sec)

使用set设置控制事务

mysql> set autocommit=0;	//设置禁止自动提交，0是禁止，1是开启自动提交，1是默认
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> update qq set score= score + 20 where name='zhangsan';	//插入一条记录
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from qq;
+----+----------+--------+--------------+--------+
| id | name     | score  | address_new  | hobby  |
+----+----------+--------+--------------+--------+
|  1 | zhangsan | 120.00 | 地址不详     | NULL   |
|  2 | wangwu   |  70.00 | 上海         | 篮球   |
+----+----------+--------+--------------+--------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql ~]# mysql -uroot -pabc123	//退出再重进

mysql> use blbl
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> show tables;
+----------------+
| Tables_in_blbl |
+----------------+
| qq             |
| ss             |
| yy             |
+----------------+
3 rows in set (0.00 sec)

mysql> select * from qq;	//发现数据没有被改变，说明没有自动提交
+----+----------+-------+--------------+--------+
| id | name     | score | address_new  | hobby  |
+----+----------+-------+--------------+--------+
|  1 | zhangsan | 90.00 | 地址不详     | NULL   |
|  2 | wangwu   | 70.00 | 上海         | 篮球   |
+----+----------+-------+--------------+--------+
2 rows in set (0.00 sec)

数据库的存储引擎

数据库常用的两种存储引擎

MyISAM

myisam不支持事务，不支持外简约束，只支持全文索引，数据文件和索引文件是分开保存的
myisam对数据的访问速度快。对事务完整性没有要求
比较适合查询，插入为主的应用
myisam是表级锁定，读或写无法同时进行

myisam支持三种不同储存格式

静态表
存储的每个记录都是固定长度，这样存储的优点是存储非常快，出现故障容易恢复，缺点是占用空间通常比动态多
动态表
记录不是固定的，优点是占用空间少，但是会产生记录碎片，需要定期清理，出现故障比较难恢复
压缩表
由myisamchk工具创建。占用空间非常小

MYISAm适合的场景

公司业务不需要事务支持
单方面读取或写入数据比较多的业务
对数据一致性要求不要的业务
数据修改相对较少的业务

InnoDB

特点

读写阻塞与事务隔离级别相关
非常高效的缓存索引和数据
表与主键以簇的方式存储
支持分区，表空间
支持外键约束
查看系统支持的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看表使用的存储引擎

mysql> show table status from blbl where name='qq'\G;
*************************** 1. row ***************************
           Name: qq
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-07-06 22:34:39
    Update_time: 2021-07-11 22:58:24
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

修改存储引擎
①通过alter table

mysql> alter table qq engine=MyISAM;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table qq;
| qq    | CREATE TABLE "qq" (
  "id" int(11) NOT NULL,
  "name" char(10) NOT NULL,
  "score" decimal(5,2) DEFAULT NULL,
  "address_new" varchar(50) DEFAULT NULL,
  "hobby" varchar(20) DEFAULT NULL,
  PRIMARY KEY ("id"),
  UNIQUE KEY "address_new" ("address_new")
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

②通过修改配置文件修改引擎

[root@mysql ~]# vim /etc/my.cnf
default-storage-engine=INNODB	//添加一行
[root@mysql ~]# systemctl restart mysqld.service

mysql> create table qwe(id int,name char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table qwe;	//修改后的引擎只对新加的表生效，之前的表还是之前的引擎
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| qwe   | CREATE TABLE "qwe" (
  "id" int(11) DEFAULT NULL,
  "name" char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

③创建表时指定存储引擎

mysql> create table tyu(id int,name char(10)) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tyu;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| tyu   | CREATE TABLE "tyu" (
  "id" int(11) DEFAULT NULL,
  "name" char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)