数据库基础

Jan05

于 2021-04-01 19:11:48 发布

阅读量132

点赞数

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

本文链接：https://blog.csdn.net/qq_36585538/article/details/115361248

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

后端面试-数据库

一、数据库基础
二、MySQL
三、NoSQL/Redis

一、数据库基础

事务的概念和特性？

概念：事务（transaction）是一个操作序列，不可分割的工作单位，以BEGIN TRANSACTION 开始，以ROLLBACK/COMMIT结束。
特性：ACID
1、原子性（Atomicity）:逻辑上不可分割的操作单元，事务的所有操作要么全部提交成功，要么全部失败。
2、一致性（Consistency）:事务的执行必须使数据库保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。
3、隔离性（ISolation）:一个事物所做的修改在最终提交以前，对其他事务是不可见的（并发执行的事务之间不能相互影响）。
4、持久性（Durability）：一旦事务提交成功，对数据的修改是永久性的。

会出现哪些并发⼀致性问题？

丢失修改：一个事务对数据进行了修改，在事务提交之前，另一个事务对同一个数据进行了修改，覆盖了之前的修改。
脏读（Dirty Read）：一个事务读取了被另一个事务修改，但并未提交（进行了回滚）的数据，造成两个事务得到的数据不一致。
不可重复读（Nonrepeatable Read）：在同一个事务中，某查询操作在一个时间读取某一行数据和之后一个时间读取数据，发现数据已修改。
幻读（phantom read）:当同一查询多次执行时，由于其他事物在这个数据范围内执行了插入/删除操作，到导致每次返回不同的结果集（和不可重复读的区别：针对的是一个数据整体/范围，且针对insert/delete操作）。

数据库的四种隔离级别？

事务隔离级别	脏读	不可重复读	幻读
未提交读	是	是	是
提交读	否	是	是
可重复读	否	否	是
可串行化	否	否	否

事务隔离级别
1、未提交读（Read Uncommited）：在⼀个事务提交之前，它的执⾏结果对其它事务也是可⻅的。会导致脏读、不可重复读、幻读。
2、提交读（Read Commited）：⼀个事务只能看⻅已经提交的事务所作的改变。可避免脏读问题。
3、可重复读（Repeatable Read）：可以确保同⼀个事务在多次读取同样的数据时得到相同的结果，（MySQL的默认隔离级别）。可避免不可重复读；
4、可串⾏化（Serializable）：强制事务串⾏执⾏，使之不可能相互冲突，从⽽解决幻读问题。可能导致⼤量的超时现象和锁竞争，实际很少使⽤。

什么是乐观锁和悲观锁？

悲观锁：认为数据随时会被修改，因此每次读取数据之前都会上锁，防⽌其它事务读取或修改数据；应⽤于数据更新⽐较频繁的场景；
乐观锁：操作数据时不会上锁，但是更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试；适⽤于读多写少的场景。乐观锁的实现⽅式有：
1、加⼀个版本号或者时间戳字段，每次数据更新时同时更新这个字段；
2、先读取想要更新的字段或者所有字段，更新的时候⽐较⼀下，只有字段没有变化才进⾏更新

常⻅的封锁类型？

各种锁详解
意向锁是 InnoDB ⾃动加的，不需⽤户⼲预。对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会⾃动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以显式地给记录集加共享锁或排他锁：
锁的作⽤：⽤于管理对共享资源的并发访问，保证数据库的完整性和⼀致性
1、S锁，X锁

	兼容性	加锁方式
S锁共享锁	加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁	select…lock in share mode
X锁排他锁	加了X锁的记录，不允许其他事务再加S锁或者X锁	select…for update

