MySQL71-75：索引、视图、数据库设计范式

高开低走。

已于 2022-10-05 09:57:54 修改

阅读量166

点赞数

分类专栏：锦到说MySQL 文章标签：数据库 java sql

于 2021-03-19 16:20:40 首次发布

本文链接：https://blog.csdn.net/qq_43568982/article/details/114996405

版权

锦到说MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

开始时间：2021-03-19

索引

相当于字典目录，缩小查找范围，提高查询效率

适用范围：

数据量庞大。(根据客户的需求,根据线上的环境)
该字段很少的DM操作。（因为字段进行修改操作，索引也需要维护)
该字段经常出现在where子句中。（经常根据哪个字段查询)

索引工作原理,事先将字段中内容进行排序，在WHERE命令进行定位时,避免对表中所有的数据行进行遍历将会提升查询速度

查看某个语句的执行计划，了解本次查询过程中，是否用了已经创建的索引
explain +完整句子

mysql> select ename,sal from emp where sal=5000;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
//type=all 是最烂的情况

创建好索引再执行这个语句
create index XXX on 表名(字段名);

mysql> create index emp_sal_index on emp(sal);

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
//type=ref

此时rows=1，速度快了

如果我们把这种查询放在sal<1000的情况上，可以得到range级别

mysql> explain select * from emp where sal<1000;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | range | sal_index     | sal_index | 9       | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
#type=range

优化后最低要求达到range级别，一般达到ref，最高是const，const就要求以主键来建立索引，实际上开发是很少这样做的

查看已有的索引
show index from 表名

删除索引
drop index XX on 表名;

添加了索引后（可能是内存，可能是硬盘文件），索引会自动排序，比如按照字母来排，那么又会细分为首字母，第二个字母按照ABCD划分区。
查询的时候先定位第一个区，再进行细分，提高了检索效率

通过B-Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的"物理地址"，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

原表中每个子段都对应了物理地址，建立索引后，后面的查询是直接查物理地址了

select ename from emp where name=‘Smith’;
create index emp_index on emp(ename);
select ename from emp where 物理地址='XXX';

分类

单一索引	给单个字段添加索引
复合索引	给多个字段联合起来添加1个索引
主键索引	主键上会自动添加索引
唯一索引	有unique约束的字段上会自动添加索引

模糊查询，第一个通配符是%时，索引会失效

视图(view)

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
存储查询语句，便于调用
即常驻形式的临时表

作用

提高了查询语句复用性，避免了在多处地方重复进行查询语句开发行为
隐藏业务中涉及表关系，开发人员通过视图进行操作时是不会知道其具体
操作的表

//新建一个table，防止操作视图后改变原表
mysql> create table emp_bak as select * from emp;

create view XX as XX

//根据table创建视图
mysql> create view myview1 as select empno,ename from emp_bak;
//视图数据
mysql> select * from myview1;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+

update 视图 set XX=XX where 字段=XX;

//对视图进行修改
mysql> update myview1 set empno='7777' where ename='smith';

mysql> select * from myview1;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7777 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+

也可以对视图所在的表直接操作
增删改查都可以

//更新后原数据也随之更新
mysql> select * from emp_bak;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7777 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

视图方便于数据集的脱敏处理
可以隐藏表的细节

DBA命令

导出当前数据库数据

//可以指定导出的文件路径，此时多了一个.sql文件
C:\Users\Administrator>mysqldump mysql>D:\mysql_test.sql -uroot -p333


//再次登录mysql
C:\Users\Administrator>mysql -uroot -p333

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bupttest           |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

//删除刚刚导出的mysql
mysql> drop database mysql;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bupttest           |
| mydb               |
| performance_schema |
| test               |
+--------------------+

//创建数据库
mysql> create database mysql;

mysql> use mysql;
Database changed
//把刚刚导出的再导入
mysql> source D:\mysql_test.sql

数据库设计三范式

设计范式：设计表的依据。按照这个三范式设计的表不会出现数据冗余。

第一范式

第一范式:任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式

在第一范式的基础之上，所有非主键字段完全依赖主键,不能产生部分依赖。
在这里插入图片描述
所以不建议使用复合主键

多对多的话，建议使用三张表，关系表上两个外键
在这里插入图片描述

第三范式

建立在第二范式基础之上，所有非主键字段直接依赖主键,不能产生传递依赖。
中间字段直接依赖主键，该字段依赖中间字段，就叫传递依赖

一对多，两张表，多的表加外键

在这里插入图片描述
cname不能和下面的表合并，否则就不满足第三范式

提醒:在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

联查结果是笛卡尔积，效率确实低些

一对一表的设计

主键共享

在这里插入图片描述

外键唯一

一对多的变形
在这里插入图片描述
结束时间：2021-03-19

高开低走。

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录