将AND baojia_list.id IN(*)条件删除后: SELECT sp_color.id, sp_color.color, baojia_list.price, baojia_list.mydate FROM sp_color, baojia_list WHERE sp_color.id = baojia_list.color_id AND sp_color.sp_id =7534;#Empty set (3.13 sec)EXPLAIN 结果: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE baojia_list ALL NULL NULL NULL NULL 589111 1 SIMPLE sp_color eq_ref PRIMARY PRIMARY 4 b2b2.baojia_list.color_id 1 Using where
我将SQL换成下面这样:SELECT max(baojia_list.id),sp_color.id,sp_color.color,baojia_list.price,baojia_list.mydate FROM sp_color,baojia_list WHERE sp_color.id=baojia_list.color_id AND sp_color.sp_id=7534 GROUP BY color_id#结果是3.46秒问题是同一个查询,为什么MSSQL也没有建立索引,速度却在0.1秒左右。
SELECT baojia_list.id,sp_color.color, baojia_list.price FROM sp_color, baojia_list WHERE sp_color.sp_id =462 AND price >0 AND baojia_list.color_id = sp_color.id AND baojia_list.id IN ( SELECT MAX( id ) FROM baojia_list GROUP BY color_id ); 这个SQL也是,查询起来几十秒,如果把那个in()的子查询去掉后,1秒就能查出来。 问题是我想得到不同的color_id在 baojia_list最大(最新的报价)那条。 可以通过修改SQL实现吗?
mysql> EXPLAIN SELECT baojia_list.id, sp_color.color, baojia_list.price -> FROM sp_color, baojia_list -> WHERE sp_color.sp_id =462 -> AND price >0 -> AND baojia_list.color_id = sp_color.id -> AND baojia_list.id -> IN ( -> -> SELECT MAX( id ) -> FROM baojia_list -> GROUP BY color_id -> ); +----+--------------------+-------------+--------+---------------+----------+---------+---------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------------+--------+---------------+----------+---------+---------------------------+--------+-------------+ | 1 | PRIMARY | baojia_list | ALL | color_id | NULL | NULL | NULL | 575085 | Using where | | 1 | PRIMARY | sp_color | eq_ref | PRIMARY | PRIMARY | 4 | b2b2.baojia_list.color_id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | baojia_list | index | NULL | color_id | 5 | NULL | 83 | Using index | +----+--------------------+-------------+--------+---------------+----------+---------+---------------------------+--------+-------------+ mysql> EXPLAIN SELECT baojia_list.id, sp_color.color, baojia_list.price -> FROM sp_color, baojia_list -> WHERE sp_color.sp_id =462 -> AND price >0 -> AND baojia_list.color_id = sp_color.id; +----+-------------+-------------+--------+---------------+---------+---------+---------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+---------------+---------+---------+---------------------------+--------+-------------+ | 1 | SIMPLE | baojia_list | ALL | color_id | NULL | NULL | NULL | 575085 | Using where | | 1 | SIMPLE | sp_color | eq_ref | PRIMARY | PRIMARY | 4 | b2b2.baojia_list.color_id | 1 | Using where | +----+-------------+-------------+--------+---------------+---------+---------+---------------------------+--------+-------------+ 2 rows in set (0.00 sec)
服务器是一台开发时用的普通PC,两个数据库都安装在上面。 现在的问题可能是:MSSQL转到MSYSQL后,SQL语句要注意哪些问题? 为了实现18楼那个查询结果,将sql改成如下后,查询时间在1秒多一点。 SELECT color,mid( prices,1,locate(',',prices)-1) from ( SELECT c.color, GROUP_CONCAT(b.price ORDER BY b.id DESC) AS prices FROM sp_color c, baojia_list b WHERE c.sp_id = 462 AND c.id = b.color_id GROUP BY c.id) as ss;
SELECT sp_color.id, sp_color.color, baojia_list.price, baojia_list.mydate
FROM sp_color, baojia_list
WHERE sp_color.id = baojia_list.color_id
AND sp_color.sp_id =7534;#Empty set (3.13 sec)EXPLAIN 结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE baojia_list ALL NULL NULL NULL NULL 589111
1 SIMPLE sp_color eq_ref PRIMARY PRIMARY 4 b2b2.baojia_list.color_id 1 Using where
FROM sp_color,baojia_list
WHERE
sp_color.id=baojia_list.color_id
AND sp_color.sp_id=7534 GROUP BY color_id#结果是3.46秒问题是同一个查询,为什么MSSQL也没有建立索引,速度却在0.1秒左右。
每种数据库都有它自己的优点缺点。
MSSQL之所以没有索引,也那么快,是它的独特之处,这种独特之处是要有其他开销(比如存储)作为补偿。所以不要太纠结这个问题。加索引就是了。
MSSQL快,你可以再深入一下,看看它是不是内部为它创建索引了.没有索引,只是表扫描,不可能那么快.
MSSQL呢,我用管理器确实没有看到其他索引。
FROM sp_color, baojia_list
WHERE sp_color.sp_id =462
AND price >0
AND baojia_list.color_id = sp_color.id
AND baojia_list.id
IN (
SELECT MAX( id )
FROM baojia_list
GROUP BY color_id
);
这个SQL也是,查询起来几十秒,如果把那个in()的子查询去掉后,1秒就能查出来。
问题是我想得到不同的color_id在 baojia_list最大(最新的报价)那条。
可以通过修改SQL实现吗?
-> FROM sp_color, baojia_list
-> WHERE sp_color.sp_id =462
-> AND price >0
-> AND baojia_list.color_id = sp_color.id
-> AND baojia_list.id
-> IN (
->
-> SELECT MAX( id )
-> FROM baojia_list
-> GROUP BY color_id
-> );
+----+--------------------+-------------+--------+---------------+----------+---------+---------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+--------+---------------+----------+---------+---------------------------+--------+-------------+
| 1 | PRIMARY | baojia_list | ALL | color_id | NULL | NULL | NULL | 575085 | Using where |
| 1 | PRIMARY | sp_color | eq_ref | PRIMARY | PRIMARY | 4 | b2b2.baojia_list.color_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | baojia_list | index | NULL | color_id | 5 | NULL | 83 | Using index |
+----+--------------------+-------------+--------+---------------+----------+---------+---------------------------+--------+-------------+
mysql> EXPLAIN SELECT baojia_list.id, sp_color.color, baojia_list.price
-> FROM sp_color, baojia_list
-> WHERE sp_color.sp_id =462
-> AND price >0
-> AND baojia_list.color_id = sp_color.id;
+----+-------------+-------------+--------+---------------+---------+---------+---------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------------+--------+-------------+
| 1 | SIMPLE | baojia_list | ALL | color_id | NULL | NULL | NULL | 575085 | Using where |
| 1 | SIMPLE | sp_color | eq_ref | PRIMARY | PRIMARY | 4 | b2b2.baojia_list.color_id | 1 | Using where |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------------+--------+-------------+
2 rows in set (0.00 sec)
在sql server运行速度是可以的 ,到mysql运行速度慢了下来,是否在mysql
数据中是否有是否使用索引,如没有使用索引添加索引就可以了。另外服务器的参数
是否调整、硬件环境这都是影响的因素要综合考虑才是的。
现在的问题可能是:MSSQL转到MSYSQL后,SQL语句要注意哪些问题?
为了实现18楼那个查询结果,将sql改成如下后,查询时间在1秒多一点。
SELECT color,mid( prices,1,locate(',',prices)-1) from (
SELECT c.color, GROUP_CONCAT(b.price ORDER BY b.id DESC) AS prices
FROM sp_color c, baojia_list b
WHERE c.sp_id = 462
AND c.id = b.color_id
GROUP BY c.id) as ss;