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

最新推荐文章于 2024-07-11 21:41:59 发布

back2childhood

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量632

点赞数 1

分类专栏：数据库mysql/redis 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_44609676/article/details/130556412

版权

数据库mysql/redis 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

接上文
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语句。
另外需要注意，视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。