慢查询性能

Question

我的数据库包含20,000行，下面的查询很慢。请帮我加快速度：

SELECT r.report_id,
       r.user_id,
       u.user_name,
       u.user_mail,
       d.department_name,
       r.report_comment,
       r.report_target_date,
       r.report_create_date,
       r.report_revised_date,
       r.report_root_id
FROM   report r
       JOIN user u
         ON u.user_id = r.user_id
       JOIN department d
         ON u.department_id = d.department_id
WHERE  ( u.user_name LIKE Concat('%', 'hieu', '%') )
       AND ( d.department_name LIKE Concat('%', 'BOM', '%') )
       AND r.report_id IN (SELECT r.report_id
                           FROM   report r
                           WHERE  r.user_id = 1
                           UNION ALL
                           SELECT r.report_id
                           FROM   report r
                                  JOIN user u
                                    ON u.user_id = r.user_id
                                  JOIN department d
                                    ON u.department_id = d.department_id
                                  JOIN authority a
                                    ON r.user_id = a.user_src_id
                           WHERE  a.user_dest_id = 1)
       AND ( r.report_id IN (SELECT r.report_id
                             FROM   report r
                             WHERE  r.report_comment LIKE
                                    Concat('%', 'this', '%'))
              OR r.report_id IN (SELECT rc.report_id
                                 FROM   report_content rc
                                 WHERE  rc.visited_company LIKE
                                        Concat('%', 'this', '%')
                                         OR rc.report_description LIKE
                                            Concat('%', 'this', '%')) )
ORDER  BY r.report_target_date DESC
LIMIT  0, 30 

创建表：

CREATE TABLE IF NOT EXISTS `report` (
  `report_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `report_comment` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `report_target_date` datetime NOT NULL,
  `report_create_date` datetime NOT NULL,
  `report_revised_date` datetime DEFAULT NULL,
  `report_revised_id` int(11) DEFAULT NULL,
  `report_root_id` int(11) DEFAULT NULL,
  `enable` int(2) NOT NULL DEFAULT '1',
  PRIMARY KEY (`report_id`),
  UNIQUE KEY `unique_report` (`report_id`),
  KEY `support_report` (`report_id`,`report_target_date`,`report_create_date`,`report_revised_date`,`report_revised_id`,`report_root_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18497 ;


CREATE TABLE IF NOT EXISTS `report_content` (
  `report_content_id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `timestamp` text COLLATE utf8_unicode_ci NOT NULL,
  `visited_company` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `report_description` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`report_content_id`),
  UNIQUE KEY `unique_report` (`report_content_id`,`report_id`),
  UNIQUE KEY `unique_report_content` (`report_content_id`,`report_id`),
  KEY `support_report_content` (`report_content_id`,`report_id`,`visited_company`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=278 ;

我的解释是：

id  | select |   table   |    type   |    possible_keys    |    key   |key_len|    ref  | rows  |  extra
1   |PRIMARY |     r     |    ALL    |         (NULL)      |  (NULL)  | (NULL)| (NULL)  | 20246 | using where/filesort
1   |PRIMARY |     p     |  eq_ref   | PRIMARY,unique_user |  NULL    | 4   |   NULL    |1      | using where
5   |SUBQUERY| report_content|  all  |          NULL       |    NULL  | 4   |   NULL    |160    |  Using where
6   |UNION   | report_content|  all  |          NULL       |    NULL  | 4   |   NULL    |160    |  Using where

现在，我将查询更改如下：

    SELECT r.report_id,
         r.user_id,
         u.user_name,
         u.user_mail,
         d.department_name,
         r.report_comment, 
         r.report_target_date,
         r.report_create_date,
         r.report_revised_date,
         r.report_root_id
FROM report AS r FORCE INDEX (unique_report) , `user` 
                AS u FORCE INDEX (unique_user) , `department` 
                AS d FORCE INDEX (unique_department)
WHERE (u.user_name LIKE CONCAT('%', 'hieu', '%')) 
        AND (d.department_name LIKE CONCAT('%', 'bom', '%')) 
        AND r.report_id IN (
SELECT r.report_id
    FROM report r
    WHERE r.user_id =1 UNION ALL
SELECT r.report_id
    FROM report r FORCE INDEX (unique_report)
    JOIN USER u FORCE INDEX (unique_user) ON u.user_id = r.user_id
    JOIN department d FORCE INDEX (unique_department) ON u.department_id = d.department_id
    JOIN authority a FORCE INDEX (unique_authority) ON r.user_id = a.user_src_id
    WHERE a.user_dest_id =1) AND (r.report_id IN (
SELECT  r.report_id
    FROM report r
    WHERE r.report_comment LIKE CONCAT('%', 'when', '%')) OR EXISTS (
SELECT rc.report_id
    FROM report_content rc
    WHERE rc.visited_company LIKE CONCAT('%', 'when', '%') 
    OR MATCH (`timestamp`,report_description) 
    AGAINST('+when+' WITH QUERY EXPANSION) AND r.report_id = rc.report_id))
ORDER BY r.report_target_date DESC
LIMIT 0, 30

比以前更快: 1.97s。

所以谁能帮我加快速度。

Answer 1

MySql并不是真正属于我的领域，但我相信基本原理是一样的.

1. 尽量少使用子查询
2. 将用户表和部门表连接到第一个子查询中是完全无用的。
3. 使用EXISTS子句代替IN (链接以作解释)
4. 设置索引
5. 检查执行计划