内容均为网上收集整理,供自己学习做笔记用。
1)原理
优化,就是同时考虑空间复杂度与时间复杂度结合达到最优解。sql语句的优化大致分为几个大的方向去思考:
- 减少数据量的访问,设置好合理的字段类型,启用压缩,通过索引的访问等来减少磁盘的IO读写。
- 返回更少的数据,尽量返回只需要的字段和数据分页处理,来减少磁盘的IO与网络IO消耗。
- 减少交互次数,批量进行DML操作,函数存储等来减少数据的连接次数。
- 减少服务器的CPU开销,尽量减少数据排序操作、全表查询等,减少CPU的内存占用。
- 利用更多的资源,使用表分区,可以增加并行操作来更大限度利用CPU的资源。
2)总结
- 最大化利用索引。
- 尽可能的去避免全表扫描。
- 减少无效的数据查询。
3)语句的语法顺序
- SELECT
- DISTINCT <SELECT_list>
- FROM <left_table>
- <join_type> JOIN <right_table>
- ON <join_condition>
- WHERE <where_condition>
- GROUP BY <group_by_list>
- HAVING <having_condition>
- ORDER BY <order_by_condition>
- LIMIT <limit_number>
4)语句的执行顺序
- 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.1)避免在开头使用模糊查询,会导致数据库引擎放弃索引来进行全表扫描。
select * from table where col1 like '%张%';
优化方式-》避免在前面使用%
select * from table where col1 like '张%';
如果必须要在前面使用模糊查询,
1.使用MySQL内置函数 INSTR(str,substr)来匹配,类似indexOf()方法,查询字符串出现的角标位置。
2.使用FullText全文索引,用 match against 检索。
3.当数据量非常大时,建议可以使用ElasticSearch、solr,亿级数据量检索速度秒级。
1.2)尽量避免使用in和not in,会导致引擎走全表扫描。in可以使用exists进行优化。
select * from table1 where col1 in('1','2','3');
如果是连续的数值,建议使用between来代替in
select * from table1 where col1 between 1 and 3;
如果是子查询的话,可以使用exist来代替in
--不走索引
select * from table1 where col1 in (select col1 from table2);
--走索引
select * from table1 where exist (select * from table where table2.col1=table1.col1);
1.3)尽量避免使用or,会导致数据库引擎放弃索引而进行全表扫描。
select * from table1 where col1 = 1 or col1 = 2;
可以用union来代替不同or
select * from table where col1 = 1
union
select