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等),也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力,如果代码的运行实例很多,就要特别注意内存变量对内存的消耗。一般对于大的数据集我们最好使用临时表,同时创建索引。

链接:

(本文完)

SQL从入门到放弃系列——SQL连接[九]

SQL标准主要有SQL92和SQL99两个。
SQL连接。

笛卡尔积

也叫交叉连接。CROSS JOIN。

等值连接

就是用两张表中都存在的列进行连接。

可以使用表的别名使SQL语句更简洁,但需要注意,一旦用别名,在查询中就只能使用别名代替,否则会报错。错误示范:

SELECT player_id, player.team_id, player_name, height, team_name FROM player AS a, team AS b  a, team as b WHERE a.team_id = b.team_id

非等值连接

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest

外连接

左外连接

指左边的表是主表,需要显示左表的全部行,右表是从表,用(+)表示从表。

SELECT * FROM player, team where player.team_id = team.team_id(+)

相当于SQL99的:

SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id

右外连接

与左外相反。

SELECT * FROM player, team where player.team_id(+) = team.team_id

相当于SQL99的:

SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id

自连接

自连接可以对多表操作,也可以对单表操作。就是查询条件使用了当前表的字段。

比如:查看比布雷克 – 格里芬高的球员都有谁,及其身高。

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height

当然,不用自连接也可以,但是需要查询两次。

SELECT height FROM player WHERE player_name = '布雷克 - 格里芬'
SELECT player_name, height FROM player WHERE height > 2.08

合并起来SQL不简洁。

思考

三支球队的可能比赛组合。

就是笛卡尔积,去掉球队名相同的记录。

SELECT t1.team_name,t2.team_name FROM team as t1 LEFT JOIN team as t2 ON t1.team_id != t2.team_id

SELECT t1.team_name , t2.team_name FROM team as t1 ,team as t2 where t1.team_id<t2.team_id;

上面的为分主客场的SQL,下面的不分主客场。

(本文完)

SQL从入门到放弃系列——子查询[八]

子查询种类有哪些?
如何提高子查询性能?
SQL脚本(https://github.com/cystanford/sql_nba_data)

子查询分类

子查询可以依据是否执行多次划分为关联子查询和非关联子查询。

子查询从表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件执行,那么这样子查询叫做非关联子查询;如果这个子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后将结果反馈给外部,这种嵌套的执行方式称为关联子查询。

非关联子查询举例:

查询哪个球员身高最高、最高身高是多少?

SQL: SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)

关联子查询举例:

如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询子查询都要重新计算一次,这样的子查询称为关联子查询。比如:

查询每个球队中大于平均身高的球员信息

SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)
疑问,下面这句结果和上面一样,难道说上面仅仅是为了演示关联子查询?
SELECT player_name, height, team_id from player where height > (SELECT avg(height) from player)

EXISTS子查询

该子查询用来判断条件是否满足,满足为true,不满足为false。

比如查询出场过的球员的姓名、id、球队id。(是否出场由player_score表中是否有记录判断)

SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

集合比较子查询

集合比较子查询作用是和另一个查询结果集进行比较,用IN、ANY、ALL、SOME操作符。

IN子查询可以实现上面那个例子:

SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

这里还是有相同的疑问,为啥要加上WHERE player.player_id = player_score.player_id这个条件?

用IN还是用EXISTS

抽象一下:

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

这要涉及到IN和EXISTS的执行过程了。

如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用IN;

如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。

区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是EXISTS,那么以外层表为驱动表,先被访问;如果是IN,那么先执行子查询。所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 ,另外IN不对NULL进行处理。

IN 是把外表和内表作hash连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的,需要分情况。

NOT IN 和NOT EXISTS

如果查询语句使用了NOT IN那么内外表都进行全表扫描,没有用到索引;而NOT EXISTS的子查询依然能用到表上的索引。所以无论哪个表大,用NOT EXISTS都比NOT IN要快。(本段摘自:https://www.cnblogs.com/liyasong/p/sql_in_exists.html

ANY、ALL子查询

查询球员信息表中比team_id为1002中任何一个球员身高高的球员信息,输出id,name,height。

SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)

输出结果有35条。

查询比所有球员身高高的球员信息,输出id,name,height。

SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)

输出结果只有一条。

看上面两个需求的中文描述都一样,为啥结果不一样?

第一个其实是查询比1002球队中球员身高最低的高的结果;第二个其实是查询比1002球队中球员身高最高的都高的结果。

注意ANY、ALL关键字必须与比较操作符一起使用。

将子查询作为计算字段

比如:查询每个球队的球员数。

SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team

思考

查询场均得分大于20分的球员信息(id,name,team_id),场均得分从player_score表中查询。【仅供参考】。

SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id AND player_score.score > 20)

SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id AND player

(本文完)