学习SQL知识优化篇之范式设计及索引优化

最新推荐文章于 2023-07-10 22:06:46 发布

申未曲

最新推荐文章于 2023-07-10 22:06:46 发布

阅读量204

点赞数

分类专栏：关系数据库文章标签： sql

本文链接：https://blog.csdn.net/qq_16546235/article/details/108949535

版权

关系数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

首先在设计数据库之前，就应该选好工具，在工具完备的情况下，再进行逻辑查询优化以及物理查询优化，必要的情况下，还需要找外援（缓存数据库）提高数据库效率。

范式设计

数据表的范式有哪些？
一共有五大范式，分别为1NF，2NF，3NF，DCNF，5NF。
就是第一范式、第二范式、第三范式、巴斯科德范式、第五范式（又称完美范式）
范式的等级越高，数据表的冗余就越少。并且高范式会包含低范式的所有内容。

1NF：每个DBMS都支持的范式，保证数据表属性的原子性，即不可分割的字段。
2NF：当前表的非主属性都要与表中的候选键，有完全依赖关系。即，表中的非主属性，不允许依赖于两个候选键。

例如：球员表中有球员编号，姓名，年龄，比赛编号，比赛地点，得分
这张表就有两个依赖关系，不满足2NF范式要求：
球员编号（姓名、年龄）
比赛编号（地点、得分）

应该将改变分为两张表↓
球员表（球员编号、球员姓名、球员年龄、比赛编号）
比赛表（比赛编号、比赛地点、得分）

1.减少数据冗余（若一个球员要参加M场比赛，那么球员的姓名与年龄字段就冗余了M-1条数据。若一场比赛有N个球员要参加，那么比赛场地与比赛得分就冗余了N-1条数据。）
2.避免插入异常（若插入表时，还没有比赛要进行，就会不允许插入）
3.避免删除异常（若比赛期间有个人员不参加比赛，若没有保存比赛表就会将比赛信息也删除。）
4.避免更新异常（若比赛的时间发生了改变，那么所有参加这场比赛的所有数据都需要进行修改，否则就会出现比赛信息不一致。）

3NF：不允许表中有非主属性传递依赖于候选键。即：有一张表中，有球员编号、球员名称、球队名称、球队教练，这其中球队名称依赖于球员球员编号，而球队教练又依赖于球队名称。那么球队教练与球员编号就存在传递依赖。

这就需要将表拆成以下的表↓
球员表（球员编号、球员名称、球队编号）
球队表（球队编号、球队教练）

那么第三范式还有哪些问题呢？

若有一张仓库管理表（仓库编号、仓库名、仓库管理员、产品名称、产品数量）

这张表中没有字段能拆分，保证字段原子性，满足第一范式（1NF）。非主属性（仓库名、仓库管理员、产品名、产品数量）都完全依赖于候选键（仓库编号），满足第二范式（2NF）。这张表中，非主属性与候选键之间没有传递依赖关系，满足第三范式（3NF）。

但是还是存在了问题。
1.若插入一张仓库表时，还没有产品进行入库，那么插入就会产生异常。
2.若仓库管理员需要修改，那么就需要修改N条数据。
3.若仓库中的所有商品都卖出去了，那么就会将仓库信息都删除。

要解决这些问题就需要使用巴斯-科德范式（BCNF），满足3NF的基础上，消除了候选键对非主属性的传递依赖或部分依赖。

需要将仓库管理表拆分为：
仓库表（仓库编号、仓库名称、仓库管理员）
商品表（仓库编码、商品名称、商品数量）

反范式
因为范式设计会降低数据表中的冗余字段，那么可能在某些情况下，会使得查询的效率变低，因为若要查询一张报表，需要关联N张表的数据就肯定会导致查询速度变慢。

比如：若需要查询商品评论表的数据，那就需要关联商品表，获取商品名称，需要关联用户表，获取用户名称。数据量小的时候影响不大，那么数据量达到百万级别的时候呢？

这边使用存储过程批量新增百万个商品、百万个用户、百万条商品评论。

