石家庄局域网网站建设,深圳怎么制作公司网页,成都的网站建设开发公司哪家好,南宁网络推广平台有哪些最近开始面试了#xff0c;410面试了一家公司 针对自己薄弱的面试题库#xff0c;深入了解下#xff0c;也应付下面试。在这里先祝愿大家在现有公司好好沉淀#xff0c;定位好自己的目标#xff0c;在自己的领域上发光发热#xff0c;在自己想要的领域上#xff08;技术…最近开始面试了410面试了一家公司 针对自己薄弱的面试题库深入了解下也应付下面试。在这里先祝愿大家在现有公司好好沉淀定位好自己的目标在自己的领域上发光发热在自己想要的领域上技术管理、项目管理、业务管理等越走越远希望各位面试都能稳过待遇都是杠杠的
mysql基础问题可以查看两篇博客会进行不定期更新更新慢了请大家谅解大家有想了解的面试题相关可以评论下我会尽快更新进去。希望在下个月能读透所有的技术问题并找到合适的一个公司面试题之数据库相关-mysql篇-CSDN博客 1.如何设计一个支撑每秒10万写入的高并发MySQL系统 分布式架构核心原则 分布式架构原则 分而治之讲写入负载分散到多个节点中异步处理解耦即使相应与数据持久化冗余扩展所有组件无单点故障 典型架构拓扑 客户端 → 负载均衡 → 写入API层 → 消息队列 → 分库分表集群↓缓存集群↓数据分析层 分库分表详细方案 分片策略设计 水平分库分表分片路由策略 一致性哈希扩容流程 新增物理节点注册到元数据中心 数据迁移工具扫描待迁移分片 双写新旧分片直至数据同步完成 流量切换至新分片 清理旧分片冗余数据 写入链路优化 异步写入架构 1. 客户端请求 → 2. API服务验证 → 3. 写入Kafka → 4. 返回成功响应↓
5. 消费者批量写入MySQL → 6. 更新Redis缓存 批量写入优化消息队列配置kafka生产配置 mysql极致优化 关键的innodb参数连接与线程配置 高可用保障措施 多活数据中心设置双主同步且都有备库主库通过专线同步到热备主库热备主库同步热备从库信息故障自动转移机制 性能验证方法 压测工具关键指标监控 指标阈值监控工具QPS单库1万Prometheus主从延迟100mspt-heartbeat线程运行数max_connections×80%Grafana磁盘IOPS标称值70%iostat 典型解决方案 热点数据问题 动态分片将热点分片进一步拆分 本地缓存在应用层缓存热点分片路由 限流保护对特定分片实施写入限流 分布式事务处理 本地写入消息队列 异步发送MQ(可靠消息服务保证) 2.MySQL Group Replication vs MGR vs 传统主从复制
核心架构差异
特性传统主从复制MySQL Group Replication (MGR)拓扑结构主从星型拓扑全对称P2P架构数据同步方式异步/半同步复制基于Paxos协议的原子广播组成员管理手动配置自动故障检测与成员管理一致性级别最终一致性即时一致性(可配置)故障切换需手动或借助工具自动选举新Primary
技术对比 传统同步 数据流基于binlog的逻辑日志复制模式 异步复制默认半同步复制after_commit/after_sync 局限 脑裂风险切换时可能丢数据从库可能落后 MGR架构 核心组件 group communication engine:基于paxos的xcom协议certification layer冲突检测 工作流程 事务在本地执行广播到组内所有节点多数节点确认后提交应用事务到所有节点 关键能力对比
能力维度传统复制MGR自动故障转移❌ 需要VIP/Proxy✅ 内置自动选举多主写入❌ 单主✅ 支持多主(需配置)数据一致性最终一致强一致(多数节点确认)网络分区容忍❌ 可能丢数据✅ 遵循CP原则节点扩展性线性扩展读能力建议3-9个节点
性能指标对比
指标传统半同步复制MGR单主模式MGR多主模式写入TPS12,0009,5007,200平均延迟(ms)81522故障切换时间(s)5-30(手动)1-31-3网络带宽消耗1X2.5X3X 适用场景 传统复制适合 读写分离的报表系统 异地灾备场景 对一致性要求不高的业务 已有成熟中间件管理的环境 MGR适合 需要高可用的核心业务系统 金融级数据一致性要求的场景 云原生/K8s环境部署 希望减少外部依赖的架构 Q:MGR的Paxos协议如何保证数据一致性
A:MySQL Group Replication (MGR) 通过改进的Paxos协议具体实现为XCom来保证分布式环境下的数据一致性
一致性保证核心步骤 事务准备阶段 客户端事务在Primary节点执行 生成包含所有修改的写集(writeset) 写集通过认证层进行冲突检测 提案广播 Primary节点作为Proposer将写集广播给所有节点 节点收到提案后 检查自身状态是否正常 验证写集冲突 返回Promise应答 多数派确认Accept/Accepted 当收到多数节点(N/21)的Promise后 Primary发送Accept请求 各节点将事务写入relay log内存磁盘 全局提交Learn 收到多数节点的Accepted响应后 Primary提交本地事务 广播Commit消息通知所有节点 所有节点应用该事务 关键一致性保护机制 多数派原则(Quorum) 每个事务必须得到多数节点确认 3节点集群至少2个确认 5节点集群至少3个确认 公式W R N (W写节点数, R读节点数, N总节点数) 冲突检测与解决 基于GTID的认证检查冲突 版本向量每个事务携带版本信息 视图同步 异常处理机制 节点故障处理 故障类型处理方式少数节点宕机继续服务多数节点宕机停止服务网络分区多数派分区继续服务 脑裂预防 世代时钟(Epoch Number)每次视图变更递增 Fencing机制旧Primary自动降级 恢复流程 故障节点重新加入 从最新节点拉取GTID集合 自动选择增量同步或全量同步 追平数据后重新加入组 与经典的paxos区别 特性经典PaxosMGR-XCom成员管理静态动态消息传输原始UDPTCP流控领导者角色临时选举稳定Primary数据载体任意值事务写集持久化点多数接受多数确认 性能优化 流水线化处理 批量认证合并多个事务的写集检测减少网络往返次数 流控机制 一致性级别配置 MGR支持两种模式 单主模式SET GLOBAL group_replication_single_primary_modeON; 所有写操作到primary 保证线程一致性 多主模式SET GLOBAL group_replication_enforce_update_everywhere_checksON; 任何节点可写 保证因果一致性
3.数据误删后如何快速恢复删库删表后的操作
紧急处理流程 A[发现误删] -- B[立即停止相关服务] -- C[评估影响范围] -- D[选择恢复方案] -- E[执行恢复] -- F[数据验证] -- G[恢复服务] 基于备份恢复流程 全量数据备份恢复根据每日备份的数据进行dump恢复根据时间点恢复需要binlog有完整的日志 前提需要打开binlog日志查看脚本show variales like log_bin%;-- 相关内容根据binlog日志一个个去找mysqlbinlog -v binlog.000007 | grep -i drop table AAA根据该命令找对应的日志文件。推荐下载到本地根据binlog中的位置点对数据回滚# at 1020恢复其他节点的日志到1020 恢复其他binlog日志7之前的所有的文件恢复 mysqlbinlog ./binlog.000001 ./binlog.000002 ./binlog.000003 ./binlog.000004 ./binlog.000005 ./binlog.000006 | mysql -uroot -password 恢复日志文件7的到某个节点mysqlbinlog --stop-position 1020 ./binlog.000007 |mysql -uroot -password 无备份恢复方案 使用binlog2sql工具 使用美团开源的myflash工具 innodb引擎特殊恢复 使用undrop-for-innodb工具适用场景无备份且binlog不可用 数据库恢复服务MySQL数据恢复专家、DiskInternals MySQL Recovery、Kroll Ontrack 不同操作的恢复策略
操作类型恢复难度推荐方案DELETE误删★★☆binlog2sql闪回DROP TABLE★★★全备恢复binlogTRUNCATE★★★☆解析表空间文件DROP DATABASE★★★★全备恢复磁盘损坏★★★★★专业恢复工具
预防措施 备份策略配置每天进行数据备份安全防护配置设置延迟复制从库启动回收站功能操作审计措施使用预生产环境测试更新功能并对DDL操作的语句进行审计后执行 关键恢复原则 立即停止写入防止覆盖原有数据页 优先使用逻辑备份比物理恢复更安全可控 测试恢复流程定期验证备份有效性 保留多个副本采用3-2-1备份策略(3份副本2种介质1份离线)
Q:基于binlog的闪回Flashback技术实现
A:MySQL闪回技术是通过逆向解析binlog来恢复误操作数据的关键手段
闪回基础原理 binlog记录机制 ROW格式记录行级别变更前镜像before_image和变更后镜像after_image写入时机事务提交时一次性写入整个事务的binlog 闪回核心思想delete逆向操作原理 主流的闪回技术
工具名称开发方语言特点适用场景binlog2sql大众点评Python纯SQL实现精细恢复MyFlash美团C二进制级别高性能恢复mysqlbinlog_flashback阿里C集成补丁云环境
binlog2sql实现深度解析 核心的处理流程 A[解析binlog] -- B[提取DML事件] -- C[构建行变更对象] -- D[生成逆向SQL] -- E[过滤与排序] -- F[输出恢复脚本] 关键实现代码 逆向使用update操作解析闪回sql执行恢复 4.主从延迟的根本原因即解决方案 5.InnoDB的自适应哈希索引AHI适用场景? Q:为什么高频等值查询能加速 6.MySQL的CPU利用率飙高如何定位? Q:通过performance_schema分析热点SQL与锁争用? 7.如何优化一条SELECT COUNT(*) FROM big_table? Q:为什么InnoDB不缓存总行数替代方案? 8.如果让你设计一个分布式MySQL你会考虑哪些问题? Q:数据分片、一致性协议Raft/Paxos、分布式事务XA/TCC的选择? 9.MySQL 8.0相比5.7的核心改进? Q:窗口函数、CTE、原子DDL、直方图统计? 10.为什么MySQL默认隔离级别是RR可重复读?
mysql主从复制是通过binlog日志进行数据同步的而早期的版本中binlog记录的是sql语句的原文。若此时binlog格式设置为statement时mysql可能在从库执行的sql逻辑与主库不一致。比如在删除某个区间数据时delete from user where age 13 and create_time 2025-4-15 limit 1;为什么在sql执行结果不一致 在主库执行这条sql的时候用的是索引age;而在备库执行这条语句的时候却使用了索引create_time.mysql执行优化器会进行采样预估在不同的mysql库里面采样计算出来的预估结果不一样会影响优化器的判断由于优化器会进行成本分析可能最终选择索引不一致。这跟sql执行过程有一定关系。而因为这条delete语句带了limit所以查出来的记录很大可能不会是同一条数据排序可能不一样会导致准备数据不一致的情况。另外使用RC或者RU的话是不会添加GAP LOCK间隙锁而主从复制过程中出现的事务乱序问题更容易导致备库在SQL回滚后与主库内容不一致。所以mysql选择了RR隔离级别RR级别在更新数据时会增加记录锁和间隙锁可以避免事务乱序导致的数据不一致问题。
Q:为啥ORACLE选择的默认级别是RC
Aoracle目前支持三种事务隔离级别RC(读已提交默认)serializable可串行化read-only只读其中Read-only隔离级别类似于序列化隔离级别但只读事务甚至不允许在事务中进行数据修改显然只能选择RC Q:为何大厂要改成RC
A出于性能、死锁和实时性高的需求
提高并发性RC隔离级别下锁粒度小只锁住一行数据提高了并发性尤其读密集的应用下表现优异。行级锁减少了锁冲突提升了并发度。减少死锁RR级别下会增加GAP Lock和next-key lock是的所得粒度变大死锁的概率也增大。而RC隔离级别下不存在间隙锁只需行锁即可减少了死锁的发生概率。满足实时性RC每次读取数据都会获取最新的行版本适合实时性要求高的应用而RR读取的数据可能不会反应出其他事务对数据的更改无法满足对实时性要求高的场景简化主从同步RC要求实行行式binlog有助于减少主从同步时数据不一致问题 11.分库分表下如何实现精准分页
全局排序发推荐 实现步骤统一排序字段确保所有的分片使用相同的排序规则如使用时间排序 -- 每个分片执行
SELECT * FROM table ORDER BY sort_field LIMIT (pageNo-1)*pageSize pageSize 各分片查询想所有分片发送相同的分页查询请求内存归并将分片返回的结果在内存中排序然后截取制定页数据性能优化使用流式处理避免内存溢出优点是结果绝对精确缺点是随着页码增大性能会下降出现深分页问题 二次查询法优化深分页 实现步骤各分片查询排序字段值不返回具体完整行数据 SELECT id FROM table ORDER BY create_time LIMIT 10000, 10获取最小/最大边界值用边界值精确查询完整数据 SELECT * FROM table
WHERE create_time BETWEEN ? AND ?
ORDER BY create_time LIMIT 10 分片键连续分页法 适用场景 分片键本身具有连续性如时间范围分片能预先确定分片键的分布情况 实现方式 根据分片键确定数据所在分片只向特定分片发起查询在该分片内做常规分页 适用elasticSeach等搜索引擎 将分库分表数据同步到ES利用ES的分布式分页能力注意 ES的formsize方式也有深分页限制可考虑使用search_after或者scroll api 业务层解决方案 禁止跳页 只提供下一页功能每次携带最后一条记录的排序字段值 SELECT * FROM table
WHERE create_time ?
ORDER BY create_time DESC
LIMIT pageSize 性能优化建议 避免深分页产品设计上限制最大页码使用覆盖索引减少回表操作缓冲热门页对前几页结果进行缓冲预计算对静态数据可提前计算分页结果分批获取客户端分批加载数据无线滚动 技术选型
方案精准度性能实现复杂度适用场景全局排序高中/差中数据量中等要求绝对准确二次查询高较好高深分页场景分片键连续高优低分片键分布规律明显ES搜索高优中已使用ES的场景禁止跳页中优低用户浏览行为 12.mysql如何同步ES
分库分表的情况下如果需要精确的查找数据需要用elasticsearch的集成此时如何同步ES有四种方案应用同步双写、MQ队列双写、基于SQL脚本同步、基于binlog同步组件
应用数据库同步双写 特点应用在同步数据库的时候也同步给ES保障了ES的实时性实现起来相对简单不需要引入额外的组件或者复杂的逻辑。但是每次写入mysql的同时写入ES可能会对两个系统的性能产生影响在高并发的情况下会到值双写失败导致数据不一致等问题且每次写入操作都需要双写逻辑增加了业务逻辑的复杂性和维护难度具体实现 代码调整每次数据库调整的时候复制相同逻辑到elasticsearch事务管理使用数据库的事务确保操作的院子性避免数据不一致性能优化尽量批量操作或者使用异步方式来处理降低对性能的影响 MQ队列异步双写 特点利用MQ队列可以实现异步处理通过异步方式可以降低对数据库写入性能的影响利用消息队列的持久化和重试机制可以调数据的可靠性。但是由于异步处理会存在数据延迟问题而且需要引入消息队列和额外的消费者逻辑增加系统的复杂性。具体实现 消息队列集成选择兵集成一个消息队列如kafka或者RabbitMQ业务逻辑修改将数据写入Mysql后将变更信息发送到消息队列消费者开发开发消费者服务从消息队列中读取消息并异步写入到elasticsearch。异步处理为消息队列的消费这实现异步处理和重试逻辑 基于SQL抽取 特点通过定时任务根据数据库的时间戳字段来抽取并同步数据到elasticsearch,同步哦该方式无序修改业务逻辑对原系统无感知通过定时任务逻辑相对简单易于理解与维护。但是数据库同步存在延迟无法满足实时性要求定时任务可能对数据库产生额外的查询压力。具体实现 时间戳字段添加在对应的数据库表中添加时间戳用于记录数据变更定时任务配置按照固定频率查询mysql中自上次同步后发生变化的数据。数据抽取定时任务将查询结果抽取出来同步到elasticsearch。数据同步将抽取的数据写入到elasticsearch完成同步过程。 利用binlog进行同步 特点利用binlog日志通过消息队列或者直接消费binlog变化来同步数据库到ES中。不需要修改现有的业务代码对现有系统无感知可以利用binlog精确捕捉到数据库的所有变更确保数据同步的完整性binlog可以高效地处理数据变更对源数据库性能营销较小通常配合消息队列使用在网络波动或者服务故障的情况下也能保证数据库最终一致性。但是需要搭建和维护binlog监听和消息对垒系统增加了系统架构的复杂性虽然基于实时同步但是遇到消息队列积压可能会出现数据延迟。具体实现 binlog启用确保mysql实例开启了binlog功能并且binlog格式row或者mixed能够支持所需的数据库同步需求binlog监听配置部署并配置binlog监听器如debezium监听制定的mysql实例和数据库消息队列集成将binlog监听器和消息队列kafka等集成确保binlog变更能够转换成消息并发送到队列中消息消费者开发开发消息消费者服务该服务从消息队列中读取binlog变更消息将其转换成ES可以理解的格式数据同步消息消费者将转换的数据写入ES完成同步异常处理实现异常处理机制确保数据在同步失败后能够进行重试或者日志记录一遍排障。
13.order by如何工作的呢
ORDER BY 是SQL中用于排序结果集的关键子句其工作原理在不同数据库系统中有所不同但核心机制相似。
基本流程 数据获取阶段 数据库首先执行WHERE条件过滤获取满足条件的记录集 如果使用了索引覆盖扫描可能直接从索引获取数据 排序缓冲区(Sort Buffer)操作 数据库分配一块内存区域作为排序缓冲区 将待排序数据加载到缓冲区可能只加载排序键和行指针 当缓冲区不够会使用磁盘辅助排序 排序执行阶段 在内存或磁盘上对数据进行排序 使用快速排序、归并排序等算法 结果返回阶段 按照排序后的顺序访问数据行 返回给客户端或下一处理阶段 不同场景下的排序 能够使用索引排序最优情况 -- 当有索引(idx_age_name)时 SELECT * FROM users ORDER BY age, name; 工作方式 直接按索引顺序读取数据索引本身是有序的 不需要额外排序操作 性能最佳称为Using index执行计划 内存排序小数据量 -- 数据量小于sort_buffer_size SELECT * FROM small_table ORDER BY create_time DESC; 工作方式 在内存排序缓冲区完成全部排序 使用快速排序法 速度较快毫秒级完成 外部排序大数据量 -- 数据量超过内存限制 SELECT * FROM large_table ORDER BY revenue; 工作方式 将数据分成多个块每块在内存中排序后写入临时文件 对这些有序临时文件进行多路归并 最终得到完全有序的结果集 可能涉及磁盘I/O性能较差 关键参数与优化 sort_buffer_size 控制排序内存缓冲区大小 过小会导致频繁磁盘临时文件 过大可能浪费内存 max_length_for_sort_data 决定排序时存储完整行还是仅排序键行指针 影响内存使用效率 tmpdir 外部排序时临时文件存储位置 应放在高性能存储设备上 执行计划分析 通过explain查看排序方式 using filesort:需要额外排序不走 using index可利用索引顺序无需排序 性能优化 为order by 创建合适索引 -- 为排序创建复合索引 ALTER TABLE orders ADD INDEX idx_status_created(status, created_at); 避免select *** -- 只查询需要的列减少排序数据量 SELECT id, name FROM users ORDER BY age; 合理设置缓冲区大小 -- 会话级调整 SET SESSION sort_buffer_size 8*1024*1024; 利用覆盖索引 -- 索引包含所有查询字段 SELECT user_id FROM orders WHERE statuspaid ORDER BY create_time; 避免复杂表达是排序 -- 不推荐 SELECT * FROM products ORDER BY ROUND(price*0.9); -- 推荐 SELECT *, ROUND(price*0.9) AS discounted_price FROM products ORDER BY discounted_price; 特殊场景处理 多字段混合排序 -- 注意索引设计顺序 SELECT * FROM employees ORDER BY department ASC, salary DESC; limit分页深化 -- 深分页问题解决方案 SELECT * FROM large_table WHERE id last_seen_id ORDER BY id LIMIT 100; null值排序控制 -- 控制NULL值的排序位置 SELECT * FROM customers ORDER BY last_purchase_date NULLS LAST;
Q:我们查询语句怎么匹配到数据的呢
A先通过索引树找到对应的主键然后在拿到对应的主键ID搜索id主键索引树找到对应的行数据这里前提是为排序字段加上索引且不用*需要什么字段直接查询什么字段eg:select name,age,city from user where city fuzhou order by age limit 10加上order by后的执行流程就是
mysql为对应的线程初始化sort_buffer放入需要查询的name、age、city字段从索引树idx_city找到第一个满足cityfuzhou条件的id到主键id索引树中拿到对应的行数据取name、age、city三个字段值存到sort_buffer从索引树idx_city拿下一个记录的主键id重复上面两个步骤知道找不到cityfuzhou的数据为止根据前面找到的所有数据在sort_buffer中将所有age进行排序按照排序结果前10行返回给调用方 找数据-回表-取数据-排序-取前10条记录
Q:sort_buffer不够的时候磁盘临时文件如何辅助处理
sort_buffer大小是通过sort_buffer_size来控制的如果排序的数据小于sort_buffer_size则正在内存中完成否则需要借助磁盘临时文件处理。
可以通过optimizer_trace开启统计在执行sql语句的时候 查询输出统计信息
-- 打开optimizer_trace开启统计
set optimizer_trace enabledon;
--执行sql语句
select name,age,city from user where city fuzhou order by age limit 10;
--查询输出统计信息
select * from information_shcema.optimizer_trace;
根据查询结果中的number_of_tmp_files是否大于0 来查看是否使用了临时文件,sort_mode默认为additional_feilds
若使用了临时文件即归并排序算法大致流程如下
从主键id索引树拿到需要的数据后放到sort_buffer内存块中当sort_buffer快满时对sort_buffer中的数据进行排序排序后将数据放到磁盘的一个小文件中。继续回到主键ID索引树种取数据继续放到sort_buffer内存中排序后也要把这些数据写入到磁盘临时小文件中。继续循环知道去除所有满足条件的数据最后把磁盘排序好的小文件合并成大文件。
Q:既然sort_buffer中存储不下数据为何sort_buffer不只放age字段节省空间呢
rowid的排序就是只查询sql排序的字段和主键id然后在sort_buffer中进行排序。
是否开启rowid排序主要通过max_length_for_sort_data参数该参数mysql用于表示排序行数据的长度超过这个值mysql就会主动换成rowid排序默认为1024根据我们表设计的字段长度来计算。
将max_length_for_sort_data参数改小后通过optimizer_trace开启统计在执行sql语句的时候 查询输出统计信息
--设置排序字段单行最大长度为64
set max_length_for_sort_data 64;-- 打开optimizer_trace开启统计
set optimizer_trace enabledon;
--执行sql语句
select name,age,city from user where city fuzhou order by age limit 10;
--查询输出统计信息
select * from information_shcema.optimizer_trace;
根据查询结果中的number_of_tmp_files是否大于0 来查看是否使用了临时文件。
sort_mode来看是否走了rowid
rowid排序的一个流程
mysql为对应的线程初始化sort_buffer放入需要排序的age字段以及主键id从索引树中拿到对应符合的数据行取出排序条件的age和主键id值存储到sort_buffer从索引树中拿到下一个记录的主键id值重复上面两个步骤知道条件不符合即city!fuzhou前面几步已经找到cityfuzhou的数据在sort_buffer中将所有数据根据age进行排序遍历排序后取前10行并按照id的值回表取出其他需要的字段city、age、name返回给调用方
总结全字段排序与rowid排序对比
全字段排序sort_buffer内存不够的话就需要用到磁盘临时文件造成磁盘访问rowid排序sort_buffer可以放更多的数据但是需要回到原表区数比全字段多一次回表
一般情况下对于innodb存储引擎会优先使用全字段排序其中max_length_for_sort_data一般为1024排序字段一般也不会超过这个长度。 14.存储引擎的三大特性
mysql存储引擎中包含三大特性分别为buffer pool、adaptive hash index、double write。
buffer pool: 原理buffer pool是innodb存储引擎用于缓存数据也和索引的内存区域。它提高了数据库的读写性能因为数据和索引在内存中读写比在磁盘读写快当需要访问数据时会先从buffer pool查找在进去磁盘查找工作方式buffer pool采用LRU最近最少使用算法来管理内存中的页。当buffer pool满时会把最近最少使用的也淘汰腾出新的空间。配置参数 innodb_buffer_pool_size设置buffer pool的大小通常建议设置为物理内存的70%以平衡数据库和其他系统的需求。innobd_buffer_pool_instances设置buffer pool的实例数量用于减少所得竞争提高并发性能。 应用场景适用于频繁读取数据的场景如事务处理、查询优化等 Adaptive hash index 原理adaptive hash index AHI是innodb存储引擎中的一种内存结构用于加速等值查询。inodb会自动监控索引的使用情况如果发现某个索引频繁被访问就会在内存中为该索引创建哈希索引减少查询时间。工作方法AHI是一个哈希表结构键是索引键值值是该键值对应的页面位置。需二级索引频繁查询固定时间内连续多次等值查询号号不行order by无效成为热点数据会建立hash index带来速度的提升。可通过%hash_index%查询配置参数 innodb_adaptive_hash_index:控制是否启用AHI默认启用。在高并发的场景下如果CPU资源紧张可考虑禁用innodb_adaptive_hash_index_partitions:设置AHI的分区数量增加分区数量可以减少哈希冲突但也会增加内存消耗。应用场景使用与频繁等值查询如主键查询或者唯一索引查询。在高并发读取的场景下AHI可以减少磁盘I/O条查询应用速度。 限制 只能用于等值比较如 ,IN等无法用于排序存在哈希冲突的可能性可能导致性能下降维护AHI需要额外的内存和CPU资源 double write: 原理double write是innodb存储引擎用于提高数据完整性和可靠性的机制。它通常在数据页写入数据文件之前将他们写入成double write buffer的连续存储区域确保数据的一致性。如果在写入过程中发生崩溃innodb可以从double write buffer中恢复数据工作方式数据页首先被写入double write buffer 然后通过两次写入操作顺序写将数据写入文件的适当位置。这种方式减少I/O开销因为数据是连续写入的。配置参数 innodb_doublewrite:控制是否启用默认启用innodb_doublewrite_dir:设置文件存储位置innodb_doublewrite_files:定义文件数量innodb_doublewrite_pages控制每个线程的最大double write页数 应用场景用于需要高可靠性和数据一致性的场景。如生产环境中的数据库系统
15.自增id用完了怎么办
表的自增id达到上限后在申请时它的值就不会该表进而导致插入时报主键冲突无法继续插入当表未创建自增Id时rowid自增达到上限后会归0再重新递增若出现相同的rowid则覆盖前面的数据thread_id自增到上限重置为0然后继续增加但我们在show processlist不会看到两个一样的线程id 16.jion算法说明
jion算法包含了简单嵌套循环索引嵌套循环块嵌套循环哈希连接排序合并连接 简单嵌套循环simple nested-loop join snlj 原理驱动表中的每一条记录会依次与被驱动表中的每一条记录比较寻找符合条件的匹配记录。特点SNLJ实现简单直接同时也非常低效通常在小表连接大表的时候使用。优点实现机器简单可广泛应用快速验证缺点性能差对大数据量的查询效率非常低尤其是驱动表和被驱动表数据差大的时候。执行过程 从驱动表取出一条记录遍历被驱动表中的所有记录找到匹配的记录将匹配的结果加入结果集 索引嵌套循环连接index nested-loop join ,inlj 原理INLJ是嵌套循环连接的优化版本通过被驱动表上的索引可以避免对其进行全表扫描而直接通过索引定位到匹配的记录特点利用了被驱动表索引提升查询效率非常适合·小标连接大表·或者·大表间的少量匹配·场景优点速度快大幅减少了被驱动表的扫描此书依赖被驱动表索引若索引丢失性能直接退化为简单嵌套循环执行过程 从驱动表中取出一条记录使用该记录的连接键通过索引快速定位被驱动表中的匹配记录将匹配结果加入结果集 块嵌套循环连接block nested-loop join,bnlj 原理在BNLJ中驱动表的一部分数据会首先被加载到内存缓冲区join buffer中然后在于被驱动表进行比较匹配操作特点通过批量加载缓冲区减少被驱动表的扫描此书适合于·大表主键无索引连接·优点缓冲机制可以显著降低被驱动表扫描次数缺点对内存消耗较大缓冲取不足会导致效率下降执行过程 将驱动表的一部分数据加载到join buffer中。遍历被驱动表的所有记录与join buffer中的数据匹配重复上述过程知道驱动表的数据全部处理完毕 哈希连接hash join 原理哈希连接是一种高效的连接算法适用于·等值连接·核心思想是 构建阶段以较小的表为基准基于连接键构建哈希表 作为构建表探测阶段扫描较大的表探测表根据哈希值快速定位构建表中的匹配项 特点哈希连接使用于需要连接的大表之间的数据量大缺少索引、但连接条件是·等值条件·优点在等值连接上效率极高尤其适合无索引的大数据集缺点无法处理范围条件、不等式条件哈希表构建阶段对内存的依赖较大执行流程 哈希连接hash join是mysql8.0引入的一种高效的连接算法适用于大数据集的等值连接构建阶段将较小的表加载到内存中并基于连接构建哈希表探测阶段扫描较大的表根据连接键查找哈希表中的匹配记录 排序合并连接sort-merge join 原理排序合并连接通过·先对表的连接键进行排序·然后使用类似归并排序的方式合并两个表 为表A和表B基于连接键进行排序分别从两个排序后的表读取数据对比连接键合并匹配的记录对不满足条件的记录通过移动较小的一方指针解决 特点非常适合需要排序的大表或者需要进行范围条件连接的时候优点适用于范围条件和等值条件特别适合超大数据集缺点排序阶段可能会导致较高的I/O开销执行流程 排序合并连接sort-merge join是一种高效的连接算法特别适用于没有索引的大数据集和需要范围连接的场景。排序阶段对两个表的连接键进行排序合并阶段使用类似·归并排序·的方式依次扫描两个排序后的表 总结 SNLJ适合小表或者快速验证INLJ优选小表到大表且有索引的场景BNLJ适合无索引的场景HASH JOIN等值连接的高效利器SORT-MERGE JOIN格式和范围查询和排序场景 Q:为何大厂都不建议使用多表join 性能问题 计算复杂性多表关联需要进行复杂的计算如哈希连接、合并连接这些会增加查询的计算开销影响相应时间资源消耗join操作特别是在多表参与时会占用大量的内存和cpu资源尤其是当色剂大表或者需要大量数据进行join是索引依赖join性能很大程度上依赖于索引的错在和选择当索引不当或者没有搜因时查询性能会大幅度下降 可扩展性 水平扩展挑战在分布式数据库架构中join操作可能需要跨多个节点获取数据即跨节点join这往往会导致大量的数据传输增加系统负担难以分片复杂的join查询在分片数据中难以优化因为涉及多个分片的数据可能位于不同的物理节点上难以高效的进行 维护与复杂度 代码复杂性复杂的多表join查询往往导致sql语句难以理解和维护调试困难当数据错误或者查询性能不佳时复杂join查询更难以调试和定位问题 架构设计 领域模型割裂join通常需要深度了解不同表之间的关系容易导致领域模型设计上不一致微服务架构限制在微服务架构中数据通常是中心化和跨服务的join查询往往违反服务数据自治原则 替代策略 反范式化适当的冗余存储以减少join需求数据冗余在不同表中冗余存储一些公共字段避免频繁的join分而治之将复杂的查询拆分为多个简单查询在应用层进行数据组合预先计算通过ETL作业预先计算和存储结果使用nosql数据库设计生产系统数据时采用文档数据库或者键值存储一类的nosql数据库来支持复杂数据需求 17.NULL值引发的命案
命案1count/distinct数据丢失 count是mysql常用的统计函数当字段值为NULL时count(column)会忽略这些记录导致统计不准确 解决方案使用count(*)统计所有的记录包括字段值为NULL的行扩展避免使用count(常量因为他的行为与count(*)一致但可读性较差 当使用count(distinct column1, column2)时如果任意一个字段值为null即使另一列有不同的值查询结果也会忽略这些记录。 解决方案 避免字段值为null在建表时设置字段值非空并指定默认值使用代替函数通过IFNULL(column,default_value)将NULL转换为默认值。 命案2NULL对比结果为未知 在使用非等于查询或者!时NULL值的记录会被忽略解决方案显示处理NULL在查询条件中加入对NULL的判断 命案3NULL值运算都为NULL 在使用NULL值进行运算比如加减乘除拼接等最终结果都NULL 命案4聚合空指针异常 在使用聚合函数如SUM、AVG时如果字段值为NULL查询结果也会为NULL而不是预期为0这可能是导致程序在处理结果时出现空指针异常等。解决方案使用IFNULL函数将NULL转换成0 命案5GROUP BY/ORDER BY会统计NULL值 在使用group by与order by时不会剔除NULL会将NULL作为最小值解决方案使用is not null 剔除NULL记录 命案6NULL值导致索引失效 结论NULL值不一定会导致索引失效但会影响索引效率字段定义为NOT NULL时 查询IS NULL被任务是impossible where 不会走索引查询is not null 通常不走索引而是全表扫描如果查询只涉及索引字段覆盖索引场景is not null查询可以走索引 字段允许null时 查询条件 is null5.7之后可以走索引is not null通常不使用索引除非覆盖索引。null值分布占比会显著影响查询性能和索引使用 NULL值分布占比的影响 当NULL值占比较小时IS NULL查询更倾向于使用索引当非NULL值占比比较小时is not null 查询更倾向于使用索引 优化建议 合理的设计字段属性对不会存储NULL值的字段建议定义为NOT NULL减少NULL值处理开销若字段需要存储NULL值确保查询条件能够充分利用索引定期更新统计信息使用analyze table 更新表的统计信息确保优化器成本估算更准确 小结 字段中的NULL值赞比对索引使用有重要影响 当NULL值占比小IS NULL查询走索引IS NOT NULL查询可能全表扫描NULL值占比大时IS NOT NULL查询走索引IS NULL查询可能全表扫描 优化器的成本计算可能出现误判 统计信息不准确时优化器可能错误的选择使用索引。定期更新统计信息避免误判 回表操作可能导致性能下降 二级索引扫描行数过多时回表成本可能高于全表扫描 总结 表设计时尽量避免NULL值设置字段的默认值为空字符串或者0查询时显示处理NULL使用IF NULL或者COALESCE函数将NULL转换成默认值在查询条件中加入IS NULL或者IS NOT NULL优先使用ISNULL(COLUMN)判断NULL值提升代码可读性和执行效率
18.mysql备份的方式
备份数据三种方式热备份、温备份、冷备份 热备份当数据库进行备份时数据库的读写操作不受影响温备份当数据库备份时数据库的读操作可以执行但是不能执行写操作冷备份当数据库备份时数据库不能进行读写操作需要下线处理innodb支持三种备份方式而myisam不支持热备份 数据库备份两种方式物理备份、逻辑备份 物理备份通过tar、cp等命令直接打包复制数据库的书库文件达到备份效果逻辑备份通过特定工具从数据库导出数据并另存备份逻辑备份丢失数据的精度 备份策略 一般备份需要备份数据、二进制日志 innodb事务日志、代码存储过程、存储函数、触发器、事件调度器、服务器配置文件直接cp,tar复制数据库文件数据量小直接复制数据库文件mysqldump:数据量一般先使用mysqldump对数据库进行完全备份xtrabackup:数据量很大而又不过分影响运行使用xtarback进行完全备份后定期使用xtrabackup进行增量备份或者差异备份 备份工具 mysqldump逻辑备份工具适合所有的存储引擎支持温备、完全备份、部分备份对于innodb存储引擎支持热备cptar等归档复制工具物理备份工具使用与所有的存储引擎冷备、完全备份和部分备份lvm2 snapshot几乎热备借助文件系统管理工具进行备份mysqlhot copy名不副实的一个工具几乎冷备仅支持myisam存储引擎xtrabackup:一款非常强大的innodb/etardb热备工具支持完全备份、增量备份
19.数据库在线迁移方案 20.对大表的查询为何不会把内存撑爆
假设我们要对一个300G的innodb表A执行一个全表扫描。当未设置查询条件select * from A此时innodb的郑航数据保存在主键索引上的所以全表扫描实际是直接扫描表A的主键索引由于没有设置条件所以查到的每一行数据都可以直接放到结果集返回给调用方。执行逻辑 获取一行写到net_buffer。这块内存的大小由参数net_buffer_length定义默认为16K重复后去行知道net_buffer写满调用网络接口发出去如果发送成功就清空net_buffer然后继续取下一行数据并写入到net_buffer。如果发送函数返回eagain或者wsaewouldblock就标识本地网络栈socket send buffer写满了进入等待知道网路栈重新可写在继续发送。 执行流程 一个查询在发送过程中占用的mysql内部内存最大就是net_buffer_length这么大并不会达到300Gsocket send buffer 也可能达到300G默认定义/proc/sys/net/core/wmem_default如果socket send buffer 被写满就会暂停读数据的流程也就是说mysql是边读边发的这就说明若客户端接收比较慢会导致mysql服务端由于结果发不出去这个事务的执行时间边长但是不会导致数据库内存打爆。
21.加密后的数据如何支持模糊查询
内存解密数据量小时简单方便但数据量大时可能会导致OOM映射查询建立两个表的映射关系加密表通过非加密表进行数据查询加密函数通过AES DES等方式对字段解密后查询分片查询根据加密算法的策略对字段值进行分片拆分后查询 22.mysql8的索引跳跃扫描
mysql中的索引跳跃扫描skip scan是一种优化策略为了提高特定条件下对符合索引的利用效率。 以staff表为例表中有id、dept、job三个字段并对dept和job进行复合索引 iddeptjob1软件it2软件it3管理am4管理bm 跳跃扫描的优化实现 分区扫描 将索引按照dept的唯一值进行分割每个分区相当于不同的部门。 应用条件 对每个dept分区中的position行进行扫描即跳跃扫描仅搜索jobit的员工 跳过无关分区 由于jobit的限定我们只在每个dept的分区中寻找匹配的job而不是对整个表进行扫描 实际效果 虽然mysql索引跳跃扫描概率上并不是独立标识的特性但此类索引优化策略在新版的mysql中能改进索引条件推送和查询优化器来实现。他潜在的减少了读取和滤除不必要的数据开销。
