事务

• 学习目标:

知道事物的四大特性

1.事物就是用户定义的一系列执行sql语句的操作，这些操作要么完全执行，要么完全都不执行，它是一个不可分割的工作执行单元

事物的使用场景：

在日常生活中，有时候我们需要进行银行转账，这个银行转账操作背后就是需要执行多个sql语句，假如这些sql执行到一半突然停电，那么就会导致这个功能只完成了一半，这种情况是不允许出现了，要想解决这个问题，就只需要通过事务来完成。

2.事务的四大特性

• 原子性
• 一致性
• 隔离性
• 持久性

原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态.（在前面的例子中，一致性确保了，及时在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性：一个事务所做的修改操作在提交事务前，对于其他事务来说是不可见的。(在前面的例子中，当执行完第三条、第四条语句还未开始时，此时另外的一个账户汇总程序开始运行，则其看到支票账户的余额并没有被减去200美元)

持久性：一旦事务提交，则其所作的修改会永久保存到数据库中。

1. 事务的使用 在使用事务之前，先要确保表的存储引擎是InnoDB类型，只有这个类型才可以使用服务，MySQL数据库中表的储存引擎默认是InnoDB类型

表的存储引擎说明: 表的存储引擎就是提供存储数据一种机制，不同表的存储引擎提供不同的存储机制。

---查看MySQL数据库支持的表的存储引擎

show engines;

输出结果为:

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

说明:

• 常用的表的存储引擎是iNNODB  MyISAM
• InnoDB是支持事务的
• MyISAM不支持事务，优势是访问速度快，对事务没有要求或以select
insert
  为主的都可以使用该存储引擎来创建表

如何查看表的存储引擎：

show create table classes;

输出结果为:

+---------+------------------------------------------------
| Table   | Create Table
+---------+------------------------------------------------
| classes | CREATE TABLE `classes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------
1 row in set (0.00 sec)

我们可以看到classes使用的表的存储引擎是InnoDB

可以修改表的存储引擎:

alter table 表名 engine = 引擎类型;

#比如:
alter table students engine = 'MyLSAM';

开启事务

begin;

或者
start transcation;

说明：

• 开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不是维护到物理表中
• MySQL数据库默认采用自动化提交(autocommit)模式，如果没有显示的开启一个事务，每条sql语句都会被当做一个事务执行提交的操作
• 当设置autocommit = 0 就是取消了自动提交事务模式，直到显示的执行commit和rollback表示该数据结束。

索引

索引的作用：提升数据库的开发速度

1. 索引的介绍 索引在Mysql中也叫作"键"，它是一个特殊的文件，它保存着数据库表里面所记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能够加快数据库的查询速度。

引用场景：当数据库中的数据量较大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

1. 索引的使用 查看表中已有的索引：

show index from 表名;

说明：

• 主键列会自动创建索引，外键约束也会自动创建索引

索引的创建：

• 创建索引的语法格式

alter table 表名 add index 索引名[可选](列名,..)

• 给name字段添加索引 ``mysql alter table classes add index my_name(name);

说明：
* 索引名不指定，默认使用字段名
索引的删除
-- 删除索引的语法格式
```mysql
alter table classes drop index my_name;

1. 案例-验证索引查询性能 创建测试表 testindex:

create table test_index(title varchar(10));

其中使用python对其进行连接操作如下:

import pymysql

if __name__ == '__main__':

    #创建连接对象
    conn = pymysql.connect(host="localhost",
                           port=3306,
                           user="root",
                           password="root",
                           database="python0213",
                           charset="utf8")

    #获取游标，目的是执行sql
    cursor = conn.cursor()

    #准备SQL
    sql = "insert into mytest(name) values(%s);"

    try:
        #循环执行1000次插入数据的操作
        for i in range(10000):
            cursor.execute(sql,["test" + str(i)])
        #代码执行到此说明添加数据完成，那么提交数据到数据库
        conn.commit()
    except Exception as e:
        conn.rollback()
    finally:
        #关闭游标
        cursor.close()
        #关闭连接
        conn.close()

输出结果为:

| test9992 |
| test9993 |
| test9994 |
| test9995 |
| test9996 |
| test9997 |
| test9998 |
| test9999 |
+----------+
10000 rows in set (0.01 sec)

接下来我们设置索引时间

set profiling = 1
select * from mytest where name = 'test8888';

输出结果为:

+----------+
| name     |
+----------+
| test8888 |
+----------+
1 row in set (0.09 sec)

查看花费时间

show profiles;

显示结果为:

+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        1 | 0.04958325 | select * from mytest where name = 'test8888' |
+----------+------------+----------------------------------------------+
1 row in set, 1 warning (0.01 sec)

当我们设置了索引之后

select * from mytest where name = 'test8888';

输出结果为:

+----------+
| name     |
+----------+
| test8888 |
+----------+
1 row in set (0.06 sec)

显示查询时间:

show profiles;

输出结果为:

+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        1 | 0.04958325 | select * from mytest where name = 'test8888' |
|        2 | 0.58298000 | alter table mytest add index(name)           |
|        3 | 0.06113475 | select * from mytest where name = 'test8888' |
+----------+------------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

所以设置索引值之后，好像第三个比第一个更慢??。。。。。

联合索引

联合索引的创建就是减少磁盘空间，联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。创建联合索引的步骤如下:

# 1创建表mytest1
create table mytest1(id int not null primary key auto_increment, name varchar(30),age int);
# 2新建一个index
alter table mytest1 add index(name,age);
# 3展现结果
show create mytest1;

输出结果为:

+---------+---------------------------------
| Table   | Create Table
+---------+---------------------------------
| mytest1 | CREATE TABLE `mytest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------
1 row in set (0.00 sec)

KEY
name(
name,
age)
，我们同时创建了name和age两个索引值。

如果要删除，仅保留一个联合主键，输出方式为:

alter table mytest1 drop index name;

#展示结果为:
show create table mytest1;

输出结果为:

+---------+--------------------------------
| Table   | Create Table
+---------+--------------------------------
| mytest1 | CREATE TABLE `mytest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------
1 row in set (0.00 sec)

1. 联合索引的最左原则 在使用联合索引的时候，我们遵守一个最左原则，即index(name,age)支持name、name和age组合查询，而不支持单独age查询，因为没有用到创建的联合索引。

2. MySQL中的索引的优点和缺点和使用原则：

• 优点：

1.加快数据的查询

• 缺点：

1. 创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加

• 使用原则：1.通过优缺点对比，不是索引越多越好，而是需要自己合理的使用

1. 对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建

2. 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

3. 在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男,女两个不同值。相反的，在一个字段上不同值较多可建立索引。

4. 小结

• 索引是加快数据的查询一种手段
• 创建索引使用: alter table 表名 add index 索引名
  
• 删除索引使用：alter table 表名 drop index 索引名
  

(完)