数据库知识不完全总结--春招实习

yvonneit
615 阅读20分钟

基本SQL

DDL、DML和DCL

  • DDL：数据定义语言，主要用于定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作，如CREATE、ALTER、DROP，TRUNCATE
  • DML：数据操作语言，用于处理数据，如SELECT（查询）、INSERT（添加）、UPDATE、DELETE、CALL、EXPLANIN PLAN、LOCK TABLE
  • DCL：数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如COMMIT、SAVEPOINT、SET TRANSACTION

MySQL服务器的逻辑架构分为几层

  • 第一层：服务层(为客户端服务): 为请求做连接处理，授权认证，安全等。
  • 第二层：Mysql核心服务层：主要提供，查询解析、分析、优化、缓存以及内置函数，跨存储引擎功能（存储过程、视图、触发器）
  • 第三层：存储引擎层，负责数据的存储和提取

20190302221137.png

MySQL常见的日志种类？redo、undo、binlog 日志的作用

二进制日志（binlog） 错误日志（errorlog） 慢查询日志（slow query log） 一般查询日志（general log）

Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback；

Redo日志记录某数据块被修改后的值，可以用来恢复未写入data file的已成功事务更新的数据

连接查询和子查询的比较

子查

mysql中in 和exists 区别

drop、delete、truncate 比较

  • truncate用于清除表内所有数据，delete如果不带where也会删除指定表的所有数据。但是truncate 和 delete 只删除数据不删除表的结构。
  • drop会将表删除，同时表的约束、触发器(trigger)、索引(index)也会删除。但依赖于该表的存储过程和函数会保留，但会变成无效
  • delete 操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。 truncate、drop 操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger

select for update

什么是视图？什么时候更新视图

视图是一种虚拟存在的表

数据库自增主键可能的问题

SQL慢查询的优化

数据结构

B+树

事务

什么是事务

事务就是一组原子性的SQL语句，或者说一个独立的工作单元，事务内的语句要么全部执行成功，要么全部执行失败。

事务的特性

  1. 原子性（Atomicity）：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
  2. 一致性（Consistency）：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。
  3. 隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其它事务是不可见的。
  4. 持久性（Durability）：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志来保证持久性。

只有满足一致性，事务的执行结果才是正确的。

在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。

在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。

事务满足持久化是为了能应对数据库崩溃的情况

在并发环境下，数据库可能出现的并发一致性问题

  1. 脏读（读取未提交数据）：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
  2. 不可重复读（前后多次读取，数据内容不一致）：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同
  3. 幻读（前后多次读取，数据总量不一致）：T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

产生并发不一致主要原因是破坏事务的隔离性。解决方法是通过并发控制（锁）来保证隔离性。

不可重复读和幻读的区别

  1. 不可重复读是读取了其他事务更改的数据，针对update操作 解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

  2. 幻读是读取了其他事务新增的数据，针对insert操作 解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

Mysql的锁粒度

  • 表级锁：Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁
  • 行级锁：Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁。
    • Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
    • Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
    • Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

MyISAM采用表级锁，InnoDB支持行级锁和表级锁,默认为行级锁

MySQL锁分类：共享锁（S锁）和排他锁（X锁），意向锁

表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X）

  • 共享锁（Share Locks，简记为S锁）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。

    共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

  • 排它锁（(Exclusive lock,简记为X锁)）又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

即加S锁后，其他事务可以加S锁但不能加X锁，但只能读数据。加X锁，其他事务不能加锁，加X锁后可以读写数据

意向锁：

意向锁只有innodb引擎支持，是一种表锁。

  • 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）。在获取某些行的 S 锁之前必须获得IS锁
  • 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）。事务要获取某些行的 X 锁，必须先获得表的 IX 锁。

意向锁是引擎自己维护的，用户无法操作意向锁

意向锁不会与行级的共享 / 排他锁互斥!!

意向锁存在的意义：如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次。这样很耗时。

通过意向锁可以解决问题，意向锁会与普通的排他 / 共享锁（这里的锁指表锁）互斥。在事务T检测到有其它事务持有意向排他锁，则对该表加锁就会被阻塞，而无需检测每一行。

意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁 / 排他锁 互斥。

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

参考：juejin.cn/post/684490…

MySQL的事务隔离级别

  • 未提交读（READ UNCOMMITTED）：最低的隔离级别，事务中的修改，即使没有提交，对其它事务也是可见的。可能会导致脏读、幻读或不可重复读
  • 提交读（READ COMMITTED）：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。可以阻止脏读，但是幻读或不可重复读仍有可能发生
  • 可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样数据的结果是一样的。可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • 可串行化（SERIALIZABLE）：强制事务串行执行。该级别可以防止脏读、不可重复读以及幻读

