MYSQL AUTO_INCREMENT奇技淫巧

原创 2018年01月29日 17:45:59

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

 

 

 

版权声明:本文为博主原创文章,未经博主允许不得转载。

MySQL auto_increment 用法

用法: CREATE TABLE test ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(15...
  • w5167839
  • w5167839
  • 2014年06月24日 23:09
  • 1357

MYSQL AUTO_INCREMENT自增的用法

alter table tbname auto_increment = x ; 设置表tbname的唯一auto_increment字段起始值从x开始,如果此表数据量很多的话,这样执行起来会很慢. ...
  • xuxu120
  • xuxu120
  • 2017年06月01日 18:08
  • 1246

C++的一个奇技淫巧

C++如何写一个函数,得到一个数组的长度呢? size_t GetArrayLength(int Array []) {      return sizeof(Array)/sizeof(Arr...
  • Lyintong
  • Lyintong
  • 2016年03月18日 16:19
  • 231

MySQL主从复制中关于AUTO_INCREMENT的奇怪问题

昨天修改一个表的字段,发现主从复制同步失效了,但同步进程没有出错,mysql日志也没有出错,仔细查找了一下原因,发现是新增的自增主键字段的起始值在主从库上不一致,导致按主键更新数据时无法在从库找到对应...
  • newhappy2008
  • newhappy2008
  • 2008年04月21日 21:58
  • 8208

MySql查看与修改auto_increment方法

本文将介绍如何查看表的auto_increment及其修改方法查看表当前auto_increment表的基本数据是存放在mysql的information_schema库的tables表中,我们可以使...
  • fdipzone
  • fdipzone
  • 2015年12月28日 22:07
  • 20355

mysql auto_increment 的坑

Statement-Based Replication,不要设置innodb_autoinc_lock_mode=2 会导致主从数据不一致 innodb_autoinc_lock_mode=2 时,a...
  • hittata
  • hittata
  • 2016年05月24日 19:41
  • 941

mysql修改表为字段添加auto_increment

文章来源: http://hi.baidu.com/luzheng22/blog/item/67d5ab7fc7825b0f28388add.html 比如我创建这样一个表CREATE TABLE p...
  • swallow95599
  • swallow95599
  • 2009年04月21日 20:20
  • 2060

MySQL中创建表时AUTO_INCREMENT的用法

MySQL中创建表时AUTO_INCREMENT的用法 导读:本文介绍了MySQL中创建表时AUTO_INCREMENT的用法。 关键词:AUTO_INCREMENT MySQL数据库...
  • rongwenbin
  • rongwenbin
  • 2013年08月19日 16:03
  • 1563

MySQL中,AUTO_INCREMENT的困惑?

对于一个AUTO_INCREMENT ? 1)它的约束能力如何?像NOT NULL有那么强吗,当一个字段是AUTO_INREMENT,给他一个ID,数据库层应该不会报错吧?     MySQL中cre...
  • budapest
  • budapest
  • 2012年02月15日 14:48
  • 2067

mysql 分表技术 partition与auto_increment

mysql 5.1之后,开始新增了partition功能,可以做到对代码透明的情况下进行数据分区。但是从实际使用中来看,限制颇多: 如果用来分区的字段和主键不是同一个,则不能分区见很早前的一...
  • chimei2345
  • chimei2345
  • 2015年01月07日 16:15
  • 522
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MYSQL AUTO_INCREMENT奇技淫巧
举报原因:
原因补充:

(最多只允许输入30个字)