SQL学习分享

SQL学习分享

数据库基础

建表的原则（三大范式）

1. 1NF：保证原子性。比如地址：杭州市江干区xxx这样的地址就不是原子性的，是可以切割的。
2. 2NF：保证各列完全依赖主键。比如
   • （学生，课程） -> 高考成绩。成绩完全依赖于学生和课程
   • （学生，课程，学校）-> 高考成绩。这就不是完全依赖
3. 3NF：消除传递依赖：可以根据非主键的列，找到与其他列的对应关系。

drop、delete、truncate

drop：删除一张表，删除数据并且删除表的数据结构
delete：删除的是行数据
truncate：是先把表删除，再创建一张完全一样的表。

DDL、DML

DDL就是建表，对表的数据结构做出改变的操作
DML增删改查

关系型数据库和非关系型数据库

关系型数据库：表都是由结构的。
非关系型数据库：以元组为单位，一个元组中有的数据不固定

Mysql相关

一条SQL的执行顺序

1. from
2. where
3. group by xxx having
4. 聚合函数
5. select
6. order by
7. limit

执行一条select

1. 连接器：建立连接，管理连接、校验用户身份；

2. 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。

3. 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构4. 建语法树，方便后续模块读取表名、字段、语句类型；
   执行 SQL：执行 SQL 共有三个阶段：

4. 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。

5. 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；

6. 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

7. 行锁须知

   1. 对于临键锁，如果发现唯一索引会退化成记录锁
   2. Innodb支持的行锁是作用在索引上的，sql没有执行索引，Innodb都是使用表锁的。Myisam是不支持行锁的。

8. 悲观锁：指的是数据在被处理的过程中，处于被锁定的状态。排他锁，共享锁都是悲观锁的实现

9. 乐观锁：乐观锁是不锁定数据，只去判断是否发生了冲突。版本号、时间戳实现

10. Mvcc：三者是并列关系哦

Mysql索引

索引是建立在引擎的基础上的，所以不同的引擎所对应的索引的机制是不同的
常用的引擎是Innodb

索引的分类

1. 从索引的功能上来说可以分为
   1. 主键索引
   2. 唯一索引
   3. 普通索引
   4. 全文索引
2. 从索引的物理存储来讲，索引可以分为
   1. 聚簇索引
   2. 非聚簇索引
3. 从组成索引的字段个数来分，可以分为
   1. 单列索引
   2. 联合索引
      针对这些索引我们展开讲解

功能索引

1. 主键索引
   1. 主键索引是唯一索引，唯一索引不一定是主键索引
   2. 主键索引不能为空
   3. 在Innodb引擎中，
      1. 主键索引是聚簇索引，非主键索引是非聚簇索引。
      2. 非主键索引的数据节点引用了主键索引。
      3. 在Innodb引擎中，如果用户没有设置主键，则会指定一个非空的唯一索引作为主键，如果没有非空的唯一索引，则选择自己生成一列作为主键索引。
      4. nnodb的主键索引文件其实就是数据文件。在聚簇索引里面装载了所有的数据）
   4. 一张表中，只能有一个主键索引
   5. 可以被外键引用，外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
   6. 主键索引最好要自增，因为如果主键是自增的，一般来说说插入数据就是追加插入，如果不是自增的，就会出现数据随机插入。这可能会引起页分裂等问题，造成性能损失
2. 唯一索引：
   1. 唯一索引不一定是主键，主键一定是唯一索引。
   2. 唯一索引支持空值。
   3. 唯一索引比普通索引定位数据更快一些，在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回
   4. 唯一索引更新会慢一些，因为不能使用change buffer，唯一索引的更新会比普通索引慢一些。（其实change buffer也就普通索引能用）
      1. change buffer。当数据更新的时候，对于普通索引，如果目标页在内存中（mysql都是以页为单位存储数据的）直接更新。唯一索引，如果目标也在内存中，就判断唯一性约束然后更新（判断唯一性约束就必须把页读进内存，这也是为什么mysql不能使用change buffer的原因）。而普通索引，就把更新的操作写进change buffer，等待下一次页被读入内存再更新，这就大大提高了IO。
3. 普通索引：就是普通索引
4. 全文索引：主要是针对字符串类型数据提升查询速率

从存储结构来看索引的存储

可以这么理解，Mysql是以页为单位存储数据的，而B+ Tree是逻辑上的结构，可以理解为一个页就是一个node，一般来说页是16k大小。理解这一点很重要
在Innodb中（myisam专门讲）

