MySQL面试题

最新推荐文章于 2023-11-21 23:15:36 发布

开水烫蛤蟆

最新推荐文章于 2023-11-21 23:15:36 发布

阅读量548

点赞数

分类专栏： Java面试文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/jiayoubaobei2/article/details/121802475

版权

Java面试专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库三大范式

数据类型使用策略

MySQL中myisam与innodb的区别

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

存储过程与函数

优化长难的查询语句

drop、delete与truncate的区别

数据库三大范式

第一范式(1NF)指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割
第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分
第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖
我的最终总结
- 第一范式：列不可再分，每列的值具有原子性，不可再分割
- 第二范式：非主键列需完全依赖于主键
- 第三范式：非主键列没有相互依赖

数据类型使用策略

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销

MySQL中myisam与innodb的区别

MyISAM:

不支持事务，以及主外键，但是每次查询都是原子的;
支持表级锁，即每次操作是对整个表加锁
存储表的总行数，查询总行数速度快
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性，每次查询都需要两次操作，第一次查索引文件，第二次查数据文件

InnoDb:

支持ACID的事务，支持事务的四种隔离级别，也支持主外键;
支持行级锁及外键约束:因此可以支持写并发;
不存储总行数;一个InnoDb引擎存储在一个文件空间 (共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里)，也有可能为多个(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制
必须有主键索引，采用聚集索引

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统

什么是索引

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。
你所加列上的索引值经过某种计算将地址放到数据结构中，经过算法能够快速查找，可以将无序的数据进行有序的查询，对这个排序结果生成一个倒排表，同样也有个地址链与之对应，根据地址链拿到里面的数据

索引的优缺点

优点
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
缺点
- 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
- 空间方面：索引需要占物理空间

order by原理

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存sort_buffer中使用内部排序，最后返回排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更不算读到内存之后的排序了。
即从磁盘中读到内存中进行排序，在内存中进行排序然后返回结果
但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

索引类型

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
- 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
- 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
全文索引：是目前搜索引擎使用的一种关键技术。
- 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引，论文里找关键字那种的
联合索引:素可以覆盖多个数据列，如像INDEXIColumnA columnBI索引

B+Tree原理

下面是mysql用的一个索引数据结构

B+tree大致原理

浅色的块称为磁盘块，每个磁盘块里面包含几个数据项和指针
真实的数据存在叶子节点中，非叶子节点不存储真实数据，只存储指引搜索方向的数据项
如果查找某一数据项的话，会把对应的磁盘块由磁盘加载到内存中，发生一次io，在内存中用二分法锁定指针，根据指针所指磁盘地址，将对应的磁盘块加载到内存中，发生了第二次io，然后再次锁定相应的指针，将对应的内容加载到内存中，共三次io，在内存种做二分法找到对应的数据

hash索引的底层就是hash表，适合单条记录查询；B+Tree索引是平衡二叉树，关键字查询效率比较平均，在大量重复键值情况下，hash索引的效率也是极低的，因为存在hash碰撞

索引设计的原则

查询更快，占用空间更小

用在where关键字后面作为条件查询的字段，或者子链接中指定的列适合做索引
较频繁作为查询条件的字段才去创建索引
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
使用短索引(就是不对长字符串的数据列)，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
更新频繁字段不适合创建索引
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的联合索引，那么只需要修改原来的索引即可
定义有外键的数据列一定要建立索引
对于定义为text、image和bit的数据类型的列不要建立索引

事务的四大特性(ACID)

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的，通过另外三个特性保证一致性
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

事务的隔离级别

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，但是虽然读到的都是已经提交的数据，但是有可能每次都到的数据不一样，但是这个数据确实是真实的，可以阻止脏读，但是幻读或不可重复读仍有可能发生，oracle就是这个默认级别，程序中有可能出现每次读到的数据是不一样的，解决这个问题一个是提高隔离级别，一个是通过加上读锁的方式，这样写锁就加不进来无法进行修改了
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生，每次都到的数据都是一样的，假如期间有别的事务对事务进行了修改，他读到的还是原来的数值，他不关心你又做了哪些更改，里面read-view是不变的，你再怎么更新数据都不会变得，所以每次读到的都是一样的，所以是可重复读，这个只针对查询操作，你做新增操作的话，就会影响到可重复读，查询每次都到都是一样的，但是进行了插入操作的话，读到的数据就不一样了，造成了幻读的问题
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读，幻读就是第一次读读到5条，第二次读就有可能读到6条
RC和RR都是采用MVCC机制，RR底层只是针对查询操作，对于插入操作的话每次读取到的数据是不一样的，会出现幻读这种情况

脏读、不可重复读和幻读概念

脏读
- 某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的
- 数据更改未提交就能读到
不可重复读
- 能读到数据更改已提交的数据
- 在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据
幻读
- 在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的
- 能读到新增数据已提交的数据
如何解决幻读
- 将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
- 间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作

约束有哪几种

非空约束NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
唯一约束UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
主键约束PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
外键约束FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一
检查约束CHECK: 用于控制字段的值范围

varchar与char的区别

char的特点
- char表示定长字符串，长度是固定的；
- 如果插入数据的长度小于char的固定长度时，则用空格填充；
- 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法
- 对于char来说，最多能存放的字符个数为255，和编码无关
varchar的特点
- varchar表示可变长字符串，长度是可变的；
- 插入的数据是多长，就按照多长来存储；
- varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法
- 对于varchar来说，最多能存放的字符个数为65532

慢查询日志(一般DBA、运维去做)

查询比较慢的sql语句的日志记录
用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考

具体操作

开启慢查询日志
- 配置项：slow_query_log
- 可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件
设置临界时间
- 配置项：long_query_time
- 查看：show VARIABLES like 'long_query_time'，单位秒
- 设置：set long_query_time=0.5
实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉
查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

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

查询条件有没有命中索引
- 分析语句的执行计划，加入使用了效率比较低的索引，肯定有优化空间的
是不是查询了不需要的数据列
- 首先分析sql语句，看有没有加载不需要的数据列，就是尽量不要使用select *
是不是数据量太大
- 如果确实是因为数据量太大，进行横向或者纵向分表
访问数据太多导致查询性能下降
总是返回全部列。解决办法：避免使用SELECT *
重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果
使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化

存储过程与函数

什么是存储过程存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快
优点
- 存储过程是预编译过的，执行效率高
- 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯
- 安全性高，执行存储过程需要有一定权限的用户
- 存储过程可以重复使用，减少数据库开发人员的工作
缺点
- 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
- 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
- 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
- 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦

优化长难的查询语句

将一个大的查询分为多个小的相同的查询
观察sql语句，比如一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销
执行单个查询可以减少锁的竞争
优化关联查询
- 确定ON或者USING子句中是否有索引。
- 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引
优化子查询
- 用关联查询替代
- 优化GROUP BY和DISTINCT
- 这两种查询据可以使用索引来优化，是最有效的优化方法
- 关联查询中，使用标识列分组的效率更高
优化UNION查询
- UNION的效率高于UNION ALL
优化WHERE子句
- 定位低效SQL语句
- 先从索引着手
- 应尽量避免全表扫描
- 应尽量避免在 where 子句中使用!=或<>操作符
- 应尽量避免在 where 子句中使用or 来连接条件
- in 和 not in 以及模糊查询和函数也要慎用，否则会导致全表扫描

drop、delete与truncate的区别

三者都表示删除，但是三者有一些差别

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate

什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率

数据库的乐观锁和悲观锁是什么？怎么实现的

概述：
- 数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段
乐观锁：
- 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现
悲观锁:
- 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
两种锁的使用场景
- 从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量
- 但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适

聚集索引和非聚集索引的区别

聚集索引
- 将数据存储与索引放到了一块，并按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的
非聚集索引
- 叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置，再去磁盘查找数据，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引
区别及优缺点
- 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
- 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
- 聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
- 非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
- 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块
- 优缺点
  - 聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快

MySQL执行计划

执行计划就是sql的执行查询的顺序，以及使用索引的查询，返回的结果集行数
通过explain对查询语句进行分析
id越大，执行的优先级越高，几个id就几个sql查询
selectType表示一个查询类型，子查询，衍生查询，联合查询啥的
table就是查询的表
type：优化sql查询的重要手段，判断优化程度的指标，all代表全表查询最差查询，range范围查询，ref非唯一性索引扫描，eq-ref唯一性索引扫描，只有一条记录与之匹配，避免all和index
possible_keys:可能用到的索引信息
key：真正用到的索引信息

MySQL主从同步原理

Mysql的主从复制中主要有三个线程: master (binlog dump thread) 、slave (I/O thread 、SQLthread)，Master-条线程和Slave中的两条线程

主节点binlog,主从复制的基础是主库记录数据库的所有变更记录到binlog. binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
主节点 log dump线程，当binlog有变动时，log dump线程读取其内容并发送给从节点。
从节点I/O线程接收binlog内容，并将其写入到relay log文件中。
从节点的SQL线程读取relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性。
注:主从节点使用binglog文件+ position偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从position的位置发起同步，第一次同步到为什么位置了，第二次同步就从这个位置进行同步，是增量同步