当前位置：首页 > news >正文

mysql从零开始（4）----索引/视图/范式

news 2025/9/16 15:55:30

接上文
mysql从零开始（3）

索引

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
索引是需要排序的，索引存储成一个B-Tree数据结构

mysql查询方面有两种方式：全表扫描、根据索引检索

原理

在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。
在MyISAM存储引擎中，索引存储在一个.MYI文件中
在InnoDB存储引擎中索引存储在一个逻辑名称叫tablespace中
在MEMORY存储引擎当中索引被存储在内存当中
不管索引存储在哪里，索引在mysql当中都是一个B-Tree的形式存在。

创建删除查看

何时

数据量庞大，全表查询太费时
该字段经常出现在where的后面，以条件的形式存在，即该字段总是被扫描
该字段很少进行DML(insert delete update)操作，因为DML之后，索引需要重新维护

尽管索引可以极大提高查询的效率，我们也不应该随意添加索引，因为索引底层是B-Tree，索引过多的话会使索引维护起来的代价过大。
我们在查询的时候可以尽量通过主键字段、unique字段查询，效率较高、代价较小。

语法

# 创建索引
create index 索引名 on 表名(字段名);
# 删除索引
drop index 索引名 on 表名;
# 查看sql语句是否使用了索引检索
explain sql语句;
# 如果type类型是all，说明没有使用索引，如果是ref，说明使用了索引

索引失效

模糊查询

select * from test where name like '%T';

即使name加上了索引，也不能使用索引查找。所以在查找时应当尽量避免模糊查询的时候用%开头。

or

使用or做条件筛选时，只有or两边的字段都有索引时才会走索引。

复合索引

使用复合索引时，要使用左侧的列来查找，索引才能生效。如

create index test_id_name on test(id,name);
# 走索引
mysql> explain select * from test where id = 1;
# 不走索引
mysql> explain select * from test where name = '1';

运算

如果在where中索引列参与了运算，索引失效。

# ref
select * from test where id = 1;
# all
select * from test where id + 1 = 1;

函数

如果在where中索引列使用了函数，索引失效

# ref
select * from test where lower(name) = 'tom';

视图view

基础知识

视图就是站在不同的角度去看待同一份数据，对视图对象的增删改查会影响到原表！（个人感觉有点像c++的引用）
创建视图的语句为：

create view 视图名 as DQL语句；

比如，

# 创建一个test表的视图
create view test_view as select * from test;
# 删除视图
drop view test_view;

对视图对象的增删改查和对原表的操作一样。

作用

视图的主要作用是简化复杂的sql语句，就像在编程时我们会将常用的功能单独拿出去写成一个函数一样，视图也是起到这样的作用。
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用，为了防止每一次使用这个sql语句的时候都需要重新编写，我们就可以将其以视图对象的形式新建，在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发、利于维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。
另外需要注意，视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

范式

概念

数据库设计范式是数据库表的设计依据，按照范式进行设计，可以有效避免表中数据的冗余、空间浪费。数据库设计范式共有三个：

第一范式
第二范式
第三范式

第一范式

必须有主键，并且每一个字段都是原子性不可再分

个人信息
1tom
2jack

上面这个表就是不满足第一范式的，他没有主键，即使将个人信息作为主键也不符合，因为个人信息可以分为身份id和姓名

第二范式

满足第一范式
所有非主键字段必须完全依赖主键，不要产生部分依赖

ID	名字	朋友ID	朋友姓名
11	tom	21	T
12	jack	22	J
13	sun	21	T
11	tom	22	J

这张表就是典型的多对对关系：每个人都可能有多个朋友。
首先先修改上面这个表使其满足第一范式：ID和朋友ID联合作为主键。
但是此时仍然不满足第二范式，因为第二范式要求所有非主键字段必须完全依赖主键，在此刻名字依赖于ID，朋友名字依赖于朋友ID，这成为了部分依赖（复合主键时），这就需要分表。

ID(PK)	姓名
11	tom
12	jack
13	sun

朋友ID(PK)	朋友姓名
21	T
22	J

id(PK)	姓名(FK)	朋友姓名(FK)
1	11	21
2	12	22
3	13	21
4	11	22

第三范式

满足第二范式
要求所有非主键字典必须直接依赖主键，不要产生传递依赖

ID(PK)	名字	老师ID	老师姓名
11	tom	21	T
12	jack	22	J
13	sun	23	S
11	tom	23	S

该表是典型的多对一的典型：一个老师可能有多个学生
该表满足第一范式：有主键
该表满足第二范式：主键不是复合主键，没有产生部分依赖。主键是单一主键
但是该表不满足第三范式：有传递依赖！因为老师姓名依赖于老师ID，老师ID依赖于ID，产生了传递依赖。应该更改为：

老师ID(PK)	老师名字
11	T
12	J
13	S

ID(PK)	姓名	老师ID(FK)
11	tom	21
12	jack	22
13	sun	23
11	tom	23

索引

原理