mysql多列索引(Multiple-Part Index)多個列上range scan時使用in Sql代碼 show create table 20130314t1 CREATE TABLE `20130314t1` ( `id` int(11) NOT NULL, `col1` int(11) NOT NULL, `col2` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `key1` (`col1`,`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 其中表裡插入了100w數據,col1和col2都是100以內的隨機數. 進行如下的查詢, Sql代碼 select COUNT(*) from 20130314t1 where col1 >19 and col1 <30 and col2 >30 and col2 <33; 這是在多列索引上進行的range scan,理論上mysql只能使用索引的一部分,即col1那部分,從查詢計劃來看,key_len是4 mysqlslap工具測試下,平均時間是0.178s 把查詢改成 Sql代碼 select COUNT(*) from 20130314t1 where col1 BETWEEN 20 and 29 and col2 >30 and col2 <33; 這個非常奇怪,理論上key_len應該還是4,因為5.1的文檔說 引用 If the operator is >, <, >=, <=, !=, <>, BETWEEN, or LIKE, the optimizer uses it but considers no more key parts. 結果key_len成了8,不過mysqlslap測試的結果提升不大,變成了0.156s 現在使用in語句,因為都是int類型,語句可以改成 Sql代碼 select COUNT(*) from 20130314t1 where col1 in(20,21,22,23,24,25,26,27,28,29) and col2 >30 and col2 <33 key_len還是8,不過變成了0.005s 這是因為col1 BETWEEN 20 and 29是range scan(范圍掃描), 而col1 in(20,21,22,23,24,25,26,27,28,29)是多值相等,盡管結果一樣,但是意義有著顯著的不同. 可以通過show status like 'Handler_%';來觀察三個語句執行中的讀情況 select COUNT(*) from 20130314t1 where col1 >19 and col1 <30 and col2 >30 and col2 <33; | Handler_read_key | 1 | | Handler_read_next | 99856 | select COUNT(*) from 20130314t1 where col1 BETWEEN 20 and 29 and col2 >30 and col2 <33; | Handler_read_key | 1 | | Handler_read_next | 90168 | select COUNT(*) from 20130314t1 where col1 in(20,21,22,23,24,25,26,27,28,29) and col2 >30 and col2 <33; | Handler_read_key | 10 | | Handler_read_next | 2072 | 看到使用了in之後,Handler_read_next變小了,說明按索引掃描的行明顯變少了,所以有了提高.
