当WHERE中存在变量时，MySQL查询不使用索引

问题描述

我有这张桌子:

CREATE TABLE `ClientesHora_copy` (
`dia` varchar(6) default NULL,
`hora` varchar(2) default NULL,
`sit` varchar(17) default NULL,
`nodo` varchar(6) default NULL,
`clientes` decimal(41,0) default NULL,
`segundos` double default NULL,
`llamadas` decimal(41,0) default NULL,
`fecha` datetime default NULL,
KEY `nodo_fecha` (`nodo`,`fecha`),
KEY `nodo` (`nodo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

和此查询:

SET @sitio= 'ABA000';
SET @horaini='2013-02-12 15:18:00';
SET @horafin='2013-02-12 20:36:00';
    EXPLAIN SELECT nodo,sit,clientes,segundos,llamadas,fecha,hora,@horaini AS  horaini,@horafin AS horafin
        FROM `ClientesHora_copy`
        WHERE
        nodo =@sitio
        AND
        fecha BETWEEN DATE_SUB(DATE_FORMAT(@horaini, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)
            AND DATE_SUB(DATE_FORMAT(@horafin, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)

我在解释这个问题

id  select_type  table               type    possible_keys  key     key_len  ref        rows  Extra
------  -----------  -----------------  ------  -------------  ------  -------  ------  -------  -------------
 1  SIMPLE       ClientesHora_copy  ALL     (NULL)         (NULL)  (NULL)   (NULL)  2716460  Using where

但是，如果我不使用@sitio变量(而是使用@ horaini，@ horafin变量):

But if I dont use the @sitio variable (but use the @horaini, @horafin variables):

EXPLAIN SELECT nodo,sit,clientes,segundos,llamadas,fecha,hora,@horaini AS  horaini,@horafin AS horafin
        FROM `ClientesHora_copy`
        WHERE
        nodo ='ABA000'
        AND
        fecha BETWEEN DATE_SUB(DATE_FORMAT(@horaini, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)
            AND DATE_SUB(DATE_FORMAT(@horafin, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)

我明白了:

id  select_type      table                type    possible_keys    key         key_len  ref       rows  Extra
------  -----------  -----------------  ------  ---------------  ----------  -------  ------  ------  -------------
 1  SIMPLE            ClientesHora_copy  range   nodo_fecha,nodo  nodo_fecha  18       (NULL)       1  Using where

关于Mysql为什么不将索引与@sitio变量一起使用，却与@fechaini和@fechafin一起使用的任何想法吗?

Any idea of why Mysql doesnt use the index with the @sitio variable but it does with @fechaini and @fechafin?

谢谢！

推荐答案

最可能的解释是列nodo是字符数据类型，而character_set_connection与为该列指定的字符集不匹配.

The most likely explanation is that column nodo is character datatype, and character_set_connection does not match the character set specified for the column.

如果该列是用latin1字符集定义的，请尝试:

If the column is defined with latin1 characterset, try:

WHERE nodo = CONVERT(@sitio USING latin1)

---

作为示例，使用utf8，explain输出显示没有可用的索引:

---

As a demonstration, with utf8, explain output shows no index available:

EXPLAIN SELECT t.* FROM mytable t WHERE t.foo = CONVERT(@foo USING utf8)
                                                                  ^^^^
id select_type table type possible_keys key    key_len ref    rows Extra
-- ----------- ----- ---- ------------- ------ ------- ------ ---- -----------
 1 SIMPLE      t     ALL  (NULL)        (NULL) (NULL)  (NULL)    3 Using where

但是对于latin1，解释输出显示index可用(并且已使用):

But with latin1, explain output shows index is available (and is used):

EXPLAIN SELECT t.* FROM mytable t WHERE t.foo = CONVERT(@foo USING latin1)
                                                                  ^^^^^^
id select_type table type possible_keys key    key_len ref    rows Extra
-- ----------- ----- ---- ------------- ------ ------- ------ ---- -----------
 1  SIMPLE     t     ref  t_ix          t_ix   13      const     1 Using where

这篇关于当WHERE中存在变量时，MySQL查询不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！