对SQL索引的优化,主要是在sql语句细节上的琱琢。
因为我们既然在大数据量的查询下为了提高效率建立了索引,就要使用到索引。而SQL语句中有时候有些写法可能会导致索引的失效或者效果大减的情况。
简单说对于SQL优化,就三点:
- 最大化利用索引;
- 尽可能避免全表扫描;
- 减少无效数据的查询;
首先要清楚SELECT语句 - 执行顺序:
FROM
<表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join…>
<join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
DISTINCT
#数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>
一、SQL优化策略
注意:以下优化的策略均适用于大数据量的情况下,数据量较少的时候没必要使用这些策略。
1. 模糊查询
原sql:
select * from t where name like '%李%'
优化方案:
select * from t where name like '李%'
原理:模糊查询的时候尽量在字段后面使用模糊查询最左原则
2. 避免使用or
原sql:
select * from t where id = 1 OR id = 2
优化方案:
select * from t where id = 1
union
select * from t where id = 2
原因:使用 or,会导致数据库引擎放弃索引进行全表扫描。所以通过union 合并两条查询结果。
3. 避免使用null值判断
在数据库设计之初最好避免null的出项,可以使用固定的值进行初始化。在后续的判断周通过初始化的值进行null的判断。
原因:
使用null值判断的话会导致数据库引擎放弃索引进行全表扫描。
4. 在where条件中,等式左侧不要进行表带式、函数操作。
-- 全表扫描
SELECT * FROM t WHERE age/10 = 3
-- 走索引
SELECT * FROM t WHERE age = 9*3
5. 避免使用where 1=1的条件
处理方法,在代码中拼接数据库的时候尽量,先判断有没有条件没有条件的话就不需要拼接 where 1=1。
6. 查询条件不要用 <> 或者 !=
7. where条件仅包含复合索引非前置列
当有符合索引的时候,例如 有联合索引(a,b,c)
当 where b = *** and c = ‘**’ 这种没有以 a 为条件来检索时;B+树就不知道第一步该查哪个节点,从而需要去全表扫描了(即不走索引)。
这里索引也支持最左匹配原则
8. order by 条件要与where中条件一致,否则order by不会利用索引进行排序
1. 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;
对于上面的语句,数据库的处理顺序是:
二、其他的优化
- 避免出现select *
在设计查询操作时,应该尽量只查询需要的字段。一些用于维护的字段或者不需要的字段不要查询出来。调高效率,减少IO、内存和CPU的小号。
- 避免出现不确定结果的函数
例如:now()、rand()、sysdate()、current_user()等不确定结果的函数
- 多表关联查询时,小表在前,大表在后
在MySQL中,执行 from 后的表关联查询是从左往右执行的
- 避免使用HAVING字句
因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。
where和having的区别:where后面不能使用组函数