数据库基础知识点整理

最新推荐文章于 2024-04-12 15:47:51 发布

Dora哒哒哒

最新推荐文章于 2024-04-12 15:47:51 发布

阅读量227

点赞数 1

分类专栏：数据库文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_41524366/article/details/107470306

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

SQL语句
存储引擎
索引
事务
锁机制
数据类型
补充

SQL语句

常用SQL语句（CRUD）

SQL语句的执行顺序

select–>from–>where–>group by–>having–>order by

实际的执行顺序是from–>where–>group by–>having–>select–>order by

连接查询

内连接

两张表进行连接查询的时候，只保留两张表中完全匹配的结果集。
左外连接

左表为主表，返回左表所有行，即使在右表中没有匹配的记录
右外连接

右表为主表，返回右表所有的行，即使在左表中没有匹配的记录
全连接

两张表进行连接查询时，返回左表和右表中所有没有匹配的行。

存储引擎

InnoDB

是MySQL（5.5版以后）默认的数据库引擎

MyISAM

是MySQL（5.5版之前）默认的数据库引擎，不支持事务和行锁，而且最大的缺陷就是崩溃后无法安全恢复。适合读密集的情况。

InnoDB和MyISAM比较

是否支持行锁：MyISAM只支持表锁，而Innodb支持行锁和表锁，但是默认为行锁
是否支持事务和崩溃后的安全恢复：MyISAM强调的是性能，每次查询具有原子性，执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供支持事务功能，具有崩溃修复能力。
是否支持外键：MyISAM不支持，而Innodb支持
是否支持MVCC：Innodb支持，应对高并发事务，MVCC比单纯的加锁更加高效
索引：MyISAM只有非聚簇索引，索引文件和数据文件是分离的，索引检索时，会先查找到索引所在叶子节点的data域值，然后将该值作为地址查找到数据文件对应的数据；而Innodb主索引是聚簇索引，数据保存在主索引中，如果是按照主索引检索，可以通过叶子节点查找到索引对应的数据行信息，如果按照辅助索引检索，需要进行两次查询，首先查询到主键值，再到主索引中查找相应的数据行。
行数（count）的运算：Innodb不保存表具体的行数，执行count*需要全表扫描；MyISAM用一个变量保存了整张表的行数，执行上述语句只需要输出这个变量，速度很快。

索引

索引的优点

能减少服务器数据扫描的行数
可以帮服务器避免临时表和排序表
可以将随机IO变成顺序IO

索引的分类

从物理存储角度：

聚簇索引和非聚簇索引
- 聚簇索引
  
  数据的物理存放顺序与索引顺序是一致的。主键B+树的叶子节点存储行数据，辅助B+树叶子节点存储主键值。一般情况下主键会默认创建聚簇索引，一张表只允许存在一个聚簇索引。
  
  根据主索引检索时，直接找到key所在节点即可取出数据；根据辅助索引检索时，会先找出主键值，再走一遍主索引（回表查询）。
- 非聚簇索引
  
  索引文件和数据文件是分离的，表数据存储顺序与索引顺序无关，主键和非主键B+树叶子节点存储的都是指向数据行的地址。
  
  索引检索时，首先按照B+树搜索算法搜索索引，如果指定的key存在，就会取出data的值，然后以data域的值作为地址查找数据文件对应的数据记录。
从数据结构角度：
- B+树索引
  
  是一种多路平衡查找树，一个节点可以放置多个元素，数据都存储在叶子结点，且叶子节点之间有链表结构连接。
- hash索引
  
  能以O（1）时间进行查找，但是失去了有序性。
  
  只支持精确查找，无法用于部分查找和范围查找；无法用于排序和分组。
从逻辑角度：

主键索引：是一种特殊的唯一索引，不允许有空值

普通索引或单列索引

多列索引（复合索引）

唯一索引或者非唯一索引

如何建立索引

参照一下规则建立索引：

索引并非越多越好，太多的索引会占用更多磁盘空间；
对于经常更新的字段尽量不要建立索引；对于经常查询的字段建立索引；
数据较少的字段可以不建立索引，可能遍历的索引的时间会比查询的时间更长；
在不同值较多的列上建立索引，不同值较少的字段，比如男女等字段无须建立索引，如果建立了索引不但不会提升效率，反而会严重降低数据的更新速度；
在经常需要排序或者分组的列上建立索引，如果排序的的列有多个，可以在这些列上建立联合索引。

什么情况索引会失效

索引列参与表达式计算
函数运算
以%开头的模糊查询
字符串不加单引号，或者与数字比较
查询条件中使用or
使用正则表达式

MySQL索引结构

B+树索引

与B树的比较

B树与B+树的不同之处在于：

B树非叶子节点也存储数据
叶子节点上无链表

B树在提高磁盘IO性能的同时，并没有解决元素遍历效率低下的问题。B+树只需要遍历叶子节点就可以实现整棵树的遍历。而且数据库中基于范围的查询是非常频繁的，如果使用B树，则需要做局部的中序遍历，坑需要跨层访问，效率太低。

