mysql数据库基础

一、索引数据结构

索引的本质，帮助mysql高效获取数据的排好序的数据结构

1）二叉树

2）红黑树

​ （本质二叉树，二叉平衡树）

​ 缺点：百万数据后，树的高度变的不可控，查找磁盘io次数变的不可控

3）Hash表

​ 查找性能很高，

​ 缺点：无法模糊范围查询

4）B-Tree

• 叶子节点具有相同的深度，叶节点指针为空
• 所有的索引元素不重复
• 节点中的数据索引从左到右递增排列

**缺点：**data的存储空间比较大,mysql每列默认大小16k,导致存储数据过少

5）B+Tree(mysql的数据结构)(B-Tree变种)

- 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接，提高区间访问的性能
每次一行一行的load进ram进行比对

二、mysql索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash和B+Tree。

mysql中每节点默认大小16k,一个磁盘指针6byte，一个索引8byte，每个节点存储1170个元素

mysql中每节点默认大小16k,一个磁盘指针6byte，一个索引8byte，每个节点存储1170个元素，最大数据2000w左右

三、mysql的执行过程

连接器,（缓存）题词分析器，优化器，执行器

四、存储引擎原理

不同的表有不同的存储引擎

1）innrdb

• 磁盘文件二个，
• frm : 表结构文件
• idb ： 索引+数据
• 索引叶子节点存的是所有数据
• 非主键索引（辅助索引），他的叶子节点存储的是主键id,(为什么如此设计，如果存储的是数据，那么会出现一致性问题，性能原因，空间浪费)

InnoDB索引实现(聚集索引)

•表数据文件本身就是按B+Tree组织的一个索引结构文件

•聚集索引-叶节点包含了完整的数据记录

•为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

是因为mysql的设计b+tree必须要用主键作为索引，如果没有设置主键，就会在表中找一个唯一一列作为主键，如果找不到则自动生成RowId主键（隐藏主键），主键自增（如果不自增，会为了满足B+tree的特性，则会导致元素的分裂，以及影响整个树的平衡，这样效率会很低）

•为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

2）myIsam

磁盘文件共三个，

MYD ：数据存储在MYD文件中

MYI ： 主键，索引

frm ： 表结构文件

主键索引叶子节点存储的是：索引所在行的磁盘指针

五、索引

聚集索引（同一个概念聚簇索引）：（就是索引和和表的数据列聚集在一个文件） innerdb的主键索引就是，MyISaM非聚集索引，稀疏索引就是非聚集索引

**联合索引：**能用联合索引，就用联合（反之浪费空间）

聚簇索引：
    数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引：
    数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
联合索引中最左前缀原则 与 B+ 树的存储结构有关

六、Explain工具

​ 使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

explain 两个变种

**explain extended：**会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通 过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表 进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。 mysql>> explain extended select * from film where id = 1;

1. id列  id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
2. select_type列  
     1）simple：简单查询
     2）primary：复杂查询中最外层的 select
     3）subquery：包含在 select 中的子查询（不在 from 子句中）
     4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为 派生表（derived的英文含义） 用这个例子来了解 primary、subquery 和 derived 类型
3. table列 这一列表示 explain 的一行正在访问哪个表。
4. partitions 列
	查询将匹配记录的分区。 对于非分区表，该值为 NULL。
5. type列 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
	const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多 有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为 system
	eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合 条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
	ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要 和某个值相比较，可能会找到多个符合条件的行。
	- NULL：MySQL能在优化阶段分解查询语句，在执行阶段不用再去访问表或者索引。
    - system、const：MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）。
    - system是const的一个特例，表示表里只有一条元组匹配时为system。
    - eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。
    - ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。
    - range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。
    - index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点。
    - ALL：全表扫描，扫描聚簇索引的所有叶子节点。
6. possible_keys列 
	此列显示在查询中可能用到的索引。
        如果该列为NULL，则表示没有相关索引，可以通过检查where子句看是否可以添加一个适当的索引来提高性能。
