MySQL面试题汇总_关于提高insert的性能,以下做法错误的是?设置innodb-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_53379153/article/details/130299667

文章详细介绍了B树和B+树的区别，强调了B+树在数据库索引中的优势，特别是MySQL的InnoDB存储引擎如何利用B+树实现索引，包括主键索引和非聚集索引。讨论了索引的创建、查找过程、覆盖索引、事务的ACID特性以及并发问题。此外，提到了读写分离、分库分表等数据库优化策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

B树和B+树之间的区别是什么？

B+树其实是B树的升级版。

B树的特点是：

1.排序的，节点左侧的节点比自己小，右侧的节点比自己大。

2.一个节点里面可以有多个元素。

3.叶子节点具有相同的深度。

4.所有索引元素不重复

5.节点中的数据索引从左到右递增排列

B+树的特点是：

1.非叶子节点不存储data，只存储索引（冗余），可以放更多的索引。

2.叶子节点具有所有的索引字段

3.叶子节点用指针连接，提高区间访问的性能（比如要查找一部分数据）。

4.叶子节点还有一部分地方是用来存储相邻节点的磁盘文件地址的，更方便了区间访问的操作。

索引的性质：

索引是帮助MySQL高效获取数据的排好序的数据结构。

索引数据结构：二叉树，红黑树，哈希表，B树。

说说MySQL为何选择数据结构：

为了提升效率啊！全表扫描很浪费时间。

首先，存储到数据库的表中实际上就是存储在了数据库的磁盘里面。如果想要查找的话，那实际上就是全表查找，也就是说一个一个查找，那么每一次查找都是一次I/O操作，这个效率是非常低的。因为每一次查找就是把数据加载出来，然后和要找的进行比对，这就是一次I/O操作，很浪费时间。

当列数据如果是自增的话，跟全表扫描就没有区别了。二叉树的话，在插入的时候，会退化成链表。

红黑树是一种特殊的二叉平衡树（弱二叉平衡树），如果数据太多的时候，那么树的高度太大了。

所以最后使用B+树。

B+树是如何查找的？？

从根节点开始，把根节点元素加载到内存中（RAM）中，在内存里面折半查找，找到一个区间，然后开始找第二个节点，现在已经是下一层了，现在也在内存中折半查找，然后确定第三层，直到叶子节点，然后继续查找叶子节点。

因为使用的是内存，加载到内存中，比用IO操作操作磁盘要高效的多。

高版本的MySQL，基本上非叶子节点都是在MySQL初始化的时候，已经存入到内存中了，真正在查找某个索引元素的时候，就是在内存中直接定位到一个叶子节点在磁盘中的位置（该叶子节点的磁盘文件地址），查找到了之后，直接将叶子节点加载到内存中，找到我们要查找的那个索引就可以了，然后对照着索引，从磁盘文件中获得该索引的data，也就是整个过程中，我们只做了一次磁盘的IO操作，这个效率可以说是非常高了。

MyISAM存储引擎索引实现：非聚集索引

MyISAM存储引擎有三个文件：frm文件（表结构文件），MYD（数据文件），MYI（索引文件）

MyISAM索引文件和数据文件是分离的，是非聚集索引。从MYI（索引文件）中按照B+树的方式查找到我们需要查找的索引时，获得索引对应的磁盘文件地址，然后拿到磁盘文件地址之后，去MYD（数据文件）中拿到数据。

黄色那部分是MYI，蓝色那部分是MYD 。

在查完了MYI之后，还要去MYD再查，这个行为其实是回表，效率上面来讲没有InnoDB效率高，InnoDB是聚集索引。

InnoDB存储引擎索引实现：聚集索引

只有frm文件和ibd文件，存储的时候也是按照B+树来进行索引查找，但是叶子节点放的是索引和其他索引所在行的数据。数据和索引放在了一个树上面，我们称为聚集索引，也就是说叶节点包含了完整的数据记录。

查找过程是，先从二级索引表里面找到我们需要的名字，然后同时也找到了对应的索引，再从一级索引（主键索引）的表里面找这个索引对应的完整数据记录。其实这个过程也算是回表，这个过程一直使用的是B+树来查找的。

