MySQL慢查询

Question

SELECT
  items.item_id, items.category_id, items.title, items.description, items.quality,
  items.type, items.status, items.price, items.posted, items.modified,
  zip_code.state_prefix, zip_code.city, books.isbn13, books.isbn10, books.authors,
  books.publisher
FROM
(
  (
    items
    LEFT JOIN bookitems ON items.item_id = bookitems.item_id
  )
  LEFT JOIN books ON books.isbn13 = bookitems.isbn13
)
LEFT JOIN zip_code ON zip_code.zip_code = items.item_zip
WHERE items.rid = $rid`

我正在运行此查询，以获取用户物品及其位置列表。zip_code表中有超过40k条记录，这可能是问题所在。目前需要长达15秒才能返回约20个物品的列表！我该如何使此查询更加高效？

更新：以下是相关表的创建代码。对于格式问题，我表示抱歉！

CREATE TABLE `bookitems` (  
    `bookitem_id` int(10) unsigned NOT NULL auto_increment COMMENT 'BookItem ID',  
    `item_id` int(10) unsigned NOT NULL default '0' COMMENT 'Item ID',  
    `isbn13` varchar(13) NOT NULL default '' COMMENT 'Book ISBN13',  
    `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Date of Last Modification',  
    PRIMARY KEY  (`bookitem_id`),  
    UNIQUE KEY `item_id` (`item_id`),  
    KEY `fk_bookitems_isbn13` (`isbn13`),  
    CONSTRAINT `fk_bookitems_isbn13` FOREIGN KEY (`isbn13`) REFERENCES `books` (`isbn13`),  
    CONSTRAINT `fk_bookitems_item_id` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`)  
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=latin1;  

CREATE TABLE `books` (  
  `isbn13` varchar(13) NOT NULL default '' COMMENT 'Book ISBN13 (pk)',  
  `isbn10` varchar(10) NOT NULL default '' COMMENT 'Book ISBN10 (u)',  
  `title` text NOT NULL COMMENT 'Book Title',  
  `title_long` text NOT NULL,  
  `authors` text NOT NULL COMMENT 'Book Authors',  
  `publisher` text NOT NULL COMMENT 'ISBNdb publisher_text',  
  PRIMARY KEY  (`isbn13`),  
  UNIQUE KEY `isbn10` (`isbn10`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

CREATE TABLE `items` (  
  `item_id` int(10) unsigned NOT NULL auto_increment COMMENT 'Item ID',  
  `rid` int(10) unsigned NOT NULL default '0' COMMENT 'Item Owner User ID',  
  `category_id` int(10) unsigned NOT NULL default '0' COMMENT 'Item Category ID',  
  `title` tinytext NOT NULL COMMENT 'Item Title',  
  `description` text NOT NULL COMMENT 'Item Description',  
  `quality` enum('0','1','2','3','4','5') NOT NULL default '0' COMMENT 'Item Quality',  
  `type` enum('forsale','wanted','pending') NOT NULL default 'pending' COMMENT 'Item Status',  
  `price` int(6) unsigned NOT NULL default '0' COMMENT 'Price',  
  `posted` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Date of Listing',  
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Date of Last Modification',  
  `status` enum('sold','found','flagged','removed','active','expired') NOT NULL default 'active',  
  `item_zip` int(5) unsigned zerofill NOT NULL default '00000',  
  PRIMARY KEY  (`item_id`),  
  KEY `fk_items_rid` (`rid`),  
  KEY `fk_items_category_id` (`category_id`),  
  CONSTRAINT `fk_items_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`),  
  CONSTRAINT `fk_items_rid` FOREIGN KEY (`rid`) REFERENCES `users` (`rid`)  
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1;  

CREATE TABLE `users` (  
  `rid` int(10) unsigned NOT NULL auto_increment COMMENT 'User ID',  
  `fid` int(10) unsigned NOT NULL default '0' COMMENT 'Facebook User ID',  
  `role_id` int(10) unsigned NOT NULL default '4',  
  `zip` int(5) unsigned zerofill NOT NULL default '00000' COMMENT 'Zip Code',  
  `joined` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'INSERT Timestamp',  
  `email` varchar(255) NOT NULL default '',  
  `notes` varchar(255) NOT NULL default '',  
  PRIMARY KEY  (`rid`),  
  UNIQUE KEY `fid` (`fid`),  
  KEY `fk_users_role` (`role_id`),  
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`)  
) ENGINE=InnoDB AUTO_INCREMENT=1013 DEFAULT CHARSET=latin1;  