MySQL的MVCC含义，是如何实现的

MVCC指多版本控制，是为了应对高并发事发事务。Innodb引擎支持MVCC，MVCC只在 READ COMMITTED(提交读) 和 REPEATABLE READ（可重复读） 两个隔离级别下工作。

MVVC是

Mysql索引

索引是什么？作用？为什么索引能提高查询速度？索引的缺点

索引（Index）是帮助MySQL高效获取数据的数据结构，也就是说索引是一种数据结构。作用是协助快速查询、更新数据库表中数据

为什么能提高查询速度:

MySQL在存储数据是页为基本数据结构的，各个数据页可以组成一个双向链表，每个数据页中的记录又可以组成一个单向链表。如果不使用索引在查找时需要先遍历双向链表，找到所在的页从所在的页内中查找相应的记录:如果不是根据主键查询，就只能遍历所在页的单链表

通过索引可以将无序的数据变成有序，可以通过查找树来快速定位到记录所在的页。B+树索引就是为记录建立B+树来提高查询的速度

索引的缺点：

索引需要占物理和数据空间。

如果使用的是B+树索引，B+树是一颗平衡树，如果我们对这颗树增删改的话，那肯定会破坏原有的数据结构。要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度。

所以如果表经常进行插入、删除、修改，就不适合适用索引

数据库有哪些索引？原理是什么？

  1. 普通索引 :这是最基本的索引类型，而且它没有唯一性之类的限制
  2. 唯一索引:这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所值都只能出现一次，即必须唯一。
  3. 主键索引：特殊的唯一索引，不允许有空值
  4. 全文索引:MySQL从3.23版开始支持全文索引和全文检索。在MySQL中，全文索引索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。
  5. 组合索引：复合索引指多个字段上创建的索引，遵循”最左前缀“原则

另一种分类：从数据结构方面

  1. B+Tree 索引：大多数 MySQL 存储引擎的默认索引类型，
  2. 哈希索引：哈希索引能以 O(1) 时间进行查找，但是失去了有序性：无法用于排序与分组；只支持精确查找，无法用于部分查找和范围查找。InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找
  3. 全文索引：MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引
  4. 空间数据索引：MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储

原理：索引是数据结构，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树，或者hash。MySQL就普遍使用B+Tree实现其索引结构。

MySQL索引为什么要用B+树实现

聚簇索引和非聚簇索引（二级索引）区别

  • 聚簇索引:以主键创建的索引,以主键值的大小为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列
  • 二级索引:以自定义的列的大小为页和记录的排序规则，在叶子节点处存储的记录内容是列 + 主键。然后再根据主键查询数据（回表操作）。非聚簇索引在创建是可以是多列的

如果索引包含所有满足查询需要的数据的索引成为覆盖索引，不需要回表操作。

如索引(username,age)，查询数据的时候：select username , age from user where username = 'Java3y' and age = 20。明显查询时会用索引，且需要的数据都在索引中，所以不再需要回表。

hash索引

索引最左匹配原则是什么

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

存储引擎

Mysql常见的存储的引擎

  • innodb引擎：默认的事务型引擎，支持行锁，采用MVCC来支持高并发，有可能死锁 支持事务，支持外键，支持崩溃后的安全恢复
  • MyISAM存储引擎：拥有全文索引、压缩、空间函数，不支持事务和行级锁，不支持外键，崩溃后无法安全恢复，对于不会进行修改的表，支持 压缩表
  • MEMORY存储引擎
  • Merge存储引擎

Innodb和MyISAM的对比

MyISAM特点:

  • 不支持行锁(MyISAM只有表锁)，读取时对需要读到的所有表加锁，写入时则对表加排他锁；
  • 不支持事务
  • 不支持外键
  • 不支持崩溃后的安全恢复
  • 在表有读取查询的同时，支持往表中插入新纪录
  • 支持BLOB和TEXT的前500个字符索引，支持全文索引
  • 支持延迟更新索引，极大地提升了写入性能
  • 对于不会进行修改的表，支持 压缩表 ，极大地减少了磁盘空间的占用

InnoDB: mysql 5.5之后默认的引擎

  • 支持行锁，采用MVCC来支持高并发，有可能死锁
  • 支持事务
  • 支持外键
  • 支持崩溃后的安全恢复
  • 不支持全文索引

MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。据库做主从分离的情况下，经常选择MyISAM作为从库的存储引擎

MySQL优化

