MySQL基础学习7-索引和视图

田某某a

已于 2023-07-25 14:18:35 修改

阅读量138

点赞数

分类专栏： MySQL学习笔记文章标签： mysql 学习数据库

于 2023-07-25 14:18:16 首次发布

本文链接：https://blog.csdn.net/m0_50696241/article/details/131916819

版权

MySQL学习笔记专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一、索引

1.索引是啥？

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。

索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

请添加图片描述

如果不添加索引，MySQL查询的时候就全表扫描

请添加图片描述

MySQL在查询方面主要就是两种方式：

全表扫描
根据索引检索

注意：

字典是可以根据a,b,c,d进行排序的，因为只有排序了才会有区间查找（缩小扫描范围其实就是扫描某个区间罢了）

所以在MySQL数据可中索引也需要排序

2.索引的实现原理：

请添加图片描述

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

###b+树的创建过程
根据根节点来判断，如果根节点有两个，小于两个中最小的那个，会被分配至左边，大于两数最大的数，会被分配至右边，在两数中间的数，被分配至中间

###b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

请添加图片描述

在MySQL当中，主键上和 unique字段上都会自动添加索引！

啥时候考虑给字段添加索引？

—1. 数据量庞大（需要测试）
—2.该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描

3.索引操作

3.1 索引怎么创建

# 给emp表的ename字段添加索引，起名enameIndex
mysql> create index enameIndex on emp(ename);

# 删除索引
# 将emp表上的enameIndex索引对象删除
mysql> drop index enameIndex on emp;

3.2 怎么查看索引

explain select * from emp where ename = 'KING';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

加了索引后

mysql> create index enameIndex on emp(ename);

mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | enameIndex    | enameIndex | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

3.3 什么时候索引失效

一、

select * from emp where ename like '%T'

# ename即使添加了索引，也不会走索引，为什么？
# 因为模糊匹配当中以'%'开头了！
# 尽量避免模糊查询的时候以'%'开头
优化的手段

二、索引列上进行了函数操作

当在索引列上进行函数操作时，如使用UPPER()或LOWER()函数对字符串进行大小写转换，或使用DATE()函数对日期进行格式转换，索引就会失效。因为函数会改变列的值，从而使得索引无法正确匹配。

三、索引列上进行了类型转换

当在索引列上进行类型转换时，如将字符串转换为数字，或将日期转换为字符串，索引也会失效。因为类型转换会改变列的值，从而使得索引无法正确匹配。

四、索引列上使用了NULL值

当在索引列上使用了NULL值时，索引也会失效。因为NULL值无法与其他值进行比较或匹配，所以无法使用索引。

五、索引列上的数据分布不均匀

当索引列的数据分布不均匀时，索引也会失效。例如，在一个包含10000个记录的表中，如果一个值的出现频率非常高，那么使用索引查询该值时，由于需要扫描的记录太多，反而会导致查询变得缓慢。

六、索引列上存在大量重复值

当索引列上存在大量重复值时，索引也会失效。因为索引是按照值来排序的，如果存在大量重复值，那么索引就无法正确排序，从而导致查询变得缓慢。

七、索引列上进行了隐式类型转换

当在索引列上进行隐式类型转换时，索引也会失效。例如，在一个VARCHAR类型的列上进行比较时，如果使用了数字，那么就会发生隐式类型转换，从而导致索引失效。

二、视图

1. 啥是视图

站在不同角度去看待同一份数据

2. 怎么创建视图对象？怎么删除视图对象？

create view kk as select * from t_user;
# 把select * from emp的查询结果当成一个视图创建出来
+---------------+
| a             |
| dept          |
| emp           |
| kk            |
| salgrade      |
| t_user        |
| trans         |
+---------------+

drop view kk;
# 删除视图

注意：

只有DQL语句（查询语句）才能以view的形式创建

3.用视图干啥？

可以面向视图对象进行增删改查，对视图对象的增删改善，会导致原表被操作。（通过对视图的操作，会影响原表）

# 原表
+------+------+------------+---------------------+
| id   | name | birth      | creat_time          |
+------+------+------------+---------------------+
|    1 | lisi | 1009-11-24 | 2023-07-13 20:12:32 |
|    1 | lisi | 1009-11-24 | 2023-07-13 20:12:47 |
|    1 | lisi | 1009-11-24 | 2023-07-13 20:12:54 |
+------+------+------------+---------------------+
# 视图
create view kk as select * from t_user;

# 再看原表
+------+------+------------+---------------------+
| id   | name | birth      | creat_time          |
+------+------+------------+---------------------+
|    1 | lisi | 1009-11-24 | 2023-07-13 20:12:32 |
|    1 | lisi | 1009-11-24 | 2023-07-13 20:12:47 |
|    1 | lisi | 1009-11-24 | 2023-07-13 20:12:54 |
| NULL | 李娟 | 2009-05-23 | 2023-07-18 19:48:40 |
+------+------+------------+---------------------+