您现在的位置是：首页 > 原理深入 > Java面试总结Java面试总结
mysql面试专栏
第十三双眼睛2023-10-24【Java面试总结】人已围观
简介mysql面试专栏
mysql面试专栏
1.1MySQL 索引使用有哪些注意事项呢？
可以从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引一些规则特性
索引哪些情况会失效
InnoDB引擎里面有两种索引类型，一种是主键索引、一种是普通索引。
InnoDB用了B+树的结构来存储索引数据。
当使用索引列进行数据查询的时候，最终会到主键索引树中查询对应的数据行进行返回。
理论上来说，使用索引列查询，就能很好的提升查询效率，但是不规范的使用
会导致索引失效，从而无法发挥索引本身的价值。
导致索引失效的情况有很多：
在索引列上做运算，比如使用函数
在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。
当索引列存在隐式转化的时候， 比如索引列是字符串类型，但是在sql查询中没有使用引号。
在索引列使用不等于号、not查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。
使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。
使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。
除了这些场景以外，对于多表连接查询的场景中，连接顺序也会影响索引的使用。
不过最终是否走索引，我们可以使用explain命令来查看sql的执行计划，然后针对性的进行调优即可
1.1.2索引不适合哪些场景
数据量少的不适合加索引
更新比较频繁的也不适合加索引
区分度低的字段不适合加索引（如性别）
1.1.3索引的一些潜规则
覆盖索引
回表
索引数据结构（B+树）
最左前缀原则
索引下推
1.2.日常工作中你是怎么优化 SQL 的？
MySQL的性能优化我认为可以分为4大部分
硬件和操作系统层面的优化
架构设计层面的优化
MySQL程序配置优化
SQL优化
硬件及操作系统层面优化：
从硬件层面来说，影响Mysql性能的因素有，CPU、可用内存大小、磁盘读写速度、网络带宽，从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到Mysql性能。这部分的优化一般由DBA或者运维工程师去完成。
架构设计层面的优化
MySQL是一个磁盘IO访问量非常频繁的关系型数据库
在高并发和高性能的场景中.MySQL数据库必然会承受巨大的并发压力，而此时，我们的优化方式可以分为几个部分。
1.搭建Mysql主从集群，单个Mysql服务容易单点故障，一旦服务器宕机，将会导致依赖Mysql数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高可用性。
2.读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响
3.引入分库分表机制，通过分库可以降低单个服务器节点的IO压力，通过分表的方式可以降低单表数据量，从而提升sql查询的效率。
4.针对热点数据，可以引入更为高效的分布式数据库，比如Redis、MongoDB等，他们可以很好的缓解Mysql的访问压力，同时还能提升数据检索性能。
MySQL程序配置优化
对于Mysql数据库本身的优化，一般是通过Mysql中的配置文件my.cnf来完成的，比如。Mysql5.7版本默认的最大连接数是151个，这个值可以在my.cnf中修改。
binlog日志，默认是不开启
缓存池bufferpoll的默认大小配置等。
由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置官方只会提供一个默认值，具体情况还得由使用者来修改。
关于配置项的修改，需要关注两个方面：
1配置的作用域，分为会话级别和全局
2是否支持热加载
因此，针对这两个点，我们需要注意的是：
全局参数的设定对于已经存在的会话无法生效
会话参数的设定随着会话的销毁而失效
全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效
SQL优化又能分为三步曲
第一、慢SQL的定位和排查
我们可以通过慢查询日志和慢查询日志分析工具得到有问题的SQL列表。
第二、执行计划分析
针对慢SQL，我们可以使用关键字explain来查看当前sql的执行计划.可以重点关注 type key rows filterd 等字段 ，从而定位该SQL执行慢的根本原因。再有的放矢的进行优化
第三、使用show profile工具
Show Profile是MySQL提供的可以用来分析当前会话中，SQL语句资源消耗情况的工具，可用于 SQL调优的测量。在当前会话中.默认情况下处于show profile是关闭状态，打开之后保存最近15次的运行结果
1.2.说说分库与分表的设计
分库分表方案:
水平分库：以字段为依据，按照一定策略（hash、range 等），将一个库中的数据拆分到多个库中。
水平分表：以字段为依据，按照一定策略（hash、range 等），将一个表中的数据拆分到多个表中。
垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
常用的分库分表中间件：
sharding-jdbc（当当）
Mycat
TDDL（淘宝）
Oceanus(58 同城数据库中间件)
vitess（谷歌开发的数据库中间件）
Atlas(Qihoo 360)
分库分表可能遇到的问题
事务问题：需要用分布式事务啦
跨节点 Join 的问题：解决这一问题可以分两次查询实现
跨节点的 count,order by,group by 以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
数据迁移，容量规划，扩容等问题
ID 问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑 UUID
跨分片的排序分页问题（后台加大 pagesize 处理？）
三种应用场景
只分库不分表
当数据库的读写访问量过高，还有可能会出现数据库连接不够用的情况。这个时候我们就需要考虑分库，通过增加数据库实例的方式来获得更多的数据库连接，从而提升系统的并发性能。
只分表不分库
当单表存储的数据量非常大的情况下，并且并发量也不高，数据库的连接也还够用。但是数据写入和查询的性能出现了瓶颈，这个时候就需要考虑分表了。将数据拆分到多张表中来减少单表存储的数据量，从而提升读写的效率。
既分库又分表
结合前面的两种情况，如果同时满足前面的两个条件，也就是数据连接也不够用，并且单表的数据量也很大，从而导致数据库读写速度变慢的情况，这个时候就要考虑既分库又分表。
InnoDB 与MyISAM 的区别
MyISAM和InnoDB都是Mysql里面的两个存储引擎。
在Mysql里面，存储引擎是可以自己扩展的，它的本质其实是定义数据存储的方式以及数据读取的实现逻辑。
在Mysql 5.5之前，默认的存储引擎是MyISAM，从5.5以后，InnoDB就作为了默认的存储引擎。
MyISAM引擎的数据是通过二进制的方式存储在磁盘上，它在磁盘上体现为两个文件
一个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录，
一个是.MYI文件，I代表Index，是MyISAM的索引文件
还有一个.frm文件，是表的结构文件，两种存储引擎都有
因为索引和数据是分离的，所以在进行查找的时候，先从索引文件中找到数据的磁盘位置，再到数据文件中找到索引对应的数据内容。
在InnoDB存储引擎中，数据同样存储在磁盘上，它在磁盘上只有一个ibd文件，里面包含索引和数据。
MyISAm和InnoDB的区别有4个，
第一个，数据存储的方式不同，MyISAM中的数据和索引是分开存储的，而InnoDB是把索引和数据存储在同一个文件里面。
第二个，对于事务的支持不同，MyISAM不支持事务，而InnoDB支持ACID特性的事务处理
第三个，对于锁的支持不同，MyISAM只支持表锁，而InnoDB可以根据不同的情况，支持行锁，表锁，间隙锁，临键锁
第四个，MyISAM不支持外键，InnoDB支持外键
比如如果需要支持事务，那必须要选择InnoDB。如果大部分的表操作都是查询，可以选择MyISAM。
数据库索引的原理，为什么要用 B+树，为什么不用二叉树？
首先，常规的数据库存储引擎，一般都是采用B树或者B+树来实现索引的存储。因为B树是一种多路平衡树，用这种存储结构来存储大量数据，它的整个高度会相比二叉树来说，会矮很多。
而对于数据库来说，所有的数据必然都是存储在磁盘上的，而磁盘IO的效率实际上是很低的，特别是在随机磁盘IO的情况下效率更低。所以树的高度能够决定磁盘IO的次数，磁盘IO次数越少，对于性能的提升就越大，这也是为什么采用B树作为索引存储结构的原因。
在Mysql的InnoDB存储引擎里面，它用了一种增强的B树结构，也就是B+树来作为索引和数据的存储结构。
相比较于B树结构，B+树做了几个方面的优化。
B+树的所有数据都存储在叶子节点，非叶子节点只存储索引。
叶子节点中的数据使用双向链表的方式进行关联。
使用B+树来实现索引的原因，我认为有几个方面。
B+树非叶子节点不存储数据，所以每一层能够存储的索引数量会增加，意味着B+树在层高相同的情况下存储的数据量要比B树要多，使得磁盘IO次数更少。
在Mysql里面，范围查询是一个比较常用的操作，而B+树的所有存储在叶子节点的数据使用了双向链表来关联，所以在查询的时候只需查两个节点进行遍历就行，而B树需要获取所有节点，所以B+树在范围查询上效率更高。
在数据检索方面，由于所有的数据都存储在叶子节点，所以B+树的IO次数会更加稳定一些。
因为叶子节点存储所有数据，所以B+树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但是B树需要遍历整个树。
另外，基于B+树这样一种结构，如果采用自增的整型数据作为主键，还能更好的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。
1.2.事务的隔离级别有哪些？MySQL 的默认隔离级别是什么？
首先，事务隔离级别，是为了解决多个并行事务竞争导致的数据安全问题的一种规范。
具体来说，多个事务竞争可能会产生三种不同的现象。
假设有两个事务T1/T2同时在执行，T1事务有可能会读取到T2事务未提交的数据，但是未提交的事务T2可能会回滚，也就导致了T1事务读取到最终不一定存在的数据产生脏读的现象。
假设有两个事务T1/T2同时执行，事务T1在不同的时刻读取同一行数据的时候结果可能不一样，从而导致不可重复读的问题。
假设有两个事务T1/T2同时执行，事务T1执行范围查询或者范围修改的过程中，事务T2插入了一条属于事务T1范围内的数据并且提交了，这时候在事务T1查询发现多出来了一条数据，或者在T1事务发现这条数据没有被修改，看起来像是产生了幻觉，这种现象称为幻读。
隔离级别，分别是：
读未提交，在这种隔离级别下，可能会产生脏读、不可重复读、幻读。读已提交（RC)，在这种隔离级别下，可能会产生不可重复读和幻读。可重复读（RR），在这种隔离级别下，可能会产生幻读
串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题。
这四种隔离级别里面，只有串行化解决了全部的问题，但也意味着这种隔离级别的性能是最低的。
在Mysql里面，InnoDB引擎默认的隔离级别是RR（可重复读），因为它需要保证事务ACID特性中的隔离性特征。
1.2.InnoDB引擎中的索引策略，了解过吗？
覆盖索引
最左前缀原则
索引下推
索引下推优化是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
最左前缀原则：MySQl 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了
(a)、(a,b)、(a,b,c)三个索引。
覆盖索引：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。
1.1MySQL 索引使用有哪些注意事项呢？
可以从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引一些规则特性
索引哪些情况会失效
InnoDB引擎里面有两种索引类型，一种是主键索引、一种是普通索引。
InnoDB用了B+树的结构来存储索引数据。
当使用索引列进行数据查询的时候，最终会到主键索引树中查询对应的数据行进行返回。
理论上来说，使用索引列查询，就能很好的提升查询效率，但是不规范的使用
会导致索引失效，从而无法发挥索引本身的价值。
导致索引失效的情况有很多：
在索引列上做运算，比如使用函数
在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。
当索引列存在隐式转化的时候， 比如索引列是字符串类型，但是在sql查询中没有使用引号。
在索引列使用不等于号、not查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。
使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。
使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。
除了这些场景以外，对于多表连接查询的场景中，连接顺序也会影响索引的使用。
不过最终是否走索引，我们可以使用explain命令来查看sql的执行计划，然后针对性的进行调优即可
1.1.2索引不适合哪些场景
数据量少的不适合加索引
更新比较频繁的也不适合加索引
区分度低的字段不适合加索引（如性别）
1.1.3索引的一些潜规则
覆盖索引
回表
索引数据结构（B+树）
最左前缀原则
索引下推
1.2.日常工作中你是怎么优化 SQL 的？
MySQL的性能优化我认为可以分为4大部分
硬件和操作系统层面的优化
架构设计层面的优化
MySQL程序配置优化
SQL优化
硬件及操作系统层面优化：
从硬件层面来说，影响Mysql性能的因素有，CPU、可用内存大小、磁盘读写速度、网络带宽，从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到Mysql性能。这部分的优化一般由DBA或者运维工程师去完成。
架构设计层面的优化
MySQL是一个磁盘IO访问量非常频繁的关系型数据库
在高并发和高性能的场景中.MySQL数据库必然会承受巨大的并发压力，而此时，我们的优化方式可以分为几个部分。
1.搭建Mysql主从集群，单个Mysql服务容易单点故障，一旦服务器宕机，将会导致依赖Mysql数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高可用性。
2.读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响
3.引入分库分表机制，通过分库可以降低单个服务器节点的IO压力，通过分表的方式可以降低单表数据量，从而提升sql查询的效率。
4.针对热点数据，可以引入更为高效的分布式数据库，比如Redis、MongoDB等，他们可以很好的缓解Mysql的访问压力，同时还能提升数据检索性能。
MySQL程序配置优化
对于Mysql数据库本身的优化，一般是通过Mysql中的配置文件my.cnf来完成的，比如。Mysql5.7版本默认的最大连接数是151个，这个值可以在my.cnf中修改。
binlog日志，默认是不开启
缓存池bufferpoll的默认大小配置等。
由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置官方只会提供一个默认值，具体情况还得由使用者来修改。
关于配置项的修改，需要关注两个方面：
1配置的作用域，分为会话级别和全局
2是否支持热加载
因此，针对这两个点，我们需要注意的是：
全局参数的设定对于已经存在的会话无法生效
会话参数的设定随着会话的销毁而失效
全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效
SQL优化又能分为三步曲
第一、慢SQL的定位和排查
我们可以通过慢查询日志和慢查询日志分析工具得到有问题的SQL列表。
第二、执行计划分析
针对慢SQL，我们可以使用关键字explain来查看当前sql的执行计划.可以重点关注 type key rows filterd 等字段 ，从而定位该SQL执行慢的根本原因。再有的放矢的进行优化
第三、使用show profile工具
Show Profile是MySQL提供的可以用来分析当前会话中，SQL语句资源消耗情况的工具，可用于 SQL调优的测量。在当前会话中.默认情况下处于show profile是关闭状态，打开之后保存最近15次的运行结果
1.2.说说分库与分表的设计
分库分表方案:
水平分库：以字段为依据，按照一定策略（hash、range 等），将一个库中的数据拆分到多个库中。
水平分表：以字段为依据，按照一定策略（hash、range 等），将一个表中的数据拆分到多个表中。
垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
常用的分库分表中间件：
sharding-jdbc（当当）
Mycat
TDDL（淘宝）
Oceanus(58 同城数据库中间件)
vitess（谷歌开发的数据库中间件）
Atlas(Qihoo 360)
分库分表可能遇到的问题
事务问题：需要用分布式事务啦
跨节点 Join 的问题：解决这一问题可以分两次查询实现
跨节点的 count,order by,group by 以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
数据迁移，容量规划，扩容等问题
ID 问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑 UUID
跨分片的排序分页问题（后台加大 pagesize 处理？）
三种应用场景
只分库不分表
当数据库的读写访问量过高，还有可能会出现数据库连接不够用的情况。这个时候我们就需要考虑分库，通过增加数据库实例的方式来获得更多的数据库连接，从而提升系统的并发性能。
只分表不分库
当单表存储的数据量非常大的情况下，并且并发量也不高，数据库的连接也还够用。但是数据写入和查询的性能出现了瓶颈，这个时候就需要考虑分表了。将数据拆分到多张表中来减少单表存储的数据量，从而提升读写的效率。
既分库又分表
结合前面的两种情况，如果同时满足前面的两个条件，也就是数据连接也不够用，并且单表的数据量也很大，从而导致数据库读写速度变慢的情况，这个时候就要考虑既分库又分表。
InnoDB 与MyISAM 的区别
MyISAM和InnoDB都是Mysql里面的两个存储引擎。
在Mysql里面，存储引擎是可以自己扩展的，它的本质其实是定义数据存储的方式以及数据读取的实现逻辑。
在Mysql 5.5之前，默认的存储引擎是MyISAM，从5.5以后，InnoDB就作为了默认的存储引擎。
MyISAM引擎的数据是通过二进制的方式存储在磁盘上，它在磁盘上体现为两个文件
一个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录，
一个是.MYI文件，I代表Index，是MyISAM的索引文件
还有一个.frm文件，是表的结构文件，两种存储引擎都有
因为索引和数据是分离的，所以在进行查找的时候，先从索引文件中找到数据的磁盘位置，再到数据文件中找到索引对应的数据内容。
在InnoDB存储引擎中，数据同样存储在磁盘上，它在磁盘上只有一个ibd文件，里面包含索引和数据。
MyISAm和InnoDB的区别有4个，
第一个，数据存储的方式不同，MyISAM中的数据和索引是分开存储的，而InnoDB是把索引和数据存储在同一个文件里面。
第二个，对于事务的支持不同，MyISAM不支持事务，而InnoDB支持ACID特性的事务处理
第三个，对于锁的支持不同，MyISAM只支持表锁，而InnoDB可以根据不同的情况，支持行锁，表锁，间隙锁，临键锁
第四个，MyISAM不支持外键，InnoDB支持外键
比如如果需要支持事务，那必须要选择InnoDB。如果大部分的表操作都是查询，可以选择MyISAM。
数据库索引的原理，为什么要用 B+树，为什么不用二叉树？
首先，常规的数据库存储引擎，一般都是采用B树或者B+树来实现索引的存储。因为B树是一种多路平衡树，用这种存储结构来存储大量数据，它的整个高度会相比二叉树来说，会矮很多。
而对于数据库来说，所有的数据必然都是存储在磁盘上的，而磁盘IO的效率实际上是很低的，特别是在随机磁盘IO的情况下效率更低。所以树的高度能够决定磁盘IO的次数，磁盘IO次数越少，对于性能的提升就越大，这也是为什么采用B树作为索引存储结构的原因。
在Mysql的InnoDB存储引擎里面，它用了一种增强的B树结构，也就是B+树来作为索引和数据的存储结构。
相比较于B树结构，B+树做了几个方面的优化。
B+树的所有数据都存储在叶子节点，非叶子节点只存储索引。
叶子节点中的数据使用双向链表的方式进行关联。
使用B+树来实现索引的原因，我认为有几个方面。
B+树非叶子节点不存储数据，所以每一层能够存储的索引数量会增加，意味着B+树在层高相同的情况下存储的数据量要比B树要多，使得磁盘IO次数更少。
在Mysql里面，范围查询是一个比较常用的操作，而B+树的所有存储在叶子节点的数据使用了双向链表来关联，所以在查询的时候只需查两个节点进行遍历就行，而B树需要获取所有节点，所以B+树在范围查询上效率更高。
在数据检索方面，由于所有的数据都存储在叶子节点，所以B+树的IO次数会更加稳定一些。
因为叶子节点存储所有数据，所以B+树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但是B树需要遍历整个树。
另外，基于B+树这样一种结构，如果采用自增的整型数据作为主键，还能更好的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。
1.2.事务的隔离级别有哪些？MySQL 的默认隔离级别是什么？
首先，事务隔离级别，是为了解决多个并行事务竞争导致的数据安全问题的一种规范。
具体来说，多个事务竞争可能会产生三种不同的现象。
假设有两个事务T1/T2同时在执行，T1事务有可能会读取到T2事务未提交的数据，但是未提交的事务T2可能会回滚，也就导致了T1事务读取到最终不一定存在的数据产生脏读的现象。
假设有两个事务T1/T2同时执行，事务T1在不同的时刻读取同一行数据的时候结果可能不一样，从而导致不可重复读的问题。
假设有两个事务T1/T2同时执行，事务T1执行范围查询或者范围修改的过程中，事务T2插入了一条属于事务T1范围内的数据并且提交了，这时候在事务T1查询发现多出来了一条数据，或者在T1事务发现这条数据没有被修改，看起来像是产生了幻觉，这种现象称为幻读。
隔离级别，分别是：
读未提交，在这种隔离级别下，可能会产生脏读、不可重复读、幻读。读已提交（RC)，在这种隔离级别下，可能会产生不可重复读和幻读。可重复读（RR），在这种隔离级别下，可能会产生幻读
串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题。
这四种隔离级别里面，只有串行化解决了全部的问题，但也意味着这种隔离级别的性能是最低的。
在Mysql里面，InnoDB引擎默认的隔离级别是RR（可重复读），因为它需要保证事务ACID特性中的隔离性特征。
1.2.InnoDB引擎中的索引策略，了解过吗？
覆盖索引
最左前缀原则
索引下推
索引下推优化是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
最左前缀原则：MySQl 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了
(a)、(a,b)、(a,b,c)三个索引。
覆盖索引：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。
Tags：
很赞哦！ ()
上一篇：Spring 面试题（二）
下一篇：消息队列面试专栏
文章评论
本站推荐
猜你喜欢
