mysql - 请帮我优化一个mysql搜索查询

Question

我在 MySql (5.1) InnoDB 中有一个查询，它在一个包含部分的表中进行搜索。带有零件的表包含大约 500 000 行。该搜索还连接了另外两个表 tblcategory 和 tblheadcategory。我有很多用户使用此查询，它使我的服务器在负载过重时几乎崩溃。

我知道对此使用全文搜索是一个好方法，我希望我们可以更改它以在将来使用它。但由于 InnoDB 不可能做到这一点，所以我需要一个“快速”优化来让它现在运行。我应该如何优化它并设置索引和其他东西以使这个查询尽可能好地运行？

这是查询:

SELECT tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory

FROM tblpart

INNER JOIN tblcategory ON tblpart.categoryid = tblcategory.categoryid
INNER JOIN tblheadcategory ON tblcategory.headcategoryid = tblheadcategory.headcategoryid

WHERE (tblpart.title LIKE '%bmw%' OR tblpart.description LIKE '%bmw%' OR tblpart.brand LIKE '%bmw%')

ORDER BY

tblpart.title='bmw' DESC,
tblcategory.category LIKE '%bmw%' DESC

LIMIT 50;

表格:

CREATE TABLE `tblpart` (
    `partid` int(10) NOT NULL auto_increment,
    `userid` int(11) default '1',
    `categoryid` int(10) default '1',
    `title` varchar(100) default NULL,
    `brand` varchar(100) default NULL,
    `description` varchar(100) default NULL,
    PRIMARY KEY  (`partid`),
    KEY `userid` (`userid`),
    KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=534007 DEFAULT CHARSET=utf8;

CREATE TABLE `tblcategory` (
    `categoryid` int(10) NOT NULL auto_increment,
    `category` varchar(255) default NULL,
    `headcategoryid` int(10) default NULL,
      PRIMARY KEY  (`categoryid`)
) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8;

CREATE TABLE `tblheadcategory` (
    `headcategoryid` int(10) NOT NULL auto_increment,
    `headcategory` varchar(255) default NULL,
    PRIMARY KEY  (`headcategoryid`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

EXPLAIN 给出以下内容:(抱歉，我不知道如何正确格式化)

id   select_type   table            type    possible_keys   key      key_len  ref                         rows        extra
1    SIMPLE        tblpart          ALL     NULL            NULL     NULL     NULL                        522905      Using where; Using temporary; Using filesort
1    SIMPLE        tblcategory      eq_ref  PRIMARY         PRIMARY  4        tblpart.categoryid          1
1    SIMPLE        tblheadcategory  eq_ref  PRIMARY         PRIMARY  4        tblcategory.headcategoryid  1 

---

更新

根据建议，我尝试了 FULLTEXT 解决方案:

新的 MyISAM 表:

CREATE TABLE `tblpart_search` (
    `partid` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    `brand` varchar(100) DEFAULT NULL,
    `description` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`partid`),
    FULLTEXT KEY `all` (`title`,`brand`,`description`)
) ENGINE=MyISAM AUTO_INCREMENT=359596 DEFAULT CHARSET=utf8;

触发器:

DELIMITER ;;
CREATE TRIGGER `tblpart_insert_trigger` AFTER INSERT ON `tblpart` 
FOR EACH ROW INSERT INTO tblpart_search VALUES(NEW.partid,NEW.title,NEW.brand,NEW.description);;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_update_trigger` AFTER UPDATE ON `tblpart` 
FOR EACH ROW UPDATE tblpart_search SET tblpart_search.title=NEW.title,tblpart_search.brand=NEW.brand,tblpart_search.description=NEW.description WHERE tblpart_search.partid=NEW.partid;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_delete_trigger` AFTER DELETE ON `tblpart` 
FOR EACH ROW DELETE FROM tblpart_search WHERE tblpart_search.partid=OLD.partid;;
DELIMITER ;

新查询:

SELECT tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory

FROM tblpart_search
INNER JOIN tblpart ON tblpart_search.partid = tblpart.partid
INNER JOIN tblcategory ON tblpart.categoryid = tblcategory.categoryid
INNER JOIN tblheadcategory ON tblcategory.headcategoryid = tblheadcategory.headcategoryid

WHERE MATCH (tblpart_search.title, tblpart_search.brand, tblpart_search.description) AGAINST ('bmw,car')
LIMIT 50;

Answer 1

您无法真正优化带有前导通配符的查询(即使使用 FULLTEXT 搜索)。

您在这里唯一可以做的就是将查询一分为三(在客户端):

SELECT  tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory
FROM    tblpart
INNER JOIN
        tblcategory
ON      tblpart.categoryid = tblcategory.categoryid
INNER JOIN
        tblheadcategory
ON      tblcategory.headcategoryid = tblheadcategory.headcategoryid
WHERE   tblpart.title = 'bmw'
ORDER BY
        tblcategory.category LIKE '%bmw%' DESC
LIMIT 50

SELECT  tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory
FROM    tblpart
INNER JOIN
        tblcategory
ON      tblpart.categoryid = tblcategory.categoryid
INNER JOIN
        tblheadcategory
ON      tblcategory.headcategoryid = tblheadcategory.headcategoryid
WHERE   tblpart.title <> 'bmw'
        AND  (tblpart.title LIKE '%bmw%' OR tblpart.description LIKE '%bmw%' OR tblpart.brand LIKE '%bmw%')
        AND tblcategory.category LIKE '%bmw%'
LIMIT N

SELECT  tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory
FROM    tblpart
INNER JOIN
        tblcategory
ON      tblpart.categoryid = tblcategory.categoryid
INNER JOIN
        tblheadcategory
ON      tblcategory.headcategoryid = tblheadcategory.headcategoryid
WHERE   tblpart.title <> 'bmw'
        AND  (tblpart.title LIKE '%bmw%' OR tblpart.description LIKE '%bmw%' OR tblpart.brand LIKE '%bmw%')
        AND tblcategory.category NOT LIKE '%bmw%'
LIMIT N

并替换N在最后一次查询中使用 50 - records , 其中records是前面查询返回的记录数

第一个查询可以使用 title 上的索引来提供服务.

更新:

A FULLTEXT搜索可以这样实现:

CREATE TABLE `tblpart_search` (
    `partid` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    `brand` varchar(100) DEFAULT NULL,
    `description` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`partid`),
    FULLTEXT KEY `all` (`title`,`brand`,`description`)
) ENGINE=MyISAM AUTO_INCREMENT=359596 DEFAULT CHARSET=utf8;

触发器:

DELIMITER ;;
CREATE TRIGGER `tblpart_insert_trigger` AFTER INSERT ON `tblpart` 
FOR EACH ROW INSERT INTO tblpart_search VALUES(NEW.partid,NEW.title,NEW.brand,NEW.description);;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_update_trigger` AFTER UPDATE ON `tblpart` 
FOR EACH ROW UPDATE tblpart_search SET tblpart_search.title=NEW.title,tblpart_search.brand=NEW.brand,tblpart_search.description=NEW.description WHERE tblpart_search.partid=NEW.partid;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_delete_trigger` AFTER DELETE ON `tblpart` 
FOR EACH ROW DELETE FROM tblpart_search WHERE tblpart_search.partid=OLD.partid;;
DELIMITER ;

新查询:

SELECT tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory

FROM tblpart_search
INNER JOIN tblpart ON tblpart_search.partid = tblpart.partid
INNER JOIN tblcategory ON tblpart.categoryid = tblcategory.categoryid
INNER JOIN tblheadcategory ON tblcategory.headcategoryid = tblheadcategory.headcategoryid

WHERE MATCH (tblpart_search.title, tblpart_search.brand, tblpart_search.description) AGAINST ('+bmw +car' IN BOOLEAN MODE)
LIMIT 50;

设置ft_min_word_len至 3或更少，以便它可以索引 3 -字符词，如 'BMW'和 'CAR' .