贴出你的 explain SELECT * FROM vlc_caomei_state WHERE city = '太原市' AND region='山西省' order by region ;show index from vlc_caomei_state ;估计是索引没创建正确。
用 and 连接的N个条件,跟顺利没有什么关系,优化器自己去调整。
mysql> explain SELECT * FROM vlc_caomei_state -> WHERE -> city = '太原市' -> AND region='山西省' -> order by region -> ; +----+-------------+------------------+------+---------------+---------+---------+-------------+-------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+---------------+---------+---------+-------------+-------+-----------------------+ | 1 | SIMPLE | vlc_caomei_state | ref | regions | regions | 306 | const,const | 60900 | Using index condition | +----+-------------+------------------+------+---------------+---------+---------+-------------+-------+-----------------------+ 1 row in set (0.02 sec)mysql> show index from vlc_caomei_state ; +------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----------- ----+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comm ent | +------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----------- ----+ | vlc_caomei_state | 0 | PRIMARY | 1 | id | A | 3918061 | NULL | NULL | | BTREE | | | | vlc_caomei_state | 1 | time | 1 | time | A | 3918061 | NULL | NULL | YES | BTREE | | | | vlc_caomei_state | 1 | version | 1 | version | A | 96 | NULL | NULL | YES | BTREE | | | | vlc_caomei_state | 1 | versions | 1 | version_1 | A | 1342 | NULL | NULL | YES | BTREE | | | | vlc_caomei_state | 1 | versions | 2 | version_2 | A | 1342 | NULL | NULL | YES | BTREE | | | | vlc_caomei_state | 1 | versions | 3 | version_3 | A | 1342 | NULL | NULL | YES | BTREE | | | | vlc_caomei_state | 1 | versions | 4 | version_4 | A | 4703 | NULL | NULL | YES | BTREE | | | | vlc_caomei_state | 1 | regions | 1 | region | A | 78 | NULL | NULL | YES | BTREE | | | | vlc_caomei_state | 1 | regions | 2 | city | A | 782 | NULL | NULL | YES | BTREE | | | +------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----------- ----+ 9 rows in set (0.00 sec)mysql>
#用时:28s SELECT * FROM vlc_caomei_state WHERE city = '济南市' order by region #用时:24s SELECT * FROM vlc_caomei_state WHERE city = '济南市' AND region='山东省' order by region 好像知道原因了。多数据量,不加limit的话两条语句时间差不多#用时:++++(太慢) SELECT * FROM vlc_caomei_state WHERE city = '济南市' order by region limit 0,10; #用时:0.1s SELECT * FROM vlc_caomei_state WHERE city = '济南市' AND region='山东省' order by region limit 0,10;
explain SELECT * FROM vlc_caomei_state
WHERE
city = '太原市'
AND region='山西省'
order by region ;show index from vlc_caomei_state ;估计是索引没创建正确。
mysql> explain SELECT * FROM vlc_caomei_state
-> WHERE
-> city = '太原市'
-> AND region='山西省'
-> order by region
-> ;
+----+-------------+------------------+------+---------------+---------+---------+-------------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+---------+---------+-------------+-------+-----------------------+
| 1 | SIMPLE | vlc_caomei_state | ref | regions | regions | 306 | const,const | 60900 | Using index condition |
+----+-------------+------------------+------+---------------+---------+---------+-------------+-------+-----------------------+
1 row in set (0.02 sec)mysql> show index from vlc_caomei_state ;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------
----+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comm
ent |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------
----+
| vlc_caomei_state | 0 | PRIMARY | 1 | id | A | 3918061 | NULL | NULL | | BTREE | |
|
| vlc_caomei_state | 1 | time | 1 | time | A | 3918061 | NULL | NULL | YES | BTREE | |
|
| vlc_caomei_state | 1 | version | 1 | version | A | 96 | NULL | NULL | YES | BTREE | |
|
| vlc_caomei_state | 1 | versions | 1 | version_1 | A | 1342 | NULL | NULL | YES | BTREE | |
|
| vlc_caomei_state | 1 | versions | 2 | version_2 | A | 1342 | NULL | NULL | YES | BTREE | |
|
| vlc_caomei_state | 1 | versions | 3 | version_3 | A | 1342 | NULL | NULL | YES | BTREE | |
|
| vlc_caomei_state | 1 | versions | 4 | version_4 | A | 4703 | NULL | NULL | YES | BTREE | |
|
| vlc_caomei_state | 1 | regions | 1 | region | A | 78 | NULL | NULL | YES | BTREE | |
|
| vlc_caomei_state | 1 | regions | 2 | city | A | 782 | NULL | NULL | YES | BTREE | |
|
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------
----+
9 rows in set (0.00 sec)mysql>
#用时:28s
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
order by region #用时:24s
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
AND
region='山东省'
order by region
好像知道原因了。多数据量,不加limit的话两条语句时间差不多#用时:++++(太慢)
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
order by region
limit 0,10;
#用时:0.1s
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
AND
region='山东省'
order by region
limit 0,10;