㈠ 如何用mysql表模擬隊列
通常大家都會使用redis作為應用的任務隊列表，redis的List結構，在一段進行任務的插入，在另一端進行任務的提取。
任務的插入
$redis->lPush("key:task:list",$task);
任務的提取
$tasks = $redis->RPop("key:task:list",0,-1);
可是大家想，如何使用mysql來實現一個隊列表呢？
映入大家腦海的一個典型的模式是一個表包含多種類型的記錄：未處理記錄，已處理記錄，正在處理記錄等。一個或者多個消費者線程在表中查詢未處理的記錄，然後聲稱正在處理這個任務，處理完成之後，再講記錄更新為已處理狀態。
這個典型的模式，存在兩個問題；1：隨著隊列表越來越大，查找未處理記錄的速度會越來越慢。2：頻繁的加鎖會讓多個消費者線程增加競爭。
首先我們來創建一個表
create table unsent_emails{ id int not null primary key auto_increment, status enum("unsent","claimed","sent"),
owner int unsigned not null default 0,
ts timestamp, key (owner,status,ts)
};
該表的列owner用來存儲當前正在處理這個記錄的連接id，由函數 CONNECTION_ID()返回的連接id或者線程id。如果這個記錄當前被沒有被處理，則該值為0
我們在 owner status ts上面做了索引的處理，所以查找未處理洞枯的記錄會很快。
通過我們會採用 select for update的方式來標記待處理的記錄，方法如下
begin;select id from unsent_emails where owner = 0 and status = 'unsent'
limit 10 for update;-- result 10,20,33update unsent_emails set status = 'claimed',owner = CONNECTION_ID() where id in (10,20,33);
commit;
select的時候，使用了兩個索引，應該會很快。問題出在select 和 update兩個查詢之間的間隙，這里的加鎖會讓其他相同的查詢全部阻塞。
如果我們採用update then select的方式，那麼效果就會更加高效，代碼如下
set autocommit=1;commit;update unsent_emails set statue = 'claimed',owner = CONNECTION_ID() where owner = 0 and status = 'unsent'
limit 10;set autocommit=0;select id from unsent_emails where owner = CONNECTION_ID() and status = 'claimed';
根本無需使用select去查蔽配找哪些記錄還沒有處理。客戶端協議會告訴你更新了幾條記錄，就可以知道這次需要處理多少條記錄。
這樣是不是解決了上面的第二個問題，select for update的模式的加鎖會增加多個消費隊列的競爭問題。
其實所有的select for update 都可以替換為 update then select模式。
問題還沒有結束，還有一種情況需要處理，就是比如正在處理任務的進程異常退出了，那麼對應的進程正在處理的任務也就變為僵屍任務了。如何避免這種情況的發生呢？
所以我們還是需要一個新的定時器或者線程來定時檢測納並洞並且update，將那些僵屍任務的記錄更新到原始狀態，就可以了。
僵屍任務的定義必須符合兩點，1：任務被擱置了很久，比如十分鍾，而通常一個任務只需要10秒就可以處理完；2：任務的owner(線程id或者連接id)已經不存在，只需要執行show processlist就可以獲取當前正在工作的線程id了。代碼如下
update unsent_emails set owner = 0,status = 'unsent'
where owner not in (10,20,33,44) and status = 'claimed'
and ts < current_timestamp - interval 10 minute;
一個基於mysql構建的隊列表就完成了。
當然，最好的辦法就是將任務隊列從資料庫中遷移出來。redis真是一個很好的隊列容器，當然也可以使用ssdb(基於leveldb，內存佔用更少)。
㈡ 用sql語句，怎麼解決mysql資料庫死鎖
MySQL死鎖問題的相關知識是本文我們主要要介紹的內容，接下來我們就來一一介紹這部分內容，希望能夠對您有所幫助。
1、MySQL常用存儲引擎的鎖機制
MyISAM和MEMORY採用表級鎖(table-level locking)
BDB採用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖
InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖
2、各種鎖特點
表級鎖：開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖沖突的概率最高，並發度最低
行級鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖沖突的概率最低，並發度也最高
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，並發度一般
3、各種鎖的適用場景
表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用
行級鎖則更適合於有大量按索引條件並發更新數據，同時又有並發查詢的應用，如一些在線事務處理系統
4、死鎖
是指兩個或兩個以上的進程在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。
表級鎖不會產生死鎖。所以解決死鎖主要還是針對於最常用的InnoDB。
5、死鎖舉例分析
在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。
在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。
例如，一個表db。tab_test，結構如下：
id：主鍵;
state：狀態;
time：時間;
索引：idx_1(state，time)
出現死鎖日誌如下：
?***(1) TRANSACTION:
?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read
?mysql tables in use 1, locked 1
?LOCK WAIT 3 lock struct(s), heap size 320
?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
?update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任務1的sql語句)
?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務1等待的索引記錄)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) TRANSACTION:
?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
?mysql tables in use 1, locked 1
?3 lock struct(s), heap size 320, undo log entries 1
?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務2的sql語句)
?*** (2) HOLDS THE LOCK(S): (任務2已獲得的鎖)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務2等待的鎖)
?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting
?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
?*** WE ROLL BACK TRANSACTION (1)
?(回滾了任務1，以解除死鎖)
原因分析：
當「update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)」執行時，MySQL會使用idx_1索引，因此首先鎖定相關的索引記錄，因為idx_1是非主鍵索引，為執行該語句，MySQL還會鎖定主鍵索引。
假設「update tab_test set state=1067,time=now () where id in (9921180)」幾乎同時執行時，本語句首先鎖定主鍵索引，由於需要更新state的值，所以還需要鎖定idx_1的某些索引記錄。
這樣第一條語句鎖定了idx_1的記錄，等待主鍵索引，而第二條語句則鎖定了主鍵索引記錄，而等待idx_1的記錄，這樣死鎖就產生了。
6、解決辦法
拆分第一條sql，先查出符合條件的主鍵值，再按照主鍵更新記錄：
?select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);
?update tab_test state=1064,time=now() where id in(......);
㈢ mysql 分區和分表 哪個好
mysql 分區和分表好
一，什麼是mysql分表，分區
什麼是分表，從表面意思上看呢，就是把一張表分成N多個小表，具體請看mysql分表的3種方法
什麼是分區，分區呢就是把一張表的數據分成N多個區塊，這些區塊可以在同一個磁碟上，也可以在不同的磁碟上
一，先說一下為什麼要分表
當一張的數據達到幾百萬時，你查詢一次所花的時間會變多，如果有聯合查詢的話，我想有可能會死在那兒了。分表的目的就在於此，減小資料庫的負擔，縮短查詢時間。
根據個人經驗，mysql執行一個sql的過程如下：
1,接收到sql;2,把sql放到排隊隊列中 ;3,執行sql;4,返回執行結果。在這個執行過程中最花時間在什麼地方呢？第一，是排隊等待的時間，第二，sql的執行時間。其實這二個是一回事，等待的同時，肯定有sql在執行。所以我們要縮短sql的執行時間。
mysql中有一種機制是表鎖定和行鎖定，為什麼要出現這種機制，是為了保證數據的完整性，我舉個例子來說吧，如果有二個sql都要修改同一張表的同一條數據，這個時候怎麼辦呢，是不是二個sql都可以同時修改這條數據呢？很顯然mysql對這種情況的處理是，一種是表鎖定（myisam存儲引擎），一個是行鎖定（innodb存儲引擎）。表鎖定表示你們都不能對這張表進行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數據操作完了，才能對這條數據進行操作。如果數據太多，一次執行的時間太長，等待的時間就越長，這也是我們為什麼要分表的原因。
二，分表
1，做mysql集群，例如：利用mysql cluster ，mysql proxy，mysql replication，drdb等等
有人會問mysql集群，根分表有什麼關系嗎？雖然它不是實際意義上的分表，但是它啟到了分表的作用，做集群的意義是什麼呢？為一個資料庫減輕負擔，說白了就是減少sql排隊隊列中的sql的數量，舉個例子：有10個sql請求，如果放在一個資料庫伺服器的排隊隊列中，他要等很長時間，如果把這10個sql請求，分配到5個資料庫伺服器的排隊隊列中，一個資料庫伺服器的隊列中只有2個，這樣等待時間是不是大大的縮短了呢？這已經很明顯了。所以我把它列到了分表的范圍以內，我做過一些mysql的集群：
linux mysql proxy 的安裝，配置，以及讀寫分離
mysql replication 互為主從的安裝及配置，以及數據同步
優點：擴展性好，沒有多個分表後的復雜操作（php代碼）
缺點：單個表的數據量還是沒有變，一次操作所花的時間還是那麼多，硬體開銷大。
2，預先估計會出現大數據量並且訪問頻繁的表，將其分為若干個表
這種預估大差不差的，論壇裡面發表帖子的表，時間長了這張表肯定很大，幾十萬，幾百萬都有可能。 聊天室裡面信息表，幾十個人在一起一聊一個晚上，時間長了，這張表的數據肯定很大。像這樣的情況很多。所以這種能預估出來的大數據量表，我們就事先分出個N個表，這個N是多少，根據實際情況而定。以聊天信息表為例：
我事先建100個這樣的表，message_00,message_01,message_02..........message_98,message_99.然後根據用戶的ID來判斷這個用戶的聊天信息放到哪張表裡面，你可以用hash的方式來獲得，可以用求余的方式來獲得，方法很多，各人想各人的吧。下面用hash的方法來獲得表名：
查看復制列印?
<?php
function get_hash_table($table,$userid) {
$str = crc32($userid);
if($str<0){
$hash = "0".substr(abs($str), 0, 1);
}else{
$hash = substr($str, 0, 2);
}
return $table."_".$hash;
}
echo get_hash_table('message','user18991'); //結果為message_10
echo get_hash_table('message','user34523'); //結果為message_13
?>
說明一下，上面的這個方法，告訴我們user18991這個用戶的消息都記錄在message_10這張表裡，user34523這個用戶的消息都記錄在message_13這張表裡，讀取的時候，只要從各自的表中讀取就行了。
優點：避免一張表出現幾百萬條數據，縮短了一條sql的執行時間
缺點：當一種規則確定時，打破這條規則會很麻煩，上面的例子中我用的hash演算法是crc32，如果我現在不想用這個演算法了，改用md5後，會使同一個用戶的消息被存儲到不同的表中，這樣數據亂套了。擴展性很差。
3，利用merge存儲引擎來實現分表
我覺得這種方法比較適合，那些沒有事先考慮，而已經出現了得，數據查詢慢的情況。這個時候如果要把已有的大數據量表分開比較痛苦，最痛苦的事就是改代碼，因為程序裡面的sql語句已經寫好了，現在一張表要分成幾十張表，甚至上百張表，這樣sql語句是不是要重寫呢？舉個例子，我很喜歡舉子
mysql>show engines;的時候你會發現mrg_myisam其實就是merge。
查看復制列印?
mysql> CREATE TABLE IF NOT EXISTS `user1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE IF NOT EXISTS `user2` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('張映', 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `alluser` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> INDEX(id)
-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select id,name,sex from alluser;
+----+--------+-----+
| id | name | sex |
+----+--------+-----+
| 1 | 張映 | 0 |
| 1 | tank | 1 |
+----+--------+-----+
2 rows in set (0.00 sec)
mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0);
Query OK, 1 row affected (0.00 sec)
mysql> select id,name,sex from user2
-> ;
+----+-------+-----+
| id | name | sex |
+----+-------+-----+
| 1 | tank | 1 |
| 2 | tank2 | 0 |
+----+-------+-----+
㈣ MySQL知識點總結
只要欄位值還可以繼續拆分，就不滿足第一範式。
範式設計得越詳細，對某些實際操作可能會更好，但並非都有好處，需要對項目的實際情況進行設定。
在滿足第一範式的前提下，其他列都必須完全依賴於主鍵列。 如果出現不完全依賴，只可能發生在聯合主鍵的情況下：
實際上，在這張訂單表中，proct_name 只依賴於 proct_id ，customer_name 只依賴於 customer_id。也就是說，proct_name 和 customer_id 是沒用關系的，customer_name 和 proct_id 也是沒有關系的。
這就不滿足第二範式：其他列都必須完全依賴於主鍵列！
拆分之後，myorder 表中的 proct_id 和 customer_id 完全依賴於 order_id 主鍵，而 proct 和 customer 表中的其他欄位又完全依賴於主鍵。滿足了第二範式的設計！
在滿足第二範式的前提下，除了主鍵列之外，其他列之間不能有傳遞依賴關系。
表中的 customer_phone 有可能依賴於 order_id 、 customer_id 兩列，也就不滿足了第三範式的設計：其他列之間不能有傳遞依賴關系。
修改後就不存在其他列之間的傳遞依賴關系，其他列都只依賴於主鍵列，滿足了第三範式的設計！
查詢每門課的平均成績。
查詢 score 表中至少有 2 名學生選修，並以 3 開頭的課程的平均分數。
分析表發現，至少有 2 名學生選修的課程是 3-105 、3-245 、6-166 ，以 3 開頭的課程是 3-105 、3-245。也就是說，我們要查詢所有 3-105 和 3-245 的 degree 平均分。
查詢所有學生的 name，以及該學生在 score 表中對應的 c_no 和 degree 。
通過分析可以發現，只要把 score 表中的 s_no 欄位值替換成 student 表中對應的 name 欄位值就可以了，如何做呢？
查詢所有學生的 no 、課程名稱 ( course 表中的 name ) 和成績 ( score 表中的 degree ) 列。
只有 score 關聯學生的 no ，因此只要查詢 score 表，就能找出所有和學生相關的 no 和 degree ：
然後查詢 course 表：
只要把 score 表中的 c_no 替換成 course 表中對應的 name 欄位值就可以了。
查詢所有學生的 name 、課程名 ( course 表中的 name ) 和 degree 。
只有 score 表中關聯學生的學號和課堂號，我們只要圍繞著 score 這張表查詢就好了。
只要把 s_no 和 c_no 替換成 student 和 srouse 表中對應的 name 欄位值就好了。
首先把 s_no 替換成 student 表中的 name 欄位：
再把 c_no 替換成 course 表中的 name 欄位：
查詢 95031 班學生每門課程的平均成績。
在 score 表中根據 student 表的學生編號篩選出學生的課堂號和成績：
這時只要將 c_no 分組一下就能得出 95031 班學生每門課的平均成績：
查詢在 3-105 課程中，所有成績高於 109 號同學的記錄。
首先篩選出課堂號為 3-105 ，在找出所有成績高於 109 號同學的的行。
查詢所有成績高於 109 號同學的 3-105 課程成績記錄。
查詢所有和 101 、108 號學生同年出生的 no 、name 、birthday 列。游御
查詢 '張旭' 教師任課的學生成績表。
首先找到教師編號悔中：
通過 sourse 表找到該神前岩教師課程號：
通過篩選出的課程號查詢成績表：
查詢某選修課程多於5個同學的教師姓名。
首先在 teacher 表中，根據 no 欄位來判斷該教師的同一門課程是否有至少5名學員選修：
查看和教師編號有有關的表的信息：
我們已經找到和教師編號有關的欄位就在 course 表中，但是還無法知道哪門課程至少有5名學生選修，所以還需要根據 score 表來查詢：
根據篩選出來的課程號，找出在某課程中，擁有至少5名學員的教師編號：
在 teacher 表中，根據篩選出來的教師編號找到教師姓名：
查詢 「計算機系」 課程的成績表。
思路是，先找出 course 表中所有 計算機系 課程的編號，然後根據這個編號查詢 score 表。
查詢 計算機系 與 電子工程系 中的不同職稱的教師。
查詢課程 3-105 且成績 至少 高於 3-245 的 score 表。
查詢課程 3-105 且成績高於 3-245 的 score 表。
查詢某課程成績比該課程平均成績低的 score 表。
查詢所有任課 ( 在 course 表裡有課程 ) 教師的 name 和 department 。
查詢 student 表中至少有 2 名男生的 class 。
查詢 student 表中不姓 "王" 的同學記錄。
查詢 student 表中每個學生的姓名和年齡。
查詢 student 表中最大和最小的 birthday 值。
以 class 和 birthday 從大到小的順序查詢 student 表。
查詢 "男" 教師及其所上的課程。
查詢最高分同學的 score 表。
查詢和 "李軍" 同性別的所有同學 name 。
查詢和 "李軍" 同性別且同班的同學 name 。
查詢所有選修 "計算機導論" 課程的 "男" 同學成績表。
需要的 "計算機導論" 和性別為 "男" 的編號可以在 course 和 student 表中找到。
建立一個 grade 表代表學生的成績等級，並插入數據：
查詢所有學生的 s_no 、c_no 和 grade 列。
思路是，使用區間 ( BETWEEN ) 查詢，判斷學生的成績 ( degree ) 在 grade 表的 low 和 upp 之間。
准備用於測試連接查詢的數據：
分析兩張表發現，person 表並沒有為 cardId 欄位設置一個在 card 表中對應的 id 外鍵。如果設置了的話，person 中 cardId 欄位值為 6 的行就插不進去，因為該 cardId 值在 card 表中並沒有。
要查詢這兩張表中有關系的數據，可以使用 INNER JOIN ( 內連接 ) 將它們連接在一起。
完整顯示左邊的表 ( person ) ，右邊的表如果符合條件就顯示，不符合則補 NULL 。
完整顯示右邊的表 ( card ) ，左邊的表如果符合條件就顯示，不符合則補 NULL 。
完整顯示兩張表的全部數據。
在 MySQL 中，事務其實是一個最小的不可分割的工作單元。事務能夠 保證一個業務的完整性 。
比如我們的銀行轉賬：
在實際項目中，假設只有一條 SQL 語句執行成功，而另外一條執行失敗了，就會出現數據前後不一致。
因此，在執行多條有關聯 SQL 語句時， 事務 可能會要求這些 SQL 語句要麼同時執行成功，要麼就都執行失敗。
在 MySQL 中，事務的 自動提交 狀態默認是開啟的。
自動提交的作用 ：當我們執行一條 SQL 語句的時候，其產生的效果就會立即體現出來，且不能 回滾 。
什麼是回滾？舉個例子：
可以看到，在執行插入語句後數據立刻生效，原因是 MySQL 中的事務自動將它 提交 到了資料庫中。那麼所謂 回滾 的意思就是，撤銷執行過的所有 SQL 語句，使其回滾到 最後一次提交 數據時的狀態。
在 MySQL 中使用 ROLLBACK 執行回滾：
由於所有執行過的 SQL 語句都已經被提交過了，所以數據並沒有發生回滾。那如何讓數據可以發生回滾？
將自動提交關閉後，測試數據回滾：
那如何將虛擬的數據真正提交到資料庫中？使用 COMMIT :
事務的實際應用 ，讓我們再回到銀行轉賬項目：
這時假設在轉賬時發生了意外，就可以使用 ROLLBACK 回滾到最後一次提交的狀態：
這時我們又回到了發生意外之前的狀態，也就是說，事務給我們提供了一個可以反悔的機會。假設數據沒有發生意外，這時可以手動將數據真正提交到數據表中：COMMIT 。
事務的默認提交被開啟 ( @@AUTOCOMMIT = 1 ) 後，此時就不能使用事務回滾了。但是我們還可以手動開啟一個事務處理事件，使其可以發生回滾：
仍然使用 COMMIT 提交數據，提交後無法再發生本次事務的回滾。
事務的四大特徵：
事務的隔離性可分為四種 ( 性能從低到高 ) ：
查看當前資料庫的默認隔離級別：
修改隔離級別：
測試 READ UNCOMMITTED ( 讀取未提交 ) 的隔離性：
由於小明的轉賬是在新開啟的事務上進行操作的，而該操作的結果是可以被其他事務（另一方的淘寶店）看見的，因此淘寶店的查詢結果是正確的，淘寶店確認到賬。但就在這時，如果小明在它所處的事務上又執行了 ROLLBACK 命令，會發生什麼？
這就是所謂的 臟讀 ，一個事務讀取到另外一個事務還未提交的數據。這在實際開發中是不允許出現的。
把隔離級別設置為 READ COMMITTED ：
這樣，再有新的事務連接進來時，它們就只能查詢到已經提交過的事務數據了。但是對於當前事務來說，它們看到的還是未提交的數據，例如：
但是這樣還有問題，那就是假設一個事務在操作數據時，其他事務干擾了這個事務的數據。例如：
雖然 READ COMMITTED 讓我們只能讀取到其他事務已經提交的數據，但還是會出現問題，就是 在讀取同一個表的數據時，可能會發生前後不一致的情況。* 這被稱為* 不可重復讀現象 ( READ COMMITTED ) 。
將隔離級別設置為 REPEATABLE READ ( 可被重復讀取 ) :
測試 REPEATABLE READ ，假設在兩個不同的連接上分別執行 START TRANSACTION :
當前事務開啟後，沒提交之前，查詢不到，提交後可以被查詢到。但是，在提交之前其他事務被開啟了，那麼在這條事務線上，就不會查詢到當前有操作事務的連接。相當於開辟出一條單獨的線程。
無論小張是否執行過 COMMIT ，在小王這邊，都不會查詢到小張的事務記錄，而是只會查詢到自己所處事務的記錄：
這是 因為小王在此之前開啟了一個新的事務 ( START TRANSACTION ) * ，那麼* 在他的這條新事務的線上，跟其他事務是沒有聯系的 ，也就是說，此時如果其他事務正在操作數據，它是不知道的。
然而事實是，在真實的數據表中，小張已經插入了一條數據。但是小王此時並不知道，也插入了同一條數據，會發生什麼呢？
報錯了，操作被告知已存在主鍵為 6 的欄位。這種現象也被稱為 幻讀，一個事務提交的數據，不能被其他事務讀取到 。
顧名思義，就是所有事務的 寫入操作 全都是串列化的。什麼意思？把隔離級別修改成 SERIALIZABLE :
還是拿小張和小王來舉例：
此時會發生什麼呢？由於現在的隔離級別是 SERIALIZABLE ( 串列化 ) ，串列化的意思就是：假設把所有的事務都放在一個串列的隊列中，那麼所有的事務都會按照 固定順序執行 ，執行完一個事務後再繼續執行下一個事務的 寫入操作 ( 這意味著隊列中同時只能執行一個事務的寫入操作 ) 。
根據這個解釋，小王在插入數據時，會出現等待狀態，直到小張執行 COMMIT 結束它所處的事務，或者出現等待超時。
轉載： https://github.com/baa-god/sql_node/blob/master/mysql/
㈤ MySQL基本調度策略淺析
MySQL允許影響語句的調度特性，這樣會使來自幾個客戶機的查詢更好地協作，從而單個客戶機不會被鎖定太長的時間。更改調度特性還能保證特定的查詢處理得更快。我們先來看一下MySQL的預設調度策略，然後來看看為改變這個策略可使用什麼樣的選項。出於討論的目的，假設執行檢索( SELECT)的客戶機程序為讀取程序。執行修改表操作( DELETE，INSERT，REPLACE 或UP DATE)的另一個客戶機程序為寫入程序。
MySQL的基本調度策略可總結如下：
◆寫入請求應按其到達的次序進行處理。
◆寫入具有比讀取更高的優先權。
在表鎖的幫助下實現調度策略。客戶機程序無論何時要訪問表，都必須首先獲得該表的鎖。可以直接用LOCK TABLES 來完成這項工作，但一般伺服器的鎖管理器會在需要時自動獲得鎖。在客戶機結束對表的處理時，可釋放表上的鎖。直接獲得的鎖可用UNLOCK TABLES 釋放，但伺服器也會自動釋放它所獲得的鎖。
執行寫操作的客戶機必須對表具有獨占訪問的鎖。在寫操作進行中，由於正在對表進行數據記錄的刪除、增加或更改，所以該表處於不一致狀態，而且該表上的索引也可能需要作相應的更新。如果表處於不斷變化中，此時允許其他客戶機訪問該表會出問題。讓兩個客戶機同時寫同一個表顯然不好，因為這樣會很快使該表不可用。允許客戶機讀不斷變仿大化的表也不是件好事，因為可能在讀該表的那一刻正好正在對它進行更改，其結果是不正確的。執行讀取操作的客戶機必須有一把防止其他客戶機寫該表的鎖，以保證讀表的過程中表不出現變化。不過，該鎖無需對讀取操作提供獨占訪問。此鎖還允許其他客戶機同時對表進行讀取。讀取備數豎不會更改表，所有沒必要阻止其它客戶機對該表進行讀取。
MySQL允許藉助幾個查詢限修飾符對其調度策略施加影響。其中之一是DELETE、INSERT、LOAD DATA、REPLACE 和UP DATE 語句的LOW_PRIORITY 關鍵字。另一個是SELECT 語句的HIGH_PRIORITY 關鍵字。第三個是INSERT 和REPLACE 語句的DELAYED 關鍵字。
LOW_PRIORITY 關鍵字按如下影響調度。一般情況下，如果某個表的寫入操作在表正被讀取時到達，寫入程序被阻塞，直到讀取程序完成，因為一旦某個查詢開始，就不能中斷。如果另一讀取請求在寫入程序等待時到達，此讀取程序也被阻塞，因為預設的調度策略為寫入程序具有比讀取程序高的優先順序。在第一個讀取程序結束時，寫入程序繼續，在此寫入程序結束時，第二個讀取程序開始。
如果寫入請求為LOW_PRIORITY 的請求，則不將該寫入操作視為具有比讀取操作優先順序高的操作。在此情形下，如果第二個讀取請求在寫入程畢春序等待時到達，則讓第二個讀取操作排在等待的寫入操作之前。僅當沒有其他讀取請求時，才允許寫入程序執行。這種調度的更改從理論上說，其含義為LOW_PRIORITY 寫入可能會永遠被阻塞。當正在處理前面的讀取請求時，只要另一個讀取請求到達，這個新的請求允許排在LOW_PRIORITY 寫入之前。
SELECT 查詢的HIGH_PRIORITY 關鍵字作用類似。它使SELECT 插在正在等待的寫入操作之前，即使該寫入操作具有正常的優先順序。INSERT 的ELAYED 修飾符作用如下，在表的一個INSERT DELAYED 請求到達時，伺服器將相應的行放入一個隊列，並立即返回一個狀態到客戶機程序，以便該客戶機程序可以繼續執行，即使這些行尚未插入表中。如果讀取程序正在對表進行讀取，那麼隊列中的行掛起。在沒有讀取時，伺服器開始開始插入延遲行隊列中的行。伺服器不時地停下來看看是否有新的讀取請求到達，並進行等待。如果是這樣，延遲行隊列將掛起，並允許讀取程序繼續。在沒有其他的讀取操作時，伺服器再次開始插入延遲行。這個過程一直進行到延遲行隊列空為止。
此並非出現在所有MySQL版本中。下面的表列出了這些修飾符和支持這些修飾符的MySQL版本。可利用此表來判斷所使用的MySQL版本具有什麼樣的功能：
如果其他客戶機可能執行冗長的SELECT 語句，而且您不希望等待插入完成，此時INSERT DELAYED 很有用。發布INSERT DELAYED 的客戶機可以更快地繼續執行，因為伺服器只是簡單地將要插入的行插入。不過應該對正常的INSERT 和INSERT DELAYED 性能之間的差異有所認識。如果INSERT DELAYED 存在語法錯誤，則向客戶機發出一個錯誤，如果正常，便不發出信息。例如，在此語句返回時，不能相信所取得的AUTO_INCREMENT 值。也得不到惟一索引上的重復數目的計數。之所以這樣是因為此插入操作在實際的插入完成前返回了一個狀態。其他還表示，如果INSERT DELAYED 語句的行在等待插入中被排隊，並且伺服器崩潰或被終止(用kill -9)，那麼這些行將丟失。正常的TERM 終止不會這樣，伺服器會在退出前將這些行插入。
㈥ sql資料庫出現隊列問題如何解決
sql資料庫出現隊列問題可以這樣解決：文中從 select ... limit 1 這一點開始，也就是 limit 1這東西出現開始，思路就錯了。錯就錯在worker不應該管理任務的分發（當worker會去卜顫老回寫mysql，就參與了任務的型升分發），只負責單調領任務即可。分發的事情交給獨立的進洞備程配合mq或者redis進行。
㈦ 如何使用MySQL實現隊列
這完全是文不對題啊，隊列是一種先進先出的空宴數據結構，通常改虧哪在各種編程語言中都提供相應的類庫支持，但MySQL是一個關系型資料庫管理系統，並不直接提供這種功能，也不應該提供核碼這種功能。
如果真需要先進先出，就把查詢的結果放入到對應高級語言的隊列中即可。
㈧ mysql 什麼時候分區 什麼時候分表
一，什麼是mysql分表，分區
什麼是分表，從表面意思上看呢，就是把一張表分成N多個小表，具體請看mysql分表的3種方法
什麼是分區，分區呢就是把一張表的數據分成N多個區塊，這些區塊可以在同一個磁碟上，也可以在不同的磁碟上
一，先說一下為什麼要分表
當一張的數據達到幾百萬時，你查詢一次所花的時間會變多，如果有聯合查詢的話，我想有可能會死在那兒了。分表的目的就在於此，減小資料庫的負擔，縮短查詢時間。
根據個人經驗，mysql執行一個sql的過程如下：
1,接收到sql;2,把sql放到排隊隊列中 ;3,執行sql;4,返回執行結果。在這個執行過程中最花時間在什麼地方呢？第一，是排隊等待的時間，第二，sql的執行時間。其實這二個是一回事，等待的同時，肯定有sql在執行。所以我們要縮短sql的執行時間。
mysql中有一種機制是表鎖定和行鎖定，為什麼要出現這種機制，是為了保證數據的完整性，我舉個例子來說吧，如果有二個sql都要修改同一張表的同一條數據，這個時候怎麼辦呢，是不是二個sql都可以同時修改這條數據呢？很顯然mysql對這種情況的處理是，一種是表鎖定（myisam存儲引擎），一個是行鎖定（innodb存儲引擎）。表鎖定表示你們都不能對這張表進行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數據操作完了，才能對這條數據進行操作。如果數據太多，一次執行的時間太長，等待的時間就越長，這也是我們為什麼要分表的原因。
二，分表
1，做mysql集群，例如：利用mysql cluster ，mysql proxy，mysql replication，drdb等等
有人會問mysql集群，根分表有什麼關系嗎？雖然它不是實際意義上的分表，但是它啟到了分表的作用，做集群的意義是什麼呢？為一個資料庫減輕負擔，說白了就是減少sql排隊隊列中的sql的數量，舉個例子：有10個sql請求，如果放在一個資料庫伺服器的排隊隊列中，他要等很長時間，如果把這10個sql請求，分配到5個資料庫伺服器的排隊隊列中，一個資料庫伺服器的隊列中只有2個，這樣等待時間是不是大大的縮短了呢？這已經很明顯了。所以我把它列到了分表的范圍以內，我做過一些mysql的集群：
linux mysql proxy 的安裝，配置，以及讀寫分離
mysql replication 互為主從的安裝及配置，以及數據同步
優點：擴展性好，沒有多個分表後的復雜操作（php代碼）
缺點：單個表的數據量還是沒有變，一次操作所花的時間還是那麼多，硬體開銷大。
2，預先估計會出現大數據量並且訪問頻繁的表，將其分為若干個表
這種預估大差不差的，論壇裡面發表帖子的表，時間長了這張表肯定很大，幾十萬，幾百萬都有可能。 聊天室裡面信息表，幾十個人在一起一聊一個晚上，時間長了，這張表的數據肯定很大。像這樣的情況很多。所以這種能預估出來的大數據量表，我們就事先分出個N個表，這個N是多少，根據實際情況而定。以聊天信息表為例：
我事先建100個這樣的表，message_00,message_01,message_02..........message_98,message_99.然後根據用戶的ID來判斷這個用戶的聊天信息放到哪張表裡面，你可以用hash的方式來獲得，可以用求余的方式來獲得，方法很多，各人想各人的吧。下面用hash的方法來獲得表名：
查看復制列印?
<?php
function get_hash_table($table,$userid) {
$str = crc32($userid);
if($str<0){
$hash = "0".substr(abs($str), 0, 1);
}else{
$hash = substr($str, 0, 2);
}
return $table."_".$hash;
}
echo get_hash_table('message','user18991'); //結果為message_10
echo get_hash_table('message','user34523'); //結果為message_13
?>
說明一下，上面的這個方法，告訴我們user18991這個用戶的消息都記錄在message_10這張表裡，user34523這個用戶的消息都記錄在message_13這張表裡，讀取的時候，只要從各自的表中讀取就行了。
優點：避免一張表出現幾百萬條數據，縮短了一條sql的執行時間
缺點：當一種規則確定時，打破這條規則會很麻煩，上面的例子中我用的hash演算法是crc32，如果我現在不想用這個演算法了，改用md5後，會使同一個用戶的消息被存儲到不同的表中，這樣數據亂套了。擴展性很差。
3，利用merge存儲引擎來實現分表
我覺得這種方法比較適合，那些沒有事先考慮，而已經出現了得，數據查詢慢的情況。這個時候如果要把已有的大數據量表分開比較痛苦，最痛苦的事就是改代碼，因為程序裡面的sql語句已經寫好了，現在一張表要分成幾十張表，甚至上百張表，這樣sql語句是不是要重寫呢？舉個例子，我很喜歡舉子
mysql>show engines;的時候你會發現mrg_myisam其實就是merge。
查看復制列印?
mysql> CREATE TABLE IF NOT EXISTS `user1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE IF NOT EXISTS `user2` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('張映', 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `alluser` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> INDEX(id)
-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select id,name,sex from alluser;
+----+--------+-----+
| id | name | sex |
+----+--------+-----+
| 1 | 張映 | 0 |
| 1 | tank | 1 |
+----+--------+-----+
2 rows in set (0.00 sec)
mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0);
Query OK, 1 row affected (0.00 sec)
mysql> select id,name,sex from user2
-> ;
+----+-------+-----+
| id | name | sex |
+----+-------+-----+
| 1 | tank | 1 |
| 2 | tank2 | 0 |
+----+-------+-----+
2 rows in set (0.00 sec)
從上面的操作中，我不知道你有沒有發現點什麼？假如我有一張用戶表user，有50W條數據，現在要拆成二張表user1和user2，每張表25W條數據，
INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000
這樣我就成功的將一張user表，分成了二個表，這個時候有一個問題，代碼中的sql語句怎麼辦，以前是一張表，現在變成二張表了，代碼改動很大，這樣給程序員帶來了很大的工作量，有沒有好的辦法解決這一點呢？辦法是把以前的user表備份一下，然後刪除掉，上面的操作中我建立了一個alluser表，只把這個alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的
a，如果你使用 alter table 來把 merge 表變為其它表類型，到底層表的映射就被丟失了。取而代之的，來自底層 myisam 表的行被復制到已更換的表中，該表隨後被指定新類型。
b，網上看到一些說replace不起作用，我試了一下可以起作用的。暈一個先
mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from alluser;
+----+--------+-----+
| id | name | sex |
+----+--------+-----+
| 1 | 張映 | 0 |
| 1 | tank | 1 |
| 2 | tank2 | 1 |
+----+--------+-----+
3 rows in set (0.00 sec)
c，一個 merge 表不能在整個表上維持 unique 約束。當你執行一個 insert，數據進入第一個或者最後一個 myisam 表（取決於 insert_method 選項的值）。mysql 確保唯一鍵值在那個 myisam 表裡保持唯一，但不是跨集合里所有的表。
d,當你創建一個 merge 表之時，沒有檢查去確保底層表的存在以及有相同的機構。當 merge 表被使用之時，mysql 檢查每個被映射的表的記錄長度是否相等，但這並不十分可靠。如果你從不相似的 myisam 表創建一個 merge 表，你非常有可能撞見奇怪的問題。
優點：擴展性好，並且程序代碼改動的不是很大
缺點：這種方法的效果比第二種要差一點
三，總結一下
上面提到的三種方法，我實際做過二種，第一種和第二種。第三種沒有做過，所以說的細一點。哈哈。做什麼事都有一個度，超過個度就過變得很差，不能一味的做資料庫伺服器集群，硬體是要花錢買的，也不要一味的分表，分出來1000表，mysql的存儲歸根到底還以文件的形勢存在硬碟上面，一張表對應三個文件，1000個分表就是對應3000個文件，這樣檢索起來也會變的很慢。我的建議是
方法1和方法2結合的方式來進行分表
方法1和方法3結合的方式來進行分表
我的二個建議適合不同的情況，根據個人情況而定，我覺得會有很多人選擇方法1和方法3結合的方式
二，mysql分表和分區有什麼區別呢
1，實現方式上
a），mysql的分表是真正的分表，一張表分成很多表後，每一個小表都是完正的一張表，都對應三個文件，一個.MYD數據文件，.MYI索引文件，.frm表結構文件。
[root@BlackGhost test]# ls |grep user
alluser.MRG
alluser.frm
user1.MYD
user1.MYI
user1.frm
user2.MYD
user2.MYI
user2.frm
Php代碼
[root@BlackGhost test]# ls |grep user
alluser.MRG
alluser.frm
user1.MYD
user1.MYI
user1.frm
user2.MYD
user2.MYI
user2.frm
簡單說明一下，上面的分表呢是利用了merge存儲引擎（分表的一種），alluser是總表，下面有二個分表，user1，user2。他們二個都是獨立的表，取數據的時候，我們可以通過總表來取。這里總表是沒有.MYD,.MYI這二個文件的，也就是說，總表他不是一張表，沒有數據，數據都放在分表裡面。我們來看看.MRG到底是什麼東西
[root@BlackGhost test]# cat alluser.MRG |more
user1
user2
#INSERT_METHOD=LAST
Php代碼
[root@BlackGhost test]# cat alluser.MRG |more
user1
user2
#INSERT_METHOD=LAST
從上面我們可以看出，alluser.MRG裡面就存了一些分表的關系，以及插入數據的方式。可以把總表理解成一個外殼，或者是聯接池。
b），分區不一樣，一張大表進行分區後，他還是一張表，不會變成二張表，但是他存放數據的區塊變多了。
[root@BlackGhost test]# ls |grep aa
aa#P#p1.MYD
aa#P#p1.MYI
aa#P#p3.MYD
aa#P#p3.MYI
aa.frm
aa.par
Php代碼
[root@BlackGhost test]# ls |grep aa
aa#P#p1.MYD
aa#P#p1.MYI
aa#P#p3.MYD
aa#P#p3.MYI
aa.frm
aa.par
從上面我們可以看出，aa這張表，分為二個區，p1和p3，本來是三個區，被我刪了一個區。我們都知道一張表對應三個文件.MYD,.MYI,.frm。分區呢根據一定的規則把數據文件和索引文件進行了分割，還多出了一個.par文件，打開.par文件後你可以看出他記錄了，這張表的分區信息，根分表中的.MRG有點像。分區後，還是一張，而不是多張表。
2，數據處理上
a），分表後，數據都是存放在分表裡，總表只是一個外殼，存取數據發生在一個一個的分表裡面。看下面的例子：
select * from alluser where id=』12′表面上看，是對表alluser進行操作的，其實不是的。是對alluser裡面的分表進行了操作。
b），分區呢，不存在分表的概念，分區只不過把存放數據的文件分成了許多小塊，分區後的表呢，還是一張表。數據處理還是由自己來完成。
3，提高性能上
a），分表後，單表的並發能力提高了，磁碟I/O性能也提高了。並發能力為什麼提高了呢，因為查尋一次所花的時間變短了，如果出現高並發的話，總表可以根據不同的查詢，將並發壓力分到不同的小表裡面。磁碟I/O性能怎麼搞高了呢，本來一個非常大的.MYD文件現在也分攤到各個小表的.MYD中去了。
b），mysql提出了分區的概念，我覺得就想突破磁碟I/O瓶頸，想提高磁碟的讀寫能力，來增加mysql性能。
在這一點上，分區和分表的測重點不同，分表重點是存取數據時，如何提高mysql並發能力上；而分區呢，如何突破磁碟的讀寫能力，從而達到提高mysql性能的目的。
4），實現的難易度上
a），分表的方法有很多，用merge來分表，是最簡單的一種方式。這種方式根分區難易度差不多，並且對程序代碼來說可以做到透明的。如果是用其他分表方式就比分區麻煩了。
b），分區實現是比較簡單的，建立分區表，根建平常的表沒什麼區別，並且對開代碼端來說是透明的。
三，mysql分表和分區有什麼聯系呢
1，都能提高mysql的性高，在高並發狀態下都有一個良好的表面。
2，分表和分區不矛盾，可以相互配合的，對於那些大訪問量，並且表數據比較多的表，我們可以採取分表和分區結合的方式（如果merge這種分表方式，不能和分區配合的話，可以用其他的分表試），訪問量不大，但是表數據很多的表，我們可以採取分區的方式等。
㈨ mysql 查詢後更新 高並發
一種：使用行鎖，SELECT `id` FROM `urls` ORDER BY `c_time` LIMIT 1 FOR UPDATE
壞處：進程阻塞
另外一種，使用更新亂租隊列（添加一張記錄更新的時間隊列表，執行更新前，去隊列里查詢最新的更新時間，所有針對這個id的訪問都先把時間插入到時間隊列表），枯鎮隊列可沒陪粗使用庫，也可以使用緩存（redis等）