SQL进阶学习(持续更新)

SQL进阶学习

基于自身的SQL基础，在学习极客时间相关课程后对SQL的认识提升总结。

1. SQL基础

1. 大小写规范
   1）关键字,函数名是不区分大小写的
   2）MySql在Windows环境下全部不区分大小写
   3）在Linux环境下,数据库名、表名、变量名是严格区分大小写的,字段名是忽略大小写的
2. count() 方法注意
   1）count(*) 统计所有行数
   2）count(字段) 统计非空的行数
3. HAVING 与 WHERE 的区别：WHERE 是用于数据行，而 HAVING 则作用于分组
4. 子查询
   1）非关联子查询：与主查询的执行无关，只需要执行一次即可
   SQL: SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)
   2）关联子查询：需要将主查询的字段值传入子查询中进行关联查询
   SQL: SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)
5. 自连接
   比如我们想要查看比布雷克·格里芬高的球员都有谁，以及他们的对应身高
   SQL：SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = ‘布雷克 - 格里芬’ and a.height < b.height

2. SQL查询优化点

1. 尽量避免在SQL语句的WHERE子句中使用函数（可以转化为范围扫描）
2. 效率比较：count(*) > count(字段)
3. EXIST 和 IN 子查询效率的比较
   1）当查询字段进行了索引时，主表 A 大于从表 B，使用 IN 子查询
   2）主表 A 小于从表 B 时，使用 EXIST 子查询
4. 查询优化的本质是减少扫描的行数及回表的次数

3. 从数据页的角度理解B+树查询

1) 数据库中的存储结构是怎样的

1. 在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）
2. 一个页中可以存储多个行记录（Row），同时在数据库中，还存在 (Extent)、段（Segment）和表空间（Tablespace）。行、页、区、段、表空间的关系如下图所示：

• 区（Extent）是比页大一级的存储结构，在 InnoDB存储引擎中，一个区会分配64个连续的页。因为 InnoDB存储引擎中的页默认大小是16KB，所以一个区的大小是 64*16KB=1MB
• 段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
• 表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

3. 在InnoDB中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。

2) 数据页内的结构是怎样的

页（Page）如果按照类型划分的话，常见的数据页（保存B+树节点）、系统页、Undo页和事业数据页等。数据页是我们最常使用的页。
表页的大小限定了表行的最大长度，不同DBMS的表页大小不同。比如在MySQL的InnoDB存储引擎中，默认页的大小是16KB。

3) B+树是如何进行记录检索的？

如果通过B+树的索引查询行记录，首先是从B+树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。

4) 普通索引和唯一索引在查询效率上有什么不同？

• 唯一索引在普通索引上增加了约束性，找到了关键字就停止检索
• 普通索引会存在用户记录中的关键字相同情况，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中进行读取。InnoDB存储引擎的页大小为16KB，在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次”判断下一条记录“的操作，对于CPU来说，这些操作所消耗的时间是可以忽略不计的。

4. 从磁盘I/O的角度理解SQL查询的成本

1) 查询多行记录，数据库将采用缓冲池的方式提升页的查找效率

2) 数据库缓冲池

• 缓冲池管理器会尽量将经常使用的数据保存起来。在数据库读操作时，会通过内存或磁盘将页面存放到缓冲池中再进行读取。
• 在对数据库记录进行修改时，首先修改缓冲池中页里面的记录，然后数据库会以一定的频率刷新到磁盘上。注意，不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用 checkpoint 的机制将数据回写到磁盘上。
• 比如，当缓冲池不够用时，需要释放掉一些不常用的页，就可以采用强行 checkpoint 方式，将不常用的脏页（缓冲池中被修改过的页，与磁盘上的数据页不一致）回写到磁盘，然后再从缓冲池将这些也释放掉。

3) 数据页加载的三种方式（如果缓冲池没有该页数据）

• 内存读取（如果该数据存于内存中，执行时间在 1ms 左右）
• 随机读取（如果数据不在内存中，整体预估 10ms）
• 顺序读取（批量读取方式，）

5. 如何设计索引

1）一张表的索引个数不宜过多，否则一条记录的增加和修改，会因为过多的索引造成额外的负担。

2）针对这个情况，当你需要新建索引的时候，首先考虑在原有的索引片上增加索引，也就是采用复合索引的方式，而不是新建一个新的索引。

3）定期检查索引的使用情况，对应很少使用到的索引可以及时删除

4）在索引片中，我们也需要控制索引列的数量，通常情况下我们将 WHERE 里的条件列添加到索引中，而 SELECT 中的非条件则不需要添加。除非 SELECT 中的非条件列数少，并且该字段会经常使用到。

5）单列索引和复合索引长度也需要控制，在MySQL InnoDB中，系统默认单个索引长度最大为 767 bytes。

6. 锁：悲观锁和乐观锁是什么？

按照粒度进行划分（每个层级的锁数量有限制，当某层级的锁数量超过这个层级的阈值，锁会升级）

• 行锁，按照行的粒度对数据进行锁定。锁定力度小，发生锁冲突概率低，可以实现的并发度高，但对于锁的开销比较大，容易出现死锁的情况。
• 页锁，在页的粒度上进行锁定，锁定的数据资源比行锁要多，易出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁开销介于表锁和行锁之间，会出现死锁。并发度一般。
• 表锁，对数据表进行锁定，锁定粒度很大，同时发生锁冲突的改了也会较高，数据访问的并发度低。好处在于锁的使用开销小，加锁会很快。

从数据库管理的角度对锁进行划分

• 共享锁，共享锁锁定的资源可以被其他用户读取，但不能修改。在进行 SELECT 的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁。
• 排他锁，排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。
• 意向锁，给更大一级别的空间示意里面是否已经上过锁。

为什么共享锁会发生死锁的情况？

• 客户端1 开启事务，对某条数据加 读锁
• 客户端2 开启事务，对同条数据加 读锁，此时再执行更新操作，就是一直等待。

避免死锁的一些方法

• 如果事务涉及多个表，操作比较复杂，可以尽量一次锁定所有的资源，而不是逐步来获取；
• 如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；
• 不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率。

从程序员的角度进行划分

• 乐观锁（Optimistic Locking）（版本号机制 或 时间戳机制）
  乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不同每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。

• 乐观锁的版本号机制
  在表中设计一个版本字段 version，第一次读的时候，会获取 version字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE … SET version = version + 1 WHERE version = version。此时如果已经有事务对这条数据进行更改，修改就不会成功。

• 乐观锁的时间戳机制
  时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

• 悲观锁（Pessimistic Locking）也是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。