SQL必知必会学习笔记(七)
学极客时间的 SQL必知必会专栏,做记录(数据库调优)
数据库调优
目标
要让数据库运行得更快,也就是说响应的时间更快,吞吐量更大。更细致的目标可以通过用户反馈和日志分析来确定,还可以通过监控运行状态来整体了解服务器和数据库的运行情况。
- 服务器资源使用监控。通过监控服务器的 CPU、内存、I/O 等使用情况,可以实时了解服务器的性能使用,与历史情况进行对比。
- 数据库内部状况监控。通过活动会话(Active Session)监控指标可以清楚的了解到数据库当前是否处于非常繁忙的状态,是否存在 SQL 堆积等。还可以对事务、锁等待等进行监控。
维度
合适的DBMS
表优化
优化原则:
- 表结构要尽量遵循第三范式的原则(关于第三范式,我在后面章节会讲)。这样可以让数据结构更加清晰规范,减少冗余字段,同时也减少了在更新,插入和删除数据时等异常情况的发生。
- 如果分析查询应用比较多,尤其是需要进行多表联查的时候,可以采用反范式进行优化。反范式采用空间换时间的方式,通过增加冗余字段提高查询的效率。
- 表字段的数据类型选择,关系到了查询效率的高低以及存储空间的大小。一般来说,如果字段可以采用数值类型就不要采用字符类型;字符长度要尽可能设计得短一些。针对字符类型来说,当确定字符长度固定时,就可以采用 CHAR 类型;当长度不固定时,通常采用 VARCHAR 类型。
逻辑查询优化
SQL 查询优化,可以分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变 SQL 语句的内容让 SQL 执行效率更高效,采用的方式是对 SQL 语句进行等价变换,对查询进行重写。重写查询的数学基础就是关系代数。SQL 的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。
物理查询优化
物理查询优化是将逻辑查询的内容变成可以被执行的物理操作符,从而为后续执行器的执行提供准备。它的核心是高效地建立索引,并通过这些索引来做各种优化。物理查询优化是在确定了逻辑查询优化之后,采用物理优化技术(比如索引等),通过计算代价模型对各种可能的访问路径进行估算,从而找到执行方式中代价最小的作为执行计划。
创建索引需考虑的情况:
- 如果数据重复度高,就不需要创建索引。通常在重复度超过 10% 的情况下,可以不创建这个字段的索引。比如性别这个字段(取值为男和女)。
- 要注意索引列的位置对索引使用的影响。比如我们在 WHERE 子句中对索引字段进行了表达式的计算,会造成这个字段的索引失效。
- 要注意联合索引对索引使用的影响。我们在创建联合索引的时候会对多个字段创建索引,这时索引的顺序就很重要了。比如我们对字段 x, y, z 创建了索引,那么顺序是 (x,y,z) 还是 (z,y,x),在执行的时候就会存在差别。
- 要注意多个索引对索引使用的影响。索引不是越多越好,因为每个索引都需要存储空间,索引多也就意味着需要更多的存储空间。此外,过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计算时间,影响评估的效率。
确定SQL查询所采用的路径:
- 单表扫描:对于单表扫描来说,我们可以全表扫描所有的数据,也可以局部扫描。
- 两张表的连接:常用的连接方式包括了嵌套循环连接、HASH 连接和合并连接。
- 多张表的连接:多张数据表进行连接的时候,顺序很重要,因为不同的连接路径查询的效率不同,搜索空间也会不同。我们在进行多表连接的时候,搜索空间可能会
使用 Redis 或 Memcached 作为缓存
通常我们对于查询响应要求高的场景(响应时间短,吞吐量大),可以考虑内存数据库,毕竟术业有专攻。传统的 RDBMS 都是将数据存储在硬盘上,而内存数据库则存放在内存中,查询起来要快得多。
库级优化
库级优化是站在数据库的维度上进行的优化策略,比如控制一个库中的数据表数量、采用主从架构优化读写策略、对数据库分库分表等。
设计范式
我们在设计关系型数据库模型的时候,需要对关系内部各个属性之间联系的合理化程度进行定义,这就有了不同等级的规范要求,这些规范要求被称为范式(NF)。你可以把范式理解为,一张数据表的设计结构需要满足的某种设计标准的级别。
目前关系型数据库一共有 6 种范式,按照范式级别,从低到高分别是:1NF(第一范式)、2NF(第二范式)、3NF(第三范式)、BCNF(巴斯 - 科德范式)、4NF(第四范式)和 5NF(第五范式,又叫做完美范式)。数据库的范式设计越高阶,冗余度就越低,同时高阶的范式一定符合低阶范式的要求。
- 1NF 指的是数据库表中的任何属性都是原子性的,不可再分。
- 2NF 指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。
- 3NF 在满足 2NF 的同时,对任何非主属性都不传递依赖于候选键。
- BCNF,也叫做巴斯 - 科德范式,它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。
1NF 需要保证表中每个属性都保持原子性;2NF 需要保证表中的非主属性与候选键完全依赖;3NF 需要保证表中的非主属性与候选键不存在传递依赖。
表的键
范式的定义会使用到主键和候选键(因为主键和候选键可以唯一标识元组),数据库中的键(Key)由一个或者多个属性组成。常用键如下:
- 超键:能唯一标识元组的属性集叫做超键。
- 候选键:如果超键不包括多余的属性,那么这个超键就是候选键。
- 主键:用户可以从候选键中选择一个作为主键。
- 外键:如果数据表 R1 中的某属性集不是 R1 的主键,而是另一个数据表 R2 的主键,那么这个属性集就是数据表 R1 的外键。
- 主属性:包含在任一候选键中的属性称为主属性。
- 非主属性:与主属性相对,指的是不包含在任何一个候选键中的属性。