我有一张表为 trade ,其中里面有2个字段进行索引 trade_cate varchar(20), posttime INI(11)
在Mysql 4.0中我执行 
select * from trade where trade_cate like '001%' order by posttime 
通过Explain是使用正常的 
possible_keys 为 trade_cate
key  为 trade_cate
Extra 为 Using where; Using filesort 但是我升级成Mysql 5.0和5.1发现 执行效率比原来低了几十倍,经过Explain发现,Mysql 5.x竟然用错了索引
possible_keys 为 trade_cate
key  为 posttime 
Extra 为 Using where但是我的SQL语句如果写成 select * from trade where trade_cate = '001' order by posttime ,索引使用正常的是不是MySQL 5.x的查询分析器对like处理有问题,我估计MySQL 4.1也有类似问题?
无奈我只能在Mysql 5.x里使用USE INDEX (trade_cate) 强制使用trade_cate索引,但是要修改很多相关的程序,工作量就大了,不知道各位有没有碰到类似问题

解决方案 »

  1. 这个是因为查询分析器自己觉得用posttime上的索引比trade_cate 上的索引来的快 ,所以就自己做了决定,没通知你。
      

  2. 但是实际情况是使用posttime比trade_cate慢啊,而且使用 select * from trade where trade_cate = '001' order by posttime 这个就正常啊
      

  3. 貌似 trade_cate  like “001%” 会使用索引trade_cate ,并且将记录限定在trade_cate >=001 和 trade_cate <=001之间,因此索引是trade_cate >=001而 trade_cate =‘001’可能由于trade_cate 的可取值比较少(从字面上看是类别),而posttime列的值可能都不同(从字面上看是提交时间,一般来说基本上不可能重复),如下所示:如果表中存在如下记录:
    001 记录1
    001 记录2
    001 记录3
    001 记录4
    002 记录5
    如果是用trade_cate作索引,可能有一个集合需要去选择,而posttime据我猜测应该是精确到秒,因此可能出现time('2009-01-01 18:00:01') 记录1
    time('2009-01-01 18:00:02') 记录2显然如果用posttime可能更合适点,只要一次就能找到对应的记录
    不知道理解对不对?
      

  4. 尝试用optimize table 优化下表,式一下。我想可能是数据采集出现错误。
    因为mysql在对比2个索引,确认该使用哪个索引的时候, 优先考虑的是哪个最快,或更有效
    假如trade_cate中含有001的记录 在整个表中比例很大,用posttime索引更快
    反之用trade_cate索引.所以在mysql中应该还存储一些类似如键值分布图的数据,来进行以上的优先度计算。如果这些数值失效的情况,可能会引起优化判断失误。所以尝试优化一下表
      

  5. Mysql 有没有限定查询使用什么索引呢?
      

  6. 5楼,use index 楼主有说明mysql根据表的信息预估了命中行数,索引使用情况等。并不是每次mysql都可以预估正确,建议使用楼上提到的optimize table 尝试下。
    or drop index ,create index 。try again
      

类似问题 »