SQL性能优化系列——Hash索引的原理[六]

上一篇说了B+树的原理,这篇说一下Hash的原理和使用。Hash本身是一个函数,又被称为散列函数,它可以帮助提升检索数据的效率。打个比方,Hash就好像一个智能前台,你只要告诉它想要查找的人的姓名,它就会告诉你这个人的位置,只需要一次交互就可以完成查询,效率非常高。MD5就是Hash函数的一种。

Hash算法是通过某种确定性的算法(如MD5、SHA1、SHA2、SHA3)将输入变为输出。相同的输入永远得到相同的输出。如果想验证两个文件是否相同,只需要对两个文件进行Hash函数运算比较结果是否相同即可。

代码统计Hash检索效率

Python的数据结构中有数组和字典两种,其中数组检索类似于全表扫描,需要对整个数组进行检索。字典是Hash表实现的,存储的是key-value值,数据检索效率很高。

没有对比就没有伤害,例如:

实验1:在数组中添加10000个元素,然后分别对这10000个元素进行检索,最后统计检索时间。

import time
# 插入数据
result = []
for i in range(10000):
       result.append(i)
# 检索数据
time_start=time.time()
for i in range(10000):
       temp = result.index(i)
time_end=time.time()
print('检索时间', time_end-time_start)

运行结果:

检索时间为 1.2436728477478027 秒。

实验2:采用Hash表的形式存储数据,即在Python中采用字典方式添加10000个元素,然后检索这10000个数据,最后统计时间。

import time
# 插入数据
result = {}
for i in range(1000000):
       result[i] = i
# 检索数据
time_start=time.time()
for i in range(10000):
       temp = result[i]
time_end=time.time()
print('检索时间:',time_end-time_start)

运行结果:

检索时间为 0.0019941329956054688 秒。

通过对比,检索效率差别很大。因为Hash算法复杂度为O(1),数组检索数据的算法复杂度为O(n)。

MySQL中的Hash索引

采用Hash进行检索效率非常高,基本上一次检索就可以找到数据,而B+树需要自顶向下依次查找,多次访问节点才能找到数据,中间需要多次I/O操作,从效率上来说Hash比B+树更高。

Hash索引示意图如下:

键值key通过Hash映射找到bucket。在这里bucket指的是一个能存储一条或多条记录的存储单位。一个桶的结构包含了一个内存指针数组,其中的每一行数据都会指向下一条,形成链表结构,当遇到Hash冲突时,会在桶中进行键值的查找。

什么是Hash冲突?

如果桶的空间小于输入的空间,不同的输入可能会映射到同一个桶中,这时就会产生Hash冲突,如果冲突的量很大,就会影响读取的性能。

Hash索引与B+树索引的区别

1.Hash索引不能进行范围查询,而B+树可以。因为Hash索引指向的数据是无序的,B+树的叶子节点是个有序链表。

2.Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用),而B+树可以。因为Hash索引在计算Hash值时,是将索引键合并后再一起计算Hash值,所以不会对每个索引单独计算Hash值,因此如果用到联合索引的一个或几个索引时,联合索引无法被利用。

3.Hash索引不支持 ORDER BY 排序,因为 Hash 索引指向的数据是无序的,因此无法起到排序优化的作用,而B+树可以。同理,也无法使用Hash索引进行模糊查询,而B+树使用 LIKE 进行模糊查询的时候,LIKE 后面前模糊查询(%开头)的话可以起到优化作用。

对于等值查询来说,通常Hash索引的效率更高,不过当索引列的重复值很多效率就会很低。因为遇到 Hash 冲突时,需要对桶中的行指针进行比较,找到查询的关键字,非常耗时。所以,Hash 索引通常不会用到重复值多的列上,比如列为性别、年龄的情况等。

总结

Hash 索引存在很多限制,相比之下数据库中B+树索引的使用面更广。不过键值型数据库 Redis 存储的核心就是 Hash 表。

(本文完)

SQL性能优化系列——索引的原理[五]

1.为什么索引要存放到硬盘上?如何评价索引的数据结构设计的好坏?
2.使用平衡二叉树作为索引的数据结构有哪些不足?
3.B树和B+树的数据结构是怎样的?为什么常用B+树作为索引的数据结构?

既然前面说索引其实是一种数据结构,那么索引的数据结构究竟是怎样的?

如何评价索引的数据结构好坏

数据库服务器有两种存储介质:硬盘和内存。内存为临时存储,容量有限,且发生断电或故障重启时会造成数据丢失;硬盘属于永久性存储,所以需要把数据存储在硬盘上。

虽然内存读取速度很快,但还是需要将索引存到硬盘上,当在硬盘上进行查询时,也就产生了磁盘的I/O操作。相比内存的存取来说,磁盘I/O存取消耗的时间要高很多。通过索引查找某行数据时,需要计算磁盘I/O次数,次数越多,消耗时间越长。如果索引的数据结构尽可能减少磁盘的I/O操作,则消耗的时间也越少。

二叉树的局限性

二分查找法是一种高效的数据检索方式,时间复杂度为O(log2n),是不是二叉树就适合做索引呢?

先看最基础的二叉搜索树,搜索某个节点和插入节点的规则一样,假设搜索插入的数值为key:

1.如果key大于根节点,则在右子树中查找;

2.如果key小于根节点,则在左子树中查找;

3.如果key等于根节点,则直接返回根节点即可。

例如,对数列(34,22,89,5,23,77,91)创造出来的二叉树,如下图所示:

但是存在特殊情况,就是二叉树的深度非常大。比如给出的数据顺序为(5,22,23,34,77,89,91),创造出来的二叉树如下图所示:

能看出,第一个树深度是3,也就是说最多需要3次比较就可以找到节点,第二个树深度是7,最多需要7次比较才能找到节点。

第二棵树也是二分查找树,但是性能上已经退化成了一条链表,查找数据的时间复杂度变成了O(n)。为解决这个问题,人们提出了平衡二叉搜索树(AVL树),它在二分搜索树的基础上增加了约束,每个节点的左子树和右子树的高度差不得超过1,也即是说节点的左子树和右子树仍然为平衡二叉树。

注:常见平衡二叉树包括:平衡二叉搜索树、红黑树、数堆、伸展树。平衡二叉搜索树是最早提出来自平衡的二叉搜索树,当提到平衡二叉树时一般就是指平衡二叉搜索树。

上面说过数据查询的时间主要依赖于磁盘I/O的次数,如果采用二叉树的形式,即使通过平衡二叉树进行改进,树的深度也是O(log2n),当n比较大时,深度依然很高。比如:

每访问一个节点就需要进行一次磁盘I/O操作,对于上面的树来说,需要进行5次I/O操作。虽然平衡二叉树比较的效率高,但是树的深度也同样高,意味着磁盘I/O操作次数多,会影响整体查询效率。

如果针对同样的数据将二叉树改造成M叉树(M>2)呢?当M=3时,同样的31个节点可以由下面的三叉树存储:

能看到,当数据量大的时候,以及树的分叉M大的时候,M叉树的高度会远小于二叉树的高度。B树也就由此产生了。

什么是B树

B树英文名叫Balance Tree,平衡的多路搜索树,它的高度远小于平衡二叉树的高度,在文件系统和数据库系统中常用B树作为索引的数据结构。

B树数据结构如下:

B树作为平衡的多路搜索树,它的每一个节点最多包含M个子节点,M称为B树的阶。同时能看到,每个磁盘块中包括了关键字和子节点的指针。如果一个磁盘块中包括了x个关键字,那么指针数就是x+1个。对于一个100阶的B树来说,如果有3层的话最多可以存储约100万的索引数据,对于大量的索引数据来说,采用B数的结构是非常适合的,因为高度远小于二叉树。

