Mysql

如何在 MySQL 中的多列索引中跳過前綴列檢查?

  • July 18, 2019

下面是我的展示表結構:

CREATE TABLE `table1` (
 `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent',
 `postLocation` smallint(6) NOT NULL,
 `postArea` smallint(6) DEFAULT NULL,
 `postDetails` text,
 KEY `table1_postType_postLocation_postArea_index` (`postType`,`postLocation`,`postArea`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

在這裡,我在列上有索引(postType, postLocation, postArea)。我的postType列值是固定的,它將是0 or 1.

如何使用索引(postType, postLocation, postArea)或跳過postType列值並使用其他兩個索引?

如果我包含0, 1在查詢中,它會跳過postType索引檢查中的列嗎?還是會先檢查索引中的postType列而1 or 2不是繼續檢查索引中的下一列?下面是一個例子:

select *
from table1 t
where t.postType in (0, 1)
 and t.postLocation = 1
 and t.postArea = 15;

請建議我是否需要更新postType列的數據類型。

這取決於。由於您使用的是 MySQL 8.0,因此以開頭的索引type可能沒問題。我猜優化器會足夠聰明

  1. 獲取具有 (0,1,15) 的行
  2. 繼續獲取,現在獲取具有 (1,1,15) 的行。

EXPLAIN你可以從或中得到一些線索EXPLAIN FORMAT=JSON

您可以通過這種技術“證明”它正在(或沒有)以最佳方式執行此操作:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

如果Handler_read_%值的總和非常接近返回的行數,那麼它可能以最佳方式做到了。否則這些Handler_read值可能會大得多。

PRIMARY KEY在那張桌子上沒有看到。如果那個三元組是“唯一的”,那麼讓它成為 PK。由於 PK 的“分群”,這將加快速度。

如果索引不夠好,請將其重新排列到type最後。

(小毛病:從列名中刪除常量“post”;它使查詢變得混亂。)

引用自:https://dba.stackexchange.com/questions/243202