说明:本文章建立在有一定的数据库基础,只说明sql的优化思路,具体问题细节大家可以去网上找找资料。
个人思路sql的优化分三个方面:
1.程序员经验不足,sql写的太烂,这里就不多说了
2.表设计的缺陷,或者是二次开发增强了一些业务造成的join太多【这是无法避免的,但是设计库表的同时尽量向三范式靠拢,它能最大限度的合理处理数据,当然死死的遵循也是不可取的,必要的时候也是考虑反三范式的,完了,好话坏话都被我说了,那就分析一下他们吧】
三范式【三大特性】:
原子性:无重复的列,一列只能包含一个字段
不符合原子性:在校信息可拆分多个单位列
学号 | 姓名 | 在校信息 |
1 | 张三 | 读研,研一 |
2 | 李四 | 本科,大四 |
符合原子性:不能再次拆分多个单位列
学号 | 姓名 | 学历 | 年级 |
1 | 张三 | 读研 | 研一 |
2 | 李四 | 本科 | 大四 |
唯一性:主键约束,一行只能被唯一标识
不符合唯一性:只通过张三不确定是某一个学生
姓名 | 学历 | 年级 |
张三 | 读研 | 研一 |
李四 | 本科 | 大四 |
张三 | 本科 | 大三 |
王五 | 本科 | 大三 |
符合唯一性:通过学号可以确定某一个学生
学号 | 姓名 | 学历 | 年级 |
1 | 张三 | 读研 | 研一 |
2 | 李四 | 本科 | 大四 |
3 | 张三 | 本科 | 大三 |
4 | 王五 | 本科 | 大三 |
依赖性:非主键字段要严格依赖于主键字段
不符合依赖性:
学号 | 姓名 | 年级 | 辅导员编号 | 辅导员名称 | 辅导员性别 | 辅导员年龄 |
1 | 张三 | 研一 | 110 | 张飞 | 男 | 39 |
2 | 李四 | 大四 | 120 | 李白 | 男 | 40 |
符合依赖性:
学号 | 姓名 | 年级 | 辅导员编号 |
1 | 张三 | 研一 | 110 |
2 | 李四 | 大四 | 120 |
辅导员编号 | 辅导员名称 | 辅导员性别 | 辅导员年龄 |
110 | 张飞 | 男 | 39 |
120 | 李白 | 男 | 40 |
优势:没有冗余字段,增删改效率较高
劣势:join表太多,查询效率低,索引优化较难
反三范式【和三范式相反,这里就做过多说明】:
优势:查询效率高,索引优化相对简单
劣势:存在冗余字段,增删改效率较低
3.索引优化
a.什么是索引
索引是一种数据结构,它会持久化到硬盘,可以提高查询效率,你可以把它理解为排序好的可快速查找的数据。
b.什么情况下适合创建索引
主键自带索引
经常被查询的字段适合添加索引
外键适合添加索引
排序字段适合添加索引
分组字段适合添加索引
c.什么情况下不适合创建索引
不被查询的字段不适合创建索引
增删改比较频繁的表不适合创建索引
表数据较少的表不适合创建索引
表重复率比较高的字段不适合创建索引
d.如何避免索引失效
全值索引匹配
最左匹配原则
select * 有可能会造成索引失效
字符串查询的时候不加单引号会造成索引失效
sql上进行函数,计算,类型转换会造成索引失效
范围查找范围右边的字段查询会造成索引失效
like以通配符查询所有会生效
is null is not null or 不等号会造成索引失效
e.如何进行线上排查【只说流程哦】
首先是运营人员或者测试人员发现产品页面很慢
前后端人员确定是后端问题
后端人员确定不是代码逻辑问题,是sql问题
由项目组长或者项目经理告诉运维人员开启数据库阈值,进行慢sql查询
等个两到三天获取到慢sql
对获取的慢sql执行explan计划,可以看到sql的查询类型,
如:
是否是单表或者是子查询,或者产生临时表
查询是否是全表扫描或者是范围扫描等
sql索引及sql实际使用索引等
一般就是这些了,在实际中我们项目中表是设计都是采用单表,在执行计划中看看索引是否被使用等信息在进行优化
包括慢sql查询我们这边也是不使用的,我们这边整合了阿里云的Druid插件,它不仅提供了数据库连接的一下信息和sql的访问次数,成功次数及sql代码,最重要的是它提供了可视化页面,这点非常的好!