MySQL：四、MySQL的索引和事务

一、索引

1.1 索引介绍

• 索引作用：索引用于快速找出在某个列中有一特定值的行。
• 索引优点：不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。
• MySQL中，所有数据类型的列都可以被索引，常用的存储引擎InnoDB和MyISAM能支持每个表创建16个索引。InnoDB和MyISAM使用的索引其底层算法是B-tree（B树），B-tree是一种自平衡的树，类似于平衡二叉排序树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的操作都在对数时间内完成。
• 注意：索引也不易过多，索引越多写入，修改的速度越慢。因为，写入修改数据时，也要修改索引。

1.2 索引分类

索引类型	功能说明
普通索引	最基本的索引，它没有任何限制
唯一索引	某一行启用了唯一索引则不准许这一列的行数据中有重复的值。针对这一列的每一行数据都要求是唯一的 unique
主键索引	它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，常用于用户ID。类似于书中的页码 primary key
全文索引	对于需要全局搜索的数据，进行全文索引

1.2.1 普通索引

• 添加语法：alter table 表 add index(字段)

cmd界面添加普通索引：
alter table money add index(username);

1.2.2 唯一索引

• 添加语法：alter table 表 add unique(字段)

添加唯一索引：
alter table money add unique(username);

1.2.3 主键索引

• 添加语法：alter table 表名 add primary key(字段)

添加主键索引：
alter table money add primary key(id);

1.2.4 创建表时声明索引

• 创建表时可在创建表语句后加上对应的类型即可声明索引

create table user (
	id int auto_increment,
	name varchar(20),
	primary key(id),
	unique (name)
);

1.3 查看索引

• 语法：show index from 表名;

show index from user\G;

\G：改变查看输出格式。

1.4 删除索引

• 语法：alter table 表名 drop index 字段；

alter table money drop index age;

1.5 索引效率验证

import random
import time

from MySQL.day04.code.SQLModel import SQL

1、插入数据：
def insert_data():
    db = SQL(password='hzl2020',database='mydb2')
    for i in range(100000,500001):
        name1 = name2 = f'zhangsan-{i}'
        qq = random.randint(100000,10000000000)
        sex = random.choice(['男','女'])
        email = f'{qq}@.com'
        sql = f'insert into user(name1,name2,qq,sex,email) values("{name1}","{name2}","{qq}","{sex}","{email}")'
        # sql = 'insert into user(name1,name2,qq,sex,email) values("%s","%s","%s","%s","%s")'
        db.insert(sql)
    db.close()

2、查询数据：
def query_data():
    db = SQL(password='hzl2020',database='mydb2')
    # 查有索引
    sql1 = 'select * from user where name1="zhangsan-500000"'
    # 查无索引
    sql2 = 'select * from user where name2="zhangsan-500000"'

3、获得查询所需时间
    start = time.time()
    res = db.get_one(sql1)
    print(res)
    end = time.time()
    print('消耗时间',end-start)

    db.close()

调用对应函数方法：
if __name__ == '__main__':
    # insert_data()
    # query_data()

二、事务

• 在MySQL中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务作用：事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert，update，delete 语句。

2.1 事务控制语言（DTL）

• 我们每执行一条SQL语句，每执行一组SQL语句,我们都可以称为事务。如果一组SQL语句里，某一个SQL语句失败了，称为整个事务的失败，因此出现这种情况必须要恢复到正常的情况上才能没有问题。
• 事务可以看作是一个“容器”，将多条语句，放入该容器。最后，只要一个命令行，来决定其中的所有语句是否“执行” 。

2.2 事务的四大特征

• 事务是必须满足4个条件（ACID）：

1. 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
2. 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致性与原子性密切相关。
3. 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
   4个状态：串行化、可重复读、读取已提交、读取未提交。
4. 持久性（Durability）：也称永久性，已被提交的事务对数据库的修改应该永久保存在数据库中。

2.3 事务的原理

• 传统情况：
  

• 事务机制：
  

2.4 事务模式

使用 start transaction，开启事务模式
使用 commit 语句执行后，事务中操作的语句才能真正生效
使用 rollback 语句进行回滚。

开启事务模式：
start transaction;		==》 等价于：begin;
执行操作：
insert into table_name(...) values(...);
如果语句没问题提交：
commit;
如果有问题回滚：
rollback;

2.5 事务的流程

执行语句	说明
start transaction	执行开始事务命令后,下面将进入事务模式.
update、delete、insert 操作	在事务执行后,这些操作只在内存状态下进行,而不是在物理状态下
commit	在事务执行完成后,确认执行无误且成功,就可以使用commit把内存中执行的结果,提交到物理内存中
rollback	如果在事务执行过程中,发生了错误,则可以使用rollback命令回滚到上一个事务操作

在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。commit()方法提交游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。