数据库面试题

key:索引，事务

一、基本概念
1.主键、外键、超键、候选键

2.为什么用自增列作为主键（？）

3.触发器的作用？
触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。
它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。

4.什么是存储过程？用什么来调用？
存储过程是一个预编译的SQL语句，模块化，一次创建，多次调用，比单纯SQL快

调用
1）可以用一个命令对象来调用存储过程。
2）可以供外部程序调用，比如：java程序。

优点：
1）存储过程是预编译过的，执行效率高。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3）安全性高，执行存储过程需要有一定权限的用户。
4）存储过程可以重复使用，可减少数据库开发人员的工作量。

缺点：
移植性差

与函数的区别:

5.什么叫视图？游标是什么？
视图：是一种虚拟的表，具有和物理表相同的功能。试图通常是有一个表或者多个表的行或列的子集
优点：

1）对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。

2)用户通过简单的查询可以从复杂查询中得到结果。

3)维护数据的独立性，试图可从多个表检索数据。

4)对于相同的数据可产生不同的视图。

缺点：

性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据

游标：是对查询出来的结果集作为一个单元来有效的处理。需要逐条处理数据的时候，游标显得十分重要。（感觉像是迭代器）

6.drop、truncate、 delete区别

drop直接删掉表。
truncate删除表中数据，再插入时自增长id又从1开始。
delete删除表中数据，可以加where字句。

7.什么是临时表，临时表什么时候删除?
临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

11.非关系型数据库和关系型数据库区别，优势比较?

13.什么是 内连接、外连接、交叉连接、笛卡尔积等?

14.varchar和char的使用场景?
1.char的长度是不可变的，而varchar的长度是可变的。
2.char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。
3.char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。char类型存储的时候是初始预计字符串再加上一个记录字符串长度的字节，占用空间较大。
varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。varchar类型存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小。
4.两者的存储数据都非unicode的字符数据。

15.SQL语言分类
SQL语言共分为四大类：
数据查询语言DQL
数据操纵语言DML
数据定义语言DDL
数据控制语言DCL。

16.like %和-的区别
通配符的分类
%百分号通配符:表示任何字符出现任意次数(可以是0次).
_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.
like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.

17.count()、count(1)、count(column)的区别
count()对行的数目进行计算,包含NULL
count(column)对特定的列的值具有的行数进行计算,不包含NULL值。
count()还有一种使用方式,count(1)这个用法和count(*)的结果是一样的。

18.最左前缀原则（？）

二、索引
1.什么是索引？
数据库索引，是数据库管理系统中一个排序的数据结构，索引的实现通常使用B树及其变种B+树。

协助快速查询、更新数据库表中数据。

一是增加了数据库的存储空间
二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

4.哪些列适合建立索引、哪些不适合建索引？

5.什么样的字段适合建索引
唯一、不为空、经常被查询的字段

6.MySQL B+Tree索引和Hash索引的区别?
B+树索引需要从根节点到枝节点，hash索引一次到位

1.MySQL 索引使用有哪些注意事项呢？
索引哪些情况会失效，索引不适合哪些场景，索引规则

索引哪些情况会失效

查询条件包含or，可能导致索引失效
如何字段类型是字符串，where时一定用引号括起来，否则索引失效
like通配符可能导致索引失效。
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
在索引列上使用mysql的内置函数，索引失效。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
索引字段上使用is null， is not null，可能导致索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景
数据量少的不适合加索引
更新比较频繁的也不适合加索引
区分度低的字段不适合加索引（如性别）

索引的一些潜规则
覆盖索引
回表
索引数据结构（B+树）
最左前缀原则
索引下推

2.MySQL 遇到过死锁问题
查看死锁日志show engine innodb status;
找出死锁Sql
分析sql加锁情况
模拟死锁案发
分析死锁日志
分析死锁结果

3.怎么优化SQL

4.分库与分表的设计
5.InnoDB与MyISAM的区别

3.数据库索引的原理，为什么要用 B+树，为什么不用二叉树？

为什么不是一般二叉树？
如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？
B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

为什么不是B树而是B+树呢？
B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。
B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

7.聚集索引与非聚集索引的区别
一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

何时使用聚集索引或非聚集索引？



8.limit 1000000 加载很慢的话，你是怎么解决的呢？

9.如何选择合适的分布式主键方案

10.事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
读未提交（Read Uncommitted）
读已提交（Read Committed）
可重复读（Repeatable Read）
串行化（Serializable）
Mysql默认的事务隔离级别是可重复读(Repeatable Read)

11.什么是幻读，脏读，不可重复读呢

平衡二叉树，b树，b+树，红黑树

\12. 在高并发情况下，如何做到安全的修改同一行数据？(乐观锁和悲观锁)
悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外



乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

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

show status 命令了解各种 sql 的执行频率
通过慢查询日志定位那些执行效率较低的 sql 语句
explain 分析低效 sql 的执行计划

14.select for update有什么含义，会锁表还是锁行还是其他。
查询+加悲观锁+没用索引/主键的话就是表锁，否则就是是行锁。

