Mysql笔记06：索引、事务和视图

1 索引

加速查询的一种数据结构，索引保存的数据结构主要为B+树，及hash的方式.
优点：提升查询效率
缺点： 降低了插入/修改/删除效率，增加了硬盘的使用空间

注意：
索引不是无限制加的，要根据需要来加
1）数据量够大
2）索引应该加在经常查找的字段上

（1）索引的分类
创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。
1）主键 Primary Key
2）唯一键 Unique
3）普通索引（一般提到创建索引，没有特别说明，都是指普通索引） Key|Index

（2） 索引的使用
1）查看索引

show index from 表名;

2）创建索引（对于非主键、非唯一约束、非外键的字段，可以创建普通索引）

• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
• 字段类型如果不是字符串，可以不填写长度部分，如int类型不需指定长度

create index 索引名称 on 表名(字段名称(长度))

3）删除索引

drop index 索引名称 on 表名;

注意：

• 建立的主键和外键也是索引
• 对于修改频率高的表，不加索引，加索引会降低效率

调优查询的工具
EXPLAIN 命令

key: 查询时的索引关键字
rows： 查询所用的行数

2 事务（Transaction）

——————————————————针对于增删改

事务四大特性(简称ACID)

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

2）一致性(Consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态。也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

3）隔离性(Isolation)
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。commit未提交时，其他事务并不能实现对同一数据的修改，会等待另一个commit提交. 比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4）持久性(Durability)
一旦事务提交，则其所做的修改会永久保存到数据库。

注意：
1）mysql客户端中对于单个语句是默认开启事务的，当需要多个语句一起执行时，此时需要手动开启事务。
2）表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎，查看表的创建语句，可以看到engine=innodb。

1 开启事务
开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中。

begin; 或start transaction;

2 提交事务
将缓存中的数据变更维护到物理表中。

commit;

3 回滚事务
放弃缓存中变更的数据。

rollback;

注意：
1）修改数据的命令会自动的触发事务，包括insert、update、delete。
2）在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据。

3 视图

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦。解决办法就是：定义视图

视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

（1）定义视图
建议以v_开头

create view 视图名称 as select语句;

（2）查看视图
查看表会将所有的视图也列出来

show tables;

（3）使用视图
视图的用途就是查询

select * from v_stu;

（4）删除视图
drop view 视图名称;

drop view v_stu_sco;

视图的作用：

• 提高了重用性，就像一个函数
• 对数据库重构，却不影响程序的运行
• 提高了安全性能，可以对不同的用户
• 让数据更加清晰