mysql - 如何优化以下查询？

Question

SELECT r.*, u.username 
FROM `reservation` AS r JOIN 
     `users` AS u 
WHERE u.id = r.user_id 
AND   DATE(r.bx_date) >= DATE('2012-08-22') 
AND   DATE(r.bx_date) <= DATE('2012-08-22') 
AND   r.status='1' 
ORDER BY r.id desc

bx_date - 预订日期。

运行此查询需要超过 8 秒。我在预订表中有超过 500,000 条记录，在用户表中有 40,000 条记录。

我没有对我的数据库表进行任何优化。什么都没有。只是PK罢了。我该如何优化这个查询？有哪些选项可以提高该数据库的性能。

谢谢

餐 table 预订:

CREATE TABLE `reservation` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL COMMENT 'User ID',
 `tx_id` varchar(15) NOT NULL COMMENT 'Transaction ID',
 `tx_date` datetime NOT NULL COMMENT 'Transaction Date',
 `bx_date` date NOT NULL COMMENT 'Booking Date',
 `theater_id` int(10) unsigned NOT NULL COMMENT 'Theater ID',
 `movie_id` int(10) unsigned NOT NULL COMMENT 'Movie ID',
 `showtime_id` int(10) unsigned NOT NULL COMMENT 'Show Time ID',
 `category_id` int(10) unsigned NOT NULL COMMENT 'Category ID',
 `full_tickets` tinyint(2) unsigned NOT NULL COMMENT 'Number of Full Tickets',
 `half_tickets` tinyint(2) unsigned NOT NULL COMMENT 'Number of Half Tickets',
 `no_seats` tinyint(3) unsigned NOT NULL COMMENT 'No of Seats',
 `full_ticket_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Full Ticket Price',
 `half_ticket_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Half Ticket Price',
 `amount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Total Amount',
 `method` tinyint(1) unsigned NOT NULL COMMENT 'Payment Method 1=web 2=mobile 3=theater',
 `paymentgateway_id` int(10) unsigned NOT NULL COMMENT 'Payment Gateway ID',
 `payment_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 = Cash 1=Credit Card',
 `status` tinyint(1) unsigned NOT NULL COMMENT 'Status 0=provisional 1=booked 2=canceled 3=auto canceld',
 `comment` text,
 `reservation_type` tinyint(1) unsigned NOT NULL COMMENT 'Reservation Type 0=complimentary 1=advamce booking 2=Mobile Booking, 3 - Theater Offline Booking',
 `complimentary_type` tinyint(1) unsigned NOT NULL COMMENT '0=none 1=loyalty rewards 2=marketing 3=promotional 4=show blocking 5=vip',
 `description` mediumtext NOT NULL COMMENT 'Complimentary Description',
 `title` varchar(10) DEFAULT NULL COMMENT 'Title',
 `fname` varchar(255) DEFAULT NULL COMMENT 'First Name',
 `lname` varchar(255) DEFAULT NULL COMMENT 'Last Name',
 `gender` tinyint(1) unsigned DEFAULT NULL COMMENT 'Gender 0=male 1=female',
 `dob` date DEFAULT '0000-00-00' COMMENT 'Date of Birth',
 `nic` varchar(10) DEFAULT NULL COMMENT 'NIC no',
 `address` text COMMENT 'Address Line 1',
 `city` varchar(255) DEFAULT NULL COMMENT 'City',
 `district` varchar(50) DEFAULT NULL COMMENT 'District ',
 `country` varchar(255) DEFAULT NULL COMMENT 'Country',
 `mobile` varchar(15) DEFAULT NULL COMMENT 'Mobile No',
 `contact_phone` varchar(15) DEFAULT NULL COMMENT 'Fixed Land Phone Number',
 `email` varchar(255) DEFAULT NULL COMMENT 'Email Address',
 `timer` int(4) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=555706 DEFAULT CHARSET=utf8

表用户:

CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT,
 title varchar(100) NOT NULL,
 name varchar(255) NOT NULL DEFAULT '',
 last_name varchar(255) NOT NULL,
 username varchar(150) NOT NULL DEFAULT '',
 email varchar(100) NOT NULL DEFAULT '',
 password varchar(100) NOT NULL DEFAULT '',
 usertype varchar(25) NOT NULL DEFAULT '',
 block tinyint(4) NOT NULL DEFAULT '0',
 sendEmail tinyint(4) DEFAULT '0',
 registerDate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 lastvisitDate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 activation varchar(100) NOT NULL DEFAULT '',
 params text NOT NULL,
 gender varchar(6) NOT NULL,
 date_of_birth date NOT NULL,
 nic_no varchar(10) NOT NULL,
 address varchar(255) NOT NULL,
 city varchar(100) NOT NULL,
 district varchar(100) NOT NULL,
 mobile varchar(15) NOT NULL,
 subscribe_sms tinyint(1) NOT NULL,
 contact_phone varchar(15) NOT NULL,
 newsletter_subscribe tinyint(1) NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY username (username),
 KEY usertype (usertype),
 KEY idx_name (name),
 KEY idx_block (block),
 KEY email (email)) 
 ENGINE=InnoDB AUTO_INCREMENT=34265 DEFAULT CHARSET=utf8

Answer 1

首先我会像这样重写查询:

SELECT r.*, u.username 
FROM 
`reservation` AS r 
INNER JOIN `users` AS u ON u.id = r.user_id 
WHERE 
r.bx_date = '2012-08-22' /* or use BETWEEN when you really have a range */
AND   r.status='1' 
ORDER BY r.id desc

INNER JOIN 只是与您的语法不同，但作用相同。在我看来，这个更不容易出错，而且更直观，因为您不必在 WHERE 子句中区分什么是联接，什么不是。

由于 bx_date 列是 DATE 类型，因此您不需要函数 DATE() 再次从中生成日期。通常，在列上使用函数会阻止使用索引(如果该列上存在索引)。

为了让事情在开始时保持简单，只需将索引添加到经常用于连接或 WHERE 子句中的列。

在您的情况下，用于 JOIN 的列已经是主键，因此它们隐式地已经有一个索引。

你可以这样添加一个索引:

ALTER TABLE reservation ADD INDEX idx_reservation_bx_date (bx_date); /*if I remember correctly :) */

有关更多信息，请阅读 here .

您还可以使用复合索引，这是多列索引。

更新 感谢 ypercube 在评论中的建议:

ALTER TABLE reservation ADD INDEX idx_reservation_bx_date_status (bx_date, status); /*if I remember correctly :) */

然后，您可以通过在查询前放置一个 EXPLAIN 来检查是否使用了索引或使用了什么索引。当然，你必须衡量、衡量和衡量一个指数是否好。过度索引也不是一个好主意，因为 INSERTS 和 UPDATES 变得更慢。要查看索引是否固定您的查询，您必须确保它不在缓存中的某个地方，所以只需使用 SQL_NO_CACHE 测试执行您的查询，如下所示:

SELECT SQL_NO_CACHE r.*, u.username 
FROM 
`reservation` AS r 
INNER JOIN `users` AS u ON u.id = r.user_id 
WHERE 
r.bx_date = '2012-08-22' /* or use BETWEEN when you really have a range */
AND   r.status='1' 
ORDER BY r.id desc;

--

</UPDATE>

另一个规则是，当表有很多行时，索引具有很少不同值的列不是一个好主意。您在预订中的状态栏只有 4 个不同的值。在此列上添加索引会使索引变得无用。它甚至会变得更糟，因为 MySQL 可能会使用索引，然后必须进行额外的查找才能找到与索引位置对应的实际数据。

一个很好的读物是Use-the-index-Luke