mssql 视图新建索引 mysql视图添加索引

转载

西门吹雪 2024-04-01 18:44:52

文章标签 mssql 视图新建索引 mysql java sql 数据库 文章分类 数据仓库大数据

1、索引（index）

索引类似于目录，根据索引进行查询可大大增加检索数据的效率。但是存储索引需要一定的内存，而且当数据修改时也要对索引进行维护，成本较高，所以不能随意地添加索引。
当出现以下情况时，可以考虑给字段添加索引：

表中数据量庞大
字段经常被检索，即出现在where语句中的字段
很少对字段进行修改

MySQL中可通过show index from 表名;查看当前表中添加索引的字段，建立和删除索引的语句分别为：create index 索引名 on 表名(字段名);、drop index 索引名 on 表名;，下面通过有无索引的查询比较说明性能优劣。

//假设有员工表emp
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
//无索引查询工资为5000的员工信息，查询了14条记录
mysql> explain select * from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
//有索引查询工资为5000的员工信息，查询了1条记录
mysql> create index emp_sal on emp(sal);
mysql> explain select * from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal       | emp_sal | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

需要注意的是具有主键约束和唯一约束的字段默认添加索引，分别称为主键索引、唯一索引，除此之外，和单一索引相对的是复合索引。
根据复合索引进行查询的效果好坏依赖查询语句，好比有两个字段姓和名建立了复合索引，单纯的查名与没有索引效果一样，查姓和姓名都会提高速度，这与索引的底层实现原理—B+树的建立有关。

2、视图（view）

视图可以理解为将表中的部分数据拿出来当作一张虚拟表，通过操作视图里的数据从而达到修改原表数据的目的。这样做的好处就是可以隐藏表的实现细节，做到数据的保密。
创建和删除视图的语句分别为：create view 视图名 as select语句;、drop view 视图名。

//创建视图，由emp表部分字段构成
mysql> create view emp_view as select ename,sal from emp;
//修改视图中的数据
mysql> select * from emp where ename='KING';
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
mysql> update emp_view set sal=6000 where ename='KING';
//原表数据随之修改
mysql> select * from emp where ename='KING';
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 6000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

3、数据库设计三范式

设计表的过程中要根据设计范式，以防止出现数据冗余的情况。

1、第一范式

任何一张表都应该有主键，且每个字段原子性不可再分。
如果有员工表如下，显然有两个问题：员工编号重复，给后续工作带来麻烦；联系方式中邮箱和电话没有分为两个字段。

员工编号	员工姓名	联系方式
1	Alice	邮箱、电话
1	Bob	邮箱、电话

2、第二范式

在第一范式的基础上，要求所有非主键字段完全依赖主键，不能产生部分依赖。
往往采用复合主键时会出现部分依赖现象，不同的字段依赖复合主键的部分主键，这就涉及到多对多关系的表设计：设计2张具有依赖关系的表和1张关系表，其中关系表采用两个外键。例如学生表（学号、姓名）以及课程表（课程编号、课程名称）的设计。

3、第三范式

在第二范式的基础上，要求所有非主键字段不能传递依赖于主键。
第三范式涉及到一对多关系的表设计：设计2张具有依赖关系的表，其中‘多’的那张表采用一个外键。例如学生表（学号、姓名）以及班级表（班级编号、班级名称）的设计。
具有一对一关系的表理论上设计成一张表即可，但是实际应用中可能对表的一部分数据经常查询，另外一部分可作为补充信息，这个时候就要设计成多张表。
一对一关系的表有两种设计方案：主键共享、外键唯一：