1. 聚簇索引（在Innodb中，必须存在聚簇索引）：
   1. 选择主键作为聚簇索引，如果主键不存在，则选择非空的唯一索引作为聚簇索引，如果不满足，则自己生成一列作为主键索引。
   2. 聚簇索引文件本身就是数据文件。聚簇索引叶子节点存放的就是所有数据这个和Myisam是不同的。
   3. 聚簇索引是唯一索引
2. 非聚簇索引
   1. 叶子节点存放的是主键值，索引带来了两个问题。回表查询和索引覆盖。
   2. 非聚簇索引可以有很多个

回表查询和覆盖索引

回表查询：因为非聚簇索引的叶子节点存放的是主键值，所以通常需要拿主键值再返回聚簇索引再查询一次，这就是回表查询。
覆盖索引：覆盖索引就是解决回表查询问题的，可以从两个角度去做覆盖查询这件事
1. 尽量在SQL中使用具体字段，少用*
2. 创建一个索引，比如要查询，姓名和身份证号，就可以针对姓名和身份证号做一个索引，这样就避免了回表查询（因为非聚簇索引中已经包含了）

从构建索引的数量来看

1. 单列索引：由一个数据字段组成的索引
2. 联合索引：由多个字段组成的索引
   1. 最左匹配原则：比如创建一个联合索引（a,b,c），其实底层数据存储的时候是，在a有序的前提下b是有序的，在ab有序的情况下c是有序的

SQL优化

1. Mysql实现分布式锁
   1. 创建一张表，如果用到了某一个资源，就插入一条数据，等到资源被释放了，就把这条记录删除，
2. 解锁的过程只要吧对应的进程Id给kill掉就行。
3. 数据库查询优化
   1. 加索引
   2. 分页
   3. 分表：
      1. 水平分表（按照主键递增的规律或者Hash%来粉白）
      2. 垂直分表（把用的多的数据和用的不多的数据（或者较大的数据）分开）
4. Mysql分页：
   1. 还要分页再优化，那就是懒加载模式了
5. 索引的选择性：
   区分度越大的做索引越合适，所以性别没必要做索引

Mysql建立索引的标准

建立索引的好处

1. 最大的好处当然是提升查询效率
2. 建立唯一性索引，保证一行数据的唯一性
3. 提升分组的效率

建立索引的坏处

4. 创建销毁索引的开销
5. 增加额外的空间去存储索引
6. 维护索引，数据的增删改都需要维护索引。

建立索引的标准

需要建立索引的时候
什么时候应该建立索引，应该针对哪些字段建立索引
7. 针对频繁用于条件查询的字段，where 的条件，分组的条件 group by。这样可以提升查询效率，提升分组效率。
8. 表达数据的唯一性的时候，建立唯一索引

不需要建立索引的时候
9. 不应该针对频繁需要改动的数据建立索引比如余额
10. 不应该针对区分度不高的字段加索引。比如男女
11. 针对有空值的列不建议建立索引
1. NULL占据空间的存储空间比较大
2. Mysql在做语法优化的时候对于NULL值的处理会很麻烦，存储NULL值的也会占用更大的空间

Mysql 索引的物理存储

在Mysql中索引的物理存储是选择用B+树的，为什么呢？

B+树

可以从图中看出，在Innodb引擎中Mysql是使用了B+树作为索引的。为什么呢

1. 采用B+树可以采用二分查找的方式，效率比较高

2. B+树只有在叶子节点才会存放数据，其他节点存放索引。这使其能够深度更小，宽度更大，能减少底层IO

3. B+树在底层的叶泽节点采用了双向链表，支持范围查询

4. B+树有大量冗余节点，插入删除的时候对总体结构影响不是特别大

5. B+树 Vs B树

   1. B树的范围查询性能弱于B+树
   2. B树的节点存放数据与索引，所以相同数据量B树更深一点
   3. B树的查询需要把数据部分也读入进内存，而本质上我们只是想比较索引，所以会造成一定的资源浪费。

6. B+树 Vs Hash

   1. Hash不支持范围查找，因为值相同的两个值，可能哈希后的位置天差地远，所以不支持范围查找
   2. 不支持模糊查询
   3. Hash的应用场景太少了

索引失效

抓住一个核心，索引存放的是列的原始值，一旦表达式结果不是列的原始值，就不会走索引

1. 左模糊匹配
2. 联合索引为采用最左匹配
3. 隐式转换，varchar类型的数据转换成 phone = 123 phone被转换成int类型不走索引
4. 使用or可能致使索引失效：有一列没有走索引
5. 对索引使用了函数
6. 对索引做了表达式判断 age - 1 = 10
7. 使用了=null

Mysql中的锁

锁的分类

