数据库基本知识

数据库

 

 

（1） 说一下 MySQL 执行一条查询语句的内部执行过程？

• 连接器：客户端先通过连接器连接到 MySQL 服务器。
• 缓存：连接器权限验证通过后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
• 分析器：分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
• 优化器：优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
• 执行器：优化器执行完就进入执行器，执行器就开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

 

（2） 数据库的索引类型

数据库的索引类型分为逻辑分类和物理分类

逻辑分类：

主键索引 当关系表中定义主键时会自动创建主键索引。每张表中的主键索引只能有一个，要求主键中的每个值都唯一，即不可重复，也不能有空值。
唯一索引 数据列不能有重复，可以有空值。一张表可以有多个唯一索引，但是每个唯一索引只能有一列。如身份证，卡号等。
普通索引 一张表可以有多个普通索引，可以重复可以为空值
全文索引 可以加快模糊查询，不常用
物理分类：

聚集索引（聚簇索引） 数据在物理存储中的顺序跟索引中数据的逻辑顺序相同，比如以ID建立聚集索引，数据库中id从小到大排列，那么物理存储中该数据的内存地址值也按照从小到大存储。一般是表中的主键索引，如果没有主键索引就会以第一个非空的唯一索引作为聚集索引。一张表只能有一个聚集索引。
非聚集索引 数据在物理存储中的顺序跟索引中数据的逻辑顺序不同。非聚集索引因为无法定位数据所在的行，所以需要扫描两遍索引树。第一遍扫描非聚集索引的索引树，确定该数据的主键ID，然后到主键索引（聚集索引）中寻找相应的数据。

 

（3） MySQL怎么建立索引，怎么建立主键索引，怎么删除索引？

MySQL建立索引有两种方式：用alter table或者create index。

alter table table_name add primary key(column_list) #添加一个主键索引
alter table table_name add index (column_list)      #添加一个普通索引
alter table table_name add unique (column_list)     #添加一个唯一索引

create index index_name on table_name (column_list)   #创建一个普通索引
create unique index_name on table_name (column_list)  #创建一个唯一索引

Mysql删除索引同样也有两种方式：alter table 和 drop index

alter table table_name drop index index_name    #删除一个普通索引
alter table table_name drop primary key         #删除一个主键索引

drop index index_name on table table_name

 

（4） 索引的优缺点，什么时候使用索引，什么时候不能使用索引（重点）

• 经常搜索的列上要建索引
• 作为主键的列上要建索引
• 经常需要连接、排序、范围查找的列上

哪些列不适合建索引？

• 很少查询的列
• 更新频繁的列
• 数据值的取值比较少的列（比如性别）

 

（3） 索引的底层实现（重点）

数据库的索引是使用B+树来实现的。

B+树是一种特殊的平衡多路树，是B树的优化改进版本，它把所有的数据都存放在叶节点上，中间节点保存的是索引。这样一来相对于B树来说，减少了数据对中间节点的空间占用，使得中间节点可以存放更多的指针，使得树变得更矮，深度更小，从而减少查询的磁盘IO次数，提高查询效率。另一个是由于叶节点之间有指针连接，所以可以进行范围查询，方便区间访问。

而红黑树是二叉的，它的深度相对B+树来说更大，更大的深度意味着查找次数更多，更频繁的磁盘IO，所以红黑树更适合在内存中进行查找。

 

（4） B树和B+树的区别（重点）

这都是由于B+树和B具有不同的存储结构所造成的区别，以一个m阶树为例。

• 关键字的数量不同；B+树中分支结点有m个关键字，其叶子结点也有m个，其关键字只是起到了一个索引的作用，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
• 存储的位置不同；B+树中的数据都存储在叶子结点上，也就是其所有叶子结点的数据组合起来就是完整的数据，但是B树的数据存储在每一个结点中，并不仅仅存储在叶子结点上。
• 分支结点的构造不同；B+树的分支结点仅仅存储着关键字信息和儿子的指针（这里的指针指的是磁盘块的偏移量），也就是说内部结点仅仅包含着索引信息。
• 查询不同；B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。

 

（5） 索引最左前缀/最左匹配

