【SQL笔记】基础（一）

个人学习笔记分享，当前能力有限，请勿贬低，菜鸟互学，大佬绕道

如有勘误，欢迎指出和讨论，本文后期也会进行修正和补充

前言

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

个人站点：在搭了在搭了。。。（右键 - 新建文件夹）

posted @ 2020-08-26 09:16 Echo_Ye 阅读(75) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Echo_Ye

不知归期，不如归去

【SQL笔记】基础（一）

前言

1.基础知识

2.书写规范

3.基础技巧

3.0.explain（重中之重）

3.1.case...when..

3.2.having

3.3.self join

3.4.COALESCE

4.基础性能优化

4.1.使用子查询时，尽量使用exists替代in

4.2.合理使用索引，避免触发排序（重点）

5.补充（重点）

5.1.善用explain

5.2.注意mybatis和mybatisPlus对代码进行的调整

5.3.注意避免造成锁表

BB两句

公告