一个M阶的B树有如下特性:

1.根节点的儿子数的范围是[2,M];

2.每个中间节点的包含k-1个关键字和k个孩子,孩子的数量=关键字的数量+1,k的取值范围为[ceil(M/2),M];(ceil函数为向上取整)

3.叶子节点包括k-1个关键字(叶子节点没有孩子),k的取值范围为[ceil(M/2),M];

4.假设中间节点节点的关键字为:key[1],key[2],。。。,key[k-1],且关键字按照升序排序,即key[i]<key[i+1]。此时k-1个关键字相当于划分了k个范围,也即是对应k个指针,分别为:p[1],p[2],…p[k],其中p[1]指向关键字小于key[1]的子树,p[i]指向关键字属于(key[i-1],key[i])的子树,p[k]指向关键字大于key[k-1]的子树;

5.所有叶子节点位于同一层。

上面的图所示的B树刚好符合上面5点特征。

那么如何用B树来进行查找呢?假设想要查找关键字9,步骤如下:

1.与根节点的关键字(17,35)进行比较,9小于17,得到指针p1;

2.按指针p1找到磁盘块2,关键字为(8,12),9在8和12之间,得到指针p2;

3.按指针p2找到磁盘块6,关键字为(9,10),然后就找到了关键字9.

什么是B+树

B+树基于B树做了改进,主流的DBMS都支持B+树的索引方式,比如MySQL。B+树和B树的差异如下:

1.有k个孩子的节点就有k个关键字。即孩子数量=关键字数量。

2.非叶子节点的关键字也会同时存在在子节点中,并且是在子节点中所有关键字的最大(或最小)。

3.非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都存在叶子节点中。而B树中,非叶子节点既保存索引,也保存数据记录。

4.所有关键字都在叶子节点中出现,叶子节点构成一个有序链表,而且叶子节点本身按照关键字的大小从小到大顺序链接。

下图就是B+树,阶数为3,根节点中的关键字1、18、35分别是子节点(1,8,14)、(18,24,31)和(35,41,53)中的最小值。每一层父节点的关键字都会出现在下一层的子节点的关键字中,因此在叶子节点中包括了所有关键字的信息,并且每一个叶子节点都有一个指向下一个叶子节点的指针,这样就形成了一个链表。

比如,要查找关键字16,B+树会自顶向下逐层查找:

1.与根节点(1,18,35)比较,16在1和18之间,得到指针p1(指向磁盘块2);

2.找到磁盘块2,关键字为(1,8,14),16大于14,得到指针p3(指向磁盘块7);

3.找到磁盘块7,关键字为(14,16,17),然后找到关键字16,所以关键字16对应的数据。

整个过程总共进行了3次I/O操作,看起来B+数和B树的查询过程差不多,但是B+树和B树的根本差异在于B+树的中间节点并不直接存储数据。这样好处有什么呢?

首先,B+树查询效率更稳定。“稳定”怎么理解呢?因为B+树每次只有访问到叶子节点才能找到对应的数据,而在B树中,非叶子节点也会存储数据,这样就会造成查询效率不稳定的情况:有时候访问到了非叶子节点就可以找到关键字,而有时需要访问到叶子节点才能找到关键字。

其次,B+树查询效率更高。因为B+树比B树更矮胖(阶数更大,深度更低),查询所需磁盘I/O也会更少。同样的磁盘页大小,B+树可以存储更多的节点关键字。

不仅是对单个关键字的查询上,在查询范围上,B+树的效率也比B树高。因为所有关键字都出现在叶子节点中,并通过有序链表进行了链接。而在B树中则需要通过中序遍历才能完成查询范围的查找,效率要低很多。(何谓中序遍历?中序遍历(LDR)是二叉树遍历的一种,也叫做中根遍历、中序周游。在二叉树中,中序遍历首先遍历左子树,然后访问根结点,最后遍历右子树。)

总结

磁盘的I/O操作次数对索引的使用效率至关重要。B树和B+树都可以作为索引的数据结构,在MySQL中采用的是B+树。通过以上对比,B+树在查询性能上更稳定,在磁盘页大小相同的情况下,树的结构更矮胖,所需进行的磁盘I/O次数更少,更适合进行关键字的范围查询。

(本文完)

SQL性能优化系列——索引概览[四]

1.索引种类?
2.什么情况需要创建索引?

索引的本质是帮我们快速定位想要查找的数据。

索引是万能的?

索引(Index),好比一本书的目录,可以快速定位与查找特定值,从而加快数据查询效率。

索引就是帮助数据库管理系统高效获取数据的数据结构。

如果不使用索引,就必须从第一条记录开始扫描,直到把所有数据表都扫描完才能找到目标数据。但是创建更多的索引就行了吗?

索引不是万能的,某些情况使用索引反而会降低效率。

索引的价值是帮我们从海量数据中查找目标数据,如果数据量少,使用索引与否效率区别不大。(1000行的数据量就很少,不需要创建索引。)另外,数据重复度大,比如高于10%时,也不需要对该字段使用索引,比如前面举的性别这个例子,就不需要创建索引,因为想要在100万行数据中查找其中的50万行(比如性别为男的数据),一旦创建了索引,就需要先访问50万次索引,然后再访问50万次数据表,这样加起来的开销比不使用索引可能还大。没有数据支撑的空谈都是耍流氓,下面做两个实验:

实验1:数据行数少的情况下,索引效率咋样?

heros_without_index表,数据共69条就不列进来了,下面只是数据结构:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for heros_without_index
-- ----------------------------
DROP TABLE IF EXISTS `heros_without_index`;
CREATE TABLE `heros_without_index`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `hp_max` float NULL DEFAULT NULL,
  `hp_growth` float NULL DEFAULT NULL,
  `hp_start` float NULL DEFAULT NULL,
  `mp_max` float NULL DEFAULT NULL,
  `mp_growth` float NULL DEFAULT NULL,
  `mp_start` float NULL DEFAULT NULL,
  `attack_max` float NULL DEFAULT NULL,
  `attack_growth` float NULL DEFAULT NULL,
  `attack_start` float NULL DEFAULT NULL,
  `defense_max` float NULL DEFAULT NULL,
  `defense_growth` float NULL DEFAULT NULL,
  `defense_start` float NULL DEFAULT NULL,
  `hp_5s_max` float NULL DEFAULT NULL,
  `hp_5s_growth` float NULL DEFAULT NULL,
  `hp_5s_start` float NULL DEFAULT NULL,
  `mp_5s_max` float NULL DEFAULT NULL,
  `mp_5s_growth` float NULL DEFAULT NULL,
  `mp_5s_start` float NULL DEFAULT NULL,
  `attack_speed_max` float NULL DEFAULT NULL,
  `attack_range` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_main` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_assist` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `birthdate` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10069 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

对name进行条件查询:

SELECT id, name, hp_max, mp_max FROM heros_without_index WHERE name = '刘禅'

