MySQL分表自增ID問題的解決方法
當(dāng)我們對MySQL進(jìn)行分表操作后,將不能依賴MySQL的自動增量來產(chǎn)生唯一ID了,因為數(shù)據(jù)已經(jīng)分散到多個表中?! ?br />
應(yīng)盡量避免使用自增IP來做為主鍵,為數(shù)據(jù)庫分表操作帶來極大的不便。
在postgreSQL、oracle、db2數(shù)據(jù)庫中有一個特殊的特性---sequence。 任何時候數(shù)據(jù)庫可以根據(jù)當(dāng)前表中的記錄數(shù)大小和步長來獲取到該表下一條記錄數(shù)。然而,MySQL是沒有這種序列對象的。
可以通過下面的方法來實現(xiàn)sequence特性產(chǎn)生唯一ID:
1. 通過MySQL表生成ID
對于插入也就是insert操作,首先就是獲取唯一的id了,就需要一個表來專門創(chuàng)建id,插入一條記錄,并獲取最后插入的ID。代碼如下:
CREATE TABLE `ttlsa_com`.`create_id` ( `id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE = MYISAM
也就是說,當(dāng)我們需要插入數(shù)據(jù)的時候,必須由這個表來產(chǎn)生id值,我的php代碼的方法如下:
<?php function get_AI_ID() { $sql = "insert into create_id (id) values('')"; $this->db->query($sql); return $this->db->insertID(); } ?>
這種方法效果很好,但是在高并發(fā)情況下,MySQL的AUTO_INCREMENT將導(dǎo)致整個數(shù)據(jù)庫慢。如果存在自增字段,MySQL會維護(hù)一個自增 鎖,innodb會在內(nèi)存里保存一個計數(shù)器來記錄auto_increment值,當(dāng)插入一個新行數(shù)據(jù)時,就會用一個表鎖來鎖住這個計數(shù)器,直到插入結(jié) 束。如果是一行一行的插入是沒有問題的,但是在高并發(fā)情況下,那就悲催了,表鎖會引起SQL阻塞,極大的影響性能,還可能會達(dá)到 max_connections值。
innodb_autoinc_lock_mode:可以設(shè)定3個值:0、1、2
0:traditonal (每次都會產(chǎn)生表鎖)
1:consecutive (默認(rèn),可預(yù)判行數(shù)時使用新方式,不可時使用表鎖,對于simple insert會獲得批量的鎖,保證連續(xù)插入)
2:interleaved (不會鎖表,來一個處理一個,并發(fā)最高)
對于myisam表引擎是traditional,每次都會進(jìn)行表鎖的。
2. 通過redis生成ID
function get_next_autoincrement_waitlock($timeout = 60){ $count = $timeout > 0 ? $timeout : 60; while($r->get("serial:lock")){ $count++; sleep(1); if ($count > 10) return false; } return true; } function get_next_autoincrement($timeout = 60){ // first check if we are locked... if (get_next_autoincrement_waitlock($timeout) == false) return 0; $id = $r->incr("serial"); if ( $id > 1 ) return $id; // if ID == 1, we assume we do not have "serial" key... // first we need to get lock. if ($r->setnx("serial:lock"), 1){ $r->expire("serial:lock", 60 * 5); // get max(id) from database. $id = select_db_query("select max(id) from user_posts"); // or alternatively: // select id from user_posts order by id desc limit 1 // increase it $id++; // update Redis key $r->set("serial", $id); // release the lock $r->del("serial:lock"); return $id; } // can not get lock. return 0; } $r = new Redis(); $r->connect("127.0.0.1", "6379"); $id = get_next_autoincrement(); if ($id){ $sql = "insert into user_posts(id,user,message)values($id,'$user','$message')" $data = exec_db_query($sql); }
3. 隊列方式
其實這也算是上面的一個解說
使用隊列服務(wù),如redis、memcacheq等等,將一定量的ID預(yù)分配在一個隊列里,每次插入操作,先從隊列中獲取一個ID,若插入失敗的話,將該ID再次添加到隊列中,同時監(jiān)控隊列數(shù)量,當(dāng)小于閥值時,自動向隊列中添加元素。
這種方式可以有規(guī)劃的對ID進(jìn)行分配,還會帶來經(jīng)濟(jì)效應(yīng),比如QQ號碼,各種靚號,明碼標(biāo)價。如網(wǎng)站的userid, 允許uid登陸,推出各種靚號,明碼標(biāo)價,對于普通的ID打亂后再隨機(jī)分配。
<?php class common { private $r; function construct() { $this->__construct(); } public function __construct(){ $this->r=new Redis(); $this->r->connect('127.0.0.1', 6379); } function set_queue_id($ids){ if(is_array($ids) && isset($ids)){ foreach ($ids as $id){ $this->r->LPUSH('next_autoincrement',$id); } } } function get_next_autoincrement(){ return $this->r->LPOP('next_autoincrement'); } } $createid=array(); while(count($createid)<20){ $num=rand(1000,4000); if(!in_array($num,$createid)) $createid[]=$num; } $id=new common(); $id->set_queue_id($createid); var_dump($id->get_next_autoincrement());
監(jiān)控隊列數(shù)量,并自動補(bǔ)充隊列和取到id但并沒有使用
以上就是本文的全部內(nèi)容,希望對大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。
相關(guān)文章
導(dǎo)致sql執(zhí)行速度慢的幾種情況盤點(生產(chǎn)環(huán)境踩過的坑)
盤點分析MySQL執(zhí)行速度慢可以幫助我們進(jìn)行優(yōu)化MySQL數(shù)據(jù)庫的效率,這篇文章主要給大家盤點介紹了關(guān)于導(dǎo)致sql執(zhí)行速度慢的幾種情況,文中介紹的這些主要是生產(chǎn)環(huán)境踩過的坑,需要的朋友可以參考下2023-03-03MYSQL不能從遠(yuǎn)程連接的一個解決方法(s not allowed to connect to this MySQL s
MYSQL不能從遠(yuǎn)程連接的一個解決方法(s not allowed to connect to this MySQL server)2011-08-08