mariadb 忽略力指數

我正在嘗試調整一個相對簡單的查詢：

select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

bigtable與整體規模相比，匹配的數量相對較少（< 1%）

這是解釋計劃：

+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
| id   | select_type | table | type   | possible_keys   | key     | key_len | ref             | rows     | Extra       |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
|    1 | PRIMARY     | bt    | ALL    | ix_smalltable_id| NULL    | NULL    | NULL            | 22709766 | Using where |
|    1 | PRIMARY     | st    | eq_ref | PRIMARY,ix_name | PRIMARY | 2       | bt.smalltable_id|        1 | Using where |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+

不知何故，即使獲取索引會更容易，但事實並非如此。

然後我嘗試強制索引：

select bt.col1,bt.col2
from bigtable bt force index (ix_smalltable_id)
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

但是查詢計劃和查詢時間是一樣的。它不想使用索引。

我嘗試在 bt.smalltable_id in (select …) 中進行操作，但查詢計劃和時間相同。

但是如果我獲取第smalltable_id一個，然後將其嵌入到選擇中，它會快得多。

問題

我可以通過啟用一些標誌來強制索引嗎？這是查詢引擎的限制嗎？

表結構

CREATE TABLE `smalltable` (
 `smalltable_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`smalltable_id`),
 KEY `ix_name` (`name`(10))
) ENGINE=InnoDB AUTO_INCREMENT=5698 DEFAULT CHARSET=utf8

CREATE TABLE `bigtable` (
 `bigtable_id` bigint unsigned NOT NULL AUTO_INCREMENT,
 `col1` varchar(255) DEFAULT NULL,
 `col2` varchar(255) DEFAULT NULL,
 ...
 `smalltable_id` smallint(5) unsigned DEFAULT NULL,
 PRIMARY KEY (`bigtable_id`),
 KEY `ix_smalltable_id` (`smalltable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23167374 DEFAULT CHARSET=utf8

我很確定問題出在“部分”索引上：

CREATE TABLE `smalltable` (
 ...
 KEY `ix_name` (`name`(10))
) 

嘗試在列的全長上添加索引後執行查詢：

ALTER TABLE smalltable
   DROP INDEX ix_name
 , ADD INDEX ix_name_full (name) ;

STRAIGHT_JOIN如果你重寫查詢會給你什麼：

SELECT bt.col1,bt.col2
FROM smalltable st
STRAIGHT_JOIN bigtable bt ON bt.smalltable_id = st.smalltable_id
WHERE st.name = 'some name occuring only once in st'
LIMIT 10

我很想知道這是否是一個更快的計劃，或者 MySQL 優化器是否“正確”首先讀取 bigtable 行。

為了進一步回答關於 MariaDB ‘忽略’強制索引的問題，問題是當你這樣做時它沒有任何東西可以使用，FORCE INDEX (ix_smalltable_id)因為無論如何它都必須進行全表掃描bigtable。

您可以嘗試FORCE INDEX (ix_name)（尤其是結合將部分索引更改為 ypercube 建議的完整索引）來查看是否有幫助，但如果它做了任何事情，它可能與STRAIGHT_JOIN重構相同。

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