我有一个日志条目表,以及大约 100 个可能的日志代码的描述表:
CREATE TABLE `log_entries` (
`logentry_id` int(11) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`partner_id` smallint(4) NOT NULL,
`log_code` smallint(4) NOT NULL,
PRIMARY KEY (`logentry_id`),
KEY `IX_code` (`log_code`),
KEY `IX_partner_code` (`partner_id`,`log_code`)
) ENGINE=MyISAM ;
CREATE TABLE IF NOT EXISTS `log_codes` (
`log_code` smallint(4) NOT NULL DEFAULT '0',
`log_desc` varchar(255) DEFAULT NULL,
`category_overview` tinyint(1) NOT NULL DEFAULT '0',
`category_error` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`log_code`),
KEY `IX_overview_code` (`category_overview`,`log_code`),
KEY `IX_error_code` (`category_error`,`log_code`)
) ENGINE=MyISAM ;
以下查询(匹配 20k 行中的 10k 行)在 0.0034 秒内执行(使用LIMIT 0,20
):
SELECT log_entries.date, log_codes.log_desc FROM log_entries
INNER JOIN log_codes ON log_codes.log_code = log_entries.log_code
WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1;
但是添加时ORDER BY log_entries.logentry_id DESC
,这当然是必要的,它减慢到0.6秒。可能是因为 log_codes 表上使用了“使用临时”?删除索引实际上使查询执行得更快,但仍然很慢(0.3 秒)。
EXPLAIN 不带 ORDER BY 的查询输出:
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+-------------+
| 1 | SIMPLE | log_codes | ref | PRIMARY,IX_overview_code | IX_overview_code | 1 | const | 56 | |
| 1 | SIMPLE | log_entries | ref | IX_code,IX_partner_code | IX_partner_code | 7 | const,log_codes.log_code | 25 | Using where |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+-------------+
并包括 ORDER BY:
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+
| 1 | SIMPLE | log_codes | ref | PRIMARY,IX_overview_code | IX_overview_code | 1 | const | 56 | Using temporary; Using filesort |
| 1 | SIMPLE | log_entries | ref | IX_code,IX_partner_code | IX_partner_code | 7 | const,log_codes.log_code | 25 | Using where |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+
关于如何让这个查询执行得更快有什么提示吗?我不明白为什么需要“使用临时”,因为应该在获取和排序适当的日志条目之前选择日志代码?
更新@Eugen Rieck:
SELECT log_entries.date, lc.log_desc FROM log_entries INNER JOIN (SELECT log_desc, log_code FROM log_codes WHERE category_overview = 1) AS lc ON lc.log_code = log_entries.log_code WHERE log_entries.partner_id = 1 ORDER BY log_entries.logentry_id;
+----+-------------+-------------+------+-------------------------+------------------+---------+-------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+-------------------------+------------------+---------+-------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |
| 1 | PRIMARY | log_entries | ref | IX_code,IX_partner_code | IX_partner_code | 7 | const,lc.log_code | 25 | Using where |
| 2 | DERIVED | log_codes | ref | IX_overview_code | IX_overview_code | 1 | | 56 | |
+----+-------------+-------------+------+-------------------------+------------------+---------+-------------------+------+---------------------------------+
更新@RolandoMySQLDBA:
使用我的原始索引,ORDER BY date DESC:
SELECT log_entries.date, log_codes.log_desc FROM (SELECT log_code,date FROM log_entries WHERE partner_id = 1) log_entries INNER JOIN (SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1) log_codes USING (log_code) ORDER BY log_entries.date DESC;
+----+-------------+-------------+------+------------------+------------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+------------------+------------------+---------+------+-------+---------------------------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 21937 | Using where; Using join buffer |
| 3 | DERIVED | log_codes | ref | IX_overview_code | IX_overview_code | 1 | | 56 | |
| 2 | DERIVED | log_entries | ALL | IX_partner_code | NULL | NULL | NULL | 22787 | Using where |
+----+-------------+-------------+------+------------------+------------------+---------+------+-------+---------------------------------+
使用您的索引,无需排序:
SELECT log_entries.date, log_codes.log_desc FROM (SELECT log_code,date FROM log_entries WHERE partner_id = 1) log_entries INNER JOIN (SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1) log_codes USING (log_code);
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+--------------------------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 57 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 21937 | Using where; Using join buffer |
| 3 | DERIVED | log_codes | index | IX_overview_code_desc | IX_overview_code_desc | 771 | NULL | 80 | Using where; Using index |
| 2 | DERIVED | log_entries | index | IX_partner_code_date | IX_partner_code_date | 15 | NULL | 22787 | Using where; Using index |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+--------------------------------+
使用您的索引,ORDER BY date DESC:
SELECT log_entries.date, log_codes.log_desc FROM (SELECT log_code,date FROM log_entries WHERE partner_id = 1) log_entries INNER JOIN (SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1) log_codes USING (log_code) ORDER BY log_entries.date DESC;
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+---------------------------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 21937 | Using where; Using join buffer |
| 3 | DERIVED | log_codes | index | IX_overview_code_desc | IX_overview_code_desc | 771 | NULL | 80 | Using where; Using index |
| 2 | DERIVED | log_entries | index | IX_partner_code_date | IX_partner_code_date | 15 | NULL | 22787 | Using where; Using index |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+---------------------------------+
更新@Joe Stefanelli:
SELECT log_entries.date, log_codes.log_desc FROM log_entries INNER JOIN log_codes ON log_codes.log_code = log_entries.log_code WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1 ORDER BY date DESC;
+----+-------------+-------------+------+--------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+--------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
| 1 | SIMPLE | log_codes | ALL | PRIMARY,IX_code_overview | NULL | NULL | NULL | 80 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | log_entries | ref | IX_code,IX_code_partner | IX_code_partner | 7 | log_codes.log_code,const | 25 | Using where |
+----+-------------+-------------+------+--------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+