查询某分类下所有子分类的文章..速度不稳定

Question

win 2003
Mysql 5.028现有需求:分类表[关键字段]
id INTEGER(20) NOT NULL AUTO_INCREMENT, 
  title VARCHAR(64) NOT NULL,//分类名
  pid INTEGER(20) default 0, //上级id
文章表
 id INTEGER(20) NOT NULL AUTO_INCREMENT,
   unitid INTEGER(20) NOT NULL,
   labelid INTEGER(20) NOT NULL,
   publishtime DATETIME,
   downloadtime DATETIME,
   createtime TIMESTAMP,
   ....
   其他属性字段.
   ....
   md5 VARCHAR(255),
   PRIMARY KEY(id),
   UNIQUE (md5,unitid));
   
    ENGINE=MyISAM DEFAULT CHARSET=utf8  md5用于标识一文章
unitid:分类id一篇文章可从属于多个分类.
存储方式===>该篇文章多次存储到分类下,unitid区别.
表索引
Table     Non_unique  Key_name              Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment
--------  ----------  --------------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
sarticle           0  PRIMARY                          1  id           A               240913    (NULL)  (NULL)          BTREE              
sarticle           0  md5                              1  md5          A               (NULL)    (NULL)  (NULL)  YES     BTREE              
sarticle           0  md5                              2  unitid       A               (NULL)    (NULL)  (NULL)          BTREE              
sarticle           1  sarticle_publishtime             1  publishtime  A                40152    (NULL)  (NULL)  YES     BTREE              
当点击一个分类时,需要将该分类下所有子分类的文章取出来.并排重.从log-slow-queries中取出几条来,时间很长:
# Query_time: 152  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SELECT sarticle.md5, sarticle.title, sarticle.id, sarticle.unitid, sarticle.labelid, sarticle.url, sarticle.content, sarticle.author, sarticle.source, sarticle.sitename, sarticle.siteid, sarticle.publishtime, sarticle.downloadtime, sarticle.ptype, sarticle.topicid, sarticle.organizationid, sarticle.uwords FROM sarticle WHERE sarticle.publishtime>='20091217100104' AND sarticle.unitid IN (629,384,427,488,503,360,530,487,601,634,387,500,623,602,454,486,519,501,624,388,417,380,603,502,416,600,539,389,622,386,510,559,548,341,512,549,604,537,362,455,458,513,485,581,621,363,365,514,483,364,339,511,459,429,593,632,576,453,518,524,489,562,531,633,575,430,517,452,532,383,428,431,605,635,606,382,361,580,620,563,381,640,435,506,499,434,479,533,498,328,410,329,470,481,507,577,358,401,359,618,331,338,641,357,554,337,642,484,550,553,527,578,534,332,526,411,619,335,334,614,457,478,615,570,558,333,542,456,557,579,480,336,437,385,415,482,572,436,377,424,643,547,323,468,251,352,374,326,528,376,571,477,348,612,422,476,330,545,350,588,392,421,450,400,589,544,529,543,327,451,555,420,496,474,423,467,644,546,347,398,426,414,613,346,556,396,598,469,425,616,449,551,535,520,412,564,402,405,368,536,391,397,413,490,408,473,522,599,495,356,639,617,407,492,523,552,610,584,406,432,471,597,493,611,322,521,494,409,596,565,592,403,367,595,491,390,585,540,351,497,295,475,404,433,638,541,465,626,342,569,637,573,345,443,591,630,525,636,631,516,399,466,343,574,444,440,366,369,438,464,594,587,625,439,344,442,582,445,472,505,628,340,379,441,325,627,250,504,373,324,515,566,607,590,446,463,586,561,508,372,583,609,538,509,460,567,418,378,461,608,370,349,395,462,371,560,394,568,419,448,393,447) AND sarticle.labelid=14 GROUP BY sarticle.md5 ORDER BY sarticle.publishtime DESC LIMIT 21;
# User@Host: monitor[monitor] @ localhost [127.0.0.1]
# Query_time: 104  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SELECT sarticle.md5, sarticle.title, sarticle.id, sarticle.unitid, sarticle.labelid, sarticle.url, sarticle.content, sarticle.author, sarticle.source, sarticle.sitename, sarticle.siteid, sarticle.publishtime, sarticle.downloadtime, sarticle.ptype, sarticle.topicid, sarticle.organizationid, sarticle.uwords FROM sarticle WHERE sarticle.publishtime>='20091221100152' AND sarticle.unitid IN (667,684,696,705,708,694,730,687,695,289,286,285,703,668,284,702,678,675,689,697,711,669,757,755,288,688,685,282,712,690,756,666,704,701,745,656,707,281,665,657,670,706,758,674,686,682,673,766,693,715,763,744,672,741,753,662,759,691,754,661,725,716,762,724,768,713,709,726,740,733,663,699,767,734,764,698,680,731,765,750,692,727,664,752,749,651,652,760,653,747,718,761,751,746,732,735,748,729,714,728,736,739,681,717,671,700,722,660,283,659,738,290,658,737,710,654,655,719,683,723,676,720,742,679,743,721) AND sarticle.labelid=13 GROUP BY sarticle.md5 ORDER BY sarticle.publishtime DESC LIMIT 21;
# User@Host: monitor[monitor] @ localhost [127.0.0.1]
# Query_time: 137  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SELECT sarticle.md5, sarticle.title, sarticle.id, sarticle.unitid, sarticle.labelid, sarticle.url, sarticle.content, sarticle.author, sarticle.source, sarticle.sitename, sarticle.siteid, sarticle.publishtime, sarticle.downloadtime, sarticle.ptype, sarticle.topicid, sarticle.organizationid, sarticle.uwords FROM sarticle WHERE sarticle.publishtime>='20091221100119' AND sarticle.unitid IN (629,384,427,488,503,360,530,487,601,634,387,500,623,602,454,486,519,501,624,388,417,380,603,502,416,600,539,389,622,386,510,559,548,341,512,549,604,537,362,455,458,513,485,581,621,363,365,514,483,364,339,511,459,429,593,632,576,453,518,524,489,562,531,633,575,430,517,452,532,383,428,431,605,635,606,382,361,580,620,563,381,640,435,506,499,434,479,533,498,328,410,329,470,481,507,577,358,401,359,618,331,338,641,357,554,337,642,484,550,553,527,578,534,332,526,411,619,335,334,614,457,478,615,570,558,333,542,456,557,579,480,336,437,385,415,482,572,436,377,424,643,547,323,468,251,352,374,326,528,376,571,477,348,612,422,476,330,545,350,588,392,421,450,400,589,544,529,543,327,451,555,420,496,474,423,467,644,546,347,398,426,414,613,346,556,396,598,469,425,616,449,551,535,520,412,564,402,405,368,536,391,397,413,490,408,473,522,599,495,356,639,617,407,492,523,552,610,584,406,432,471,597,493,611,322,521,494,409,596,565,592,403,367,595,491,390,585,540,351,497,295,475,404,433,638,541,465,626,342,569,637,573,345,443,591,630,525,636,631,516,399,466,343,574,444,440,366,369,438,464,594,587,625,439,344,442,582,445,472,505,628,340,379,441,325,627,250,504,373,324,515,566,607,590,446,463,586,561,508,372,583,609,538,509,460,567,418,378,461,608,370,349,395,462,371,560,394,568,419,448,393,447) AND sarticle.labelid=13 GROUP BY sarticle.md5 ORDER BY sarticle.publishtime DESC LIMIT 21;查询时间长时好时坏,show processlist看查询进程
Locked
Copying to tmp table
等.附上my.ini部份参数
max_connections=100
query_cache_size=100M
table_cache=256
tmp_table_size=256M
thread_concurrency = 2
max_allowed_packet=4M
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=205M
key_buffer_size=512M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 2Mlog-slow-queries =slow_mysql_12.txt
long_query_time = 5

