MySQL的事务和索引

目录

一、Mysql事务

        1.1 什么是事务

        1.2 事务的四大特性

二、Mysql 事务的使用

        2.1 自动提交事务

        2.2 设置手动提交

        2.3 事务开始

三、事务的隔离级别

​编辑

3.1  事务隔离级别实验

四、索引

        4.1 索引的概念

         4.2 语法

        4.3 在哪些列上建立索引？

五、三大范式

        5.1 三大范式的概念

        5.2 第一范式（1NF）：原子性

        5.3 第二范式（2NF）：唯一性

        5.4 第三范式（3NF）：冗余问题

---

一、Mysql事务

        TCL事务控制语言(transaction control language)

        1.1 什么是事务

        事务（transaction）： MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，（操作多个表）这样，这些数据库操作语句就构成一个事务！

        

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

• 事务用来管理 insert,update,delete 语句

        1.2 事务的四大特性

        一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。 

        原子性（Atomicity ）：物质最小的单位。对应事务来说就是这个事务的所有操作不可以分开，必须同时执行，要么都成功，要么都失败

        一致性（Consistency）:不管事务成功与失败，最后的结果都要是一致的

        隔离性（Isolation）：事务的隔离性就是 如果存在两个事务 他们是相互隔离，互不影响的

        持久性（Durability）：事务一旦提交，就应该永久保存下来，不会因为客户端退出去而丢失。最终MySQL数据表里的数据存储在硬盘文件

二、Mysql 事务的使用

                因为MySQL默认是自动提交事务（当执行完一条数据操作语句，默认就提交了）。

        2.1 自动提交事务

        select @@autocommit;
        如果值等于1，就代表自动提交。
        为了能够控制事务必须把自动提交关闭。

        2.2 设置手动提交

    set autocommit = 0
    如果值等于 0 就已经设置为了手动提交

        2.3 事务开始

        如果前面没有commit,rollback，DDL,事务开始，当执行commit或者是rollback事务结束。

# 事务开始
insert into dept values(70,'TA','ZZ');
delete from dept where deptno=10;
#再有其他数据操作也是在一个事务内

commit;或者rollback; # 事务结束，下面再执行就是一个新的事务

        再第一个中开启事务的方式:当autocommit = 1也就是自动事务时使用  

start transaction;# 开始事务
insert into dept values(80,'KI','ZZ');
delete from dept where deptno=10;
#再有其他数据操作也是在一个事务内

commit;或者rollback; # 事务结束，下面再执行就是一个新的事务

三、事务的隔离级别

         多个事务时，就会出现数据问题

• 脏读

        读到了脏数据就叫脏读。什么是脏数据呢，就是没有提交的数据就叫脏数据

        事务1添加了一条数据，还没提交，事务2就已经读到了这条新增加的数据就出现了脏读

• 不可重复读

        两次读到的数据是不同的

        事务1修改了一条数据，事务2第一次查询是一个数据而第二次查询是不同的数据

• 幻读

        两次读取到数据条数不同，好像出现了幻觉，就是幻读

                怎么解决这些问题呢？

        要想解决这些问题就靠事务隔离级别，事务隔离的程度高低。

3.1  事务隔离级别实验

select @@transaction_isolation; 查看默认的事务隔离级别

        修改默认的配置文件中的（全部修改my.ini）
        transaction-isolation = 隔离级别；

#命令行修改隔离级别；
修改隔离级别的语句是：set [作用域] transaction isolation level [事务隔离级别]，
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}。

上面操作完要退出从新登录设置才生效。一旦从新登录，之前设置的手动提交又变成了自动提交，还得修改。

四、索引

        4.1 索引的概念

        索引是为了提高查询的速度而保存到数据中一种数据结构（B-tree）。数据库索引就想书的目录，书有目录要去找内容都会先查查目录，然后目中会有查找内容的页码，这样直接翻到对应的页码了，大大提高了查找的速度。

         4.2 语法

CREATE INDEX indexName ON table_name (column_name)

        4.3 在哪些列上建立索引？

• 主键、外键、唯一约束创建后会自动创建了索引

• 经常出现在where后面的列

• 表数据量比较大时，至少超过万行

• 列值重复比较少的列

示例：

# ename 经常出现在where后面，并且重复比较少
CREATE INDEX emp_name_index ON emp(ename);

五、三大范式

        5.1 三大范式的概念

        在设计数据库时要遵守一定规范，业界就有第一范式（1NF）、第二范式（2NF）、第三范式（3NF）。

        注意：这些范式是一个包含关系，后面的包含前面的。

        5.2 第一范式（1NF）：原子性

        列是原子的（不可再分），如果一个列可以再分就违反了第一范式。

举例说明：需求描述：数据库系统中需要一个实体表，该表用来存储用户信息，其中“地址”这个属性，要求查询到省份、城市和详细地址。

                        家庭信息这一列是可以再分的，这样就违反了第一范式。  

解决方法：

        5.3 第二范式（2NF）：唯一性

        普通字段都要和主键关联，意思就是表里必须创建一个主键，这样就解决了唯一性问题。         

                        学号有重复，这样设计把学分依赖于学号了。          

 

        5.4 第三范式（3NF）：冗余问题

        普通字段都要和主键直接依赖，不能间接依赖。

                上面学院电话和学号不是直接依赖，那么就违反第三范式。  

       修改解决：