34,590
社区成员
发帖
与我相关
我的任务
分享请教日志表索引的问题
日志表结构如下图，除了info字段都建立了索引，数据库是sql2012，估计每天会产生万条以上记录
请问：1、type_id字段文档查看是100,文档新建是101,文档修改是102，查询文档相关日志时条件用 type_id>=100 and type_id<200，是否有必要新建一个bigtype字段？
2、大部分查询条件都包括 type_id,unit_id,project_id 这三个条件，是否必要将这三个字段一起做一个索引？
3、部分记录info字段无内容，无内容的info用null更好，还是空字符串更好？目前info字段字数不会超过500基本都是汉字，使用nvarchar(MAX)，nvarchar(500)，varchar(MAX)，有区别吗？
4、document_id字段多为0，这种情况下没有查询这个字段的意义，能否设定为0时，不添加到索引IX_document_id中
5、id不被用来做查询条件，是否有必要用主键和id这个字段？用ctime(默认值getdate())是否会出现重复值，用来做主建是否更好？
6、日志表被我单独建了一个数据库，查询时将两个数据库联合，例如查询未读文档，用LEFT OUTER JOIN
连接两个数据表。目前纠结是否再建个表记录文档是否已读，问这种查询是否不好？
7、日志数据库只新增和查询，不修改不删除，我设置简单日志，请问还能做一些别的优化吗？
如果方便请说一下这么做的理由。
请问：1、type_id字段文档查看是100,文档新建是101,文档修改是102，查询文档相关日志时条件用 type_id>=100 and type_id<200，是否有必要新建一个bigtype字段？
2、大部分查询条件都包括 type_id,unit_id,project_id 这三个条件，是否必要将这三个字段一起做一个索引？
3、部分记录info字段无内容，无内容的info用null更好，还是空字符串更好？目前info字段字数不会超过500基本都是汉字，使用nvarchar(MAX)，nvarchar(500)，varchar(MAX)，有区别吗？
4、document_id字段多为0，这种情况下没有查询这个字段的意义，能否设定为0时，不添加到索引IX_document_id中
5、id不被用来做查询条件，是否有必要用主键和id这个字段？用ctime(默认值getdate())是否会出现重复值，用来做主建是否更好？
6、日志表被我单独建了一个数据库，查询时将两个数据库联合，例如查询未读文档，用LEFT OUTER JOIN
连接两个数据表。目前纠结是否再建个表记录文档是否已读，问这种查询是否不好？
7、日志数据库只新增和查询，不修改不删除，我设置简单日志，请问还能做一些别的优化吗？
如果方便请说一下这么做的理由。
...全文
请发表友善的回复…
发表回复
o光o 2014-01-03
- 打赏
- 举报
再次感谢您的回复
hengkangdba 2014-01-02
- 打赏
- 举报
人工三连断。
發糞塗牆 2014-01-02
- 打赏
- 举报
对于第三个问题，我个人比较偏向用null，第一个是存储问题，第二个是可以用filter index来提高查询速度，另外MAX是LOB类型，存储结构和普通数据不一致，如果长度都在500以内，建索引其实也不是不好。但是最好评估一下平均长度，如果都在400~500之间，建议用定长，如果长度0~500，那就用变长
發糞塗牆 2014-01-02
- 打赏
- 举报
你的问题有点多，无法一下子说明白，最好逐个问，我逐个回答
發糞塗牆 2014-01-02
- 打赏
- 举报
1、type_id字段文档查看是100,文档新建是101,文档修改是102，查询文档相关日志时条件用 type_id>=100 and type_id<200，是否有必要新建一个bigtype字段？
如果你的类型不多，没必要建索引，因为选择度低。
2、大部分查询条件都包括 type_id,unit_id,project_id 这三个条件，是否必要将这三个字段一起做一个索引？
可以，按照每个字段的选择度从高到底定义索引，比如unit_id，所有数据中都是唯一，那么可以做索引的第一列，type_id应该重复数据很多，没必要排第一列。
3、部分记录info字段无内容，无内容的info用null更好，还是空字符串更好？目前info字段字数不会超过500基本都是汉字，使用nvarchar(MAX)，nvarchar(500)，varchar(MAX)，有区别吗？
如果长度能预估，那么不要用MAX，最好指定一个合适的范围。并且如果你的数据不存在多语言，可以不用n类型。
4、document_id字段多为0，这种情况下没有查询这个字段的意义，能否设定为0时，不添加到索引IX_document_id中
2012可以用filter index来提高性能。
5、id不被用来做查询条件，是否有必要用主键和id这个字段？用ctime(默认值getdate())是否会出现重复值，用来做主建是否更好？
id如果是主键，做聚集也行。getdate（）在高度并发的情况下可能会重复
6、日志表被我单独建了一个数据库，查询时将两个数据库联合，例如查询未读文档，用LEFT OUTER JOIN
连接两个数据表。目前纠结是否再建个表记录文档是否已读，问这种查询是否不好？
如非必要，减少表关联的次数
7、日志数据库只新增和查询，不修改不删除，我设置简单日志，请问还能做一些别的优化吗？
这个主要看你的SLA，如果数据库的数据不能丢失，那简单模式不适合用。
發糞塗牆 2014-01-02
- 打赏
- 举报
另外n开头的类型，主要用于处理乱码，比如你的系统是简体中文，但是要存放韩文等，那就必须用n开头的类型，否则就乱码，但是如果仅存简体中文和英文，那就不用，还可以减少存储空间和处理开销
發糞塗牆 2014-01-02
- 打赏
- 举报
1、info字段不会用来搜索，一般不超过100字，我之前用的是 varchar(255)极少情况超过字数，nvarchar(500)应该是够用但说不定会有奇葩情况，如果nvarchar(MAX)与nvarchar(500)效率上和占用空间上没有差别的就不如用nvarchar(MAX)。info字段大部分简体汉字少量英文和数字，用nvarchar是否会比varchar更有效率？
n比没有n的存储空间大一倍，另外max和500存储的空间也不同，索引的话max和500会有一些区别，不过具体我也没实际测过。如果你真不能预估，那就扩大一点，但是不建议直接使用MAX。除非你真的无法预估。
2、id字段在这个表没有意义，所以我想去掉id，用datetime做主键，但datetime可能有重复，能让datetime不重复吗？如果不要主键是否会影响查询效率
你可以用联合主键，有时候自增ID还是有用的。主键虽然的确可以没有，不过根据实践，一个大表应该具有主键和聚集索引（主键默认就是聚集索引）。这对速度、非聚集索引、和空间管理都有效，这个讲起来有点多...
3、日志表单独放一个数据库，查询时连接两个数据库的两个表，不知道这是否不好，还是分别在两个数据库查询，然后再程序里拼接。（就两个地方用到，公告和短消息，需要连接三个表，用户表、公告表、日志表）
一般来说，同一个库内查询会更高效，因为一些信息是库级别的，跨库的话获取的信息会不准确，导致优化器选择次优甚至极差的执行计划。除非表的体积已经达到无法忍受的地步，不然不要贸然分库，对日后管理都有意向。
o光o 2014-01-02
- 打赏
- 举报
谢谢你的回复，我根据你的提示去百度了几篇文章，又涨了一些知识，而且可以立刻用上。
这里还有一些相关疑问请教
1、info字段不会用来搜索，一般不超过100字，我之前用的是 varchar(255)极少情况超过字数，nvarchar(500)应该是够用但说不定会有奇葩情况，如果nvarchar(MAX)与nvarchar(500)效率上和占用空间上没有差别的就不如用nvarchar(MAX)。info字段大部分简体汉字少量英文和数字，用nvarchar是否会比varchar更有效率？
2、id字段在这个表没有意义，所以我想去掉id，用datetime做主键，但datetime可能有重复，能让datetime不重复吗？如果不要主键是否会影响查询效率
3、日志表单独放一个数据库，查询时连接两个数据库的两个表，不知道这是否不好，还是分别在两个数据库查询，然后再程序里拼接。（就两个地方用到，公告和短消息，需要连接三个表，用户表、公告表、日志表）
發糞塗牆 2014-01-02
- 打赏
- 举报
还有一个问题，如果你的日志不是实时操作，而是比如一天才插入一次，可以考虑分区、列存储索引等提高速度的技术。如果你的查询经常是按照时间段来查，对ctime就要重点优化了
