千萬級資料庫使用索引查詢速度更慢的疑惑-資料回表問題

資料庫：TiDB資料庫（和mysql資料庫極其相似的資料庫）

表名：index_basedata

表資料：13 000 000條資料

表索引：包含一個普通索引，索引列 ”year“

測試sql：

• SQL1 : select brand from index_basedata where year = 2018 group by day  limit 5; 

• SQL2 : select brand from index_basedata where month = 201807 group by day  limit 5; 

• SQL3 : select brand from index_basedata where year = 2018  limit 5;  

• SQL4 : select brand from index_basedata where month = 201807  limit 5; 

• sql1與sql2對比 ， sql3與sql4對比

測試sql執行時間：

• SQL1 : 23.6s

• SQL2 : 4.5s

• SQL3 : 0.007s

• SQL4 : 1.4s

explain解釋： 瞭解TiDB資料庫相關與explain請移步：https://pingcap.com/docs-cn/overview/#tidb-%e6%95%b4%e4%bd%93%e6%9e%b6%e6%9e%84

• sql1:select brand from xcar_index_basedata_noprice where year = 2018 group by day  limit 5; 

• sql2：select brand from xcar_index_basedata_noprice where month = 201807 group by day  limit 5; 

• sql3:select brand from xcar_index_basedata_noprice where year = 2018  limit 5;  

• sql4:select brand from xcar_index_basedata_noprice where month = 201807  limit 5; 

從圖中可以看出，sql1和sql3使用了索引，sql2和sql4沒有使用索引。

對於sql1和sql2，本應該使用所以的查詢時間少，但是使用了索引的sql1使用的時間是沒有使用索引的sql2查詢時間的5倍，為什麼？

對於sql3和sql4，恢復了正常，使用索引比不使用索引查詢速度快，為什麼上述兩個現象會相差如此之大？

在sql1和sql2中，sql1索引列獲取資料的速度大於sql2中獲得資料的速度。但是在group by時在sql1中，使用索引得到的地址，需要回表才可以得到真實的資料，需要根據地址去獲取資料，資料回表問題嚴重。

在sql2中獲取的是直接資料，group by 不需要回表。

sql2,sql3,sql4表現正常。

通俗的講：如果索引的列在select所需獲得列中就不需要回表（因為在mysql中索引是根據索引列的值進行排序的，所以索引節點中存在該列中的部分值），如果select所需獲得列中有大量的非索引列，索引就需要到表中找到相應的列的資訊，這就叫回表。

案例：

測試環境：與上述相同

測試sql：

• sql1：select brand from index_basedata where year = 2018 group by day limit 5;

• 執行時間：21.8s

• explain一下：

• 

• 使用了索引“year”, 則索引列為year，但是select brand from..中brand並不是索引列，就需要回表（通過圖也可以看出，進行了tablescan，另外其中的IndexLookUp也說明了進行了回表），所以花費時間長

• sql2：select brand from index_basedata where year = 2018 group by year limit 5;

• 執行時間：21.7s

• explain一下：

• 

• 使用了索引“year”, 則索引列為year，但是select brand from..中brand並不是索引列，就需要回表（通過圖也可以看出，進行了tablescan,另外其中的IndexLookUp也說明了進行了回表），所以花費時間長，另外，對於sql2中的group by使用的是索引列，所以使用的StreamAgg，不同於sql1

• sql3：select year from index_basedata where year = 2018 group by year limit 5;

• 執行時間：2.5s

• explain一下：

• 

• 可以看到：沒有tablescan，也沒有使用IndexLookUp而是IndexReader說明直接從索引中讀取索引列並使用。

總結：在上述案例中，sql3使用了索引列，沒有進行回表，sql1與sql2進行了回表，所以花費時間長。所以說，發生嚴重的回表的時候，查詢速度比不使用索引還慢。

select year from xcar_index_basedata_noprice where year = 2018 group by year limit 5;

select month from xcar_index_basedata_noprice where month = 201807 group by month limit 5

select brand_id from xcar_index_basedata_noprice where month = 201807 group by month limit 5;

select brand_id from xcar_index_basedata_noprice where year = 2018 group by year limit 5;

select brand_id from xcar_index_basedata_noprice where year = 2018 group by month limit 5;

對應執行時間：