对name字段建立索引后,heros_with_index表,数据完全相同,69条,数据结构如下:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for heros_with_index
-- ----------------------------
DROP TABLE IF EXISTS `heros_with_index`;
CREATE TABLE `heros_with_index`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `hp_max` float NULL DEFAULT NULL,
  `hp_growth` float NULL DEFAULT NULL,
  `hp_start` float NULL DEFAULT NULL,
  `mp_max` float NULL DEFAULT NULL,
  `mp_growth` float NULL DEFAULT NULL,
  `mp_start` float NULL DEFAULT NULL,
  `attack_max` float NULL DEFAULT NULL,
  `attack_growth` float NULL DEFAULT NULL,
  `attack_start` float NULL DEFAULT NULL,
  `defense_max` float NULL DEFAULT NULL,
  `defense_growth` float NULL DEFAULT NULL,
  `defense_start` float NULL DEFAULT NULL,
  `hp_5s_max` float NULL DEFAULT NULL,
  `hp_5s_growth` float NULL DEFAULT NULL,
  `hp_5s_start` float NULL DEFAULT NULL,
  `mp_5s_max` float NULL DEFAULT NULL,
  `mp_5s_growth` float NULL DEFAULT NULL,
  `mp_5s_start` float NULL DEFAULT NULL,
  `attack_speed_max` float NULL DEFAULT NULL,
  `attack_range` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_main` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_assist` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `birthdate` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10069 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

对name再查询:

SELECT id, name, hp_max, mp_max FROM heros_with_index WHERE name = '刘禅'

对比两种查询时间:

mysql> use buerguo;
Database changed
mysql> SELECT id, name, hp_max, mp_max FROM heros_without_index WHERE name = ‘刘禅’
-> ;
+——-+——+——–+——–+
| id | name | hp_max | mp_max |
+——-+——+——–+——–+
| 10015 | 刘禅 | 8581 | 1694 |
+——-+——+——–+——–+
1 row in set (0.00 sec)

mysql> SELECT id, name, hp_max, mp_max FROM heros_with_index WHERE name = ‘刘禅’
-> ;
+——-+——+——–+——–+
| id | name | hp_max | mp_max |
+——-+——+——–+——–+
| 10015 | 刘禅 | 8581 | 1694 |
+——-+——+——–+——–+
1 row in set (0.00 sec)

mysql>

通过观察,两种查询时间相差无几,装逼失败。。

其实想说的是:理论上数据量少时,创建name字段索引的查询时间要比没有创建索引的查询时间长,也就是说数据量不大的情况下,索引发挥不出作用。

实验2:性别(男或女)字段真的不应该创建索引吗?

如果一个字段的取值少,比如性别这个字段,通常不需要创建索引,但是特殊情况还是需要考虑的,比方说唐僧到女儿国了,100万女施主,男性只有5个(小白龙也算上),男性只是女性20万分之一了。

