-
mysql面试题
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
- 视图
- 逻辑架构
- MySQL 中的 SQL执行流程
- 存储引擎
- MyISAM和InnoDB
- 索引的数据结构
- 常见索引概念
- InnoDB的B+树索引的注意事项
- 为什么不适用Hash索引？
- 为什么不使用B树索引
- MySQL8.0索引新特性
- 哪些情况适合创建索引
- 哪些情况不适合创建索引
- 性能分析工具的使用
- 索引失效案例
- 关联查询优化
- 子查询优化
- 排序优化
- GROUP BY优化
- 优化分页查询
- 优先考虑覆盖索引
- 如何给字符串添加索引
- 索引下推（ICP）
- 从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？
- EXISTS 和 IN 的区分
- SELECT COUNT(*) 、 SELECT COUNT(1) 和 SELECT COUNT(具体字段)
- 关于SELECT(*)
- LIMIT 1 对优化的影响
- 多使用COMMIT
- 三大范式
- 反范式化
- 如何定位调优问题
- 调优的维度和步骤
- 优化MySQL服务器
- 优化数据库结构
- 大表优化
- MySQL 8.0新特性：隐藏索引对调优的帮助
- 事务的ACID特性
- 事务的状态
- 数据并发问题
- 四种隔离级别
- 事务的四种特性到底是基于什么机制实现呢
- redo日志
- redo日志的刷盘策略
- MTR
- undo日志
- undo日志的作用
- redo和undo的区别
- 锁的概述——读写并发问题
- MYSQL的锁
- MVCC
- MVCC如何解决幻读
- 二进制日志(bin log)
- 中继日志
- 主从复制的原理
- bingLog写入时机
- binlog与redolog对比
- 两阶段提交
- 主从复制的作用
- binlog的格式
- 主从同步的数据一致性问题
- 数据库备份
- mycat核心概念
- MyCat原理介绍
- mycat配置文件
- MyCat分片
- 分片规则
- mycat性能监控工具
- Mycat读写分离，以及banlance机制
- 一主一从和双主双从
- mycat集群
- 主从同步不一致
视图
-
视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
-
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
-
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
-
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
- 在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化；反之亦然。
-
视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。
逻辑架构
那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为
第1层：连接层-
系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。
-
经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
- 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
- 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限
-
TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。
第2层：服务层
-
SQL Interface: SQL接口
- 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface
-
Parser: 解析器
- 在解析器中对 SQL 语句进行语法分析、语义分析。并为其创建 语法树
-
Optimizer: 查询优化器
- SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个
执行计划 。 - 这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连
接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将
查询结果返回给用户。
- SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个
-
Caches & Buffers： 查询缓存组件
- 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。
-
第3层：引擎层
- 正的负责了MySQL中数据的存储和提取，对物理服务器级别
维护的底层数据执行操作
- 正的负责了MySQL中数据的存储和提取，对物理服务器级别
-
存储层
MySQL 中的 SQL执行流程
- 大多数情况查询缓存就是个鸡肋，为什么呢？
- 查询缓存命中率不高:两个查询请求在任何字符上的不同（例如：空格、注释、
大小写），都会导致缓存不会命中。 - 如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于 更新压力大的数据库 来说，查询缓存的命中率会非常低
- MYSQL 8.0去除了查询缓存
- 查询缓存命中率不高:两个查询请求在任何字符上的不同（例如：空格、注释、
存储引擎
-
InnoDB 引擎：具备外键支持功能的事务存储引擎
- 大于等于5.5之后，默认采用InnoDB引擎
- 默认事务型引擎
- 数据文件结构：
- 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
- 表名.ibd 存储数据和索引
- 对比MyISAM的存储引擎， InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引（写的时候，需要维护多个索引）
- MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。(MyISAM的索引和数据是分开的，InnoDB 是放在一起的)
-
MyISAM 引擎：主要的非事务处理存储引擎
- MyISAM 不支持事务、行级 锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复
- 5.5之前默认的存储引擎
- 优势是访问的 速度快（二级索引的叶子节点存储的是真实的数据地址，直接拿着地址找数据，即使回表，也是很快的） ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
- 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高（MyISAM 会在数据库底层维护一个记录行数的常量）
- 数据文件结构
- 表名.frm 存储表结构
- 表名.MYD 存储数据 (MYData)
- 表名.MYI 存储索引 (MYIndex)
- 应用场景：只读应用或者以读为主的业务
-
Memory 引擎：置于内存的表
- Memory采用的逻辑介质是 内存 ， 响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。
- Memory同时 支持哈希（HASH）索引 和 B+树索引 。
- Memory表至少比MyISAM表要 快一个数量级 。
- 数据文件与索引文件分开存储。
- 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。
- 用的最少
MyISAM和InnoDB
-
存储限制
- MyISAM：有
- InnoDB：64TB
-
事务安全
- MyISAM：不支持事务
- InnoDB：支持事务
-
锁机制
- MyISAM：表锁，不支持行锁，，即使操作一条记录也会锁住整个表，不适合高并发的操作
- InnoDB：行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
-
哈希索引
- MyISAM:不支持
- InnoDB：不支持
-
索引缓存
- MyISAM:只缓存索引，不缓存真实数据
- InnoDB:不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
-
支持外键
- MyISAM：不支持外键
- InnoDB：支持外键
-
聚簇索引
- MyISAM：没有聚簇索引，全是二级索引
索引的数据结构
- 索引的本质：索引是数据结构。
- 优点
- 提高数据检索的效率，降低 数据库的IO成本
- 通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性
- 加速表和表之间的连接
- 减少查询中分组和排序的时 间
- 缺点
- 空间上的代价:索引即数据，数据即索引
- 时间上的代价：索引提高了读的效率，却降低了写的效率
一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页 最多存放3条记录 ，存放目录项记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项- 记录的内节点代表的数据页可以存放 1000条目录项记录 ，那么：- 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
- 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
- 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
- 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记
录！！！
你的表里能存放 100000000000 条记录吗？所以一般情况下，我们 用到的B+树都不会超过4层 ，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过 二分法 实现快速定位记录。
常见索引概念
-
聚簇索引
-
使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
- 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表
- 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键
大小顺序排成一个 双向链表 。
-
B+树的 叶子节点 存储的是完整的用户记录。
- 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
-
优点：
- 数据访问更快 ，记录真实的数据，不需要回表
- 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
-
缺点：
- 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
- 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
- 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据
-
-
二级索引（辅助索引、非聚簇索引）
-
回表
- 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根
据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就
是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！
- 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根
-
为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗
- 可以这样做，但是数据冗余太严重，如果二级索引中也存储完整的用户数据，相当于换了个排序方式的聚簇索引
-
联合索引
- 我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按
照 c2和c3列 的大小进行排序，这个包含两层含义：- 先把各个记录和页按照c2列进行排序。
- 在记录的c2列相同的情况下，采用c3列进行排序
- 注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意
思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下- 建立 联合索引 只会建立如上图一样的1棵B+树。
- 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树
- 我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按
InnoDB的B+树索引的注意事项
- 根页面位置万年不动
- 内节点中目录项记录的唯一性（除了记录索引列，还会记录主键）
- 一个页面最少存储2条记录
为什么不适用Hash索引？
不适合范围查找
为什么不使用B树索引
实际上mongDB使用的就是B树索引，只不过MYSQL引擎没有使用B树木索引
B+树索引和B树索引最大的区别就在于，B+树的数据只存储在叶子节点，而B树索引数据会存储在叶子节点和非叶子节点。 一个页的默认大小是16KB，我认为如果非叶子节点如果存储数据，那么会降低检索叶子节点的效率，因为检索是从根节点开始，如果一次检索出的数据更多，检索定位到叶子节点的速度就快。而B树的非叶子节点会存储数据，索引有非叶子定位叶子节点的效率相对于较慢，但是这个又涉及到概率问题，如果检索的数据正好在非叶子节点中，那么只需要两次IO，最夸张的情况下，可能在根节点就能检索出数据了,也就是一次IO就可以了，但是B+树比较稳定，一般只需要3次IO，B树的话，看情况，运气好1次，不好就会很多次，因为B树的结构会更深。相比来说B+树更好
MySQL8.0索引新特性
-
降序索引
-
隐藏索引
- 在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能
通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较
大，这种操作就会消耗系统过多的资源，操作成本非常高。 - 从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使
查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），
确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索 引，再删除索引的方式就是软删除 。
- 在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能
哪些情况适合创建索引
-
字段的数值有唯一性的限制
-
频繁作为 WHERE 查询条件的字段
-
经常 GROUP BY 和 ORDER BY 的列
-
UPDATE、DELETE 的 WHERE 条件列
-
.DISTINCT 字段需要创建索引
-
多表 JOIN 连接操作时，创建索引注意事项
- 连接表的数量尽量不要超过 3 张
- 对 WHERE 条件创建索引
- 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致
-
使用列的类型小的创建索引
- 索引也是数据，列的类型越小，索引占用的空间也就越小，同样一个页检索出的数据就越多
-
使用字符串前缀创建索引
-
区分度高(散列性高)的列适合作为索引
-
使用最频繁的列放到联合索引的左侧（最左前缀原则）
-
在多个字段都要创建索引的情况下，联合索引优于单值索引
-
限制索引的数目
- 能用limit就用limit，同时一定不要使用*
哪些情况不适合创建索引
- 在where中使用不到的字段，不要设置索引
- 数据量小的表最好不要使用索引
- 有大量重复数据的列上不要建立索引
- 避免对经常更新的表创建过多的索引
- 不建议用无序的值作为索引
- 删除不再使用或者很少使用的索引
- 不要定义冗余或重复的索引
性能分析工具的使用
-
数据库服务器的优化步骤
-
首先观察服务器状态，是否存在 周期性变动，是不是业务活动造成流量变大，如果是可以考虑使用缓存解决
-
如果不是，开启慢查询，定位到慢sql，找到满sql后开始分析
- EXPLAIN：查看下执行优化器选择的执行计划（是否索引，索引是否失效）
- SHOE PROFILE：分析到底是SQL执行慢，还是等待时间慢
- 如果是等待时间慢，就得考虑进行参数调优
- 如果是SQL执行慢，就需要进行SQL的优化
-
如果问题，没有解决，就得考虑【读写分离】、【分库分表】了
-
-
查看系统性能参数
- 在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。 SHOW STATUS语句语法如下：SHOW [GLOBAL|SESSION] STATUS LIKE ‘参数’
- 一些常用的性能参数如下：
- Connections：连接MySQL服务器的次数。
- Uptime：MySQL服务器的上线时间。
- Slow_queries：慢查询的次数。
- Innodb_rows_read：Select查询返回的行数
- Innodb_rows_inserted：执行INSERT操作插入的行数
- Innodb_rows_updated：执行UPDATE操作更新的行数
- Innodb_rows_deleted：执行DELETE操作删除的行数
- Com_select：查询操作的次数。
- Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
- Com_update：更新操作的次数。
- Com_delete：删除操作的次数。
-
统计SQL的查询成本：last_query_cost
- 如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：
运行结果（1 条记录，运行时间为 0.042s ）
然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：
如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？
运行结果（100 条记录，运行时间为 0.046s ）：
然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。
你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间
基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页 数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 没有增加多少查询时间 。 - 使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候
- 如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：
-
定位执行慢的 SQL：慢查询日志
-
开启slow_query_log
- set global slow_query_log=‘ON’;
- set global slow_query_log=‘ON’;
-
修改long_query_time阈值
- show variables like ‘%long_query_time%’;
- 查询当前系统中有多少条慢查询记录
- SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’;
- 分析日志：mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log
- show variables like ‘%long_query_time%’;
-
-
查看 SQL 执行成本：SHOW PROFILE
-
分析查询语句：EXPLAIN
-
分析优化器执行计划：trace
索引失效案例
-
全值匹配我最爱(联合索引)
-
最佳左前缀法则
-
主键插入顺序
-
计算、函数、类型转换(自动或手动)导致索引失效
-
类型转换导致索引失效
-
范围条件右边的列索引失效
-
不等于(!= 或者<>)索引失效
-
is null可以使用索引，is not null无法使用索引
-
like以通配符%开头索引失效
-
OR 前后存在非索引的列，索引失效
-
数据库和表的字符集统一使用utf8mb4
关联查询优化
- 保证被驱动表的JOIN字段已经创建了索引
- 需要JOIN 的字段，数据类型保持绝对一致。
- LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
- INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
- 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
- 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
- 衍生表建不了索引
子查询优化
排序优化
- 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过
滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段
上。反之，亦然。
GROUP BY优化
优化分页查询
- 优化思路一
优先考虑覆盖索引
- 什么是覆盖索引？
- 一个索引包含了满足查询结果的数据就叫做覆盖索引。简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。
如何给字符串添加索引
- 优先考虑 前缀索引
- 说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少
- 使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。（因为前缀索引存储的不是完整的数据）
索引下推（ICP）
利用索引列，通过where减少回表操作
从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？
EXISTS 和 IN 的区分
SELECT COUNT(*) 、 SELECT COUNT(1) 和 SELECT COUNT(具体字段)
问：在 MySQL 中统计数据表的行数，可以使用三种方式： SELECT COUNT(*) 、 SELECT COUNT(1) 和 SELECT COUNT(具体字段) ，使用这三者之间的查询效率是怎样的？
关于SELECT(*)
LIMIT 1 对优化的影响
多使用COMMIT
三大范式
第一范式：确保表中每一列数据的原子性，不可再分！（必须要有主键）
第二范式：在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。
第三范式设计表：再满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。
反范式化
如何定位调优问题
调优的维度和步骤
我们需要调优的对象是整个数据库管理系统，它不仅包括 SQL 查询，还包括数据库的部署配置、架构等。从这个角度来说，我们思考的维度就不仅仅局限在 SQL 优化上了。通过如下的步骤我们进行梳理：
-
第1步：选择适合的 DBMS
-
第2步：优化表设计
-
第3步：优化逻辑查询
-
第4步：优化物理查询
-
第5步：使用 Redis 或 Memcached 作为缓存
-
第6步：库级优化
优化MySQL服务器
- 优化服务器硬件
- 优化MySQL的参数
优化数据库结构
-
拆分表：冷热数据分离
-
增加中间表
-
增加冗余字段
-
优化数据类型
-
优化插入记录的速度
- mysiam
- INNODB
- mysiam
-
使用非空约束
大表优化
MySQL 8.0新特性：隐藏索引对调优的帮助
事务的ACID特性
事务的状态
数据并发问题
四种隔离级别
事务的四种特性到底是基于什么机制实现呢
redo日志
- 为什么需要redo日志？
redo日志的刷盘策略
一秒刷新一次
MTR
undo日志
undo日志的作用
redo和undo的区别
锁的概述——读写并发问题
- 事务的 隔离性 由这章讲述的 锁 来实现
MYSQL的锁
-
从数据操作的类型划分：读锁、写锁
-
从数据操作的粒度划分：表级锁、页级锁、行锁
- 从对待锁的态度划分:乐观锁、悲观锁
- 其它锁之：全局锁
- 其它锁之：死锁
- 按加锁的方式划分：显式锁、隐式锁
MVCC
MVCC如何解决幻读
REPEATABLE READ 只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。
二进制日志(bin log)
中继日志
主从复制的原理
Slave 会从 Master 读取 binlog 来进行数据同步。
bingLog写入时机
binlog与redolog对比
两阶段提交
在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的 写入时机 不一样。
两阶段提交解决的是，由于主库宕机，导致binlog丢失，但是redo日志已经持久化到磁盘，此时就会出现主从不一致的情况主从复制的作用
binlog的格式
- Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
- Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
- Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。
主从同步的数据一致性问题
如何解决一致性问题？
数据库备份
mycat核心概念
- 分片
- 简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。 数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。
-
垂直（纵向）切分：按照不同的表（或者Schema）来切分到不同的数据库（主机）之上
-
水平（横向）切分：根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库
-
- 简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。 数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。
- 逻辑库(schema)
- MyCat是一个数据库中间件，通常对实际应用来说，并不需要知道中间件的存在，业务开发人员只需要知道数据库的概念，所以数据库中间件可以被看做是一个或多个数据库集群构成的逻辑库
- 逻辑表（table）
- 既然有逻辑库，那么就会有逻辑表，分布式数据库中，对应用来说，读写数据的表就是逻辑表。逻辑表，可以是数据切分后，分布在一个或多个分片库中，也可以不做数据切分，不分片，只有一个表构成。
- 分片表
- 是指那些原有的很大数据的表，需要切分到多个数据库的表，这样，每个分片都有一部分数据，所有分片构成了完整的数据。 总而言之就是需要进行分片的表。
- 非分片表
- 一个数据库中并不是所有的表都很大，某些表是可以不用进行切分的，非分片是相对分片表来说的，就是那些不需要进行数据切分的表
- ER表
- 一个数据库中并不是所有的表都很大，某些表是可以不用进行切分的，非分片是相对分片表来说的，就是那些不需要进行数据切分的表（就是把不需要进行拆分且存在关联关系的表放在同一个库中，进而避免了多表连接的跨库操作）
- 全局表
- 在一个大型的项目中,会存在一部分字典表(码表) , 在其中存储的是项目中的一些基础的数据 , 而这些基础的数据 , 数据量都不大 , 在各个业务表中可能都存在关联 。当业务表由于数据量大而分片后 ， 业务表与附属的数据字典表之间的关联查询就变成了比较棘手的问题 ， 在MyCat中可以通过数据冗余来解决这类表的关联查询 ， 即所有分片都复制这一份数据（数据字典表），因此可以把这些冗余数据的表定义为全局表。（对于一些数据量不大的表，可以考虑使用数据冗余的方式，在多个库中建表，也是避免跨库操作的手段）
- 分片表
- 既然有逻辑库，那么就会有逻辑表，分布式数据库中，对应用来说，读写数据的表就是逻辑表。逻辑表，可以是数据切分后，分布在一个或多个分片库中，也可以不做数据切分，不分片，只有一个表构成。
MyCat原理介绍
MyCat原理中最重要的一个动词就是 “拦截”, 它拦截了用户发送过来的SQL语句, 首先对SQL语句做一些特定的分析,如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL语句发往后端的真实数据库，并将返回的结果做适当处理，最终再返回给用户，如图所示。
在图中,user表被分为三个分片节点dn1、dn2、dn3, 他们分布式在三个MySQL Server(dataHost)上，因此可以使用1-N台服务器来分片,分片规则(sharding rule)为典型的字符串枚举分片规则, 一个规则的定义是分片字段+分片函数。这里的分片字段为 status，分片函数则为字符串枚举方式。MyCat收到一条SQL语句时，首先解析SQL语句涉及到的表，接着查看此表的定义，如果该表存在分片规则，则获取SQL语句里分片字段的值，并匹配分片函数，得到该SQL语句对应的分片列表，然后将SQL语句发送到相应的分片去执行，最后处理所有分片返回的数据并返回给客户端。以"select * from user where status=‘0’" 为例, 查找 status=‘0’ ,按照分片函数, ‘0’ 值存放在dn1,于是SQL语句被发送到第一个节点中执行, 然后再将查询的结果返回给用户。如果发送的SQL语句为 “select * from user where status in (‘0’,‘1’)” , 那么SQL语句会被发送到dn1,dn2对应的主机上执行, 然后将结果集合并后输出给用户。
mycat配置文件
server.xml
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户--> <property name="useHandshakeV10">1</property> <property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 --> <property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 --> <property name="sqlExecuteTimeout">300</property> <!-- SQL 执行超时 单位:秒--> <property name="sequnceHandlerType">2</property> <!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>--> <!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况--> <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property> <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false --> <!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议--> <!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号--> <!-- <property name="processorBufferChunk">40960</property> --> <!-- <property name="processors">1</property> <property name="processorExecutor">32</property> --> <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool --> <property name="processorBufferPoolType">0</property> <!--默认是65535 64K 用于sql解析时最大文本长度 --> <!--<property name="maxStringLiteralLength">65535</property>--> <!--<property name="sequnceHandlerType">0</property>--> <!--<property name="backSocketNoDelay">1</property>--> <!--<property name="frontSocketNoDelay">1</property>--> <!--<property name="processorExecutor">16</property>--> <!-- <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> <property name="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查 <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> --> <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志--> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1开启 0关闭 --> <property name="useOffHeapForMerge">0</property> <!-- 单位为m --> <property name="memoryPageSize">64k</property> <!-- 单位为k --> <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property> <!-- 单位为m --> <property name="systemReserveMemorySize">384m</property> <!--是否采用zookeeper协调切换 --> <property name="useZKSwitch">false</property> <!-- XA Recovery Log日志路径 --> <!--<property name="XARecoveryLogBaseDir">./</property>--> <!-- XA Recovery Log日志名称 --> <!--<property name="XARecoveryLogBaseName">tmlog</property>--> <!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接--> <property name="strictTxIsolation">false</property> <property name="useZKSwitch">true</property> </system> <!-- 全局SQL防火墙设置 --> <!--白名单可以使用通配符%或着*--> <!--例如<host host="127.0.0.*" user="root"/>--> <!--例如<host host="127.0.*" user="root"/>--> <!--例如<host host="127.*" user="root"/>--> <!--例如<host host="1*7.*" user="root"/>--> <!--这些配置情况下对于127.0.0.1都能以root账户登录--> <!-- <firewall> <whitehost> <host host="1*7.0.0.*" user="root"/> </whitehost> <blacklist check="false"> </blacklist> </firewall> --> <user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <user name="user"> <property name="password">user</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> </mycat:server>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
-
schema.xml- name：指定逻辑库的库名 , 可以自己定义任何字符串 ;
- checkSQLschema： 取值为 true / false ;
如果设置为true时 , 如果我们执行的语句为 “select * from ITCAST.TB_TEST;” , 则MyCat会自动把schema字符去掉, 把SQL语句修改为 “select * from TB_TEST;” 可以避免SQL发送到后端数据库执行时, 报table不存在的异常 。不过当我们在编写SQL语句时, 指定了一个不存在schema, MyCat是不会帮我们自动去除的 ,这个时候数据库就会报错, 所以在编写SQL语句时,最好不要加逻辑库的库名, 直接查询表即可。 - sqlMaxLimit
当该属性设置为某个数值时,每次执行的SQL语句如果没有加上limit语句, MyCat也会自动在limit语句后面加上对应的数值 。也就是说， 如果设置了该值为100，则执行 select * from TB_TEST 与 select * from TB_TEST limit 100 是相同的效果 。所以在正常的使用中, 建立设置该值 , 这样就可以避免每次有过多的数据返回。
-
name: 定义逻辑表的表名
-
dataNode：这个逻辑表都在哪些节点上
-
rule：指定逻辑表的分片规则的名字, 规则的名字是在rule.xml文件中定义的, 必须与tableRule标签中name属性对应。
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100"> <!-- auto sharding by id (long) --> <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate" /> --> </schema> <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" /> <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> <dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" /> <dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> </writeHost> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> <!-- <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost> </dataHost> <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat> <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql> <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost> <dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost> <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </dataHost> --> <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql" dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost> </dataHost> --> </mycat:schema>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
MyCat分片
- 垂直分片
- 水平分片
垂直分片和水平分片就是解决数据分片的两种逻辑，在分库分表工作中通常都是二者都有，对于不同的业务，比如订单表和用户表，就可以才用垂直分片，即放在不同的数据节点，而水平分片是对同一个表的数据分片，把一个表的不同的数据放在不同的数据库上
分片解决数据库磁盘的压力和数据查询压力
垂直分片是一种业务纵向考虑的
水平分片是横向考虑的分片规则
分片规则其实是对水平分片来说的
- 取模分片
- 范围分片
- 枚举分片
- 范围求模算法
该算法为先进行范围分片, 计算出分片组 , 再进行组内求模。
优点： 综合了范围分片和求模分片的优点。 分片组内使用求模可以保证组内的数据分布比较均匀，分片组之间采用范围分片可以兼顾范围分片的特点。
缺点： 在数据范围时固定值（非递增值）时，存在不方便扩展的情况，例如将 dataNode Group
size 从 2 扩展为 4 时，需要进行数据迁移才能完成 ；
- 固定分片hash算法（类似于redis的hash槽）
该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与1111111111 进行位 & 运算
优点： 这种策略比较灵活，可以均匀分配也可以非均匀分配，各节点的分配比例和容量大小由
partitionCount和partitionLength两个参数决定缺点：和取模分片类似。
- 取模范围算法
该算法先进行取模，然后根据取模值所属范围进行分片。
优点：可以自主决定取模后数据的节点分布
缺点：dataNode 划分节点是事先建好的，需要扩展时比较麻烦。
- 字符串hash求模范围算法
与取模范围算法类似, 该算法支持数值、符号、字母取模，首先截取长度为 prefixLength 的子
串，在对子串中每一个字符的 ASCII 码求和，然后对求和值进行取模运算
（sum%patternValue），就可以计算出子串的分片数。
优点：可以自主决定取模后数据的节点分布
缺点：dataNode 划分节点是事先建好的，需要扩展时比较麻烦。
-
应用指定算法
由运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串（必须是数字）计算分片号 , 配置如下 :
-
字符串hash解析算法
截取字符串中的指定位置的子字符串, 进行hash算法， 算出分片 ， 配置如下：
- 一致性hash算法
一致性Hash算法有效的解决了分布式数据的拓容问题 , 配置如下:
mycat性能监控工具
Mycat读写分离，以及banlance机制
balance=“0” : 不开启读写分离机制 , 所有读操作都发送到当前可用的writeHost上. （从库只是数据备份的作用）
balance=“1” : 全部的readHost 与 stand by writeHost (备用的writeHost) 都参与 select 语句的负载均衡,简而言之,就是采用双主双从模式(M1 --> S1 , M2 --> S2, 正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。);
balance=“2” : 所有的读写操作都随机在writeHost , readHost上分发
balance=“3” : 所有的读请求随机分发到writeHost对应的readHost上执行, writeHost不负担 读压力 ;balance=3 只在MyCat1.4 之后生效 .一主一从和双主双从
一主一从：master写库宕机，salve读库其实不会切换为主库
双主双从：解决单主库宕机的问题
mycat集群
主从同步不一致
-
MySQL主从不同步情况
-
1、网络的延迟
由于mysql主从复制是基于binlog的一种异步复制通过网络传送binlog文件，理所当然网络延迟是主从不同步的绝大多数的原因，特别是跨机房的数据同步出现这种几率非常的大，所以做读写分离，注意从业务层进行前期设计。 -
2、主从两台机器的负载不一致
由于mysql主从复制是主数据库上面启动1个io线程，而从上面启动1个sql线程和1个io线程，当中任何一台机器的负载很高，忙不过来，导致其中的任何一个线程出现资源不足，都将出现主从不一致的情况。 -
3、max_allowed_packet设置不一致
主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。 -
4、自增键不一致
key自增键开始的键值跟自增步长设置不一致引起的主从不一致。 -
5、同步参数设置问题
mysql异常宕机情况下，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏，导致主从不一致。 -
6、自身bug
mysql本身的bug引起的主从不同步 -
7、版本不一致
特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面不支持该功能。
-
1.停止slave从节点 stop slave; 2.跳过一步错误，后面的数字可变 set global sql_slave_skip_counter =1; 3.开启slave start slave; 4.查看slave状态 show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes ok，现在主从同步状态正常了。。。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
3、使用第三方工具如pt-table-sync
- 相关阅读:
字符编码个人理解
搭建自己的文件服务器
矩阵乘法通过缓存命中率提升运算效率
JavaScript的作用域和作用域链
Google I/O 2023 - Dart 3 发布，快来看看有什么更新吧
[附源码]计算机毕业设计JAVA校园失物招领平台
maven_0
ARM pwn 入门 (3)
持续集成和持续部署
mysql中的存储过程
- 原文地址：https://blog.csdn.net/m0_45364328/article/details/126569431
-
