数据库应用——MySQL索引视图与事务

前言：在企业信息话的过程中，数据库中表的数据量越来越大，性能会急剧下降，创建索引对保持良好的性能非常关键。索引是对查询性能优化最有效的手段，能够轻易将查询性能提高几个数量级。
执行数据库操作时，有时需要多个操作表示一个操作流程，要么都执行，要么都不执行，这就是数据库的事务。

一、数据库索引

数据库索引是数据库管理系统中一个排序的数据结构，以协助快速查询、更新书库表中的书库。

1.索引的概念

• 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

• 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

• 当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作;第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。

2.索引的作用

• 在数据库系统中建立索引主要有以下作用:

  • 实现表与表之间的参照完整性;

  • 在使用order by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

  • 设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率

  • 特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成干倍

  • 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本

  • 通过创建唯一性索引保证数据表数据的唯一性

  • 可以加快表与表之间的连接

  • 在使用分组和排序时，可大大减少分组和排序时间

3.索引的分类

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索

• 单列索引与多列索引：索引可以是单列上创建的索引，也可以是在多列上船舰的索引

4.创建索引的原则依据

• 表的主键、外键必须有索引
• 数据量超过300行的表应该有索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引
• 唯一性太差的字段不适合建立索引
• 更新太频繁地字段不适合创建索引
• 经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引
• 索引应该建在选择性高的字段上
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

5.创建及查看索引

在创建索引之前，首先先创建一个库并给其定义相关数据

mysql> create database school;         //创建一个名叫‘school’的库
Query OK, 1 row affected (0.01 sec)

mysql> show databases;          //查看显示创建成功
+--------------------+ 
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;              //进入库
Database changed
mysql> create table biao (                  //创建一个表叫‘biao’
    -> id int(4) not null primary key auto_increment,     //第一个为id，类型为int，长度为4，不为空，定义其为主键，设置为自动增长
    -> name varchar(10) not null,    //第二个为name，类型为varchar，长度为10，不为空
    -> address varchar(50) default 'nj',     //第三个为address，类型为varchar，长度为50，默认为nj
    -> age int(3) not null);           //第四个为age，类型为int，长度为3，不为空
Query OK, 0 rows affected (0.01 sec)

mysql> insert into biao (name,address,age) values ('wangwu','bj',20),('zhaoliu','sh',34);   //给表内写数据，id设置为自动增长，所以不需要定义。
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from biao;                                                              
+----+---------+---------+-----+
| id | name    | address | age |  
+----+---------+---------+-----+
|  1 | wangwu  | bj      |  20 |
|  2 | zhaoliu | sh      |  34 |
+----+---------+---------+-----+
2 rows in set (0.00 sec)
//成功创建

创建索引的方法有以下几种

1）创建普通索引

mysql> create index index_age on biao (age);     //将age定义为索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from biao;       //查看索引
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| biao  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| biao  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2）创建唯一索引

mysql> create unique index unique_name on biao (name);     //创建唯一索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from biao;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| biao  |          0 | PRIMARY     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| biao  |          0 | unique_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

3）创建主键索引

有两种方式，一种是在创建表的同时创建主键，主键索引自动创建。

mysql> create table biao add primary key id；

另一种是已经创建了表，没有指定主键，然后修改表加入主键，主键索引会自动创建

mysql> alter table biao add unique index_name (name);   
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from biao;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| biao  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| biao  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

4）全文索引

全文索引目前只有使用MyISAM类型表的时候有效（MyISAM是默认的表类型）。全文索引可以建立在text、char或者carchar类型的字段或者字段组合上，创建表时指定或修改表时指定全文索引，命令格式如下

create table 表名（列明 text，fulltext（列名））engine=MyISAM；
alter table 表名 add fulltext（列明）；

使用show index 命令查看创建的索引，可以看到索引类型是fulltext，表示全文索引

show index from 表名\G；

5）多列索引

多列索引只需要在创建索引时指定多列即可

