视图、事务与索引

简述了视图、事务、索引的概念与使用。

视图

通俗的讲，视图就是一条 select 语句执行后返回的结果集，通常是对若干张基本表的引用，是一张虚表。通过对视图的引用，可以避免修改数据库结构便需要修改代码的麻烦，而且可以减少复杂的 SQL 语句的使用，增强可读性。

建议以 v_ 开头定义视图

create view 视图名称 as select语句;
-- 以 jing_dong 数据库为例建视图 v_goods_info
create view v_goods_info as 
select g.*, c.name as cate_name, b.name as brand_name from goods as g 
left join goods_cates as c on g.cate_id=c.id 
left join goods_brands as b on g.brand_id=b.id;
-- 查看视图
show tables;
-- 使用视图
-- 视图多用于查询
-- 当视图中包含如下结构，便不可对其修改：聚合函数、distinct 关键字、group by 、order by 子句等
select name, brand_name, cate_name from v_goods_info;
-- 删除视图
drop view 视图名称;

视图的作用

提高重用性
对数据库重构时，不影响程序的运行
提高了安全性能，可以对不同权限的用户提供不同的视图
让数据更加清晰，增加可读性

事务

事务是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单位。如从一个账号扣款然后另一个账号收款，这两个操作要么都执行，要么都不执行，这可视为一个事务。

事务有如下四大特性（ACID）

原子性（Atomicity）

一个事务中的所有操作要么全部提交成功（commit），要么全部失败回滚（rollback），不可以只执行其中一部分。
一致性（Consistency）

数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性（Durability）

一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

表的引擎类型必须是 Innodb 类型才可以使用事务。可以通过查看表的创建命令 show create table 表名 来确定引擎类型。

可以用 SQL 命令 start transaction; 或 begin; 来开始一个事务，然后要么用 commit; 命令提交修改，结束一个事务，要么使用 rollback; 撤销所有的修改。

索引

索引是一个特殊的文件（InnoDB 数据表是的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

索引的创建用如下命令

-- 如果指定字段是字符串，需要指定长度
-- 字段类型如果不是字符串，可以不写长度部分
create index 索引名称 on 表名(字段名称(长度));

查看索引

show index from 表名;

删除索引

drop index 索引名称 on 表名;

下面用一个例子来说明建立索引对查询时间的贡献。

使用 pymysql 建立一个有十万条数据的表。

from pymysql import connect
def main():
    conn = connect(host="localhost", port=3306, database='jing_dong', user='root', password='mysql')
    cursor = conn.cursor()
    # 新建测试表
    cursor.execute('create table test_index(title varchar(10));')
    # 插入数据
    for i in range(100000):
        cursor.execute('insert test_index values ("name-%d");' % i)
    conn.commit()
if __name__ == "__main__":
    main()

在建立索引之前，进行查询

use jing_dong;
-- 开启运行时间监测
set profiling=1;
-- 查询一条数据
select * from test_index where title = 'name-6834';
-- 查看运行时间
show profiles;
-- 在我的虚拟机上，花费了 0.06994325 sec

为表建立索引，并进行相同的查询

-- 为title列建立索引
create index title_index on test_index(title(10));
-- 执行查询语句
select * from test_index where title = 'name-6834';
-- 查看运行时间
show profiles;
-- 可以发现最近执行的查询语句，时间变为了 0.00038925 sec

需要注意的是，建立太多的索引会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为了一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。