女儿国数据表user_gender:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_gender
-- ----------------------------
DROP TABLE IF EXISTS `user_gender`;
CREATE TABLE `user_gender`  (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `user_gender` tinyint(1) NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

要筛选男性可以使用语句,查询语句如下:(*也就三个字段:user_id,user_name,user_gender)

SELECT * FROM user_gender WHERE user_gender = 1

为了防止装逼失败,直接说结论吧。在未创建索引的情况下,运行效率不高,如果对user_gender字段创建索引呢?

SELECT * FROM user_gender WHERE user_gender = 1

同样的数据,运行结果相同,时间却只是未创建索引时的10分之1,查询效率提升了不止一个档次。

通过上面两个实验想要说明什么呢,其实索引价值就是快速定位,如果想要定位的数据有很多,那么索引就失去了他的使用价值,比如通常情况下性别字段男女比例平衡。不过有时考虑到这个字段中数值分布的情况,在实验2中,性别字段的数值分布非常特殊,男性比例非常少,使用索引查询效率就提升了。因此,不仅要看字段中的数值个数,还要根据数值的分布情况来考虑是否需要创建索引。

索引种类

从功能逻辑上说,索引共有4种:普通索引、唯一索引、主键索引、全文索引。

普通索引是基础的索引,没有任何约束,主要用于提高查询效率。

唯一索引是在普通索引的基础上添加了数据唯一性的约束,在一张表中可以有多个唯一索引。

主键索引是在唯一索引的基础上增加了不为空的约束,也就是NOT NULL+UNIQUE,一张表中最多只有一个主键索引(这是由主键索引的物理实现方式决定的,因为数据存储在文件中只能按一种顺序进行存储,但可以有多个普通索引或唯一索引)。

全文索引用的不多,MySQL中全文索引只支持英文。通常可以采用专门的全文搜索引擎如ES和Solr。

前三种索引是同一类索引,只是对数据的约束性逐渐提升。

按物理实现方式,索引分为两种:聚集索引和非聚集索引。非聚集索引又称为二级索引或辅助索引。

聚集索引可以按主键来排序存储数据,查找行更有效。举例:在一本字典中查找“数”这个字,直接在字典中找拼音“shu”的位置即可。这样找到了索引的位置,在它后面就是想要的数据行。

非聚集索引:数据库系统会有单独的存储空间存放非聚集索引,这些索引项是按照顺序存储的,但索引项指向的内容是随机存储的。即是说系统会进行两次查找,第一次先找到索引,第二次找到索引对应的位置的数据行取出。非聚集索引不会把索引指向的内容直接放到索引后面,而是维护单独的索引表(只维护索引,不维护索引指向的数据),为数据检索提供方便。举例:如果想要找“数”字,按照部首查找方式,先找到偏旁部首,然后这个目录告诉我们“数”字在第多少页,然后我们才去指定的页码找这个字。

(都快忘了字典咋用的了。。)

聚集索引与非聚集索引使用区别:

1.聚集索引叶子节点存储的就是数据记录,非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。

2.一个表只能有一个聚集索引,因为只能有一种排序存储的方式,但是可以有多个非聚集索引,也就是多个索引目录提供数据检索。

3.使用聚集索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚集索引低。

实验3:使用聚集索引和非聚集索引的查询效率

还以刚才的user_gender表举例,对比一下聚集索引和非聚集索引查询效率。通过建表语句看到,该表设置了user_id为主键也就是聚集索引的字段是user_id。此处查询一下user_id=90001的用户信息:

(把该表中唐僧5人踢出去然后空降了10位精壮的成年男子。。)

对user_id和user_name两种情况的查询结果如下:

mysql> SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001
-> ;
+———+—————-+————-+
| user_id | user_name | user_gender |
+———+—————-+————-+
| 900001 | student_890001 | 0 |
+———+—————-+————-+
1 row in set (0.00 sec)

mysql> SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = ‘student_890001’
-> ;
+———+—————-+————-+
| user_id | user_name | user_gender |
+———+—————-+————-+
| 900001 | student_890001 | 0 |
+———+—————-+————-+
1 row in set (0.54 sec)

可以通过看执行时间进行对比。

然后通过对user_name字段创建普通索引,再查询,运行时间也降低到0.05.s了。

结论:

1.对where子句的字段建立索引,可以大幅提升查询效率。

2.采用聚集索引进行数据查询,比使用非聚集索引的查询效率高。如果查询次数比较多,尽量使用主键索引进行数据查询。

 

除了业务逻辑和物理实现方式,索引还可以按照字段个数进行划分,分成单一索引和联合索引。索引列为一列时为单一索引,多个列组合在一起创建的索引称之为联合索引。

注意:创建联合索引需要注意创建时的顺序,因为联合索引(x,y,z)和(z,y,x)在使用的时候效率可能会存在差别。

需要说明的是联合索引存在最左匹配原则。也就是按照最左有限的方式进行索引的匹配。比如(x,y,z),如果查询条件是where x=1 and y=2 and z=3,就可以匹配上联合索引;如果查询条件是where y=2,就无法匹配上联合索引。

实验4:联合索引的最左原则

还是以user_gender表为例,把user_id和user_name设置为联合主键,对比查询效率。

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001 AND user_name = 'student_890001'

查询用时:0.046s

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001

查询用时:0.046s

然后看普通查询:

SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

查询用时:0.943s

可以看到使用联合索引(user_id,user_name)时,在where子句中队联合索引中的字段user_id和user_name进行条件查询,或者只对user_id进行查询,效率基本上是一样的;当对user_name进行条件查询时,效率会低很多,这是因为根据联合索引的最左原则,user_id在user_name的左侧,如果没有使用user_id直接使用user_name进行条件查询,联合索引就会失效。

总结

使用索引可以从海量数据中快速定位数据,不过索引也存在不足:占用存储空间、降低数据库写操作的性能等。如果有多个索引还会增加索引选择的时间。当使用索引时,需要权衡索引的利(提升查询效率)弊(维护索引所需的代价)。

思考

关于联合索引的最左原则指的是什么?使用联合索引时需要注意哪些地方?

(本文完)

SQL性能优化系列——反范式设计[三]

1.有了范式设计,为什么有时还需要反范式设计?
2.反范式设计的适用场景?存在哪些问题?

反范式设计

尽管数据表的设计有很多范式,但实际上在设计表的时候不一定要参照这些标准。因为在设计的过程中,有时为了性能和读取效率会违反范式化的原则,即通过增加少量冗余、通过空间换取时间的优化思路,对查询效率进行优化。

比如,查询某商品前1000评论,涉及两张表。

商品评论表:product_comment

字段 comment_id product_id comment_text comment_time user_id
含义 商品评论id 商品id 评论内容 评论时间 用户id
DROP TABLE IF EXISTS `product_comment`;
CREATE TABLE `product_comment`  (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `comment_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `comment_time` datetime(0) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`comment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10000 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

用户表:user

字段 user_id user_name create_time
含义 用户id 用户昵称 注册时间
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `create_time` datetime(0) NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

下面用这两种表进行模拟反范式优化。

模拟数据:两张百万量级数据表(通过存储过程实现)

用户表:随机生成100万用户

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_user`(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, interval RAND()*60 second);
INSERT INTO user(user_id, user_name, create_time)
VALUES((start+i), CONCAT('user_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

date_start用来定义注册的初始时间;

date_temp变量计算每个用户的注册时间,新注册的和上一个注册的间隔60s;

REPEAT。。UNTIL 。。END REPEAT循环,对max_num个用户的数据进行计算;

autocommit设置为0,这样等计算完成再统一插入,效率更高。

然后运行call insert_many_user(10000,1000000);调用存储过程,通过start和max_num两个参数对初始的user_id和要创建的用户数量进行设置。

 

商品评论表:随机生成100万条商品评论

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_product_comments`(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE user_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = substr(MD5(RAND()),1, 20);
SET user_id = FLOOR(RAND()*1000000);
INSERT INTO product_comment(comment_id, product_id, comment_text, comment_time, user_id)
VALUES((START+i), 10001, comment_text, date_temp, user_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

执行call insert_many_products_commonts(10000,1000000);调用存储过程。

 

反范式优化实验对比

如果查询10001这个商品的前1000条评论,sql:

SELECT p.comment_text, p.comment_time, u.user_name FROM product_comment AS p 
LEFT JOIN user AS u 
ON p.user_id = u.user_id 
WHERE p.product_id = 10001 
ORDER BY p.comment_id DESC LIMIT 1000

运行时长0.395秒,查询效率并不高。

关联查询product_comment和user,数据量超过百万量级,查询效率会降低。因为查询在两表上进行聚集索引扫描,然后嵌套循环,这样查询耗费的时间会有几百毫秒甚至更久。

如果想提升查询效率,可以考虑在商品评论表中添加用户昵称字段:user_name

这样形成了product_comment2:

DROP TABLE IF EXISTS `product_comment2`;
CREATE TABLE `product_comment2`  (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `comment_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `comment_time` datetime(0) NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`comment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10000 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

这样查询只需要:

SELECT comment_text, comment_time, user_name FROM product_comment2 WHERE product_id = 10001 ORDER BY comment_id DESC LIMIT 1000

优化后只需要扫描一次聚集索引,运行时间是之前的十分之一,查询效率明显提升。(难怪个别网站不允许修改昵称)

反范式存在的问题与适用场景

当冗余信息有价值或能显著提升查询效率的时候,就可以采取反范式优化。

此外,反范式优化常用在数据仓库的设计中,因为数据仓库通常存储历史数据,实时性要求不高,对历史数据分析需求强。此时适当允许数据冗余度,更方便数据分析。

数据仓库和数据库的使用区别:

1.数据库设计目的在于捕获数据,数据仓库设计目的在于分析数据;

2.数据库对数据增删改实时性要求高,需要存储在线用户数据,数据仓库存储的一般是历史数据;

3.数据库设计尽量避免冗余,但为了提升查询效率允许少量冗余,数据仓库设计更偏向反范式设计。

了解

最近正在基于Hadoop建设某国企的数据集市项目(地域性非全网),恰如老师所言,我们就是在遵循反范式的设计。

简要来说,我们把数据加工链路分为四层,从下到上依次为:ODS贴源层、DWD明细层、DWS汇总层和ADS应用层。

多源异构的业务数据被源源不断ETL到ODS贴源层之后,经过清洗、规范、转换、拼接等,生成各类宽表存储在DWD明细层;再根据业务模型设计,以这些宽表为基础,生成各类标准的指标数据存储在DWS汇总层;ADS层则基于DWS层的汇总指标再度组合,计算得出应用层数据,直接面向业务需求。

在这样的系统设计中,反范式不仅体现在“宽表”的设计中,更体现在数据加工链路的完整生命周期中——上层都是对下层的冗余。

 

SQL性能优化系列——设计范式[二]

1.数据库的设计范式都有哪些?
2.数据表的键都有哪些?
3.1NF/2NF/3NF指的是什么?

范式是数据表设计的基本原则,往往会被忽略,也可能不知不觉就用上了,只是不知道有个这玩意儿而已。

数据库的设计范式都有哪些

在设计关系型数据库模型时,需要对关系内部的各个属性间练习的合理化程度进行定义,这就有了不同等级的规范要求,这些规范要求被称为范式(NF)。可以理解为表设计结构需要满足的某种设计标准的级别。

目前关系型数据库共有6种范式。按照范式级别,由低到高分别是:1NF(第一范式),2NF(第二范式),3NF(第三范式),BCNF(巴斯-科德范式),4NF(第四范式)和5NF(第五范式,又叫做完美范式)。

数据表的范式设计越高阶,冗余度就越低,同时高阶范式一定满足低阶范式的要求,即类似常说的向下兼容,比如满足2NF的一定满足1NF,依次类推。

通常设计数据表时应尽量满足3NF。但有时候为了提升查询性能,又会刻意破坏范式规则,也就是反规范化,提升冗余度,降低表之间的关联性(上一篇说过了)。

数据表中的键都有哪些

数据表中的键(Key)由一个或多个属性组成。范式的定义会用到主键和候选键(主键和候选键可以唯一标识元组)。常用键和属性如下:

超键:能唯一标识元组的属性集叫做超键。

候选键:如果超键不包括多余的属性,那么这个超键就是候选键。候选键就是最小的超键

主键:用户可以从候选键中选择一个作为主键。

外键:如果数据表R1中的某个属性集不是R1的主键,而是另一个表R2的主键,那么这个属性集就是数据表R1的外键。

主属性:包含在任一候选键中的属性称为主属性。

非主属性:与主属性相对,指不包含在任何一个候选键中的属性。

通常,我们将候选键称之为“码”,把主键也称为“主码”。因为键可能是由多个属性组成的,针对单个属性,还可以用主属性和非主属性来区分。

数据表的设计范式都有哪些

1NF指的是数据库表中的任何属性都是原子性的,不可再分。事实上,任何DBMS都会满足第一范式的要求,不会将字段拆分。

2NF指的是数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。所谓完全依赖不同于部分依赖,也就是不能仅仅依赖候选键的一部分属性,而要必须依赖全部属性。以一场NBA比赛举个例子:

候选键(球员编号,比赛编号) → 非主属性(姓名,年龄,比赛时间,比赛场地,得分)

上面这个关系说明球员编号和比赛编号决定了球员姓名、年龄、比赛时间。。等数据。

但是这个数据表并不满足第二范式,因为数据表中的字段还存在如下对应关系:

(球员编号)→(姓名,年龄)

(比赛编号)→(比赛时间,比赛场地)

也就是说候选键中的某个属性决定了非主属性。这就是非主属性并非完全依赖候选键。这样可能会产生如下问题:

1.数据冗余:如果一个球员可以参加m场比赛,那么球员姓名和年龄就重复了m-1次。一场比赛也可能会有n个球员参加,那么比赛时间和比赛地点就重复了n-1次。

2.插入异常:如果要添加一场比赛,但是未确定参加比赛球员都有谁,那么就没法插入。

3.删除异常:如果要删除某个球员编号,如果没有单独保存比赛表的话,就会同时把比赛信息删掉。

4.更新异常:如果要调整某场比赛的时间,那么数据表中所有含有这场比赛的时间的记录都要进行调整,否则就会出现同一场比赛时间不同的情况。

为了避免上述情况,可以把球员比赛表设计成如下三张表:

球员表包含球员编号、姓名、年龄等属性;

比赛表包含比赛编号、比赛时间、比赛场地等属性;

球员比赛关系表包含球员编号、比赛编号、得分等属性;

这样每张表都符合第二范式,避免上述异常发生。1NF就是要字段属性是原子性的,2NF就是说每张表就是独立对象、一张表只表达一个意思。

3NF在满足2NF的同时,对任何非主属性都不传递依赖于候选键。就是说不能存在非主属性A依赖于非主属性B、B依赖于候选键的情况。举个例子:

还用球员信息表举例,字段包括:球员编号、姓名、球队名称和球队主教练。

球员编号决定了球队名称,球队名称又决定了球队主教练,这样非主属性球队主教练就会传递依赖于球员编号,因此不符合3NF的要求。其实球队名称和球队主教练两个字段用一个球队编号代替就行了。

总结

关系型数据库的设计都是基于关系模型的,在关系模型中存在4种键,这些键的核心作用就是标识。

后面提到的是范式,这些范式本身存在一些问题,可能会带来插入、更新、删除等异常情况,另一方面也可能会降低查询效率。因为范式等级越高,设计出来的表越多,多表关联查询时难免会影响到效率。

(本文完)

SQL性能优化系列——优化概览[一]

1.数据库调优的目标是什么?
2.数据库调优有哪些维度可以选择?
3.如何思考和分析数据库调优这事儿?

数据库调优的目标

简单说,数据库调优目标是为了让数据库运行更快、响应更快、吞吐量更大,但随着用户量不断增加,需要考虑并发问题,还有用户在进行不同业务操作时,数据库的事务处理和SQL查询都会有所不同。因此数据库调优目标还需要更精细的定位。如何确定调优目标呢?

1)用户反馈

通过用户的反馈,找到和数据相关的问题。

2)日志分析

通过查看数据库日志和操作系统日志等方式找出异常,定位问题。

3)服务器资源使用监控

通过监控服务器的CPU、内存、IO使用情况实时了解服务器性能使用,与历史情况对比。

4)数据库内部状况监控

数据库监控中,活动会话(Active Session)监控是一个重要指标,通过它可以清楚了解数据库当前的活动状态是否处于繁忙、是否存在SQL堆积等。

数据库调优有哪些维度可以选择?

数据库调优,调优对象是整个数据库管理系统,不仅仅局限于SQL优化,还包括数据库的部署配置、架构等。可以通过以下几个步骤梳理:

第一步,选择适合的DBMS

之前讲到了SQL和NoSQL两大阵营。在RDBMS中常用的由SQLServer、Oracle、MySQL等。如果对事务性处理及安全性要求比较高的话,可以选择商业数据库产品。这些数据库在事务处理和查询性能上都比较强。比如采用SQLServer,单表存储上亿条都没问题。如果数据表设计的好,即使不采用分库分表查询效率一样不差。

此外,也可以采用开源的MySQL,它有很多存储引擎可供选择,进行事务处理的话可以采用InnoDB,非事务处理可以采用MyISAM。

NoSQL阵营包括键值型数据库、文档型数据库、搜索引擎、列式存储和图形数据库。这些库的使用场景和优缺点各有不同,比如列式存储数据库可以大幅降低系统IO,适合于分布式文件系统和OLAP,但数据库频繁增删改列式存储就不恰当了,原因在前面的放弃系列已经说过不再赘述。

第二步,优化表设计

可以参考的优化原则:

1)表结构要尽量遵循第三范式的原则(后面讲到)。这样可以让数据结构更清晰、规范,减少冗余字段,同时减少了增删改数据时异常情况的发生。