用二级索引查找没有用一级索引查找快。

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

首先，为什么要使用主键，没有主键的话无法搭建起B+树的结构，所以如果我们自己不设定主键的话，那么在底层，数据库会尝试帮我们从我们的表中找到一个合适的列，比如说里面没有重复值的列作为主键，如果我们的列里面没有合适的列的话，那么会给我们自己创建一个Row_id的列作为提供主键的列。那这样的话还不如我们自己在创建表的时候就设置好主键。

其次，推荐使用整型的主键，在B+树的结构下，如果我们要去查找一个索引的话，那么会将所有非叶子节点都加载到内存中，在内存中使用类似折半查找的方法找到对应的叶子节点，再从叶子节点中找到我们的想要的索引，那么对于折半查找来讲，整型的数字查起来要比其他类型的数据方便很多，比如字符串什么的。

最后，推荐使用自增的主键，永远都是往后面添加索引，节点分裂的概率非常小，但是如果不是自增的话，可能会经常分裂。分裂的原因是，每个节点能够存储的索引数量是固定的，是有上限的，如果超过了上限那么节点就会分裂。分裂是很麻烦的，因为分裂的时候还要调节树里面各个节点的位置，尽量做到减少分裂。

为什么不使用Hash表，而使用B+树呢？

使用Hash表，首先需要确定一列索引，然后通过哈希算法算出索引对应的散列值，再用散列值对数组长度进行取模操作，由此确定索引和索引行对应的磁盘文件地址。整体来说，Hash索引要比B+树索引更高效。但是问题就是Hash索引只能够满足"="，"in"的问题，不能进行模糊查询，因为无法确定一个特定的范围，每次只能找一个，这对于开发来说，效率太太太低了。

索引最左前缀原理：

联合索引（有一个最左前缀原则）：

按照字段排序的顺序进行比较，先比较第一个字段，name字段，然后是age字段，最后再比较position字段。

关于最左前缀原理，为什么当name字段没有在where条件中的时候，是不使用索引的？

因为当name不确定的时候，其实age这个字段的排序是无序的，因为查找索引的过程其实是，先比较的name字段，name字段如果一样，才按照age的大小顺序进行排序。如果搜寻的条件里面没有name字段，那么也就是说age其实是无序的，如果要按照查找索引的方式那么就需要遍历所有的叶子节点去找age和position，就相当于是全表查找，这样的效率非常低。

所以如果要走查找索引的方式，那么必须得有name字段。

当无法走索引的时候，就是全表扫描了，效率会比较低。

InnoDB中的B+树是怎么产生的？

show global status like 'Innodb_page_size'; InnoDB一页的大小是16384

select 16384/1024; 16KB

当InnoDB去磁盘读数据的时候，最小的一个单位是一页，如果InnoDB向磁盘中存数据，至少开辟16KB的大小，每次操作至少是16KB起步。

CREATE TABLE 't1'(

'a' int primary key,

'b' int,

'c' int,

'd' int,

'e' varchar(20)

) ENGINE=InnoDB;

drop table t1;

select * from t1;

show index from t1;

insert into t1 values(4,3,1,1,'d');

insert into t1 values(1,1,1,1,'a');

insert into t1 values(8,8,8,8,'h');

insert into t1 values(5,2,3,6,'e');

insert into t1 values(3,3,2,2,'c');

insert into t1 values(7,4,5,5,'g');

insert into t1 values(6,6,4,4,'f');

在底层是这样的！！！！！

注意：这里面是用空间来换的时间，页目录的位置占用了空间，但是确实在查找上面省去了很大的时间。

用户数据区域：user record

页目录：page directory

其实一个page就是一个节点。

高度为3的B+树能存多少条数据：

首先一个节点（一页）有16KB，那么假设一个int（4个字节）+一个指针（6个字节）是10b，可以存并且指向一个页，那么16KB/10b=16*1024/10=1638。那么第二层如果有1638个，也就代表第二层就是1638*1638，然后最后一层假设一个数据是1KB（当然，不可能只有用户数据区域的部分，这种是理想情况下）16KB/1KB=16,也就是一个叶节点可以存储16条数据，那么1638*1638*16=4200多万。

