Mysql
快速查詢，理論上使用索引，還在慢查詢日誌中
我很難解釋
EXPLAIN這些查詢的結果。它們都在慢查詢日誌中結束，但執行時間約為 0.0050 毫秒，最終結果集始終低於 100 行。這裡有什麼問題？我的第二個“改進”版本更好嗎？有什麼建議嗎？
mysql> # Original mysql> EXPLAIN SELECT SQL_NO_CACHE relSrc, relDst, 1 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships1 -> LEFT JOIN productsRelationshipsDesc on 1=relTypeID -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 2 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships2 -> LEFT JOIN productsRelationshipsDesc on 2=relTypeID -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships3 -> LEFT JOIN productsRelationshipsDesc on 3=relTypeID -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 5 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships5 -> LEFT JOIN productsRelationshipsDesc on 5=relTypeID -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 6 as relType, relTypeDesc, fracQty, '24794' as source FROM productsRelationships6 -> LEFT JOIN productsRelationshipsDesc on 6=relTypeID -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 7 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships7 -> LEFT JOIN productsRelationshipsDesc on 7=relTypeID -> WHERE relDst='24794' OR relSrc='24794' -> ORDER BY relType, relSrc, RelDst; +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+ | 1 | PRIMARY | productsRelationships1 | index | PRIMARY,src-1 | src-1 | 2 | NULL | 663 | Using where; Using index | | 1 | PRIMARY | productsRelationshipsDesc | ref | relTypeID | relTypeID | 1 | const | 1 | Using index | | 2 | UNION | productsRelationships2 | index | src-dst-2 | src-dst-2 | 4 | NULL | 13126 | Using where; Using index | | 2 | UNION | productsRelationshipsDesc | ref | relTypeID | relTypeID | 1 | const | 1 | Using index | | 3 | UNION | productsRelationships3 | index | PRIMARY | PRIMARY | 4 | NULL | 11459 | Using where; Using index | | 3 | UNION | productsRelationshipsDesc | ref | relTypeID | relTypeID | 1 | const | 1 | Using index | | 4 | UNION | productsRelationships5 | index | PRIMARY,src-5 | src-5 | 2 | NULL | 369 | Using where; Using index | | 4 | UNION | productsRelationshipsDesc | ref | relTypeID | relTypeID | 1 | const | 1 | Using index | | 5 | UNION | productsRelationships6 | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2 | NULL | 2 | Using union(dst-6,PRIMARY); Using where | | 5 | UNION | productsRelationshipsDesc | ref | relTypeID | relTypeID | 1 | const | 1 | Using index | | 6 | UNION | productsRelationships7 | index | PRIMARY,src-7 | src-7 | 2 | NULL | 1 | Using where; Using index | | 6 | UNION | productsRelationshipsDesc | ref | relTypeID | relTypeID | 1 | const | 1 | Using index | | NULL | UNION RESULT | <union1,2,3,4,5,6> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort | +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+ 13 rows in set (0.00 sec) mysql> mysql> mysql> # Improved? mysql> EXPLAIN SELECT SQL_NO_CACHE relSrc, relDst, relType, fracQty, source, relTypeDesc FROM ( -> SELECT relSrc, relDst, 1 as relType, 0 as fracQty, '24794' as source FROM productsRelationships1 -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 2 as relType, 0 as fracQty, '24794' as source FROM productsRelationships2 -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 3 as relType, 0 as fracQty, '24794' as source FROM productsRelationships3 -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 5 as relType, 0 as fracQty, '24794' as source FROM productsRelationships5 -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 6 as relType, fracQty, '24794' as source FROM productsRelationships6 -> WHERE relDst='24794' OR relSrc='24794' -> UNION ALL -> SELECT relSrc, relDst, 7 as relType, 0 as fracQty, '24794' as source FROM productsRelationships7 -> WHERE relDst='24794' OR relSrc='24794' -> ) AS rels -> LEFT JOIN productsRelationshipsDesc ON relType=relTypeID -> ORDER BY relType, relSrc, RelDst; +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 38 | Using filesort | | 1 | PRIMARY | productsRelationshipsDesc | ref | relTypeID | relTypeID | 1 | rels.relType | 1 | Using index | | 2 | DERIVED | productsRelationships1 | index | PRIMARY,src-1 | src-1 | 2 | NULL | 663 | Using where; Using index | | 3 | UNION | productsRelationships2 | index | src-dst-2 | src-dst-2 | 4 | NULL | 13126 | Using where; Using index | | 4 | UNION | productsRelationships3 | index | PRIMARY | PRIMARY | 4 | NULL | 11459 | Using where; Using index | | 5 | UNION | productsRelationships5 | index | PRIMARY,src-5 | src-5 | 2 | NULL | 369 | Using where; Using index | | 6 | UNION | productsRelationships6 | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2 | NULL | 2 | Using union(dst-6,PRIMARY); Using where; Using index | | 7 | UNION | productsRelationships7 | index | PRIMARY,src-7 | src-7 | 2 | NULL | 1 | Using where; Using index | | NULL | UNION RESULT | <union2,3,4,5,6,7> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+ 9 rows in set (0.00 sec)這些是表格定義
mysql> SHOW CREATE TABLE productsRelationships1\G *************************** 1. row *************************** Table: productsRelationships1 Create Table: CREATE TABLE `productsRelationships1` ( `relSrc` smallint(5) unsigned NOT NULL, `relDst` smallint(5) unsigned NOT NULL, PRIMARY KEY (`relSrc`,`relDst`), UNIQUE KEY `src-1` (`relSrc`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE productsRelationships2\G *************************** 1. row *************************** Table: productsRelationships2 Create Table: CREATE TABLE `productsRelationships2` ( `relSrc` smallint(5) unsigned NOT NULL, `relDst` smallint(5) unsigned NOT NULL, KEY `src-dst-2` (`relSrc`,`relDst`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE productsRelationships3\G *************************** 1. row *************************** Table: productsRelationships3 Create Table: CREATE TABLE `productsRelationships3` ( `relSrc` smallint(5) unsigned NOT NULL, `relDst` smallint(5) unsigned NOT NULL, PRIMARY KEY (`relSrc`,`relDst`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE productsRelationships5\G *************************** 1. row *************************** Table: productsRelationships5 Create Table: CREATE TABLE `productsRelationships5` ( `relSrc` smallint(5) unsigned NOT NULL, `relDst` smallint(5) unsigned NOT NULL, PRIMARY KEY (`relSrc`,`relDst`), UNIQUE KEY `src-5` (`relSrc`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE productsRelationships6\G *************************** 1. row *************************** Table: productsRelationships6 Create Table: CREATE TABLE `productsRelationships6` ( `relSrc` smallint(5) unsigned NOT NULL, `relType` tinyint(2) unsigned NOT NULL DEFAULT '6', `fracQty` int(2) unsigned NOT NULL, `relDst` smallint(5) unsigned NOT NULL, PRIMARY KEY (`relSrc`,`relDst`), UNIQUE KEY `src-6` (`relSrc`), UNIQUE KEY `dst-6` (`relDst`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE productsRelationships7\G *************************** 1. row *************************** Table: productsRelationships7 Create Table: CREATE TABLE `productsRelationships7` ( `relSrc` smallint(5) unsigned NOT NULL, `relDst` smallint(5) unsigned NOT NULL, PRIMARY KEY (`relSrc`,`relDst`), UNIQUE KEY `src-7` (`relSrc`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE productsRelationshipsDesc\G *************************** 1. row *************************** Table: productsRelationshipsDesc Create Table: CREATE TABLE `productsRelationshipsDesc` ( `relTypeID` tinyint(2) unsigned NOT NULL AUTO_INCREMENT, `relTypeDesc` varchar(100) NOT NULL, UNIQUE KEY `relTypeID` (`relTypeID`,`relTypeDesc`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)這是每個表中的數據量。
mysql> SELECT COUNT(1) FROM productsRelationships1\G *************************** 1. row *************************** COUNT(1): 663 1 row in set (0.00 sec) mysql> SELECT COUNT(1) FROM productsRelationships2\G *************************** 1. row *************************** COUNT(1): 263 1 row in set (0.00 sec) mysql> SELECT COUNT(1) FROM productsRelationships3\G *************************** 1. row *************************** COUNT(1): 8551 1 row in set (0.01 sec) mysql> SELECT COUNT(1) FROM productsRelationships5\G *************************** 1. row *************************** COUNT(1): 369 1 row in set (0.00 sec) mysql> SELECT COUNT(1) FROM productsRelationships6\G *************************** 1. row *************************** COUNT(1): 80 1 row in set (0.00 sec) mysql> SELECT COUNT(1) FROM productsRelationships7\G *************************** 1. row *************************** COUNT(1): 0 1 row in set (0.00 sec) mysql> SELECT COUNT(1) FROM productsRelationshipsDesc\G *************************** 1. row *************************** COUNT(1): 7 1 row in set (0.00 sec)更新：根據
EXPLAINIt APPEARS to be using index 但慢查詢日誌說明了這一點# Query_time: 0.005458 Lock_time: 0.000340 Rows_sent: 38 Rows_examined: 50579，這是當我取出 ORDER 子句時它可能是一個快速查詢，但它幾乎在系統的每一個操作中都被呼叫。
UPDATE2：好的，我要瘋了
# Query_time: 0.003527 Lock_time: 0.000164 Rows_sent: 38 Rows_examined: 8554 SET timestamp=1370017780; SELECT SQL_NO_CACHE relSrc, relDst, 3 as relType, 0 as fracQty, '24794' as source FROM productsRelationships3 WHERE relSrc='24794' OR relDst='24794';這怎麼可能？
productsRelationships3有一個組合索引PRIMARY KEY (relSrc,relDst)，為什麼要檢查每一行？
評論中有很多很好的材料，但我在早期忽略了一些明顯的東西，這實際上使問題的答案相當簡單。
我錯過的是
OR在查詢中的重複使用，我最初將其誤讀為AND… 這會產生很大的不同。你要求這個：WHERE relDst='24794' OR relSrc='24794'不幸的是 MySQL
EXPLAIN使用了這個片語using index，因為這並不意味著它正在索引中進行查找。Using index如果表中需要的所有列都包含在單個索引中，您實際上可以執行相當於全表掃描的操作。事實上，正如我將在下面展示的那樣，這正是這裡發生的事情。
Using index表示優化器已決定從索引中讀取數據，而不是從實際表行中讀取數據。由於索引幾乎總是表的列的適當子集，因此如果SELECT可以在單個索引中找到所有列，這意味著要處理的數據字節數更少，可能會降低成本CPU、I/O 和/或記憶體方面的查詢。所以這是一個很好的策略……但這並不意味著索引正在用於查找……只是它是一個成本較低的地方，可以從中讀取必要的列數據。
type大多數查詢的列顯示index。再一次，這並不意味著正在使用索引。這實際上與type=ALL- 全表掃描 - 但從索引樹而不是表數據讀取……所以這個值type與Using index.用於查找的索引通常顯示在輸出
key列中EXPLAIN…但是當type=index和ref=時NULL，情況並非如此。
PRIMARY因此，在您的大多數查詢中，優化器決定從頭到尾讀取整個鍵索引，在另一列中查找匹配值的行OR。多列索引不能用於解析
OR條件。它只能用於解決AND條件……或涉及從索引中的第一列開始的連續列子集的條件。2 列索引非常像印刷的城市電話簿，按姓氏然後名字排序……其中名稱通常以粗體列印，地址以較小或不太顯眼的字型列印。
我不能使用電話簿來查找姓氏為“Jones”
OR、名字為“John”的每個人，但我可以非常有效地使用它來查找姓氏為“Jones”和AND名字為“John”的每個人。AND（因此與我之前錯過的vs之間存在很大差異OR。）我也可以非常有效地使用它來找到姓氏為“瓊斯”的每個人，而不管他們的名字如何，但是當我想要做的只是找到每個人時，它完全沒用無論他們的姓氏如何，名字都是“約翰”。或者也許不完全。它不是最佳的，但在一定程度上仍然有用。繼續這個類比，假設我仍然需要找到名字為“John”的每個人，我將不得不閱讀目錄中的每一行，但我不需要閱讀不是粗體的列印——地址不要告訴我任何關於名字的事情。
因此，直接從索引讀取的價值，即使我不能從索引中數據的排序中受益……
Using index意味著優化器已經找到了一個覆蓋索引，可以滿足其讀取列數據的需求，無論是否索引用於查找或連接。有一張桌子是個例外：
| 5 | UNION | productsRelationships6 | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2 | NULL | 2 | Using union(dst-6,PRIMARY); Using where |這裡的區別在於您在兩列上都有索引，其中感興趣的列是索引中最左邊的（在本例中是唯一的）列。
UNIQUE KEY `src-6` (`relSrc`), UNIQUE KEY `dst-6` (`relDst`)優化器巧妙地選擇
index_merge優化——在兩個索引中查找所需的值並合併已辨識的行。如果您的所有表都有類似的排列，您應該會看到在慢速查詢日誌中檢查的行數要少得多——並且查詢可能會完全從慢速查詢日誌中退出。
這些索引不一定必須是唯一索引，但如果數據支持它，這是理想的，因為唯一索引只能返回一個值……但我建議將其添加到每個表中：
KEY dst_src (relDst,relSrc)…要不就…
KEY (relDst)現在，您有兩個索引 - 主鍵和這個新索引（同樣，如果它適合您的數據，您可能想要創建它
UNIQUE，或者如果它不適合）……其中一個按 relSrc 排序（主鍵） key) 和另一個按 relDst 排序…所以優化器可以選擇合併這個鍵和主鍵的結果，並顯著減少所涉及的行數。文件排序無法避免，因為
UNION ALL總是在幕後創建一個臨時表……它不能有索引……但我傾向於認為，基於上述內容，這不是原因查詢被記錄為“不使用索引”。最後，當您進行基準測試時，您可能希望
SELECT SQL_NO_CACHE在查詢中使用，以確保您每次都實際執行查詢，並且不會因為您從查詢中獲得結果而看到看似很小或奇怪不一致的響應時間記憶體，如果它在您的系統上啟用。