mysql 语句优化问题

Question

CREATE 
    TABLE date    
    (     
        year varchar(4) NOT NULL, 
        month varchar(2) NOT NULL, 
        day varchar(2) NOT NULL,
        info varchar(20),
        INDEX date1 USING BTREE (year, month, day)    ) 
    ENGINE= InnoDB DEFAULT CHARSET= utf8;insert into date values('2009','01','01','xxx');
insert into date values('2009','02','01','xxx');
insert into date values('2009','03','01','xxx');
insert into date values('2009','04','01','xxx');
insert into date values('2009','05','01','xxx');
insert into date values('2009','06','01','xxx');
insert into date values('2009','07','01','xxx');
insert into date values('2009','08','01','xxx');
=============================================================root:aaa>show index from date\G
*************************** 1. row ***************************
       Table: date
  Non_unique: 1
    Key_name: date1
Seq_in_index: 1
 Column_name: year
   Collation: A
 Cardinality: 8
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: date
  Non_unique: 1
    Key_name: date1
Seq_in_index: 2
 Column_name: month
   Collation: A
 Cardinality: 8
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: date
  Non_unique: 1
    Key_name: date1
Seq_in_index: 3
 Column_name: day
   Collation: A
 Cardinality: 8
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
3 rows in set (0.00 sec)================================================================================现在我想查2009-02-01 到2009-06-01 里面的info信息.
 explain select year,month,day,info from date where 
concat(year,concat('-',concat(month,concat('-',day)))) >='2009-02-01' and  concat(year,concat('-',concat(month,concat('-',day))))<='2009-06-01';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | date  | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+各位大哥注意看了, type= ALL   如果这里的数据有几千万行,将很影响性能.所以我想问, 在不改变表结构的情况下 ,按此条件查询,怎么样才能用到这个联合索引.(注 :个人感觉 type=index 和 type=all 区别不大，扫描的行数都一样，所以最好改成eq_ref 最好了)
        
                                                               -----忘各位大哥不吝赐教.

Answer 1

mysql> explain select * from date where year='2009' and month between '02' and '06' and day between '01' and '31';
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | date  | ref  | date1         | date1 | 14      | const |    1 | Using where | 
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Answer 2

  楼上的大哥，多谢了，不过，我现在还想改下，insert into date values('2010','01','01','xxx');
insert into date values('2011','02','01','xxx');
insert into date values('2012','03','01','xxx');
insert into date values('2013','04','01','xxx');我现在插入了这些数据。。我想要2009-04-01 到 2012-03-01 的数据呢？
类型也是type=req 。。请问怎么改。

Answer 3

额  1楼的大哥，貌似不行了。你给的SQL语句，在我上面执行的结果是。root:aaa>explain  select * from date where year='2009' and month between '02' and '06' and day between '01' and '31';*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: date
         type: ALL
possible_keys: date1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.02 sec) 请问，你是使用我的表结构吗？

Answer 4

mysql> explain
    -> select * from date where year ='2009' and month>'3'
    -> union all
    -> select * from date where year ='2010'
    -> union all
    -> select * from date where year ='2011'
    -> union all
    -> select * from date where year ='2012' and month<'3';
+----+--------------+----------------+-------+---------------+-------+---------+-------+------+-------------+
| id | select_type  | table          | type  | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+--------------+----------------+-------+---------------+-------+---------+-------+------+-------------+
|  1 | PRIMARY      | date           | range | date1         | date1 | 22      | NULL  |    1 | Using where |
|  2 | UNION        | date           | ref   | date1         | date1 | 14      | const |    1 | Using where |
|  3 | UNION        | date           | ref   | date1         | date1 | 14      | const |    1 | Using where |
|  4 | UNION        | date           | range | date1         | date1 | 22      | NULL  |    1 | Using where |
| NULL | UNION RESULT | <union1,2,3,4> | ALL   | NULL          | NULL  | NULL    | NULL  | NULL |             |
+----+--------------+----------------+-------+---------------+-------+---------+-------+------+-------------+
5 rows in set (0.00 sec)

Answer 5

1：不明白你为什么要把数字定义成VARHCHAR类型？
2：是日期形式的字段为什么不直接定义成DATETIME类型的，这样你按范围查询就方便很多
3：尽量使每个查询都能用到索引，这样效率会提高很多。

Answer 6

刚才的查询是错误的。忽视了类型是VARCHAR类型的，这样比较的时候直接<是错误的。
mysql>  select * from date where year ='2009' and 0+ month>3
    ->  union all
    ->  select * from date where year ='2010'
    ->  union all
    ->  select * from date where year ='2011'
    ->  union all
    ->  select * from date where year ='2012' and 0+month<=3;
+------+-------+-----+------+
| year | month | day | info |
+------+-------+-----+------+
| 2009 | 04    | 01  | xxx  |
| 2009 | 05    | 01  | xxx  |
| 2009 | 06    | 01  | xxx  |
| 2009 | 07    | 01  | xxx  |
| 2009 | 08    | 01  | xxx  |
| 2010 | 01    | 01  | xxx  |
| 2011 | 02    | 01  | xxx  |
| 2012 | 03    | 01  | xxx  |
+------+-------+-----+------+
8 rows in set (0.00 sec)mysql>