MySQL的一些历史遗留问题，希望ACMAIN_CHM、zuoxingyu等高手帮忙回答下

不考虑你的说法的情况下。 b_post; 是符合数据3NF设计的。但按范式设计在某个情况下会影响查询速度。这查询速度无法满足要求的情况下，则有时必须根据常用的查询添加必要的冗余字段，比如a_post;中的username 。所有对初学数据库设计的来说，首先的要求是严格遵守范式，数据库设计起码达到3NF，但对有经验的设计来说，则应该理解范式的精神的情况下根据需要进行设计。

好长..
1:
我觉得2个设计都可以,不过看你自己的业务需求,适当的冗余可以减少join表的消耗;
2:
能让程序计算的就让程序计算好,就比如一般都不会在mysql中使用正则,都在程序中使用;
3:
个人喜欢用datetime,直观,读取的之后不需要转换,而用int 无非就是减少存储空间.现在硬盘这么大,这个可以忽视;
int(4),int(10)没有差别.数字是表示显示的长度而已,数据库实际存的的是他的取值范围;
4:
where md5 is not null and md5 !=0
不知道有错没

（2）mysql中的函数该不该用的问题
单纯从这句"INSER INTO post SET postid=NULL,dateline=UNIX_TIMESTAMP() " 来说，会慢一点儿，但MYSQL需要的时间仅是1.2E-8，可以说是微乎其微。单纯从这句来说，不可能让人感到速度上的差异。但如果是 select * from table where cdate>=curdate(); 则会有速度上的影响，不如select * from table where cdate>='2011-02-14'; 因为第二句可以利用缓存。

第一个问题：根据情况来定，如果数据量大，pv和查询概率很高。冗余字段不是很复杂，短varchar或者int类型的，可以在表里增加冗余字段提升速度，这就是所谓的以空间换取时间，

啊不好意思，刚才没发现打错了。我现在用的是A方案，我认为B方案（不存username的）会死机。
但是我现在不能修改帖子了。我的那一段话是：
表a_post与表b_post的区别，表b_post中没有username字段。
a_post 不用交表
b_post 需要交表
其实原先我一直都是按照表b_post的这样设计的，因为表 user里面已经存了username字段了。如果要取的话直接交表left join。但是后来我发现discuz的论坛的帖子的表的设计不是这样的（表 cdb_threads），他里面直接存入了发帖者的用户名称，是按照a_post的这样的方案来设计的。而且以前我遇到过，用的是b_post这样的方案（交表的那种）。
同时连几个表，user有10万数据，b_post表有100万数据，如果真的是按照表b_post来设计的话，那么要查询通过title和username同时来查询b_post中的数据，那么是直接死机，数据库服务器都垮掉了。（我以前的一个提问：http://topic.csdn.net/u/20091218/11/390b8c91-fe51-4ef0-a485-a5d6f1747500.html）
A方案是东西全都放在自己表里面，查询的时候直接一个表。
B方案是东西放了很多个表，查询的时候需要交表。
是这样的。
我现在不知道是用A方案好还是用B方案好。

如果是用A方案，查询的时候不用交表，自然快一些。但是存储很站空间，而且如果别人的用户名更新了的话，那么对应的post里面的表也要更新啊。不过discuz是用这样的方案，更新了用户名的时候需要更新多个表。
B方案就是在更新了用户名的时候就不用更新其他的表了，但是在查询的时候必须交表，对于服务器不好的主机来说，会死机的，如果超过10万数据并且很庞大的数据的时候。

第二个问题，数据库，顾名思义，是“存数据的库”，能不用mysql 的函数就不用，在程序里计算好，比如时间，字符串的截取等，正如狼头哥写到的，有时候还要考虑索引，某些情况下如果用了mysql的函数，就会使索引失效，所以尽量只对mysql做最基本的查询添加删除修改操作

第三个问题：建议你阅读手册，int（10）后面的10不是代表长度，而是代表显示的长度，
varchar（10）这样的才代表字符长度如果存储的数字范围确定，那么就用最小的满足你的类型，比如tinyint，smallint，而不是int，可以节约存储空间，增加查询速度

啊因为这些都是一些遗留的没解决的问题，所以想放到一起来都解决额
不行啊。比如百度贴吧里面的发帖时间，其实百度里面存入的是精确到秒的发帖时间，但是显示出来的确实精确到分的时间，如果用datetime的话，这样不太好啊。因为有的地方最好不要让访问者看到精确到秒的时间的。
在PHP中可以直接用date("Y-m-d H:i:s",time())来直接格式化UNIX的时间戳格式的时间。

第四个问题：数据库尽量不要用default null 字段，因为数据库在查询和匹配时会专门判断null字段从而付出时间代价最好用default ""或default 0查询null字段用select * from tb where a is null查询 0就是
select * from tb where a='0'

（3）mysql中的时间该如何存的问题，用int(10)还是？以及tinyint的值的长度的问题

