文章目录
一、SQL语句及索引的优化
SQL语句的优化
-
尽量避免使用子查询
-
用IN来替换OR
-
读取适当的记录LIMIT M,N,而不要读多余的记录
-
禁止不必要的Order By排序
-
总和查询可以禁止排重用union all
-
避免随机取记录
-
将多次插入换成批量Insert插入
-
只返回必要的列,用具体的字段列表代替 select * 语句
-
区分in和exists
-
优化Group By语句
-
尽量使用数字型字段
-
优化Join语句
索引的优化/如何避免索引失效
二、数据库表结构的优化:使得数据库结构符合三大范式与BCNF
三、系统配置的优化
四、硬件的优化
在开始介绍如何优化sql前,先附上mysql内部逻辑图让大家有所了解
(1)连接器: 主要负责跟客户端建立连接、获取权限、维持和管理连接
(2)查询缓存: 优先在缓存中进行查询,如果查到了则直接返回,如果缓存中查询不到,在去数据库中查询。
MySQL缓存是默认关闭的,也就是说不推荐使用缓存,并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的:
-
先说下缓存中数据存储格式:key(sql语句)- value(数据值),所以如果SQL语句(key)只要存在一点不同之处就会直接进行数据库查询了;
-
由于表中的数据不是一成不变的,大多数是经常变化的,而当数据库中的数据变化了,那么相应的与此表相关的缓存数据就需要移除掉;
(3)解析器/分析器: 分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析,最终得到抽象语法树,然后再使用预处理器对抽象语法树进行语义校验,判断抽象语法树中的表是否存在,如果存在的话,在接着判断select投影列字段是否在表中存在等。
(4)优化器: 主要将SQL经过词法解析、语法解析后得到的语法树,通过数据字典和统计信息的内容,再经过一系列运算 ,最终得出一个执行计划,包括选择使用哪个索引
-
在分析是否走索引查询时,是通过进行动态数据采样统计分析出来;只要是统计分析出来的,那就可能会存在分析错误的情况,所以在SQL执行不走索引时,也要考虑到这方面的因素
(5)执行器: 根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据,完成SQL的执行。
一、SQL语句及索引的优化
SQL语句的优化
1. 尽量避免使用子查询
例:
SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = 'chackca');
其子查询在Mysql5.5版本里,内部执行计划是这样:先查外表再匹配内表,而不是先查内表t2,当外表的数据很大时,查询速度会非常慢。
在MariaDB10/Mysql5.6版本里,采用join关联方式对其进行了优化,这条SQL语句会自动转换为:SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id
但请注意的是:优化只针对SELECT有效,对UPDATE/DELETE
子查询无效,固生产环境应避免使用子查询
由于MySQL的优化器对于子查询的处理能力比较弱,所以不建议使用子查询,可以改写成Inner Join
,之所以 join 连接效率更高,是因为 MySQL不需要在内存中创建临时表
2. 用IN来替换OR
-
低效查询:
SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;
-
高效查询:S
ELECT * FROM t WHERE id IN (10,20,30);
另外,MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from table_name where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。
3. 读取适当的记录LIMIT M,N,而不要读多余的记录
select id,name from