与红黑树的比较

红黑树和AVL树都是存储在内存中才会使用的数据结构，平衡二叉树和红黑树因为每次插入删除数据都需要进行重新平衡，如果在内存中这个问题不是特别大，但是在磁盘中，性能开销比较大，因此主要应用在treemap中。

而B+树为多路平衡树，每个节点有多个元素，插入值可以在已有的节点上进行操作，而不用改变树的高度，从而大量减少重新平衡的次数，这种就比较适合作为数据库索引这种需要持久化在磁盘，同时经常查询和插入的应用中。

索引优化

覆盖索引

一个索引包含了（覆盖了）满足查询结果的数据就叫做覆盖索引。当能通过读取索引就能得到想要的数据，就不需要回表读取了。

事务

什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的四大特性（ACID）

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保要么全部完成，要么完成不起作用。
一致性（Consistency）：指事务执行前后，数据处于一种合法的状态。
隔离性（Isolation）：并发访问数据库的时候，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
持久性（Durability）：一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障，也不应该对其有任何影响。

MySQL中事务ACID实现原理

参考孤独烟：MySQL中事务ACID实现原理

原子性

利用Innodb的undo log（回滚日志）。

undo log记录了事务回滚需要的信息，当事务执行失败或者调用了回滚，可以利用undo log的信息将数据回滚到修改之前的样子。
一致性
- 数据库层面：通过原子性、隔离性和持久性来保证一致性，AID是手段，C是目的。
- 应用层面：通过代码判断数据库事务是否有效，然后决定回滚还是提交数据。
隔离性

利用锁和MVCC。
持久性

MySQL会将磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘，但如果此时突然宕机，内存中的数据就会丢失。

采用redo log。在数据修改的时候，不仅在内存中操作，还会在redo log上记录。当事务提交时候，会将redo log日志进行刷盘。当数据库宕机时，会将redo log中的内容恢复到数据库中，再根据undo log 和bin log来决定回滚数据还是提交数据。

并发事务带来哪些问题？

脏读

一个事务读取了另一个事务修改还未提交的数据。
丢失修改

指一个事务读取并修改一个数据时，另一个事务也在读取并修改了数据，那么第一个事务修改的结果就被丢失，成为丢失修改。
不可重复读

一个事务内两次读取到的数据是不一样的。
幻读

一个事务内多次查询返回的结果集不一样。在读取的前后另一个事务对数据进行了新增或者删除。

事务的隔离级别

读未提交

一个事务可以读取到另一个事务还未提交的数据。存在脏读，不可重复读和幻读。
读已提交

一个事务能读到另一个事务已提交的数据。可避免脏读，但是存在不可重复读和幻读。
可重复读(REPEATABLE READ)

InnoDB默认用了可重复读，在这种情况下，使用next-key locks解决幻读问题。

是快照读，不会任何锁，根本不能解决幻读问题。

select * from tx_tb where pId >= 1;

用上了lock in share mode，解决了幻读问题。

select * from tx_tb where pId >= 1 lock in share mode;
可串行化

在该隔离级别下，所有的select语句后面都加上了lock in share mode，无论如何查询都会使用next-key locks。所有的select操作者均为当前读。

锁机制

参考博客孤独烟：select加锁分析

锁类型

共享锁（S锁）：当事务T1对一个数据A加共享锁，另一个事务T2可以读取数据A，但是不可以修改数据A。
排它锁（X锁）：当事务T1对一个数据A加排它锁，另一个事务T2不可以读取数据A，也不可以修改数据A。
意向共享锁（IS锁）：一个事务在获取S锁之前，一定会先在所有的表上加IS锁。
意向排他锁（IX锁）：一个事务在获取X锁之前，一定会先在所有的表上加IX锁。

意向锁存在的目的：

事务T想要对表A加锁，需要检测是否有其他事务在对表A或者表A某一行加了锁，如果对每一行都进行检测是很耗时的。

引入意向锁，就只需要检测是否有其他事务对表A加了意向锁。

加锁算法

Record Locks：行锁。该锁是对索引记录加锁，而不是对行加锁，所以Innodb的行锁最终落在聚簇索引上。
Gap Locks：对索引的间隙加锁，防止其他事务插入数据。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁。
Next-Key Loxks：Record Locks+Gap Locks

多版本并发控制（MVCC）

参考：MySQL隔离级别和MVCC

MVCC就是在RR和RC的隔离级别下，使用select操作时访问数据记录版本链的过程，这样子可以是不同事务的读写和写读并发执行，提高系统性能。

RR和RC两个隔离级别的一个很大不同在于生成ReadView的时机不同，RC在每一次进行select操作前都会生成一个readview，而RR只在第一次select前生成一个readview，之后的查询操作都重复这个readview就好了。

select的快照读和当前读

