1、查询SQL尽量不要使用select *,而是select具体字段。
反例子:
select * from employee;
select id,name from employee;
CREATE TABLE `employee` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `date` datetime DEFAULT NULL, `sex` int(1) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select id,name from employee where name='jay'
select id,name from employee where name='jay' limit 1;
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userId` int(11) NOT NULL, `age` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `idx_userId` (`userId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select * from user where userid=1 or age =18
//使用union allselect * from user where userid=1 union allselect * from user where age = 18
//或者分开两条sql写:select * from user where userid=1select * from user where age = 18
select id,name,age from employee limit 10000,10
//方案一 :返回上次查询的最大记录(偏移量)select id,name from employee where id>10000 limit 10.
//方案二:order by + 索引select id,name from employee order by id limit 10000,10
//方案三:在业务允许的情况下限制页数:
select userId,name from user where userId like '3';
select userId,name from user where userId like '123%';
List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);
Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = userId!=null;
select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();
explain select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);
select * from user where age-1 =10;
select * from user where age =11;
select * from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id>2;
select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;
select age,name from user where age <>18;
//可以考虑分开两条sql写select age,name from user where age <18;select age,name from user where age >18;
免责申明:
本文系转载,版权归原作者所有,如若侵权请联系我们进行删除!
《数据治理行业实践白皮书》下载地址:https://fs80.cn/4w2atu
《数栈V6.0产品白皮书》下载地址:https://fs80.cn/cw0iw1
想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:https://www.dtstack.com/?src=bbs
同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:https://github.com/DTStack