Answer 1

analyze table sarticle;一下，然后贴出你的 show index from sarticle;explain SELECT md5, title, id, unitid, labelid, url, content, 
author, source, sitename, siteid, publishtime, downloadtime, 
ptype, topicid, organizationid, uwords 
FROM sarticle 
WHERE publishtime>='20091217100104' 
AND unitid IN (629,384,427,488,503,360,530,487,601,634,387,500,623,602,454,486,519,501,624,388,417,380,603,502,416,600,539,389,622,386,510,559,548,341,512,549,604,537,362,455,458,513,485,581,621,363,365,514,483,364,339,511,459,429,593,632,576,453,518,524,489,562,531,633,575,430,517,452,532,383,428,431,605,635,606,382,361,580,620,563,381,640,435,506,499,434,479,533,498,328,410,329,470,481,507,577,358,401,359,618,331,338,641,357,554,337,642,484,550,553,527,578,534,332,526,411,619,335,334,614,457,478,615,570,558,333,542,456,557,579,480,336,437,385,415,482,572,436,377,424,643,547,323,468,251,352,374,326,528,376,571,477,348,612,422,476,330,545,350,588,392,421,450,400,589,544,529,543,327,451,555,420,496,474,423,467,644,546,347,398,426,414,613,346,556,396,598,469,425,616,449,551,535,520,412,564,402,405,368,536,391,397,413,490,408,473,522,599,495,356,639,617,407,492,523,552,610,584,406,432,471,597,493,611,322,521,494,409,596,565,592,403,367,595,491,390,585,540,351,497,295,475,404,433,638,541,465,626,342,569,637,573,345,443,591,630,525,636,631,516,399,466,343,574,444,440,366,369,438,464,594,587,625,439,344,442,582,445,472,505,628,340,379,441,325,627,250,504,373,324,515,566,607,590,446,463,586,561,508,372,583,609,538,509,460,567,418,378,461,608,370,349,395,462,371,560,394,568,419,448,393,447) 
AND labelid=14 
GROUP BY md5 
ORDER BY publishtime DESC LIMIT 21;看一下结果是什么？