InnoDB是如何支持范围查找能走索引的：

比如说a>6,那么首先找到a=6的节点里面的数据，那么此时所有右侧的节点里面的数据都是我们需要查找的，将右侧的所有数据都取出来就可以了。

再比如说查找a<6,那么也是首先找到a=6的节点里面的数据，那么此时所有左侧的节点里面的数据都是我们需要查找的，将左侧的所有数据都取出来就可以了。

切记，如果底层是Hash表就不能进行范围查找or模糊查询。

为什么要遵守最左前缀原则才能利用到索引：

最左前缀原则指的是在联合索引的情况下。

这里面涉及到一个问题就是回表，左边的表最后查到了对应的bcd字段后，依然是获得一个主键字段，然后回表，通过主键字段去主键索引里面来继续搜寻到完整的数据片段，然后返回。

举例：

explain select * from t1 where b=1 and c=1 and d=1;(根据多个条件查找)

create index idx_t1_bcd on t1(b,c,d);（这句话应该加在每个select语句的后面）

如果我把上面的换成（explain select * from t1 where c=1 and d=1 and b=1），这种情况下也可以算作是遵守最左前缀原则。走索引。

如果我把上面的换成（explain select * from t1 where c=1 and d=1），这种情况下不可以算作是遵守最左前缀原则。不走索引。

如果我把上面的换成（explain select * from t1 where b=1 and d=1），这种情况下可以算作是遵守最左前缀原则。走索引。这里面是先通过b=1，找到叶子节点，然后再遍历叶子节点，通过d=1的条件去遍历叶子节点，找到我们需要的结果后再去回表到主键索引里面得到完整的数据记录。

如果我把上面的换成（explain select * from t1 where b>1），这种情况下不走索引。因为当我们找到b=1的时候，我们就等于是要b=1右边所有的叶子节点里面的索引。然后每个索引还都需要去回表，这样的效果反而不如全表查找。全表查找的时候等于是遍历全部的数据一共是8条，走索引的话等于遍历b的值大于1的7条数据，但是走索引的话还需要涉及到回表，这样反而更浪费了时间。明明可以直接走，非要绕一个弯儿，这样的话，MySQL底层的优化器就会选择走全表查找，而不是走索引。

如果我把上面的换成（explain select * from t1 where b>6），这种情况下就走索引，因为相对于全表查询来说，这样的效率是更高的。

也就是说，并不是走索引一定就比全表查询效率要高，因为在联合索引中，走索引还会涉及到一个问题就是回表也是需要耗费时间的。

所以由此可知，在联合索引中范围查找有时候可能会导致索引失效。因为MySQL底层的优化器，会判断一下，如果你这个走索引反而消耗时间更长，那么这种情况下，自然会选择全表查找。

所以说在开发中，进行范围查找的时候，尽量要把范围选择的精准一些。

如果我把上面的换成（explain select b from t1 where b>1），那么这种情况下反而走索引了。这也叫覆盖索引。因为这里面我搜的不是全部的字段，我搜的是b字段，而在联合索引的B+树里面已经可以获得b字段了，所以我们就不需要再去回表了，那么这种情况下，还是走索引比较省时间。

覆盖索引：我们要搜的字段（比如题目中搜索的是b字段），正好在联合索引的B+树中有，也就是正好是联合索引的其中之一，那么这种情况下不需要涉及到回表，我们称这为覆盖索引。速度比较快。

只要当前搜索的字段能在当前表里面找到，就不需要涉及到回表，那么这种就叫做覆盖索引。

举个例子：explain select b,c,d,a from t1 where b>1 ，这种情况也叫做覆盖索引。

只要带上e字段，那么就得回表，那么相比较来说回表耗费时间多，还是索引失效，最终依然走全表查询。

如果我把上面的换成（explain select b from t1）,那么这种情况下依然是走索引的。虽然都是查找一个完整的表并且主键索引和联合索引里面都有B字段，但是我只需要获得b字段。那么因为我联合索引里面的B+树的叶子节点是不完整的数据片段，而主键索引里面的B+树的叶子节点是完整的数据片段，这种情况下可能在联合索引里面的B+树扫描3个页就可以得到了，但是可能在主键索引里面需要扫描4个页才行，因为存的数据多嘛。所以自然会选择走索引。

