MySQL同时使用全文索引和普通索引。

Question

我有一张这样的桌子：

    id  type    idElem  name      date  content  lang     state  
------  ------  ------  ------  ------  -------  ------  --------

当我对它运行此查询时：

SELECT 
  `contents`.* 
FROM
  `contents` 
WHERE `contents`.type = "files" 
  AND `contents`.name = "url" 
  AND `contents`.state = 2 
  AND MATCH(`contents`.content) AGAINST ("somewords")

只使用content上的全文索引，而不使用type, idElem, name, state上的全文索引：

    id  select_type  table     partitions  type      possible_keys  key     key_len  ref       rows  filtered  Extra                          
------  -----------  --------  ----------  --------  -------------  ------  -------  ------  ------  --------  -------------------------------
     1  SIMPLE       contents  (NULL)      fulltext  type,idx       idx     0        const        1      5.00  Using where; Ft_hints: sorted  

为什么？

下面是我的表模式(有超过300 k行)：

CREATE TABLE `contents` (
  `id` int(11) NOT NULL,
  `type` varchar(35) NOT NULL,
  `idElem` int(11) NOT NULL,
  `name` varchar(35) NOT NULL,
  `date` int(11) NOT NULL,
  `content` longtext NOT NULL,
  `lang` varchar(3) DEFAULT NULL,
  `state` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `contents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `type` (`type`,`idElem`,`name`,`state`);
ALTER TABLE `contents` ADD FULLTEXT KEY `idx` (`content`);


ALTER TABLE `contents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Answer 1

MySQL很少在查询中使用超过一个表的索引，也从不将全文索引与任何其他索引交叉。

您的计划显示-出于某些原因，它选择英国“金融时报”作为这个查询的最佳选择。

索引合并不适用于全文索引.我们计划在未来的MySQL发行版中将其扩展到包括这些内容。

合并优化

问题后编辑:您可以更改索引

KEY type (type,idElem,name,state);

敬-

KEY type (type,name,state); 

比较- MySQL可以开始使用它，根据您的数据，它可能会更好(可能不是)。如果MySQL继续仅使用FT索引，则可以使用索引提示-强制索引(“type”)进行测试。

里克·詹姆斯的评论:不总是，如果范围-是的(至少我没有其他的例子)，并且同意你的观点，

但如果(和=、=和=)，如原始问题(或PK搜索)，则可以采用索引进行直接搜索。

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `text` varchar(2048) DEFAULT NULL,
  KEY `idx` (`id`,`value`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

填充和简单测试：

select * from t1 where (id >2 and id < 4) and MATCH (text) AGAINST ('very')

正如你所预测的：

1   SIMPLE  t1      fulltext    idx,text    text    0   const   1   11.11   Using where; Ft_hints: sorted

但是：

select * from t1 where id =3  and value = '3' and MATCH (text) AGAINST ('very')

它还需要其他指数：

1   SIMPLE  t1      ref idx,text    idx 262 const,const 1   11.11   Using where

所以它总是需要-制造和测试。