15.MySQL事务得四大特性以及实现原理

原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
隔离性：通过锁以及MVCC,使事务相互隔离开。
一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

16.如果某个表有近千万数据，CRUD比较慢，如何优化。
分库分表(存在相关问题:分表方案、中间件、解决方案等)、优化表结构、索引优化

17.如何写sql能够有效的使用到复合索引
用户可以在多个列上建立索引,这种索引叫做复合索引。

需要关注查询Sql条件的顺序，确保最左匹配原则有效，同时可以删除不必要的冗余索引。

18.mysql中in 和exists的区别
主查询A,次查询B
in先b后a，exists先a后b
B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exists

\19. 数据库自增主键可能遇到什么问题
做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用UUID哈
自增主键会产生表锁，从而引发问题
自增主键可能用完问题。

20.MVCC熟悉吗，它的底层原理
MVCC,多版本并发控制,它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并发性能的一种机制。
知识点:
事务版本号
表的隐藏列
undo log
read view

21.数据库中间件了解过吗，sharding jdbc，mycat？

22.MYSQL的主从延迟，你怎么解决

主从同步延迟的原因

主从同步延迟的解决办法

❝Mysql主从有什么优点？为什么要选择主从？❞
高性能方面：主从复制通过水平扩展的方式，解决了原来单点故障的问题，并且原来的并发都集中到了一台Mysql服务器中，现在将单点负载分散到了多台机器上，实现读写分离，不会因为写操作过长锁表而导致读服务不能进行的问题，提高了服务器的整体性能。
可靠性方面：主从在对外提供服务的时候，若是主库挂了，会有通过主从切换，选择其中的一台Slave作为Master；若是Slave挂了，还有其它的Slave提供读服务，提高了系统的可靠性和稳定性。

❝若是主从复制，达到了写性能的瓶颈，你是怎么解决的呢？❞
主从模式对于写少读多的场景确实非常大的优势，但是总会写操作达到瓶颈的时候，导致性能提不上去。

这时候可以在设计上进行解决采用分库分表的形式，对于业务数据比较大的数据库可以采用分表，使得数据表的存储的数据量达到一个合理的状态。

也可以采用分库，按照业务进行划分，这样对于单点的写，就会分成多点的写，性能方面也就会大大提高。

❝主从复制的过程有数据延迟怎么办？导致Slave被读取到的数据并不是最新数据。❞
主从复制有不同的复制策略，对于不同的场景的适应性也不同，对于数据的实时性要求很高，要求强一致性，可以采用同步复制策略，但是这样就会性能就会大打折扣。

若是主从复制采用异步复制，要求数据最终一致性，性能方面也会好很多。只能说，对于数据延迟的解决方案没有最好的方案，就看你的业务场景中哪种方案使比较适合的。

23.大表查询的优化方案
优化shema、sql语句+索引；
可以考虑加缓存，memcached, redis，或者JVM本地缓存；
主从复制，读写分离；
分库分表；

24.什么是数据库连接池?为什么需要数据库连接池呢?
「连接池基本原理：」 数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。

「应用程序和数据库建立连接的过程：」

通过TCP协议的三次握手和数据库服务器建立连接
发送数据库用户账号密码，等待数据库验证用户身份
完成身份验证后，系统可以提交SQL语句到数据库执行
把连接关闭，TCP四次挥手告别。
「数据库连接池好处：」

资源重用 (连接复用)
更快的系统响应速度
新的资源分配手段
统一的连接管理，避免数据库连接泄漏

25.一条SQL语句在MySQL中如何执行的

第一层负责连接处理，授权认证，安全等等
第二层负责编译并优化SQL
第三层是存储引擎。

26.InnoDB引擎中的索引策略
覆盖索引
最左前缀原则
索引下推

27.数据库存储日期格式时，如何考虑时区转换问题？
datetime类型适合用来记录数据的原始的创建时间，修改记录中其他字段的值，datetime字段的值不会改变，除非手动修改它。
timestamp类型适合用来记录数据的最后修改时间，只要修改了记录中其他字段的值，timestamp字段的值都会被自动更新。

28.一条sql执行过长的时间，你如何优化，从哪些方面入手？
查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等
优化索引结构，看是否可以适当添加索引
数量大的表，可以考虑进行分离/分表（如交易流水表）
数据库主从分离，读写分离
explain分析sql语句，查看执行计划，优化sql
查看mysql执行日志，分析是否有其他方面的问题

29.MYSQL数据库服务器性能分析的方法命令有哪些?

30.Blob和text有什么区别
Blob用于存储二进制数据，而Text用于存储大字符串。
Blob值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中的字节的数值。
text值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

31.mysql里记录货币用什么字段类型比较好
Decimal和Numric类型

32.Mysql中有哪几种锁

33.Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？
B+树可以进行范围查询，Hash索引不能。
B+树支持联合索引的最左侧原则，Hash索引不支持。
B+树支持order by排序，Hash索引不支持。
Hash索引在等值查询上比B+树效率更高。
B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。

36.mysql有关权限的表有哪几个呢
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。

37.Mysql的binlog有几种录入格式？分别有什么区别？
statement，row和mixed。

