文章介绍了数据库的三范式、MySQL的InnoDB和MyISAM引擎的区别、事务的ACID特性、索引的作用和类型以及SQL优化方法。还讨论了数据库的并发问题、事务隔离级别、大表优化策略，包括分库分表后的ID处理和查询执行过程。此外，提到了MVCC的概念和锁机制，如悲观锁和乐观锁，以及如何避免死锁。最后，文章提到了主键与索引的区别，以及MySQL的高可用性方案。
1、数据库的三范式
（1）第一范式： 列不可再分
（2）第二范式： 行可以唯一区分，主键约束
（3）第三范式： 表的非主属性不能依赖于其它表的非主属性，外键约束。
2、MySQL有哪些引擎
#查看支持的存储引擎
mysql> show engins;
常用引擎：
- InnDB： 行级锁。
- 优点： 提供具有提交、回滚和崩溃恢复能力的事务安全，支持自增列，支持外键约束，并发能力强。
- 缺点： 占用空间大，处理效率低。
- MYISAM： 全表锁，以select、insert为主的应用使用。
- 优点： 执行速度快，占用空间小。
- 缺点： 不支持事务，不支持外键，并发差，对事务完整性没有要求。
- Memory： 全表锁，主要用于那些内容变化不频繁的代码表。
- 优点： 存储速度快，存储在内存中，默认使用HASH索引，检索效率高。
- 缺点： 占用和数据量成正比的内存空间且数据在mysql重启时会丢失。
- MERGE： 是一组MYISAM表的组合。
3、说说InnoDB与MyISAM的区别
（1）锁的不同：
- InnoDB： 使用行级锁
- MyISAM： 使用全表锁
（2）占用空间与执行速度不同：
- InnoDB： 占用空间大，执行效率低
- MyISAM： 占空间小，执行效率高
（3）支持插件
- InnoDB： 支持事务，支持自增列，支持外键约束，并发能力强
- MyISAM： 不支持事务，不支持外键约束，事务完整性不要求，并发能力差
4、数据库的事务
（1）什么是事务？
一组操作集合，多条sql语句执行，要么成功，要么失败。
（2）事务的特性：
数据事务特性：原子性，一致性，隔离性，持久性。简称：ACID
- 原子性： 组成一个事务的多个数据库操作是一个不可分割的原子单位，只有所有操作成功才能提交，若一个操作失败，则所有操作必须回滚。
- 一致性：事务操作成功后，数据库所处状态和它的业务规则一致。
- 隔离性：在并发数据操作时，不同事务拥有各自数据空间，事务的操作不会对彼此产生干扰。
- 持久性：一旦事务提交成功，事务中的所有操作都必须持通过数据库存储到磁盘中。
5、索引是什么
（1）概念： 索引是帮助MySQL 高效获取数据 的 数据结构，加快数据库的查询速度。
（2）存储空间： 索引本身就大，不可能全部存储在内存中，因此 索引存储在磁盘上的文件中。
（3）索引类型：
- 聚集索引
- 覆盖索引
- 组合索引
- 前缀索引
- 唯一索引
- …
默认使用 B+ 树结构组织的索引（多路搜索树，并不一定是二叉树）
6、SQL优化手段有哪些
- （1）查询语句不要使用 select *
- （2）尽量减少子查询，使用关联查询（left join，right join，inner join）替代
- （3）减少使用 IN 或者 NOT IN，使用 exsits，not exists 或者关联查询语句替代
- （4）or 的查询尽量使用 union 或者 union all 代替 （在确认没有从夫数据或者不用剔除重复数据时 union all 会更好）
- （5）避免在 where 子句中使用逻辑关系操作符（!=，<，>），否则引擎会放弃使用索引进行全盘扫描。
- （6）应尽量避免在 where 子句对字段进行 null 值判断，否则引擎会放弃使用索引进行全盘扫描。 如
select id from t where num is null可以在num上设置默认值0，确保num列没有null值，然后使用select id from t where num=0
7、简单说一说drop、delete与truncate的区别
- drop： 主要用于删除数据库和表结构的删除语句，并且不会回滚。
- delete： 删除表的数据，不删除表的结构，可回滚。
- truncate： 清除表数据，并初始化表设置，可回滚。（就是初始化表）
8、什么是视图
视图 是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作。
视图 通常是一个表或者多个表的行或列的子集，对视图的修改不影响基本表，主要用于改动不频繁的静态数据。
9、 什么是内联接、左外联接、右外接？
- 内连接（inner join）： 匹配2张表中相关联的记录。
- 左外连接（left outer join）： 除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。
- 右外连接（right outer join）： 除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。
# 左表：t ， 右表：m
select * from t left outer join m;
10、 并发事务带来哪些问题?
在典型的应用程序中，多个事务并发运行，经常操作相同的数据。
可能导致的结果：
- 脏读（ Dirty Read ）： 当一个事务正在对数据修改，此时数据库的数据是原始数据，在这时另一个事物在该数据库读取相同数据，前一个事务完成数据修改，后一个事务再次读取相同数据，前后得到的值是不同的。
- 丢失修改（Lost to modify）： 当一个事务对一个数据修改，另一个事务也对相同数据进行修改，当前一个事务早于后一个事务提交修改数据，最终前一个事务修改的数据被后一个事务覆盖。
- 不可重复读（Unrepeatableread）： 一个事务多次读取同一数据，而另一事务中途修改了数据，则前一个事务读取的数据可能不一致。
- 幻读（Phantom read）： 一个事务读取数据，之后另一个事务插入数据，在之后的查询前一个事务查询到多出的数据。
不可重复读和幻读区别：
不可重复读 主要是多次读取一条记录发现某些字段的值被改变。
幻读 主要是在新增或删除，多次读取一条记录会发现记录增多或减少。
11、 事务隔离级别有哪些?MySQL的默认隔离级别是?
- 读取未提交（read-uncommited）： 最低隔离级别，允许读取尚未提交的数据变更，可能导致脏读、幻读或不可重复读。
- 读取已提交（read-commited）： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- 可重复读（repeatable-read）： 对同一字段的多次读取保持一致，除非数据是被本身事务所修改，可以阻止脏读或不可重复读，但可能发生幻读。
- 可串行化（serializable）： 最高隔离级别，完全服从ACID隔离级别，所有事务逐个执行，这样事务之间不产生干扰。
MySQL InnoDB存储引擎默认支持的隔离级别是 可重复读，通过select @@tx_isolation查看
InnoDB引擎采用了 Next-key Lock锁算法，避免了幻读，所以原则上InnoDB 已经可以达到完全保证事务隔离性的要求。
12、 如何优化大表
（1）限定数据范围： 禁止不带任何限制数据范围条件的查询语句，主要是不能全表扫描。
（2）读写隔离： 主库负责写，从库负责读。
（3）垂直分区： 根据数据库数据表相关性进行拆分。例如用户表中既有用户登录信息又有用户基本信息，可以将用户表拆分成两个独立表，甚至独立成库。
- 垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少IO次数，此外垂直分区可以简化表的结构，易于维护。
- 垂直拆分的缺点： 主键产生冗余，需要管理冗余列，并会引起join操作，可以通过应用层的join来解决，而且还会让事务变得复杂。
（4）水平分区： 保持数据结构不变，通过某种策略存储数据分片。这样将每一片数据分散到不同的表或库中，达到了分布式的目的，水平拆分可以支持非常大的数据量。
水平拆分是指数据表行的拆分，表的行数超过200万行时， 会变慢，这时就需要拆成很多张表存放。
例如，我们可以将用户信息表拆分成多个用户信息表，这样可以避免数据量过大产生性能影响。
水平拆分可以支持非常大的数据量，但是分表仅仅是解决单一表数据过大的问题，但由于表的数据还是在同一台机器上，对提升Mysql的并发能力并没有意义，所以 水平拆分最好分库
水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决，跨节点 join 操作，性能较差，逻辑复杂。
常见数据库分片的方案
- 客户端代理： 分片逻辑在应用端，封装在 jar 包中，通过修改或者封装JDBC实现。例如当当网使用的 Sharding-JDBC，阿里的 TDDL 是两种常见实现。
- 中间件代理： 在应用和数据中间加一层代理。分片逻辑统一维护在中间件服务中。 现在的Mycat，360的 Atlas，网易的DDB 等等都是这种架构的实现。
13、分库分表之后,id 主键如何处理？
由于分表之后，不可能再使用 逐渐索引，但是我们又需要一个全局唯一的 id 支持。
生成全局 ID 的几种方式：
- UUID： 不适合做主键，因为太长了，并且无需不可读，查询效率低，比较适合文件重命名。
- 数据库自增 id： 通过设置不同步长，生成不重复ID的策略实现高可用。这种生成方式 id 是有序的，但是需要独立部署数据库实例，成本过高，并且有性能瓶颈（id使用完了）。
- 利用 redis 生成 id： 性能较好，灵活方便，不依赖数据库，但是引入新组件造成系统更加复杂，可用性降低，编码更加复杂，增加系统成本。
- 雪花算法： Twitter的一个生成唯一随机ID。
- Leaf分布式ID生成器： 美团的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，依赖关系型数据库、ZK等中间件。
14、说说在 MySQL 中一条查询 SQL 是如何执行的？
（1）取得连接器
（2）查询缓存： key = SQL语句，value = 查询结果，缓存命中取出，缓存未命中，则全表查询并存入缓存中，Mysql 8.0版本不再支持使用查询缓存。
（3）分析器： 通过词法分析和语法分析，进行语法校验。
（4）优化器： 在表中有多个索引时，决定使用什么索引，或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。
（5）执行器： 通过分析器让 SQL 知道执行的操作列表，通过优化器知道操作执行顺序，开始执行语句。执行语句时需要判断是否具备权限，没有权限会返回错误；有权限则打开表，根据表的引擎定义，去使用引擎提供的接口获取这个表的第一行，判断 id 是都等于 1。若是则返回，若不是调用引擎接口查询下一行，重复相同步骤，直到最后一行返回。
15、索引有什么优缺点？
- 索引的优点：
- 提高检索速度，降低IO成本：通过缩小表中需要查询的记录的数目从而加快搜索速度。
- 降低排序成本，降低CPU消耗：索引先将数据排序，降低了排序的成本。
- 索引的缺点：
- 占用存储空间：因为索引存储在磁盘的文件上。
- 降低更新表的速度：因为表与索引必须保持一致性。
16、MySQL 中 varchar 与 char 的区别？varchar[30] 中的 30代表的涵义？
（1）varchar 与 char 区别：char是一种固定长度的类型，varchar 是一种可变长度的类型。
（2）varchar(30) 表示最多可存放的字符为 30 个字符，是存储空间大小的上限。
（3）高效率——char，节省空间——varchar
17、int[11] 的含义
int(11) 中的 11，不影响 int 字段存储的范围，主要是 int 字段显示位数。
@[TOC](18、为什么 SELECT COUNT(*) FROM table 在 InnoDB 比MyISAM 慢？)
对于 select count(*) FROM table 语句，在没有 WHERE 条件的情况下， InnoDB 比 MyISAM 可能慢些，尤其在大表情况下。
原因： InnoDB 是实时统计结果，会全表扫描； MyISAM 内部维持了一个计数器，预存了结果。
21、什么时候不要使用索引？
索引最重要的作用是用来提高查询是效率，增删改的操作容易修改索引，整个数据库操作性能会变低。
（1）经常增删改的列
（2）有大量重复的列
（3）表记录太少
22、说说什么是 MVCC？
MVCC（Multi-Version Concurrency Control）多版本并发控制，是一种用来解决 读-写 冲突的无锁并发控制，就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，避免了脏读和不可重复读，但不能解决更新丢失的问题。
23、说说 MVCC 的实现原理
MVCC 的目的是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是以来记录中的 3 个隐式字段、undo 日志、Read View 来实现。
24、请说说 MySQL 数据库的锁？
- 共享锁： 不堵塞，多个用户可以在同一时刻读取同一个资源，相互之间不影响。
- 排它锁： 一个写操作阻塞其它的读锁和写锁，这样只允许一个用户进行写入，防止其他用户读取正在写入的资源。
- 表锁： 系统开销最小，会锁定整张表。
- 行锁： 容易出现死锁，发生冲突概率低，并发高。
25、说说什么是锁升级？
锁升级主要是在未获取锁的情况下，进行尝试获取更大范围的锁。
- 在MySQL数据库中，行锁只能加在索引上，如果操作不走索引，就会升级为表锁。
- 原因： InnoDB 的行锁是加在索引上的，如果不走索引，自然没办法使用行锁，原因是 InnoDB 是将 primary key index 和相关的行数据共同放在 B+ 树叶节点，在查找的时候，通过 primary-data 的键值对方式查找数据。
- 当非唯一索引记录数超过一定数量时，行锁也会升级为表锁。 当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。
- 原因： 当非唯一索引相同的内容达到记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失败，行锁自然就会升级为表锁。
26、说说悲观锁和乐观锁
悲观锁： 数据库被外界的修改保持保守态度，将数据处于锁状态。悲观的视线往往是依靠数据库提供的锁机制，也只有数据库提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统实现了加锁机制，也是没有办法保证系统不会修改数据。
在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
乐观锁： 相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制，乐观锁大多是基于数据版本（Version）记录机制实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一，此时将提交数据的版本数据与数据库表对应记录的版本信息进行比对，如果提交的数据版本 > 当前版本，则予以更新，否则是过期数据。
数据版本： 为数据增加一个版本表示，在基于数据库的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。
27、怎样尽量避免死锁的出现？
（1）良好的退出机制： 设置获取锁的超出时间，保证最差的情况下有退出机制。
（2）良好的执行环境： 串行化访问资源。
（3）操作隔离： 避免事务中的用户交叉。
（4）简化事务： 保持事务简短并在一个批处理中。
（5）使用低隔离级别： 减少锁的数量，降低资源占用时间。
（6）使用绑定连接
28、主键和候选键有什么区别？
- 主键： 是数据行的唯一标识。
- 候选键： 是指可以成为主键的字段，并且可以用于任何外键引用。
29、主键与索引有什么区别？
表结构中 主键是唯一不可空的字段，并且创建一个唯一索引；但是其它字段也可以增加一个唯一索引。
（1）主键 一定会创建一个唯一索引，但是唯一索引的列不一定是主键。
（2）主键不允许 null 值，唯一索引允许 null 值。
（3）一个表只能有一个主键，但可以有多个唯一索引。
（4）主键能被引用为外键，唯一索引不能被用于外键。
（5）主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质的区别。
30、MySQL 如何做到高可用方案？
（1）MySQL 高可用：分库分表，通过 MyCat 连接多个 MySQL
（2）MyCat 高可用：Haproxy，连接多个MyCat
（3）Haproxy 高可用：通过 keepalived 辅助 Haproxy
扫一扫
390
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
