【已解决】求助： 加索引， 改善性能

ccchallenge

下面SQL

1. select spc_results.*, spc_word_mst.WORD_NAME
   
2.   from spc_results
   
3.   LEFT JOIN spc_word_mst
   
4.     ON spc_results.WORD_ID = spc_word_mst.WORD_ID
   
5. WHERE spc_results.KRNT_CD = 'apr'
   
6.    AND CONCAT(spc_results.SEARCH_DATE, ' ', spc_results.SEARCH_TIME) >=
   
7.        '2012/10/05 00:00:00'
   
8.    AND CONCAT(spc_results.SEARCH_DATE, ' ', spc_results.SEARCH_TIME) <
   
9.        '2012/10/05 24:00:00'
   
10. ORDER BY spc_results.SEARCH_DATE desc,
    
11.           spc_results.SEARCH_TIME desc,
    
12.           spc_results.BAITAI_ID   asc,
    
13.           RESULTS_ID              asc LIMIT 0,
    
14.           100

复制代码

spc_results表里数据几十万。
spc_word_mst数据几百条。

在不改变SQL的情况下，往哪个字段上加索引，会改善性能。
建表SQL

1. CREATE TABLE `spc_results` (
   
2. `RESULTS_ID`  bigint(20) NOT NULL AUTO_INCREMENT ,
   
3. `KRNT_CD`  char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
   
4. `WORD_ID`  bigint(20) NOT NULL ,
   
5. `BAITAI_ID`  char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
   
6. `SEARCH_DATE`  char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
   
7. `SEARCH_TIME`  char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
   
8. `SPONSOR_URL`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
   
9. `SPONSOR_TITLE`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
   
10. `SPONSOR_INFORMATION`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
11. `EVIDENCE_ADDRESS`  bigint(20) NULL DEFAULT NULL ,
    
12. `CREATED_AT`  datetime NULL DEFAULT NULL ,
    
13. `CREATED_USER`  char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
14. `CREATED_IP`  varchar(39) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
15. `UPDATED_AT`  datetime NULL DEFAULT NULL ,
    
16. `UPDATED_USER`  char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
17. `UPDATED_IP`  varchar(39) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
18. `AD_URL`  varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
19. PRIMARY KEY (`RESULTS_ID`),
    
20. INDEX `KRNT_CD` USING BTREE (`KRNT_CD`) ,
    
21. INDEX `dateindex` USING BTREE (`SEARCH_DATE`, `SEARCH_TIME`)
    
22. )
    
23. ENGINE=InnoDB
    
24. DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
    
25. AUTO_INCREMENT=1413495
    
26. ROW_FORMAT=COMPACT
    
27. ;
    
28. 
    
29. CREATE TABLE `spc_word_mst` (
    
30. `KRNT_CD`  char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    
31. `WORD_ID`  bigint(20) NOT NULL ,
    
32. `WORD_NAME`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
33. `WORD_FLG`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
34. `CREATED_AT`  datetime NULL DEFAULT NULL ,
    
35. `CREATED_USER`  char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
36. `CREATED_IP`  varchar(39) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
37. `UPDATED_AT`  datetime NULL DEFAULT NULL ,
    
38. `UPDATED_USER`  char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
39. `UPDATED_IP`  varchar(39) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
    
40. PRIMARY KEY (`KRNT_CD`, `WORD_ID`),
    
41. INDEX `WORD_ID` USING BTREE (`WORD_ID`)
    
42. )
    
43. ENGINE=InnoDB
    
44. DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
    
45. ROW_FORMAT=COMPACT
    
46. ;
    

复制代码

我在spc_results_jh表的KRNT_CD字段上加了索引，spc_word_mst表的WORD_ID上加了索引。虽然type都变成了ref，不过速度提升不明显。Status的Handler_read_next高的吓人，11万多,这个数字跟只加spc_results.KRNT_CD = 'apr'条件所查出来的数据个数一样。是不是索引根本没起作用。                                                                                                                                                                                                               
执行计划：                                                                                                               

id        table                     type      possibale_key        key                   ken_len        ref                                 rows        extra
1        spc_results            ref        KRNT_CD                KRNT_CD          18                const                             131418     Using where; Using filesort
1        spc_word_mst       ref        WORD_ID               WORD_ID         8                  spc_results.WORD_ID   1        

请高手指点。

cchallenge

对不住，更正一下。
Status的Handler_read_key高的吓人，11万多,这个数字跟只加spc_results.KRNT_CD = 'apr'条件所查出来的数据个数一样。是不是索引根本没起作用。                                                                                                                                                                                                               
Status的Handler_read_next高的吓人，23万多.

zhangshengdong

1，看执行计划
2，自己搭建一个测试环境，把数据导过来，自己加索引做测试
3，基本上的问题，都可以通过索引解决，如果索引解决不了。我认为就要从数据表的结构入手了。

cchallenge

多谢回复。执行计划看都行了。不过还是很花时间。20多万的数据，要8秒。

看来得从其他地方着手了

cenalulu

原因很简单：    AND CONCAT(spc_results.SEARCH_DATE, ' ', spc_results.SEARCH_TIME) >=
       '2012/10/05 00:00:00'
   AND CONCAT(spc_results.SEARCH_DATE, ' ', spc_results.SEARCH_TIME) <
       '2012/10/05 24:00:00'
这个条件导致了无法使用索引。

优化方案：
把条件改成
spc_results.SEARCH_DATE >='2012-10-05' and spc_results.SEARCH_DATE <= '2012-10-05'
and spc_results.SEARCH_TIME >='00:00:00'' and spc_results.SEARCH_TIME <= '24:00:00'
索引列的外面不要加任何函数，是SQL优化的最基本原则。

此外把 date 和 time合并成一个物理列是一个比较好的方案。

ccchallenge

多谢楼上。
两个方法都不错。
我试试！

cchallenge

改完sql不到1秒完事了。多谢cenalulu