distinct 和 group by的使用

公司同事有一个小项目的30w数据搜索用到like和排重查询比较慢，我对语句做了下优化。

mysql> desc shop;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| uid          | bigint(20) unsigned | NO   | MUL | 0       |                |
| sid          | bigint(20) unsigned | YES  | MUL | 0       |                |
| nick         | char(128)           | YES  | MUL |         |                |
| shop_type    | char(1)             | YES  |     | C       |                |
| shop_score   | int(10) unsigned    | YES  |     | 0       |                |
| shop_level   | int(10) unsigned    | YES  |     | 0       |                |
| category_id1 | int(10)             | YES  | MUL | 0       |                |
| category_id2 | int(10) unsigned    | NO   | MUL | 0       |                |
| shop_title   | varchar(128)        | YES  |     |         |                |
| shop_avatar  | varchar(255)        | YES  |     |         |                |
| sort         | int(6)              | NO   |     | 0       |                |
| locate       | varchar(45)         | NO   |     |         |                |
| disabled     | tinyint(1)          | NO   |     | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+
14 rows in set (0.01 sec)

mysql> explain select * from shop,(select distinct sid from shop where disabled = 0 and shop_title like '%迪卡侬%' limit 10) shopx where shop.sid=shopx.sid group by shop.sid;
+----+-------------+------------+------+---------------+---------+---------+-----------+--------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key     | key_len | ref       | rows   | Extra                                        |
+----+-------------+------------+------+---------------+---------+---------+-----------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL    | NULL    | NULL      |     10 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | shop       | ref  | INX_sid       | INX_sid | 9       | shopx.sid |      1 | NULL                                         |
|  2 | DERIVED     | shop       | ALL  | INX_sid       | NULL    | NULL    | NULL      | 269483 | Using where; Using temporary                 |
+----+-------------+------------+------+---------------+---------+---------+-----------+--------+----------------------------------------------+
3 rows in set (0.01 sec)

mysql> explain select  * from shop where disabled = 0 and shop_title like '%迪卡侬%' group by sid limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | shop  | index | INX_sid       | INX_sid | 9       | NULL |   10 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> select SQL_NO_CACHE distinct sid from shop where disabled = 0 and shop_title like '%迪卡侬%' limit 10;
9 rows in set (0.45 sec)

mysql> select SQL_NO_CACHE * from shop,(select distinct sid from shop where disabled = 0 and shop_title like '%迪卡侬%' limit 10) shopx where shop.sid=shopx.sid group by shop.sid;
...
9 rows in set (0.46 sec)

mysql> select SQL_NO_CACHE  * from shop where disabled = 0 and shop_title like '%迪卡侬%' group by sid limit 10;
...
9 rows in set (4.96 sec)