2、意向锁：表锁，相互兼容，表明“某个事务持有了锁、或准备去持有锁”
1）意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。
2）
a.意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
b.意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁
3）例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。
3、问题：
q1:为什么意向锁是表级锁呢？
当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；
（1）如果意向锁是行锁，则需要遍历每一行数据去确认；
（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。
q2:意向锁怎么支持表锁和行锁并存？
（1）首先明确并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。
（2）如果事务A对某一行上锁，其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，让行锁与表锁共存，就会带来很多问题。于是有了意向锁的出现，如q1的答案中，数据库不需要在检查每一行数据是否有锁，而是直接判断一次意向锁是否存在即可，能提升很多性能。
4、意向锁和共享锁、排他锁的兼容关系。
1）当事务A对某个数据范围（行或表）上了“某锁”后，另一个事务B是否能在这个数据范围上“某锁”。
2）意向锁相互兼容，因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。
3）表级S锁和X、IX锁不兼容：因为上了表级S锁后，不允许其他事务再加X锁。
4）表级X锁和 IS、IX、S、X不兼容：因为上了表级X锁后，会修改数据，所以即使是行级排他锁，因为表级锁定的行肯定包括行级锁定的行，所以表级X和IX、X都不兼容。
== 注意：上了行级X锁后，行级X锁不会因为有别的事务上了IX而堵塞，一个mysql是允许多个行级X锁同时存在的，只要他们不是针对相同的数据行。==

事务A\事务B	IS	IX	S	X
IS	是	是	是	否
IX	是	是	否	否
S	是	否	是	否
X	否	否	否	否

5、封锁粒度的概念
MySQL 中提供了两种封锁粒度：⾏级锁以及表级锁。
封锁粒度⼩：
好处：锁定的数据量越少，发⽣锁争⽤的可能就越⼩，系统的并发程度就越⾼；
坏处：系统开销⼤（加锁、释放锁、检查锁的状态都需要消耗资源）

什么是三级封锁协议？

⼀级封锁协议：事务在修改数据之前必须先对其加X锁，直到事务结束才释放。可以解决丢失修改问题（两个事务不能同时对⼀个数据加X锁，避免了修改被覆盖）；
⼆级封锁协议：在⼀级的基础上，事务在读取数据之前必须先加S锁，读完后释放。可以解决脏读问题（如果已经有事务在修改数据，就意味着已经加了X锁，此时想要读取数据的事务并不能加S锁，也就⽆法进⾏读取，避免了读取脏数据）；
三级封锁协议：在⼆级的基础上，事务在读取数据之前必须先加S锁，直到事务结束才能释放。可以解决不可重复读问题（避免了在事务结束前其它事务对数据加X锁进⾏修改，保证了事务期间数据不会被其它事务更新）与二级封锁的区别在于：二级读完后释放，三级，事务结束释放

什么是两段锁协议？

事务必须严格分为两个阶段对数据进⾏加锁和解锁的操作，第⼀阶段加锁，第⼆阶段解锁。也就是说⼀个事务中⼀旦释放了锁，就不能再申请新锁了。
可串⾏化调度是指，通过并发控制，使得并发执⾏的事务结果与某个串⾏执⾏的事务结果相同。事务遵循两段锁协议是保证可串⾏化调度的充分条件。
实现方式：事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。

什么是 MVCC？

多版本并发控制（Multi-Version Concurrency Control, MVCC），MVCC在每⾏记录后⾯都保存有两个隐藏的列，⽤来存储创建版本号和删除版本号。
创建版本号：创建⼀个数据⾏时的事务版本号（事务版本号：事务开始时的系统版本号；系统版本号：每开始⼀个新的事务，系统版本号就会⾃动递增）；删除版本号：删除操作时的事务版本号；
各种操作：
插⼊操作时，记录创建版本号；
删除操作时，记录删除版本号；
更新操作时，先记录删除版本号，再新增⼀⾏记录创建版本号；
查询操作时，要符合以下条件才能被查询出来：删除版本号未定义或⼤于当前事务版本号（删除操作是在当前事务启动之后做的）；创建版本号⼩于或等于当前事务版本号（创建操作是事务完成或者在事务启动之前完成）
（其实就是说，要在创建之后删除之前才查地到数据）
通过版本号减少了锁的争⽤，提⾼了系统性能；可以实现提交读和可重复读两种隔离级别，未提交读⽆需使⽤MVCC

数据库的范式？

第⼀范式（1NF，Normal Form）：属性不应该是可分的。举例：如果将“电话”作为⼀个属性（⼀列），是不符合1NF的，因为电话这个属性可以分解为家庭电话和移动电话等，如果将“移动电话”作为⼀个属性，就符合1NF；
第⼆范式 2NF：每个⾮主属性完全依赖于主属性集（候选键集）。举例：B完全依赖于A，就是说A中的所有属性唯⼀决定B，属性少了就不能唯⼀决定，属性多了则有冗余（叫依赖不叫完全依赖）。举例：（学号，课程名）这个主属性集可以唯⼀决定成绩，但是对于学⽣姓名这个属性，（学号，课程名）这个属性集就是冗余的，所以学⽣姓名不完全依赖于（学号，课程名）这⼀属性集；