1. 全局锁：会锁住数据库中所有的表
2. 表级别锁：
   1. 表级共享锁
   2. 表级排它锁
   3. 意向锁
      1. 意向排他锁
      2. 意向共享锁
   4. 元数据锁
   5. Auto_INC锁
3. 行锁：
   1. 记录锁
      1. 行级排它锁
      2. 行级共享锁
   2. 间隙锁
   3. 临键锁 = 记录锁 + 间隙锁

表级别锁

1. 意向锁：诞生的目的是表现当前这张表中某些记录是否被加了锁
   1. 从上图中就能得知，意向锁和意向锁彼此之间是兼容的。
   2. 意向锁兼容行级别锁
   3. 除了意向共享锁和表级别共享锁兼容，其他情况都不兼容
2. 表级别锁：
   1. 在加表级别锁的时候，先会去获得对应的意向锁。比如表级别排它锁，要先加上意向表级别排它锁，而这个是不兼容的。
   2. 读读兼容，读写不兼容，写写不兼容
3. 元数据锁：元数据锁存在意义是防止在执行CRUD的时候对表的结构做了变更
4. AUTO_INC锁：实现主键自增的锁

行级别锁

悲观锁：指的是数据在被处理的过程中，处于被锁定的状态。排他锁，共享锁都是悲观锁的实现
乐观锁：乐观锁是不锁定数据，只去判断是否发生了冲突。版本号、时间戳实现

只有InnoDB支持行级别锁，Myisam是不支持的！！！ InnoDB的锁是在索引上加锁，不是在列上加锁，很多全表搜表不走索引，这个锁加了白加

之前一直没解决的问题，在MVCC的时候，明明已经读取了数据的快照，还会有幻读问题。

1. 在Mysql中，在可重复读的隔离级别下有两种方法解决幻读问题
   1. 因为在Innodb引擎下，普通的select是不加锁的，通过MVCC的快照，因为读取的是快照所以新增的数据其实是看不到的
   2. 但是加锁的查询语句比如说 select xxx fro for update 这种情况下是通过 临键锁来避免幻读问题的。通过临键锁，不进增加的幻读能解决，删除的幻读也能解决！

临键锁的退化

在Mysql中，加锁的基本单位是临键锁。临键锁相当于是记录锁与间隙锁的组合，但是在一些情况下会退化成记录锁或者间隙锁。记录锁是前开后闭，而间隙锁是前开后开

1. 在唯一索引等值查询的情况下：

   1. 有记录存在：退化成记录锁
   2. 没有记录存在：退化成间隙锁

2. 在普通索引等值查询情况下：

   1. 有记录存在：不仅不会变，还会额外加一把间隙锁，锁住当前行到最近一行不符合条件记录之间的空间
   2. 记录不存在：退化成间隙锁

3. 范围查询

   1. 唯一索引有可能会退化
   2. 普通索引：不会退化

update 和 delete 的where条件没有携带索引

Innodb实现行锁，是在索引上加锁，不是数据列上加锁
已知
update、delete、select update，这类加锁的SQL语句，都会给记录加上临键锁，如果where后面跟的条件没有携带索引，那么将会走全表扫描，那么就会给表中的所有数据加上临键锁，严重一点甚至会导致业务崩溃。
insert是通过隐式锁来完成的

数据库的死锁问题

1. 设置事务的等待时间，超时就会回滚释放资源
2. 开启死锁检测：会根据深度搜索，如果判断成环就会根据优先级/锁的数量等因素回滚某一个事务

Mysql 事务

Innodb默认的事务隔离级别是可重复读

1. 事务的四个特性
   1. 原子性：通过undolog完成
   2. 隔离性：通过MVCC完成
   3. 一致性: 通过其他三个特性完成
   4. 持久性：通过redo log完成
2. 数据库的隔离级别和会出现的问题
   1. 读未提交：会出现脏读的问题
   2. 读已提交：会出现不可重复读的问题
   3. 可重复读：会出现幻读的问题
   4. 串行化：事务都是一件一件完成，并发性很弱，一般不适用。
3. 读未提交：在这个级别下，数据库的所有操作都是公开的。事务A做了什么改动，事务B能够立刻知晓。脏读，当事务A修改了某个数据的值，并且事务B把这个数据读取了，当A发生错误回滚之后，B读到的数据就是脏数据。
4. 读已提交：在这个级别下，事务的操作，只有事务被提交了，才会被公开。这个隔离级别解决了脏读问题。但是带来了不可重复读的问题。比如事务A、事务B同时读取某一个数据，事务A对数据修改并且提交，事务B再次读取这个数据，就会出现前后两次数据读取不一致的问题。
5. 可重复读：这个是Mysql默认的隔离范围，在这个隔离级别下，事务启动时到结束，看到的数据都是相同的
6. 串行化：事务的操作是串行的。

