2023-11-27 10:55 门头沟学院 Java

关注

MySQL|基础知识

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

关系型数据库的优点
- 容易理解。因为它采用了关系模型来组织数据。
- 可以保持数据的一致性。
- 数据更新的开销比较小。
- 支持复杂查询（带where子句的查询）
非关系型数据库的优点
- 不需要经过SQL层的解析，读写效率高。
- 基于键值对，数据的扩展性很好。
- 可以支持多种类型数据的存储，如图片，文档等等

2、什么是非关系型数据库？

非关系型数据库也叫NOSQL，采用键值对的形式进行存储。

它的读写性能很高，易于扩展，可分为内存性数据库以及文档型数据库，比如 Redis，Mongodb，HBase等等。

适合使用非关系型数据库的场景：

日志系统
地理位置存储
数据量巨大
高可用

18、假如你的电脑内存很小，要送一个海量数据库中读取数据，进行计算；有什么好的方法？

可以考虑以下方法：

使用游标

使用游标：游标是数据库操作的一种方法，可以逐行或逐批读取数据而不将其全部加载到内存中。通过使用游标，可以逐行读取数据库中的数据，并进行相应的计算操作。

数据分块读取

将海量数据库中的数据划分为多个小块，每次只读取一小块数据到内存中进行计算，然后释放内存。这样可以避免一次性读取大量数据导致内存不足的问题。

使用索引优化查询在海量数据库中使用索引可以提高查询效率，减少需要读取的数据量。通过优化查询语句和创建适当的索引，可以减少内存占用和计算所需的数据量。

数据压缩和存储优化对于海量数据库中的数据，可以采用数据压缩算法进行压缩，减少存储空间占用。同时，可以对数据进行存储优化，选择适当的存储格式，如列式存储，以减少内存占用和提高计算效率。

使用分布式计算框架如果单台电脑无法处理海量数据库的计算需求，可以考虑使用分布式计算框架，如Hadoop或Spark，将计算任务分发到多台计算机上进行并行计算。这样可以充分利用多台计算机的内存和计算资源，提高计算效率。

采用增量计算如果海量数据库中的数据是动态变化的，可以考虑采用增量计算的方式，只计算最新的数据或变化的部分数据，避免每次都重新计算整个数据库。

综上所述，通过数据分块读取、索引优化查询、数据压缩和存储优化、使用分布式计算框架和采用增量计算等方法，可以有效地在内存较小的情况下处理海量数据库的数据计算需求。

3. 说一下MySQL是如何执行一条SQL的？具体步骤有哪些？

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

客户端请求->
连接器（验证用户身份，给予权限） ->
查询缓存（存在缓存则直接返回，不存在则执行后续操作）->
分析器（对SQL进行词法分析和语法分析操作） ->
优化器（主要对执行的sql优化选择最优的执行方案方法） ->
执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）->
去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）简单概括：

连接器：管理连接、权限验证；
查询缓存：命中缓存则直接返回结果；（MySQL 8.0 版本直接将查询缓存的整块功能删掉了）
分析器：对SQL进行词法分析、语法分析；（判断查询的SQL字段是否存在也是在这步）
优化器：执行计划生成、选择索引；
执行器：操作引擎、返回结果；
存储引擎：存储数据、提供读写接口。

4. 说一说MySQL的内部构造？一般可以分为哪两个部分？

可以分为服务层和存储引擎层两部分，其中：

服务层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认的存储引擎。

5. 说一说Drop、Delete与Truncate的共同点和区别

Drop、Delete、Truncate都表示删除，但是三者有一些差别：

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除，会触发这个表上所有的delete触发器。
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。
Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用Drop；在想删除部分数据行时候，用Delete；在保留表而删除所有数据的时候用Truncate。

第二种回答

Drop直接删掉表;
Truncate删除表中数据，再插入时自增长id又从1开始 ;
Delete删除表中数据，可以加where字句。

DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
一般而言，drop > truncate > delete
应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view
TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
delete语句为DML（Data Manipulation Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
truncate、drop是DDL（Data Define Language),操作立即生效，原数据不放到 rollback segment中，不能回滚
在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果与事务无关，用truncate即可实现。如果和事务有关，或老是想触发trigger,还是用delete。
Truncate table 表名速度快,而且效率高,因为: truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

6. MySQL优化了解吗？说一下从哪些方面可以做到性能优化？

为搜索字段创建索引
避免使用 Select *，列出需要查询的字段
垂直分割分表
选择正确的存储引擎
慎用连接操作

B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；

B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

7. 听说过视图吗？那游标呢？

