是否有任何方法来优化运行缓慢的查询？

Question

由于以下使用索引的query.this查询，我们经常收到缓慢的日志警告。

我试图优化这个查询，但是没有运气支持我优化慢速查询，以及如何提高性能。

我要在最好的时候输出。

我的疑问是

select t.row_mod, 
       t.widget_info_id, 
       t.widget_name, 
       t.text1, 
       m.pretty_name, 
       w.website_id,
       w.fol3_website_id 
from   fbw_info_text t, 
       fbw_info_meta m, 
       fol3_website w 
where  t.widget_info_id = m.widget_info_id 
       and    t.widget_name = m.widget_name 
       and (
              m.widget_type = 'checkbox' 
              or t.widget_name in (
                  'ccapi_approved_transactions_msg',  
                  'ccapi_declined_transactions_msg', 
                  'ccapi_failed_transactions_msg'
              )
           ) 
       and t.website_id = w.website_id 
       and (
              (t.row_mod >=  '2013-10-24 20:57:32' 
                and t.row_mod <= '2013-10-24 20:59:32') 
           or 
              (m.row_mod >= '2013-10-24 20:57:32' 
                and m.row_mod <= '2013-10-24 20:59:32')
           )  
       order by widget_info_id, widget_name;

解释计划：

+----+-------------+-------+-------+-------------------------------------+----------+---------+----------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type  | possible_keys                       | key      | key_len | ref                                    | rows  | Extra                                        |
+----+-------------+-------+-------+-------------------------------------+----------+---------+----------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | w     | index | idx_1055,idx_1786                   | idx_1522 | 4       | NULL                                   | 25953 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | t     | ref   | idx_4639,idx_1055,idx_2718,idx_3388 | idx_1055 | 53      | cms.w.website_id                       |    12 | Using where                                  |
|  1 | SIMPLE      | m     | ref   | idx_2718                            | idx_2718 | 105     | cms.t.widget_name,cms.t.widget_info_id |     1 | Using where                                  |
+----+-------------+-------+-------+-------------------------------------+----------+---------+----------------------------------------+-------+----------------------------------------------+
3 rows in set (0.01 sec)

表结构

mysql> show create table fbw_info_text\G
*************************** 1. row ***************************
       Table: fbw_info_text
Create Table: CREATE TABLE `fbw_info_text` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `widget_info_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `widget_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `website_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `text1` longtext COLLATE latin1_bin,
  `text2` longtext COLLATE latin1_bin,
  `submitted_user_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `use_default` varchar(1) COLLATE latin1_bin DEFAULT NULL,
  `active_flag` varchar(1) COLLATE latin1_bin DEFAULT NULL,
  `status` varchar(1) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_4639` (`website_id`,`widget_info_id`,`widget_name`,`status`),
  KEY `idx_1055` (`website_id`),
  KEY `idx_2718` (`widget_info_id`,`widget_name`),
  KEY `idx_3388` (`widget_info_id`,`website_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.01 sec)

mysql> show create table fbw_info_meta\G
*************************** 1. row ***************************
       Table: fbw_info_meta
Create Table: CREATE TABLE `fbw_info_meta` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `widget_name` varchar(50) COLLATE latin1_bin NOT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `widget_info_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `widget_type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `needs_approval` varchar(1) COLLATE latin1_bin DEFAULT NULL,
  `widget_value` longtext COLLATE latin1_bin,
  `widget_order` int(11) DEFAULT NULL,
  `widget_attributes` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `capability_name` varchar(30) COLLATE latin1_bin DEFAULT NULL,
  `design_type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `on_approval_hook` longtext COLLATE latin1_bin,
  `display_hook` longtext COLLATE latin1_bin,
  `status` varchar(1) COLLATE latin1_bin DEFAULT NULL,
  `needs_archive` varchar(1) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_2718` (`widget_name`,`widget_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> show create table fol3_website\G
*************************** 1. row ***************************
       Table: fol3_website
Create Table: CREATE TABLE `fol3_website` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `fol3_website_id` int(11) NOT NULL,
  `website_id` varchar(50) COLLATE latin1_bin NOT NULL,
  `activate_default_host` int(11) DEFAULT '0',
  `active` int(11) NOT NULL DEFAULT '1',
  `advance_notice_days` int(11) DEFAULT '0',
  `catrequest` int(11) DEFAULT '0',
  `chain` int(11) DEFAULT NULL,
  `color_scheme` varchar(30) COLLATE latin1_bin DEFAULT NULL,
  `conversion_rate` int(11) DEFAULT '0',
  `comment` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `defunct_2` varchar(5) COLLATE latin1_bin DEFAULT NULL,
  `default_website` varchar(50) COLLATE latin1_bin NOT NULL,
  `designtype` varchar(25) COLLATE latin1_bin DEFAULT NULL,
  `key_contact_name` varchar(55) COLLATE latin1_bin DEFAULT NULL,
  `key_contact_phone` varchar(60) COLLATE latin1_bin DEFAULT NULL,
  `language_id` varchar(5) COLLATE latin1_bin DEFAULT NULL,
  `defunct_0` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `mercury` int(11) NOT NULL DEFAULT '1',
  `mgnt_defaults_last_applied` datetime DEFAULT NULL,
  `rank` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `return_url` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `defunct_1` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `store_id` varchar(15) COLLATE latin1_bin NOT NULL,
  `defunct_3` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `display_website_url` varchar(25) COLLATE latin1_bin DEFAULT NULL,
  `webloyalty` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `transmission_type` int(11) DEFAULT NULL,
  `segment` int(11) DEFAULT NULL,
  `site_id` varchar(25) COLLATE latin1_bin DEFAULT NULL,
  `canonical_status` int(1) DEFAULT '0',
  `canonical_domain` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `mobile_site_active` tinyint(1) DEFAULT '1',
  UNIQUE KEY `idx_1055` (`website_id`),
  UNIQUE KEY `idx_1522` (`fol3_website_id`),
  UNIQUE KEY `idx_1786` (`website_id`,`active`),
  KEY `idx_1867` (`transmission_type`),
  KEY `idx_857` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

Answer 1

您可以尝试显示MySQL的分析功能，以找出查询执行所占用的大部分时间，并根据结果进行优化：

set profiling=1;
Run your slow query (eg SELECT * FROM table WHERE name='xxx';
SHOW PROFILES;

http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

仔细分析解释扩展输出和优化查询，并再次分析查询以查看性能的提高。