数据库mysql面试题

1.存储引擎分类有哪些以及使用场景

• Mylsam

• InnoDB

• Memory

• Archive

• Federated

mysql默认的存储引擎为InnoDB，底层存储结构为B+树，B+树的每个节点对应innodb的一个page，page的大小是固定的，一般大小为16k

使用场景

• 经常更新的表，适合处理多重并发的更新请求

• 支持事务

• 可以从灾难中恢复，通过bin-log日志

• 支持外键约束，只有他支持外键

• 行锁

• 支持自动增加列属性

2.创建索引的原则

• 选择唯一性原则：唯一性索引的值是唯一的，可以更快的通过该索引来确定某条记录

• 为经常需要排序、分组以及联合查询的列创建索引

• 为经常做为查询条件的列创建索引

• 越多的索引导致表的查询效率变低，因为索引表每次更新表数据的时候都会重新创建这个表的索引，标的数据越多，索引列越多，那么创建索引的空间消耗就越大

• 如果索引的值很长，那么查询的速度会受到影响

• 如果索引的字段的值很长，最好使用值得前缀来进行索引

• 删除不再使用或者很少使用的索引

• 最左前缀匹配原则，非常重要的原则

• 尽量选择区分度高的列做索引

• 索引列不能参与计算

• 尽量扩展索引，不要新建索引

总而言之：索引就是方便查询的，经常查询的数据给来个索引，一张表最好只建一个索引，索引越多表的查询效率越低，大概就是这个意思

3.在索引失效的情况下？校验sql语句是否使用索引的方式为

在sql前面使用explain关键字

• like以%开头的索引无效，以like&结尾的索引有效

4.索引分类

单列索引

• 普通索引：什么都可以

• 唯一索引：索引列中的值必须是唯一的，但允许是空值，但是只有1个是空值，不然怎么叫唯一

• 主键索引：是一种特殊的唯一索引，不允许有空值

组合索引

• 多个字段组合上创建索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引遵循最左前缀集合

全文索引

• 就是在一堆文字中，通过其中某个关键字，就能找到该字段所属的记录行

空间索引

• 空间索引是对空间数据类型的字段建立的索引

5.什么是索引

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据

6.索引具体采用哪种数据结构呢

常见的mysql主要有两种数据结构：hash索引和b+tree索引，我们使用的是innodb引擎，默认的是b+树

7.innodb为什么采用的B+树，这和hash索引比较起来有什么缺点

因为hash索引底层是哈希表，哈希表是一种key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法通过索引查询的，就需要全表扫描，所以，哈希索引只适用于等值查询的场景，而b+树是一种多路平衡的查询树，所以他的节点是天然有序的，所以对于范围查询的时候不需要做全表扫描

总计一下：hash是键值对的，没有顺序，而b+树有顺序

8.b+tree索引和hash索引区别

• hash索引适合等值查询，但是无法进行范围查询

• hash索引没办法利用索引完成排序

• hash索引不支持多列联合索引的最左匹配规则

• 如果大量重复键值得情况下，hash索引的效率会很低，因为与哈希碰撞的问题

总结一下：hash键值对无序，无法进行范围和排序，并且，hash效率低，容易出现哈希碰撞

9.b+tree的页子节点可以存放哪些东西

可以存储的是整行数据，也有可能是主键的值

10.innoDB的b+tree存储整合数据和主键的值的区别

• 整行数据：innodb的b+tree存储了整行数据的是主键索引，也被称为聚凑索引

• 存储主键的值，成为非主键索引，也被称为非聚凑索引

11.聚凑索引和非聚凑索引，在查询数据的时候有什么区别，为什么

聚凑索引查询会更快些，因为主键索引树的页子节点存储的是整行数据，也就是我们需要得到的数据，而非主键索引的页子节点是主键的值，查询的主键之后，我们还需要通过主键的值再次进行查询数据（这个过程被称为回表）

12. 非主键索引一定会查询多次吗

不一定的，因为通过覆盖索引也可以只查询一次

13.覆盖索引是什么

• 覆盖索引指的是一个查询语句的执行只用从索引中就能获取到，不必从数据表中读取，也可以称之为索引覆盖，当一条查询语句符合覆盖索引条件的时候，mysql只需要通过索引尽可以返回查询所需要的数据，这样就可以避免回表操作，减少io提高效率

14.怎么查询sql语句中是否使用了索引查询

使用explain查询sql语句的执行计划，通过执行计划来分析索引的使用情况

15.优化器的执行过程

• 根据搜索条件，找出可能使用的索引

• 计算全表扫描的代价

• 计算使用不同索引执行查询的代价

• 对比各种执行方案的代价，找到成本最低的那一个

16,什么是触发器，触发器使用场景有哪些

