个人学习笔记分享，当前能力有限，请勿贬低，菜鸟互学，大佬绕道
如有勘误，欢迎指出和讨论，本文后期也会进行修正和补充
前言
SQL学习中的笔记，重点为4.2和5，题长不看的请直接空降
1.基础知识
增、删、改、查、连表、索引，新手教程一大堆，不多做叙述，菜鸟教程啥都有。。。
2.书写规范
-
表名：应有意义，使用小写英文、数字和下划线组成
Linux对大小写敏感，故方便起见，推荐统一表名的英文全部小写
-
注释：单行注释使用
--（注意有个空格）或者#标记行首，多行注释使用/*和*/标记头尾 -
缩进和空格：同Java，Python等编程语言，保持代码可读性
-
大小写：关键字用大写，表名、字段名用小写（关键词大写为习惯，非强制）
3.基础技巧
3.0.explain（重中之重）
- 解释：分析sql语句的执行情况，可以分析出当前使用的查找模式，索引和临时表的使用情况等等
- 使用场景：分析执行情况，调整sql或者索引来达到优化的目的
- 优点：可以借此方法对sql进行调试，知晓其执行情况，从而进行调整
- 备注：相关资料很多，为性能优化的基本知识
3.1.case...when..
-
解释：条件选择，类似于Java的switch语句，详情查看新手教程
-
使用场景：当需要对不同数据采取不同方案的情况下，
-
使用范例：
-
查询性别；gender字段的值为1返回“男”，为2时返回“女”，否则返回“未知”
SELECT CASE gender WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END AS genderStr FROM student_info; -
降薪；对salary字段更新，高于1W的降低20%，其余降低10%
UPDATE salary_info SET salary = CASE WHEN salary >10000 THEN salary * 0.8 ELSE salary * 0.9 END;
-
-
优点
- 省去数据的前置处理或者后置处理
- 避免使用多句sql，导致数据错乱。如范例2若对两种情况分别执行sql，将出现错误的结果
3.2.having
-
解释：对结果追加条件筛选，常与
GROUP BY结合使用，但其实也可以单独使用 -
使用场景：对数据进行再次筛选
-
使用范例：
-
统计学生的所有学科成绩，但仅返回平均分低于60的学生
SELECT COUNT(mark_record.marks),student_info.name,COUNT(mark_record.marks) FROM student_info LEFT JOIN mark_record ON mark_record.stu_id = student_info.id GROUP BY student_info.id HAVING AVG(mark_record.marks) < 60 LIMIT 1,10
-
-
优点：
- 直接对结果数据直接追加处理，也就不必使用子表啦，节省了大量资源~如范例的常规方案是先查出学生的所有学科，存为子表，再进行筛选
3.3.self join
-
解释：自连接。即自己与自己连接，仅作为一种方案，并不是SQL关键词
-
使用场景：需要引用自己身数据的情况下
-
使用范例：
-
查询与自己同班同学人数
SELECT si1.id, si1.NAME, count(*) FROM student_info si1 LEFT JOIN student_info si2 ON si1.grade_id = si2.grade_id GROUP BY si1.id -
查询某学科的所有成绩并排名
SELECT mr1.*, count( mr2.id )+ 1 FROM mark_record mr1 LEFT JOIN mark_record mr2 ON mr2.mark > mr1.mark WHERE mr1.subject_id = 1 GROUP BY mr1.id
-
-
优点：emm没看出来，只能说是一种方案吧
-
备注：注意连接条件字段最好用索引优化，否则两个全表查询，很容易查到天荒地老...
3.4.COALESCE
-
解释：返回第一个非空值，语法为
COALESCE(value,...) -
使用场景：结果可能为空时返回其他值，可以是默认值，也可以是其他字段
-
使用范例：
-
查询学生手机号，若为空则返回家长手机号，也为空则返回“无联系方式”
SELECT name, COALESCE ( phone, parent_phone, '无联系方式' ) FROM student_info
-
-
优点：
-
不必再写大量的case...when来达到上述的效果
-
设定默认值，情况下返回NULL是很不友好的，若不对空值特殊处理则很容易出现空指针，那为什么不在sql就处理好呢？
-
4.基础性能优化
此处仅指出最最最基础的部分优化，实际的性能优化是一门大学问，慢慢来
4.1.使用子查询时，尽量使用exists替代in
-
原因：子表比主表大的情况下exists的性能远比in高
-
分析：
- 每次in都会查询子表中所有满足条件的结果，并将其缓存，再与主表匹配，即每次都会遍历子表全部数据
- exists会每次从子表中查询是否满足条件，对结果不关心，故不需要缓存
- 若主表有m条数据，子表有n条数据，那么in查询最多需要m*n，而exists始终是m
-
范例：
-
查询所有持有证书的教师
-- in方法 SELECT * FROM teacher_info WHERE id IN ( SELECT person_id FROM certificate_record ); -- exists方法 SELECT * FROM teacher_info ti WHERE EXISTS ( SELECT 1 FROM certificate_record cr WHERE cr.person_id = ti.id )
-
-
优点：当子表数据远比主表大的时候，性能优化将会很明显
-
备注：说得好，我选择
join连接。。。
4.2.合理使用索引，避免触发排序（重点）
-
原理：索引的必要性就不多说了，排序会造成额外的内存消耗，且对无索引字段排序会导致全表查询
-
分析：在无排序的情况下sql会自动选取最优方案（他自己认为最优，一般是覆盖索引）
-
部分触发排序的函数
- GROUP BY 子句、ORDER BY 子句
- 聚合函数(SUM、COUNT、AVG、MAX、MIN)，但5.6以后COUNT也使用辅助索引，并不慢！
- DISTINCT
- 集合运算符(UNION、INTERSECT、EXCEPT)
- 窗口函数(RANK、ROW_NUMBER 等)。
-
解决方案：
-
集合运算符可以使用
ALL当然前提是不需要去重
-
EXISTS代替DISTINCT -
对
order by、group by或极值字段添加索引 -
能用
where就不用having -
将索引字段置于
=左侧，并避免进行运算会导致放弃索引
-
避免否定形式，如
<>、!=、NOT IN均会放弃索引
-
进行默认的数据类型转换
否则会放弃索引
mybatis可指定数据类型 -
减少中间表的使用，比如使用
haiving当然能用
where最好。。 -
对同一个表的多个字段使用
in时，可以汇总到一处所以为什么不使用
exists呢。。SELECT * FROM student_info WHERE id || grade_id IN (SELECT id || grade_id FROM dormitory_info); -
用延迟查询（覆盖索引）优化
limit适用于offset过大情况下
-- 常规查询 SELECT id,name FROM student_info WHERE gender=1 LIMIT 100000,10 -- 优化后，前提是对gender字段添加索引 SELECT id,name FROM student_info INNER JOIN (SELECT id FROM student_info WHERE gender=1 LIMIT 100000,10) as x using(id); -
利用
limit 1取得唯一行发现结果即停止扫描
-
注意组合索引的最左匹配原则
否则将失效
-
使用
like时，仅有符合最左匹配原则的索引生效业务要求全匹配那就只能放弃索引了。。
-- 不生效实例 SELECT * FROM student_info WHERE NAME LIKE "%叶子" SELECT * FROM student_info WHERE NAME LIKE "%叶子%" -- 生效实例 SELECT * FROM student_info WHERE NAME LIKE "叶子%" -
尽量用
_替换%当然前提是业务允许
-
尽量用自增id作为主键，且尽量保证其小，而且不要修改
而且为了统一规范也请使用自增id
-
使用
count统计数据会自行使用辅助索引
-
避免使用
SELECT *，且尽量对查询结果使用覆盖索引进行优化SELECT *可能会回表如果查询结果和条件均使用了索引，那性能就更好了
-
有必要的情况下使用
force index()强制使用索引非强制索引的时候，MYSQL会自行选择索引，但不一定是你想要的，通常是选择主键id
-
用批量插入替代逐条插入，更不可在业务层循环插入
逐条插入会无端增加日志和事务，效率偏低，但见得少
业务层循环插入是真滴多，效率那就不是一般的低了
-
5.补充（重点）
5.1.善用explain
explain作为执行情况分析工具，是性能优化的基础，都不知道怎么执行的，那咋优化？
相关资料很多，建议先打好基础
5.2.注意mybatis和mybatisPlus对代码进行的调整
大部分项目会使用
mybatis和mybatisPlus插件，请注意实际执行的语句是否符合预期，可能根本就不是你想要的样子相关日志会打印在控制台，推荐使用插件
mybatis sql log查看实际执行的sql，IDEA插件市场可以搜到
5.3.注意避免造成锁表
主要是基于ACID特性的考虑，数据更新时会锁住相关的数据
一旦造成锁表，会导致表内数据无法访问，相关操作将会无法执行，请尽可能避免
试想线上项目某核心操作无法执行，比如下单，长达十几分钟甚至几个小时，会发生什么。。大概率是要准备面试了
以下两种情况会造成锁表，如有更多情况，欢迎提出补充
insert into...select操作未使用索引，将会导致锁表，建议完全禁止相关操作示例：
INSERT INTO record_new SELECT * FROM record_old WHERE created_time < '2020-08-04 00:00:00'分析：
上述语句进行全表扫描的时候，为保持ACID特性，将会锁死record_old表中扫描到的数据，即全部数据，进而导致整个表被锁
解决方案：
方案1：理论上对条件添加索引即可，但范围查询结果超过总数据的30%（实际约15%），将会放弃索引，依旧全表扫描，所以建议完全禁止相关操作
方案2：可以借助外部工具进行数据迁移
方案3：将表内数据查出，再进行数据更新操作（请通过其他渠道保持ACID特性）
同一事务执行多个SQL更新操作过程，会将相关数据锁住，如果异常终止，事务没有释放锁，可能导致数据一直被锁住
示例：事务执行A和B两个sql数据更新操作，执行成功前会将A和B相关的表锁住，若发生意外，如断电、服务崩溃、服务被强制关闭等，可能会导致表一直被锁住
解决方案：重启mysql服务（反正项目都得重启了。。。）
BB两句
SQL是一门很大的学问，虽然大部分时间我们只用到了一小部分，但人毕竟是要进步的，浅尝辄止的做crud，怎能完成我的肥仔大梦（虽然我不肥）
作者：Echo_Ye
wx：Echo_YeZ
email ：echo_yezi@qq.com
个人站点：在搭了在搭了。。。（右键 - 新建文件夹）