Mysql的一些tips

趁着元旦加班没什么事的时候记录备忘，苦逼的程序员，文章之前来个刚看到的笑话：

“你们用盗版的时候有想过做出这款软件的程序员吗？！他们该如何养家糊口？！” “哈哈哈，别逗了程序员哪有家要养啊”

1、COUNT(1)和COUNT(column)的一个小区别，如果表test中column字段可以为null，那么不论多少行，COUNT(column)都是0；应用场景：在某些LEF JOIN的时候可以用COUNT(IFNULL(b.id, 0))来准确计算联表的数据量。

2、INTO在查询为empty set时将不会起作用，SELECT IFNULL(t.id, 0) FROM t INTO @param;如果t表没有记录，那么这个查询即使加了IFNULL也是没用的，INTO将不会起效，@param是初始值或者上一次被赋予的值，在存储过程中如果循环时，每次循环结束应该将这种INTO获取的变量重新赋值：

WHILE (i < 100) DO
    SELECT t.id FROM t INTO p_id;
    IF p_id != 0 THEN 
        SELECT 'do something';
    END IF;
    SELECT 0 INTO p_id;
END WHILE;

3、EXPLAIN中rows只是个估计值，不要被它欺骗。

构建表A和表C

CREATE TABLE `A` (
  `a_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a_name` char(32) NOT NULL DEFAULT '',
  `b_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`a_id`),
  KEY `idx_b_id` (`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `C` (
  `c_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `c_name` char(32) NOT NULL DEFAULT '0',
  `b_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c_id`),
  KEY `idx_b_id` (`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后往2表中分别写入100W数据，主键自增，b_id和2表主键一致，但是如果主键id%10==0，将b_id置为0；这样A、C表中b_id为0的各有10W条数据。

mysql> EXPLAIN SELECT COUNT(1) FROM A INNER JOIN C ON A.b_id = C.b_id WHERE A.a_id > 500000;
+----+-------------+-------+-------+------------------+----------+---------+-------------+--------+-------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref         | rows   | Extra       |
+----+-------------+-------+-------+------------------+----------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | A     | range | PRIMARY,idx_b_id | PRIMARY  | 4       | NULL        | 500187 | Using where |
|  1 | SIMPLE      | C     | ref   | idx_b_id         | idx_b_id | 4       | test.A.b_id |      1 | Using index |
+----+-------------+-------+-------+------------------+----------+---------+-------------+--------+-------------+

看起来好像只有50W行数据，一般预计5-30秒应该能计算完，然而实际执行这个SQL，我机器用了691秒，然后结果是 5000450000。

原因是2个表中除了和主键一致的b_id外，值为0的b_id在联表的时候产生了一个爆炸性的结果集，

然后查询解释器在解释这个sql时却会认为A、C表通过b_id相联是个1对1的关系，所以这个解释结果是错误的。

具体查询结果的计算公式为：500000 - 50000 + 50000 * 100000 = 5000450000