2)如果分析查询应用比较多,尤其是需要进行多表联查时,可以采用反范式进行优化。反范式采用空间换时间的方式,通过增加冗余字段提高查询效率。

3)表字段的数据类型选择,关系到了查询效率的高低及存储空间的大小。如果字段可以采用数值类型就不要采用字符类型;字符长度要尽可能设计的短一点。就字符类型来说,当确定字符长度固定时,可以采用CHAR类型;长度不固定时,通常采用VARCHAR类型。

数据表的结构设计是基础,也很关键。好的表结构即使在业务发展和用户数增加的情况下依然发挥作用,不好的表结构设计会让数据表变得臃肿、查询效率降低。

第三步,优化逻辑查询

SQL查询优化,可分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变SQL语句的内容让SQL执行效率更高,采用的方式是对SQL进行等价变换,对查询进行重写,重写查询的数学基础就是关系代数。

SQL的查询重写包括子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。

例如在EXISTS子查询和IN子查询时,会以小表驱动大表的原则选择适合的子查询。在WHERE子句中尽量避免对字段进行函数运算,它们会让字段的索引失效。

示例:查询内容开头为abc的内容有哪些?

如果在WHERE子句中使用函数,SQL如下:

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

如果采用查询重写的方式进行等价替换,SQL如下:

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_text LIKE 'abc%'

在数据量小时倒无所谓,数据量大时,第二种SQL查询效率会提升很多,执行时间是前者的1/10。

第四步,优化物理查询

物理查询优化是将逻辑查询的内容变为可被执行的物理操作符,从而为后续执行器的执行提供准备。它的核心是高效地建立索引,并通过这些索引做各种优化。

但是,索引并不是万能的,需要根据实际情况去创建索引:

1)如果数据重复度高,就不必创建索引。通常重复度超过10%的情况下,可以不创建这个字段的索引,比如性别字段,取值只有男和女。

2)要注意索引列的位置对索引使用的影响。比如在WHERE子句中对索引字段进行了表达式的计算,会造成这个字段的索引失效。

3)要注意联合索引对索引使用的影响。在创建联合索引的时候会对多个字段创建索引,此时索引的顺序很关键。比如对字段x、y、z创建了索引,那么顺序是(x、y、z)还是(z、y、x),在执行时就会有差别。

4)要注意多个索引对索引使用的问题。索引并非多多益善,因为每个索引都需要存储空间。此外,过多的索引也会导致优化器在执行评估的时候增加筛选出索引的计算时间,影响评估的效率。

