20170105数据库表设计知识点

20170105数据库表设计知识点

------指导老师    星哥

1.PHP（MYSQL）擅长单表操作，不要做过多无谓的连接查询

2.表字段名不要使用大驼峰命名方式，最好采用下划线，命名要和团队习惯一致，通俗易懂。外键为全表名+ID

3.表级、字段都要有注释

4.MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。 

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

5.字符串长度要根据具体的业务场景进行合适的设置，如果不明白，多去参考大公司的规则。

6.枚举型字段，最好设置成tintint,便于以后业务变更和拓展

7.对于用户名和昵称，不要混淆，一个是登陆账户名，一个是显示昵称，必要的时候可以设置成两个字段。

8.int默认是11位，不要私自设置成其他的长度。

9.关于价格，最好以int型，展示和进行业务处理是两回事，不要混淆。

10.关于图片地址，大小要合适，不要凭想象设置，比如京东是102位，淘宝是80位，一定要合适，以前图片存不进去。

11.如果含有is_del，一般会有删除时间，和谁删除的，尽量是逻辑删除。避免物理删除。

12.如果含有验证，比如邮箱，手机，最好要多一个字段来表明验证 是否成功。

13.banner图及类似的东西，要给一个排序的字段，这样在重新排序的时候，就不用删除所有记录，重新上传了。

14.避免数据冗余，比如评论昵称，如果昵称更改了，以前的昵称如何显示？

15.一般来说，订单数据是不允许轻易改变的，以免用户数据更改了以后，订单状态随之改变

16.支付和订单状态不一定要放在一起的，比如货到付款，那么写死的状态就不对了。

17.设置tingint表示的值的时候，要参考具体的业务逻辑和其他接口的设计，不要私自设置。

18.属性可以写活，但是不要出现不可控的现象，更麻烦

19.对于数据，要习惯给予一个默认数据，这样在没有数据的时候读取默认值。

20.挑一个你的SELECT语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。可以进行表分析，根据分析来优化表

21.当只有一行数据的时候，给予limit 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。 避免多余搜索

22.要习惯为条件字段加上索引。

23.在Join表的时候使用相当类型的例，并将其索引 

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。 

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

在state中查找company 
$r = mysql_query("SELECT company_name FROM users 
LEFT JOIN companies ON (users.state = companies.state) 
WHERE users.id = $user_id"); 

// 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。

24.避免 SELECT * 从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载

25.. 永远为每张表设置一个ID 

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。 

就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。 

而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……

26.尽可能使用Not Null

27.IP地址存成 UNSIGNED INT

 

很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。

 

我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。

 

而你的查询，你可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long() 和 long2ip()。

1 $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

28.固定长度的表会更快 

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理

29.垂直分割 

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。