MYSQL AUTO_INCREMENT奇技淫巧

2018-01-31 Galin

产品提了新需求,被删除掉的回贴不显示,删除后,每个帖子的楼层数保持不变,帖子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