create index 索引名字 on 索引名字 （索引1，索引2）

6）查看索引

查看索引的命令前面已经使用了，有两种命令可以查看索引，他们的显示结果完全相同。命令格式如下：

show index from 表名；
或
show key from 表名；

在命令后面加上‘\G’，可以竖向显示索引的信息

show keys from 表名\G；

6.删除索引

drop index 索引名 on 索引名；    //直接删除索引
alter table 表名 drop index 索引名； //修改表时删除索引
alter table 表名 drop primary key；   //删除主键索引

修改表结构，删除某一列，如果此列有索引存在，响应的索引也会被删除

alter table 表名 drop column 索引名；

二、MySQL事务

数据库事务是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行。正常情况下，这些操作将顺利进行，最终操作成功。但是，如果在这一系列过程中任何一个环节除了差错，数据库中所有信息都必须保持第一步操作前地状态不变。否则，数据库的信息将会一片混乱而不可预测。

1.事务的概念

• 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
• 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
• 适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
• 通过事务的整体性以保证数据的一致性
• 如果事务成功了一部分，一部分未成功，则执行回滚，回到事务的起点，重新开始操作

2.事务的ACID特性

事务具有四个特性：原子性（automicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。

1）原子性

事务是一个完整的操作，各元素是不可分的，即原子的。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

2）一致性

当事务完成时，数据必须处于一致状态；在事务开始之前，数据库中存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务成功完成时，数据必须再次回到已知的一致状态

3）隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

4）持久性

事务持久性指不管系统是否发生故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会被永久地保留在数据库中

3.事务的操作

1）MySQL操作事务

默认情况下MySQL的事务是自动提交的。之前我们用SQL操作数据库时，一条语句执行后，系统会自动执行事务提交。当需要吧一组语句作为一个事务提交时，需要手动对事务进行控制。

• 手动控制事务有两种方法。
  • 使用事务处理命令控制
  • 使用set设置事务的处理方式

1.1 使用事务命令控制事务

MySQL中使用命令控制事务需要用到3个命令。

• begin：开始一个事务
• commit：提交一个事务
• rollback：回滚一个事务

1.2 使用set设置控制事务

前面已经提到MySQL默认是自动提交事务，也可以修改为不自动提交，使用set命令操作

• set autocommit=0：禁止自动提交
• set autocommit=1：开启自动提交

三、视图

1.视图的概念

• 视图是一张虚拟的表，数据不存在视图中，视图是真实表的映射数据。例如：水中捞月是典型的视图
• 利用条件筛选，分组，排序等产生出一个结果集（结果集保存在内存中），并且做成持久化保存（保存映射）
• 视图占用资源小，真实表中数据产生变化，会影响到视图。

2.创建与查询视图

1）创建视图

create view <视图名称>
create view info_view as select id,name,age from info 条件
多表相连的条件是on
单表相连的条件是where
查询视图
select * from info_view;
select name,age from info_view;

2）查询多表相连

select info.id,info.name,info.age,info,score,info.addr,hob.hobname from info inner join hob on info.hobby=hob.id; '//从info表和hob表中选择info.id,info.name,info.age,info,score,info.addr,hob.hobname且info表中的hobby=hob表中的id'
'//inner join：内部相连'

3）创建多表相连查询视图

create view info_hob_view as select info.id,info.name,info.age,info,score,info.addr,hob.hobname from info inner join hob on info.hobby=hob.id;	'//将刚刚的多表相连查询创建名为info_hob_view的视图'
select * from info_hob_view;	'//查看视图'

3.视图的特点

• 可以针对不同用户创建不同的视图（不同权限的用户浏览不同的信息）
• 简化了sql的操作
• 相比之下安全性较高

总结

• 数据库索引分为普通索引、唯一性索引、主键索引、全文索引、多列索引。
• 数据库索引可以协助快速查询表中数据，但并不是任何字段都需要创建索引。
• 数据库事务的ACID特性
• MySQL事务命令有begin、rollback、commit、savepoint