我设计表的时候他们都是这样的NOT NULL的但是我也没给他们设置默认值的。
但是我发现在有的时候，比如数据库搬迁到其他的服务器上的数据库的时候，这样设置了NOT NULL但是却没有设置默认值是多少的时候就会数据库导入不了，一直报错。CREATE TABLE IF NOT EXISTS `pjob_thread` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `md5` char(32) NOT NULL COMMENT '帖子的唯一标识',
  `fid` mediumint(8) unsigned NOT NULL default '0' COMMENT '论坛ID',
  `cidbig` varchar(24) NOT NULL COMMENT '大分类名称',
  `cidsmall` varchar(24) NOT NULL COMMENT '小分类名称',
  `subject` varchar(200) NOT NULL,
  `keywords` varchar(120) NOT NULL,
  `description` varchar(200) NOT NULL,
  `desc` text NOT NULL COMMENT '描述内容',
  `author` char(80) NOT NULL COMMENT '发布者名称',
  `authorid` int(10) unsigned NOT NULL default '0' COMMENT '用户ID',
  `lastpost` int(10) unsigned NOT NULL default '0',
  `lastposter` char(80) NOT NULL,
  `replies` int(10) unsigned NOT NULL default '0',
  `date1` int(10) unsigned NOT NULL default '0' COMMENT '发帖日期',
  `date2` int(10) unsigned NOT NULL default '0' COMMENT '更新日期',
  `closed` tinyint(1) unsigned NOT NULL default '0' COMMENT '1表示关闭,关闭后无法回复',
  `ip` varchar(20) NOT NULL COMMENT '发布者的IP',
  `hit` int(10) unsigned NOT NULL default '0',
  `hide` tinyint(1) unsigned NOT NULL default '1' COMMENT '1表示隐藏，0表示显示',
  PRIMARY KEY  (`tid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='主贴' AUTO_INCREMENT=1;
这样设置的。另外我向问一下，我这样设置表怎么样，存储类型用MyISAM的。字符集用UTF8的。然后都是小写。
还有就是数字都是用UNSIGNED的。这样怎么样啊。

（4）mysql中值为“空”或者“0”的记录如何最大效率的匹配？where md5!=0
因为 NULL!=0 同样返回 FALSE

第一个问题，根据范式的要求，要用B方案。至于你说的一连接就死机，可能是你的索引没有创建好。USER里的USERID是主键，查询的时候select title,username from b_post inner join user on b_post.userid=user.userid 这样的写法下，MYSQL优化器会全表扫描USER表，根据索引去查询b_post表，如果b_post表的userid上没有索引，那么b_post也是全表扫描了。冗余出一个字段username出来，在维护user表的时候，同时也要维护a_post表，需要触发器来执行。

但是我用EXPLAIN来看的话，交表中涉及到的表的数据都是全部都扫描了一遍啊。
涉及表pjob_threads 和 pjob_forums 。◆ 表pjob_thread 和表pjob_forums 结构如下 ◆ mysql> desc pjob_thread;
+----------------+-----------------------+------+-----+---------+----------------+
| Field          | Type                  | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+----------------+
| tid            | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| md5            | char(32)              | NO   |     | NULL    | |
| fid            | mediumint(8) unsigned | NO   |     | 0       | |
| cidbig         | varchar(24)           | NO   |     | NULL    | |
| cidsmall       | varchar(24)           | NO   |     | NULL    | |
| subject        | varchar(200)          | NO   |     | NULL    | |
| keywords       | varchar(120)          | NO   |     | NULL    | |
| description    | varchar(200)          | NO   |     | NULL    | |
| desc           | text                  | NO   |     | NULL    | |
| author         | char(80)              | NO   |     | NULL    | |
| authorid       | int(10) unsigned      | NO   |     | 0       | |
| lastpost       | int(10) unsigned      | NO   |     | 0       | |
| lastposter     | char(80)              | NO   |     | NULL    | |
| replies        | int(10) unsigned      | NO   |     | 0       | |
| date1          | int(10) unsigned      | NO   |     | 0       | |
| date2          | int(10) unsigned      | NO   |     | 0       | |
| closed         | tinyint(1) unsigned   | NO   |     | 0       | |
| ip             | varchar(20)           | NO   |     | NULL    | |
| hit            | int(10) unsigned      | NO   |     | 0       | |
| hide           | tinyint(1) unsigned   | NO   |     | 1       | |
+----------------+-----------------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)mysql> desc pjob_forums;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| fid    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| alias  | char(60)             | NO   |     | NULL    |                |
| name   | varchar(60)          | NO   |     | NULL    |                |
| level  | tinyint(1) unsigned  | NO   |     | NULL    |                |
| pid    | smallint(5) unsigned | NO   |     | NULL    |                |
| csdnid | char(50)             | NO   |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)我的查询发现他们都是交表中涉及到的表都全表扫描了啊（pjob_thread中有10万多数据，表pjob_forums有两百多数据）。
-- 直接交表
explain
SELECT t.*,f.*,s.*
FROM pjob_thread t
LEFT JOIN pjob_forums f ON t.cidbig=f.alias AND f.level=1
LEFT JOIN pjob_forums s ON t.cidsmall=s.alias AND s.level=2
WHERE t.md5='701ca9abb5a8fd42451cacd3092b1eda' AND
      t.hide=0 AND t.csdn_tid!=''
/*
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100002 | Using where |
|  1 | SIMPLE      | f     | ALL  | NULL          | NULL | NULL    | NULL |    289 |             |
|  1 | SIMPLE      | s     | ALL  | NULL          | NULL | NULL    | NULL |    289 |             |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
3 rows in set (0.00 sec)
*/-- 这个应该称作视图吧
explain
SELECT *,
  (SELECT `name` FROM pjob_forums f WHERE f.alias=t.cidbig AND f.level=1) AS cidbigName,
  (SELECT `name` FROM pjob_forums s WHERE s.alias=t.cidsmall AND s.level=2) AS cidsmallName
FROM pjob_thread t
WHERE md5='701ca9abb5a8fd42451cacd3092b1eda' AND hide=0 AND csdn_tid!=''/*+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | t     | ALL  | NULL          | NULL | NULL    | NULL | 100002 | Using where |
|  3 | DEPENDENT SUBQUERY | s     | ALL  | NULL          | NULL | NULL    | NULL |    289 | Using where |
|  2 | DEPENDENT SUBQUERY | f     | ALL  | NULL          | NULL | NULL    | NULL |    289 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
3 rows in set (0.00 sec)*/

全表扫描了，是因为你没有建索引嘛
show index from ....
看看你的索引情况。

-- 直接交表
explain
SELECT t.*,f.*,s.*
FROM pjob_thread t
LEFT JOIN pjob_forums f ON t.cidbig=f.alias AND f.level=1
LEFT JOIN pjob_forums s ON t.cidsmall=s.alias AND s.level=2
WHERE t.md5='701ca9abb5a8fd42451cacd3092b1eda' AND
      t.hide=0 AND t.csdn_tid!=''
/*
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100002 | Using where |
|  1 | SIMPLE      | f     | ALL  | NULL          | NULL | NULL    | NULL |    289 |             |
|  1 | SIMPLE      | s     | ALL  | NULL          | NULL | NULL    | NULL |    289 |             |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
3 rows in set (0.00 sec)
*/
像这样的多表连接，MYSQL用的是左深度树的方法，就是说第一个表和第二个组成一个记录集，然后和第三个JOIN。
所以你需要在f.alias 和s.alias 上面建个索引。

我看是 “我”学mysql过程中积攒的一些疑问，
而不是 MySQL的一些历史遗留问题！
“mysql历史遗留问题”这几个字很容易让人想到，mysql历史版本产生的兼容性问题。习惯问题等。

1.典型的范式设计问题..
那个a表违反3nf 但是对于查询有好处，如果经常要出现a表中username字段跟那几个字段一起出现的查询，就用空间的牺牲换取时间的快速，减少表链接..反过来就用b表咯，复合3nf2.时间字段类型
个人支持这个int存储的方法，经常使用这样的方法，便于存储，查询的时候只要转换就可以了。3.同上，注意int(4) int(10) 是一样的，后面括号里面的数字只是显示的样式长度。。4.md5 !=0。。

我是想说#20楼中我已经给表pjob_forums的字段alias用了索引了。
但是为什么还是扫描了180行（该表总数据共289）。
我现在也明白了您的意思了。我在表pjob_thread的字段md5加了索引了。然后执行SQL：mysql> explain
    -> SELECT t.*,f.*,s.*
    -> FROM pjob_thread t
    -> LEFT JOIN pjob_forums f ON t.cidbig=f.alias AND f.level=1
    -> LEFT JOIN pjob_forums s ON t.cidsmall=s.alias AND s.level=2
    -> WHERE t.md5='701ca9abb5a8fd42451cacd3092b1eda' AND
    -> t.hide=0 AND t.csdn_tid!=''
    -> ;
+----+-------------+-------+------+---------------+-------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref                   | rows | Extra       |
+----+-------------+-------+------+---------------+-------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | t     | ref  | md5           | md5   | 96      | const                 |   10 | Using where |
|  1 | SIMPLE      | f     | ref  | alias         | alias | 180     | zbphp_beta.t.cidbig   |    1 |             |
|  1 | SIMPLE      | s     | ref  | alias         | alias | 180     | zbphp_beta.t.cidsmall |    1 |             |
+----+-------------+-------+------+---------------+-------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)mysql>
我的疑问是为什么表pjob_thread扫描的是96行而不是其他的行？
然后pjob_forums也是为什么是扫描的是 180行啊。
然后我有一个习惯是把表pjob_thread中的字段md5设为为唯一（UNIQUE）
设置为UNIQUE和INDEX有什么区别啊。。
在检索上面。
设置为index后。现在速度快多了。

为什么加了索引后查询时间反而变长了啊。
原先md5不为索引的时候，列出20条，花费平均 0.040秒。
现在md5加上索引的时候，列出20条，花费平均 0.285秒。这是怎么回事啊。

从你的执行计划来看，首先用CONST选出10行记录，然后根据索引找另外的表。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时md5加不加索引，都是全表扫描。

调试易

MySQL的一些历史遗留问题，希望ACMAIN_CHM、zuoxingyu等高手帮忙回答下

解决方案 »