Java面试 数据库 MySql
数据库根本知识数据库：由数据库管理系统管理的数据的集合。
数据库管理系统 : 是一种使用和管理数据库的大型软件，通常用于创建、使用和维护数据库。
数据库系统通常由软件、数据库和数据管理员(DBA)构成。
数据库管理员 : 数据库管理员负责全面管理和控制数据库系统。
元组（行）、码（唯一标识实体的属性，对应表中的列）、候选码（唯一的标识一个元组）、
主码 : 主码也叫主键。主码是从候选码中选出来的。 一个实体会合只能有一个主码。
外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。可重复、可空，可以有多个外键。复杂性增长（delete和upgrade都要处理对应的外键数据）、增长了数据库额外工作、分库分表不友爱（库相互独立），但包管了数据库数据的一致性和完整性；级联使用方便，减轻了程序代码量。
接洽：即实体与实体之间的关系‘
三范式
1NF(第一范式)：属性不可再分。2NF(第二范式)：非主属性对于码完全依赖。3NF(第三范式)：消除了非主属性对于码的传递函数依赖 。
存储过程：SQL语句的集合，预编译过，执行快
drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。破坏表结构。最快。
truncate (清空数据) : truncate table 表名 ，删除表中所有数据，自增重置
delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。可触发delete触发器。逐行删除，最慢，可回滚（DML）(日志记载)。
数据库计划：
需求分析 : 分析用户的需求，包括数据、功能和性能需求。
概念结构计划 : 主要接纳 E-R 模型进行计划，包括画 E-R 图。
逻辑结构计划 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
物理结构计划 : 主要是为所计划的数据库选择合适的存储结构和存取路径。
数据库实施 : 包括编程、测试和试运行
数据库的运行和维护 : 系统的运行与数据库的日常维护
NoSql
范例：键值、图、文件、宽列。机动性、可扩展性、高性能。
字符集
关系型数据库，数据和数据间的关系；sql,结构化查询语言
Mysql长处
成熟稳固，开源免费、文档丰富、开箱即用、兼容性好、社区活泼、事务支持优秀；
字段范例
https://i-blog.csdnimg.cn/direct/de371d75fa274201b3efac10a58c84c3.png
特殊地，
1、MySQL 中的整数范例可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限进步一倍，因为它不必要存储负数值，如普通的 TINYINT 范例的值范围是 -128 ~ 127，TINYINT UNSIGNED 范例的取值范围是 0 ~ 255
2、CHAR 是定长字符串，VARCHAR 是变长字符串。CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时必要使用 1 或 2 个额外字节记载字符串的长度，检索时不必要处理。CHAR 更得当存储长度较短或者长度都差不多的字符串，如MD5 算法加密后的密码、身份证号码。VARCHAR 范例得当存储长度不确定或者差别较大的字符串，例如用户昵称、文章标题等。
3、VARCHAR(100)和 VARCHAR(10)的区别 ；范围更大，相同字符串存储占用磁盘存储空间相同，消耗内存更多，分配固定大小的内存块来保存值。
4、DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储准确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。
DECIMAL 用于存储具有精度要求的小数，例如与钱币相干的数据，可以避免浮点数带来的精度损失。（对应的是 Java 类 java.math.BigDecimal）
5、TEXT 范例，但偶尔会用到，而 BLOB 范例则基本不常用。缺点：检索效率低、无法使用内存（只能是磁盘）临时表、不能又默认值、创建索引必要指定长度、消耗大量网络带宽
6、datetime 范例没偶然区信息，timestamp偶然区，根据用户所在转化，数值时间戳就是从1970-01-01 00:00:00 UTC 到当前时间所经过的秒数，便于跨系统、时间差、高效存储的长处。
7、null和“”的区别：
NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的效果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被以为是相等的。
‘’的长度是 0，是不占用空间的，而NULL 是必要占用空间的。NULL 会影响聚合函数的效果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。COUNT(*)则会统计所有的记载数，包括 NULL 值；COUNT(列名)则会忽略 NULL 值，只统计非空值的个数。查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。
8、MySQL 中没有专门的布尔范例，而是用 TINYINT(1) 范例来表示布尔值。TINYINT(1) 范例可以存储 0 或 1，分别对应 false 或 true
MySql根本架构
毗连器，命中缓存，分析器、优化器、执行器、存储引擎（InnoDB)
MySql存储引擎
插件式架构，基于表，适应差别场景来选择合适的存储引擎
MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，之后，InnoDB 是 MySQL 的默认存储引擎。区别：行级锁/表级锁；事务支持（提交回滚、可重读），是否支持外键，数据库奔溃后是否能够回复（redo log）；MVCC支持；索引实现、、性能差别、
MySQL索引
B+树、长处是加速检索（IO减少）,行创建唯一性索引；缺点：增删改也必要对索引修改、索引使用物理存储占用空间，数据量不大不能加速。
索引范例：BTree 索引（叶子存值）、哈希索引、全文索引、RTree索引
底层存储分类：聚集索引【速度快】和非聚集索引（辅助索引，二次查询【回表】）
应用维度：主键、普通、唯一、覆盖【索引包含查询的字段的值】、联合、前缀；
非聚簇索引不一定回表查询（覆盖索引）。联合索引根据索引中的字段顺序，从左到右依次匹配查询条件中的字段【不能越过次序】。最左匹配原则，直到碰到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。
mysql 5.6 后的优化 索引下推：将（Server 层）负责的事变，交给了下层（存储引擎层）行止理。
如SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;
无：找到所有 zipcode = '431200' 的用户的主键 ID->二次回表查询，获取完整的用户数据->数据交给 Server 层->Server 层根据 MONTH(birthdate) = 3 这一条件再做筛选
有：存储引擎层根据 zipcode 索引字段找到所有 zipcode = '431200' 的用户，然后直接判断 MONTH(birthdate) = 3，筛选出符合条件的主键 ID；二次回表查询，存储引擎层把符合条件的用户数据全部交给 Server 层。
注意：对于 InnoDB 表，仅用于非聚簇索引。索引下推的目标是减少全行读取次数，从而减少 I/O 使用。对于 InnoDB 聚集索引，完整的记载已经读入 InnoDB 缓冲区。在这种情况下使用索引下推不会减少 I/O
索引使用：合适字段（排序、非null、条件、毗连、频繁查询）、频繁更新（X）、限制索引数量、联合索引而不是多个索引、避免冗余索引、字符串使用普通索引、索引失效：Like% select * or 使用函数 in / not in 使用不当【效果集 大于 30%的时候索引失效】、删除不用索引
EXPLAIN 命令来分析 SQL 的 执行操持， 查询优化器 对语句进行分析，找出最优的查询方案，并表现对应的信息。
列名含义 type key extra
参考毗连https://csdnimg.cn/release/blog_editor_html/release2.3.8/ckeditor/plugins/CsdnLink/icons/icon-default.png?t=P1C7https://javaguide.cn/database/mysql/mysql-query-execution-plan.html#type-%E9%87%8D%E8%A6%81
https://i-blog.csdnimg.cn/direct/1ba2487ae7fb4145968774adb5a3fbf5.png
MySQL查询缓存
失效：查询字符上差别【基于 SQL 语句的文本匹配，任何字符的差别（如空格、大小写、换行等】；查询包含用户自定义函数（UDF）、存储函数、用户变量（如 @var）、临时表，系统库表缓存创建之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相干的所有缓存数据都将失效；
sql_cache 和 sql_no_cache 来控制某个查询语句是否必要缓存
缓存固然能够提拔数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存使用，失效后还要烧毁；MySQL 5.6 开始，查询缓存已默认禁用。MySQL 8.0 开始，已经不再支持查询缓存了
MySQL日志
redo log
规复数据，包管数据的长期性与完整性； InnoDB 存储引擎独有，瓦解规复本领；
MySQL 中数据是以页为单位，查询一条记载，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中，后续的查询/更新都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提拔性能。然后把“在某个数据页上做了什么修改”记载到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。
区分Buffer Pool和redo log buffer
https://i-blog.csdnimg.cn/direct/6efd5b8f8bb04524b261eb1c6c8b4ff5.png
为什么不每次把修改后的数据页直接刷盘而是使用 redo log
redo log是追加使用， 以是磁盘使用是顺序写，而写入数据必要先找到写入位置，然后才写到磁盘，以是磁盘使用是随机写。数据页更大，每次只修改一点数据就刷盘，相比redo只有一条记载（表空间号、数据页号、磁盘文件偏移量、更新值）
redo log 是直接写入磁盘的吗？
https://i-blog.csdnimg.cn/direct/f70a8284495d42bb8b4b3b6f55b21052.png
刷盘机会：MySQL 正常关闭；配景线程每秒1次的轮询使用；当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，配景线程会主动刷盘。事务提交时（innodb_flush_log_at_trx_commit 参数控制）
https://i-blog.csdnimg.cn/direct/9e9620401584401da27108e0d8af7868.png
区分瓦解是否会丢失数据：都会丢失1秒；已提交不会；宕机会（使用系统缓存）
数据安全性和性能成反比
重做日志文件组
2 个(默认) redo log 文件构成 循环写 write pos / checkpoint
redo log 是为了防止 Buffer Pool 中的脏页丢失而计划的，那么如果随着系统运行，Bufer Poo的脏页革新到了磁盘中，那么 redo log 对应的记载也就没用了，这时候我们擦除这些旧记载，以腾出空间记载新的更新使用。
MySQL 8.0.30 之前可以通过 innodb_log_files_in_group 和 innodb_log_file_size 配置日志文件组的文件数和文件大小；后废弃，文件数则固定为 32，文件大小则为 innodb_redo_log_capacity / 32
https://i-blog.csdnimg.cn/direct/76f22188c6d54a7e95f09f2c23c0738e.png
Undo log
undo log 属于逻辑日志，记载的是 SQL 语句，好比说事务执行一条 DELETE 语句，那 undo log 就会记载一条相对应的 INSERT 语句（删除--插入 更新----更新旧值）；
特殊地delete有delete flag删除位，update是主键只能是删除后重新插入；UPDATE/DELETE 使用在事务提交不会立即删除，会参加 history list，由配景线程 purge 进行清算
undo log 的信息也会被记载到 redo log ，类似普通数据页的刷盘使用
一条记载的每一次更新使用产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id;
通过 trx id 可以知道该记载是被哪个事务修改的;
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链。
binglog
binlog 会记载所有涉及更新数据的逻辑使用，而且是顺序写;全量写，不删除
是server实现的，任何存储引擎可以用；binlog_format参数指定参数，
statement（语句，不实时）、row(具体数据，更大的容量，规复与同步时会更消耗 IO 资源，影响执行速度)，MIXED具体情况具体选择上面的形式
https://i-blog.csdnimg.cn/direct/65e45c5065044f06a454a5d16af729c6.pnghttps://i-blog.csdnimg.cn/direct/ee88616ce4ef4276a33f409a62bdd60b.png
写入机制：
执行时候写入binlog cache,事物提交再写到page cash，由系统决定（sync_binlog参数）决定写入磁盘的机会
一个事务的 binlog 不能被拆开,线程分配一个块内存作为binlog cache;binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘；
https://i-blog.csdnimg.cn/direct/8241ae1edfdd4936898d08dd94ed000d.png
两阶段提交：
事物没提交时候，会一直处于redo log长期化的过程，事物提交的时候，就会执行binglog的长期化过程，即 redolog -> 提交 ->binglog ；以是必要redolog 俩阶段prepare和commit阶段
异常重启：redo log为preapare大概有 有无对应 binlog 日志 俩种状态 有直接提交，没有回滚事务即两阶段提交是以 binlog 写乐成为事务提交乐成的标识。
因为binlog 已经写入了，之后就会被从库(或者用这个 binlog 规复出来的库)使用。
以是，在主库上也要提交这个事务。接纳这个计谋，主库和备库的数据就包管了一致性。
问题：锁竞争激烈、磁盘i/o次数高
办理：组提交（binlog和5.7之后的redo log）
flush 阶段:多个事务按进入的顺序将 binlog 从 cache 写入文件(不刷盘)；会合redo写入+刷盘；第一个事务会成为 flush 阶段的 Leader，此时后面到来的事务都是 Follower
sync 阶段:对 binlog 文件做 fsync 使用(多个事务的 binlog 合并一次刷盘);
commit 阶段:各个事务按顺序做InnoDB commit 使用;
MySQL事务
事务是逻辑上的一组使用，要么都执行，要么都不执行；
acid:只有包管了事务的长期性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是本领，C 是目的
并发问题：
脏读【未提交即读取】
https://i-blog.csdnimg.cn/direct/3f4e1b642c084273bd1be20892d215c3.png
丢失修改
https://i-blog.csdnimg.cn/direct/87b0706c75364b73b22dfd1737fb3c6c.png
不可重复读
https://i-blog.csdnimg.cn/direct/b60919eadf4543bd9acfaed843744fad.png
幻读（范围查询增长）【记载锁不能锁插入的新语句】
https://i-blog.csdnimg.cn/direct/05e0fc88dc624ba3ba117c447572bfc6.png
并发控制方式：
锁：共享锁（锁兼容、读并行）和排他锁（写锁）InnoDB 行级锁和表级锁
MVCC：
MVCC 是一种并发控制机制；快照读+版本写+实时接纳+提交可见新版本
一致性非锁定读和锁定读（快照读和当前读）
实现：隐藏字段、Read View、undo log
每行数据添添加：
DB_TRX_ID（6字节）：表示末了一次插入或更新该行的事务 id。
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log [旧版本指针]
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引
Read View 有四个重要的字段:
m_ids:活泼事务的事务 id 列表【启动了但还没提交的事务】，一个列表.
min_trx_id : id 最小的事务，也就是m_ids 的最小值。
max_trx_id :创建 Read Vew 时当前数据库中应该给下一个事务的id 值，也就是全局事务中最大的事务 id 值 +1;
creator_trx_id :指的是创建该 Read View 的事务的事务 id。
一个事务去访问记载，小与min_trx_id可见，大于max_trx_id不可见，之中不在m_ids可见，之中在m_ids事务仍活泼不可见；
通过版本链来控制并发事务中访问同一条记载的行为就是mvcc
可重复级别的隔离是每个事物启动一个readview,整个事物都使用；读提交是每次读的时候都会生成一个新的read view【以是有大概在读的中心发生了提交】
MySql可重复级别办理了幻读吗？
1、普通 select，此时会以 MVCC 快照读的方式读取数据
2、当前读（select ... for update 等）通过 next-key lock（记载锁+间隙锁）方式办理了幻读【范围锁定】
现实上没有完全办理，在查询某条不存在的记载之后再插入数据 后查询可以发现数据存在，即幻读
MySQl锁
全局锁【全库逻辑备份、只读】，其实可重复读的隔离级别就可以实现数据库的备份而不会导致数据库的不可更新
表级锁：
表锁（表锁会限制别的线程的读写，InnoDB里如果加了表锁，本线程不能访问未加锁的表。）
元数据锁（MDL）：对表结构的锁（CRUD 使用时，加的是 MDL 读锁；结构变更使用的时候，加的是 MDL 写锁）事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的【阻塞、并发效率性能降低】；申请 MDL 锁的使用会形成一个队列
意向锁：目的是为了快速判断表里是否有记载被加锁；意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生辩论，而且意向锁之间也不会发生辩论，但表级共享锁和表级独占锁会（只有意向共享锁、表级共享锁是兼容的）
AUTO-INC锁包管AUTO_INCREMENT 修饰的字段的值是连续递增，但会影响并发的插入性能。以是InnoDB接纳了轻量级锁来申请主键字段的自增值，不必要等候执行语句执行完的。 innodb_autoinc_lock_mode参数决定，0---AUTO-INC；2-----轻量级锁；1--------insert … select 这样的批量插入数据的语句用AUTO-INC，正常insert语句使用轻量级锁；当参数=2而且binglog是statement时候出现主从复制不一致【插入顺序固定，现实会交叉】办理是binglog改为row;
行级锁：
select不加锁（快照读），也有锁定读的方式[但要在同一个事务内]
select ... lock in share mode;//对读取的记录加共享锁
select ... for update;//对读取的记录加独占锁 Record Lock，记载锁；Gap Lock，间隙锁，锁定一个范围，但是不包含记载本身；next-key lock 是包含间隙锁+记载锁；插入意向锁，间隙锁，优先级比间隙锁低会被阻塞，插入时确保同个位置不会被并行插入和办理幻读问题。
MySql是怎么加锁的？
唯一索引等值查询 （加锁的对象是针对索引）
next-key lock ----- 查到该记载 -------> 记载锁
next-key lock ----- 查到第一条大于记载（不存在） -------> 间隙锁、锁
唯一索引范围查询
首先无论怎样每次查看到一条合适的记载的时候会主动加上（上一条记载，合适的记载]即next-key 锁。【左闭右开】
大于等于 界限在表中，界限值记载所在会退化成记载锁；界限不在表中无特殊
小于等于 界限在表中无特殊 ；界限不在表中，界限值记载所在会退化成间隙锁【左开右开】（本质上也是第一条不符合条件的记载）
大于 没有特殊情况
小于 无论界限在不在表中，查询到第一条不符合条件的记载的锁所在会退化成间隙锁【左开右开】（界限在表中就是界限，不在就是符合的末了一条的吓一跳）
注意在非唯一索引上加next-key也会找到对应的主键索引加记载锁
非唯一索引等值查询
合适的记载加上next-key 锁。【左闭右开】第一条不合适的记载退化成间隙锁【左开右开】
（22，39）的锁是为了防住 id>10 age=22 记载插入引起的幻读问题
https://i-blog.csdnimg.cn/direct/5f505a12447a46009d344348946e5dcf.png
非唯一索引范围索引
纵然相等也不会退化因为不具有唯一性
没有索引的加锁会进行全表扫描全表间隙锁
BufferPool
内存和磁盘之间的过渡，读取数据，修改数据设置脏页写出磁盘
控制块管理缓存页；多余的碎片空间；查询记载会缓存整个页
Free链表、Flush链表（管理脏页）
进步缓存使用率（LRU）缺点：预读失效【无用的预读页占用了热点数据】（办理方案：分为old区作为预读页的和yong区，innodb_old_blocks_pct调节比例）Buffer Pool 污染 ： like等结构进行全表扫描时候，进入old区后，由于一一读取进入young区而将热点数据全部清除。（办理方法：设置时间间隔，只有第二次访问是在这个间隔之后才会进入young区，即设置门槛）
革新时间：redo log日志、Buffer pool空间不敷、MySQL空闲或关闭
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]