本帖最后由 yisildeai 于 2012-3-23 11:09 编辑
explain SELECT id, resaleAvgPrice, resaleAvgRate FROM stats_estate WHERE statsMonth=date_format(now(), '%Y-%m') AND id BETWEEN 0 AND 49999;
+----+-------------+--------------+------+------------------------+----------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+------------------------+----------------+---------+-------+--------+-------------+
| 1 | SIMPLE | stats_estate | ref | PRIMARY,statsMonth_idx | statsMonth_idx | 23 | const | 265696 | Using where |
+----+-------------+--------------+------+------------------------+----------------+---------+-------+--------+-------------+
用主键_索引
explain SELECT id, resaleAvgPrice, resaleAvgRate FROM stats_estate FORCE INDEX(PRIMARY) WHERE id BETWEEN 0 AND 49999 and statsMonth=date_format(now(), '%Y-%m');+----+-------------+--------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | stats_estate | range | PRIMARY | PRIMARY | 27 | NULL | 722579 | Using where |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+-------------+
索引分布情况:
------+---------+---------------+
| stats_estate | 0 | PRIMARY | 1 | id | A | 361289 |
| stats_estate | 0 | PRIMARY | 2 | statsMonth | A | 1445158 |
| stats_estate | 1 | statsMonth_idx | 1 | statsMonth | A | 18 |
为什么 这个 statsmonth_idx 这个索引 基数这么小.. 用索引扫描 扫描的行数 还少呢... 而 利用主键查询 却是接近于全表扫描呢.. 有点不太明白了.. engine 是innodb的... second_index 指向主键 所以索引的不重复字段才这么少吧..那么 为什么 主键扫描却这么慢呢... 应该如何建立索引..才使得此sql 提升速度 请各位哥 帮忙看下...谢谢...