Answer 2

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 229 to server version: 5.0.24-community-nt-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.
Database changed
mysql> explain SELECT md5, title, id, unitid, labelid, url, content,
    -> author, source, sitename, siteid, publishtime, downloadtime,
    -> ptype, topicid, organizationid, uwords
    -> FROM sarticle
    -> WHERE publishtime>='20091217100104'
    -> AND unitid IN (629,384,427,488,503,360,530,487,601,634,387,500,623,602,45
4,486,519,501,624,388,417,380,603,502,416,600,539,389,622,386,510,559,548,341,51
2,549,604,537,362,455,458,513,485,581,621,363,365,514,483,364,339,511,459,429,59
3,632,576,453,518,524,489,562,531,633,575,430,517,452,532,383,428,431,605,635,60
6,382,361,580,620,563,381,640,435,506,499,434,479,533,498,328,410,329,470,481,50
7,577,358,401,359,618,331,338,641,357,554,337,642,484,550,553,527,578,534,332,52
6,411,619,335,334,614,457,478,615,570,558,333,542,456,557,579,480,336,437,385,41
5,482,572,436,377,424,643,547,323,468,251,352,374,326,528,376,571,477,348,612,42
2,476,330,545,350,588,392,421,450,400,589,544,529,543,327,451,555,420,496,474,42
3,467,644,546,347,398,426,414,613,346,556,396,598,469,425,616,449,551,535,520,41
2,564,402,405,368,536,391,397,413,490,408,473,522,599,495,356,639,617,407,492,52
3,552,610,584,406,432,471,597,493,611,322,521,494,409,596,565,592,403,367,595,49
1,390,585,540,351,497,295,475,404,433,638,541,465,626,342,569,637,573,345,443,59
1,630,525,636,631,516,399,466,343,574,444,440,366,369,438,464,594,587,625,439,34
4,442,582,445,472,505,628,340,379,441,325,627,250,504,373,324,515,566,607,590,44
6,463,586,561,508,372,583,609,538,509,460,567,418,378,461,608,370,349,395,462,37
1,560,394,568,419,448,393,447)
    -> AND labelid=14
    -> GROUP BY md5
    -> ORDER BY publishtime DESC LIMIT 21;