触发器：指一段代码，当触发某个事件时，自动执行这些代码

• 可以通过数据库中的相关表实现级联更新

• 实时监控某张表中的某个字段的更改而需要作出的相应处理

• 例如可以生成某些业务的编号

• 注意不要滥用，否则会造成数据库及应用程序的维护困难

17.mysql中有哪些触发器

• before insert

• afert insert

• befort update

• after update

• before delete

• after delete

18.超键 候选键 主键 外键分别是什么

• 超键：在关系模式中，能唯一标识元组的属性称为超键

• 候选键：是最小的超键，没有冗余元素的超键

• 主键：数据库表中对存储数据对象予以唯一和完整标识的数据列或者属性的组合，一个数据只能有一个逐渐，且主键的取值不嗯不能缺失，即不能为空值

• 外键：在一个表中存在另一个表的主键称此表的外键

19.sql的约束有哪几种

• not null 非空约束，即约束的字段一定不能为null

• unique 唯一约束 唯一唯一，可以为空只能有一个空

• primary 主键约束，不可重复，一个表只允许存在一个

• foreign 外键越是，用于预防破怀表之间连接的动作，也能防止非法数据插入外键

• check 用于控制字段的值范围

20.说说6种关联查询，使用场景

• 交叉连接

• 内连接

• 外连接

• 联合查询

• 全连接

• 交叉连接

21.varchar（50）中50的含义

字段最多可以放50个字符，同时增加一个小知识varchar（50）和varchar（200），同样存放一个helloworld字符串，后者会消耗更多的内存

22.mysql中int（20）和char（20）和varchar（20）的区别

• int表示的是字段类型为int，显示长度为20

• char表示的是字段是固定长度字符串长度为20

• varchar表示的是可变字符串长度，长度为20

23.drop delete和truncate的区别

drop删表 delete删列 truncate是想保留表，删除所有的数据

24.union与union all的区别

• union对两个结果集进行并集操作，不包括重复行，同时进行默然规则的排序

• union all 对两个结果集进行并集操作，包括重复行，不进行排序

• union的效率高于union all

都是合并两个结果集，没有all则会把重复的给去掉

25.sql的生命周期

• 服务器与数据库建立连接

• 数据库进程拿到请求的sql

• 解析并生成执行计划，执行

• 读取数据到内存，并进行逻辑处理

• 通过步骤一的连接，发送结果到客户端

• 关掉连接，释放资源

26.列值为null时，查询是否会用到索引

列值为null也是可以走索引的

计划对列进行索引，应尽量避免把他设置为空，因为这会让mysql难以优化引用了的可空列的查询，同时增加了引擎的复杂度

27，关心过业务系统里面的sql耗时吗。统计过慢查询吗，对慢查询都是怎么优化的

我们平时写sql，都要养成explain分析的习惯，慢查询的统计，运维会定期统计给我们

优化慢查询

• 分析语句，是否加载了不必要的字段数据

• 分许sql执行语句，是否命中了索引

• 如果sql很复杂，优化sql结构

• 如果表的数据量太大，考虑分表

28.主键使用自增id还是uuid，为什么

如果是单击的话，选择自增id，如果是分布式系统的话，优先考虑uuid吧，但还是最好是自己公司有一套分布式唯一id生产方案吧

• 自增id：数据存储空间小，查询效率高，但是如果数据量过大，会超出自增长的值范围，多库合并，也有可能有问题

• uuid：适合大量数据的插入和更新操作，但是他是无序的，插入数据的效率慢，占用空间大

29.mysql自增主键用完了怎么办

自增主键一般为int类型，可以考虑提前分库分表

30.字段为什么要求定义为 not null

null值会占用更多的字节，并且null有很多坑的

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

密码散列，用户身份证号等固定长度的字符串，应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率

32.mysql驱动程序时什么

一个jar包

33.如何优化长难的查询语句，有实战过吗

• 将一个大的查询分成多个小的相同的查询

• 减少冗余记录的查询

• 一个复杂查询可以考虑拆分成多个简单的查询

• 分解关联查询，让缓存的效率更高

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

• 比如用select 具体字段替代 select *

• 使用count(*)而不是count（列名）

• 在不影响业务的情况下，使用缓存

• explain分析你的sql

35.mysql数据库cpu飙升的话，要怎么处理

通过top命令，确定是mysql导致的还是其他原因

如果是musql导致的，show processlist查看session情况，确定是不是有消耗资源的sql在运行，找出消耗高的sql，看看执行计划是否准确，索引是否缺失，数据量是否太大

kill掉这些线程，同时观察cpu使用是否下降，进行相应的调整，比如加索引，改sql改内存参数，重新跑这些sql

36.读写分离的常见方案

