questions

mysql货币使用什么类型

Numeric和Decimal

explain使用查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

explain的字段有哪些【主要看 type key rows extra】

id 查询序号
select_type 查询类型
table 表名
partitions 匹配的分区

type(主要) 查询使用了什么类型，是index还是全表扫描，同时用了index还会在extra里显示using index

1	system --> const --> eq_ref --> ref --> fulltext --> ref_or_null --> index_merge --> unique_subquery --> index_subquery --> range --> index --> ALL

prossible_keys 可能会选择的索引
key（主要） 实际选择的索引
key_len 索引的长度
ref 与索引作比较的列
rows（主要） 要检索的行数(估算值)
filtered 查询条件过滤的行数的百分比
Extra（主要） 额外信息，dictinct，using index，using filesort，using temporary，最好是using index，filesort和tmp都可能导致性能下降

select type有哪些

SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	最外层的SELECT
UNION	UNION中第二个或之后的SELECT语句
DEPENDENT UNION	UNION中第二个或之后的SELECT语句取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT, 取决于外面的查询
DERIVED	衍生表(FROM子句中的子查询)
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

2. 慢查询怎么排查

看慢查询日志，cat slow_query.log
找到的话用explain，查看他的type，是否using index，最差可能时all（扫全表），keys用了哪些索引，rows的估计值

其他数据库