38.InnoDB引擎的4大特性
插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

39.索引有哪些优缺点
「优点：」

唯一索引可以保证数据库表中每一行的数据的唯一性
索引可以加快数据查询速度，减少查询时间
「缺点：」

创建索引和维护索引要耗费时间
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
以表中的数据进行增、删、改的时候，索引也要动态的维护。

索引有哪几种类型
主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

创建索引有什么原则呢

最左前缀匹配原则
频繁作为查询条件的字段才去创建索引
频繁更新的字段不适合创建索引
索引列不能参与计算，不能有函数操作
优先考虑扩展索引，而不是新建索引，避免不必要的索引
在order by或者group by子句中，创建索引需要注意顺序
区分度低的数据列不适合做索引列(如性别）
定义有外键的数据列一定要建立索引。
对于定义为text、image数据类型的列不要建立索引。
删除不再使用或者很少使用的索引

40.百万级别或以上的数据，你是如何删除的？
我们想要删除百万数据的时候可以先删除索引
然后批量删除其中无用数据
删除完成后重新创建索引。

41.什么是最左前缀原则？什么是最左匹配原则？
最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

42.B树和B+树的区别，数据库为什么使用B+树而不是B树？

43.覆盖索引、回表等这些，了解过吗
覆盖索引： 查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

44.B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？
不用

45.非聚簇索引一定会回表查询吗
不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询

46.组合索引是什么？为什么需要注意组合索引中的顺序？
组合索引，用户可以在多个列上建立索引,这种索引叫做组合索引。

因为InnoDB引擎中的索引策略的最左原则，所以需要注意组合索引中的顺序。

47.什么是数据库事务

48.隔离级别与锁的关系
先阐述四种隔离级别，再阐述它们的实现原理。隔离级别就是依赖锁和MVCC实现的。

49.从锁的类别角度讲，MySQL都有哪些锁呢？
共享锁，排它锁

50.MySQL中InnoDB引擎的行锁是怎么实现的？
索引for update

51.为什么要使用视图？什么是视图？
为了提高复杂SQL语句的复用性和表操作的安全性
虚拟的表

视图有哪些特点？哪些使用场景？

优点缺点

52.count(1)、count() 与 count(列名) 的区别？
count()包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

MySQL中都有哪些触发器
Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete

56.mysql中int(20)和char(20)以及varchar(20)的区别
int(20) 表示字段是int类型，显示长度是 20
char(20)表示字段是固定长度字符串，长度为 20
varchar(20) 表示字段是可变长度字符串，长度为 20

57.drop、delete与truncate的区别

58.UNION与UNION ALL的区别
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
UNION的效率高于 UNION ALL

59.SQL的生命周期
服务器与数据库建立连接
数据库进程拿到请求sql
解析并生成执行计划，执行
读取数据到内存，并进行逻辑处理
通过步骤一的连接，发送结果到客户端
关掉连接，释放资源

61.列值为NULL时，查询是否会用到索引
可以 但会增加难度和开销

62.关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？
「优化慢查询：」

分析语句，是否加载了不必要的字段/数据。
分析SQl执行句话，是否命中索引等。
如果SQL很复杂，优化SQL结构
如果表数据量太大，考虑分表

63.主键使用自增ID还是UUID，为什么？
如果是单机的话，选择自增ID；如果是分布式系统，优先考虑UUID吧，但还是最好自己公司有一套分布式唯一ID生产方案吧。

自增ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长的值范围，多库合并，也有可能有问题。
uuid：适合大量数据的插入和更新操作，但是它无序的，插入数据效率慢，占用空间大。

64.mysql自增主键用完了怎么办
提前分库分表

65.字段为什么要求定义为not null
null值会占用更多的字节，并且null有很多坑的

66.如果要存储用户的密码散列，应该使用什么字段进行存储？
char

67.Mysql驱动程序是什么
Mysql驱动程序主要帮助编程语言与 MySQL服务端进行通信，如连接、传输数据、关闭等。

68.如何优化长难的查询语句？
将一个大的查询分为多个小的相同的查询
减少冗余记录的查询。
一个复杂查询可以考虑拆成多个简单查询
分解关联查询，让缓存的效率更高。

69.优化特定类型的查询语句

70.MySQL数据库cpu飙升的话，要怎么处理呢？

71.读写分离常见方案

72.500台db，在最快时间之内重启
可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。
也可以使用 salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多线程工具同时操作多台服务

73.你们数据库是否支持emoji表情存储，如果不支持，如何操作？
更换字符集utf8-->utf8mb4

74.Mysql一条SQL加锁分析
组合一：id列是主键，RC隔离级别
组合二：id列是二级唯一索引，RC隔离级别
组合三：id列是二级非唯一索引，RC隔离级别
组合四：id列上没有索引，RC隔离级别
组合五：id列是主键，RR隔离级别
组合六：id列是二级唯一索引，RR隔离级别
组合七：id列是二级非唯一索引，RR隔离级别
组合八：id列上没有索引，RR隔离级别
组合九：Serializable隔离级别