如果我把上面的换成（explain select b from t1）,那么这种情况下依然是走索引的。这里解释一下为什么不符合最左前缀原则的情况下还会去走索引，因为最左前缀原则指的其实是从B+树的根节点开始向下去查找的，这个题里面都是搜索的完整的表，直接从叶子节点开始就可以了。

很多时候当联合索引里面使用order by的时候，那么索引就会失效了。

如果我把上面的换成（explain select * from t1 order by b,c,d）,那么这种情况下索引就失效了。

走联合索引的话，优点就是叶子节点中已经根据b,c,d的顺序排序好了，不需要order by排序了，直接拿出来就能用，但是需要回表，所有的数据都回表返回全部数据记录，这个开销太大了。

全表查找的话，就直接将叶子节点里面的全部数据记录拿出来，然后去内存中排序，就可以返回用了。但是这个排序的过程在内存中是非常快的。所以相对于走联合索引来说，内存中排序的时间根本不值得一提，并且两种方法都需要将所有数据都遍历一遍，所以还是选择全表查找。

如果我把上面的换成（explain select b from t1 order by b,c,d），那么这种情况下就走索引了。

因为涉及到了覆盖索引，我们要查找的b这个字段，已经在联合索引的B+树中就能获得了，不需要回表了，并且已经按照b,c,d的顺序排序好了，所以肯定选择走索引。

MySQL中的数据转换有哪些需要注意的：

根据e这个varchar字段来建立索引（建个B+树出来）。

create index idx_t1_e on t1(e);

explain select * from t1 where a = 1; 走索引主键索引

explain select * from t1 where e = '1'; 走索引 e的索引

explain select * from t1 where a = '1'; 走索引主键索引，因为'1'会转变成1

explain select * from t1 where e = 1; 不走索引 e列所有的值都会变为0，然后再进行搜寻，但是发现没有等于1的行，所以搜不到任何东西。不走索引的原因是：因为要将所有的e里面的varchar字段转化为int类型，那么很可能导致转化后的顺序和转化前的顺序不同，也就是说B+树的结构要重新调整，非常的繁琐，并且调整后的结构无法再次被其他sql语句使用，因为全部都转化成了int类型。所以肯定是走全表查找。

也就是说对字段进行操作，有可能会导致索引失效（无法再次被sql语句使用了）。

当int类型和varchar类型放在一起进行比较的时候，varchar如果是'a','sad','eert'这种字符的话，就会自动转换成int中的数字0，如果是'123','45','66'这种的话，那么会转换成int类型的数字123，45，66。

MySQL都有哪些存储引擎：

MySQL的存储引擎是插件式的，每个存储引擎都有各自的特点。根据具体应用选择不同的存储引擎。

InnoDB存储引擎：MySQL默认的存储引擎，也是最重要，最广泛的，被设计用来处理大量的短期（short-lived）事务。

MyISAM存储引擎：也是默认的存储引擎，但是不支持事务和行级锁，在数据库崩溃后也无法安全恢复。同时MyISAM对整张表加锁，很容易因为表锁的问题导致性能低。

Memory引擎：性能很高，数据文件存储在内存中，表的结构在重启之后会保留，但是数据会丢失。用于保存数据分析中产生的中间数据。

Archive引擎：只支持INSERT和SELECT操作，但是每次SELECT查询都需要执行全表扫描。

CSV引擎：将普通的CSV文件作为MySQL的表来处理，但是这种表不支持索引。

MyISAM和InnoDB的区别是什么：

1.InnoDB支持事务，MyISAM不支持事务。

2.InnoDB支持外键，而MyISAM不支持外键。

3.InnoDB是聚集索引，MyISAM是非聚集索引。

MyISAM是主键索引构成的B+树，下面的叶子节点有全部的索引和索引对应行的磁盘文件地址，那么如果需要得到全部数据的话，还需要根据磁盘文件地址去找到对应的数据（主键索引和辅助索引是独立的），涉及到一个回表的过程。而InnoDB也是主键索引构成的B+树，下面的叶子节点包含了全部的索引和完整的数据记录，因此确定索引之后，就可以返回全部的数据记录了，不需要涉及到回表。

