mysql和phonex_当MySql的Where值类型和数据字段类型不一致时会发生什么？

在多数时候我们都会对数据库建立索引，然后在SQL中尽可能的用上索引中使用的字段，但是我们这样做后，确实用到了这个索引了吗？事实是未必的，比如当SQL中Where条件值类型和字段定义的类型不一致时，这个索引就不会被用上。

定义如下表结构：

CREATE TABLE users (

phone varchar(21),

stat varchar(4),

KEY phonestat (stat, phone)

);

插入n多的数据后来做测试，首先查看插入的数据记录数。

SELECT count(*) FROM users;

+----------+

| count(*) |

+----------+

|     5925 |

+----------+

1 row in set (1.85 sec)

我们写两个SQL，一个

SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = 0;

+----------+

| count(*)  |

+----------+

|     3640   |

+----------+

1 row in set (0.05 sec)

再看另一个SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = '0';

+----------+

| count(*)  |

+----------+

|     3566   |

+----------+

1 row in set (0.00 sec)

为什么前面一种写法执行所需要时间较长并且所选择出的记录数多一些呢?

我们使用EXPLAIN看一下对索引的使用情况。

EXPLAIN SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = 0;

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+

| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+

|  1 | SIMPLE      | users | index | phonestat     | phonestat |      75 | NULL | 5925 | Using where; Using index |

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

看rows 5925，实际进行了一次全表检索，索引没有用上。

再看第二种

EXPLAIN SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = '0';

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+

| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra                    |

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+

|  1 | SIMPLE      | users | ref  | phonestat     | phonestat |      12 | const | 2968 | Using where; Using index |

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+

1 row in set (0.00 sec)

为什么会这样呢，原因就在于所使用的条件的字段值类型和数据库定义的字段类型不一致时，MySQL就会在内部做数据转化，它的处理行为就会和我们期望的有些不一样，在这里当我们使用整数来作为定义中的字符串字段做比较时，MySQL会自动将数据库中的记录转化为整数做比较，而且将空值转化为整数0做比较，所以选出来的记录数多一些。

由此得到当MySQL的Where值类型和数据库定义的字段类型不一致时会发生：

1. 建立的索引实际没有被用上，导致查询效率低下。

2. 由于数据库对一些值的默认转化，导致查询的结果偏离实际的期望。

分享到：

2011-04-08 12:23

浏览 2095

分类:数据库

评论