sql-基础三

数据库调优

1、选择合适的dbms
2、优化表的设计
（1） 表结构要尽量遵循第三范式的原则（关于第三范式，我在后面章节会讲）。这样可以让数据结构更加清 晰规范，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生。 （2）如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式进行优化。反范式采用空 间换时间的方式，通过增加冗余字段提高查询的效率。 （3）表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。一般来说，如果字段可以采用 数值类型就不要采用字符类型；字符长度要尽可能设计得短一些。针对字符类型来说，当确定字符长度 固定时，就可以采用CHAR类型；当长度不固定时，通常采用VARCHAR类型。
3、优化查询逻辑
SQL的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。 比如我们在讲解EXISTS子查询和IN子查询的时候，会根据小表驱动大表的原则选择适合的子查询。在 WHERE子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。 我举一个例子，假设我想对商品评论表中的评论内容进行检索，查询评论内容开头为abc的内容都有哪些， 如果在WHERE子句中使用了函数，语句就会写成下面这样：

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

我们可以采用查询重写的方式进行等价替换

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_text LIKE 'abc%'`在这里插入代码片`

4、优化物理查询
第第四四步步，，优优化化物物理理查查询询 物理查询优化是将逻辑查询的内容变成可以被执行的物理操作符，从而为后续执行器的执行提供准备。它的 核心是高效地建立索引，并通过这些索引来做各种优化。 但你要知道索引不是万能的，我们需要根据实际情况来创建索引。那么都有哪些情况需要考虑呢？

1. 如果数据重复度高，就不需要创建索引。通常在重复度超过10%的情况下，可以不创建这个字段的索 引。比如性别这个字段（取值为男和女）。
2. 要注意索引列的位置对索引使用的影响。比如我们在WHERE子句中对索引字段进行了表达式的计算，会 造成这个字段的索引失效。
3. 要注意联合索引对索引使用的影响。我们在创建联合索引的时候会对多个字段创建索引，这时索引的顺 序就很重要了。比如我们对字段x, y, z创建了索引，那么顺序是(x,y,z)还是(z,y,x)，在执行的时候就会存在 差别。
4. 要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，索引多也就意 味着需要更多的存储空间。此外，过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计 算时间，影响评估的效率。 查询优化器在对SQL语句进行等价变换之后，还需要根据数据表的索引情况和数据情况确定访问路径，这就 决定了执行SQL时所需要消耗的资源。
   SQL查询时需要对不同的数据表进行查询，因此在物理查询优化阶段 也需要确定这些查询所采用的路径，具体的情况包括： 1. 单表扫描：对于单表扫描来说，我们可以全表扫描所有的数据，也可以局部扫描。 2. 两张表的连接：常用的连接方式包括了嵌套循环连接、HASH连接和合并连接。 3. 多张表的连接：多张数据表进行连接的时候，顺序很重要，因为不同的连接路径查询的效率不同，搜索 空间也会不同。我们在进行多表连接的时候，搜索空间可能会达到很高的数据量级，巨大的搜索空间显 然会占用更多的资源，因此我们需要通过调整连接顺序，将搜索空间调整在一个可接收的范围内。 物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种 可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划。在这个部分中，我们需要掌握的 重点是对索引的创建和使用。
   5、使用redis或者memcahed作为缓存。
   常用的键值存储数据库有Redis和Memcached，它们都可以将数据存放到内存中。 从可靠性来说，Redis支持持久化，可以让我们的数据保存在硬盘上，不过这样一来性能消耗也会比较大。 而Memcached仅仅是内存存储，不支持持久化。 从支持的数据类型来说，Redis比Memcached要多，它不仅支持key-value类型的数据，还支持List，Set， Hash等数据结构。 当我们有持久化需求或者是更高级的数据处理需求的时候，就可以使用Redis。如果是简 单的key-value存储，则可以使用Memcached。
   6、库级优化
   如果读和写的业务量都很大，并且它们都在同一个数据库服务器中进行操作，那么数据库的性能就会出现瓶 颈，这时为了提升系统的性能，优化用户体验，我们可以采用读写分离的方式降低主数据库的负载，比如用 主数据库（master）完成写操作，用从数据库（salve）完成读操作。
   除此以外，我们还可以对数据库分库分表。当数据量级达到亿级以上时，有时候我们需要把一个数据库切成 多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。如果你使用的是MySQL，就可 以使用MySQL自带的分区表功能，当然你也可以考虑自己做垂直切分和水平切分。
   如果数据库中的数据表过多，可以采用垂直分库的方式，将关联的数据表部署在一个数据库上。如果数据表中的列过多，可以采用垂直分表的方式，将数据表分拆成多张，把经常一起使用的列放到同一张 表里。
   如果数据表中的数据达到了亿级以上，可以考虑水平切分，将大的数据表分拆成不同的子表，每张表保持相 同的表结构。比如你可以按照年份来划分，把不同年份的数据放到不同的数据表中。2017年、2018年和 2019年的数据就可以分别放到三张数据表中。
   采用垂直分表的形式，就是将一张数据表分拆成多张表，采用水平拆分的方式，就是将单张数据量大的表按 照某个属性维度分成不同的小表。 但需要注意的是，分拆在提升数据库性能的同时，也会增加维护和使用成本。