视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能游标是对查询出来的结果集作为一个单元来有效的处理。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

8. 数据库为什么要进行分库和分表呢？都放在一个库或者一张表中不可以吗？

分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。

通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用，此外，可以很大的缓解表锁的问题。分表策略可以归纳为垂直拆分和水平拆分： 水平分表：取模分表就属于随机分表，而时间维度分表则属于连续分表。如何设计好垂直拆分，我的建议：将不常用的字段单独拆分到另外一张扩展表. 将大文本的字段单独拆分到另外一张扩展表, 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中。对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

分库与分表带来的分布式困境与应对之策 数据迁移与扩容问题----一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。分页与排序问题----需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户。

9 视图的作用是什么？可以更改吗？

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的 sql 操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。

视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。

创建视图：create view xxx as xxxx

对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

10 假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段？

设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
选择合适的表字段数据类型和存储引擎，适当的添加索引。
MySQL库主从读写分离。
找规律分表，减少单表中的数据量提高查询速度。
添加缓存机制，比如Memcached，Apc等。
不经常改动的页面，生成静态页面。
书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

11 数据库中的主键、超键、候选键、外键是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键
候选键：不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！
主键：用户选作元组标识的一个候选键程序主键
外键：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

12 SQL中的NOW()和CURRENT_DATE()两个函数有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。

CURRENT_DATE（）仅显示当前年份，月份和日期。

13 MySQL中CHAR和VARCHAR的区别有哪些？

char的长度是不可变的，用空格填充到指定长度大小，而varchar的长度是可变的。
char的存取数度还是要比varchar要快得多
char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

14 SQL语法中内连接、自连接、外连接（左、右、全）、交叉连接的区别分别是什么？

内连接：只有两个元素表相匹配的才能在结果集中显示。
外连接：左外连接: 左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
全外连接：连接的表中不匹配的数据全部会显示出来。
交叉连接：笛卡尔效应，显示的结果是链接表数的乘积。

15 你知道哪些数据库结构优化的手段？

范式优化：比如消除冗余（节省空间。。）
反范式优化：比如适当加冗余等（减少join）
限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；
拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

16 关于拆分数据表你了解哪些？

拆分其实又分垂直拆分和水平拆分

案例：简单购物系统暂设涉及如下表：

1.产品表（数据量10w，稳定）

2.订单表（数据量200w，且有增长趋势）

3.用户表（数据量100w，且有增长趋势）

以 MySQL 为例讲述下水平拆分和垂直拆分，MySQL能容忍的数量级在百万静态数据可以到千万

垂直拆分

解决问题：表与表之间的io竞争

不解决问题：单表中数据量增长出现的压力

方案：把产品表和用户表放到一个server上订单表单独放到一个server上

水平拆分

解决问题：单表中数据量增长出现的压力

不解决问题：表与表之间的io争夺

方案：用户表 通过性别拆分为男用户表和女用户表，订单表 通过已完成和完成中拆分为已完成订单和未完成订单，产品表 未完成订单放一个server上，已完成订单表盒男用户表放一个server上，女用户表放一个server上(女的爱购物哈哈)。

17 数据库高并发是我们经常会遇到的，你有什么好的解决方案吗？

在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
主从读写分离，让主服务器负责写，从服务器负责读。
将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
使用分布式架构，分散计算压力。

18、ASCII、Unicode和UTF-8编码的区别？

ASCII

ASCII 只有127个字符，表示英文字母的大小写、数字和一些符号，但由于其他语言用ASCII 编码表示字节不够，例如：常用中文需要两个字节，且不能和ASCII冲突，中国定制了GB2312编码格式，相同的，其他国家的语言也有属于自己的编码格式。

Unicode

由于每个国家的语言都有属于自己的编码格式，在多语言编辑文本中会出现乱码，这样Unicode应运而生，Unicode就是将这些语言统一到一套编码格式中，通常两个字节表示一个字符，而ASCII是一个字节表示一个字符，这样如果你编译的文本是全英文的，用Unicode编码比ASCII编码需要多一倍的存储空间，在存储和传输上就十分不划算。

UTF-8

为了解决上述问题，又出现了把Unicode编码转化为“可变长编码”UTF-8编码，UTF-8编码将Unicode字符按数字大小编码为1-6个字节，英文字母被编码成一个字节，常用汉字被编码成三个字节，如果你编译的文本是纯英文的，那么用UTF-8就会非常节省空间，并且ASCII码也是UTF-8的一部分。

三者之间的联系

搞清楚了ASCII、Unicode和UTF-8的关系，我们就可以总结一下现在计算机系统通用的字符编码工作方式：

