sql优化--索引机制

背景

随着数据量的不断增长，如何优化sql成了一大难题，经过课程学习，也了解到了一些我们日常没注意的语法细节，这里为大家做个分享！

sql优化

思路：巧用索引，避免全表扫描。对经常筛选的字段添加索引机制，但很多时候，由于表达式书写不规范，数据库会跳过索引机制，执行全表扫描，这样数据库检索速度就变慢了，所以，我们要了解哪些写法会导致全表扫描

1、优化一：
（1）不要把select 子句写成select * ，因为这样会查询所有的字段，包含业务无关的字段，这样会加重数据库的IO负担，另外这种写法，数据库会先取表结构，把星号转换为具体字段，然后再执行sql语句；虽然我们写着方便，但数据库执行起来一点都不高效
（2）谨慎使用模糊搜索

//为ename添加索引
//由于引号里边第一个字符为%，数据库检索不到指定字符串，无法利用二叉树遍历，故会进行全表扫描
select ename from table where ename like '%S%';
//优化:根据实际情况，将第一字符改为具体字符，即可利用二叉树遍历
select ename from table where ename like 'S%';

2、优化二
（1）为order by排序字段设置索引

select ename from table order by deptno;

（2）少用is null 和 is not null

//为comm薪水字段添加索引
//查出薪水不为空的员工姓名，由于is not null 未指定值，无法利用二叉树遍历，故会进行全表扫描
select ename from table where comm is not null;
//优化，由于薪水是不能为负数，可以改成薪水大于等于0，也可得到相同的结果集
select ename from table where comm >= 0;

3、优化三
（1）尽量少用 != 运算符

//查询部门编号不等于20的员工姓名，由于不等于未指定具体值，故无法使用二叉遍历，将进行全表扫描
select ename from t_emp where deptno != 20;
//优化
select ename from t_emp where deptno > 20 and deptno < 20;

（2）尽量少用 or 运算符，or之前的表达式会利用索引，之后的表达式会进行全表扫描

//未优化
select ename from table where deptno = 20 or deptno = 30;
//优化
select ename from table where deptno = 20
union all
select ename from table where deptno = 30;

4、优化四
（1）尽量少用in 和 not in运算符，他们都属于或关系，与or类似

//未优化
select ename from table where deptno (20,30);
//优化
select ename from table where deptno = 20
union all
select ename from table where deptno = 30;

（2）避免条件语句中的数据类型转换

//由于deptno为数字类型，sql会将其转为字符串进行比较筛选，因此大大降低了sql执行效率
select ename from table where deptno = '20'；

5、优化五
（1）在左侧表达式使用运算符和函数都会让索引失效

//未优化
select ename from table where salary * 12 > 100000;
//优化
select ename from table where salary > 100000/12;
//未优化
select ename from table where year(hiredate) >= 2000;
//优化
select ename from table where hiredate > = '2000-01-01 00:00:00';