MySQL Query不使用表连接中的索引 [英] MySQL Query does not use index in table join

问题描述

我正在尝试列出特定图书作者的所有book_sales信息。所以我有一个查询，并没有使用索引来查找记录。

I am trying to list all the book_sales information for a particular book author. So I have a query and it's not using Index to lookup records.

以下是我的表结构：

-- Table structure for table `books`

CREATE TABLE IF NOT EXISTS `books` (
  `book_id` int(11) NOT NULL auto_increment,
  `author_id` int(11) unsigned NOT NULL,
  `book_type_id` int(11) NOT NULL,
  `book_title` varchar(50) NOT NULL,
  `book_price` smallint(4) NOT NULL,
  `in_stock` char(1) NOT NULL,
  PRIMARY KEY  (`book_id`),
  KEY `book_type_id` (`book_type_id`),
  KEY `author_id` (`author_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `books`

INSERT INTO `books` (`book_id`, `author_id`, `book_type_id`, `book_title`, `book_price`, `in_stock`) VALUES
(1, 1, 1, 'My Book 1', 10, 'y'),
(2, 2, 1, 'My Book 2', 20, 'n'),
(3, 1, 2, 'My Book 3', 30, 'y'),
(4, 3, 3, 'My Book 4', 40, 'y'),
(5, 4, 2, 'My Book 5', 50, 'n'),
(6, 1, 1, 'My Book 6', 60, 'y'),
(7, 5, 3, 'My Book 7', 70, 'n'),
(8, 6, 2, 'My Book 8', 80, 'n'),
(9, 7, 1, 'My Book 9', 90, 'y'),
(10, 8, 3, 'My Book 10', 100, 'n');

-- Table structure for table `book_sales`

CREATE TABLE IF NOT EXISTS `book_sales` (
  `sale_id` int(11) NOT NULL auto_increment,
  `book_id` int(11) NOT NULL,
  `sale_amount` decimal(8,2) NOT NULL default '0.00',
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `price` smallint(8) NOT NULL,
  PRIMARY KEY  (`sale_id`),
  KEY `book_id` (`book_id`),
  KEY `price` (`price`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `book_sales`

INSERT INTO `book_sales` (`sale_id`, `book_id`, `sale_amount`, `time`, `price`) VALUES
(1, 1, '10.00', '2010-02-23 10:00:00', 20),
(2, 1, '20.00', '2010-02-24 11:00:00', 20);

我的查询：

SELECT sale_amount, price
FROM book_sales
INNER JOIN books ON book_sales.book_id = books.book_id
WHERE books.author_id =1

对上述内容的解析显示：

id   select_type    table        type        possible_keys      key      key_len    ref     rows      Extra
1   SIMPLE          books       ref     PRIMARY,author_id   author_id   4         const    3        Using index
1   SIMPLE          book_sales  ALL     book_id             NULL        NULL      NULL     2        Using where

显然，book_sales没有使用密钥'book_id'，虽然我有它。我该怎么做才能使book_sales表使用索引？

Clearly, book_sales is not using the key 'book_id', although I have it. What can I do to make the book_sales table use the Index?

谢谢。

根据建议完成编辑（但结果是他们仍然没有使用索引）：

//Does not use the index in book_sales table
EXPLAIN SELECT sale_amount, price
FROM books, book_sales
FORCE INDEX ( book_id ) 
WHERE book_sales.book_id = books.book_id
AND books.author_id =1

//Does not use the index in book_sales table
EXPLAIN SELECT sale_amount, price
FROM book_sales, books
WHERE books.author_id = 1
AND book_sales.book_id = books.book_id

如何强制book_sale表只有2行，以使用索引？谢谢。

How to force the book_sale table with just 2 rows, to use the index ? Thank you.

推荐答案

正如您在EXPLAIN中看到的那样，book_id被列为可能的密钥。如果MySQL不使用它，那只是优化器不认为它会加速查询。如果book_sales只有2行，并且这些行的100％共享相同的book_id，则为真。它被称为基数btw。 如何避免表扫描 （MySQL手册）

As you can see in the EXPLAIN, "book_id" is listed as a possible key. If MySQL doesn't use it, it's just that the optimizer doesn't think it would speed up the query. Which is true if "book_sales" only has 2 rows, and 100% of those rows share the same "book_id". It's called cardinality btw. How to Avoid Table Scans (MySQL Manual)

尝试用更多行填充它，你应该看到MySQL将使用索引进行连接。

Try filling it with more rows and you should see that MySQL will use an index for the join.

编辑：查询

SELECT sale_amount, price
FROM books, book_sales
FORCE INDEX ( book_id ) 
WHERE book_sales.book_id = books.book_id
AND books.author_id =1

...在这种情况下也不起作用，因为优化器仍然认识到读取索引是次优的并且切换表顺序以避免这样做。您可以使用 STRAIGHT_JOIN 强制执行表格顺序。然而，这有点像黑客攻击，因为它迫使MySQL以不是最好的方式执行查询。

...will not work either in that case because the optimizer still recognizes that reading the index is suboptimal and switches the table order to avoid doing so. You can force the table order by using STRAIGHT_JOIN. This is, however, a bit of a hack because it forces MySQL to execute the query in a way that is not the best.

      EXPLAIN
       SELECT sale_amount, price
         FROM books
STRAIGHT_JOIN book_sales FORCE INDEX (book_id) ON book_sales.book_id = books.book_id
        WHERE books.author_id = 1

这篇关于MySQL Query不使用表连接中的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！