查询优化器对SQL语句进行等价变换之后,还需要根据数据表的索引情况和数据情况确定访问路径,这就决定了SQL执行所需要消耗的资源。SQL查询时需要对不同的数据表进行查询,因此在物理查询优化阶段也需要确定这些查询采用的路径,具体情况包括:

4.1)单表扫描:对于单表查询来说,可以全表扫描,也可以局部扫描。

4.2)两张表的连接:常用连接方式包括嵌套循环连接、HASH连接和合并连接。

4.3)多张表的连接:多表连接,顺序很关键,不同的连接路径,查询效率和搜索空间不同。多表连接时,搜索空间可能会达到很高的数量级,这样无疑会占用更多资源,因此需要调整连接顺序,将搜索空间控制在可接受的范围内。

物理查询优化是在逻辑查询优化后采用的物理优化技术,通过计算代价模型对各种可能的访问路径进行估算,找到执行方式中代价最小的作为执行计划。在这部分,需要重点掌握索引的创建和使用。

第五步,使用Redis或Memcached作为缓存

使用Redis或Memcached这种操作属于请求外援的查询方式,用以提升查询效率。

因为数据都是存储在数据库中,需要从数据库中取出数据进行业务逻辑操作,当用户数激增、频繁进行数据查询时,会消耗数据库很多资源。如果将数据存放到内存中,查询效率会大幅提升。

键值存储数据库可以解决这个问题,常用的有Redis和Memcached,它们可以将数据存储到内存中。

从可靠性来说,Redis支持持久化,将数据保存到硬盘上,但性能消耗也会比较大,Memcached仅仅是内存存储,不支持持久化。

从支持的数据类型说,Redis比Memcached多。不仅仅支持key-value类型的数据,还支持List、Set、Hash等数据结构。

因此当有持久化需求或更高级数据处理需求的时候,可以采用Redis,如果仅仅是简单的key-value存储则可以采用Memcached。

通常对于查询响应要求高的场景(响应时间段、吞吐量大)可以考虑采用内存存储,查询效率高。

第六步,库级优化

库级优化是站在数据库的维度进行的优化策略。比如控制一个库中数据表的数量。另外可以采用主从架构优化读写策略,如分库分表、读写分离等。

如果读和写的业务量都很大,并且它们都在同一个数据库服务器中操作,则数据库的性能会出现瓶颈,此时为了提升系统性能、优化用户体验,可以采用读写分离的方式降低主数据库的负载。比如用主数据库(master)完成写操作,用从数据库(slave)完成读操作。

此外,还可以对数据库分库分表。当数据量达到亿级以上时,需要把数据库进行切分,放到不同数据库服务器上,减少对单一数据库服务器的压力。如果使用的是MySQL,可以使用MySQL自带的分区表功能,也可以考虑自己做垂直切分或水平切分。如何确定使用垂直还是水平呢?

如果数据库中的数据表过多,可以采用垂直分库的方式,将关联的数据表部署在同一个数据库服务器上;如果数据表中的列过多,可以采用垂直分表的方式,将数据表拆分成多张表,把经常一起使用的列放到同一张表中。

如果数据表中的数据达到了亿级以上,可以考虑水平切分,将大的数据表拆分成多个字表,每张表采用相同的表结构。比如按年份划分,把不同年份的数放到不同的数据表中。

简单说,垂直分表就是将一张表拆成多张表,水平切分就是将单张数据量大的表按照某个属性维度分成多个表结构相同的小表。

注意:分拆在提升数据库性能的同时,也增加了维护成本和使用成本。

如何思考和分析数据库调优这事儿?

数据库调优中,目标就是响应时间更短、吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮助我们确定调优思路和方式,不过综合以上分析,可以从三个维度考虑:

1)选择适合的DBMS和数据表设计方式。

2)采用逻辑查询优化和物理查询优化。

逻辑查询优化就是通过SQL等价变换提升查询效率。物理查询优化则是通过索引和表连接方式等技术进行优化。

3)请求外援增强数据库性能。

单一数据库总会遇到各种限制,利用外援则可以取长补短。此外还可以考虑对数据库进行垂直或水平切分,突破单一数据库或数据表的访问限制,提升查询性能。

(本文完)

SQL从入门到放弃系列——游标[十三]

1.什么是游标?为什么要使用游标?
2.如何使用游标?
3.如何使用游标解决一些常见问题?

什么是游标

游标是数据库中的一个重要概念,它提供了一种灵活的操作方式,可以让我们从数据结果集中每次提取一条数据记录进行操作。游标让SQL这种面向集合的语言有了面向过程的开发能力。

在SQL中游标是一种临时的数据库对象,可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用,我们可以通过操作游标来对数据行进行操作。

如何使用游标

游标实际上是一种控制数据集的更加灵活的处理方式。

使用游标一般经过5个步骤,不同DBMS,使用游标的语法略微不同。

  • 定义游标
DECLARE cursor_name CURSOR FOR select_statement

这个语法适用于Mysql,SQL Server,DB2,MariaDB,如果是Oracle,或者PostgreSQL,需要写成:

DECLARE cursor_name CURSOR IS select_statement

要使用select语句获取结果集,select_statement代表select语句。

以Mysql举例:

DECLARE cur_hero CURSOR FOR 
	SELECT hp_max FROM heros;
  • 打开游标

OPEN cursor_name

OPEN cursor_name

当定义好游标后如果想使用游标,必须先打开游标,打开游标的时候select语句的查询结果集就会送到游标工作区。

  • 从游标中取得数据
FETCH cursor_name INTO var_name ...

这句的作用是使用cursor_name这个游标来读取当前行,并且将数据保存到var_name这个变量中,游标指针指向下一个游标。如果游标读取的数据行有多个列名,则在into关键字后赋值给多个变量名即可。

  • 关闭游标
CLOSE cursor_name

当使用完游标后需要关闭游标,关闭游标后就不能再检索查询结果中的数据行,如果需要检索只能再次打开游标。

  • 释放游标
DEALLOCATE PREPARE

一定要养成释放游标的习惯,否则游标会一直存在于内存中,知道进程结束后才会自动释放。释放游标可以减少资源浪费。

示例

使用游标扫描heros数据表中的数据行,然后累计最大生命值,怎么做呢?

先创建一个存储过程calc_hp_max,然后在存储过程中创建游标cur_hero,使用FETCH获取每一行的具体数值,然后赋值给变量hp,再用变量hp_sum做累加求和,最后输出hp_sum,如下:

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 创建接收游标的变量
       DECLARE hp INT;  
       -- 创建总数变量 
       DECLARE hp_sum INT DEFAULT 0;
       -- 创建结束标志变量  
       DECLARE done INT DEFAULT false;
       -- 定义游标     
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       
       OPEN cur_hero;
       read_loop:LOOP 
       FETCH cur_hero INTO hp;
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
       DEALLOCATE PREPARE cur_hero;
END

此时执行会提示1329错误,原因是loop中当游标没有取到数据时会报错。

当游标溢出时(类似于脚标越界),可以定义一个continue事件,指定这个事件发生时修改变量done的值,以此判断是否溢出,即:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

同时在loop中添加对done的判断,如果游标的循环已结束就跳出loop:

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 创建接收游标的变量
       DECLARE hp INT;  

       -- 创建总数变量 
       DECLARE hp_sum INT DEFAULT 0;
       -- 创建结束标志变量  
     DECLARE done INT DEFAULT false;
       -- 定义游标     
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       -- 指定游标循环结束时的返回值  
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
       
       OPEN cur_hero;
       read_loop:LOOP 
       FETCH cur_hero INTO hp;
       -- 判断游标的循环是否结束  
       IF done THEN  
                     LEAVE read_loop;
       END IF; 
              
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
       DEALLOCATE PREPARE cur_hero;
