【原创】日志表设计一例分析

7010阅读　0评论2014-05-21　yueliangdao0608
分类：Mysql/postgreSQL

关于关系表的设计归根结底有两个方面。
第一，就是完全按照范式理论去设计，一般来说达到第三范式就可以了，或者你可以划分的更细到达更上一层次。比如第四，第五，第六等等。这种设计有自己的可读性很强，但是有一点，在检索数据的时候增加了多张关系表来做关联的开销。
第二，就是在范式理论上适当的做些反范式，有的东西还是不要太剥离的好。（窄表以及宽表） 这点和软件设计中的紧耦合松耦合理论一致。


下面我就以常用的LOG表来做下演示，其中有两种表的实际，一种是窄表，一种是稍微宽一点的表。
窄表：log_ytt

点击(此处)折叠或打开

  1. mysql> show create table log_ytt;
  2. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | log_ytt | CREATE TABLE `log_ytt` (
  6.   `ids` bigint(20) DEFAULT NULL,
  7.   `log_time` datetime DEFAULT NULL,
  8.   KEY `idx_u1` (`ids`,`log_time`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  10. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.00 sec)

表记录数

点击(此处)折叠或打开

  1. mysql> select * from log_ytt where ids > '4875000001'; +------------+---------------------+
  2. | ids | log_time |
  3. +------------+---------------------+
  4. | 7110000001 | 2014-05-20 21:56:42 |
  5. | 6300000001 | 2014-05-20 21:56:42 |
  6. | 6750000001 | 2014-05-20 21:56:42 |
  7. | 5310000001 | 2014-05-20 21:56:42 |
  8. | 7200000001 | 2014-05-20 21:56:42 |
  9. | 7380000001 | 2014-05-20 21:56:42 |
  10. | 5760000001 | 2014-05-20 21:56:42 |
  11. | 6930000001 | 2014-05-20 21:56:42 |
  12. | 6660000001 | 2014-05-20 21:56:42 |
  13. | 5670000001 | 2014-05-20 21:56:42 |
  14. | 6210000001 | 2014-05-20 21:56:42 |
  15. | 5850000001 | 2014-05-20 21:56:42 |
  16. | 6570000001 | 2014-05-20 21:56:42 |
  17. | 5580000001 | 2014-05-20 21:56:42 |
  18. | 5130000001 | 2014-05-20 21:56:42 |
  19. | 7290000001 | 2014-05-20 21:56:42 |
  20. | 6390000001 | 2014-05-20 21:56:42 |
  21. | 5490000001 | 2014-05-20 21:56:42 |
  22. | 5220000001 | 2014-05-20 21:56:42 |
  23. | 7560000001 | 2014-05-20 21:56:42 |
  24. | 7470000001 | 2014-05-20 21:56:42 |
  25. | 7020000001 | 2014-05-20 21:56:42 |
  26. | 6840000001 | 2014-05-20 21:56:42 |
  27. | 6030000001 | 2014-05-20 21:56:42 |
  28. | 6480000001 | 2014-05-20 21:56:42 |
  29. | 7650000001 | 2014-05-20 21:56:42 |
  30. | 5940000001 | 2014-05-20 21:56:42 |
  31. | 6120000001 | 2014-05-20 21:56:42 |
  32. | 7740000001 | 2014-05-20 21:56:42 |
  33. | 5400000001 | 2014-05-20 21:56:42 |
  34. | 5760000001 | 2014-05-21 03:19:07 |
  35. | 6840000001 | 2014-05-21 03:19:17 |
  36. | 7020000001 | 2014-05-21 03:19:32 |
  37. | 7200000001 | 2014-05-21 03:19:45 |
  38. | 7110000001 | 2014-05-21 03:19:46 |
  39. | 7380000001 | 2014-05-21 03:19:48 |
  40. | 5670000001 | 2014-05-21 03:19:58 |
  41. | 6930000001 | 2014-05-21 03:19:59 |
  42. | 6030000001 | 2014-05-21 03:20:00 |
  43. | 5940000001 | 2014-05-21 03:20:00 |
  44. | 7290000001 | 2014-05-21 03:20:02 |
  45. | 6120000001 | 2014-05-21 03:20:09 |
  46. | 5850000001 | 2014-05-21 03:20:18 |
  47. | 5580000001 | 2014-05-21 03:20:24 |
  48. | 6480000001 | 2014-05-21 03:25:05 |
  49. | 6390000001 | 2014-05-21 03:25:37 |
  50. | 6210000001 | 2014-05-21 03:25:45 |
  51. | 7470000001 | 2014-05-21 03:26:14 |
  52. | 6750000001 | 2014-05-21 03:27:17 |
  53. | 5310000001 | 2014-05-21 03:27:33 |
  54. | 5130000001 | 2014-05-21 03:27:34 |
  55. | 6570000001 | 2014-05-21 03:27:34 |
  56. | 7560000001 | 2014-05-21 03:27:45 |
  57. | 5220000001 | 2014-05-21 03:27:45 |
  58. | 5400000001 | 2014-05-21 03:27:53 |
  59. | 5490000001 | 2014-05-21 03:27:55 |
  60. | 6660000001 | 2014-05-21 03:28:07 |
  61. | 6300000001 | 2014-05-21 03:28:13 |
  62. | 7740000001 | 2014-05-21 03:28:26 |
  63. | 7650000001 | 2014-05-21 03:28:37 |
  64. +------------+---------------------+
  65. 60 rows in set (0.00 sec)

接下来，我们要检索所有IDS的平均时间。 有以下两种方式：
第一， 对表进行了两次访问，并且有GROUP BY 操作，不可取。

点击(此处)折叠或打开

  1. mysql> select sec_to_time(avg(timestampdiff(second,a.times,b.times))) as 'running'
  2.     -> from
  3.     -> (select ids,min(log_time) as times from log_ytt where 1 group by ids ) as a,
  4.     -> (select ids,max(log_time) as times from log_ytt where 1 group by ids) as b where a.ids = b.ids;
  5. +---------------+
  6. | running |
  7. +---------------+
  8. | 05:27:08.8333 |
  9. +---------------+
  10. 1 row in set (0.00 sec)



第二，虽然对表进行了最少的访问，但是也有一次GROUP BY 操作。也没办法，表设计如此。



点击(此处)折叠或打开

  1. mysql> SELECT SEC_TO_TIME(AVG(times)) AS 'Running' FROM
  2.     -> (
  3.     -> SELECT TIMESTAMPDIFF(SECOND,MIN(log_time),MAX(log_time)) AS times FROM log_ytt GROUP BY ids
  4.     -> ) AS T;
  5. +---------------+
  6. | Running |
  7. +---------------+
  8. | 05:27:08.8333 |
  9. +---------------+
  10. 1 row in set (0.00 sec)



宽表：log_ytt_horizontal.



点击(此处)折叠或打开

  1. mysql> show create table log_ytt_horizontal;
  2. +------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | log_ytt_horizontal | CREATE TABLE `log_ytt_horizontal` (
  6.   `ids` bigint(20) NOT NULL,
  7.   `start_time` datetime DEFAULT NULL,
  8.   `end_time` datetime DEFAULT NULL,
  9.   PRIMARY KEY (`ids`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  11. +------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. 1 row in set (0.00 sec)


表记录数：

点击(此处)折叠或打开

  1. mysql> select * from log_ytt_horizontal;
  2. +------------+---------------------+---------------------+
  3. | ids | start_time | end_time |
  4. +------------+---------------------+---------------------+
  5. | 5130000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:34 |
  6. | 5220000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:45 |
  7. | 5310000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:33 |
  8. | 5400000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:53 |
  9. | 5490000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:55 |
  10. | 5580000001 | 2014-05-20 21:56:42 | 2014-05-21 03:20:24 |
  11. | 5670000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:58 |
  12. | 5760000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:07 |
  13. | 5850000001 | 2014-05-20 21:56:42 | 2014-05-21 03:20:18 |
  14. | 5940000001 | 2014-05-20 21:56:42 | 2014-05-21 03:20:00 |
  15. | 6030000001 | 2014-05-20 21:56:42 | 2014-05-21 03:20:00 |
  16. | 6120000001 | 2014-05-20 21:56:42 | 2014-05-21 03:20:09 |
  17. | 6210000001 | 2014-05-20 21:56:42 | 2014-05-21 03:25:45 |
  18. | 6300000001 | 2014-05-20 21:56:42 | 2014-05-21 03:28:13 |
  19. | 6390000001 | 2014-05-20 21:56:42 | 2014-05-21 03:25:37 |
  20. | 6480000001 | 2014-05-20 21:56:42 | 2014-05-21 03:25:05 |
  21. | 6570000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:34 |
  22. | 6660000001 | 2014-05-20 21:56:42 | 2014-05-21 03:28:07 |
  23. | 6750000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:17 |
  24. | 6840000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:17 |
  25. | 6930000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:59 |
  26. | 7020000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:32 |
  27. | 7110000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:46 |
  28. | 7200000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:45 |
  29. | 7290000001 | 2014-05-20 21:56:42 | 2014-05-21 03:20:02 |
  30. | 7380000001 | 2014-05-20 21:56:42 | 2014-05-21 03:19:48 |
  31. | 7470000001 | 2014-05-20 21:56:42 | 2014-05-21 03:26:14 |
  32. | 7560000001 | 2014-05-20 21:56:42 | 2014-05-21 03:27:45 |
  33. | 7650000001 | 2014-05-20 21:56:42 | 2014-05-21 03:28:37 |
  34. | 7740000001 | 2014-05-20 21:56:42 | 2014-05-21 03:28:26 |
  35. +------------+---------------------+---------------------+
  36. 30 rows in set (0.00 sec)


如果对这种稍微冗余一些的表来进行查询，那么对表的访问以及CPU的资源占用都达到了最低。

点击(此处)折叠或打开

  1. mysql> select sec_to_time(avg(timestampdiff(second,start_time,end_time))) as 'Running' from log_ytt_horizontal;
  2. +---------------+
  3. | Running |
  4. +---------------+
  5. | 05:27:08.8333 |
  6. +---------------+
  7. 1 row in set (0.00 sec)



上一篇：【原创】MySQL 以及 Python 实现排名窗口函数
下一篇：【原创】PostgreSQL hstore 列性能提升一例