mysql事务、视图、索引简介
知识点
函数
1 | # ifnull(sql语句, 为null时的返回值)：用于判断返回结果是否为null |
视图
背景
问题
对于复杂的查询查询，往往是由多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来特别麻烦
解决方案
创建视图
定义
通俗的讲，视图就是一条select语句执行后返回的结果集。所以我们在创建的时候，主要工作就落在创建这条sql语句上。
视图是对若干张基本表的引用，是一张虚拟的表，用于存放查询语句执行的结果，不存储具体的数据(基本表数据发生改变，视图也会随之发生改变)
优点
- 减少复杂的sql查询语句，增强可读性，
- 提高了重用性
- 数据库重构时并不影响程序的运行，减少程序代码和数据库数据的耦合度
- 提高安全性，针对不同的用户
代码结构
1
2
3
4
5
6
7
8
9
10
11
12# 视图的存在是为了查询数据的方便，不是为了修改数据
# 定义视图，视图名称建议以v_开头
create view 视图名称 as select语句;
# 查看视图
show tables;
# 使用视图
select * from 视图名称 where 条件;
# 删除视图
drop view 视图名称;
事务
背景
当用户的某个行为是由多个动作组成的，任何一个动作的失败都会导致该行为的失败。
定义
事务就是一个操作序列，这些操作要么都执行，要么都不执行，他们是一个不可分割的工作单位。其包括四大特性：
- 原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有的操作要么全部提交成功，要么全部失败回滚，对于一个事务而言不可能只执行其中的一部分操作。
- 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。不存在中间态
- 隔离性：一个事务所做的修改在最终提交以前，对其它事务是不可见的。就是指在事务未提交修改之前，数据库中所有的数据依然保持在事务未执行的状态，同时此时在其它事务如果要修改同一个表中的数据就会等待之前的事务操作完成
- 持久性：一旦事务提交，则其修改的数据会永久的保存到数据库中去。
代码实现
1
2
3
4
5
6
7
8
9
10
11
12# start方式开启
start transaction;
事务包含的所有的sql语句;
# 如果要提交修改，则commit
commit;
# 如果要回滚提交，则rollback；
rollback;
# begin方式开启
begin;
事务包含的所有的sql语句
commit;
索引
背景
问题
当在大批量数据中查询某个数据时，此时查询数据会非常慢
解决方案
添加索引
定义
索引是一种特殊的文件(InnoDB数据表上的索引是数据表的一个组成部分)，里面包含着对数据表中所有记录的引用指针。它利用B-tree算法实现。当在表中设置主键和外键时，数据库会自动为这些字段创建索引。
代码结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16# 开始时间统计功能，此时开始统计sql执行语句花费的时间
set profiling = 1;
执行的各类sql语句;
# 查看包裹的sql语句执行的时间
show profils;
# 索引的存在时为了提高数据的查询速度
# 创建索引, 建议索引名称以：表名_index
# 如果建立索引的字段是int类型，字符长度可不填；若是字符串类型，则字符长度建议与定义字段时长度一致；这样可以提高性能
create index 索引名称 on 表名(字段名(字符长度))
# 查看索引
show index from 表名;
# 删除索引
drop index 索引名称 on 表名;注意
- 建立太多的索引会影响更新和插入的速度，因为其同样得更新索引文件
- 建立索引会占用内存空间
- 只有当数据量特别大，且查询频率高，而更新频率低的字段可以添加索引，反之不建议加索引
归纳
为便于记忆，提供脑图