mongodb(https://www.chaojimake.cn/question_8_88.html)
mongodb索引使用了B树，https://mp.weixin.qq.com/s/mMWdpbYRiT6LQcdaj4hgXQ
hbase(https://www.chaojimake.cn/question_8_349.html)

为什么要分库

如果业务量剧增，数据库可能会出现性能瓶颈，这时候我们就需要考虑拆分数据库。

磁盘存储
业务量剧增，MySQL单机磁盘容量会撑爆，拆成多个数据库，磁盘使用率大大降低。
并发连接支撑
知道数据库连接数是有限的（150个？）。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！高并发场景下，会出现too many connections报错。

当前非常火的微服务架构出现，就是为了应对高并发。它把订单、用户、商品等不同模块，拆分成多个应用，并且把单个数据库也拆分成多个不同功能模块的数据库（订单库、用户库、商品库），以分担读写压力。

为什么要分表

假如你的单表数据量非常大，存储和查询的性能就会遇到瓶颈了，如果你做了很多优化之后还是无法提升效率的时候，就需要考虑做分表了。一般千万级别数据量，就需要分表。
这是因为即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢啦

考虑分库分表的时机

一般数据量千万级别，B+树索引高度就会到3层以上了，查询的时候会多查磁盘的次数，SQL就会变慢。

阿里巴巴的《Java开发手册》提出：
单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表
那我们是不是等到数据量到达五百万，才开始分库分表呢？
不是这样的，我们应该提前规划分库分表，如果估算3年后，你的表都不会到达这个五百万，则不需要分库分表。
MySQL服务器如果配置更好，是不是可以超过这个500万这个量级，才考虑分库分表？
虽然配置更好，可能数据量大之后，性能还是不错，但是如果持续发展的话，还是要考虑分库分表
一般什么类型业务表需要才分库分表？
通用是一些流水表、用户表等才考虑分库分表，如果是一些配置类的表，则完全不用考虑，因为不太可能到达这个量级。

如何分库分表？原则有哪些

数据库垂直切分

数据库水平切分

一定规则

分库分表的取模算法

分库分表的范围限定算法

分库后，事务问题如何解决

分表后，跨节点的join和union问题

分库分表后，orderby groupby等聚合函数如何处理

分库分表后，分页的处理方案

如何生成全局唯一的分布式ID

主流分库分表中间件

分表要停服吗，不停服怎么做

为了避免数据热点问题如何选择分表策略

阐述常用的数据库中间件

基础问题

1. 数据类型

int：tinyint(1byte) smallint(2byte) mediumint(3byte) int(4byte) bigint(8byte)
float,double
decimal

字符串类型：varchar,char,text,blob

varchar(n):n代表字符个数，不是字节个数
char与varchar的区别
- char定长，var不定长，存储效率较高
- varchar在开头两个字节存长度
text 和blob会使用临时表，开销损失大

【Tips】经常变更的数据使用char，char不容易产生碎片
时间类型

date
timestamp（优先使用，空间开销小）

3. 数据库三大范式，范式和反范式是什么【】

第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性。第一范式要求数据库中的表都是二维表。
第二范式（2NF）：在第一范式的基础上，一个表必须有一个主键，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式（3NF）：在第二范式的基础上，非主键列只依赖（直接依赖）于主键，不依赖于其他非主键。
范式：范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。
但是查询时通常需要多表关联查询，更难进行索引优化
反范式：反范式的过程就是通过冗余数据来提高查询性能，可以减少表关联和更好进行索引优化
存在大量冗余数据，并且数据的维护成本更高

索引问题

1.索引的分类

2.索引优缺点

3.索引设计原则

唯一性
经常与其他表链接的表，在链接字段应创建索引 on 两边的字段，都要建立索引
经常出现在where子句中的字段，尤其是大表，应创建索引
索引应创建在选择性高，重复度低的字段上，如员工表，姓名和性别都作为查询条件，姓名更适合建立索引。如果两个同时建立了索引，MySQL也会自动选择以姓名作为索引查询
索引应该建立在小字段上，对于大的文本甚至超长字段，尽量不建立索引
复合索引
① 正确选择复合索引中的主列字段，一般是选择性较好的字段
② 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引
③ 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引
④ 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引

选哪些列建立索引的原则是什么

字段不为null，唯一性，不频繁更新的，需要经常作为where orderby join的条件的字段做索引
不应该给频繁更新的字段维护索引
尽量考虑建立联合索引，并避免冗余
字符串的索引用前缀表示
单张表索引不超过 5 个
4.索引的b+树
5.hash索引和b+索引的区别
6.为什么b+树打败了二叉查找树和b树
7.最左匹配原则
8.覆盖索引
9.索引下推
存储引擎
InnoDB 适用于？
MyISAM 适用以插入为主的程序，比如博客系统、新闻门户

1.存储引擎InnoDB的四大特性

插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

2.MyISAM和InnoDB的区别

InnoDB 支持事务，而 MyISAM 不支持。
InnoDB 支持外键，而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表转为 MyISAM 表会失败。
InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。
InnoDB 不保存表中数据行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量记录了整个表的行数，速度相当快（注意不能有 WHERE 子句）。

那为什么 InnoDB 没有使用这样的变量呢 因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的。

InnoDB 支持表、行（默认）级锁，而 MyISAM 支持表级锁。
InnoDB 的行锁是基于索引实现的，而不是物理行记录上。即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。
InnoDB 必须有唯一索引（如主键），如果没有指定，就会自动寻找或生产一个隐藏列 Row_id 来充当默认主键，而 Myisam 可以没有主键。

3.为何推荐使用自增主键
自增 ID 可以保证每次插入时 B+ 树索引是从右边扩展的，因此相比自定义 ID （如 UUID）可以避免 B+ 树的频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

存储结构
1.页Page，区Extend，段Segment

页（数据页）：InnoDB中每个页16KB，Myisam是1KB

一行数据大于16kb，会发生什么，怎么解决【行溢出问题】

内置的解决方式：将溢出的数据存到新页里，然后原始页里存放一个指针，需要额外一次IO去读取

聚簇索引情况：

如果表使用了聚簇索引（InnoDB的主键索引就是一个聚簇索引），那么数据行实际上是存储在聚簇索引的叶子节点中的。如果一行数据超出了页的大小，那么会发生行溢出，溢出部分会存储在一个溢出页中，而叶子节点中仍然包含原始行的数据和指向溢出页的指针。

非聚簇索引情况：

如果表使用了非聚簇索引（如普通索引），那么数据行是单独存储在聚簇索引的叶子节点之外的，这种情况下，如果一行数据超出了页的大小，会直接发生行溢出，溢出部分会存储在溢出页中，同时非聚簇索引中也会保留指向溢出页的指针。

区Extent：一个区默认是 64 个连续的页组成的，也就是 1MB

段Segment：一段相邻的区的集合，逻辑上的组织，存放b+树

2.页由哪些数据组成

file header:用于描述数据页的外部信息，比如属于哪一个表空间、前后页的页号等
page header:用来描述数据页中的具体信息，比如存在多少条纪录，第一条纪录的位置等。
infimum 和 supremum 纪录:是系统生成的纪录，分别为最小和最大纪录值，infimum 的下一条是用户纪录中键值最小的纪录，supremum 的上一条是用户纪录中键值最大的纪录，通过 next_record 字段来相连。
user records:据库表中对应的数据(Compact行格式)
free space 可插入的空闲区域
page dictionary：类似于字典的目录结构，根据主键大小，每隔 4-8 个纪录设置一个槽，用来纪录其位置，当根据主键查找数据时，首先一步到位找到数据所在的槽，然后在槽中线性搜素。这种方法比从前到后遍历页的链表的效率更快。
File Header：存储刷盘前内存的校验和，Page Tailer储存刷盘后的校验和。当刷盘的时候，出现异常，Page Tailer和File Header中的校验和不一致，则说明出现刷盘错误。

3.页插入记录的过程

1）如果 Free Space 的空间足够的话，直接分配空间来添加纪录，并将插入前最后一条纪录的 next_record 指向当前插入的纪录，将当前插入纪录的 next_record 指向 supremum 纪录。

2）如果 Free Space的空间不够的话，则首先将之前删除造成的碎片重新整理之后，按照上述步骤插入纪录。

3）如果当前页空间整理碎片之后仍然不足的话，则重新申请一个页，将页初始化之后，按照上述步骤插入纪录

4.bufferPool

Buffer Pool 是 InnoDB 存储引擎层的缓冲池，不属于 MySQL 的 Server 层，注意跟 8.0 删掉的“查询缓存”功能区分。

内存中以页（page）为单位缓存磁盘数据，减少磁盘IO，提升访问速度。缓冲池大小默认 128M，独立的 MySQL 服务器推荐设置缓冲池大小为总内存的 80%。主要存储数据页、索引页更新缓冲（change buffer）等。

5.change buffer

如果每次写操作，数据库都直接更新磁盘中的数据，会很占磁盘IO。为了减少磁盘IO，InnoDB在Buffer Pool中开辟了一块内存，用来存储变更记录，为了防止异常宕机丢失缓存，当事务提交时会将变更记录持久化到磁盘（redo log），等待时机更新磁盘的数据文件（刷脏），用来缓存写操作的内存，就是Change Buffer

Change Buffer默认占Buffer Pool的25%，最大设置占用50%。

InnoDB

1.架构设计【内存、线程】

内存数据区域划分
四大线程
- Master thread
- IO thread
- Purge thread
- Page Cleaner Thread

1）负责刷新内存池中的数据，保证缓冲池的内存缓冲的是最近的数据

2）已修改的数据文件刷新到磁盘文件

3）保证数据库发生异常的情况下InnoDB能恢复到正常状态

2.InnoDB有哪些线程

Master Thread

负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲（INSERT BUFFER），UNDO页的回收等。

IO Thread

负责 AIO 请求的回调处理。

Purge Thread

事务提交后，undo log 可能不再需要，由 Purge Thread 负责回收并重新分配的这些已经使用的 undo 页。

Page Cleaner Thread

将Master Threader中刷新脏页的工作移至该线程，如上面说的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint。

3.double writer是什么

4. 自适应hash是什么

InnoDB 会监控对表上各索引页的查询执行情况，如发现建立哈希索引可以提升速度，则建立哈希索引，这是过程不需要用户干预。（默认开启）

事务

1.什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行

2.什么是事务的四大特性ACID

3.事务的并发问题【带来脏读、不可重复度、幻读问题】

5.事务的隔离级别

串行化
可重复度（Innodb默认）
读已提交
读未提交

6.ACID的特性如何实现

原子性是 undo 日志
持久性是 redo 日志

锁

1.数据库锁的特性，有哪些锁

行锁，表锁，页锁，
共享锁=读锁S，排他锁=写锁X，更新锁U锁
乐观锁，悲观锁
2.隔离级别和锁的关系
InnoDB 的行锁是基于索引实现的，而不是物理行记录上。即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。
3.InnoDB的锁算法
4.快照读和当前读
5.innodb的可重复度如何实现
通过MVCC实现，为每个事务维护一个独立版本视图，执行期间保持一致性。
6.MVCC以及实现（乐观锁）
MVCC 的基本思想是为每个事务创建一个独立的版本视图，以便在事务执行期间保持数据的一致性。它通过在修改数据时不覆盖原有的数据，而是为每个事务创建一个新的版本来实现。

每行数据保存一个版本号/时间戳
当一个事务对某行数据进行修改时，不会直接修改原始数据，而是会在数据库中创建一个新的版本，并将原始版本的数据保留下来
查询时，根据版本号
7. mysql优化手段有哪些
给常用字段索引，使用覆盖索引的时候，最左原则，避免全表扫描
避免not in ，范围查询使用between
分表分区，拆分子表，规模大的时候拆一下insert和update等
使用覆盖索引：索引覆盖是指查询的列都包含在索引中，而无需再去访问表本身，可以减少IO和提高查询性能。不适合频繁变更的表和列
redis缓存分担压力
连接池等

集群

1.mysql的常见日志

binlog 记录mysql的写入信息和操作，追加的方式写入，用于主从复制和数据恢复。

主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
数据恢复：通过使用mysqlbinlog工具来恢复数据。
刷盘时机：sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：
    0：不去强制要求，由系统自行判断何时写入磁盘；
    1：每次commit的时候都要将binlog写入磁盘；
    N：每N个事务，才会将binlog写入磁盘。

有三种格式，分别为STATMENT、ROW和MIXED

undolog实现回滚，保证原子性的，实现的MVCC多版本并发控制。是在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLog）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
1
2
3
4
刷盘时机有三种：每秒刷盘，每次事务提交的时候刷盘
延迟写，每秒刷盘
实时写，实时刷每次事务提交刷盘
实时写，延迟刷每秒刷盘
redolog用于持久化新数据的备份，用于恢复数据库用的。
引入redolog的原因是undolog缺点：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。
如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即Redo Log。

2.主从复制的方法， 4.主从复制的原理，5.主从复制的异步复制和半同步
1 同步复制:
所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。这样，显然不可取，也不是MySQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

2 异步复制:结构一般是链式或者树形的结构去发binlog进行复制，至于slaves是否收到二进制日志，是否完成操作，不用关心,MySQL的默认设置。

3 半同步复制:
master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MySQL引入的

存在一个主从延迟的问题，产生的原因是需要复制的节点IO线程跟不上binlog的内容，解决方法一般是多个worker并行复制去做relaylog，

【重点】项目是怎么知道哪个mysql节点有哪些表的，如何控制请求向哪个节点查询

mysql主节点提供一个系统表，jdbc直接连的话，可以通过查询主节点的系统表获取集群的表信息
微服务常用一些组件来做服务发现，进行请求的分发和负载均衡，比如可以接入consul管理mysql集群信息，etcd也可以维护mysql节点的信息。应用程序可以查询服务注册中心，获取数据库节点的地址和表信息。这样，可以动态地发现数据库节点，并知道每个节点上有哪些表
3.主从复制的架构【并行复制】
https://developer.aliyun.com/article/990898#:~:text=常用的主从同步延迟解决方案：%20🥖强制读主库%20🥖延迟读,🥖降低并发%20🥖并行复制%20%28推荐%29
主库将数据库中数据的变化写入到 binlog
从库创建一个 I/O 线程向主库请求更新的 binlog
主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
从库读取 relay log 同步数据本地

现在常用一种并行复制的方式去做，是在从节点用多worker去relaylog，解决主从延迟的问题

6.主从的常见问题和解决方式

主从延迟问题，不一致性问题
https://zhuanlan.zhihu.com/p/642614348

语法

1.Where和Having的区别

WHERE 用于过滤行，出现在 FROM 子句之后。
HAVING 用于过滤分组后的结果，出现在 GROUP BY 子句之后。

在没有分组的情况下，WHERE 和 HAVING 的作用相似，但在存在 GROUP BY 子句时，HAVING 是唯一能够过滤聚合结果的地方。

2.In 和 Exists的区别

IN 用于匹配值是否在给定的值列表中，而 EXISTS 用于检查是否存在满足条件的子查询结果。
IN 通常用于对列的直接比较，而 EXISTS 通常用于与子查询结合，检查子查询是否有结果。
相对来说经常用exist去子查询，优化器执行是会使用索引去匹配，而in会扫描全表
3. Union和Union ALL的区别
Union自己去重，union all会包含重复的列，一般是unionall之后在业务里去重

4. Drop Delete Truncate的区别

SQL优化

1. 如何判断是否走了索引

2. 索引失效的几种情况

3. where子句如何优化

4. 超大分页和深度分页

5. 大表查询如何优化

使用覆盖索引：索引覆盖是指查询的列都包含在索引中，而无需再去访问表本身，可以减少IO和提高查询性能。不适合频繁变更的表和列
按照列进行垂直分割，将冷热数据分开存储，以降低查询的数据量
对大表进行分区，将数据划分为更小的物理单元，以减少查询时需要扫描的数据量。
根据查询的条件，选择合适的分区键。

MySQL questions