mysql问题集锦1

mysql> desc UserInfoHistory_bak;
+----------------------+-------------+------+-----+---------+----------------+
| Field                | Type        | Null | Key | Default | Extra          |
+----------------------+-------------+------+-----+---------+----------------+
| id                                | int(11)          | NO   | PRI | NULL    | auto_increment |
| user_id                       | int(11)          | NO   | MUL | NULL    |                |
| user_name                 | varchar(20)  | NO   |         | NULL    |                |
| user_last_login_date | datetime       | NO   | MUL | NULL    |                |
| user_password          | varchar(50)   | NO   | MUL | NULL    |                |
+----------------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> explain select date(user_last_login_date) as day ,count(user_id) as userNum from UserInfoHistory_bak where date(user_last_login_date) between '2011-07-20' and '2011-07-27' and user_password!='123456' group by date(user_last_login_date); 
+----+-------------+---------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+---------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | UserInfoHistory_bak | ALL  | NULL          | NULL | NULL    | NULL | 3227709 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> 

 

 

mysql> explain select id,user_last_login_date as day ,count(user_id) as userNum from UserInfoHistory_bak where date(user_last_login_date) between '2011-07-20 00:00:00' and '2011-07-27 00:00:00'    and user_password!='fetion2010' group by user_last_login_date; 
+----+-------------+---------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table               | type | possible_keys       | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+---------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | UserInfoHistory_bak | ALL  | index_user_password | NULL | NULL    | NULL | 3227709 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> explain select id,date(user_last_login_date) as day ,count(user_id) as userNum from UserInfoHistory_bak where user_last_login_date between '2011-07-20 00:00:00' and '2011-07-27 00:00:00'    and user_password!='fetion2010' group by user_last_login_date;       
+----+-------------+---------------------+-------+------------------------------------------------+----------------------------+---------+------+-------+-------------+
| id | select_type | table               | type  | possible_keys                                  | key                        | key_len | ref  | rows  | Extra       |
+----+-------------+---------------------+-------+------------------------------------------------+----------------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | UserInfoHistory_bak | range | index_user_last_login_date,index_user_password | index_user_last_login_date | 8       | NULL | 12773 | Using where |
+----+-------------+---------------------+-------+------------------------------------------------+----------------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql>

 

如果你在索引列使用函数调用或者更复杂的算术表达式，MySQL就不会使用索引，因为它必须计算出每个数据行的表达式值。

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/639708