【php】mysql全局ID生成方案，phpmysql全局id
【php】mysql全局ID生成方案，phpmysql全局id
生产系统随着业务增长总会经历一个业务量由小变大的过程，可扩展性是考量数据库系统高可用性的一个重要指标;在单表/数据库数据量过大，更新量不断飙涨时，MySQL DBA往往会对业务系统提出sharding的方案。既然要sharding，那么不可避免的要讨论到sharding key问题，在有些业务系统中，必须保证sharding key全局唯一,比如存放商品的数据库等，那么如何生成全局唯一的ID呢，下文将从DBA的角度介绍几种常见的方案。
1、使用CAS思想
什么是CAS协议
Memcached于1.2.4版本新增CAS(Check and Set)协议类同于Java并发的CAS(Compare and Swap)原子操作，处理同一item被多个线程更改过程的并发问题
CAS的基本原理
基本原理非常简单，一言以蔽之，就是“版本号”，每个存储的数据对象，都有一个版本号。
我们可以从下面的例子来理解：
不采用CAS，则有如下的情景：
- 第一步，A取出数据对象X；
- 第二步，B取出数据对象X；
- 第三步，B修改数据对象X，并将其放入缓存；
- 第四步，A修改数据对象X，并将其放入缓存。
结论：第四步中会产生数据写入冲突。
采用CAS协议，则是如下的情景。
- 第一步，A取出数据对象X，并获取到CAS-ID1；
- 第二步，B取出数据对象X，并获取到CAS-ID2；
- 第三步，B修改数据对象X，在写入缓存前，检查CAS-ID与缓存空间中该数据的CAS-ID是否一致。结果是“一致”，就将修改后的带有CAS-ID2的X写入到缓存。
- 第四步，A修改数据对象Y，在写入缓存前，检查CAS-ID与缓存空间中该数据的CAS-ID是否一致。结果是“不一致”，则拒绝写入，返回存储失败。
这样CAS协议就用了“版本号”的思想，解决了冲突问题。（乐观锁概念）
其实这里并不是严格的CAS，而是使用了比较交换原子操作的思想。
生成思路如下：每次生成全局id时，先从sequence表中获取当前的全局最大id。然后在获取的全局id上做加1操作，加1后的值更新到数据库，如加1后的值为203,表名是users，数据表结构如下：
CREATE TABLE `SEQUENCE` (
`name` varchar(30) NOT NULL COMMENT '分表的表名',
`gid` bigint(20) NOT NULL COMMENT '最大全局id',
PRIMARY KEY (`name`)
) ENGINE=innodbsql语句
update sequence set gid = 203 where name = 'users' and gid < 203;
sql语句的 and gid < 203 是为了保证并发环境下gid的值只增不减。
如果update语句的影响记录条数为0说明，已经有其他进程提前生成了203这个值，并写入了数据库。需要重复以上步骤从新生成。
代码实现如下：
//$name 表名
function next_id_db($name){
//获取数据库全局sequence对象
$seq_dao = Wk_Sequence_Dao_Sequence::getInstance();
$threshold = 100; //最大尝试次数
for($i = 0; $i < $threshold; $i++){
$last_id = $seq_dao->get_seq_id($name);//从数据库获取全局id
$id = $last_id +1;
$ret = $seq_dao->set_seq_id($name, $id);
if($ret){
return $id;
break;
}
}
return false;
}2、使用全局锁
在进行并发编程时，一般都会使用锁机制。其实，全局id的生成也是解决并发问题。
生成思路如下：
在使用redis的setnx方法和memcace的add方法时，如果指定的key已经存在，则返回false。利用这个特性，实现全局锁
每次生成全局id前，先检测指定的key是否存在，如果不存在则使用redis的incr方法或者memcache的increment进行加1操作。这两个方法的返回值是加1后的值，如果存在，则程序进入循环等待状态。循环过程中不断检测key是否还存在，如果key不存在就执行上面的操作。
代码如下：
//使用redis实现
//$name 为 逻辑表名
function next_id_redis($name){
$redis = Wk_Redis_Util::getRedis();//获取redis对象
$seq_dao = Wk_Sequence_Dao_Sequence::getInstance();//获取存储全局id数据表对象
if(!is_object($redis)){
throw new Exception("fail to create redis object");
}
$max_times = 10; //最大执行次数 避免redis不可用的时候 进入死循环
while(1){
$i++;
//检测key是否存在，相当于检测锁是否存在
$ret = $redis->setnx("sequence_{$name}_flag",time());
if($ret){
break;
}
if($i > $max_times){
break;
}
$time = $redis->get("sequence_{$name}_flag");
if(is_numeric($time) && time() - $time > 1){//如果循环等待时间大于1秒，则不再等待。
break;
}
}
$id = $redis->incr("sequence_{$name}");
//如果操作失败，则从sequence表中获取全局id并加载到redis
if (intval($id) === 1 or $id === false) {
$last_id = $seq_dao->get_seq_id($name);//从数据库获取全局id
if(!is_numeric($last_id)){
throw new Exception("fail to get id from db");
}
$ret = $redis->set("sequence_{$name}",$last_id);
if($ret == false){
throw new Exception("fail to set redis key [ sequence_{$name} ]");
}
$id = $redis->incr("sequence_{$name}");
if(!is_numeric($id)){
throw new Exception("fail to incr redis key [ sequence_{$name} ]");
}
}
$seq_dao->set_seq_id($name, $id);//把生成的全局id写入数据表sequence
$redis->delete("sequence_{$name}_flag");//删除key，相当于释放锁
$db = null;
return $id;
}3、redis和db结合
使用redis直接操作内存，可能性能会好些。但是如果redis死掉后，如何处理呢？把以上两种方案结合，提供更好的稳定性。
代码如下：
function next_id($name){
try{
return $this->next_id_redis($name);
}
catch(Exception $e){
return $this->next_id_db($name);
}
}4、Flicker的解决方案
因为mysql本身支持auto_increment操作，很自然地，我们会想到借助这个特性来实现这个功能。Flicker在解决全局ID生成方案里就采用了MySQL自增长ID的机制（auto_increment + replace into + MyISAM）。一个生成64位ID方案具体就是这样的：
先创建单独的数据库(eg:ticket)，然后创建一个表：
CREATE TABLE Tickets64 (
id bigint(20) unsigned NOT NULL auto_increment,
stub char(1) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY stub (stub)
) ENGINE=MyISAM当我们插入记录后，执行SELECT * from Tickets64，查询结果就是这样的：
+-------------------+------+ | id | stub | +-------------------+------+ | 72157623227190423 | a | +-------------------+------+
在我们的应用端需要做下面这两个操作，在一个事务会话里提交：
REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();这样我们就能拿到不断增长且不重复的ID了。
到上面为止，我们只是在单台数据库上生成ID，从高可用角度考虑，
接下来就要解决单点故障问题：Flicker启用了两台数据库服务器来生成ID，
通过区分auto_increment的起始值和步长来生成奇偶数的ID。
TicketServer1: auto-increment-increment = 2 auto-increment-offset = 1 TicketServer2: auto-increment-increment = 2 auto-increment-offset = 2
最后，在客户端只需要通过轮询方式取ID就可以了。
- 优点：充分借助数据库的自增ID机制，提供高可靠性，生成的ID有序。
- 缺点：占用两个独立的MySQL实例，有些浪费资源，成本较高。
参考：
http://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/
http://segmentfault.com/a/1190000004090537
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
PHP and Python: Comparing Two Popular Programming Languages
Apr 14, 2025 am 12:13 AM
PHP and Python each have their own advantages, and choose according to project requirements. 1.PHP is suitable for web development, especially for rapid development and maintenance of websites. 2. Python is suitable for data science, machine learning and artificial intelligence, with concise syntax and suitable for beginners.
PHP's Purpose: Building Dynamic Websites
Apr 15, 2025 am 12:18 AM
PHP is used to build dynamic websites, and its core functions include: 1. Generate dynamic content and generate web pages in real time by connecting with the database; 2. Process user interaction and form submissions, verify inputs and respond to operations; 3. Manage sessions and user authentication to provide a personalized experience; 4. Optimize performance and follow best practices to improve website efficiency and security.
The Enduring Relevance of PHP: Is It Still Alive?
Apr 14, 2025 am 12:12 AM
PHP is still dynamic and still occupies an important position in the field of modern programming. 1) PHP's simplicity and powerful community support make it widely used in web development; 2) Its flexibility and stability make it outstanding in handling web forms, database operations and file processing; 3) PHP is constantly evolving and optimizing, suitable for beginners and experienced developers.
PHP in Action: Real-World Examples and Applications
Apr 14, 2025 am 12:19 AM
PHP is widely used in e-commerce, content management systems and API development. 1) E-commerce: used for shopping cart function and payment processing. 2) Content management system: used for dynamic content generation and user management. 3) API development: used for RESTful API development and API security. Through performance optimization and best practices, the efficiency and maintainability of PHP applications are improved.
PHP and Python: Different Paradigms Explained
Apr 18, 2025 am 12:26 AM
PHP is mainly procedural programming, but also supports object-oriented programming (OOP); Python supports a variety of paradigms, including OOP, functional and procedural programming. PHP is suitable for web development, and Python is suitable for a variety of applications such as data analysis and machine learning.
PHP and Python: Code Examples and Comparison
Apr 15, 2025 am 12:07 AM
PHP and Python have their own advantages and disadvantages, and the choice depends on project needs and personal preferences. 1.PHP is suitable for rapid development and maintenance of large-scale web applications. 2. Python dominates the field of data science and machine learning.
Why Use PHP? Advantages and Benefits Explained
Apr 16, 2025 am 12:16 AM
The core benefits of PHP include ease of learning, strong web development support, rich libraries and frameworks, high performance and scalability, cross-platform compatibility, and cost-effectiveness. 1) Easy to learn and use, suitable for beginners; 2) Good integration with web servers and supports multiple databases; 3) Have powerful frameworks such as Laravel; 4) High performance can be achieved through optimization; 5) Support multiple operating systems; 6) Open source to reduce development costs.
PHP: Handling Databases and Server-Side Logic
Apr 15, 2025 am 12:15 AM
PHP uses MySQLi and PDO extensions to interact in database operations and server-side logic processing, and processes server-side logic through functions such as session management. 1) Use MySQLi or PDO to connect to the database and execute SQL queries. 2) Handle HTTP requests and user status through session management and other functions. 3) Use transactions to ensure the atomicity of database operations. 4) Prevent SQL injection, use exception handling and closing connections for debugging. 5) Optimize performance through indexing and cache, write highly readable code and perform error handling.