4.InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描，而MyISAM用一个变量保存了整个表的行数，在执行这个语句的时候只需要读出该变量即可，速度很快。

5.InnoDB最小的锁粒度时行锁，MyISAM最小的锁粒度是表锁。

一个更新语句的操作会锁住整张表，导致其他查询和更新的操作都会被阻塞，对并发有很大的影响。而其实如果我们要更新一行的话，就只需要把当下操作的行锁住即可，不需要将整张表锁住。

数据库设计表时，字段如何选择：

如果可以选择整型就不选字符串：

整型是定长的，没有国家和地区之分，没有字符集差异。例如：tinyint和char(1)从空间上看都是一字节，但是order by排序tinyint快。原因是后者需要考虑字符集与校对集(就是排序优先集)。

够用就行了不要假装慷慨：

比如我想存储"age"字段，就是年龄，那么基本上一个tinyint（一个字节）就足够了，可以到255岁，就不需要int去存储，因为这样就会浪费掉3个字节，很浪费内存。还比如varchar的大小，够用就行了，也不需要有太多的剩余空间，要不然在查询的时候，在表中要花更多内存。

尽量避免使用NULL：

不利于查询和索引，尽量在创建字段的时候设置 not null default 的形式。

char与varchar：

char的长度是固定的，所以会可能浪费一些空间，但是char的处理速度很快。可以根据不同的需求选择char和varchar。

MySQL中VARCHAR最多可以存储多少数据：

65535个字节是最大的限度，但是实际存储的字符数取决于字符集的选取。如果我选用的字符集是utf8mb4，那么就是说每个字符最多可以占用4个字节。那此时varchar可以存储的字符数量就是：65535/4=16383。

事务的基本特性（ACID）并且是靠什么保证的：

事务具有ACID特性：

原子性：一个事务就可以理解为一个原子，不可再分，要么是事务里面的操作全部成功，要么是全部失败。

原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚的时候撤销已经执行成功的sql。

一致性：事务如果没有提交成功，那么数据库就会保持一个状态，不会变化的。当事务提交成功的时候，才会使数据库从一个状态改变到另一个状态。

一致性是由其他三大特性保证的，也就是说其他三大特性如果都保证实施了，那么自然就满足了一致性。

隔离性：多个事务之间，彼此应该是相互隔离的，在事务的操作过程当中是不可见，不可干扰的。

隔离性是由MVCC机制来保证的。

持久性：事务提交成功，记录就会永久保存在数据库当中，哪怕系统崩溃了，这个更改也是不变的。

持久性由内存和redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log里面恢复。

MySQL Server中的主从同步就是根据binlog日志完成的，从服务器通过binlog里面的sql执行一遍，保证跟主服务器的数据一直。

当事务并发执行的时候，可能会引发什么问题，怎么解决：

隔离级别为READ UNCOMMITED 可能会引起脏读。

1.脏读（Dirty Read）：一个事务读取到了另一事务尚未提交的数据。

隔离级别为READ COMMITED 解决了脏读，只能读到提交的数据。但是会存在不可重复读现象。

2.不可重复读（NON-REPEATABLE READ）：事务A多次读取同一数据，由于其他事务的修改，导致读到的数据不一致。（单条数据的更新，update语句）

隔离级别为REAPEATED READ 解决了不可重复读的问题，在这种隔离级别下，用户多次读取同一数据，但是始终会读到与事务开始前相同的数据。但是仍会有幻读现象。

3.幻读（Phantom Read）：在同一事务中，由于其他事务插入和删除了满足查询条件的记录，导致查询结果集发生变化。

隔离级别为SERIALIZABLE 这是最高的隔离级别，所有事务都必须按顺序执行，没有并发性。这个级别可以解决脏读、不可重复读和幻读问题，但并发性能较差，因此在需要高并发的场景中不推荐使用。

4.丢失更新（Lost Update）：两个事务并发修改同一数据项，一个事务的修改覆盖了另一事务的修改。

使用乐观锁或者悲观锁来解决。

