大量数据提交,上千,上万,批量性能非常快,mysql独有
INSERT INTO student (id,NAME) VALUES(4,'name1');
INSERT INTO student (id,NAME) VALUES(5,'name2');
INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');
避免同时修改或删除过多数据,因为会造成cpu利用率过高,会造成锁表操作,从而影响别人对数据库的访问。
#一次删除10万或者100万+?
delete from student where id <100000;
#采用单一循环操作,效率低,时间漫长
for(User user:list){
delete from student;
}
#分批进行删除,如每次500
for(){
delete student where id<500;
}
delete student where id>=500 and id<1000;
一次性删除太多数据,可能造成锁表,会有lock wait timeout exceed的错误,所以建议分批操作
商品状态(state):1-上架、2-下架、3-删除
可以在执行到该语句前,把不需要的记录过滤掉
select job,avg(salary) from employee
group by job
having job ='president' or job = 'managent';
select job,avg(salary) from employee
where job ='president' or job = 'managent'
group by job;
创建复合索引,也就是多个字段
ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
满足复合索引的左侧顺序,哪怕只是部分,复合索引生效
EXPLAIN
SELECT * FROM student WHERE NAME='name1'
没有出现左边的字段,则不满足最左特性,索引失效
EXPLAIN
SELECT * FROM student WHERE salary=3000
复合索引全使用,按左侧顺序出现 name,salary,索引生效
EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000
虽然违背了最左特性,但MYSQL执行SQL时会进行优化,底层进行颠倒优化
EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='name1'
什么样的字段才需要创建索引呢?原则就是where和order by中常出现的字段就创建索引。
#使用*,包含了未索引的字段,导致索引失效
EXPLAIN
SELECT * FROM student ORDER BY NAME;
EXPLAIN
SELECT * FROM student ORDER BY NAME,salary
#name字段有索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME
#name和salary复合索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME,salary
EXPLAIN
SELECT id,NAME FROM student ORDER BY salary,NAME
#排序字段未创建索引,性能就慢
EXPLAIN
SELECT id,NAME FROM student ORDER BY sex
SHOW INDEX FROM student
#创建索引index_name
ALTER TABLE student ADD INDEX index_name (NAME)
#删除student表的index_name索引
DROP INDEX index_name ON student ;
#修改表结果,删除student表的index_name索引
ALTER TABLE student DROP INDEX index_name ;
#主键会自动创建索引,删除主键索引
ALTER TABLE student DROP PRIMARY KEY ;
三种连接如果结果相同,优先使用inner join,如果使用left join左边表尽量小
日常开发实现业务需求可以有两种方式实现:
如需求:查询所有部门的所有员工:
#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于:
#先查询部门表
SELECT id FROM tb_dept
#再由部门dept_id,查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id
假设表A表示某企业的员工表,表B表示部门表,查询所有部门的所有员工,很容易有以下程序实现,可以抽象成这样的一个嵌套循环:
List<> resultSet;
for(int i=0;i<B.length;i++) {
for(int j=0;j<A.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
上面的需求使用SQL就远不如程序实现,特别当数据量巨大时。
数据库最费劲的就是程序链接的释放。假设链接了两次,每次做上百万次的数据集查询,查完就结束,这样就只做了两次;相反建立了上百万次链接,申请链接释放反复重复,就会额外花费很多实际,这样系统就受不了了,慢,卡顿
SELECT * FROM student
UNION
SELECT * FROM student
SELECT * FROM student
UNION ALL
SELECT * FROM student