CREATE DEFINER=`root`@`localhost` PROCEDURE `batchInsert`(in start int,in max_sum int)
BEGIN
	DECLARE  p_id VARCHAR(36);
	DECLARE  pc_id VARCHAR(36);
	DECLARE  p_comment VARCHAR(255);
	DECLARE  user_id VARCHAR(36);
	DECLARE  user_name VARCHAR(36);
	DECLARE  p_name VARCHAR(12);
	DECLARE  p_standard VARCHAR(36);
	DECLARE  p_time DATETIME;
	DECLARE  age VARCHAR(36);
	DECLARE  i INT DEFAULT 1;
	SET autocommit = 0;
	REPEAT
	   SET i = i + 1;
		 SET user_name = CONCAT("USN",i);
		 SET p_comment = SUBSTR(MD5(RAND()),1,20);
		 SET p_name = CONCAT("pname",i);
		 SET p_standard = CONCAT("S",i);
		 SET user_id = CONCAT("u",i);
		 SET pc_id = CONCAT("pc",i);
		 SET p_id = CONCAT("p",i);
		 SET age = RAND(20);
		 SET p_time = CURRENT_TIMESTAMP();
		 
		 
		 INSERT INTO users(id,user_name,age,sex) values(user_id,user_name,age,1);
		 INSERT INTO product(id,product_name,product_standard) values(p_id,p_name,p_standard);
		 INSERT INTO product_comment(id,p_comment,p_user,p_time,p_product) values(pc_id,p_comment,user_id,p_time,p_id);
		 
		 
	UNTIL i = max_sum END REPEAT;
	COMMIT;
END

使用关联查询的语句

SELECT
	p.product_name,
	u.user_name,
	pc.p_comment,
	pc.p_time 
FROM
	product_comment AS pc
	LEFT JOIN users u ON pc.p_user = u.id
	LEFT JOIN product AS p ON pc.p_product = p.id;

查询的时间为11.491s，这是非常慢的。
在这里插入图片描述

这就可能需要将表设计为反范式。运行一定数据的容易，以提高查询效率。以空间换时间。

就将商品评论表改为（商品名、用户名、商品评论、评论时间）允许商品名与用户名的冗余，以提高查询的速度。

速度提升了10秒左右，由于本机物理设备不好，这已经是最高效率了。
在这里插入图片描述

索引

什么是索引？
索引相当于书本的目录，能直接告诉你，你想要的东西在哪里。提升查询的效率，但是呢，若查询数据量小（小于1000条数据）或者字段重复度大于10%的不建议添加索引，比如说性别。

口说无凭，来测试一下。

测试1：数据量小的情况（39条数据）
这是没有添加唯一索引的查询。（0.026s）
在这里插入图片描述
这是添加了唯一索引的查询（0.030s）

由此可知，若数据量小的时候，加索引与没有加索引是没有什么区别，可能速度还会更慢。

测试2：给性别设置索引，这是一种特殊情况，背景在女儿国中，100w人有10个人是男人，只有总人口的10w分之1。

没有设置索引的情况（0.391s)
在这里插入图片描述
给性别设置了普通索引（0.036s）

通过这就可以看出，索引的价值是帮助我们快速定位。若设置索引的数据有很多，那么就没有必要设置索引。

索引不是万能的，有时候加了索引反而会使查询速度变慢。

索引的种类
索引有四个类型，普通索引、约束索引、唯一索引、全文索引。
普通索引：没有任何所属条件的索引。
约束索引：在普通索引上增加了约束条件。
唯一索引：在约束索引上增加了唯一性约束，即主键索引。
全文索引：不常用，可以使用全文搜索引擎，ElasticSearch和Solr代替。

前三种（普通索引、约束索引、唯一索引）统称为一类索引，只是对数据的约束性做逐渐的提升。

按物理分类的话，索引可分为聚集索引和非聚集索引。

聚集索引：按照主键进行排序来存储，这样查询的数据行会很有效。直接可以找到索引的位置，数据行就跟在索引之后。

非聚集索引：在数据库系统中，单独存储索引，没有将数据等放到索引之后。所以要查询到数据就需要两步，第一步先找到索引，第二步再通过索引找到对应的数据。

聚集索引查询行的效率高，但是增、删、改的效率就没有非聚集索引高。因为聚集索引是使用顺序存储，那么增、删、改的时候顺序可能是随机的，那么就需要修改数据后，重新再排序。

联合索引为多个列组合而成的索引。创建联合索引的时候，要注意列的排序。比如：创建的时候顺序是（x,y,z）那我们使用的时候确实（y,z,x）那么该索引就会失效。

联合索引是左侧匹配原则，从左侧开始进行匹配，若没有则所有就会失效。