MySQL的各种索引：

1.主键索引：主键索引就是把主键列当作索引，要求主键列上面的值必须是唯一并且非空的。并且innoDB引擎推荐主键值最好使用整型并且是自增的。

CREATE TABLE table_name (

id INT NOT NULL,

column1 VARCHAR(255),

column2 VARCHAR(255),

PRIMARY KEY (id)

);

2.唯一索引：只要求索引列的值唯一，允许其为空值。

CREATE UNIQUE INDEX index_name ON table_name(column_name);

主键索引是特殊的唯一索引，因为要求主键列的值不为空，而唯一索引允许为空。

3.联合索引：

CREATE INDEX index_name ON table_name(column1,column2);

4.前缀索引：

前缀索引指的是对字符类型字段的前几个字符和二进制类型字段的前几个字节建立的索引，而不是在整个字段上建索引，可以建立在char，varchar，binary，varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

无法使用前缀索引做order by 和 group by。

CREATE INDEX index_name ON table_name(title(10));(假设有一列的列名是title)。

什么是三星索引：

三星索引其实我个人理解就是联合索引的一种，但是是一种特殊的联合索引，因为基于三星索引的查询语句不需要回表，也就是说我要查询的字段的数据都可以从索引中得到，我不需要去通过主键来回表。举个例子：

CREATE INDEX idx_user_product_quantity_price ON orders(user_id, product_id, quantity, price);

SELECT user_id, product_id, SUM(quantity) as total_quantity, SUM(quantity * price) as total_amount FROM orders WHERE user_id = 1 GROUP BY product_id;

如何提高insert的性能：

1.批量插入：一次性插入多条记录，来减少与数据库之间的通信次数。

INSERT INTO table_name (column1, column2, column3, ...) VALUES

(row1_value1, row1_value2, row1_value3, ...),

(row2_value1, row2_value2, row2_value3, ...),

...

(rowN_value1, rowN_value2, rowN_value3, ...);

2.使用事务（注意要使用手动提交事务，因为MySQL底层使用的是）：将多个INSERT语句的操作放在事务中完成，减少提交的次数。

3.设置innodb_flush_log_at_trx_commit = 0，可以提高insert语句的性能。

一共有三种取值，分别为0，1，2。

设置为1时，每次事务提交的时候，InnoDB都会把事务日志刷新到磁盘中。这会导致很多的I/O磁盘操作。

设置为0时，InnoDB会在每次主线程执行的周期（默认为1秒），将事务日志刷新到磁盘，也就意味着多个事务可以一起写入磁盘，减少了磁盘I/O操作，提高了INSERT的性能。但是增加了数据丢失的风险，如果数据库崩溃，最多会丢失1秒内的事务数据。

设置为2时，InnoDB会将事务日志刷新到操作系统缓存中，而不是直接写入磁盘，操作系统会根据一些策略来决定合适将缓存中的数据写入磁盘中。这种性能介于0和1之间，但是一致性比较差。

什么是全局锁，共享锁，排他锁：

全局锁：对整个数据库实例加锁。使数据库处于只读状态，使用该命令之后，数据库更新语句，更新类事务的提交语句等操作都会被阻塞。

共享锁：又称为读锁，指读取操作创建的锁。其他用户可以并发读取数据，但是任何事务都不能对数据进行修改，直到所有的共享锁被释放。

排他锁：若某个事务对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取（但是读取受影响），但不能进行写操作，必须等待排他被释放。排他锁是悲观锁的一种实现。

对该数据行的写操作肯定是被阻塞的，但是对于读操作来说，是有影响的，有以下两种读的方式：

一致性读（Consistent Read）：InnoDB默认的读取方式。当事务进行一致性读时，它会通过多版本并发控制（MVCC）机制读取数据行的一个快照，这样就可以避免等待排他锁被释放。即使一个数据行被加了排他锁，其他事务依然可以进行读操作，但只能读取到锁定之前的数据版本。
锁定读（Locking Read）：在某些情况下，可能需要对数据行加共享锁（Shared Lock）或者排他锁（Exclusive Lock）来进行读操作。此时，其他事务在访问这个被锁定的数据行时，需要等待锁释放。