应用程序根据业务逻辑来判断，增删改等写操作命令发给主库，查询命令发给备库，利用中间件来做代理，负责对数据库的请求识别出读还是写，并分发到不同的数据库中

37.mysql的复制原理及流程

主从复制原理

• 主数据库有个bin-log二进制文件，记录了所有的增删改sql语句

• 从数据库吧主数据库的big-log文件的sql语句复制过来

• 从数据库的重做日志文件中在执行以下这些sql语句

38.mysql中的datetime和timestamp区别

存储的都是时间，精度都为秒

• datetime的日期范围是1001-9999年 timestamp的时间范围是1970-2038年

• datetime存储时间和时区无关，timestamp存储时间和时区有关，显示的值也依赖于时区

• datetime的存储空间是8字节，timestamp的存储空间是4字节

• datetime的默认值为null，timestarmp的字段的默认值不为空

39.innodb的事务实现原理

• 原子性 undo log来实现

• 持久性 redo log来实现

• 隔离性 通过锁

• 一致性 回滚 恢复 以及并发情况下的隔离性

40.你们数据库是否支持emoji表情存储，如果不支持，如何操作

更换字符集 utf8--utf8mb4

41.mysql如何获取当前日期

select current_date();

42 一个6亿的表，一个3亿的表，通过外键tid关联。你如何最快的查询出满足条件的第50000到第50200中的在这200条数据

43.float和double的区别是什么

float类型的数据存储最多8位十进制数，内存中占4字节

double类型数据可以存储18位十进制数，内存中占8字节

44.说出一些数据库优化方面的经验

• 有外键约束的话会影响增删改的性能，如果应用程序可以保证数据库的完整性那就去除外键

• sql语句全部大写，特别是列名大写，因为数据库的机制是这样，sql语句发送到数据库服务器，数据库首先就会把sql编译成大写执行，如果一开始就是大写，省去了这个步骤

• 如果应用程序可以保证数据库的完整性，可以不需要来按照三大范式来设计数据库

• 其实可以不必要创建很多索引，索引可以加快查询速度，但是索引会消耗磁盘空间

• 如果是jdbc的话，使用preparedStatement不使用statemnt来创建sql，预编译效率肯定要高呀

45.怎么优化sql

• 对查询进行优化，应尽量避免全表扫描，应首先考虑在where及order by涉及的列上建立索引

• 使用索引可以提高查询效率

• select子句中避免使用*号，sql尽量全部大写

• 应避免在where子句中对字段进行is null值得判断，否则将导致引擎放弃使用索引而进行的全局扫描，使用 is not null

• where子句中使用or来连接条件，也会导致引擎放弃使用索引而进行全表扫描

• in 和 not in也要慎用，否则会导致全表扫描

46.你怎么知道sql语句性能是高是低

• 查看sql的执行时间

• 使用explain关键字可以模拟优化sql查询语句，从而知道mysql是如何处理你的sql的。分析你的查询语句或是表结构的性能瓶颈

47.sql的执行顺序

• from

• where

• join

• on

• group by

• having

• select

• distinct

• union

• order by

• limit 1

48.大表数据优化，怎么优化

• 优化schema sql语句+索引

• 第二加缓存

• 主从复制，读写分离

• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统

• 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择合理的sharding key 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量代sharding key 将数据定位到限定的表上去查，而不是扫描全部的表

49.mysql的分页

limit子句可以被强制select语句返回指定的记录数，

50.如何优化长难的查询语句

• 分析是一个复杂查询还是多个简单查询速度快

• mysql内部美妙能扫描内存中上百万行数据，相比之下，响应数据给客户端的就要慢得多

• 使用尽可能小的查询是好的，但是优势将一个大的查询分解成多个小的查询是很有必要的

• 将一个大的查询分成很多小的相同的查询

• 一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销

• 分解关联查询，让缓存的效率更高

• 执行单个查询可以减少锁的竞争

• 在应用层做关联更容易对数据库进行拆分

• 查询效率会大幅提升

• 较少冗余记录的查询

51.为啥使用视图，什么是视图

为了提高复杂sql语句的复用性和表操作的安全性，mysql数据库管理系统提供了视图特性

什么是视图

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成

52.视图有哪些特点，哪些使用场景

为了提高复杂sql的复用性和表操作的安全性

视图

• 是一张虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成

53.视图有哪些特点，哪些使用场景

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系

• 视图是由基本表产生的表

• 视图的建立和删除不影响基本表

• 对视图内容的更新直接影响基本表

• 当视图来自多个基本表时，不循序添加和删除数据

使用场景

• 重用sql语句

• 简化复杂的sql操作

• 使用表的组成部分而不是整个表

• 保护数据

• 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据

54.视图的优点缺点

• 查询简单化，视图能简化用户的操作

