目录
一、索引
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):
- 原子性(Atomicity):事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
- 一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致性与原子性密切相关。
- 隔离性(Isolation):多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
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()方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。