主属性集/候选码集：某⼀组属性能够唯⼀确定其它的属性（主键就是从候选键集中选的⼀个键），⽽其⼦集不能，这样的属性组中的属性就是主属性；
不在候选码集中的属性成为⾮主属性；可以通过分解来满⾜ 2NF：将（学号，课程名，成绩）做成⼀张表；（学号，学⽣姓名）做成另⼀张表，
避免⼤量的数据冗余；满⾜1NF后，要求表中的所有列，都必须依赖于主键，⽽不能有任何⼀列与主键没有关系，也就是说⼀个表只描述⼀件事情；

第三范式 3NF：在 2NF 的基础上，⾮主属性不传递依赖于主属性。
3NF在2NF的基础上，消除了⾮主属性之间的依赖；⽐如⼀个表中，主属性有（学号），⾮主属性有（姓名，院系，院⻓名），可以看到院⻓名这个⾮主属性依赖于院系，传递依赖于学号。消除的办法是分解。必须先满⾜第⼆范式（2NF），要求：表中的每⼀列只与主键直接相关⽽不是间接相关（表中的每⼀列只能依赖于主键）

传递依赖：如果C依赖于B，B依赖于A，那么C传递依赖于A；

不符合范式会出现哪些异常？
1）冗余数据：某些同样的数据多次出现（如学⽣姓名）；
2）修改异常：修改了⼀个记录中的信息，另⼀个记录中相同的信息却没有修改；
3）删除异常：删除⼀个信息，那么也会丢失其它信息（删除⼀个课程，丢失了⼀个学⽣的信息）；
4）插⼊异常：⽆法插⼊（插⼊⼀个还没有课程信息的学⽣）

列举⼏种表连接⽅式？

表连接方式
外连接（Outer Join）
1）交叉连接（笛卡尔积）：返回两表的笛卡尔积（对于所含数据分别为m、n的表，返回m*n的结果）
2）左连接：两表关联，左表全部保留，右表关联不上用null表示。
3）右连接：右表全部保留，左表关联不上的用null表示。
内连接（Inner Join）：仅将两个表中满⾜连接条件的⾏组合起来作为结果集
1）⾃然连接：只考虑属性相同的元组对；
2）等值连接：给定条件进⾏查询
3）左表独有：两表关联，查询左表独有的数据。
4）右表独有：两表关联，查询右表独有的数据。
5）全连接：两表关联，查询它们的所有记录。
6）并集去交集：两表关联，取并集然后去交集。

/*join 建表语句*/
drop database if exists test;
create database test;
use test;
 
/* 左表t1*/
drop table if exists t1;
create table t1 (id int not null,name varchar(20));
insert into t1 values (1,'t1a');
insert into t1 values (2,'t1b');
insert into t1 values (3,'t1c');
insert into t1 values (4,'t1d');
insert into t1 values (5,'t1f');
 
/* 右表 t2*/
drop table if exists t1;
create table t2 (id int not null,name varchar(20));
insert into t2 values (2,'t2b');
insert into t2 values (3,'t2c');
insert into t2 values (4,'t2d');
insert into t2 values (5,'t2f');
insert into t2 values (6,'t2a');
//笛卡尔
mysql> select * from t1 join t2;
//左连接
mysql> select * from t1 left join t2 on t1.id = t2.id;
//右连接
mysql> select * from t1 right join t2 on t1.id =t2.id;
//内连接
mysql> select * from t1 inner join t2 on t1.id = t2.id;
//左有连接
mysql> select * from t1 left join t2 on t1.id = t2.id where t2.id is null;
//右有连接
mysql> select * from t1 right join t2 on t1.id = t2.id where t1.id is  null;
//全连接
mysql> select * from t1 left join t2 on t1.id = t2.id
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id;
//并集去交集
mysql> select * from t1 left join t2 on t1.id = t2.id where t2.id is null
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