END

使用游标解决一些常见问题

游标有什么用?

当需要一些复杂数据行计算的时候就需要用到游标了。比如,针对heros数据表,假设需要对英雄的物攻成长attack_growth进行升级,方案如下:

如果这个英雄原有的物攻成长小于 5,那么将在原有基础上提升7%-10%。如果物攻成长的提升空间(即最高物攻 attack_max- 初始物攻 attack_start)大于 200,那么在原有的基础上提升 10%;如果物攻成长的提升空间在150 到 200 之间,则提升 8%;如果物攻成长的提升空间不足 150,则提升 7%。

如果原有英雄的物攻成长在 5—10 之间,那么将在原有基础上提升 5%。如果原有英雄的物攻成长大于 10,则保持不变。以上所有的更新后的物攻成长数值,都需要保留小数点后 3 位。

针对上面这个情况,你自己可以用游标来完成转换,具体的代码如下:

CREATE PROCEDURE `alter_attack_growth`()
BEGIN
       -- 创建接收游标的变量
       DECLARE temp_id INT;  
       DECLARE temp_growth, temp_max, temp_start, temp_diff FLOAT;  

       -- 创建结束标志变量  
       DECLARE done INT DEFAULT false;
       -- 定义游标     
       DECLARE cur_hero CURSOR FOR SELECT id, attack_growth, attack_max, attack_start FROM heros;
       -- 指定游标循环结束时的返回值  
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
       
       OPEN cur_hero;  
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       REPEAT
                     IF NOT done THEN
                            SET temp_diff = temp_max - temp_start;
                            IF temp_growth < 5 THEN
                                   IF temp_diff > 200 THEN
                                          SET temp_growth = temp_growth * 1.1;
                                   ELSEIF temp_diff >= 150 AND temp_diff <=200 THEN
                                          SET temp_growth = temp_growth * 1.08;
                                   ELSEIF temp_diff < 150 THEN
                                          SET temp_growth = temp_growth * 1.07;
                                   END IF;                       
                            ELSEIF temp_growth >=5 AND temp_growth <=10 THEN
                                   SET temp_growth = temp_growth * 1.05;
                            END IF;
                            UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id;
                     END IF;
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       UNTIL done = true END REPEAT;
       
       CLOSE cur_hero;
       DEALLOCATE PREPARE cur_hero;
END

 

总结

游标实际上是面向过程的思维方式,与面向集合的思维方式不同的地方在于游标更关注“如何执行”。可以通过游标更加精细、灵活地查询和管理想要的数据。

多表嵌套查询,join会非常消耗资源,使用游标会比较高效。

游标性能

好处:

灵活性强,可以解决复杂的数据处理问题,对数据进行逐行处理。

不足:

使用游标的过程中会对数据进行加锁,当业务并发量大的时候,会影响到业务的效率。同时游标是在内存中进行的处理,容易造成内存不足,消耗系统资源。

建议:

通常有游标替代方案时不用游标,实在绕不开还是需要使用游标。

思考

面向过程和面向集合这两种编程方式的区别?

(本文完)

SQL从入门到放弃系列——事务[十二]

1.什么是事务特性?
2.如何控制事务?
3.为什么有些commit和rollback会失败?
4.事务并发处理的可能异常有哪些?
5.针对可能存在的异常,四种事务的隔离级别分别是什么?

什么是事务

事务transaction,将多个数据库读或写的操作组成一个基本单元,要么完全执行,要么都不执行。

事务特性(ACID)

ACID是事务四大特性,原子性是基础,隔离性是手段,一致性是约束条件,持久性是目的。

A 原子性 Atomicity

数据处理操作的最基本单位。

C 一致性 Consistency

事务被提交或回滚后,数据库的完整性约束不被破坏。也就是说,任何写入数据库的数据都需要满足事先定义的约束规则。

I 隔离性 Isolation

每个事务彼此独立,不受其他事务执行的影响。即一个事务在提交之前,对其他事务不可见。

D 持久性 Durability

事务提交后对数据的修改是持久性的,即使系统故障情况下,数据修改依然有效。因为当事务完成,数据库的日志会被更新,此时可以通过日志,让系统恢复到最后一次成功的更新状态。

持久性是通过事务日志来保证的,日志包括回滚日志和重做日志。当通过事务对数据修改的时候,首先会将数据库的变化信息记录到重做日志中,然后在对数据库进行修改。所以即使数据库系统崩溃,数据库重启后也能找到没有更新到数据库系统中的重做日志,重新执行,从而使事务具有持久性。

事务的控制

Oracle支持事务,MySQL中InNoDB存储引擎支持事务,MyISAM存储引擎不支持,这些可以通过show engines命令查看MySQL中哪些存储引擎支持哪些不支持事务。

常用事务控制语句

  • 显式开启事务

start transaction或begin

  • 提交事务

commit,事务提交后,对数据库修改是永久性的。

  • 回滚事务

rollback或rollback to [savepoint],撤销正在进行的所有没有提交的修改,或者将事务回滚到某个保存点。

  • 创建保存点

savepoint,在事务中创建保存点,方便回滚,一个事务中可以有多个保存点。

  • 删除保存点

release savepoint,删除某个保存点。

  • 设置事务隔离级别

set transaction,设置事务的隔离级别。

 

显式事务和隐式事务

隐式事务就是自动提交,Oracle默认不自动提交,需要手写commit命令,MySQL默认自动提交,但是可以配置MySQL的参数:

mysql> set autocommit =0;  // 关闭自动提交
mysql> set autocommit =1;  // 开启自动提交
示例1
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
BEGIN;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

结果是一条关羽。

说明:在这个事务中,整个SQL一共执行了2个事务,第一个是插入关羽,提交后执行成功,第二个事务是插入两次张飞,注意的是,name字段为主键,主键的值是唯一的,那么第二次张飞插入或产生错误,然后执行rollback相当于对事务进行了回滚,所以结果只有一条数据关羽。

示例2
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

结果是两条,关羽和张飞。

说明:这两条张飞不在同一个事务中,对MySQL来说这是默认两个事务,在autocommit=1的情况下,MySQL会进行隐式事务,即自动提交,因此进行第一次插入张飞后,数据库里存在了两行数据,第二次插入张飞后就会报错:1062-duplicate entry ‘张飞’ for key ‘primary’。

即使rollback,实际上事务已经自动提交了,无法回滚了。

示例3
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

结果只有一条关羽。

说明:SQL中,在事务开始之前设置了set @@completion_type=1;结果和示例1一样。这里说一下completion_type参数的作用:

1.completion_type=0,默认为0。意思是执行commit时提交事务,在执行下一个事务时,需要手动开启。

2.completion_type=1,意思是当提交事务后,相当于执行了commit and chain,也就是开启一个链式事务,即当提交事务后开启一个相同隔离级别的事务。

3.completion_type=2,这种情况下commit=commit and release,即当提交事务后,会自动断开与服务器的连接。

所以上述示例3的第二个事务进行了回滚后结果只有一条关羽。

提醒:如果显式开启事务,那么同时需要commit才能生效,在rollback时才会回滚。

事务并发处理的三种可能异常

脏读

读到了其他事务还未提交的数据。

不可重复读

对某数据进行读取,发现两次读取的结果不同,即没有读到相同的内容。这是因为有其他事务对这个数据进行了修改或删除。

