数据库设计调优（一）

年初，便制定计划，今年主要学习数据库的相关优化，过程中既有sybase数据库，也有mysql，oracle的学习。以下除特殊说明外，其他涉及优化不针对某一具体数据库，现总结相关的设计调优如下，总体我分以下几个方面介绍我理解的数据库设计：

1.数据库（表，字段）设计

2.反规范

3.数据库设备使用

4.索引使用优化

5.分表技术

6.读写分离

7.存储过程

---

 

一  数据库（表，字段）设计

     这个主要是一个整体的设计，如我们公司的XX数据库，采用在审和审结库分离，每类案件类型拥有自己独立的数据库作为在审库，所有审结案件放到一个数据库中作为审结库。而在XX数据库中，则采用一个业务库包含所有案件类型的在审和审结记录。当然这时最简单的举例。这里更多的是考虑业务需求的逻辑，以及系统的实现方式。

     一个系统设计之初，设计构架人员应该会设计好整个系统需要考虑设计哪些库，哪些表，库表的关系，表采用什么引擎（MYSQL），表上字段的约束，字段类型，长度，锁模式，是否满足一定的范式等。

二  反范式

     首先说下范式。一般情况下，系统数据库表设计应满足3NF,即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。也就是用空间来换取时间,把数据冗余在多个表中，当查询时可以减少或者是避免表之间的关联；

     如我们现在要对一个学校的课程表进行操作，现在有两张表，一张是学生信息student（a_id,a_name,a_adress,b_id）表，一张是课程表 subject(b_id,b_subject)，现在我们需要一个这样的信息，把选择每个课程的的课程名称和学生姓名输出来：

SQL语句为：select  B.b_id,B.b_subject,A_a_name from student A ,subject B;

当上面的数据量不多时，我们这样去查询没有问题；当我们的两张表的数据都是在百万级的时候，我们去查上面的信息， 问题出现了，这个查询动不动就是几百毫秒，甚至更慢，这样的查询效率根本不能满足我们对于网页速度的要求（一般不能超过100毫秒），怎么办？当然要反范式，在课程表里面添加冗余字段——学生姓名，这样我们就可以通过下面的查询达到同样的目的：

SQL语句为：select  b_id,b_subject,a_name from subject B;

将两个查询放在一起查看执行计划，就会发现，第一个查询开销占了92%，而第二个才8%，也就是说，第二个查询比起第一个查询，效率上优化了10倍以上，成果显著啊。

总结：当我们开始着手一个项目后，范式的应用是这样的变化的：第三范式数据库的设计—–>当数据量越来越大，达到百万级时，经常要对一些多表数据进行大范围高频率进行操作——->范式数据库的设计———->网站的数据量再持续增长———->范式和反范式的数据库设计。

三  数据库设备使用

    仔细安排数据库、表和索引的物理存放位置可以提高系统的性能，尤其对于需要执行大量I/O的多用户系统和多CPU系统。

以sybase为例说明下，首先应明白物理设备，段，表分区的概念。

1.跨磁盘分布数据，避免I/O竞争

• 将有关键性能要求的数据库放置到单独的设备上；如果可能，使用不同控制器；必要时，对关键表使用段，对并行查询使用分区。
• 将频繁使用的表放到单独的设备上
• 将频繁连接的表放到单独的设备上
• 使用段，将表和索引放到不同的设备上

2.从数据库I/O隔离服务器I/O

• tempdb放置位置
• 单独的最快的磁盘
• 某些UNIX使用操作系统文件
• sybsecurity放置位置

3.将事务日志放在单独的磁盘上

• 通过减少I/O争用提高性能
• 确保数据硬盘崩溃能够完全恢复
• 加快恢复速度，因为同时异步预取请求可以在日志设备和数据设备上预先读取，而不会发生争用。

4.磁盘I/O的优化

• 查询数据时的Logical Read(2),Physical Read(18)

       Logical Read：指从cache中或磁盘中读取一次数据

       Physical Read：指从磁盘中读取一次数据  (以页－2k 为单位，当采用大块I/O时则是2,4,或8页)。

• 若Physical Read次数=0 表示cache命中率为100%
• 当采用2kI/O时 Physical Read次数= Logical Read次数 ，表示cache命中率为0
• 优化的目标是减小I/O-尤其是物理I/O的次数－提高cache命中率
• 对磁盘I/O问题的标识可以使用statistics io和sp_sysmon

四  索引的使用优化

1.索引使用中常见的问题

• 表上没有索引
• 表上的索引不是很有用，优化器没有使用
• 索引不支持范围查询，必须使用全表扫描
• 表上索引太多
• 索引项太大

2.建立聚集索引的基本思想

• 大多数表应该有聚集索引或者使用分区，以减少最后一页的竞争。
• 如果要对表经常做插入操作，聚集索引不要放在具有单调上升值的列，否则会引起封锁冲突。
• 如果聚集索引的码值与范围查询的搜索变量匹配，能提供很好的性能。
• 聚集索引不要包括经常修改的列。
• 选择聚集索引应基于where子句和连接操作类型。

3.何时考虑创建非聚集索引

• 满足查询条件的数据不超过20%
• 能实现 index covering
• 用于集函数、连接、group by和order by的列
• 要权衡索引对查询速度的加快与降低修改速度之间的利弊。

4.其它应注意的事项

• 如果索引值是唯一的，要定义成唯一性索引，这样优化器就知道对于一搜索变量只返回一行结果。
• 如果在数据库设计时使用了参照完整性，则被参照的列必须有唯一索引。
• 在一经常做插入操作的表上建索引时，使用fillfactor来减少页分裂，同时提高并发度降低死锁的发生。
• 如果在只读表上建索引，则可把fillfactor置为100。
• 索引项尽量小

5.索引的维护

• 定期监控索引的使用
• 删除影响性能的索引 
• 索引统计信息的维护
• 重建索引

转载于:https://www.cnblogs.com/lvhl/p/3428102.html