mysql select 1

　看数据库连接池源码，发现连接池的参数validationQuery(SQL查询,用来验证从连接池取出的连接)设置的值为"SELECT 1"，之前很少用这种写法，于是

google一下，总结如下: 

以seven_user表为例，如下是建表语句：

CREATE TABLE `smily_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`version` int(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

select 1；
select 1 from smily_test；
select id from smily_test；
select version from smily_test；
select * from smily_test；

 以上sql语句执行的结果如下：

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 from smily_test;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
+---+
4 rows in set (0.00 sec)

mysql> select id from smily_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

mysql> select version from smily_test;
+---------+
| version |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
+---------+
4 rows in set (0.00 sec)

mysql> select * from smily_test;

+----+---------+
| id | version |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+---------+
4 rows in set (0.00 sec)

用show profiles查看这些sql语句的执行效率；

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00017700 | select 1 |
| 2 | 0.00013400 | select 1 from smily_test |
| 3 | 0.00011000 | select id from smily_test |
| 4 | 0.00010500 | select version from smily_test |
| 5 | 0.00011700 | select * from smily_test |
+----------+------------+--------------------------------+
5 rows in set (0.00 sec)

作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的.

看得出执行效率为：  select 1 > select 1 from table > select anycolum from table > select * from table 

再来看看 count(*) 和 count(1)的效率。

+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        7 | 0.00037200 | select count(1) from smily_test    |
|        8 | 0.00034500 | select count(*) from smily_test    |
+----------+------------+------------------------------------+

 

转载于:https://www.cnblogs.com/happySmily/p/5939704.html