7. key 列 
	- 此列显示MySQL在查询时实际用到的索引。
    - 在执行计划中可能出现possible_keys列有值，而key列为null，这种情况可能是表中数据不多，MySQL认为索引对当前查询帮助不大而选择了全表查询。
    - 如果想强制MySQL使用或忽视possible_keys列中的索引，在查询时可使用force index、ignore index。
8. key_len 列
	此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。
    索引最大长度为768字节，当长度过大时，MySQL会做一个类似最左前缀处理，将前半部分字符提取出做索引。
    当字段可以为null时，还需要1个字节去记录。
    key_len计算规则：
            字符串：
              char(n)：n个数字或者字母占n个字节，汉字占3n个字节
              varchar(n)：  n个数字或者字母占n个字节，汉字占3n+2个字节。+2字节用来存储字符串长度。
            数字类型：
              tinyint：1字节      smallint：2字节               int：4字节             bigint：8字节
            时间类型
              date：3字节        timestamp：4字节          datetime：8字节
9. ref 列
	 此列显示key列记录的索引中，表查找值时使用到的列或常量。常见的有const、字段名
10.rows 列
	此列是MySQL在查询中估计要读取的行数。注意这里不是结果集的行数。
11.Extra 列
	1）Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。
    2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。
    3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。
    4）Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。
    5）Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。
    6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。

• 索引数据结构红黑树，Hash，B+树详解

• 索引是怎么支撑千万级表的快速查找

• 面试常问B+树索引面试题解析

• 联合索引底层数据结构又是怎样的

其他

•索引数据结构红黑树，Hash，B+树详解
•索引是怎么支撑千万级表的快速查找
•面试常问B+树索引面试题解析
•联合索引底层数据结构又是怎样的

七、buffer pool

 buffer pool 内存区域 一个数组， 最小单位为一页 默认为 128M
        free 链表：free链表是一个双向链表，是把所有空闲的缓冲页对应的控制块作为一个节点放到一个链表中，这个链表便称之为free链表。所有空闲区域引用的链表
        flush 链表：创建一个存储脏页的链表，凡是被修改过的缓冲页对应的控制块都会作为节点加入到这个链表中。该链表也被称为flush链表。
            修改了Buffer Pool中某个缓冲页的数据，那么它就与磁盘上的页不一致了，这样的缓冲页也被称之为脏页（dirty page）。
        lru 链表 的优化策略 避免全表扫描情况把 buffer pool 的热点数据全被刷出内存

八、锁

锁定分离

1.从性能上分为乐观锁，悲观锁

2.从都数据库操作类型分，读锁（共享锁），写锁（排他锁），都属于悲观锁

3.从对数据操作的粒度分，分为表锁和行锁

1).表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲 突的概率最高，并发度最低；

手动增加表锁 lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁 show open tables;

删除表锁 unlock tables;

读锁

当前session和其他session都可以读该表 当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等 待

写锁

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改 操作前,会自动给涉及的表加写锁。

总结： 简而言之，就是****读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2).行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁 冲突的概率最低，并发度最高。

InnoDB与MYISAM的最大不同有两点： 支持事务（TRANSACTION） 支持行级锁

九、事务

行锁支持事务 事务（Transaction）及其ACID属性：原子性，一致性，隔离性，持久性

**原子性：**语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log

一致性：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障（前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。）

**隔离性：**保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）

持久性：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log。