+----+-------------+----------+-------+----------------------+------------------
----+---------+------+--------+----------------------------------------------+
| id | select_type | table    | type  | possible_keys        | key
    | key_len | ref  | rows   | Extra                                        |
+----+-------------+----------+-------+----------------------+------------------
----+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | sarticle | range | sarticle_publishtime | sarticle_publisht
ime | 9       | NULL | 170702 | Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+----------------------+------------------
----+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)mysql> show index from sarticle;
+----------+------------+----------------------+--------------+-------------+---
--------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name | Co
llation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------------------+--------------+-------------+---
--------+-------------+----------+--------+------+------------+---------+
| sarticle |          0 | PRIMARY              |            1 | id          | A
        |      240913 |     NULL | NULL   |      | BTREE      |         |
| sarticle |          0 | md5                  |            1 | md5         | A
        |       60228 |     NULL | NULL   | YES  | BTREE      |         |
| sarticle |          0 | md5                  |            2 | unitid      | A
        |      240913 |     NULL | NULL   |      | BTREE      |         |
| sarticle |          1 | sarticle_publishtime |            1 | publishtime | A
        |       40152 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------------------+--------------+-------------+---
--------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)mysql> analyze table sarticle;
+------------------+---------+----------+-----------------------------+
| Table            | Op      | Msg_type | Msg_text                    |
+------------------+---------+----------+-----------------------------+
| monitor.sarticle | analyze | status   | Table is already up to date |
+------------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)mysql>

Answer 3

建一个（unitid，md5）的索引。

Answer 4

已经有了(md5,unitid)的唯一索引了..md5用来group by的.

Answer 5

如在sarticle表中增加一字段:unitpath,(分类路径)譬如
id 分类 pid 路径
1  A    0    0_
2  B    0    0_
3  C    1    0_1_
4  D    3    0_1_3_
5  E    3    0_1_3_
6  F    4    0_1_3_4_
若文章属于分类5,则在unitpath存入0_1_3_5_
若文章属于分类6,则在unitpath存入0_1_3_4_6_查询分类C下所有文章
执行like '0_1_3_%'如此操作就替换了In操作.like语句在此情况下效率比In快否?

Answer 6

（unitid，md5）
和
(md5,unitid)是不同的索引，现在需要的是（unitid，md5），建完之后试试再说。

Answer 7

mysql> show index from sarticle;
+----------+------------+----------------------+--------------+-------------+---
--------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name | Co
llation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------------------+--------------+-------------+---
--------+-------------+----------+--------+------+------------+---------+
| sarticle |          0 | PRIMARY              |            1 | id          | A
        |      240913 |     NULL | NULL   |      | BTREE      |         |
| sarticle |          0 | md5                  |            1 | md5         | A
        |       60228 |     NULL | NULL   | YES  | BTREE      |         |
| sarticle |          0 | md5                  |            2 | unitid      | A
        |      240913 |     NULL | NULL   |      | BTREE      |         |
| sarticle |          1 | sarticle_publishtime |            1 | publishtime | A
        |       40152 |     NULL | NULL   | YES  | BTREE      |         |
| sarticle |          1 | unitid               |            1 | unitid      | A
        |         534 |     NULL | NULL   |      | BTREE      |         |
| sarticle |          1 | unitid               |            2 | md5         | A
        |      240913 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------------------+--------------+-------------+---
