偶爾慢查詢的原因？

我們在 Windows Server 2008 R2 上執行 MySQL 5.1。

我們最近一直在對我們的數據庫進行一些診斷，並發現了一些我們無法解釋的令人不安的偽影。當我們有需要很長時間（> 2000ms）的查詢時，我們添加了一些程式碼來記錄。結果令人驚訝（並且可能解釋了我們的僵局）。

有時，通常需要很少時間（<10 毫秒）的查詢需要 4 到 13 秒。需要明確的是，這些查詢是不斷執行（每秒幾次）並且不會受到這些查詢時間峰值的影響。

我們已經通過索引尋找任何明顯的錯誤，但運氣不佳。

更新

人員表：

| people | CREATE TABLE `people` (
`people_id` bigint(20) NOT NULL AUTO_INCREMENT,
`company_id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`temp_password` varchar(10) DEFAULT NULL,
`reset_password_hash` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL,
`mobile` varchar(32) DEFAULT NULL,
`iphone_device_id` varchar(160) DEFAULT NULL,
`iphone_device_time` datetime DEFAULT NULL,
`last_checkin` datetime DEFAULT NULL,
`location_lat` double DEFAULT NULL,
`location_long` double DEFAULT NULL,
`gps_strength` smallint(6) DEFAULT NULL,
`picture_blob_id` bigint(20) DEFAULT NULL,
`authority` int(11) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '1',
`date_created` datetime NOT NULL,
`last_login` datetime NOT NULL,
`panic_mode` tinyint(1) NOT NULL DEFAULT '0',
`battery_level` double DEFAULT NULL,
`battery_state` varchar(32) DEFAULT NULL,
PRIMARY KEY (`people_id`),
KEY `email` (`email`),
KEY `company_id` (`company_id`),
KEY `iphone_device_id` (`iphone_device_id`),
KEY `picture_blob_id` (`picture_blob_id`),
CONSTRAINT `people_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `people_ibfk_2` FOREIGN KEY (`picture_blob_id`) REFERENCES `blobs` (`blob_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4658 DEFAULT CHARSET=utf8 |

索引：

+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| people |          0 | PRIMARY          |            1 | people_id        | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | email            |            1 | email            | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | company_id       |            1 | company_id       | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | iphone_device_id |            1 | iphone_device_id | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | picture_blob_id  |            1 | picture_blob_id  | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

我們在伺服器上的表中有大約 5000 行，這給我們帶來了麻煩。

前兩個問題（ Question1，Question2 ）中的 UPDATE 查詢通過 PRIMARY KEY 使用行級鎖定命中表“people”。這就是我在 2011 年 6 月 6 日上午 10:03 的問題 1 中所說的

所有事務都在遍歷 PRIMARY 鍵。由於 PRIMARY 是 InnoDB 中的聚集索引，因此 PRIMARY 鍵和行本身是在一起的。因此，遍歷一行和 PRIMARY KEY 是一回事。因此，PRIMARY KEY 上的任何索引鎖也是行級鎖。

尚未考慮其他可以將緩慢歸因於索引的其他因素：在 InnoDB 中使用非唯一索引。InnoDB 中使用非唯一索引的每個索引查找也將每行的 rowID 附加到非唯一鍵。rowID 基本上來自 Clustered Index。即使表沒有主鍵，更新非唯一索引也必須始終與聚集索引互動。

要考慮的另一件事是在索引中管理 BTREE 節點的過程。有時，它需要對節點進行頁面拆分。非唯一索引的 BTREE 節點中的所有條目都包含非唯一欄位加上聚集索引中的 rowID。為了在不影響數據完整性的情況下適當地減輕此類 BTREE 頁面的拆分，與 rowID 關聯的行必須在內部經歷行級鎖定。

如果“people”表有很多非唯一索引，請準備好在表空間中擁有大量索引頁，並且時不時地讓很小的行鎖潛入你。

還有另一個不那麼明顯的因素：主要人口

有時當索引被填充時，構成索引的鍵值可能會隨著時間的推移變得不平衡，並導致 MySQL 查詢優化器從鍵查找切換到索引掃描，最後切換到全表掃描。除非您使用新索引重新設計表以補償鍵的不平衡性，否則您無法控制。請提供“people”表的表結構、“people”表的計數以及“people”表的顯示索引輸出。

即使查詢僅使用 PRIMARY KEY，非唯一索引中鍵的不平衡性仍然需要 BTREE 平衡和頁面拆分發生。由於您不打算發生的間歇性行級鎖定，這種 BTREE 管理將產生顯著的減速。

更新 2011-06-14 22:19

來自問題 1 的查詢

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>',
iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>',
iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

想像事件中的順序

1. 按 PRIMARY KEY 查找行
2. 鎖定行和聚集索引
3. 為所有正在更新的列創建 MVCC 數據
4. 索引了四列（email、company_id、iphone_device_id、picture_blob_id）
5. 每個索引都需要BTREE管理
6. 在同一個事務空間內，步驟 1-5 試圖在同一行重複，更新相同的列（兩個查詢中的電子郵件相同，兩個查詢中的 company_id 相同，兩個查詢中的 picture_blob_id 相同，iphone_device_id 不同）

來自問題 2 的查詢

UPDATE people SET iphone_device_id=NULL
WHERE iphone_device_id='iphone:<device_id_blah>' AND people_id<>666;

UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com',
phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>',
iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',
location_lat = <lat>, location_long = <lng>, gps_strength = 66,
picture_blob_id = 1661,
authority = 1, active = 1, date_created = '2011-03-20 19:18:34',
last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55,
battery_state = 'unplugged' WHERE people_id = 666;

這兩個查詢更加令人困惑，因為第一個查詢正在更新除 people_id 666 之外的所有內容。僅第一個查詢就痛苦地鎖定了數百行。第二個查詢是更新執行 5 個事件序列的 people_id 666。第一個查詢在所涉及的每一行上執行相同的 5 個事件序列，除了 people_id 666，但 iphone_device_id 的索引在具有兩個不同查詢的攔截過程中。有人必須按照先到先得的原則鎖定 BTREE 頁面。

面對沖突過程中的這兩對查詢，可能會在一個索引中鎖定相同的 BTREE 頁面，這對於 InnoDB 或任何符合 ACID 的 RDBMS 來說可能是一種痛苦的體驗。因此，索引減速是這些查詢對的命運，除非您可以保證查詢以 AUTOCOMMIT = 1 執行或允許臟讀（儘管這樣的衝突使 READ-COMMITTED 和 READ-UNCOMMITED 成為 MVCC 的噩夢）。

更新 2011-06-15 10:29

@RedBlueThing：在問題 2 的查詢中，第一個查詢是范圍查詢，因此獲得了很多行鎖。另請注意，兩個查詢都試圖鎖定相同的空間 id 0 頁號 4611 n 位 152 被鎖定在 PRIMARY KEY 中，即聚集索引。

為了確保您的應用程序至少根據您期望的一系列事件執行，您可以嘗試兩種不同的選項：

選項 1）將此表轉換為 MyISAM（至少在開發伺服器上）。每個 UPDATE、INSERT 和 DELETE 都會在先到先得的基礎上施加一個全表鎖。

選項 2) 嘗試使用SERIALIZABLE隔離級別。這將在共享模式下鎖定所有預期的行。

使用這兩個替代選項，您期望的事件序列要麼中斷，要麼成功。如果這兩個選項都失敗了，那麼您將需要查看您的應用程序並確定查詢執行順序的優先級。一旦確定了優先級，您就可以簡單地撤消這些選項（對於選項 1，返回 InnoDB，對於選項 2，返回預設隔離級別

$$ stop using SERIALIZABLE $$).

顯示像’innodb%‘這樣的變數；– 特別是，如果數據和索引還沒有達到緩衝池的大小，您可能會比以前更難地訪問磁碟。I/O 是最大的性能殺手。

您的大部分欄位是所需欄位的兩倍。BIGINT（8 字節）對於大多數 id 來說都是多餘的。5000 行只需要一個 SMALLINT UNSIGNED（限制為 65K，只有 2 個字節）。或者使用 MEDIUMINT 來獲得安全邊際。

DOUBLE 以 8 個字節為代價為您提供 16 個有效數字。battery_level 的精度是否超過 2 位有效數字？FLOAT 佔用 4 個字節。

我的觀點是“更小-> 更可記憶體-> 更快”。

請向我們展示緩慢的查詢；至少有一些突然變慢了。沒有它們，我們只能猜測。開啟慢日誌並設置 long_query_time = 1; 這些將有助於找到最慢的查詢。

您了解“複合”索引的好處嗎？

引用自：https://dba.stackexchange.com/questions/3302