什么是存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的⼀段SQL语句的集合。想要实现相应的功能时，只需要调⽤这个存储过程就⾏了（类似于函数，有输⼊、输出参数）。
优点：
1）预先编译，⽽不需要每次运⾏时编译，提⾼了数据库执⾏效率；
2）封装了系列操作，对于⼀些数据交互⽐较多的操作，相⽐于单独执⾏SQL语句，可以减少⽹络通信量；
3）具有可复⽤性，减少了数据库开发的⼯作量；
4）安全性⾼，可以让没有权限的⽤户通过存储过程间接操作数据库；
5）更易于维护
缺点：
1）可移植性差，存储过程将应⽤程序绑定到了数据库上（也就是说存储过程依赖于数据库）；
2）开发调试复杂：没有好的IDE（没有好的开发环境）；
3）修改复杂，需要重新编译，有时还需要更新程序中的代码以更新调⽤

Drop/Delete/Truncate的区别？

Drop命令从数据库中删除表，所有的数据⾏，索引和约束都会被删除；不能回滚，不会触发触发器；
Delete⽤来删除表的全部或者部分数据，执⾏delete之后，⽤户需要提交之后才会执⾏，会触发表上的DELETE触发器（包含⼀个OLD的虚拟表，可以只读访问被删除的数据），DELETE之后表结构还在，删除很慢，⼀⾏⼀⾏地删，因为会记录⽇志，可以利⽤⽇志还原数据；
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器。操作⽐DELETE快很多（直接把表drop掉，再创建⼀个新表，删除的数据不能找回）。如果表中有⾃增（AUTO_INCREMENT）列，则重置为1；
相关
1）什么是触发器？
触发器（TRIGGER）是由事件（⽐如INSERT/UPDATE/DELETE）来触发运⾏的操作（不能被直接调⽤，不能接收参数）。在数据库⾥以独⽴的对象存储，⽤于保证数据完整性（⽐如可以检验或转换数据）。
2）有哪些约束类型？
约束（Constraint）类型：主键（Primary Key）约束，唯⼀约束（Unique），检查约束，⾮空约束，外键（Foreign Key）约束。

什么是视图？什么是游标？

视图：视图是一张虚拟的数据表，该数据表是由数据库的查询语句查询出来的结果得到的。
视图的作用：
1）将重复、复杂的查询语句创建一个视图，查询时，直接用select * from 视图，即可轻松得到查询结果，提高效率（对于更新和删除也一样）。
2）将多张表联接在一起，使他们看起来像一张表。
3）增强安全性及保密性，根据不同的用户，创建不同的视图，限制其所能查看和编辑的数据。
4）方便维护。
5）增强可读性。
6）降低查询复杂度。
游标（Cursor）：游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。
游标的作用：
1）定位到结果集中的某一行。
2）对当前位置的数据进行读写。
3）可以对结果集中的数据单独操作，而不是整行执行相同的操作。
4）是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

二、MySQL

数据库索引的实现原理（B+树）

MySQL 索引底层实现原理(B-tree、B+tree)
使⽤B树和B+树的⽐较
InnoDB的索引使⽤的是B+树实现，B+树对⽐B树的好处：
1） IO次数少：B+树的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存更多的数据，让索引树更加矮胖；
2）范围查询效率更⾼：B树需要中序遍历整个树，只B+树需要遍历叶结点中的链表；
3）查询效率更加稳定：每次查询都需要从根结点到叶结点，路径⻓度相同，所以每次查询的效率都差不多
使⽤B树索引和哈希索引的⽐较
哈希索引能以 O(1) 时间进⾏查找，但是只⽀持精确查找，⽆法⽤于部分查找和范围查找，⽆法⽤于排序与分组；B树索引⽀持⼤于⼩于等于查找，范围查找。哈希索引遇到⼤量哈希值相等的情况后查找效率会降低。哈希索引不⽀持数据的排序。

使⽤索引的优点

索引：索引是帮助MySQL高效获取数据的排好序的数据结构；索引存储在文件里
优点：
1）⼤⼤加快了数据的检索速度；
2）可以显著减少查询中分组和排序的时间；
3）通过创建唯⼀性索引，可以保证数据库表中每⼀⾏数据的唯⼀性；
4）将随机 I/O 变为顺序IO（B+Tree 索引是有序的，会将相邻的数据都存储在⼀起）
缺点：建⽴和维护索引耗费时间空间，更新索引很慢。

哪些情况下索引会失效？

转载：索引失效
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
对于多列索引，不使用第一部分，则不会使用索引
like查询是以%开头
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
如果mysql估计使用全表扫描要比使用索引快,则不使用索引
查看索引的使用情况
show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

