mysql - 如何优化两个表的搜索？

Question

我有两个表:

第一:

    CREATE TABLE IF NOT EXISTS `tags` (  `i` int(10) NOT NULL AUTO_INCREMENT,  `id` int(10) NOT NULL,  `k` varchar(32) COLLATE utf8_bin NOT NULL,  PRIMARY KEY (`i`),  KEY `k` (`k`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1505426 ;

和第二个:

CREATE TABLE IF NOT EXISTS `w` (  `id` int(7) NOT NULL,  `title` varchar(255) COLLATE utf8_bin NOT NULL,  `k1` varchar(24) COLLATE utf8_bin NOT NULL,  `k2` varchar(24) COLLATE utf8_bin NOT NULL,  `k3` varchar(24) COLLATE utf8_bin NOT NULL,  `k4` varchar(24) COLLATE utf8_bin NOT NULL,  `k5` varchar(24) COLLATE utf8_bin NOT NULL,  `k6` varchar(24) COLLATE utf8_bin NOT NULL,  `k7` varchar(24) COLLATE utf8_bin NOT NULL,  `k8` varchar(24) COLLATE utf8_bin NOT NULL,  `w` int(5) NOT NULL,  `h` int(5) NOT NULL,  `s` varchar(32) COLLATE utf8_bin NOT NULL,  `r` varchar(11) COLLATE utf8_bin NOT NULL,  `v` int(7) NOT NULL,  `c` varchar(32) COLLATE utf8_bin NOT NULL,  `c1` varchar(6) COLLATE utf8_bin NOT NULL,  `c2` varchar(6) COLLATE utf8_bin NOT NULL,  `c3` varchar(6) COLLATE utf8_bin NOT NULL,  `c4` varchar(6) COLLATE utf8_bin NOT NULL,  `c5` varchar(6) COLLATE utf8_bin NOT NULL,  `c6` varchar(6) COLLATE utf8_bin NOT NULL,  `m` varchar(4) COLLATE utf8_bin NOT NULL,  `t` int(10) NOT NULL,  `i` int(6) NOT NULL,  `o` int(6) NOT NULL,  `f` varchar(255) COLLATE utf8_bin NOT NULL,  PRIMARY KEY (`id`),  KEY `keywords` (`k1`,`k2`,`k3`,`k4`,`k5`,`k6`,`k7`,`k8`),  KEY `category` (`c`),  KEY `color1` (`c1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;

我正在尝试从表“tags”中搜索 2 个词($search1 + $search2)并在“w”表中查找 ID。 查询:

SELECT w.id,w.title,w.k1,w.k2,w.k3,w.k4,w.k5,w.k6,w.k7,w.w,w.h,w.s,w.c1,w.c2,w.c3,w.c4,w.c5,w.c6,w.m,w.fFROM tags,tags as tags2,wWHERE tags.k ='$search1' AND tags2.k = '$search2'and tags.id = tags2.id and tags2.id = w.idORDER BY `w`.`id`desc limit 24

问题是 EXPLAIN 函数显示“Using where; Using temporary; Using filesort”我也相信还有另一种方法可以优化我的类型搜索系统。实际上所有数据都在“w”表中，我只是不知道如何正确地搜索它。如果有任何帮助，我将不胜感激。

编辑:关于此的更多信息

表 'tags' 1505425 条记录 InnoDB = 65,9 MiB

表 'w' 398900 个记录，InnoDB = 140,3 MiB

在此(更新)查询之后:

select `w`.`id` AS `id`,`w`.`title` AS `title`,`w`.`k1` AS `k1`,`w`.`k2` AS `k2`,`w`.`k3` AS `k3`,`w`.`k4` AS `k4`,`w`.`k5` AS `k5`,`w`.`k6` AS `k6`,`w`.`k7` AS `k7`,`w`.`k8` AS `k8`,`w`.`w` AS `w`,`w`.`h` AS `h`,`w`.`s` AS `s`,`w`.`c1` AS `c1`,`w`.`c2` AS `c2`,`w`.`c3` AS `c3`,`w`.`c4` AS `c4`,`w`.`c5` AS `c5`,`w`.`c6` AS `c6`,`w`.`m` AS `m`,`w`.`f` AS `f` from `tags` join `tags` `tags2` join `w` where ((`tags`.`k` = '$search1') and (`tags2`.`id` = `tags`.`id`) and (`w`.`id` = `tags`.`id`) and (`tags2`.`k` = '$search2')) order by `tags`.`i` desc limit 0,24;

mysql仍然显示:

+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+| id | select_type | table      | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                       |+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+|  1 | SIMPLE      | tags       | ref    | k,id          | k       | 98      | const       |  902 |   100.00 | Using where; Using filesort ||  1 | SIMPLE      | tags2      | ref    | k,id          | id      | 4       | db.tags.id  |    4 |   100.00 | Using where                 ||  1 | SIMPLE      | w          | eq_ref | PRIMARY       | PRIMARY | 4       | db.tags2.id |    1 |   100.00 | Using where                 |+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+3 rows in set, 1 warning (0.00 sec)

一个警告，仍然一切都保持不变，有没有更好的性能解决方案？目前它工作正常，但我认为以后一切都会越来越慢。

Answer 1

您正在混合存储引擎； mySQL 很难跨存储引擎连接表。

之前:

mysql> explain SELECT w.id,w.title,w.k1,w.k2,w.k3,w.k4,w.k5,w.k6,w.k7,w.w,w.h,w.s,w.c1,w.c2,w.c3,w.c4,w.c5,w.c6,w.m,w.f FROM tags,tags as tags2,w WHERE tags.k ='$search1' AND tags2.k = 'search2' and tags.id = tags2.id and tags2.id = w.id ORDER BY `w`.`id`desc limit 24;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | tags  | ref    | k             | k       | 98      | const       |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | tags2 | ref    | k             | k       | 98      | const       |    1 | Using where                                  |
|  1 | SIMPLE      | w     | eq_ref | PRIMARY       | PRIMARY | 4       | tmp.tags.id |    1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------+

将 w 更改为 MyISAM:

mysql> alter table w engine MyISAM;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

之后:

mysql> explain SELECT w.id,w.title,w.k1,w.k2,w.k3,w.k4,w.k5,w.k6,w.k7,w.w,w.h,w.s,w.c1,w.c2,w.c3,w.c4,w.c5,w.c6,w.m,w.f FROM tags,tags as tags2,w WHERE tags.k ='$search1' AND tags2.k = 'search2' and tags.id = tags2.id and tags2.id = w.id ORDER BY `w`.`id`desc limit 24;
+----+-------------+-------+--------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+--------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | w     | system | PRIMARY       | NULL | NULL    | NULL  |    1 |             |
|  1 | SIMPLE      | tags  | ref    | k             | k    | 98      | const |    1 | Using where |
|  1 | SIMPLE      | tags2 | ref    | k             | k    | 98      | const |    1 | Using where |
+----+-------------+-------+--------+---------------+------+---------+-------+------+-------------+
3 rows in set (0.00 sec)