MVCC

1. 读未提交状态，直接读取到最新的数据就好了，不需要额外的操作。
2. 读已提交和可重复读都是通过MVCC来完成的
3. 串行化是通过加锁来完成的
   
   对于MVCC你需要掌握的点
4. 快照ReadView
   1. 可重复读和读已提交创建快照的时间点是不同的。可重复读是在当前事务开始前维护一张快照ReadView，而读已提交是事务每一次查询都重新创建一张快照ReadView。
   2. min_trix_id
   3. max_trix_id
   4. creator_tirix_id
   5. m_ids
5. 隐藏列trix_id：标记这当前修改这一行记录的事务Id
   1. 当ReadView的min_trix_id > 事务Id的时候，说明这个版本是可见的
   2. 当max_trix_id > trix_id的时候，说明这个是不可见的
6. 版本链和roll_point：当事务修改了数据后，就数据会记录在undo log中，通过roll point连接

日志

1. binlog：主从复制
2. undo log：用于MVCC中的版本链，实现原子性
   1. 版本链这个功能不加赘述
   2. 实现原子性这个功能：每一次执行事务同时会把回滚时需要的信息记录在undo log中
      1. 增加数据，记录对应的id，删除的时候直接删除
      2. 删除数据，记录删除数据，回滚的时候直接恢复
      3. 改数据，记录修改的数据，回滚的时候修复
3. redo log：用于实现持久性。断电恢复等
   1. Buffer Pool，在Mysql中，如果你要查数据，那么就从先读缓存，缓存中没有就读硬盘然后写会缓存。但是如果你要更新数据，更新完数据之后，这个数据并不会直接写入内存，而是会被标记成脏页，等待合适的时机写入内存（PageCache与刷盘的关系）。这就带来了一个问题，在内存中，数据不能持久化，断电就完了。
   2. 于是诞生了redo log来弥补这个问题，他记录了一系列的操作
   3. 可以看到redo log就是undo log的对立面
      1. undolog在乎的是事务提交前的数据
      2. redolog在乎的是事务提交后的数据
   4. redo log需要注意的点：
      1. redo log是顺序写入
      2. redo log的刷盘策略
         1. Mysql正常关闭
         2. redo log buffer空间占据一半
         3. 事务提交
         4. 后台一秒刷盘一次
4. binlog：binlog记录对数据库的增删改的动作
   1. 与redo log的区别在于：
      1. redo log是Innodb在引擎层实现的，而binlog是在server层实现的
      2. redo log和binlog文件格式不同。redo log记录的是物理上xxx位置发生了xxx事件。而binlog是逻辑上，对什么表执行了什么操作。
      3. binlog是追加写，写完创建新文件继续追加写。redo log是循环追加写
      4. binlog用于主从复制，redo log用于断电恢复这样的场景。
5. binlog的刷盘策略一般是事务提交的时候刷盘
6. Mysql的两阶段提交主要为了解决redo log和 binlog不一致的问题。
   1. redo log和binlog各有各的刷盘策略。但是当redo log写入内存而binlog没被写入（或者反之），就会有主库与从库数据不一致的问题。为了解决这个问题，所以采用了两阶段提交
   2. 分为prepare 和 commit两个阶段。

在Mysql的RR级别下能完全避免幻读吗

在Mysql的RR级别是不一定能避免幻读的，举个例子
事务A 查询id > 2的数据，事务B插入了一条id=5的数据。随后事务A去修改id=5的数据。注意。因为update、select、insert走的都不是快表，而是间隙锁的路子，所以，能找到id=5的数据，经过修改，trix_id变成事务A的事务id，再次查询数据就能查到了
再举个例子
事务A先用了快照读，读id>2的数据，事务B新增一条数据，事务A再使用当前读就会出现幻读

常用的SQL语句

SQL语句分别分为

DDL语句（类似于建表的一些语句，涉及到了数据库的定义）

1. 建表语句，关键点，key定义在下方，comment是必须的，建表之后要选择引擎和字符集

   create table tableName (
   	colName int/varchar... commment '',
   	primary key ...
   ) innoDB 、 utf-8
   

2. 修改表的属性

   alter table tableName modify colName  修改列的属性
   alter table tableName add colum colName 增加一列属性
   alter table tableName drop colum colName 删除一列属性
   alter table tableName change oldName newName 修改列名
   

3. 删除表

   drop table tableName
   

   truncate table tableName // 虽然会
   