redo log 和 undo log 都属于 InnoDB 的事务日志。下面先聊一下 redo log 存在的背景。
InnoDB 作为 MySQL 的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘 IO，效率会很低。
为此，InnoDB 提供了缓存(Buffer Pool)，Buffer Pool 中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：
当从数据库读取数据时，会首先从 Buffer Pool 中读取，如果 Buffer Pool 中没有，则从磁盘读取后放入 Buffer Pool。
当向数据库写入数据时，会首先写入 Buffer Pool，Buffer Pool 中修改的数据会定期刷新到磁盘中(这一过程称为刷脏)。
Buffer Pool 的使用大大提高了读写数据的效率，但是也带来了新的问题：如果 MySQL 宕机，而此时 Buffer Pool 中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
于是，redo log 被引入来解决这个问题：当数据修改时，除了修改 Buffer Pool 中的数据，还会在 redo log 记录这次操作;当事务提交时，会调用 fsync 接口对 redo log 进行刷盘。
如果 MySQL 宕机，重启时可以读取 redo log 中的数据，对数据库进行恢复。
redo log 采用的是 WAL(Write-ahead logging，预写式日志)，所有修改先写入日志，再更新到 Buffer Pool，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求。
既然 redo log 也需要在事务提交时将日志写入磁盘，为什么它比直接将 Buffer Pool 中修改的数据写入磁盘(即刷脏)要快呢?
主要有以下两方面的原因：
刷脏是随机 IO，因为每次修改的数据位置随机，但写 redo log 是追加操作，属于顺序 IO。
刷脏是以数据页(Page)为单位的，MySQL 默认页大小是 16KB，一个 Page 上一个小修改都要整页写入;而 redo log 中只包含真正需要写入的部分，无效 IO 大大减少。

并发事务处理带来的问题

更新丢失（Lost Update）
脏读（Dirty Reads）
不可重读（Non-Repeatable Reads）
幻读（Phantom Reads）

事务隔离级别

**注：**脏读，不可重复读，幻读，都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来结局

隔离级别	脏读（Dirty Read）	不可重复读（NonRepoatable Read）	幻读（Phantom Read）
读未提交（Read uncommitted）	可能	可能	可能
读已提交（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable）	不可能	不可能	不可能

Mysql默认级别是repeatable-read，有办法解决幻读问题吗？ 间隙锁在某些情况下可以解决幻读问题

update account set name = 'zhuge' where id > 10 and id <=20;，

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失 效，否则都会从行锁升级为表锁。

十、日志

1）日志种类

binlog：
        归属于mysql server层，binlog 是逻辑日志，它记录的是操作语句涉及的每一行修改前后的值，在任何存储引擎下都可以使用。
        二进制日志，用于主从复制、崩溃恢复，默认不开启。

undolog：
    归属于innodb引擎，是Innodb MVCC的重要组成部分，主要用于记录历史版本数据，用于事务回滚。

redolog：
    归属于innodb引擎，redolog 是物理日志，它记录的是数据页修改逻辑以及 change buffer 的变更，只能在innodb引擎下使用。

redolog 是搭配缓冲池、change buffer 使用的，缓冲池的作用是缓存磁盘上的数据页，减少磁盘的IO；change buffer 的作用是将写操作先存在内存中，
等到下次需要读取这些操作涉及到的数据页时，就把数据页加载到缓冲池中，然后在缓冲池中更新；

relaylog 主从同步数据时的中继日志，用于存从主库传来的 binlog 日志文件的内容

事务的持久性是通过redolog实现的（write ahead log（WAL）），即先写日志再写数据；而因为binlog和redolog两种日志属于不同的组件，
所以为了保证数据的一致性，要保证binlog和redolog的一致，所以有了二阶段提交的概念。

2）redo log与bin log

作用不同：
redo log 是用于 crash recovery 的，保证 MySQL 宕机也不会影响持久性;
binlog 是用于 point-in-time recovery 的，保证服务器可以基于时间点恢复数据，此外 binlog 还用于主从复制。
层次不同：
redo log 是 InnoDB 存储引擎实现的，
而 binlog 是 MySQL 的服务器层(可以参考文章前面对 MySQL 逻辑架构的介绍)实现的，同时支持 InnoDB 和其他存储引擎。
内容不同：
redo log 是物理日志，内容基于磁盘的 Page。
binlog 是逻辑日志，内容是一条条 sql。
写入时机不同：
redo log 的写入时机相对多元。前面曾提到，当事务提交时会调用 fsync 对 redo log 进行刷盘;这是默认情况下的策略，修改 innodb_flush_log_at_trx_commit 参数可以改变该策略，但事务的持久性将无法保证。
除了事务提交时，还有其他刷盘时机：如 master thread 每秒刷盘一次 redo log 等，这样的好处是不一定要等到 commit 时刷盘，commit 速度大大加快。
binlog 在事务提交时写入。

