ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MySQL自增序列的妙用

MySQL自增序列的妙用

Linux操作系统 作者:你好达西0601 时间:2015-08-19 14:12:16 0 删除 编辑

MySQL自增序列的妙用

MySQL字增序列的基本知识:

自增字段的数据类型必须为:TINYINTSMALLINTMEDIUMINTINTBIGINT中的一种,并且必须作为主键或联合主键的一部分。若是作为主键的一部分,自增序列字段值将会丧失唯一性保证。

自增序列,对INSERT语句敏感,读UPDATEDELETE语句是不敏感的。演示示例:

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_INCREMENTAUTO_INCREMENT_OFFSET,用于配置MySQL实例的自增序列增长起始值与步长。

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

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

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

 

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

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

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的二进制的日志文件限制大小为256M512M,以及日志文件为各个服务器的名称。

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

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

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

建议:

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

 

缺点:

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

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

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

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

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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29754346/viewspace-1776287/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-07-15

  • 博文量
    1
  • 访问量
    1865