DML语句（增删改查等一系列）

intert into tableName(colName,.....) value
insert into tableName values()

4. 修改

   update tableName set colName = 1 where
   

5. 删除

   delete from tableName where 
   

6. 增加索引的五种方法
   第一个：建表时自带。第二个：以修改表结构的语句alter table xxx 第三个 在已简历表的地方增加

alter table tableName add primary key(colName)
alter table tableName add unique key (colName)
alter table tableName add index indexName (colName)

create index indexName(colName) on tableName

7. 连接
   join == inner join，取的是两张表的交集
   left join = left outer join
   left join是左连接，以左表为主，查出来的结果显示左表的所有数据，然后右表显示的是和左表有交集部分的数据。
   right join是右连接，以右表为主，查出来的结果显示右表的所有数据，然后左表显示的是和右表有交集部分的数据。
   inner join是内连接，查出来的结果显示显示左表右表都有的数据。
   cross join是全连接，显示左表和右表的所有内容。
   上述的区别主要在于显示的内容不同。

8. SQL优化的思路

   1. 分表，垂直分表或者水平分表
   2. 首先要在where、order by涉及到的列上建立索引，提高查询速度
   3. 看看自己写的sql会不会出现索引失效的问题。比如用了like、or可能会引擎索引失效。
   4. 能用between就少用in，in会导致全表扫描
   5. 索引不是越多越好，索引提高select效率的时候也降低了增删改的维护成本
   6. 最好不要select * from xxx ，最好不要返回用不到的字段
   7. 尽量避免一次性查出大量数据，避免大事务，挺高并发性。

limit的优化

1. 使用索引覆盖扫描接上一次关联操作
2. limit的流程，比如limit 100,10。其实是把110条数据读出，返回最后10条数据

Mysql的读写分离

1. 什么是读写分离
   1. 让Master做数据的增删改
   2. 让Slave做数据的select
2. 优点在于写操作其实是很费时间的，所以让Master写，通过binlog做主从复制同步到slave。公司准备了第三方中间件来完成这个事情。

Innodb和Myisam

1. Innodb支持行锁Myisam不支持
2. Innodb支持事务Myisam不支持
3. Innodb聚簇索引就是数据文件本身，聚簇索引的叶子节点存放数据，而Myisam的底层存放的是指针，是地址
4. Innodb不能没有聚簇索引Myisam可以没有
   在读多写少的应用场景中可以使用Myisam

Mysql中死锁的部分

CASE 1:
事务1
update table t1 set name=‘xx’ where id = 1;
update table t1 set name=‘xxx’ where id = 2;

事务1
update table t1 set name=‘xx’ where id = 2;
update table t1 set name=‘xxx’ where id = 1;

这种情况就会导致死锁。事务一持有id=1记录的排它锁，请求id=2记录的排他锁，同理。事务二持有id=2的排它锁，请求id=1的排它锁

CASE 2:
存在外键的话
update t1 set name = ‘xxx’ where b_id=xx
这种情况会给b_id加一个共享读锁，在这种情况可能会引发死锁。

select update for加锁过程详解

innodb在索引层面引入了行锁，因此不能避开索引不谈。
一下就在唯一索引和普通索引上做文章
众所周知
等值查询时
值存在：唯一索引会当临键锁退化成记录锁，普通索引不会让临键锁退化成记录锁，反而会加一把间隙锁上去。
值不存在：唯一索引/普通索引会让临键锁退化成间隙锁

做范围查询的时候：
普通索引不会让临键锁退化，而唯一索引可能会让临键锁退化。

关于索引和临键锁的一些事情

先给结论

1. 唯一索引的等值查询，值存在，会退化成一把记录锁
2. 唯一索引的等值查询，值不存在，会退化成一把间隙锁
3. 普通索引的等值查询，值存在，（4,8]这把间隙锁存在，还会加上一把（8,16）的间隙锁
4. 值不存在，会退化成一把间隙锁
5. 范围查询，唯一索引，临键锁可能会退化成一把记录锁+间隙锁
6. 范围查询，普通索引，不会退化

关于索引失效的xxx事

1. 联合索引没有最左匹配
2. 左模糊匹配%like
3. 索引 or 没有索引
4. 用了一些函数比如说 len(a) = 1
5. 做了一些运算比如 a - 1 = 10
6. 隐式转换，比如varchar 类型的 id = 123
7. 索引建立的很没有效率，比如在sex这样的字段上加索引，可能会导致走全表扫描
8. 特殊的索引失效。当唯一索引中出现null值的时候，唯一索引可能会失效因为默认null != null
9. null可能会导致索引失效