视图，索引，事务

1.视图

• 创建视图

create view v1 as select * from user where city='北京'

• 删除视图

drop view if exists v_user 

• 视图的DML操作(对基表进行修改)

insert into v1 (userId,name)values('101','晓晓')

update v1 set name='晓晓' where userId='101'

delete from v1 where userId='101'

• 以下条件视图不能做DML操作：

 - select子句中包含distinct
 - select子句中包含组函数
 - select语句中包含group by子句
 - select语句中包含union 、union all等集合运算符
 - where子句中包含相关子查询
 - 如果视图中有计算列，则不能更新
 - 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

• 视图约束(with check option)

-- insert插入的数据，city只能是北京
create view v1 as select * from user where city='北京' with check option

2.索引

• 创建索引

create index index_user_name on user (name)

alter table user add index index_user_name(name)

• 删除索引

drop index index_user_name on user

3.索引的分类

• 普通索引

create index index_user_name on user(name)

• 普通索引-最左前缀

create index index_user_personId on user(personId(6))

• 主键索引:不允许有空值,唯一。

alter table user add primary key(userId) 

• 唯一索引:索引列的值必须唯一，但允许有空值

create unique index index_user_personId on user (personId)

alter table user add unique index_user_personId(personId)

• 多列索引:必须唯一

create unique index index_user_personId on user(personId,name,ename)

alter table user add unique index_user_personId(personId,name,ename)

4.索引

• 索引组成
  Hash+BTree

• 索引的优点

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2.建立索引可以大大提高检索的速度,以及减少表的检索行数
3.在表连接的连接条件(主外键条件)可以加速表与表之间的相连 
4.在分组和排序字段进行数据检索,可以减少查询时间中分组和排序时所消耗的时间

• 索引的缺点

1.创建索引和维护索引会耗费时间,随着数据量的增加而增加
2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3.当对表的数据进行INSERT,UPDATE,DELETE的时候,索引也要动态的维护,这样就会降低数据的维护速度

• 使用索引需要注意的地方

 1.在经常需要搜索的列上建立索引，可以加快检索的速度。
 2.主键列上可以确保列的唯一性。
 3.在表与表的连接条件上加上索引，可以加快连接查询的速度。
 4.在经常需要排序(order by),分组(group by)和的distinct列上加索引可以加快排序查询的时间， (单独order by 用不了索引，索引考虑加where或加limit) 。
 5.选择越小的数据类型越好,因为越小的数据类型通常在磁盘,内存,cpu，缓存中占用的空间很少,处理起来更快。
 6.使用最左前缀，如果你的一个字段是Char(32)或者int(32)，在创建索引的时候指定前缀长度，比如前10个字符 (前提是多数值是唯一的)那么索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作。
 7.数据的基数大效果好，基数小，加了用处也不大。
 8.在一些where之后的 < <= > >= BETWEEN IN建立字段的索引。
 9.like语句如果你对name字段建立了一个索引，当查询的时候的语句是 name like '%ABC%'，这样会使得mysql索引失效。name like 'ABC%' 那么将可以用到索引。
 10.索引不会包含NULL列，这样会使得mysql索引失效,一般需要给默认值0或者 ' '字符串。
 11.在列上进行运算，在列上使用函数，在列上类型转换，这样会使得mysql索引失效
 12.使用不等于或者not in 的时候，这样会使得mysql索引失效
 13.字符串不加单引号，这样会使得mysql索引失效.
 14.如果出现OR(或者)运算，要求所有参与运算的字段都存在索引，尽量使用 union。

• 什么情况下不能建立索引

1.查询中很少使用到的列不应该创建索引,如果建立了索引会降低mysql的性能并且增大了空间需求.
2.选择区分度高的列建立索引,比如:一个性别字段:0或者1,不建议建立索引。
3.定义为text和image和bit数据类型的列不应该增加索引,
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

3.事务

• 事务作用

事务主要用于处理操作量大，复杂度高的数据。可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句。

• ACID

事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

 - 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
 - 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
 - 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
 - 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

• 事务的隔离性
  
  Mysql数据库默认隔离级别：可重复读
  Oracle数据库默认隔离级别：读已提交
• 设置隔离级别

查看当前事物级别： 
SELECT @@tx_isolation;

设置read uncommitted级别：
set session transaction isolation level read uncommitted;

设置read committed级别：
set session transaction isolation level read committed;

设置repeatable read级别：
set session transaction isolation level repeatable read;

设置serializable级别：
set session transaction isolation level serializable;

• 事务处理

在 MySQL 中只有使用了 Innodb 引擎的数据库或表才支持事务。
rollback 事务回滚   commit 事务确认
set autocommit=0 禁止自动提交       set autocommit=1 开启自动提交（默认）