--------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)mysql> explain SELECT md5, title, id, unitid, labelid, url, content,
    -> author, source, sitename, siteid, publishtime, downloadtime,
    -> ptype, topicid, organizationid, uwords
    -> FROM sarticle
    -> WHERE publishtime>='20091217100104'
    -> AND unitid IN (629,384,427,488,503,360,530,487,601,634,387,500,623,602,45
4,486,519,501,624,388,417,380,603,502,416,600,539,389,622,386,510,559,548,341,51
2,549,604,537,362,455,458,513,485,581,621,363,365,514,483,364,339,511,459,429,59
3,632,576,453,518,524,489,562,531,633,575,430,517,452,532,383,428,431,605,635,60
6,382,361,580,620,563,381,640,435,506,499,434,479,533,498,328,410,329,470,481,50
7,577,358,401,359,618,331,338,641,357,554,337,642,484,550,553,527,578,534,332,52
6,411,619,335,334,614,457,478,615,570,558,333,542,456,557,579,480,336,437,385,41
5,482,572,436,377,424,643,547,323,468,251,352,374,326,528,376,571,477,348,612,42
2,476,330,545,350,588,392,421,450,400,589,544,529,543,327,451,555,420,496,474,42
3,467,644,546,347,398,426,414,613,346,556,396,598,469,425,616,449,551,535,520,41
2,564,402,405,368,536,391,397,413,490,408,473,522,599,495,356,639,617,407,492,52
3,552,610,584,406,432,471,597,493,611,322,521,494,409,596,565,592,403,367,595,49
1,390,585,540,351,497,295,475,404,433,638,541,465,626,342,569,637,573,345,443,59
1,630,525,636,631,516,399,466,343,574,444,440,366,369,438,464,594,587,625,439,34
4,442,582,445,472,505,628,340,379,441,325,627,250,504,373,324,515,566,607,590,44
6,463,586,561,508,372,583,609,538,509,460,567,418,378,461,608,370,349,395,462,37
1,560,394,568,419,448,393,447)
    -> AND labelid=14
    -> GROUP BY md5
    -> ORDER BY publishtime DESC LIMIT 21;
+----+-------------+----------+-------+-----------------------------+--------+--
-------+------+--------+----------------------------------------------+
| id | select_type | table    | type  | possible_keys               | key    | k
ey_len | ref  | rows   | Extra                                        |
+----+-------------+----------+-------+-----------------------------+--------+--
-------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | sarticle | range | sarticle_publishtime,unitid | unitid | 4
       | NULL | 149441 | Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+-----------------------------+--------+--
-------+------+--------+----------------------------------------------+
1 row in set (0.09 sec)

Answer 8

| sarticle |      1 | unitid         |      1 | unitid    | A    |     534 |   NULL | NULL   |    | BTREE    |     |这个值也太小了。你的 labelid 在整个表中的分布如何？labelid有多少不同值？你现在WHERE 后有 publishtime> unitid IN labelid=， 需要先在这三种找出最佳的筛选方案。

Answer 9

labelid值很少,就20-40个之间..目前.

Answer 10

这样，也没什么好办法，建个 (labelid,publishtime) 索引，应该会好一点儿。

Answer 11

labelid重复率太高了..,建立索引的效果不大吧.值恒定..就27个....
但是文章数是一直在增长的.现在40多万的数据了....兄台看看#5的想法.用like替换.

Answer 12

不是快慢的问题。你的unitid只有534个不同值，而你的这个IN中有300多个值，这样MYSQL基本上起不到筛选的作用。你的这个GROUP比较花时间。建一个（labelid,publishtime）的索引，或者建一个　（labelid,,md5,publishtime）的索引。

Answer 13

嘿嘿.
先期脑瓜的理论有误.
先前参考的资料.
某列重复值过多,没有必要建立索引,效果不大.
如性别.0/1取值.
当时有人隐含的说了个情况如果在sex字段上建立索引,速度很快.当时没注意.哎呀呀.因:unitid取值不到600个,labelid取值27个.
故:没对这两列建索引.回头看了看explain. 仅用到了sarticle_publishtime索引列.哎!!!!!  大意了.
+----+-------------+----------+-------+----------------------+------------------ 
----+---------+------+--------+----------------------------------------------+ 
| id | select_type | table    | type  | possible_keys        | key 
    | key_len | ref  | rows  | Extra                                        | 
