直接上干货:
1、索引比全表扫描要快,但索引占用存储空间,且需要定期维护,对表进行’增、删、改’时索引也会变动,占用I/O。
在经常使用的列上建立索引,where,关联等
失效因素:
模 型 数 空 运 最 快
模糊 :带有like % 的查询会导致索引失效
型 :数据类型不对
数 :对索引列进行函数时
空 :列中有null。索引不存储空值,不做not null 限制,不会按照索引进行计算
运 :对索引列加减乘除运算
最左 :复合索引,只有在查询最左侧索引时才能生效
快 :如果数据库预计全表扫描更快时,就会有效全表扫面,索引失效。
2、少使用 * 查询,distinct,having等,多用commit;
3、from 是从右向左,where 从下向下执行,优先使用where做筛选较having好(having是在检索出所有记录后才对
结果集进行过滤,需要排序,总计等操作完成后进行);
4、truncate 较delete 删除性能更快,在保证不需要全表数据的情况下;
(delete一行一行删除,同时会记录在空间表中,等待回滚,占用空间,高水位线)
5、(重点) exists,not exists 与 in, not in 的灵活使用:IN适合于外表大而内表小的情况,EXISTS反之
写法一:select * from A where ANAME in(select BNAME from B)
写法二:select * from A where exists(select BNAME from B where BNAME=A.ANAME)
--
in 先子查询,然后遍历主表 确定给定的值是否与子查询或列表中的值相匹配
exists 先主表,然后判断行是否存在 指定一个子查询,检测行的存在
1) in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,
反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
2)写法一:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.
写法二:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.
3)用exists替换distinct:
当提交一个包含一对多表信息的查询时,避免在select子句中使用distinct. 一般可以考虑用exists替换
(低效)select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;
(高效)select d.dept_no, d.dept_name from t_dept d where exists (select 1 from t_emp where d.dept_no = e.dept_no);
---------exists参考学习此文章,讲的很详细:
Sql 语句中 IN 和 EXISTS 的区别及应用_jcpp9527的博客-CSDN博客_exist in sql