SQL学习分享
数据库基础
建表的原则(三大范式)
- 1NF:保证原子性。比如地址:杭州市江干区xxx这样的地址就不是原子性的,是可以切割的。
- 2NF:保证各列完全依赖主键。比如
- (学生,课程) -> 高考成绩。成绩完全依赖于学生和课程
- (学生,课程,学校)-> 高考成绩。这就不是完全依赖
- 3NF:消除传递依赖:可以根据非主键的列,找到与其他列的对应关系。
drop、delete、truncate
drop:删除一张表,删除数据并且删除表的数据结构
delete:删除的是行数据
truncate:是先把表删除,再创建一张完全一样的表。
DDL、DML
DDL就是建表,对表的数据结构做出改变的操作
DML增删改查
关系型数据库和非关系型数据库
关系型数据库:表都是由结构的。
非关系型数据库:以元组为单位,一个元组中有的数据不固定
Mysql相关
一条SQL的执行顺序
- from
- where
- group by xxx having
- 聚合函数
- select
- order by
- limit
执行一条select
-
连接器:建立连接,管理连接、校验用户身份;
-
查询缓存:查询语句如果命中查询缓存则直接返回,否则继续往下执行。
-
解析 SQL,通过解析器对 SQL 查询语句进行词法分析、语法分析,然后构4. 建语法树,方便后续模块读取表名、字段、语句类型;
执行 SQL:执行 SQL 共有三个阶段: -
预处理阶段:检查表或字段是否存在;将 select * 中的 * 符号扩展为表上的所有列。
-
优化阶段:基于查询成本的考虑, 选择查询成本最小的执行计划;
-
执行阶段:根据执行计划执行 SQL 查询语句,从存储引擎读取记录,返回给客户端;
-
行锁须知
- 对于临键锁,如果发现唯一索引会退化成记录锁
- Innodb支持的行锁是作用在索引上的,sql没有执行索引,Innodb都是使用表锁的。Myisam是不支持行锁的。
-
悲观锁:指的是数据在被处理的过程中,处于被锁定的状态。排他锁,共享锁都是悲观锁的实现
-
乐观锁:乐观锁是不锁定数据,只去判断是否发生了冲突。版本号、时间戳实现
-
Mvcc:三者是并列关系哦
Mysql索引
索引是建立在引擎的基础上的,所以不同的引擎所对应的索引的机制是不同的
常用的引擎是Innodb
索引的分类
- 从索引的功能上来说可以分为
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 从索引的物理存储来讲,索引可以分为
- 聚簇索引
- 非聚簇索引
- 从组成索引的字段个数来分,可以分为
- 单列索引
- 联合索引
针对这些索引我们展开讲解
功能索引
- 主键索引
- 主键索引是唯一索引,唯一索引不一定是主键索引
- 主键索引不能为空
- 在Innodb引擎中,
- 主键索引是聚簇索引,非主键索引是非聚簇索引。
- 非主键索引的数据节点引用了主键索引。
- 在Innodb引擎中,如果用户没有设置主键,则会指定一个非空的唯一索引作为主键,如果没有非空的唯一索引,则选择自己生成一列作为主键索引。
- nnodb的主键索引文件其实就是数据文件。在聚簇索引里面装载了所有的数据)
- 一张表中,只能有一个主键索引
- 可以被外键引用,外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。
- 主键索引最好要自增,因为如果主键是自增的,一般来说说插入数据就是追加插入,如果不是自增的,就会出现数据随机插入。这可能会引起页分裂等问题,造成性能损失
- 唯一索引:
- 唯一索引不一定是主键,主键一定是唯一索引。
- 唯一索引支持空值。
- 唯一索引比普通索引定位数据更快一些,在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回
- 唯一索引更新会慢一些,因为不能使用change buffer,唯一索引的更新会比普通索引慢一些。(其实change buffer也就普通索引能用)
- change buffer。当数据更新的时候,对于普通索引,如果目标页在内存中(mysql都是以页为单位存储数据的)直接更新。唯一索引,如果目标也在内存中,就判断唯一性约束然后更新(判断唯一性约束就必须把页读进内存,这也是为什么mysql不能使用change buffer的原因)。而普通索引,就把更新的操作写进change buffer,等待下一次页被读入内存再更新,这就大大提高了IO。
- 普通索引:就是普通索引
- 全文索引:主要是针对字符串类型数据提升查询速率
从存储结构来看索引的存储
可以这么理解,Mysql是以页为单位存储数据的,而B+ Tree是逻辑上的结构,可以理解为一个页就是一个node,一般来说页是16k大小。理解这一点很重要
在Innodb中(myisam专门讲)
- 聚簇索引(在Innodb中,必须存在聚簇索引):
- 选择主键作为聚簇索引,如果主键不存在,则选择非空的唯一索引作为聚簇索引,如果不满足,则自己生成一列作为主键索引。
- 聚簇索引文件本身就是数据文件。聚簇索引叶子节点存放的就是所有数据这个和Myisam是不同的。
- 聚簇索引是唯一索引
- 非聚簇索引
- 叶子节点存放的是主键值,索引带来了两个问题。回表查询和索引覆盖。
- 非聚簇索引可以有很多个
回表查询和覆盖索引
回表查询:因为非聚簇索引的叶子节点存放的是主键值,所以通常需要拿主键值再返回聚簇索引再查询一次,这就是回表查询。
覆盖索引:覆盖索引就是解决回表查询问题的,可以从两个角度去做覆盖查询这件事
1. 尽量在SQL中使用具体字段,少用*
2. 创建一个索引,比如要查询,姓名和身份证号,就可以针对姓名和身份证号做一个索引,这样就避免了回表查询(因为非聚簇索引中已经包含了)
从构建索引的数量来看
- 单列索引:由一个数据字段组成的索引
- 联合索引:由多个字段组成的索引
- 最左匹配原则:比如创建一个联合索引(a,b,c),其实底层数据存储的时候是,在a有序的前提下b是有序的,在ab有序的情况下c是有序的
SQL优化
- Mysql实现分布式锁
- 创建一张表,如果用到了某一个资源,就插入一条数据,等到资源被释放了,就把这条记录删除,
- 解锁的过程只要吧对应的进程Id给kill掉就行。
- 数据库查询优化
- 加索引
- 分页
- 分表:
- 水平分表(按照主键递增的规律或者Hash%来粉白)
- 垂直分表(把用的多的数据和用的不多的数据(或者较大的数据)分开)
- Mysql分页:
- 还要分页再优化,那就是懒加载模式了
- 索引的选择性:
区分度越大的做索引越合适,所以性别没必要做索引
Mysql建立索引的标准
建立索引的好处
- 最大的好处当然是提升查询效率
- 建立唯一性索引,保证一行数据的唯一性
- 提升分组的效率
建立索引的坏处
- 创建销毁索引的开销
- 增加额外的空间去存储索引
- 维护索引,数据的增删改都需要维护索引。
建立索引的标准
需要建立索引的时候
什么时候应该建立索引,应该针对哪些字段建立索引
7. 针对频繁用于条件查询的字段,where 的条件,分组的条件 group by。这样可以提升查询效率,提升分组效率。
8. 表达数据的唯一性的时候,建立唯一索引
不需要建立索引的时候
9. 不应该针对频繁需要改动的数据建立索引比如余额
10. 不应该针对区分度不高的字段加索引。比如男女
11. 针对有空值的列不建议建立索引
1. NULL占据空间的存储空间比较大
2. Mysql在做语法优化的时候对于NULL值的处理会很麻烦,存储NULL值的也会占用更大的空间
Mysql 索引的物理存储
在Mysql中索引的物理存储是选择用B+树的,为什么呢?
B+树
可以从图中看出,在Innodb引擎中Mysql是使用了B+树作为索引的。为什么呢
-
采用B+树可以采用二分查找的方式,效率比较高
-
B+树只有在叶子节点才会存放数据,其他节点存放索引。这使其能够深度更小,宽度更大,能减少底层IO
-
B+树在底层的叶泽节点采用了双向链表,支持范围查询
-
B+树有大量冗余节点,插入删除的时候对总体结构影响不是特别大
-
B+树 Vs B树
- B树的范围查询性能弱于B+树
- B树的节点存放数据与索引,所以相同数据量B树更深一点
- B树的查询需要把数据部分也读入进内存,而本质上我们只是想比较索引,所以会造成一定的资源浪费。
-
B+树 Vs Hash
- Hash不支持范围查找,因为值相同的两个值,可能哈希后的位置天差地远,所以不支持范围查找
- 不支持模糊查询
- Hash的应用场景太少了
索引失效
抓住一个核心,索引存放的是列的原始值,一旦表达式结果不是列的原始值,就不会走索引
- 左模糊匹配
- 联合索引为采用最左匹配
- 隐式转换,varchar类型的数据转换成 phone = 123 phone被转换成int类型不走索引
- 使用or可能致使索引失效:有一列没有走索引
- 对索引使用了函数
- 对索引做了表达式判断 age - 1 = 10
- 使用了=null
Mysql中的锁
锁的分类
- 全局锁:会锁住数据库中所有的表
- 表级别锁:
- 表级共享锁
- 表级排它锁
- 意向锁
- 意向排他锁
- 意向共享锁
- 元数据锁
- Auto_INC锁
- 行锁:
- 记录锁
- 行级排它锁
- 行级共享锁
- 间隙锁
- 临键锁 = 记录锁 + 间隙锁
- 记录锁
表级别锁
- 意向锁:诞生的目的是表现当前这张表中某些记录是否被加了锁
- 从上图中就能得知,意向锁和意向锁彼此之间是兼容的。
- 意向锁兼容行级别锁
- 除了意向共享锁和表级别共享锁兼容,其他情况都不兼容
- 表级别锁:
- 在加表级别锁的时候,先会去获得对应的意向锁。比如表级别排它锁,要先加上意向表级别排它锁,而这个是不兼容的。
- 读读兼容,读写不兼容,写写不兼容
- 元数据锁:元数据锁存在意义是防止在执行CRUD的时候对表的结构做了变更
- AUTO_INC锁:实现主键自增的锁
行级别锁
悲观锁:指的是数据在被处理的过程中,处于被锁定的状态。排他锁,共享锁都是悲观锁的实现
乐观锁:乐观锁是不锁定数据,只去判断是否发生了冲突。版本号、时间戳实现
只有InnoDB支持行级别锁,Myisam是不支持的!!! InnoDB的锁是在索引上加锁,不是在列上加锁,很多全表搜表不走索引,这个锁加了白加
之前一直没解决的问题,在MVCC的时候,明明已经读取了数据的快照,还会有幻读问题。
- 在Mysql中,在可重复读的隔离级别下有两种方法解决幻读问题
- 因为在Innodb引擎下,普通的select是不加锁的,通过MVCC的快照,因为读取的是快照所以新增的数据其实是看不到的
- 但是加锁的查询语句比如说 select xxx fro for update 这种情况下是通过 临键锁来避免幻读问题的。通过临键锁,不进增加的幻读能解决,删除的幻读也能解决!
临键锁的退化
在Mysql中,加锁的基本单位是临键锁。临键锁相当于是记录锁与间隙锁的组合,但是在一些情况下会退化成记录锁或者间隙锁。记录锁是前开后闭,而间隙锁是前开后开
-
在唯一索引等值查询的情况下:
- 有记录存在:退化成记录锁
- 没有记录存在:退化成间隙锁
-
在普通索引等值查询情况下:
- 有记录存在:不仅不会变,还会额外加一把间隙锁,锁住当前行到最近一行不符合条件记录之间的空间
- 记录不存在:退化成间隙锁
-
范围查询
- 唯一索引有可能会退化
- 普通索引:不会退化
update 和 delete 的where条件没有携带索引
Innodb实现行锁,是在索引上加锁,不是数据列上加锁
已知
update、delete、select update,这类加锁的SQL语句,都会给记录加上临键锁,如果where后面跟的条件没有携带索引,那么将会走全表扫描,那么就会给表中的所有数据加上临键锁,严重一点甚至会导致业务崩溃。
insert是通过隐式锁来完成的
数据库的死锁问题
- 设置事务的等待时间,超时就会回滚释放资源
- 开启死锁检测:会根据深度搜索,如果判断成环就会根据优先级/锁的数量等因素回滚某一个事务
Mysql 事务
Innodb默认的事务隔离级别是可重复读
- 事务的四个特性
- 原子性:通过undolog完成
- 隔离性:通过MVCC完成
- 一致性: 通过其他三个特性完成
- 持久性:通过redo log完成
- 数据库的隔离级别和会出现的问题
- 读未提交:会出现脏读的问题
- 读已提交:会出现不可重复读的问题
- 可重复读:会出现幻读的问题
- 串行化:事务都是一件一件完成,并发性很弱,一般不适用。
- 读未提交:在这个级别下,数据库的所有操作都是公开的。事务A做了什么改动,事务B能够立刻知晓。脏读,当事务A修改了某个数据的值,并且事务B把这个数据读取了,当A发生错误回滚之后,B读到的数据就是脏数据。
- 读已提交:在这个级别下,事务的操作,只有事务被提交了,才会被公开。这个隔离级别解决了脏读问题。但是带来了不可重复读的问题。比如事务A、事务B同时读取某一个数据,事务A对数据修改并且提交,事务B再次读取这个数据,就会出现前后两次数据读取不一致的问题。
- 可重复读:这个是Mysql默认的隔离范围,在这个隔离级别下,事务启动时到结束,看到的数据都是相同的
- 串行化:事务的操作是串行的。
MVCC
- 读未提交状态,直接读取到最新的数据就好了,不需要额外的操作。
- 读已提交和可重复读都是通过MVCC来完成的
- 串行化是通过加锁来完成的
对于MVCC你需要掌握的点 - 快照ReadView
- 可重复读和读已提交创建快照的时间点是不同的。可重复读是在当前事务开始前维护一张快照ReadView,而读已提交是事务每一次查询都重新创建一张快照ReadView。
- min_trix_id
- max_trix_id
- creator_tirix_id
- m_ids
- 隐藏列trix_id:标记这当前修改这一行记录的事务Id
- 当ReadView的min_trix_id > 事务Id的时候,说明这个版本是可见的
- 当max_trix_id > trix_id的时候,说明这个是不可见的
- 版本链和roll_point:当事务修改了数据后,就数据会记录在undo log中,通过roll point连接
日志
- binlog:主从复制
- undo log:用于MVCC中的版本链,实现原子性
- 版本链这个功能不加赘述
- 实现原子性这个功能:每一次执行事务同时会把回滚时需要的信息记录在undo log中
- 增加数据,记录对应的id,删除的时候直接删除
- 删除数据,记录删除数据,回滚的时候直接恢复
- 改数据,记录修改的数据,回滚的时候修复
- redo log:用于实现持久性。断电恢复等
- Buffer Pool,在Mysql中,如果你要查数据,那么就从先读缓存,缓存中没有就读硬盘然后写会缓存。但是如果你要更新数据,更新完数据之后,这个数据并不会直接写入内存,而是会被标记成脏页,等待合适的时机写入内存(PageCache与刷盘的关系)。这就带来了一个问题,在内存中,数据不能持久化,断电就完了。
- 于是诞生了redo log来弥补这个问题,他记录了一系列的操作
- 可以看到redo log就是undo log的对立面
- undolog在乎的是事务提交前的数据
- redolog在乎的是事务提交后的数据
- redo log需要注意的点:
- redo log是顺序写入
- redo log的刷盘策略
- Mysql正常关闭
- redo log buffer空间占据一半
- 事务提交
- 后台一秒刷盘一次
- binlog:binlog记录对数据库的增删改的动作
- 与redo log的区别在于:
- redo log是Innodb在引擎层实现的,而binlog是在server层实现的
- redo log和binlog文件格式不同。redo log记录的是物理上xxx位置发生了xxx事件。而binlog是逻辑上,对什么表执行了什么操作。
- binlog是追加写,写完创建新文件继续追加写。redo log是循环追加写
- binlog用于主从复制,redo log用于断电恢复这样的场景。
- 与redo log的区别在于:
- binlog的刷盘策略一般是事务提交的时候刷盘
- Mysql的两阶段提交主要为了解决redo log和 binlog不一致的问题。
- redo log和binlog各有各的刷盘策略。但是当redo log写入内存而binlog没被写入(或者反之),就会有主库与从库数据不一致的问题。为了解决这个问题,所以采用了两阶段提交
- 分为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语句(类似于建表的一些语句,涉及到了数据库的定义)
- 建表语句,关键点,key定义在下方,comment是必须的,建表之后要选择引擎和字符集
create table tableName ( colName int/varchar... commment '', primary key ... ) innoDB 、 utf-8
- 修改表的属性
alter table tableName modify colName 修改列的属性 alter table tableName add colum colName 增加一列属性 alter table tableName drop colum colName 删除一列属性 alter table tableName change oldName newName 修改列名
- 删除表
drop table tableName
truncate table tableName // 虽然会
DML语句(增删改查等一系列)
intert into tableName(colName,.....) value
insert into tableName values()
- 修改
update tableName set colName = 1 where
- 删除
delete from tableName where
- 增加索引的五种方法
第一个:建表时自带。第二个:以修改表结构的语句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
-
连接
join == inner join,取的是两张表的交集
left join = left outer join
left join是左连接,以左表为主,查出来的结果显示左表的所有数据,然后右表显示的是和左表有交集部分的数据。
right join是右连接,以右表为主,查出来的结果显示右表的所有数据,然后左表显示的是和右表有交集部分的数据。
inner join是内连接,查出来的结果显示显示左表右表都有的数据。
cross join是全连接,显示左表和右表的所有内容。
上述的区别主要在于显示的内容不同。 -
SQL优化的思路
- 分表,垂直分表或者水平分表
- 首先要在where、order by涉及到的列上建立索引,提高查询速度
- 看看自己写的sql会不会出现索引失效的问题。比如用了like、or可能会引擎索引失效。
- 能用between就少用in,in会导致全表扫描
- 索引不是越多越好,索引提高select效率的时候也降低了增删改的维护成本
- 最好不要select * from xxx ,最好不要返回用不到的字段
- 尽量避免一次性查出大量数据,避免大事务,挺高并发性。
limit的优化
- 使用索引覆盖扫描接上一次关联操作
- limit的流程,比如limit 100,10。其实是把110条数据读出,返回最后10条数据
Mysql的读写分离
- 什么是读写分离
- 让Master做数据的增删改
- 让Slave做数据的select
- 优点在于写操作其实是很费时间的,所以让Master写,通过binlog做主从复制同步到slave。公司准备了第三方中间件来完成这个事情。
Innodb和Myisam
- Innodb支持行锁Myisam不支持
- Innodb支持事务Myisam不支持
- Innodb聚簇索引就是数据文件本身,聚簇索引的叶子节点存放数据,而Myisam的底层存放的是指针,是地址
- 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在索引层面引入了行锁,因此不能避开索引不谈。
一下就在唯一索引和普通索引上做文章
众所周知
等值查询时
值存在:唯一索引会当临键锁退化成记录锁,普通索引不会让临键锁退化成记录锁,反而会加一把间隙锁上去。
值不存在:唯一索引/普通索引会让临键锁退化成间隙锁
做范围查询的时候:
普通索引不会让临键锁退化,而唯一索引可能会让临键锁退化。
关于索引和临键锁的一些事情
先给结论
- 唯一索引的等值查询,值存在,会退化成一把记录锁
- 唯一索引的等值查询,值不存在,会退化成一把间隙锁
- 普通索引的等值查询,值存在,(4,8]这把间隙锁存在,还会加上一把(8,16)的间隙锁
- 值不存在,会退化成一把间隙锁
- 范围查询,唯一索引,临键锁可能会退化成一把记录锁+间隙锁
- 范围查询,普通索引,不会退化
关于索引失效的xxx事
- 联合索引没有最左匹配
- 左模糊匹配%like
- 索引 or 没有索引
- 用了一些函数比如说 len(a) = 1
- 做了一些运算比如 a - 1 = 10
- 隐式转换,比如varchar 类型的 id = 123
- 索引建立的很没有效率,比如在sex这样的字段上加索引,可能会导致走全表扫描
- 特殊的索引失效。当唯一索引中出现null值的时候,唯一索引可能会失效因为默认null != null
- null可能会导致索引失效