• 数据的安全性，视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护

• 逻辑数据的独立性，视图对重构数据库提供了一定程度的逻辑独立性

55.count（1） count（*）count（列名）的区别

• count（*）包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为null

• count（1）包括了忽略所有列，用1代表代码行，在统计结果时，不会忽略值为null

• count（列名）只包括列名的那一列

56.mysql中都有什么锁

• 表级锁，开销小，加锁快，不会出现死锁，锁定粒度大，发生所冲突的概率最高，并发度最低

• 行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高

• 页面锁：开销和加锁时间介于标所和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般

接下来是重点来了，最常问的mysql面试题

57.能说一下myisam和innodb的区别吗

myisam是5.1版本之前的默认引擎，不支持事务和行级锁，不支持外键，索引和数据不存储在一起

innodb是基于聚簇索引建立的，索引和数据存储在一起

58.说下mysql的索引有哪些，聚簇和非聚簇又是什么

索引按照数据结构来说是有b+tree和hash索引

• b+tree是左小右大的顺序存储结构，节点只包含id索引列，而叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引，一张表只能有一个聚簇索引，假设没有定义主键，innodb会选择一个唯一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引

聚簇就是数据和索引指在一块，非聚簇就是不在一块

59.那你知道什么覆盖索引和回表吗

覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们称之为福噶爱索引，而不在进行回表查询，

覆盖索引指的就是一张表有组合索引，一次查询就是的话刚好是这些表的值

60.锁的类型有哪些

mysql锁分为共享锁和排他锁，也就做读锁和写锁

读锁是共享的，可以通过lock 实现，这时候只能读不能写

写锁是排他的，他会阻塞其他的写锁和读锁，从颗粒度来区分，可以分为表锁和行锁两种

表锁会锁定整张表并且阻塞其他用户来对该表的读写操作

行锁又分为乐观锁和悲观锁，悲观锁可以通过for update实现。乐观锁可以通过版本号实现

61,事务的隔离级别

读未提交：可能会读到其他事务未提交的数据，脏读

读已提交：两次读取的结果不一致，叫做不可重复读

可重复读：mysql的默认级别，有可能产生幻读（ 事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读 ）

serializable串行：一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题

62.那ACID是靠什么保证的呢

A是由undolog日志保证

c一般是由代码层面来保证

i隔离性由mvcc来保证

d是由redolog来保证

63.那你说说什么是幻读，什么是mvcc

mvcc叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照

我们每行数实际上隐藏了两列，创建时间版本哈奥，过期删除时间版本号，没开始一个新的事务，版本号都会自动递增

64.你们数据量级多大，分库分表怎么做的

首先分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直在水平

垂直分库

基于现在在微服务拆分来说，都是已经做到了垂直分库了

垂直分表

如果表字段比较多，将不常用数据较大等等做拆分

水平分表

首先根据业务场景来决定使用什么字段作为分表字段，比如我们现在的日订单1000万，我们大部分的场景来自于c端，我们可以将user_id作为sharding_key

65.分表后怎么保证id的唯一的呢

• 设定步长

• 分布式id，自己实现一套分布式id生成算法或者使用开源的比如雪花算法这种

• 分表后不使用主键作为查询依据，每张表单独新增一个子墩作为唯一主键使用，比如订单表的订单号是惟一的，不管最终落在那张表都是基于订单号作为查询依据，更新也一样

66.分表后非sharding_key的查询怎么处理呢

• 可以做一个mapping表，

67.什么是三大范式

第一范式：每个列不可以在拆分

第二范式：非主键列完全依赖主键列

第三范式：非主键列只依赖于主键列，不依赖其他非主键

68.mysql优化

• explain查看执行计划

• sql中的in包含的值不应该过多

  • 因为in中的常量全部存储在一个数组里面，而且这个数组是排好序的，如果数值较多，产生的消耗也是比较大的，能用between就不要用in了，在或者使用连接来替换

• select语句务必指明字段名称

  • select * 增加很多不必要的消耗，增加了适应覆盖索引的可能性，当表结构发生改变时，还要重新查

• 当只需要一条数据的时候，使用limit 1

• 如果排序中没有用到索引，就尽量少排序

• 如果限制条件中其他字段没有索引，尽量少用or（or两边的字段，如果一个不熟索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况，很多时候使用union或者union all的方式来替代or会得到更好的效果）

• 尽量使用union all来替代union

• 不使用order by rand（）

• 使用合理的分页方式提高分页的效率

• 避免在where子句中堆字段进行null值判断（对于null的怕安段会导致引擎放弃使用索引进行全表扫描）

• 不建议使用% 前缀模糊查询（会导致索引失效而进行全表扫描，尽量使用后缀）

• 避免在where子句中对字段进行表达式操作