+----+-------------+----------+-------+----------------------+------------------ 
----+---------+------+--------+----------------------------------------------+ 
|  1 | SIMPLE      | sarticle | range | sarticle_publishtime | sarticle_publisht 
ime | 9      | NULL | 170702 | Using where; Using temporary; Using filesort | 
+----+-------------+----------+-------+----------------------+------------------ 
----+---------+------+--------+----------------------------------------------+ 
今天参考"组合索引"以及键位的顺序.
http://tech.it168.com/db/m/2007-04-18/200704182228406_1.shtml
这篇文章给的提示很大.明白了"组合索引"与"单列索引"的区别.应尽量把选择性低的列放在第一位==>个人理解成值重复多的列放到前面.
可使索引小些.
删除单列索引,增加了两组合索引
alter table sarticle add index label_u_p_m(labelid,unitid,publishtime,`md5`(24));
alter table sarticle add index unit_p_m(unitid,publishtime,`md5`(24)); 速度明显提高,<2s.不过我这样建的两个索引是不是重复了..鹅.因用到查询有区别.

Answer 14

你的索引中，加个 `md5` 的意义不大。explain 看一下具体用的是哪个索引。

Answer 15

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 257 to server version: 5.0.24-community-nt-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.
Database changed
mysql> explain SELECT md5, title, id, unitid, labelid, url, content,
    -> author, source, sitename, siteid, publishtime, downloadtime,
    -> ptype, topicid, organizationid, uwords
    -> FROM sarticle
    -> WHERE publishtime>='20091217100104'
    -> AND unitid IN (629,384,427,488,503,360,530,487,601,634,387,500,623,602,45
4,486,519,501,624,388,417,380,603,502,416,600,539,389,622,386,510,559,548,341,51
2,549,604,537,362,455,458,513,485,581,621,363,365,514,483,364,339,511,459,429,59
3,632,576,453,518,524,489,562,531,633,575,430,517,452,532,383,428,431,605,635,60
6,382,361,580,620,563,381,640,435,506,499,434,479,533,498,328,410,329,470,481,50
7,577,358,401,359,618,331,338,641,357,554,337,642,484,550,553,527,578,534,332,52
6,411,619,335,334,614,457,478,615,570,558,333,542,456,557,579,480,336,437,385,41
5,482,572,436,377,424,643,547,323,468,251,352,374,326,528,376,571,477,348,612,42
2,476,330,545,350,588,392,421,450,400,589,544,529,543,327,451,555,420,496,474,42
3,467,644,546,347,398,426,414,613,346,556,396,598,469,425,616,449,551,535,520,41
2,564,402,405,368,536,391,397,413,490,408,473,522,599,495,356,639,617,407,492,52
3,552,610,584,406,432,471,597,493,611,322,521,494,409,596,565,592,403,367,595,49
1,390,585,540,351,497,295,475,404,433,638,541,465,626,342,569,637,573,345,443,59
1,630,525,636,631,516,399,466,343,574,444,440,366,369,438,464,594,587,625,439,34
4,442,582,445,472,505,628,340,379,441,325,627,250,504,373,324,515,566,607,590,44
6,463,586,561,508,372,583,609,538,509,460,567,418,378,461,608,370,349,395,462,37
1,560,394,568,419,448,393,447)
    -> AND labelid=14
    -> GROUP BY md5
    -> ORDER BY publishtime DESC LIMIT 21;
+----+-------------+----------+-------+----------------------+-------------+----
-----+------+-------+----------------------------------------------+
| id | select_type | table    | type  | possible_keys        | key         | key
_len | ref  | rows  | Extra                                        |
+----+-------------+----------+-------+----------------------+-------------+----
-----+------+-------+----------------------------------------------+
|  1 | SIMPLE      | sarticle | range | label_u_p_m,unit_p_m | label_u_p_m | 17
     | NULL | 17239 | Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+----------------------+-------------+----
-----+------+-------+----------------------------------------------+
1 row in set (0.05 sec)
现在数据量68万.每次查询时间在2-10s内逛荡.