MySQL自增序列的妙用

MySQL字增序列的基本知识：

自增字段的数据类型必须为：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT中的一种，并且必须作为主键或联合主键的一部分。若是作为主键的一部分，自增序列字段值将会丧失唯一性保证。

自增序列，对INSERT语句敏感，读UPDATE，DELETE语句是不敏感的。演示示例：

root@localhost : test 02:36:44> CREATE TABLE `auto_test` (

    ->   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

    ->   `MEMBER_ID` varchar(32) DEFAULT '',

    ->   PRIMARY KEY (`ID`),

    ->   UNIQUE KEY `idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

    -> ) ENGINE=InnoDB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

root@localhost : test 02:39:20> INSERT INTO auto_test(member_id) values('eugene');

Query OK, 1 row affected (0.00 sec)

root@localhost : test 02:43:21> show create table auto_test;

| Table     | Create Table                                                                                                                                                                                                                                          

| auto_test | CREATE TABLE `auto_test` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `MEMBER_ID` varchar(32) DEFAULT '',

  PRIMARY KEY (`ID`),

  UNIQUE KEY `idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

) ENGINE=InnoDB  AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

   root@localhost : test 04:39:11> insert into auto_test(id,member_id) values(10,'alibaba');

Query OK, 1 row affected (0.00 sec)

root@localhost : test 04:41:20> show create table auto_test;

Table     | Create Table                                                                                                                                                                                                                                          

| auto_test | CREATE TABLE `auto_test` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `MEMBER_ID` varchar(32) DEFAULT '',

  PRIMARY KEY (`ID`),

  UNIQUE KEY `idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

) ENGINE=InnoDB  AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

root@localhost : test 04:53:29> update auto_test set ID=ID+20 WHERE ID =10;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

root@localhost : test 04:54:40> show create table auto_test;

Table     | Create Table                                                                                                                                                                                                                                          

| auto_test | CREATE TABLE `auto_test` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `MEMBER_ID` varchar(32) DEFAULT '',

  PRIMARY KEY (`ID`),

  UNIQUE KEY `idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

) ENGINE=InnoDB  AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

所以若真的特殊情况下，需要使用UPDATE 语句更新自增序列值，而且更新后的自增序列的最大值超过定义中的AUTO_INCREMENT值，就必须使用类似字句：

ALTER TABLE  auto_test  AUTO_INCREMENT=MAX(ID)+1;

MySQL还提供了两个全局参数AUTO_INCREMENT_INCREMENT与AUTO_INCREMENT_OFFSET，用于配置MySQL实例的自增序列增长起始值与步长。

AUTO_INCREMENT_INCREMENT= n ，则设置自增序列增长的步长为n；

AUTO_INCREMENT_OFFSET=m，则设置自增序列的起始位置为m；

若使用这个特性与复制功能结合的话，就可以方便地实现Master-Master的两边读写，以及更多的应用扩展。

根据SNS、网游等应用的特点，设计合适可扩展的分布式架构，目标：

1> 采用代理框架，对前端应用程序绝对透明，可以考虑数据库端实现一些UNION或JOIN

2> 根据数据库服务器的负载能力，随机写入数据

3> 出现宕机不影响任何在线上或非在线用户，数据损失可控且最小

4>增加数据库物理服务器，除修改ID对应的服务器IP外，中间代理层不作任何修改。

5> 最少的硬件投资，达到99.99%的要求

以SNS网站为例分析：

1>     根据负载情况，随即向任何集群中的服务器上写数据

2>     更新，删除，查询，根据ID到指定的服务器上执行

3>     在网站内部进行数据流的流转操作，以加密的ID值为唯一识别

特殊处理：

假设：

用户甲的信息存储在物理A服务器上；

用户乙的信息存储在物理B服务器上；

甲加乙为好友，则向存储甲的A服务器上存储一条信息，另外还需要在存储乙的B服务器上存储一条信息，表明A曾加B为好友，表的结构是一样的。

然后使用服务器C复制A服务器与B服务器的数据，由于MySQL自身只提供一个Master对多个Slave的复制方式，而无法提供多个Master对一个Slave的复制模式。为此，我们可以考虑对MySQL源代码进行修改，或使用原始的办法，把A,B服务器上不是当前正在使用的日志文件，就移动到C服务器上，然后并行地执行，mysqlbinlog logname.00*** | mysql –uroot –p***的方式执行。为此我们可能会进步把MySQL的二进制的日志文件限制大小为256M或512M，以及日志文件为各个服务器的名称。

     其中C服务器只有在A或 B出现问题的时候，提供读的功能。当出现问题的接点恢复正常了，立马把读的工作转移到恢复起来的服务器上。

     我们可以考虑一台备机，对应3到4个集群，而备机采用SAS硬盘即可，存储空间要大点，内存也稍微大点。

     每个集群存储20G-50G的数据，而备机则需要存储80-200G

建议：

    二进制日志文件设置太小，会导致MySQL频繁切换日志文件，也会增加额外的开销，所以日志文件要设置为合理的大小。主机宕机后，读切换到备机上，会有一段最近的数据无法查询或非最近的数据。为减少这样的损失，最好的办法是自己去修改MySQL复制方面的源代码，改造为可支持多对一的方式，这样我们因宕机而查询不到或非最新的数据量将非常小，但是这项工作会增加我们的成本，修改代码的工作未预估。若改成功，将也可以用于其他场合。

缺点：

若使用二进制日志文件，主动推送模式，再翻译二进制日志文件，并应用到数据库服务器上，此过程本身需要花费一些时间；另外一个弊端是，需要等待日志文件发生切换，然后再推送到备机。若日志文件大小未到达我们设置的大小，也不强制切换日志文件，那备机将无限地等待日志文件推送到备机上。

另外一个解决主机到备机的数据复制方式，就是扩展MySQL的复制方式，使其支持多对一的复制场景，将需要我们先花费人力资本，去修改甚至重新改写MySQL复制功能的源代码，这对我们而言存在成本，以及修改后是否还存在BUG，等问题。但是能将主机与备机之间的数据差异时间，缩短到非常时间的时间范围内，数据写与更新量适中的话，应该可以控制在秒级别。

对数据的ID依赖性强，我们可能必须对ID进行加密处理，否则可能存在安全漏洞。而我们使用加密技术后，又会增加我们的一些工作成本与运算成本。

若是根据文章标题或内容等搜索，若是走搜索引擎的话，到是可以搜索到内容，并且能转换为ID。非搜索引擎情况下，由于无法转换为对应的ID，就无法定位到那台物理服务器上进行查询操作。对于我们只有3-4个机群的情况下，我们将可以考虑由备机，在此情况下，对外提供搜索服务，返回想要获取的内容，再根据选取的内容对应的ID，而把对应的更改操作，发送到相应的服务器上。

此描述的搜索情况，是有个前提，备机要与主机保持比较小的差异时间，否则可能出现搜索不到想要的内容。要减少这样的机率事情的发生，前提就是需要我们改造MySQL复制方式，使提供多对一的方式。