索引是一种数据结构
我们先判断索引的数据结构的好坏，因为索引是保存在磁盘上的（为了防止数据丢失），那么就需要进行磁盘的I/O操作。若I/O要操作的很多，就说明这数据结构不咋样。

二叉树结构：二分查找法，是一种高效的数据检索方式。时间复杂度为 O（log_n）

二叉树分布，我们先给定一个根节点，然后判断小于根节点的放左边，大于根节点的放右边。

我们将（30,28,18,7,12,38,88,99,33）生成一个二叉树

在这里插入图片描述
可能会出现极端的情况，深度非常大
(2,3,4,7,11,14）

在这里插入图片描述
上图也是二分树，但是退化成了一条链表了，时间复杂度就为O(n)。为了解决这个问题，大神们提出了平衡二叉树，就是给二叉树增加了约束（左右树的高度差不超过1）。

若平衡保持了5个节点，每进入一个节点都需要进行一次I/O操作，那么就需要进行5次操作。虽然平衡二叉树比较的效率高，但是同样的深度也高。若深度为1000，则有可能需要进行1000次I/O操作，那堆磁盘的影响是不可想象的。

B树
二叉树，会有很深的深度，可能会有很多次对磁盘的I/O操作，效率就很低。那么就不能仅仅只是分两个叉，就需要分出M个叉出来，这就是B树。
B树又称为（Balance Tree），就是平衡的多路搜索树。提高了树的广度，降低了树的深度从而减少了I/O流的操作，提高了搜索的效率。

B树的特点：每个根节点有M个子节点（中间节点），中间节点的范围又[2,M]之间。若每个中间节点有K个关键字，那么对应的有K+1个指针，则每个中间节点就有K+1的孩子节点（叶子节点），同时B树叶子节点与非叶子节点都会存储索引以及存储数据。

在这里插入图片描述

以上图为例，若要查找关键字31，根节点判断31<38，取得P1指针指向磁盘1，然后判断31>30获得P3指针，指向磁盘6，磁盘6中有31,34。就取得了31关键字。

B+树
B+树对B树做了改进，与B树有以下的不同处。

1.每个节点中有关键字K，则对应的子节点也是K个。B树是K+1个。
2.每个子节点都有一个关键字（最大或者最小）在父节点中。
3.非叶子节点不存储索引或数据结构，只有叶子节点才存储。B树中非叶子节点也存储。
4.所有关键字都在叶子节点中，叶子节点后有指针指向另一个叶子节点，从而变成了链表。

在这里插入图片描述
根节点中有（4,20,38）对应的三个中间节点分别为（4,10,15）（20,28,33）（38,45,54）这三个中间节点分别有三个子节点（4,5,7）（10,13,14）（15,17,19）、（20,25,27）（28,30,31）（33,35,37）、（38,41,44）（45,47,53）（54,57,58）。这可以看出，每个父节点都是由子节点中的的最小关键字组合而成，叶子节点中就包含了所有的关键字。

那么此时若要查找关键字28
1.在根节点中判断20<28<38，则取到P2指针，P2指针指向磁盘2
2.在磁盘2中查找，判断28=28，则取得P2指针，P2指针指向磁盘8
3.磁盘8中包含，28,30,31关键字，则取得28关键字。

B+树与B树对比
首先，B+树的查询效率更稳定，因为B+树一定会在叶子节点中查到索引，而B树中可能在非叶子节点就查到了索引。
其次，B+树的查询效率更高，因为B+树比B树更矮胖，即广度更广，深度更浅。

MySQL的Hash索引
键值Key通过Hash映射找到对应的桶，桶中存放的是内存指针，这就可以找到键值Key对应的指针，然后取得指针指向的数据行。Hash检索的效率是非常高的，往往只需要一次检索就可以找到对应的数据。但是若是有hash冲突的话，就会在桶中进行键值key的检索，这往往会降低检索的效率。因为若要解决Hash冲突，就需要使用链表方式，一个一个检索，知道检索到对的数据行，直接将原来的时间复杂度O(1)编程了O(n)。

对比与B+树来说，检索的效率是更高效的。因为B+树需要从根节点一直检索到叶子节点，才能检索到对应的数据。

但是Hash索引同样也有限制：
1.不支持范围查询例如：where price>2000。只支持等值比较查询（IN 、= 、<>）
2.联合索引中不支持左侧匹配原则，即联合索引中取其中一个或者几个来进行查询，索引不会生效，只能使用全部的列来查询。
3.Hash索引不是按照索引值来排序的，所以也不能进行排序。
4.Hash索引中不保存数据，只保存了索引，所以不能取索引值来避免读取行。
5.Hash检索的效率是高效的，除非遇到Hash相同造成Hash冲突，这就会由Hash表变为了链表，一个一个查询直到查询出所有符合的数据行。若hash冲突很多的话，维护索引的代价就比较高。

同时InnoDB支持自适应Hash索引，这也是InnoDB的三个特色之一。

自适应索引就是在B+树上，就是当某个索引值使用得很频繁的时候，就会在B+树上建一个Hash索引，同时享用B+树和Hash索引的优点。

什么时候使用索引？

字段的数值有唯一性限制，比如ID。
经常需要在Where语句中进行查询的字段。
需要经常使用GROUP BY 的列
UPDATE、DELETE的where查询条件的字段也需要添加索引。
DISTINCT 字段也可以添加索引
JOIN 连表查询的字段，但是要注意的是，两个表要进行连接的字段若要建立索引，那么类型就必须一致。

测试1：使用where条件查询，没有设置索引

之前通过存储过程建立一张百万级别的表数据。

select * from product_comment where p_product='p100074';

没有给p_product字段设计索引，那么找到这条数据花费的时间是：1.309s
在这里插入图片描述
如果p_product设置了普通索引，查询时间为0.051s效率提升了25/1

测试2：使用UPDATE将某个用户的评论时间改为为当前时间。没有设计索引。花费时间1.676s

UPDATE  product_comment SET p_time = CURRENT_DATE() WHERE p_user = 'st100074';

在这里插入图片描述
将p_user设计索引。只花了0.042s，效率提升了将近40/1

测试3：若我们将所有用户的评论去重，没有设计索引，花费时间2.959s

select DISTINCT(p_user) from product_comment order by p_user asc;

在这里插入图片描述
将p_user设计索引，花费时间为0.676s，效率提升4/1

那么什么情况下，索引会失效？

索引列使用算数表达式例如：price+1 > 2000这样price的所有就会失效。
使用函数，例如：DATE（p_time）那么p_time的索引就会失效。
联合函数，没有遵守最左匹配原则，那么索引失效。

如果索引失效了，要使得索引继续生效，就需要重写SQL语句。

测试1：给评论发布时间设计索引，然后使用函数让索引失效。

CREATE INDEX product_time ON product_comment(p_time);

SELECT * FROM product_comment WHERE DATE(p_time) BETWEEN '2020-10-01 00:00:00' AND '2020-10-08 20:00:00';

查询 2020-10-01 00:00:00到2020-10-08 20:00:00之间的所有评论，查询时间为1.435s修改一下语句

SELECT * FROM product_comment WHERE p_time BETWEEN TIMESTAMP('2020-10-08 00:00:00') AND  TIMESTAMP('2020-10-08 20:00:00');

重写了SQL语句后，查询时间为0.038s，速度提升了37/1
在这里插入图片描述
测试2：使用联合索引，但是顺序错乱。
首先创建一个联合索引（id,user_name）

CREATE INDEX relate_indexs ON users(id,user_name);

查询时没有参照最左匹配原则，而是直接使用user_name来查询，就会导致索引失效。

在这里插入图片描述
参照最左匹配原则，查询时间为0.030s，效率提升40/1。

SELECT * FROM users where id='st100590';
SELECT * FROM users where id='st100590' and user_name = 'US100590';

在这里插入图片描述

在极客时间SQL必知必会专栏里，学到了很多，也把学到的一些知识，按自己的理解转成了上述的文字。要在有限的日子里，一直学习无限的知识！加油！

申未曲

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
学习SQL知识优化篇之范式设计及索引优化

首先在设计数据库之前，就应该选好工具，在工具完备的情况下，再进行逻辑查询优化以及物理查询优化，必要的情况下，还需要找外援（缓存数据库）提高数据库效率。范式设计数据表的范式有哪些？一共有五大范式，分别为1NF，2NF，3NF，DCNF，5NF。就是第一范式、第二范式、第三范式、巴斯科德范式、第五范式（又称完美范式）范式的等级越高，数据表的冗余就越少。并且高范式会包含低范式的所有内容。1NF：每个DBMS都支持的范式，保证数据表属性的原子性，即不可分割的字段。2NF：当前表的非主属性都要与表.
复制链接

扫一扫