sql查询是否存在优化,不要再用count(*)了
常用查询语句:
select count(*) from table where a=1
优化后语句:
select 1 from table where a=1 limit 1
实验
表结构
CREATE TABLE `t_s` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(25) DEFAULT NULL, `sname` varchar(25) DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=8000001 DEFAULT CHARSET=latin1;
执行存储过程
begin declare v int default 5; while v < 8000000 do insert into t_s(cname,sname) values ('testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652)); set v = v + 1; end while; end
数据量(8000000)
![](https://img2022.cnblogs.com/blog/1351374/202205/1351374-20220510112534315-2001971585.png)
常用查询1
SELECT COUNT(*) FROM t_s WHERE cname='testingpartitions' 受影响的行: 0 时间: 0.979s
类似查询2
SELECT COUNT(*) FROM t_s WHERE cname='testingpartitions' LIMIT 1 受影响的行: 0 时间: 0.973s
优化查询:
SELECT 1 FROM t_s WHERE cname='testingpartitions' LIMIT 1 受影响的行: 0 时间: 0.001s