select * from table where id = ?;

执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为Serializable中不成立，后面我会补充。）
那么，执行

select * from table where id = ? lock in share mode;

会对读取记录加S锁 (共享锁)，执行

select * from table where id = ? for update

会对读取记录加X锁 (排他锁)。那么

加的是表锁还是行锁呢？

针对这点，我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示:

Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！
Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据!
Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生！从该级别才开始加入间隙锁。
Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

之所以能够锁表，是通过行锁+间隙锁来实现的。那么，RU和RC都不存在间隙锁，这种说法在RU和RC中还能成立么？
因此，该说法只在RR和Serializable中是成立的。如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行！

数据类型

字符串

主要有CHAR和VARCHAR两种类型，一种是定长的，一种是变长的。

VARCHAR这种变长类型能够节省空间，因为只需要存储必要的内容。

varchar(50)和varchar(200)存储hello所占空间一样，但是后者在排序时会消耗更多内存。

整形

TINYINT，SMALLINT，MEDIUNINT，INT，BIGINT分别使用8,16,24,32,64位存储，一般情况下越小的列越好。

INT(11)中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说时没有意义的。

浮点型

FLOAT和DOUBLE为浮点类型，DEMICAL位高精度小数类型。CPU原生支持浮点运算，但是不支持DEMICAL类型的计算，因此DEMICAL的计算比浮点类型需要更高的代价。

时间类型

datetime：占8个字节，存储的时间范围为1000-01-01 – 9999-12-31。虽然存储的时间范围大，但是存储的信息不带时区信息，如果改变数据库的时区，该项的值自己不会发生变更。
timestamp：占4个字节，存储的时间范围为1970-01-01 – 2038-01-19。2038年以后的时间无法用timestamp类型存储，但是存储的信息带时区信息，如果改变数据库的时区，该项的值自己会发生变更。

尽量使用timestamp，因为它比datetime效率更高。

补充

drop、delete、truncate的区别

drop：删除表的内容和结构，释放空间，没有备份的时候要慎用
truncate：删除表的内容，表的结构还在，可以释放空间，没有备份表之前要慎用
delete：删除表的内容，不删除结构，不释放空间，可通过回滚恢复数据

从操作效率上来看：drop>truncate>delete

count(*) count(1) count(主键id) count(字段)区别

count(*)：不会把字段取出来，专门做了优化，包含null行

count(1)：不会把字段取出来，包含null行

count(主键id)：把字段取出来，判断不为null的，按行累加

从执行效率来说：count(*)>count(1)>count(主键id)

数据库三范式

第一范式：强调列的原子性，即数据库表的每一列都是不可分割的原子数据项
第二范式：要求实体的属性完全依赖主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式：任何非主属性不依赖其他非主属性

唯一索引和普通索引如何选择

当一列里面的数据是唯一的时候，需要建立索引，选唯一索引

虽然唯一索引影响了insert速度，但是这个速度损耗可以忽略不计，但是提高查找速度是很明显的。

唯一索引的插入速度比普通索引慢

在进行非聚簇索引的插入时，先判断索引页是否在内存中。如果在，直接插入；如果不在，就先放入缓存区中，再以一定的频率插入。

但是唯一索引无法利用这个缓存区，因为为了保证唯一性，需要将数据加载进内存才能判断是否违反唯一性约束。
普通索引的查找速度比普通索引快
- 普通索引在找到满足条件的第一条记录之后，还需要判断吓一跳记录，直到第一个不满足条件的记录出现
- 唯一索引在找到满足条件的第一条记录之后，直接返回，不用判断下一条记录了

一条SQL语句在MySQL中如何执行的（MySQL架构）

MySQL主要分为Server层和引擎层，Sever层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（bin log），这个日志模块是所有执行引擎共用的，redo log只有InnoDB才有。

连接器：身份认证和权限相关

查询缓存：执行查询语句的时候，会先查询缓存

分析器：没有命中缓存的话，就会经过分析器，分析SQL语句干嘛的，语法是否正确

优化器：按照MySQL认为最优的方案去执行

执行器：执行语句，然后从存储引擎返回数据

SQL查询流程：权限校验–>查询缓存–>分析器–>优化器–>权限校验–>执行器–>引擎
SQL更新流程：分析器–>权限校验–>执行器–>引擎–>redo log prepare–>bin log–>redo log commit

一个SQL执行很慢的可能原因

分两种情况来讨论：

大多数情况很正常，偶尔很慢
- 数据库在刷新脏页，数据库在更新数据的时候，会同步记录redo log，等空闲的时候把redo log里面的日志同步到磁盘。
  
  发生脏页刷新的场景：redo log写满了，内存不够用了，MySQL正常关闭的时候
- 执行的时候遇到锁，如表锁、行锁。可以通过show processlist来查看当前状态
一直执行很慢
- 没有用上索引：该字段没有索引；对字段进行运算、函数操作导致没法使用索引
- 数据库选错了索引