面试考点（六）——数据库考点汇总（二）

最新推荐文章于 2022-10-26 17:05:55 发布

--流星。

最新推荐文章于 2022-10-26 17:05:55 发布

阅读量277

点赞数

分类专栏：刷题总结文章标签：面试数据库

本文链接：https://blog.csdn.net/weixin_45974176/article/details/123706089

版权

刷题总结专栏收录该内容

14 篇文章 0 订阅

订阅专栏

数据库考点汇总系列

1、面试考点（六）——数据库考点汇总（一）
2、面试考点（六）——数据库考点汇总（二）
3、面试考点（六）——数据库考点汇总（三）

问题

8、乐观锁和悲观锁的区别

1、悲观锁

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

2、乐观锁

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

适用场景：

乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
悲观锁适用于经常产生冲突，上层应用会不断的进行retry（重试）。

9、B+树的结构、优点

B+树的结构：
在这里插入图片描述

1、B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。
之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。
另外，B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储1000×1000×1000=10 亿个数据。一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

2、因为 B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。
那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点，要实现这一点是很不容易的。
有心的读者可能还发现上图 B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
其实上面的 B 树我们也可以对各个节点加上链表。这些不是它们之前的区别，是因为在 MySQL 的 InnoDB 存储引擎中，索引就是这样存储的。也就是说上图中的 B+ 树索引就是 InnoDB 中 B+ 树索引真正的实现方式，准确的说应该是聚集索引。

通过上图可以看到，在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

B+树的优点：

IO次数更少：由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
遍历更加方便：B+树的叶子结点都是相链的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

10、mysql的性能优化有哪些

SQL优化

加上LIMIT限制，避免查询不需要记录
查询具体的字段而非全部字段
优化子查询
尽量使用小表驱动大表
对于连续数值，使用BETWEEN不用IN，in可能会索引失效
排查慢SQL
尽量使用主键查询和覆盖索引

索引优化

(1) 对于使用like的查询，查询如果是’%a’不会使用到索引 ,而 like 'a%'就会用到索引。最前面不能使用%和_这样的变化值;
(2) 应尽量避免在 where 子句中使用 != 或 < > 操作符，为这些操作符会导致查询引擎放弃索引而进行全表扫描。优化方法： key<>0 改为 key>0 or key<0。
(3) 在索引列上避免使用 IS NULL 或 IS NOT NULL操作，索引是不索引空值的。
(4) 不要在列上进行运算操作
(5) 字符串不加单引号索引失效;
(6)组合索引，如果没有使用第一列索引，索引失效；
(7) 在 MySQL 5.0 之前的版本应尽量避免在 where 子句中使用 or 来连接条件,因为当or语句前后没有同时使用索引，该索引失效而进行全表扫描，只有当or左右查询字段均为索引时，才会生效；

数据库结构优化

适当增加冗余字段
选择合适的字段类型
分库分表

系统硬件优化

磁盘
网络
内存

11、explain的使用，分析出有哪些字段

explain的使用：

注意：MYSQL 5.6.3以前只能EXPLAIN SELECT;
MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE

mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | staff | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

分析的字段：
在这里插入图片描述

注：
select_type：

table：
查询的表名。不一定是实际存在的表名。
可以为如下的值：

<unionM,N>: 引用id为M和N UNION后的结果。
< derivedN>: 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。
< subqueryN>: 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。

type:
从最好到最差的连接类型依次为：
system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

extra：
distinct：在select部分使用了distinc关键字
Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
Using index：“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

12、联合索引的最左原则（最左前缀匹配原则）是什么

最左前缀匹配原则的特性：

对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是（a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
= 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立（a，b，c）索引可以任意顺序。
如果建立的索引顺序是（a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

最左前缀匹配原则的成因：