第二大脑

主题

2026-08-25
3587
12.8m

数据库 (p34) ​

1.你所知道的存储引擎有哪些？区别？ ​

MySQL 支持多种存储引擎，比如 InnoDB，MyISAM，Memory，Archive 等等。

在大多数的情况下，直接选择使用 InnoDB 引擎都是最合适的，==InnoDB 也是 MySQL 的默认存储引擎。==

  • InnoDB 支持事物，而 MyISAM 不支持事物
  • InnoDB 支持行级锁，表锁，而 MyISAM 支持表级锁
  • InnoDB 支持 MVCC，而 MyISAM 不支持
  • InnoDB 支持外键，而 MyISAM 不支持
  • InnoDB5.7 之前不支持全文索引，而 MyISAM 支持
  • nnoDB 必须有主键，没有指定会默认生成一个隐藏列作为主键，而 MyISAM 可以没有

MyISAM 存储引擎：不支持事务、外键，支持表级锁（表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁），

InnoDB 存储引擎：MySQL5.5 版本之后的默认存储引擎，支持事务，支持行级锁（行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁），支持聚集索引方式存储数据

2.数据库的事务特性？ ​

ACID

原子性，在同一个事务中的 SQL 语句，要么全部执行成功，要么全部执行失败。

一致性，张三向李四转 100 元，转账前和转账后的数据是正确的状态，这就叫一致性，如果出现张三转出 100 元，李四账号没有增加 100 元这就出现了数据错误，就没有达到一致性。

隔离性，事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

持久性，持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

例如我们在使用 JDBC 操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

3.隔离级别 ​

读未提交，一个事务可以读取另一个未提交事务的数据。有脏读

读已提交，读提交，能解决脏读问题。 只能读到已经提交了的内容。 有不可重复读

可重复读，就是专门针对“不可重复读”这种情况而制定的隔离级别，自然，它就可以有效的避免“不可重复读”。而它也是 MySql 的默认隔离级别。

事例：程序员拿着信用卡去享受生活（卡里当然是只有 3.6 万），当他埋单时（事务开启，不允许其他事务的 UPDATE 修改操作），收费系统事先检测到他的卡里有 3.6 万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

可串行化，事务“串行化顺序执行”，也就是一个一个排队执行。这种级别下，“脏读”、“不可重复读”、“幻读”都可以被避免，但是执行效率奇差，性能开销也最大，所以基本没人会用

脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种数据还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据还没有提交那么另外一个事务读取到的这个数据我们称之为脏数据。依据脏数据所做的操作肯能是不正确的。
不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有执行结束，另外一个事务也访问该同一数据，那么在第一个事务中的两次读取数据之间，由于第二个事务的修改第一个事务两次读到的数据可能是不一样的，这样就发生了在一个事物内两次连续读到的数据是不一样的，这种情况被称为是不可重复读。
幻象读：一个事务先后读取一个范围的记录，但两次读取的纪录数不同，我们称之为幻象读（两次执行同一条 select 语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中）

4.如何避免索引失效？ ​

-1.如果条件中有 or，即使其中有条件带索引也不会使用 (这也是为什么尽量少用 or 的原因)

-2.索引字段的值不能有 null 值，有 null 值会使该列索引失效

3.对于多列索引，不是使用的第一部分，则不会使用索引（最左原则）

4.like 查询以% 开头

5.如果列类型是字符串，那一定要在条件中将数据使用单引号引用起来,否则不使用索引

6.在索引的列上使用表达式或者函数会使索引失效

例如：select * from users where YEAR(adddate) < 2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate < ’2007-01-01′。

（

1.范围查询, 右边的列不能使用索引, 否则右边的索引也会失效

2.不要在索引上使用运算, 否则索引也会失效.

3.字符串不加引号, 造成索引失效.

4.尽量使用覆盖索引, 避免 select *, 这样能提高查询效率

  1. or 关键字连接：or 的前面列有索引，后面没有索引，那么查询时候前后索引都会失效。

）

5.联合索引最左原则 [必会] ​

联合索引的最左原则就是建立索引 KEY union_index (a,b,c) 时，等于建立了 (a)、(a,b)、(a,b,c) 三个索引，从形式上看就是索引向左侧聚集，所以叫做最左原则，因此最常用的条件应该放到联合索引的组左侧。

6.MYSQL 中索引存储的数据结构？ ​

B+Tree

1650448365675-68f5901d-9dce-4a96-84a9-a1e416e4d974.png

B+Tree 中只有叶子结点会带有指向具体记录的指针。

B+Tree 中所有的叶子结点通过指针连接在一起。

B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。

  1. B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。
  2. B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。

一个 B+Tree 可以存多少条数据呢？

一个三层的 B+Tree 可以存储的数据量为 2100 万 条数据。

在 InnoDB 存储引擎中，B+Tree 的高度一般为 2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次 IO，那我们通过主键索引查询的时候，其实最多只需要 2-4 次 IO 操作就可以了。

7.按照物理存储方式，可以分为聚簇索引和非聚簇索引。 ​

主键索引，其实就是聚簇索引（Clustered Index）; 主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index），或者叫作辅助索引。

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

  • 主键索引的叶子结点存储的是一行完整的数据。
  • 非主键索引的叶子结点存储的则是主键值。

所以，当我们需要查询的时候：

  1. 如果是通过主键索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
  2. 如果是通过非主键索引来查询数据，例如 select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

对于第二种查询方式而言，一共搜索了两棵 B+Tree，**第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表3、慎用 in 和 not in；

4、尽量避免大事务操作，提高系统并发能力。

9.索引优化？ ​

1650468081920-5e97d97a-d5fa-4fab-bbe8-7fac91e2d89d.png

10.Mysql 深度分页怎么解决？ ​

我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。

讨论如何优化 MySQL 百万数据的深分页问题，四个方案：

把条件转移到主键索引树：把查询条件，转移回到主键索引树，那就可以减少回表次数啦

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10; 写漏了，可以补下时间条件在外面

INNER JOIN 延迟关联：延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了 inner join 代替子查询。

标签记录法：就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。假设上一次记录到 100000，则 SQL 可以修改为：select id,name,balance FROM account where id > 100000 order by id limit 10;

使用 between...and...：可以将limit查询转换为已知位置的查询，这样 MySQL 通过范围扫描between...and，就能获得到对应的结果。

如果知道边界值为 100000，100010 后，就可以这样优化：

select id,name,balance FROM account where id between 100000 and 100010 order by id;

11.Mysql 预读机制？ ​

InnoDB 使用两种预读算法来提高 I/O 性能：线性预读 (linear read-ahead) 和随机预读 (randomread-ahead)

12.覆盖索引？ ​

即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

13.数据库设计？ ​

遵循数据库三范式

第一范式：数据表中每个字段都必须是不可拆分最小单元，确保每一列的原子性

第二范式：表中每一列必须有唯一性，都必须依赖于主键

第三范式：表中的每一列都必须与主键直接相关，而不是间接相关，字段没有冗余。

有时候可以根据场景合理地反规范化：

A：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。

B：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。

C：分割表。

数据表拆分：主要就是垂直拆分和水平拆分。

水平切分: 将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。

垂直切分: 将表中大字段单独拆分到另外一张表, 形成一对一的关系。

D: 字段设计

  1. 表的字段尽可能用 NOT NULL
  2. 字段长度固定的表查询会更快
  3. 把数据库的大表按时间或一些标志分成小表

14.数据库锁 ​

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

表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

15.悲观锁、乐观锁 ​

（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。适用于多读的应用类型，这样可以提高吞吐量

最近更新