代码之家 › 专栏 › 技术社区 › Devner

MySQL查询在表联接中不使用索引

indexing join mysql

Devner · 技术社区 · 14 年前

我想列出一个特定作者的所有书籍销售信息。所以我有一个查询,它不使用索引来查找记录。

以下是我的表格结构:

-- 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-id”键,尽管我有。我该怎么做才能使书籍销售表使用索引?

谢谢您。

根据建议进行的编辑(但结果是它们仍然不使用索引):

//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 回复 | 直到 6 年前

Josh Davis 14 年前

正如您在解释中所看到的,“book_id”是作为一个可能的键列出的。如果MySQL不使用它,那只是优化器认为它不会加速查询。如果“Book_Sales”只有两行,并且100%的行共享相同的“Book_ID”,那么这是正确的。它被称为基数btw。 How to Avoid Table Scans (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 . 然而,这有点像黑客,因为它强制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

streetparade 14 年前

试试这个

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