MySQL查询在表连接中未使用索引

Question

MySQL查询在表连接中未使用索引

12

我尝试列出特定图书作者所有的书籍销售信息。所以我有一个查询，但它没有使用索引来查找记录。

以下是我的表结构：

-- 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

对于上面的问题，使用EXPLAIN显示：

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表使用这个索引呢？

谢谢。

根据建议进行了编辑（但结果仍然没有使用索引）：

//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表使用索引？谢谢。

- Devner

2个回答

-1

试试这个

SELECT sale_amount, price
FROM book_sales,books
LEFT JOIN books ON(book_sales.book_id = books.book_id)
WHERE books.author_id =1

- streetparade

谢谢您的回复。我尝试了您的代码。遇到了“#1066 - Not unique table/alias: 'books'”错误。所以我在FROM子句中删除了",books"。进行了一次explain，但它仍然没有使用索引。还有其他方法可以修复这个问题吗？ - Devner

Hy Devner，请遵循Josh Davis的答案。 - streetparade

我已经访问了他发布的链接。我看到一个名为“--max-seeks-for-key=1000”的选项。它说：“使用--max-seeks-for-key=1000选项启动mysqld，或使用SET max_seeks_for_key=1000告诉优化器假定没有键扫描导致超过1,000个键查找。”我不确定这实际上意味着什么。你能否请解释一下？谢谢。 - Devner

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

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

- Josh Davis · Accepted Answer

正如您在 EXPLAIN 中看到的那样，“book_id”被列为可能的关键字。如果 MySQL 没有使用它，那只是优化器认为它不会加速查询。如果“book_sales”只有 2 行数据，并且这些行中 100% 具有相同的“book_id”，那么这是正确的。顺便说一下，这被称为基数。请参阅《如何避免表扫描》（MySQL 手册）。尝试添加更多的行，您将看到 MySQL 将使用索引进行连接。

编辑：查询

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来强制表的顺序。然而，这有点像是一种hack，因为它强制MySQL以不是最佳的方式执行查询。

      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