MySQL 面试中常见的问题有哪些？

正好，前几天刚给公司招聘了一个Java后端开发的人员

小于哥：ACID是什么?可以详细说一下吗?

候选人：A=Atomicity：原子性，就是要么全部成功，要么全部失败。不可能只执行一部分操作。

候选人：C=Consistency：一致性，系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态。

候选人：I=Isolation：隔离性，通常来说：一个事务在完全提交之前，对其他事务是不可见的.注意前面的通常来说加了红色，意味着有例外情况。

候选人：D=Durability：持久性，一旦事务提交，那么就永远是这样子了，哪怕系统崩溃也不会影响到这个事务的结果。

小于哥：请说一下索引的区别：

候选人：一个表只能有一个主键索引，但是可以有多个唯一索引。

• 主键索引一定是唯一索引，唯一索引不是主键索引。
• 主键可以与外键构成参照完整性约束，防止数据不一致。
• 联合索引：将多个列组合在一起创建索引，可以覆盖多个列。（也叫复合索引，组合索引）
• 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作（基本不用）。
• 全文索引：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索 （基本不用）

小于哥：一个B+树的节点中到底存多少个元素合适呢？

候选人： 答案是：B+树中一个节点为一页或页的倍数最为合适。

候选人：因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费；如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费；所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

小于哥：Mysql中B+树的一个节点大小为多大呢？

候选人：这个问题的答案是“1页”，这里说的“页”是Mysql自定义的单位（其实和操作系统类似），Mysql的Innodb引擎中一页的默认大小是16k（如果操作系统中一页大小是4k，那么Mysql中1页=操作系统中4页），可以使用命令SHOW GLOBAL STATUS like 'Innodbpagesize'; 查看。

候选人：并且还可以告诉你的是，一个节点为1页就够了。

小于哥：什么是最左前缀原则？

候选人：MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
复制代码

候选人：这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

候选人：由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

小于哥：数据库三大范式是什么

候选人：第一范式：每个列都不可以再拆分。

候选人：第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

候选人：第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

小于哥：varchar和char的使用场景?

候选人：1.char的长度是不可变的，而varchar的长度是可变的。

定义一个char[10]和varchar[10]。

如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

候选人：2.char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。

char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。

varchar是以空间效率为首位。

候选人：3.char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。 varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

候选人：4.两者的存储数据都非unicode的字符数据。

小于哥：请说说乐观锁与悲观锁的区别

候选人：对于悲观锁来讲

候选人：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

候选人：对于乐观锁来讲

候选人：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

小于哥：请说说两种锁的使用场景？

候选人：从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

小于哥：在项目开发的时候，大表遇到数据量过大，怎么优化？

候选人：当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

候选人：限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；

候选人：读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；

候选人：缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

候选人：垂直分区：

候选人：根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。 简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

​

编辑切换为居中

img

候选人：水平分区：

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

​

编辑切换为居中

数据库水平拆分

水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水品拆分最好分库 。

小于哥：请说说 乐观锁常见的两种实现方式

候选人：乐观锁一般会使用版本号机制或CAS算法实现。

小于哥：请说说MyISAM存储引擎应用场景有哪些？

候选人：MyISAM 是 MySQL 5.1 及之前的版本的默认的存储引擎。MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数（GIS)等，但MyISAM 不「支持事务和行级锁」，对于只读数据，或者表比较小、可以容忍修复操作，依然可以使用它。

小于哥：请说说MySQL存储引擎MyISAM与InnoDB区别

候选人：存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

候选人：常用的存储引擎有以下：

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

小于哥：int(20)中20的含义

候选人：int(20)是指显示字符的长度，不影响内部存储，只是当定义了ZEROFILL时，前面补多少个 0

小于哥：什么是触发器，MySQL中都有哪些触发器？

触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器：

候选人：Before Insert

候选人：After Insert

候选人：Before Update

候选人：After Update

候选人：Before Delete

候选人：After Delete

小于哥：MySQL查询字段区不区分大小写？

候选人：答案是不区分

小于哥能做到区分吗？

候选人：不能吧

​

编辑切换为全宽

添加图片注释，不超过 140 字（可选）

\

小于哥：如何解决需要区分英文大小写的场景

小于哥：解决方案一

MySQL默认的字符检索策略：utf8_general_ci，表示不区分大小写。

可以使用utf8_general_cs，表示区分大小写，也可以使用utf8_bin，表示二进制比较，同样也区分大小写 。

小于哥：直接修改sql语句，在要查询的字段前面加上binary关键字

 -- 在每一个条件前加上binary关键字
select * from user where binary username = 'admin' and binary password = 'admin';
 
-- 将参数以binary('')包围
select * fromuserwhere username likebinary('admin') andpasswordlikebinary('admin');
复制代码

小于哥：一个6亿的表a，一个3亿的表b，通过外键tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

候选人：1、如果A表TID是自增长,并且是连续的,B表的ID为索引

select * from a,b where a.tid = b.id and a.tid>50000limit200;

候选人：2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

select * from b , (select tid from a limit50000,200) a where b.id = a .tid;

小于哥：Hash索引和B+树所有有什么区别或者说优劣呢?

候选人：首先要知道Hash索引和B+树索引的底层实现原理:

候选人：hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

候选人：那么可以看出他们有以下的不同:

候选人：hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.

候选人：因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.

• hash索引不支持使用索引进行排序,原理同上.
• hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性.
• hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
• hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

候选人：因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

小于哥：主键使用自增ID还是UUID?

候选人：推荐使用自增ID,不要使用UUID.

候选人：因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.

候选人：总之,在数据量大一些的情况下,用自增主键性能会好一些.

补充：MySQL 有哪些数据类型？

MySQL 数据类型非常丰富，常用类型简单介绍如下：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT。

浮点数类型：FLOAT、DOUBLE、DECIMAL。

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB。

日期类型：Date、DateTime、TimeStamp、Time、Year。

其他数据类型：BINARY、VARBINARY、ENUM、SET...

其实很多大概率不会问，平常用的的很少，很多没啥真的技术含量，就理论知识硬背，所以小于哥就给大家整理了一下~

不会有人刷到这还想白嫖吧？不会吧？点赞对真的我很重要！要不加个关注？