在哪些地⽅适合创建索引？

索引的建立规则：
1）表的主键、外键必须有索引；
2）数据量超过300的表应该有索引；
3）经常与其他表进行连接的表，在连接字段上应该建立索引；
4）经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5）索引应该建在选择性高的字段上；
6）索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7）频繁进行数据操作的表，不要建立太多的索引；
8）删除无用的索引，避免对执行计划造成负面影响；
9）复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

总结成四个点：
1、较频繁地作为查询条件的字段适合
2、唯一性太差的字段不适合建立索引
3、更新太频繁地字段不适合创建索引
4、不会出现在where条件中的字段不该建立索引

索引的分类

转载：索引分类、类型、区别
一、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
1）普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
2）唯一索引：索引列中的值必须是唯一的，但是允许为空值。
3）主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

主键索引与唯一索引的区别：
1）主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
2）主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
3）唯一性索引列允许空值，而主键列不允许为空值。
4）主键索引在创建时，已经默认为非空值+唯一索引了。
5）一个表最多只能创建一个主键索引，但可以创建多个唯一索引。
6）主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
7）主键可以被其他表引用为外键，而唯一索引不能。

二、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。
三、全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不贵用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。
四、覆盖（Covering）索引：索引包含了所有满⾜查询所需要的数据，查询的时候只需要读取索引⽽不需要回表读取数据；
五、聚集（Clustered）索引/⾮聚集索引：对磁盘上存放数据的物理地址重新组织以使这些数据按照指定规则排序的⼀种索引（数据的物理排列顺序和索引排列顺序⼀致）。因此每张表只能创建⼀个聚集索引（因为要改变物理存储顺序）。优点是查询速度快，因为可以直接按照顺序得到需要数据的物理地址。缺点是进⾏修改的速度较慢。对于需要经常搜索范围的值很有效。⾮聚集索引只记录逻辑顺序，并不改变物理顺序；
六、分区索引：分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。B树和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性。
七、虚拟索引（Virtual）：模拟索引的存在⽽不⽤真正创建⼀个索引，⽤于快速测试创建索引对执⾏计划的影响。没有相关的索引段，不增加存储空间的使⽤

MySQL的两种存储引擎 InnoDB 和 MyISAM 的区别？

区别：
1）InnoDB⽀持事务，可以进⾏Commit和Rollback；
2）MyISAM 只⽀持表级锁，⽽ InnoDB 还⽀持⾏级锁，提⾼了并发操作的性能；
3）InnoDB ⽀持外键；
4）MyISAM 崩溃后发⽣损坏的概率⽐ InnoDB ⾼很多，⽽且恢复的速度也更慢；
5）MyISAM ⽀持压缩表和空间数据索引，InnoDB需要更多的内存和存储；
6）InnoDB ⽀持在线热备份
应用场景：
MyISAM 管理⾮事务表。它提供⾼速存储和检索（MyISAM强调的是性能，每次查询具有原⼦性，其执⾏速度⽐InnoDB更快），以及全⽂搜索能⼒。如果表⽐较⼩，或者是只读数据（有⼤量的SELECT），还是可以使⽤MyISAM；
InnoDB ⽀持事务，并发情况下有很好的性能，基本可以替代MyISAM。
热备份和冷备份
热备份：在数据库运⾏的情况下备份的⽅法。优点：可按表或⽤户备份，备份时数据库仍可使⽤，可恢复⾄任⼀时间点。但是不能出错
冷备份：数据库正常关闭后，将关键性⽂件复制到另⼀位置的备份⽅式。优点：操作简单快速，恢复简单

如何优化数据库？

SQL 语句的优化
1）分析慢查询⽇志：记录了在MySQL中响应时间超过阀值long_query_time的SQL语句，通过⽇志去找出IO⼤的SQL以及发现未命中索引的SQL
2）使⽤ Explain 进⾏分析：通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使⽤、哪些索引被实际使⽤、表之间的引⽤以及被扫描的⾏数等问题；
3）应尽量避免在 where ⼦句中使⽤ != 、 < 、 > 操作符或对字段进⾏null值判断，否则将引擎放弃使⽤索引⽽进⾏全表扫描；
4）只返回必要的列：最好不要使⽤ SELECT * 语句；
5）只返回必要的⾏：使⽤ LIMIT 语句来限制返回的数据；
6）将⼀个⼤连接查询分解成对每⼀个表进⾏⼀次单表查询，然后在应⽤程序中进⾏关联，这样做的好处有：

