Mysql
MySQL 鎖定而 CREATE TABLE AS SELECT
我正在執行以下（虛擬）查詢
CREATE TABLE large_temp_table AS SELECT a.*, b.*, c.* FROM a LEFT JOIN b ON a.foo = b.foo LEFT JOIN c ON a.bar = c.bar假設查詢需要 10 分鐘才能執行。在執行時嘗試更新表 a、b 或 c 中的值將等待上述查詢首先完成。我想避免這種鎖（數據一致性不感興趣）。我怎樣才能做到這一點？
使用：MySQL 5.1.41 和 InnoDB 表
ps 設置事務隔離級別讀取未送出；不會改變行為
更新 在執行查詢時， SHOW ENGINE INNODB STATUS 的輸出如下（我故意在這裡做了一個非常慢的查詢）
===================================== 120323 15:26:29 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 8 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1470, signal count 1468 Mutex spin waits 0, rounds 7525, OS waits 112 RW-shared spins 803, OS waits 364; RW-excl spins 1300, OS waits 959 ------------ TRANSACTIONS ------------ Trx id counter 0 3145870 Purge done for trx's n:o < 0 3141943 undo n:o < 0 0 History list length 22 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 2958192640 MySQL thread id 7942, query id 69073 localhost root SHOW ENGINE INNODB STATUS ---TRANSACTION 0 3145869, ACTIVE 20 sec, OS thread id 2955325440, thread declared inside InnoDB 343 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1024, 162 row lock(s) MySQL thread id 7935, query id 69037 localhost root Copying to tmp table CREATE TABLE 1_temp_foo AS SELECT SQL_NO_CACHE a.* FROM crm_companies AS a LEFT JOIN users b ON a.zipcode = b.uid LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number ORDER BY a.country, a.name1 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 27579 OS file reads, 613 OS file writes, 392 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, node heap has 9 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2030837110 Log flushed up to 1 2030837110 Last checkpoint at 1 2030837110 0 pending log writes, 0 pending chkp writes 231 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 21060366; in additional pool allocated 1048576 Dictionary memory allocated 2897304 Buffer pool size 512 Free buffers 0 Database pages 503 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 36022, created 166, written 504 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 2957578240, state: waiting for server activity Number of rows inserted 2022, updated 7, deleted 13, read 528536 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 8.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================更新 2
在查詢執行時嘗試更新 b、c 或 d 時，INNODB 狀態如下：
===================================== 120323 16:12:58 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 27 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2959, signal count 2957 Mutex spin waits 0, rounds 27587, OS waits 426 RW-shared spins 1321, OS waits 516; RW-excl spins 2578, OS waits 1855 ------------ TRANSACTIONS ------------ Trx id counter 0 3145998 Purge done for trx's n:o < 0 3145994 undo n:o < 0 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 2958602240 MySQL thread id 7990, query id 69621 localhost root SHOW INNODB STATUS ---TRANSACTION 0 3145997, ACTIVE 35 sec, OS thread id 2955325440, thread declared inside InnoDB 227 mysql tables in use 1, locked 0 MySQL thread id 7984, query id 69594 localhost root Copying to tmp table CREATE TABLE 1_temp_foo AS SELECT SQL_NO_CACHE a.* FROM crm_companies AS a LEFT JOIN users b ON a.zipcode = b.uid LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number ORDER BY a.country, a.name1 Trx read view will not see trx with id >= 0 3145998, sees < 0 3145998 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 54447 OS file reads, 1335 OS file writes, 509 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 584 inserts, 584 merged recs, 4 merges Hash table size 34679, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2060137545 Log flushed up to 1 2060137545 Last checkpoint at 1 2060137545 0 pending log writes, 0 pending chkp writes 338 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 20799534; in additional pool allocated 1047808 Dictionary memory allocated 2897304 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 70769, created 661, written 3156 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread id 2957578240, state: waiting for server activity Number of rows inserted 2022, updated 66643, deleted 13, read 626517 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 7.59 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================還有實際打開的程序列表
我在您的
SHOW INNODB STATUS\GCREATE TABLE 1_temp_foo AS SELECT SQL_NO_CACHE a.* FROM crm_companies AS a LEFT JOIN users b ON a.zipcode = b.uid LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number ORDER BY a.country, a.name1這個查詢讓我毛骨悚然，因為它結合了您可能沒有想到的三件事：
- InnoDB 基於您的初始前提參與：
Using: MySQL 5.1.41 and InnoDB Tables- MyISAM 也參與其中。為什麼涉及 MyISAM？所有內部溫度表都是 MyISAM ！！！生成的連接是一個 MyISAM 表，在填充臨時表時必須將其轉換為 InnoDB。MyISAM 表的預設鎖定級別是多少？表級鎖定。
- 由於必須使新創建的表存在，因此涉及 DDL。在填充臨時表、轉換為 InnoDB 並最終重命名之前，該新表不會顯示
1_temp_foo。還有另一個值得注意的副作用。當你這樣做
CREATE TABLE tblname AS SELECT ...結果表沒有索引。
我有一些你可能會發現有助於繞過鎖定問題的東西。它涉及首先將表作為單獨的查詢，然後填充它。製作臨時表有兩種選擇：
選項#1：嘗試創建具有相同佈局的表格
CREATE TABLE 1_temp_foo LIKE crm_companies;這將創建
1_temp_foo具有與原始表完全相同的索引和儲存引擎的表crm_companies。選項#2：嘗試僅使用相同的儲存引擎創建表，但沒有索引。
CREATE TABLE 1_temp_foo SELECT * FROM crm_companies WHERE 1=2; ALTER TABLE 1_temp_foo ENGINE=InnoDB;創建表格後（無論您選擇哪種方式），您現在可以像這樣填充表格：
INSERT INTO 1_temp_foo SELECT SQL_NO_CACHE a.* FROM crm_companies AS a LEFT JOIN users b ON a.zipcode = b.uid LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number ORDER BY a.country, a.name ;現在，該查詢應該生成行級鎖，以便使數據可用於可重複讀取。換句話說，這是一個事務查詢。
警告
選項 #2 優於選項 #1
- 優勢 #1：如果 crm_companies 有任何外鍵約束，則選項 #1 是不可能的。為簡單起見，您必須選擇 OPTION #2。
- 優勢#2：由於選項#2 創建了一個沒有使用者定義索引的表，因此該表的載入速度應該比通過選項#1 創建的表快。