mysql数据库--事务、索引、视图（7）

一、事务

• 事务把一组操作看做一个整体，要不都操作成功，要不都操作失败 。

• 表的数据库引擎必须是innodb，innodb支持事物，myisam不支持事务

• 修改表引擎：alter table 表名 engine = innodb

事务的特性（ACID）
数据库的事务必须具备ACID特性，ACID是指 Atomic（原子性）、Consistensy（一致性）、Isolation（隔离型）和Durability（持久性）的英文缩写。
1、原子性（Atomicity）
事务包装的一组sql，要么都执行成功，要么都失败。这些操作是不可分割的。
2、一致性（Consistency）
数据库的数据状态是一致的。
事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性。
3、持久性：（Durability）
事务成功提交之后，对于数据库的改变是永久的。哪怕数据库发生异常，重启之后数据亦然存在。
4、隔离性（Isolation）
一个事务的成功或者失败对于其他的事务是没有影响。2个事务应该相互独立。

事务的操作：
开启事务：start transaction / begin
提交事务：commit，将数据写到硬盘
回滚：rollback

二、索引

索引就像图书的目录，可以加快查询速度

3.1 索引的优点

• 可以大大加快数据的检索速度
• 唯一索引可以保证数据的唯一性
• 可以降低分组、排序的时间
• 可以使用查询优化器提高系统性能

3.2 索引的缺点

• 建立索引会建立对应索引文件，占用大量空间
• 建立索引会降低增、删、改的效率

3.3 不建立索引

• 频繁更新的字段不要建立索引
• 没出现在where、having，不要建立索引
• 数据量少的表没有必要建立索引
• 唯一性比较差的字段不要建立索引

3.4 索引分类

普通索引

create index 索引名 on 表名(字段 asc/desc) 默认asc升序

唯一索引

在唯一索引所在列不能有重复值，增加和修改会受影响。

create  unique index 索引名 on 表名(字段 asc/desc) 默认asc升序

主键索引

创建表，主键索引会自动添加，要求在主键上不能有重复值，不能有空值

复合索引（联合索引） 索引了多个列

• 使用联合索引，必须包含左前缀。 （a,b,c)
  • a
  • a,b
  • a,b,c

全文索引（了解）

一般会用全文索引服务器(sphinx)，不会直接创建全文索引

create  FULLTEXT index 索引名 on 表名(字段 asc/desc)

删除索引

drop index 索引名 on 表

查看索引

show index from 表 \G

#查看sql性能
explain select sno,sname from student where class='1812'\G;
mysql> explain select sno,sname from student where sclass='1812' ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
type:  ALL  全表扫描
    index  使用索引
    range  在指定范围内使用索引
    const、system 常量查询

其它创建索引的方式

alter table 表 add index(字段1,字段2,...)
alter table 表 add primary key(字段1,字段2,...)
alter table 表 add unique(字段1,字段2,...)
alter table 表 add fulltext(字段1,字段2,...)

3.5 不使用索引的情况

• 查询时的联合索引没有左前缀，不使用索引
• or条件里，如果一方字段没有索引，则不使用索引
• 类型不对应的不会使用索引
• like ‘%tom’ ,如果左边是通配符，不会使用索引
• 使用!=、<>、not in操作，不使用索引

三、视图

有时候经常会遇到复杂的查询，写起来比较麻烦，这时候我们可以使用视图简化查询。视图就是固化的sql语句，可以不把视图当做基本表使用

• 不要在视图上进行增、删、改

创建视图
create view 视图名(字段列表) as  select子句

删除视图
drop view 视图名

四、数据库备份和恢复

• 备份

  不用登录mysql，直接执行mysqldump命令,将指定数据库备份到家目录下的指定文件
  mysqldump –uroot –p 数据库名 > ~/备份文件名.sql;
  

• 恢复

  首先要创建一个mysql数据库，然后退出mysql，执行以下命令
  mysql -uroot –p 数据库名 < ~/备份文件.sql