MYSQL AUTO_INCREMENT奇技淫巧

产品提了新需求，被删除掉的回贴不显示，删除后，每个帖子的楼层数保持不变，帖子A是第二个回贴，帖子B是第三个回贴，删除掉A后，A不出现在回帖列表，但是B的楼层数还是显示3。

于是需要记录下每个回贴的楼层数，在回帖表里增加字段记录楼层。原来的表结构是

CREATE TABLE `comment` (

 `id` bigint(20) NOT NULL,      评论的帖子ID

 `articleId` bigint(20) DEFAULT NULL,  主贴ID

 UNIQUE KEY `ARTICLE_FLOOR_INDEX` (`articleId`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

最开始的思路是利用mysql 自增字段的特性，可以增加个楼层floor字段，并且和主贴编号articleId建组合索引，floor字段按articleId分组自增。

mysql> CREATE TABLE `comment` (

 `id` bigint(20) NOT NULL,

 `articleId` bigint(20) DEFAULT NULL,

 `floor` int(10) NOT NULL AUTO_INCREMENT,

  KEY`ARTICLE_FLOOR_INDEX` (`articleId`,`floor`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULTCHARSET=latin1 ;

报错：

1075 - Incorrect table definition; therecan be only one auto column and it must be defined as a key

 

 

自增字段floor必须是索引的第一个字段。把索引的两个字段对调如下：

mysql> CREATE TABLE `comment` (

 `id` bigint(20) NOT NULL,

 `articleId` bigint(20) DEFAULT NULL,

 `floor` int(10) NOT NULL AUTO_INCREMENT,

  KEY`ARTICLE_FLOOR_INDEX` (`floor`,`articleId`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULTCHARSET=latin1 ;

Query OK, 0 rows affected

 

这里用测试数据插入了九行。

insert into comment(id,articleId) values(1,1000),(2,2000),(3,3000),(4,1000),(5,2000),(6,3000),(7,1000),(8,2000),(9,3000);

 

看到floor字段按照articleId分组自增。

mysql> select * from comment order by articleId;

+----+-----------+-------+

| id | articleId | floor |

+----+-----------+-------+

|  1|      1000 |     1 |

|  4|      1000 |     2 |

|  7|      1000 |     3 |

|  2|      2000 |     1 |

|  5|      2000 |     2 |

|  8|      2000 |     3 |

|  3|      3000 |     1 |

|  6|      3000 |     2 |

|  9|      3000 |     3 |

+----+-----------+-------+

9 rows in set

 

但是查询时最常用的where 条件是articleId=xxxx，如果按照这样建立索引，查询性能不好，

需要全表扫描，rows=9。

explain select * from comment where articleId=1000;

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

|  1| SIMPLE      | comment | ALL  | NULL          | NULL | NULL    | NULL |   9 | Using where |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

1 row in set

 

山穷水尽疑无路，柳暗花明又一村。数据表引擎修改后为myisam,自增字段就不需要强制放在索引的第一列。

mysql> CREATE TABLE `comment` (

 `id` bigint(20) NOT NULL,

 `articleId` bigint(20) DEFAULT NULL,

 `floor` int(10) NOT NULL AUTO_INCREMENT,

  KEY`ARTICLE_FLOOR_INDEX` (`articleId`,`floor`) USING BTREE

) ENGINE=myisam AUTO_INCREMENT=10 DEFAULTCHARSET=latin1 ;

Query OK, 0 rows affected

 

再看看查询效率

mysql> explain select * from comment where articleId=1000;

+----+-------------+---------+------+---------------------+---------------------+---------+-------+------+-------+

| id | select_type | table   | type | possible_keys       | key                 | key_len | ref   | rows | Extra |

+----+-------------+---------+------+---------------------+---------------------+---------+-------+------+-------+

|  1| SIMPLE      | comment | ref  | ARTICLE_FLOOR_INDEX | ARTICLE_FLOOR_INDEX |9       | const |    3 | NULL |

+----+-------------+---------+------+---------------------+---------------------+---------+-------+------+-------+

 

rows=3，已经把索引充分利用起来。

结论： where子句中使用最频繁的一列放在组合索引的最左边。

 

理论上问题应该解决了，但万万没想到的是最重要的一点，原来的程序框架底层必须要使用事务，InnoDB支持事务，MyISAM不支持，一个美好巧妙的设计就被破坏掉。又乖乖的按照土方法来。

CREATE TABLE `comment` (

 `id` bigint(20) NOT NULL,

 `articleId` bigint(20) DEFAULT NULL,

 `floor` int(10) NOT NULL,

  KEY`ARTICLE_FLOOR_INDEX` (`articleId`,`floor`) USING BTREE

) ENGINE=InnoDB  AUTO_INCREMENT=10 DEFAULTCHARSET=latin1