(1) 在计算机内存中，统一使用Unicode编码，当需要保存到硬盘或者需要传输的时候，就转换为UTF-8编码

(2)用记事本编辑的时候，从文件读取的UTF-8字符被转换为Unicode字符到内存里，编辑完成后，保存的时候再把Unicode转换为UTF-8保存到文件。如下图（截取他人图片）

浏览网页的时候，服务器会把动态生成的Unicode内容转换为UTF-8再传输到浏览器：

19、char和varchar的区别？

char是固定长度的字符串类型，varchar是可变长度的字符串类型。

拿char(128)和varchar(128)举例来说。char(128)是无论字符串大小，都会在磁盘上分配128个字符的内存空间。而varchar(128)会根据字符本身的长短来分配内存空间。

补充在MySQL中，CHAR和VARCHAR都是用于存储字符类型数据的数据类型，它们的区别在于存储方式和使用场景。

CHAR类型用于存储固定长度的字符串，其长度在定义表时就已经固定，且最大长度为255个字符。当存储的字符串长度小于定义的长度时，MySQL会在其后面补充空格使其长度达到定义的长度。由于存储的长度是固定的，因此CHAR类型的读取速度比VARCHAR类型更快。

VARCHAR类型则用于存储可变长度的字符串，其长度可以在存储数据时动态地改变，但最大长度也为255个字符。当存储的字符串长度小于定义的长度时，MySQL不会在其后面补充空格。由于存储的长度是可变的，因此VARCHAR类型的存储空间相对更小，但读取速度比CHAR类型稍微慢一些。

那与varchar相比，char字段是不是一无是处呢？

大部分情况，是的，最好使用varchar。不过考虑一个极端的场景：某个字段的最大长度是100字节，但是会频繁修改。如果使用char(100)，则插入记录后就分配了100个字节，后续修改不会造成页分裂、页空隙等问题，而varchar(100)由于没有提前分配存储空间，后续修改时可能出现页分裂，进而导致性能下降。

20、说一下你理解的外键约束？

举例来说，某一个字段是表b的主键，但是它也是表a中的字段，表a中该字段的使用范围取决于表b。外键约束主要是用来维护两个表的一致性。

补充外键约束的作用是维护表与表之间的关系，确保数据的完整性和一致性。让我们举一个简单的例子：

假设你有两个表，一个是学生表，另一个是课程表，这两个表之间有一个关系，即一个学生可以选修多门课程，而一门课程也可以被多个学生选修。在这种情况下，我们可以在学生表中定义一个指向课程表的外键，如下所示：

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

这里，students表中的course_id字段是一个外键，它指向courses表中的id字段。这个外键约束确保了每个学生所选的课程在courses表中都存在，从而维护了数据的完整性和一致性。

如果没有定义外键约束，那么就有可能出现学生选了不存在的课程或者删除了一个课程而忘记从学生表中删除选修该课程的学生的情况，这会破坏数据的完整性和一致性。因此，使用外键约束可以帮助我们避免这些问题。

21、说一下你理解的分库分表？

根据业务字段的hash值来确定分片的，比如user_id不同的用户信息就会存储到不同分片当中，他是多个分片同时提供服务。

补充当数据量过大造成事务执行缓慢时，就要考虑分表，因为减少每次查询数据总量是解决数据查询缓慢的主要原因。你可能会问：“查询可以通过主从分离或缓存来解决，为什么还要分表？”但这里的查询是指事务中的查询和更新操作。

为了应对高并发，一个数据库实例撑不住，即单库的性能无法满足高并发的要求，就把并发请求分散到多个实例中去，这种就是分库。

总的来说，分库分表使用的场景不一样：分表是因为数据量比较大，导致事务执行缓慢；分库是因为单库的性能无法满足要求。

22、数据库delete和trancate区别

delete和truncate都是用来删除数据或表的命令，但是它们之间有一些区别。

delete属于数据库DML操作语言，只删除数据不删除表的结构，会走事务，执行时会触发trigger;
在InnoDB中，DELETE其实并不会真的把数据删除，mysql实际上只是给删除的数据打了个标记为已删除，因此delete删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间(重用→覆盖);
DELETE执行时，会先将所删除数据缓存到rollback segement中，事务commit之后生效；
delete from table_name删除表的全部数据，对于MyISAM会立刻释放磁盘空间，InnoDB不会释放磁盘空间； truncate用于删除表中的所有数据，但是保留表的结构。使用truncate命令后，表的结构和数据都会被删除，无法恢复。