Java八股|MySql系列

Chrison_mu

于 2024-09-10 10:58:29 发布

阅读量313

点赞数 8

文章标签： java mysql 数据库

本文链接：https://blog.csdn.net/weixin_52875110/article/details/127309492

版权

1.MySql基础

1.关系型和非关系型数据库的区别？

关系型数据库的优点

容易理解，因为它采用了关系模型来组织数据。

可以保持数据的一致性。

数据更新的开销比较小。

支持复杂查询（带 where 子句的查询）

非关系型数据库（NOSQL）的优点（Redis是nosql）

无需经过 SQL 层的解析，读写效率高。

基于键值对，读写性能很高，易于扩展

可以支持多种类型数据的存储，如图片，文档等等。

扩展（可分为内存性数据库以及文档型数据库，比如 Redis，MongoDB，HBase 等，适合场景：数据量大高可用的日志系统/地理位置存储系统）。

2.详细说一下一条 MySQL 语句执行的步骤

Server 层按顺序执行 SQL 的步骤为：

客户端请求 -> 连接器（验证用户身份，给予权限）

查询缓存（存在缓存则直接返回，不存在则执行后续操作）（没有打在数据库上的意思）

分析器（对 SQL 进行词法分析和语法分析操作）

优化器（主要对执行的 SQL 优化选择最优的执行方案方法）

执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

redolog 先写日志在写磁盘（酒店老板的小黑板）有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。物理日志，在某个数据页做了什么修改，循环写binlog逻辑日志，记录这个语句的原始逻辑，追加写

3.常见指令

a. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称
b. show databases; -- 显示mysql中所有数据库的名称
c. show columns from table_name from database_name; 或show columns from
database_name.table_name; -- 显示表中列名称
d. show grants for user_name; -- 显示一个用户的权限,显示结果类似于grant 命令
e. show index from table_name; -- 显示表的索引
f. show status; -- 显示一些系统特定资源的信息,例如,正在运行的线程数量
g. show variables; -- 显示系统变量的名称和值
h. show processlist; -- 显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查
看他们自己的进程,但是如果他们拥有process权限,就可以查 看所有人的进程,包括密码。
i. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的
最新更新时间
j. show privileges; -- 显示服务器所支持的不同权限
k. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库
l. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库
m. show engies; -- 显示安装以后可用的存储引擎和默认引擎。
n. show innodb status; -- 显示innoDB存储引擎的状态
o. show logs; -- 显示BDB存储引擎的日志
p. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知
q. show errors; -- 只显示最后一个执行语句所产生的错误
r. show [storage] engines; --显示安装后的可用存储引擎和默认引擎
s. show procedure status --显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过 程名称,
创建时间等
t. show create procedure sp_name --显示某一个存储过程的详细信息

4.两张表join数据库底层如何执行

t1驱动表，t2被驱动表

对驱动表t1做了全表扫描，这个过程需要扫描100行；
而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
所以，整个执行流程，总扫描行数是200。
使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
如果使用join语句的话，需要让小表做驱动表。

被驱动表上没有可用的索引，算法的流程是这样的：

把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

如果你的join语句很慢，就把join_buffer_size改大。

如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

5.join语句怎么优化

Multi-Range Read优化(MRR)。这个优化的主要目的是尽量使用顺序读盘。

根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;
将read_rnd_buffer中的id进行递增排序；
排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

这里，read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的。如果步骤1中，read_rnd_buffer放满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。之后继续找索引a的下个记录，并继续循环。
MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
那怎么才能一次性地多传些值给表t2呢？方法就是，从表t1里一次性地多拿些行出来，一起传给表t2。
既然如此，我们就把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是join_buffer。join_buffer 在BNL算法里的作用，是暂存驱动表的数据。但是在NLJ算法里并没有用。那么，我们刚好就可以复用join_buffer到BKA算法中。

BNL转BKA
一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成BKA算法了。

把表t2中满足条件的数据放在临时表tmp_t中；
为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
让表t1和tmp_t做join操作。

2.索引

1.MySQL 使用索引的原因

根本原因

索引的出现，就是为了提高数据查询的效率，就像书的目录一样。

对于数据库的表而言，索引其实就是它的“目录”。

扩展

创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

帮助引擎层避免排序和临时表

将随机 IO 变为顺序 IO，加速表和表之间的连接。

2.索引分类

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）

2.1创建索引的原则

1.索引最左匹配原则
2.为经常需要排序、分组操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.限制索引的数目
5.尽量选择区分度高的列作为索引
6.索引列不能参与计算
7.扩展索引
8.条件带like 注意事项
9.尽量使用数据量少的索引
10.尽量使用前缀来索引
11.删除不再使用或者很少使用的索引
12.=和in可以乱序。

2.2最左匹配原则

索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。
如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。
例子：
如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。(很简单：索引命中只能是相等的情况，不能是范围匹配）

3.索引的三种常见底层数据结构以及优缺点

三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。

哈希表这种适用于等值查询的场景，比如 memcached 以及其它一些 NoSQL 引擎，不适合范围查询。

有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但更新数据成本高。（更新就要发生数组的移动，在性能方面可能就没有那么高了）

N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。

扩展（以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。）

4.索引的常见类型以及它是如何发挥作用的？

根据叶子节点的内容，索引类型分为主键索引（聚簇索引）和非主键索引（非聚簇索引|辅助索引）。

主键索引的叶子节点存的整行数据，在InnoDB里也被称为聚簇索引。

非主键索引叶子节点存的主键的值，在InnoDB里也被称为二级索引。

4.1B+树与B树对比

B+tree 非叶子节点只存储键值信息，数据记录都存放在叶子节点中。而B-tree的非叶子节点也存储数据。所以B+tree单个节点的数据量更小，在相同的磁盘I/O次数下，能查询更多的节点。
B+tree 所有叶子节点之间都采用单链表连接。适合MySQL中常见的基于范围的顺序检索场景，而B-tree无法做到这一点。
B树大量应用在数据库和文件系统当中。

它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。B树算法减少定位记录时所经历的中间过程，从而加快存取速度。
假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据，如果换成二叉查找树，则需要20层！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。
如mongoDB数据库使用，单次查询平均快于Mysql（但侧面来看Mysql至少平均查询耗时差不多

4.2B+树与hash索引比较

5.MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？

InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。

MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，叶节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的。

6.InnoDB 为什么设计 B+ 树索引？

两个考虑因素：

InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。

CPU 将磁盘上的数据加载到内存中需要花费大量时间。

为什么选择 B+ 树：

哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。

B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO。

而 B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO。

普通索引还是唯一索引？

由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发建议你优先考虑非唯一索引。

7.什么是覆盖索引和索引下推？

覆盖索引：

在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推：

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

Chrison_mu

关注

8
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
Java八股|MySql系列

覆盖索引：在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。索引下推：MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
复制链接

扫一扫