MySQL优化order by查询注意字段类型与参数类型不同问题

创造于:2010年05月19日08:54 档案在:MySQL 浏览次数:205 作者:liuhui

在优化MySQL的order by查询过程中发现一个非常有意思的现象，如果查询条件的字段类型为字符型，但参数值是数字却没有使用单引号区分字符与数字，就会产生两种不同的查询效果，实验如下：

一，创建数据表test001，并创建索引keyidx

CREATE TABLE `test001` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`key1` varchar(30) NOT NULL DEFAULT '',
`key2` varchar(30) NOT NULL DEFAULT '',
`key3` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `keyidx` (`key1`,`key2`,`key3`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=gbk

二，使用PHP创建测试数据，代码如下

<?php
$db = mysql_connect("localhost","root","");
mysql_select_db("test");
set_time_limit(0);
$table = 'test001';
for($i=0;$i<100000;$i++){
$k1 = 'liuhui';
$k2 = 10;
$k3 = rand(1,100000);
mysql_query("insert into $table (key1,key2,key3) values ('".$k1."','".$k2."',".$k3.")",$db);
}
?>

三，使用explain分析一条查询语句，注意key2的值为数字型
mysql> explain select * from test001 where key1='liuhui' and key2 = 10 order by key3 desc limit 10\G

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: test001
         type: range
possible_keys: keyidx
          key: keyidx
      key_len: 62
          ref: NULL
         rows: 100009
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

结果：使用了文件排序，不正确。

四，使用explain分析一条查询语句，注意key2的值为字符型
mysql> explain select * from test001 where key1='liuhui' and key2 = '10' order by key3 desc limit 10\G
*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: test001
         type: range
possible_keys: keyidx
          key: keyidx
      key_len: 124
          ref: NULL
         rows: 100009
        Extra: Using where
1 row in set (0.00 sec)

结果：使用条件查询，正确。

测试结果发现，由key2的字段类型为varchar类型，但如果其查询的参数为数字型，就会产生文件排序的问题，这一点告诉我们，在使用数据库查询的过程中一定要注意字段类型与参数类型的一致性。