MySQL通过索引可以提高查询速度

Question

MySQL通过索引可以提高查询速度

3

我有以下这个结构，并带有索引来帮助我们更快地检索：

CREATE TABLE IF NOT EXISTS `index_site` ( `id_building` char(32) NOT NULL, `id_client` char(32) NOT NULL, `id_broker` smallint(5) unsigned NOT NULL, `kind_client` char(1) NOT NULL, `city` smallint(6) unsigned NOT NULL, `lat` float(10,6) NOT NULL, `lng` float(10,6) NOT NULL, `zone` smallint(2) unsigned NOT NULL, `sector` smallint(4) unsigned NOT NULL, `subregion` smallint(6) unsigned NOT NULL, `country` char(2) NOT NULL, `habs` smallint(5) unsigned NOT NULL, `bath` smallint(5) unsigned NOT NULL, `persons` smallint(5) unsigned NOT  NULL, `include_elevator` enum('1','0') NOT NULL, `build_level` varchar(20) NOT NULL, `area` mediumint(8) unsigned NOT NULL, `area_um` enum('1','2','3','4','5') NOT NULL, `area_str` varchar(10) NOT NULL, `code` char(10) NOT NULL, `title` tinytext NOT NULL, `type_offer` varchar(50) NOT NULL, `offer_name` varchar(20) NOT NULL, `comments` text NOT NULL, `type_building` varchar(50) NOT NULL, `address` tinytext NOT NULL, `sector_name` tinytext NOT NULL, `city_name` varchar(50) NOT NULL, `subregion_name` varchar(50) NOT NULL, `area_terrain` varchar(10) NOT NULL, `area_um_terrain` tinyint(4) NOT NULL, `image` varchar(70) NOT NULL, `image_total` tinyint(2) unsigned NOT NULL, `build_status` tinyint(3) unsigned NOT NULL, `tags` text NOT NULL, `url` varchar(200) NOT NULL, `include_offer_value` enum('1','0') NOT NULL, `offer_value` varchar(15) NOT NULL, `offer_value_format` varchar(20) NOT NULL, `prc_comission` varchar(5) NOT NULL, `date_added` datetime NOT NULL, `date_updated` datetime NOT NULL, `date_expire` datetime NOT NULL, `date_suspended` date NOT NULL, `visits` int(11) NOT NULL, `kind_offer` tinyint(4) NOT NULL, `kind_building` tinyint(5) unsigned NOT NULL, `kind_building_type` tinyint(5) unsigned NOT NULL, `mark_bld` tinyint(3) unsigned NOT NULL, `mark_bld_color` char(7) NOT NULL, `status` tinyint(1) unsigned NOT NULL, `is_made` enum('0','1') NOT NULL, `is_project` enum('0','1') NOT NULL, `is_bm` enum('0','1') NOT NULL, `is_demo` enum('0','1') NOT NULL, `is_leading` enum('0','1') NOT NULL, `visible_in_metasearch` mediumtext NOT NULL, `visible_in_web` mediumtext NOT NULL, `seller_image` varchar(150) NOT NULL, `seller_name` varchar(50) NOT NULL,
KEY `id_broker` (`id_broker`), KEY `id_client` (`id_client`), KEY `kind_building` `kind_building`), KEY `city` (`city`), KEY `offer_value` (`offer_value`), KEY `is_bm` (`is_bm`), KEY `status` (`status`), KEY `sector` (`sector`), KEY `zone` (`zone`), KEY `area` (`area`), KEY `prc_comission` (`prc_comission`), KEY `is_made` (`is_made`), KEY `is_leading` (`is_leading`), KEY `id_building` (`id_building`), KEY `date_added` (`date_added`), KEY `code` (`code`), KEY `country` (`country`), KEY `habs` (`habs`), KEY `kind_offer` (`kind_offer`), FULLTEXT KEY `tags` (`tags`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

是的，它太大了！！！ :)

好的，这个主题涉及到我在查找结果时使用一些关键字来进行结构化操作；这是很正常的，我执行以下查询：

SELECT * FROM `index_site` WHERE kind_building='1' AND kind_offer='1' AND city='1'

这个查询只用了0.0179秒，非常棒，但是我在查询中添加了EXPLAIN:

EXPLAIN SELECT * FROM `index_site` WHERE kind_building='1' AND kind_offer='1' AND city='1'

我得到了以下结果：

+----+-------------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+-------------------------------------------------------------+
| 1 | SIMPLE | index_site | index_merge | kind_building,city,kind_offer | kind_offer,city,kind_building | 1,2,1 | NULL | 184 | Using intersect(kind_offer,city,kind_building); Using where |
+----+-------------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+-------------------------------------------------------------+

我使用正确的键，但在MySQL的Extra列中，当出现"Using where"时，他们说"你看起来有些不对".

我的问题是，如果我具有正确的查询和索引，为什么会出现"Using where"的问题，出了什么问题？

谢谢您的帮助！

- josotoru

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Quassnoi · Accepted Answer

从文档中得知：

如果“Extra”列还显示“Using where”，则表示正在使用索引来执行键值查找。

您正在从表中选择所有字段（*）。

由于合并交集中使用的索引未覆盖所有字段，因此需要在表本身中查找这些字段。

尝试运行此命令：

SELECT  kind_building, kind_offer, city
FROM    index_site
WHERE   kind_building = '1'
        AND kind_offer = '1'
        AND city = '1'

"

，Using where应该放在这里。

"