3) redo log与undo log

redo log 和zz会生成一个read view的视图，在后续的操作中会基于一些机制从read view和undo日志版本链中获取对应的数据的一个机制，保证在并发情况下获取正确的数据



1.是mysql中的多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，由三个部分组成：隐藏字段、undolog、readview
2.我们有一个undo日志版本链，在一个事务中第一次查询会生成一个read view的视图，在后续的操作中会基于一些机制从read view和undo日志版本链中获取对应的数据的一个机制，保证在并发情况下获取正确的数据

更新，新增，删除才会自动添加事务id,生成事务id,就会生成read-view.

read-viem:生成的时全库，针对的时session

mysql会默认给每一张表增加一个事务id字段，回滚指针两个字段

修改：插入一条记录，插入之前的记录放到undo日志里面，并回滚指针指向他

当 执行查询sql时，会身材read-viem,包含所有未提交事务数组，和已创建的最大事务id

版本链比对规则,

​ 最大事务id表示当前事务之前的最大提交id(max_id),最小事务id表示所有未提交事务id

​ 1.trx_id

​ 2.trx_id>max_id 不可见

​ 3.min_id <= trx_id <= maxi_id

创建了查询快照，记录执 行sql这一刻最大的已提交事务id(快照点已提交最大事务id)

创建事务id <= max(当前事务id(12)，快照点已提交最大事务id），

删除事务id> max(当前事 务id(12)，快照点已提交最大事务id）

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的 性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优 于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和 MYISAM相比就会有比较明显的优势了。 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候， 可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

优化建议 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁 合理设计索引，尽量缩小锁的范围 尽可能减少检索条件范围，避免间隙锁 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql 尽量放在事务最后执行 尽可能低级别事务隔离

十二、mysql主从

主从同步原理：
    mysql主从赋值的核心就是二进制日志binlog(记录了DDL数据定义语言语句和DML数据操作语言语句)
    1.0 主库在事务提交时，会把数据变更记录在二进制日志文件 binlog 中
    2.0 从库读取主库的二进制日志文件 binlog，写入到从库的中继日志 relay log 中
    3.0 从库从中继日志中读取数据，然后写入到从库中

1.为什么要主从

1. 如果主服务器出现问题，可以快速切换到从服务
2. 从服务器提供查询，实现读写分离
3. 从服务器备份，避免主服务器受到影响

2.同步方式

1. 基于GTiD,事务(底层基于bin-log)
2. 基于bin-log

3.主从复制：延时问题？

1、优化网络环境

2、增加从库数量：增加从库数量可以增加数据同步的速度和可靠性，同时也能减少每个从库的负担，提高从库响应速度。

3、调整数据库相关参数：可以调整一些MySQL数据库中的相关参数，比如调整binlog格式、binlog缓冲区大小、innodb_flush_log_at_trx_commit等参数，采用半同步模式，以加快数据的同步速度。

4、分区数据库：将数据库分成多个区，每个从库只复制自己所需要的数据区，可以有效的减少排队堵塞、网络传输等方面的延迟问题。

综上所述，优化网络环境、增加从库数量、调整数据库相关参数、分区数据库等方法可以有效的降低MySQL主从复制模式的延迟。

4.主从复制方式

1. 同步复制（Fully Syncharonized）
2. 异步复制(Async） ，默认主从复制方式
3. 半同步复制 （如果超时没有ask，则降级为异步）

5.高可用

**MM方案，已经废弃**

MMM(Master-Master replication managerfor Mysql，Mysql主主复制管理器)是一 套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管 理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)

1.1 优点

（1）高可用性，扩展性好，出现故障自动转移，对于主主同步，在同一时间只提供一台数 据库写操作，保证数据的一致性。

（2）配置简单，容易操作。

1.2 缺点

（1）需要一台备份服务器，浪费资源

（2）需要多个虚拟IP

（3）agent可能意外终止，引起裂脑。（当网络抖动，mater1切换master2，但是抖动消失后master1恢复，就会形成）

MHA方案

MHA服务，有两种角色， MHA Manager(管理节点)和 MHA Node(数据节点)。在 MySQL故障切换过程中，MHA能做到在0\~30秒之内自动完成数据库的故障切换操作，目 前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据 库服务器。

2.1 优点

（1）不需要备份服务器

（2）不改变现有环境

（3）操作非常简单

（4）可以进行日志的差异修复

（5）可以将任意slave提升为master

2.2 缺点

（1）需要全部节点做ssh秘钥

（2）MHA出现故障后配置文件会被修改，如果再次故障转移需要重新修改配置文件。

（3）自带的脚本还需要进一步补充完善，且用perl开发，二次开发困难。

十三、分表分库维度

水平切分：比如按照时间
优点：
1. 单库单表的数据保持一定的量级，有助于性能的提高
2. 切分的表的结构相同，应用层改造较少，只需要增加路由规则即可
3. 提高了系统的稳定性和负载能力
缺点：
1. 切分后数据是分散的，很难利用数据库的关联查询，跨库查询性能较差
2. 拆分规则难以抽象
3. 分片数据的一致性难以解决
4. 数据扩容的难度和维护量极大
垂直切分：按照字段，比如把大字段单独裂成一张表
优点：
1. 拆分后业务清晰，拆分规则明确
2. 系统之间进行整合或扩展容易
3. 按照成本、应用等级、应用的类型等将表放到不同的机器上，便于管理
4. 便于实现动静分离、冷热分离的数据库表的设计模式
5. 数据维护简单
缺点：
1. 部分业务表无法进行关联、只能通过接口的方式来解决，提高了系统的复杂度
2. 受每种业务不同的限制，存在单库性能瓶颈，对数据扩展和性能提升不友好
3. 事务处理复杂

十四、mysql 调优

Mysql 慢查询：聚合查询、多表查询、表数据过大查询、深度分页查询 表象：页面加载过慢、接口压测响应时间过长（超过了1s）

1.参数调优
      调整 MYSQL 配置，如 最大连接数，线程池缓存线程数，缓存大小 等，部分数据存入redis、mongodb等其它数据库
2.硬件升级 加配置.....
3.定期清理无用数据
4.对于不同的业务场景使用不同的存储引擎
      读多写少：MyISAM
      读少写多，事务型：InnoDB
5.设计优化：
      分库分表(水平拆分、垂直拆分)、使用冗余字段(但是不宜太多)、数据类型优化(适配实际值的存储长度)、索引优化(覆盖索引、联合索引、前缀索引、索引下推)、
      索引下推：在 like '%xxx%' 时，如果前面的字段是索引，后面的字段不是索引，那么就会导致全表扫描，索引下推就是解决这个问题的
      覆盖索引：索引中包含了查询的字段，不需要回表
      联合索引：多个字段组成的索引，可以减少索引的数量，但是也会增加索引的长度，所以需要权衡
      前缀索引：索引的字段不是全部，而是前面的一部分，可以减少索引的长度，但是会增加查询的次数，需要权衡



监控报警、 排查慢SQL、MySQL调优
    监控报警(搭建 Prometheus  + Grafana)
        监控MYSQL查询性能，
    排查慢SQL
        通过慢查询日志，找出慢查询SQL，然后通过explain分析SQL执行计划，找出问题所在
        show status like 'slow_queries';
        show variables like 'long_query_time';
        show variables like 'slow_query_log';
        开启慢查询日志，修改慢查询阈值：
        set slow_query_log='ON';    #开启慢查询日志
        set long_query_time = 1;     #设置慢查询阈值
        分析查询计划 explain：
            explain分析sql执行计划（访问类型、记录条数、索引长度等）；主要关注字段：
            possible_keys：查询可能用到的索引
            key：实际使用的索引
            key_len：实际使用的索引的字节数长度。
            type：访问类型，看有没有走索引。all（全表扫描），ref（命中非唯一索引），const（命中主键/唯一索引）、range(范围索引查询)、index_merge(使用多个索引)、 system(一行记录时,快速查询)。
            Extra：额外信息。看有没有走索引。
            using index：覆盖索引，不回表。
            using filesort：需要额外的排序。排序分为索引排序和filesort排序，索引排序一般更快，深分页等查询数据量大时filesort更快。
            using index condition：索引下推。MySQL5.6开始支持。联合索引某字段是模糊查询（非左模糊）时，该字段进行条件判断后，后面几个字段可以直接条件判断，判断过滤后再回表对不包含在联合索引内的字段条件进行判断。
            using where：不走索引，全表扫描

十五、三范式

1NF(第一范式)：属性不可再分。
2NF(第二范式)：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。
3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。

十六、页数据

页数据：每一页最大默认是16KB 包含 文件头 38字节， 页头 56字节， 两个虚拟行记录 26字节， 行数据 大小不确定， 页中尚未使用的空间 大小不确定， 页字典 记录某条数据的相对位置 大小不确定， 叶尾 8字节 校验页是否完整

相关面试问题

1.100万数据的表或1亿数据的表如何优化？
    1.优化数据库配置
    2，增加硬件资源
    3.分库分表
    4.冷热数据拆分
    5.sql索引优化
2.为什么sql语句不要过多使用join
    1.性能问题，（会产生临时表。join是2个或多个表连接查询，需要大量的计算资源和内存，join操作过多会导致sql的执行效率降低）
    2.可读性和维护性问题
3.limit 500000和limit 50的速度一样快吗？优化方案
    1.速度：前者查询一般会查询500000数据然后丢弃，后者直接获取  （数据量很小的情况下，相差不大，如果相差恨到会跳过大量的数据行）
    2.优化：使用合适的索引，
        从业务层面优化：如果涉及大量数据查询，做冷热数据分离
        从sql层面优化：假设存在主键id，可以使用子查询跳过前面的数据集
4、高度为3的b+树可以存放多少数据？
	大约2千万左右。
5.Mysql夺命三连问：什么是索引下推？什么是索引覆盖？什么是回表
	索引下推：是mysql5.6推出的查询方案，主要目的是减少数据活查询中不必要的数据读取和计算。将查询条件尽可能的推送到索引层面进行一个过滤，减少从磁盘读取的数据量，和后续的计算开销
	索引覆盖:就是查询条件与返回值都在索引上面，不需要回表
	回表：就是通过索引查询后的数据，根据主键获取完整表数据
6.sql组内排序
7.数据量多大的时候要开始分表分库？    
	需要结合业务场景和系统架构来考虑？
    1.单表数据量超过百万级别，考虑分表
    2.单个数据库的性能无法满足需求的时候考虑分库
    3.数据库的访问频率，单个库的某些表的并发非常高，又无法满足并发需求，需要把这些表分到不同的数据库节点上，去提高整个数据库的io能力
    4.业务拆分：当系统业务逻辑越来越复杂，不同的业务之间的数据耦合度越来底，需要考虑对系统的拆分，方便管理和扩展
8.timestamp和datetime的区别
    1.datatime范围更大到9999年，timestamp到2038年，timestamp只需要4个字节，datatime需要8个字节，timestamp存储时以秒为单位，datetime存储的时具体日期和时间
    2.timestamp没有默认值，默认值时当前时间，datatime也没有默认值，默认为NULL
    3.timestamp受时区影响，datatime不受时区影响
9. limit 1000000 加载很慢的话，你是怎么解决的呢？
    - 方案一：如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit
    - 方案二：在业务允许的情况下限制页数：
    - 方案三：order by + 索引（id为索引）
    - 方案四：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）
10.DROP和TRUNCATE和DELETE的区别是什么？
	- DROP是删除表和数据不记录日志
	- DELETE命令从一个表中删除某一行，或多行，TRUNCATE命令永久地从表中删除每一行。
	- delete支持条件删除，truncate只能删除整个表
	- delete时DML语句，delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项，固然会慢
	- truncate是DDL语句，truncate不需要支持回滚，会保留聚集索引，重置自增索引
11.索引合并：可以让一条SQL使用多个索引。然后对这些索引取交集、并集、交集的并集，从而减少读表次数，提高查询效率。
   	执行计划的type列会显示index_merge，
12.MySQL为什么使用B+树，而不是B树？
    1.当数据量大的时候，树的高度会比较高，数据量大的时候，查询会比较慢；
    2.当数据线性增大时，二叉搜索树会呈现单边倒的情况，时间复杂度退化 O(n)，效率更低；
    3.b+树的数据只存储在叶子节点，而非叶子节点存储的时索引和指针
    4.B树没有内部节点和叶子结点的区分，它的每个节点都是即存了key又存了data
    5.因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO 操作变多，查询性能变低。
    6.进行范围查询时，由于缺乏叶子结点的连接，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的IO问题，效率不如B+树。
13.MySQL中叶子节点超过16k的问题
	当一个数据行的大小超过16KB时，MySQL无法存储这行数据，会抛出"Row size too large"的异常。这会导致数据被丢失，严重影响系统稳定运行
	增加叶子节点大小：可以通过修改参数innodb_page_size来实现。
14.复合(组合)索引失效的几种情况总结 
    -复合索引绑定的第一个列,没有出现在查询条件中;（全部失效，第2-7项的情况是部分失效）
    -复合索引绑定的多个列是有顺序的,某一个列没有出现在查询条件中,存储引擎不能使用索引中该列及其后的所有列。
    -查询条件中出现某个列是范围查询的，存储引擎不能使用复合索引中该列其后的所有列。
    -查询条件中某列使用否定条件的（!= <> IS NOT NULL），存储引擎不能使用索引中该列其后的所有列。
    -查询条件中某列使用LIKE条件后的字段是以%开头的（如：’%ABC’），存储引擎不能使用索引中该列及其后的所有列。
    -查询条件中某列使用函数的，存储引擎不能使用索引中该列及其后的所有列。
    -查询条件中某列使用类型转换的（包括显示的和隐示的），存储引擎不能使用索引中该列及其后的所有列。如：字符串类型的列NAME=3,就是隐示的类型转换，将INT型转换为字符串类型。如果写为NAME=’3’,就不是类型转换。
15.DISTINCT和GROUP BY的区别?
    -有索引的情况下：group by和distinct都能使用索引，效率相同。
    -无索引的情况下：distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发filesort，导致sql执行效率低下。
    -DISTINCT子句将所有NULL值视为相同的值。
16.亿级数据中如何查询 uid为4(非主键id)的那一条数据：
	1.0 分库分表
        垂直分库、水平分库
    2.0 ES存储
17.mysql怎么优化？
	Mysql 慢查询：聚合查询、多表查询、表数据过大查询、深度分页查询
	表象：页面加载过慢、接口压测响应时间过长（超过了1s）
18.mysql中b+树在插入时怎么变化的？
 	1.如果节点不存在，则新增节点，并作为B+树的根节点
	2.如果节点存在，则查找当前数值所对应的位置，然后插入到叶子节点（如果插入的节点未达到最大数量）
	3.如果已经达到最大数量，则将当前叶子节点进行对半分裂，将（m/2）个放入左边节点，剩余放入右边节点。
	4.将分列后的右节点的第一个值上升到父节点中。如果未达到最大数则结束，如果达到则继续分裂

本文由 zzpp 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: 2024/09/12 09:05