水平切分和垂直切分

  • 水平切分：水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力

    水平切分策略： + 哈希取模：hash(key) % N； + 范围：可以是 ID 范围也可以是时间范围； + 映射表：使用单独的一个数据库来存储映射关系。 存在的问题： + 事务的问题：分布式事务 + 连接 + ID唯一性：使用全局唯一 ID（GUID），为每个分片指定一个 ID 范围，分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

  • 垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

    在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

MySQL主从分离（读写分离）：为什么需要主从分离？如何保证主数据库和从数据库同步？如何实现主从分离？主从分离的延迟问题如何解决

主从分离也就是读写分离：通常情况下是一个主数据库(Master)负责写操作，从数据库（Slave，通常多个Slave）负责读请求。主数据库在一定情况下可能也会承担读请求，以应对延迟问题

主从分离主要是在读多写少的情况下使用

为什么需要主从分离:

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

主从分离的数据同步：

主要通过通过主从复制实现

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  • I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Replay log）中。
  • SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句。

主服务器master由binlog 线程记录数据库操作日志到Binary log，从服务器开启i/o线程将二进制日志记录的操作同步到relay log（存在从服务器的缓存中），另外sql线程将relay log日志记录的操作在从服务器执行

延迟问题： 延迟问题指的是主从数据库的同步会有时间差，由此可能引发向从数据库的读请求异常。 解决方法：

  • 在Master上增加一个自增表，这个表仅含有1个的字段。当Master接收到任何数据更新的请求时，均会触发这个触发器，该触发器更新自增表中的记录。由于自增表也参与Mysq的主从同步，因此在Master上作的 Update更新也会同步到Slave上。当Client通过Proxy进行数据读取时，Proxy可以先向Master和Slave的自增表发送查询请求，当二者的数据相同时，Proxy可以认定 Master和Slave的数据状态是一致的，然后把select请求发送到Slave服务器上，否则就发送到Master上。

补充

三大范式

  • 第一范式（确保每列保持原子性） 数据库表的所有字段都不能再分解为更基本的数据单位。
  • 第二范式（确保表中的每列都和主键相关） 第二范式在第一范式的基础上更进一层，第二范式需要确保数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
  • 第三范式（确保每列都和主键列直接相关，而不是间接相关） 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

参见：www.cnblogs.com/1906859953L…

一些问题

  • 手写 SQL 语句，特别是连接查询与分组查询。

  • 连接查询与子查询的比较。

  • drop、delete、truncate 比较。

  • 视图的作用，以及何时能更新视图。

  • 理解存储过程、触发器等作用。

  • ACID 的作用以及实现原理。

  • 四大隔离级别，以及不可重复读和幻影读的出现原因。

  • 封锁的类型以及粒度，两段锁协议，隐式和显示锁定。

  • 乐观锁与悲观锁。

  • MVCC 原理，当前读以及快照读，Next-Key Locks 解决幻影读。

  • 范式理论。

  • SQL 与 NoSQL 的比较。

  • B+ Tree 原理，与其它查找树的比较。

  • MySQL 索引以及优化。

  • 查询优化。

  • InnoDB 与 MyISAM 比较。

  • 水平切分与垂直切分。

  • 主从复制原理、作用、实现。

  • redo、undo、binlog 日志的作用。

  • MySQL数据库的索引原理、与慢SQL优化的5大原则

  • 数据库隔离级别有哪些，各自的含义是什么，MYSQL默认的隔离级别是是什么。

  • MYSQL有哪些存储引擎，各自优缺点。

  • 高并发下，如何做到安全的修改同一行数据。

  • 乐观锁和悲观锁是什么，INNODB的标准行级锁有哪2种，解释其含义。

  • SQL优化的一般步骤是什么，怎么看执行计划，如何理解其中各个字段的含义。

  • 数据库会死锁吗，举一个死锁的例子，mysql怎么解决死锁。

  • MYsql的索引原理，索引的类型有哪些，如何创建合理的索引，索引如何优化。

  • 聚集索引和非聚集索引的区别。

  • select for update 是什么含义，会锁表还是锁行或是其他。

  • 为什么要用Btree实现，它是怎么分裂的，什么时候分裂，为什么是平衡的。

  • 数据库的ACID是什么。

  • 某个表有近千万数据，CRUD比较慢，如何优化。

  • Mysql怎么优化table scan的。

  • 如何写sql能够有效的使用到复合索引。

  • mysql中in 和exists 区别。

  • 数据库自增主键可能的问题。

  • 你做过的项目里遇到分库分表了吗，怎么做的，有用到中间件么，比如sharding jdbc等,他们的原*理知道么。

avatar
文章
阅读
粉丝