幻读

事务a根据条件查询得到了n条数据,但此时事务b更改或增加了m条符合事务a查询条件的数据,这样当事务a再次进行查询的时候发现会有n+m条数据,产生了幻读。

隔离级别从低到高:

隔离级别 脏读 不可重复读 幻读
读未提交
读已提交 ×
可重复读 × ×
可串行化 × × ×

可串行化,将事务进行串行化,也就是一个队列中按照顺序执行,是最高级别的隔离等级,可以解决事务读取中所有可能出现的异常情况,但是牺牲了系统的并发性。

MySQL默认的隔离级别就是可重复读。

 

总结

隔离级别越低,系统吞吐量即并发程度越大,但同时意味着出现异常问题的可能性会更大。在实际使用过程中,往往需要在性能和正确性上进行权衡和取舍,没有完美的解决方案,只有适合与否。

思考

不可重复读和幻读区别

不可重复读和幻读都是在先后两次读取的时候发现不一致的情况,但是两种读取略有差别。不可重复读是对于同一条记录内容的“不可重复读”,幻读是对于某一范围的数据集,发现查询数据集的行数多了或者少了,从而出现的不一致。所以不可重复读的原因是:对于要查询的那条数据进行了UPDATE或DELETE,而幻读是对于要查询的那个范围的数据集,进行了INSERT。

(本文完)

SQL从入门到放弃系列——存储过程[十一]

1.如何创建存储过程。
2.如何使用流控制语句。
3.如何使用存储过程。

什么是存储过程,如何创建存储过程

存储过程(Stored Procedure),是SQL中另一重要应用,和视图一样,都是对SQL代码进行封装,可以反复利用。它和视图有相同的优点,清晰、安全,还可以减少网络传输量。和视图不同的是,视图是虚拟表,通常不对底层数据表进行直接操作;而存储过程是程序化的SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。存储过程可以认为是SQL语句和流控制语句构成的语句集合,可以接收输入参数,也可以返回计算结果。

创建存储过程

CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
    需要执行的语句
END

更新、删除存储过程

和创建视图语法相同。

示例一

创建一个累加运算的存储过程,计算1+2+3+。。。+n的和,代码如下:

CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END

调用存储过程时,使用CALL add_num(50);即可。

mysql> call add_num(50);
+------+
| sum  |
+------+
| 1275 |
+------+
1 row in set (0.00 sec)
delimiter的作用

如果使用Navicat工具创建存储过程,直接执行创建语句即可,如果用的是MySQL,需要用delimiter临时定义新的结束符。新的结束符用(//)或($$)。如:

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

Navicat会自动设置delimiter为其他符号,不需要再进行delimiter的操作。

存储过程的3种参数类型
参数类型 是否返回 作用
IN 向存储过程传入参数,存储过程中修改该参数的值,不能被返回。
OUT 把存储过程计算的结果放到该参数中,调用者可以得到返回的值。
INOUT IN和OUT的结合,既用于存储过程的传入参数,同时把计算结果放到参数中,调用者得到结果。
示例二

创建存储过程get_hero_scores,用于查询某一类型英雄中最大生命值,最小的最大魔法值,以及平均最大攻击值。代码:

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

定义了4个参数,3个为OUT类型,1个为IN类型。

调用存储过程:

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;
@max_max_hp @min_max_mp @avg_max_attack
8050 0 342.1666564941406

 

流控制语句

流控制语句是用于做流程控制的,上面的两个示例中,用到的控制语句:

1.BEGIN…END:中间用到了多个语句,每个语句用分号为结束符;

2.DECLARE:用于声明变量;

3.SET:赋值语句;

4.SELECT…INFO:把从数据表中查询的结果放到变量中,即为变量赋值。

此外,其他常用的流控制语句:

1.IF…THEN…ENDIF:条件判断语句。

2.CASE:多条件的分支判断。

CASE 
	WHEN expression1 THEN ...
	WHEN expression2 THEN ...
	...
    ELSE 
    --ELSE 语句可以加,也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

3.LOOP LEAVE ITERATE:loop是循环语句,使用leave跳出循环,使用iterate可以进入下一次循环。可以把leave理解为break,把iterate理解为continue。

4.REPEAT…UNTIL…END REPEAT:循环语句。

5.WHILE…DO…END WHILE:循环语句。

 

存储过程使用争议

存储过程的优点

  1. 一次编译,多次使用。
  2. 安全性强。
  3. 减少网络传输量。

存储过程缺点

  1. 可移植性差。MySQL、Oracle和SQLServer编写的存储过程换作其他数据库都要重新编写。
  2. 调试困难。
  3. 版本管理困难。比如数据表的索引发生变化,可能会导致存储过程失效。
  4. 不适合高并发场景。高并发场景需要减少数据库的压力,有时数据库会采用分库分表的方式,而且可扩展性要求很高,这种情况下,存储过程会很难维护,增加数据库的压力。

 

思考

针对heros表,编写存储过程get_sum_score,查询某一类英雄的最大生命值的总和。

DELIMITER //
create procedure `get_sum_score`(IN roleMain VARCHAR(255),OUT total FLOAT)
BEGIN
select sum(hp_max) as sum_hp_max from heros where role_main=roleMain INTO total;
END//
DELIMITER ;

call get_sum_score('刺客',@total);
select @total

(本文完)

SQL从入门到放弃系列——视图[十]

1.什么是视图?
2.视图的增删改?
3.视图和临时表的区别?各自优缺点?

视图是一张虚拟表,相当于一张或多张表的结果集,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。可以简化SQL、重用。

创建视图

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

嵌套视图

创建视图后还可以在他们的基础上继续创建视图。

修改视图

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

和创建视图一样,只是对原有视图的更新。比如在原有基础上新增一个player_name字段:

ALTER VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) from player)

删除视图

DROP VIEW view_name

利用视图对数据进行格式化(组装数据)

CREATE VIEW player_team AS 
SELECT CONCAT(player_name, '(' , team.team_name , ')') AS player_team FROM player JOIN team WHERE player.team_id = team.team_id

视图和临时表的区别

视图只是一条预编译的SQL语句,并不保存实际数据;  临时表是保存在tempdb中的实际的表。
(物理空间的分配不一样,视图不分配空间,临时表会分配空间 )。

视图优缺点

优点

1.根据不同用户定制不同数据

2.简化数据库操作,提高复用性

3.安全性,视图仅仅是查询,视图的更新和删除并不会影响基表数据

4.合并分离的数据,更直观

缺点

1.性能差

视图的行列数据是动态生成的,必须把视图查询转化为对基表的查询,性能受影响。

2.修改限制

修改视图信息时,如果视图比较复杂,修改受限。

临时表优缺点

没有对比单说优缺点就是耍流氓,临时表通常和表变量比较。

临时表 与 表变量

1.存储位置:临时表是利用了硬盘(tempdb数据库) ,表名变量是占用内存,因此小数据量当然是内存中的表变量更快。当大数据量时,就不能用表变量了,太耗内存了。大数据量时适合用临时表。

2.性能:不能一概而论,表变量存储数据有个性能临界点,在这个临界点之内,表变量比临时表快,表变量是存储在内存中的。

3.索引:表变量不支持索引和统计数据,但可以有主键;临时表则可以支持索引和统计数据。

我们对于较小的临时计算用数据集考虑使用表变量。如果数据集比较大,如果在代码中用于临时计算,同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化,比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等),也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力,如果代码的运行实例很多,就要特别注意内存变量对内存的消耗。一般对于大的数据集我们最好使用临时表,同时创建索引。

链接:

(本文完)