1.让缓存更⾼效。对于连接查询，如果其中⼀个表发⽣变化，那么整个查询缓存就⽆法使⽤。⽽分解后的多个查询，即使其中⼀个表发⽣变化，对其它表的查询缓存依然可以使⽤；
2.分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使⽤到，从⽽减少冗余的查询；
3.减少锁竞争

索引的优化
注意会引起索引失效的情况，以及在适合的地⽅建⽴索引
数据库表结构的优化
1）设计表时遵循三⼤范式；
2）选择合适的数据类型：尽可能不要存储NULL字段；使⽤简单的数据类型（int, varchar/ text）；
3）表的⽔平切分（Sharding）：将同⼀个表中的记录拆分到多个结构相同的表中（策略：哈希取模；根据ID范围来分）。当⼀个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从⽽缓解单个数据库的压⼒；
4）表的垂直切分：将⼀张表按列切分成多个表。可以将不常⽤的字段单独放在同⼀个表中；把⼤字段独⽴放⼊⼀个表中；或者把经常使⽤的字段（关系密切的）放在⼀张表中。垂直切分之后业务更加清晰，系统之间整合或扩展容易，数据维护简单
系统配置的优化
1）操作系统：增加TCP⽀持的队列数；
2）MySQL配置⽂件优化：缓存池⼤⼩和个数设置
硬件的优化
1）磁盘性能：固态硬盘；
2）CPU：多核且⾼频；
3）内存：增⼤内存

什么是主从复制？实现原理是什么？

主从复制（Replication）是指数据可以从⼀个MySQL数据库主服务器复制到⼀个或多个从服务器，从服务器可以复制主服务器中的所有数据库或者特定的数据库，或者特定的表。默认采⽤异步模式。
实现原理：
1）主服务器 binary log dump线程（二进制日志转储线程）：将主服务器中的数据更改（增删改）⽇志写⼊ Binary log 中；
2）从服务器 I/O 线程：负责从主服务器读取binary log，并写⼊本地的 Relay log（继电器的日志）；
3）从服务器 SQL线程：负责读取 Relay log，解析出主服务器已经执⾏的数据更改，并在从服务器中重新执⾏（Replay），保证主从数据的⼀致性。

为什么要主从复制？

读写分离：主服务器负责写，从服务器负责读；
缓解了锁的争⽤，即使主服务器中加了锁，依然可以进⾏读操作；
从服务器可以使⽤ MyISAM，提升查询性能以及节约系统开销；
增加冗余，提⾼可⽤性
数据实时备份，当系统中某个节点发⽣故障时，可以⽅便的故障切换
降低单个服务器磁盘I/O访问的频率，提⾼单个机器的I/O性能

三、NoSQL/Redis

关系型数据库和⾮关系型数据库的区别？

非关系型数据库的优势：
性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
关系型数据库的优势：
复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持：使得对于安全性能很高的数据访问要求得以实现。
其他：
1.对于这两类数据库，对方的优势就是自己的弱势，反之亦然。
2.NOSQL数据库慢慢开始具备SQL数据库的一些复杂查询功能，比如MongoDB。
3.对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如Redis set nx。

Redis

REmote DIctionary Server(Redis) 是一个由 Salvatore Sanfilippo 写的 key-value 存储系统，是跨平台的非关系型数据库。
Redis 通常被称为数据结构服务器，因为值（value）可以是字符串(String)、哈希(Hash)、列表(list)、集合(sets)和有序集合(sorted sets)等类型。

参考

Jan05

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库基础

后端面试-数据库一、数据库基础事务的概念和特性？会出现哪些并发⼀致性问题？数据库的四种隔离级别？什么是乐观锁和悲观锁？常⻅的封锁类型？什么是三级封锁协议？什么是两段锁协议？什么是 MVCC？数据库的范式？列举⼏种表连接⽅式？什么是存储过程？有哪些优缺点？Drop/Delete/Truncate的区别？什么是视图？什么是游标？二、MySQL数据库索引的实现原理（B+树）使⽤索引的优点哪些情况下索引会失效？在哪些地⽅适合创建索引？索引的分类？MySQL的两种存储引擎 InnoDB 和 MyISAM 的区别？如何
复制链接

扫一扫

专栏目录