CREATE TABLE `zip_code` (  
  `id` int(11) unsigned NOT NULL auto_increment,  
  `zip_code` varchar(5) character set utf8 collate utf8_bin NOT NULL,  
  `city` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `county` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `state_name` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `state_prefix` varchar(2) character set utf8 collate utf8_bin default NULL,  
  `area_code` varchar(3) character set utf8 collate utf8_bin default NULL,  
  `time_zone` varchar(50) character set utf8 collate utf8_bin default NULL,  
  `lat` float NOT NULL,  
  `lon` float NOT NULL,  
  `search_string` varchar(52) NOT NULL default '',  
  PRIMARY KEY  (`id`),  
  KEY `zip_code` (`zip_code`)  
) ENGINE=MyISAM AUTO_INCREMENT=42625 DEFAULT CHARSET=utf8;

- andrhamm

---

你是否使用EXPLAIN查看了查询计划？能否为每个表发布SHOW CREATE TABLE的输出以及EXPLAIN SELECT ...的输出？ - Mark Byers

你使用在 JOIN 上的所有列都被索引了吗？ - bobince

如果您正在使用phpmyadmin，您可以单击“建议表结构”链接以获取一些提示。 - SeanJA

Answer 1

SELECT  items.item_id,
        items.category_id,
        items.title,
        items.description,
        items.quality,
        items.TYPE,
        items.status,
        items.price,
        items.posted,
        items.modified,
        zip_code.state_prefix,
        zip_code.city,
        books.isbn13,
        books.isbn10,
        books.authors,
        books.publisher
FROM    items
LEFT JOIN
        bookitems
ON      bookitems.item_id = items.item_id
LEFT JOIN
        books
ON      books.isbn13 = bookitems.isbn13
LEFT JOIN
        zip_code
ON      zip_code.zip_code = items.item_zip
WHERE   items.rid = $rid

创建以下索引：

items (rid)
bookitems (item_id)
books (isbn13)
zip_code (zip_code)

- Quassnoi

---

我认为WHERE需要先过滤items，而不是在检索所有块(...)之后再进行过滤。 - KM.

Answer 2

你的第一选择应该是优化你的数据库。 这40k行数据都是有用的吗？或者你可以将一些旧数据移动到包含归档数据的表中吗？你是否使用了适当的索引？清单还在继续...

- Jonas B

---

zip_code表中的40k条记录分别表示邮政编码、城市名称、州名、州缩写、县和纬度/经度坐标。实际上，这些数据都是必需的，无法删除。事实上，最新的数据库比我多年前在http://www.micahcarrick.com找到的旧数据库多约10k条记录。 - andrhamm

Answer 3

第一个问题是你有哪些索引。你在zip_code（邮政编码）上有索引吗？你的其他表有多大？显然有帮助的索引是，如我所说，zip_code（邮政编码），然后是items（rid），bookitems（item_id）和books（isbn13）。

我认为你几乎肯定需要在zip_code上建立索引，在针对该表的任何查询中都可能使用它。你可能还想在isbn13和bookitems（item_id）上建立索引。我不知道items（rid）应该是什么。在这个查询中建立索引会有所帮助，但可能并不总是有用。

除此之外，查询中没有明显的缺陷。

顺便说一句，“items left join bookitems ...”周围的括号是多余的。默认情况下，表从左到右连接。

- Jay

Answer 4

仅凭查询语句我不确定，但是

看起来你正在使用字符串数据类型连接行，这比使用整数（如ID），特别是如果没有索引，比较慢。

- Francisco Soto