关系型数据库设计原则与注意事项

时间：2021-05-24 15:04:35

一、表设计原则：

  1. 字段尽量设置为Not Null
  2. 避免where子句进行null判断。尽量设置为0.
  3. 认真规范字段大小，越小越好；数据类型越简单越好
  4. 表中不应该有重复值和字段
  5. 表中记录应有唯一标志符
  6. 表名规范前缀
  7. 一个表尽量存储一个对象本身
  8. 小数空间占用可能比整数大，精度高时会消耗更多CPU资源。可能的情况下，把数据存储为整数，由客户程序再转换运算。
  9. 一些短且定长的，使用char比varchar更高效
  10. 使用Memory引擎要避免Blog、TEXT类型（因为Memory不支持BLOG、TEXT，所以查询过程需要使用MyISAM磁盘临时表）
  11. DATETIME比TIMESTAMP大一倍，但TIMESTAMP时间区间支持1970-2038。
  12. 避免字符串作ID
  13. 避免where子句使用!=和<>操作符，否则将引擎放弃使用索引而进行全表扫描。
  14. 避免where子句使用or连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可改为union all，
    如select id from t where num = 10 union all select id from t where num = 20
  15. like模糊查询也将导致全表扫描 ， 如：
    select id from t where name like '%abcd%';
  16. in和not in也要慎用，否则将导致全表扫描，如：
    select id from t where num in(1,2,3)。能用between 就不要使用in
  17. 避免where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如
    select id from t where num/2=10，应该改为select id from t where num=100*2
  18. 避免在where子句中对字段进行函数操作，这将导致全表扫描
  19. 不要在where子句中的=左边进行函数、算术运算或其他表达式运算。
  20. 在使用索引作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引
  21. 能用exists代替in时优先使用exists
    select num from a where exists(select 1 from b where num=a.num)
  22. 索引尽量建在重复数据少的字段上
  23. 索引数量尽量不超过6个，否则会影响insert和update效率
  24. 尽量使用数字型字段
  25. 尽量使用varchar/nvarchar代替char/nach，因为变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
  26. 任何地方不要使用select * from t，要用具体的字段列表代替*
  27. 尽量使用表变量来代替临时表
  28. 尽量避免频繁创建和删除临时表，以减少系统表资源的消耗
  29. 尽量避免使用游标
  30. 在所有的存储过程和触发器开始处设置SET NOCOUNT ON，在结束时设置SET NOCOUNT OFFice。
  31. 尽量避免向客户端返回大数据量。
  32. 尽量避免大事务操作，提高系统并发能力。
二、数据库监测项目

在任何系统中，数据库通常是最大的性能瓶颈。而影响数据库性能的最大两个问题是数据库设计和SQL代码质量。很多系统都拥有良好的或者至少是可用的数据库设计，但由于没有经过适当的性能测试，SQL代码质量通常都会很差。这样的SQL代码在开发环境中可能运行很快，因为其中只有小数据集和最小的负载。但是当成千上万的用户同时读取数据库中上百万条记录的时候，它就很可能会崩溃。

不幸的是，这些问题一开始并不明显，直到系统增大、突然开始崩溃的时候才会显现出来。在增大的过程中，数据库系统看起来运行得很快（因为数据都位于内存中，而且很少有并发的查询），并且对用户的响应也很快，但实际上它的内部运行效率很低。这并不重要，我们关注的是在系统增大并遇到性能问题之前找到这些问题并加以解决。

关于这个问题有很多不错的书和站点进行了解析，其中的关键工具：

  • 慢查询日志
  • INNODB状态系统

以及描述当前性能的MySQL统计信息。

我们见到过很多系统每秒会读取500,000条数据，这是出现SQL问题的明显预兆，但公司往往对其一无所知直到服务器开始崩溃。


MySQL系统应该对所有数据使用 INNODB存储引擎，因为INNODB与之前的MyISAM相比，运行得更快、更稳定，并且管理性能和备份工作也更加容易和快捷。

在主配置文件中，INNODB应该被设置为默认的数据库引擎，并且系统应该不时地进行检查，看是否意外创建了MyISAM的表。


1、总要拥有良好的DB配置和备份
2、使用读/写数据库分离


随着系统变得越来越庞大，特别是当它们拥有很差的SQL时，一台数据库服务器通常不足以处理负载。但是多个数据库意味着重复，除非你对数据进行了分离。更一般地，这意味着建立主/从副本系统，其中程序会对主库编写所有的Update、Insert和Delete变更语句，而所有Select的数据都读取自从数据库（或者多个从数据库）。


尽管概念上很简单，但是想要合理、精确地实现并不容易，这可能需要大量的代码工作。因此，即便在开始时使用同一台数据库服务器，也要尽早计划在PHP中使用分离的DB连接来进行读写操作。如果正确地完成该项工作，那么系统就可以扩展到2台、3台甚至12台服务器，并具备高可用性和稳定性。【我不知道这篇文章是几年前的，我相信，目前所谓的读写分离好象用的不多了，更多的会采用前置处理，然后由数据库自动分发，以及采用更好的缓存功能。读写其实并不能增强多少性能。当然如果是电子商务网站，或许可以。但对于PHP来说，真的没多大意义 ，因为PHP没有连接池功能，读和写发生交互的时候，相当于连接了两个数据库，还不能互相同时使用】


3、使用类似Memcached之类的数据库缓存
4、构建测试和开发环境

最佳的实践是拥有两个测试系统，一个用于开发者的代码和功能的整合测试，另一个要与生产环境完全一致，从而更容易向生产环境平滑地过渡。


三、查询慢的原因

1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 
2、I/O吞吐量小，形成了瓶颈效应。 
3、没有创建计算列导致查询不优化。 
4、内存不足 
5、网络速度慢 
6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 
7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 
8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列 
10、查询语句不好，没有优化 



更多优化处理知识 

http://blog.csdn.net/wanglei_samrtfish/article/details/7480069

标签：

相关文章