假如我们对a b c三个字段建立了联合索引，在联合索引中，从最左边的字段开始，任何连续的索引都能匹配上，当遇到范围查询的时候停止。比如对于联合索引index(a,b,c),能匹配a,ab,abc三组索引。并且对查询时字段的顺序没有限制，也就是a,b,c; b,a,c; c,a,b; c,b,a都可以匹配。

 

（6） Mysql的优化（高频，索引优化，性能优化）

高频访问：

• 分表分库：将数据库表进行水平拆分，减少表的长度
• 增加缓存： 在web和DB之间加上一层缓存层
• 增加数据库的索引：在合适的字段加上索引，解决高频访问的问题

并发优化：

• 主从读写分离：只在主服务器上写，从服务器上读
• 负载均衡集群：通过集群或者分布式的方式解决并发压力

 

（7） MYSQL数据库引擎介绍，innodb和myisam的特点与区别

• InnoDB ： InnoDB是mysql的默认引擎，支持事务和外键，支持容灾恢复。适合更新频繁和多并发的表 行级锁
• MyISAM ： 插入和查询速度比较高，支持大文件，但是不支持事务，适合在web和数据仓库场景下使用 表级锁
• MEMORY ： memory将表中的数据保存在内存里，适合数据比较小而且频繁访问的场景
• CSV
• blackhole

 

（8） 数据库中事务的ACID（四大特性都要能够举例说明，理解透彻，比如原子性和一致性的关联，隔离性不好会出现的问题）

数据库事务是指逻辑上对数据的一种操作，这个事务要么全部成功，要么全部失败。

• A: atom 原子性

数据库事务的原子性是指：事务是一个不可分割的工作单位，这组操作要么全部发生，要么全部不发生。

• C: consistency 一致性

数据库事务的一致性是指：在事务开始以前，数据库中的数据有一个一致的状态。在事务完成后，数据库中的事务也应该保持这种一致性。事务应该将数据从一个一致性状态转移到另一个一致性状态。
比如在银行转账操作后两个账户的总额应当不变。

• I: isolation 隔离性

数据库事务的隔离性要求数据库中的事务不会受另一个并发执行的事务的影响，对于数据库中同时执行的每个事务来说，其他事务要么还没开始执行，要么已经执行结束，它都感觉不到还有别的事务正在执行。

• D：durability 持久性

数据库事务的持久性要求事务对数据库的改变是永久的，哪怕数据库发生损坏都不会影响到已发生的事务。
如果事务没有完成，数据库因故断电了，那么重启后也应该是没有执行事务的状态，如果事务已经完成后数据库断电了，那么重启后就应该是事务执行完成后的状态。

 

（9） 数据库连接池的作用

连接池是将已经创建好的连接保存在池中，当有请求来时，直接使用已经创建好的连接对数据库进行访问。这样省略了创建连接和销毁连接的过程。这样性能上得到了提高。

 

（10） 数据的锁的种类，加锁的方式

• 按类型来分有乐观锁和悲观锁
• 按粒度来分有行级锁，页级锁，表级锁
• 按作用来分有共享锁（读锁）和排他锁（写锁）

 

（11）乐观锁与悲观锁解释一下

• 一般的数据库都会支持并发操作，在并发操作中为了避免数据冲突，所以需要对数据上锁，乐观锁和悲观锁就是两种不同的上锁方式。
• 悲观锁假设数据在并发操作中一定会发生冲突，所以在数据开始读取的时候就把数据锁住。而乐观锁则假设数据一般情况下不会发生冲突，所以在数据提交更新的时候，才会检测数据是否有冲突。

 

（12） 什么是共享锁和排他锁

• 共享锁是读操作的时候创建的锁，一个事务对数据加上共享锁之后，其他事务只能对数据再加共享锁，不能进行写操作直到释放所有共享锁。

• 排他锁是写操作时创建的锁，事务对数据加上排他锁之后其他任何事务都不能对数据加任何的锁（即其他事务不能再访问该数据）

 

（13）数据库高并发的解决方案

在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
增加数据库索引。提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
主从读写分离，让主服务器负责写，从服务器负责读。
将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
使用分布式架构，分散计算压力。