青岛专用网站制作,网站页尾信息,企业的建站方式,网站建设竞争性磋商文件1、关系型和非关系型数据库的区别#xff1f; 关系型数据库的优点 容易理解#xff0c;因为它采用了关系模型来组织数据。 可以保持数据的一致性。 数据更新的开销比较小。 支持复杂查询#xff08;带 where 子句的查询#xff09; 非关系型数据库#xff08;NOSQL#x…1、关系型和非关系型数据库的区别 关系型数据库的优点 容易理解因为它采用了关系模型来组织数据。 可以保持数据的一致性。 数据更新的开销比较小。 支持复杂查询带 where 子句的查询 非关系型数据库NOSQL的优点 无需经过 SQL 层的解析读写效率高。 基于键值对读写性能很高易于扩展 可以支持多种类型数据的存储如图片文档等等。 扩展可分为内存型数据库以及文档型数据库比如 RedisMongoDBHBase 等适合场景数据量大高可用的日志系统/地理位置存储系统。
2、详细说一下一条 MySQL 语句执行的步骤 Server 层按顺序执行 SQL 的步骤为 客户端请求 - 连接器验证用户身份给予权限 查询缓存存在缓存则直接返回不存在则执行后续操作 分析器对 SQL 进行词法分析和语法分析操作 优化器主要对执行的 SQL 优化选择最优的执行方案方法 执行器执行时会先看用户是否有执行权限有才去使用这个引擎提供的接口- 去引擎层获取数据返回如果开启查询缓存则会缓存查询结果
3、MySQL 使用索引的原因 根本原因 索引的出现就是为了提高数据查询的效率就像书的目录一样。 对于数据库的表而言索引其实就是它的“目录”。 扩展 创建唯一性索引可以保证数据库表中每一行数据的唯一性。 帮助引擎层避免排序和临时表 将随机 IO 变为顺序 IO加速表和表之间的连接。
4、索引的三种常见底层数据结构以及优缺点 三种常见的索引底层数据结构分别是哈希表、有序数组和搜索树。 哈希表这种适用于等值查询的场景比如 memcached 以及其它一些 NoSQL 引擎不适合范围查询。 有序数组索引只适用于静态存储引擎等值和范围查询性能好但更新数据成本高。 N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。 扩展以 InnoDB 的一个整数字段索引为例这个 N 差不多是 1200。棵树高是 4 的时候就可以存 1200 的 3 次方个值这已经 17 亿了。考虑到树根的数据块总是在内存中的一个 10 亿行的表上一个整数字段的索引查找一个值最多只需要访问 3 次磁盘。其实树的第二层也有很大概率在内存中那么访问磁盘的平均次数就更少了。
5、索引的常见类型以及它是如何发挥作用的 根据叶子节点的内容索引类型分为主键索引和非主键索引。 主键索引的叶子节点存的整行数据在InnoDB里也被称为聚簇索引。 非主键索引叶子节点存的主键的值在InnoDB里也被称为二级索引。 备注但我们在开发的过程中往往会根据业务需要在不同的字段上建立索引这些索引就是二级索引
6、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么 InnoDB 存储引擎B 树索引的叶子节点保存数据本身其数据文件本身就是索引文件。 MyISAM 存储引擎B 树索引的叶子节点保存数据的物理地址叶节点的 data 域存放的是数据记录的地址索引文件和数据文件是分离的。
7、InnoDB 为什么设计 B 树索引 两个考虑因素 InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。 CPU 将磁盘上的数据加载到内存中需要花费大量时间。 为什么选择 B 树 哈希索引虽然能提供O1复杂度查询但对范围查询和排序却无法很好的支持最终会导致全表扫描。 B 树能够在非叶子节点存储数据但会导致在查询连续数据可能带来更多的随机 IO。 而 B 树的所有叶节点可以通过指针来相互连接减少顺序遍历带来的随机 IO。 普通索引还是唯一索引 由于唯一索引用不上 change buffer 的优化机制因此如果业务可以接受从性能角度出发建议你优先考虑非唯一索引。
8、什么是覆盖索引和索引下推 覆盖索引 在某个查询里面索引 k 已经“覆盖了”我们的查询需求称为覆盖索引。 覆盖索引可以减少树的搜索次数显著提升查询性能所以使用覆盖索引是一个常用的性能优化手段。 索引下推 MySQL 5.6 引入的索引下推优化index condition pushdown) 可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。
9、哪些操作会导致索引失效 对索引使用左或者左右模糊匹配也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。原因在于查询的结果可能是多个不知道从哪个索引值开始比较于是就只能通过全表扫描的方式来查询。 对索引进行函数/对索引进行表达式计算因为索引保持的是索引字段的原始值而不是经过函数计算的值自然就没办法走索引。 对索引进行隐式转换相当于使用了新函数。备注cast int to char) WHERE 子句中的 OR语句只要有条件列不是索引列就会进行全表扫描。(备注sql优化
10、字符串加索引 直接创建完整索引这样可能会比较占用空间。 创建前缀索引节省空间但会增加查询扫描次数并且不能使用覆盖索引。前缀索引不能使用覆盖索引 倒序存储再创建前缀索引用于绕过字符串本身前缀的区分度不够的问题。 创建 hash 字段索引查询性能稳定有额外的存储和计算消耗跟第三种方式一样都不支持范围扫描。
日志相关 11、MySQL 的 change buffer 是什么 当需要更新一个数据页时如果数据页在内存中就直接更新而如果这个数据页还没有在内存中的话在不影响数据一致性的前提下InnoDB 会将这些更新操作缓存在 change buffer 中备注存储在内存中。 这样就不需要从磁盘中读入这个数据页了在下次查询需要访问这个数据页的时候将数据页读入内存然后执行 change buffer 中与这个页有关的操作备注MySQL的merge过程。通过这种方式就能保证这个数据逻辑的正确性。 注意唯一索引的更新就不能使用 change buffer实际上也只有普通索引可以使用。 适用场景
对于写多读少的业务来说页面在写完以后马上被访问到的概率比较小此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。反过来假设一个业务的更新模式是写入之后马上会做查询那么即使满足了条件将更新先记录在 change buffer但之后由于马上要访问这个数据页会立即触发 merge 过程。这样随机访问 IO 的次数不会减少反而增加了 change buffer 的维护代价。
12、MySQL 是如何判断一行扫描数的 MySQL 在真正开始执行语句之前并不能精确地知道满足这个条件的记录有多少条。 而只能根据统计信息来估算记录数。这个统计信息就是索引的区分度。
13、MySQL 的 redo log 和 binlog 区别
14、为什么需要 redo log redo log 主要用于 MySQL 异常重启后的一种数据恢复手段确保了数据的一致性。 其实是为了配合 MySQL 的 WAL 机制在日志之前写好。因为 MySQL 进行更新操作为了能够快速响应所以采用了异步写回磁盘的技术写入内存后就返回。但是这样会存在 crash后 内存数据丢失的隐患而 redo log 具备 crash safe 的能力。
15、为什么 redo log 具有 crash-safe 的能力是 binlog 无法替代的 第一点redo log 可确保 innoDB 判断哪些数据已经刷盘哪些数据还没有 redo log 和 binlog 有一个很大的区别就是一个是循环写一个是追加写。也就是说 redo log 只会记录未刷盘的日志已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志保存的是全量的日志。 当数据库 crash 后想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时binlog 是无法恢复的。虽然 binlog 拥有全量的日志但没有一个标志让 innoDB 判断哪些数据已经刷盘哪些数据还没有。 但 redo log 不一样只要刷入磁盘的数据都会从 redo log 中抹掉因为是循环写数据库重启后直接把 redo log 中的数据都恢复至内存就可以了。 第二点如果 redo log 写入失败说明此次操作失败事务也不可能提交 redo log 每次更新操作完成后就一定会写入日志如果写入失败说明此次操作失败事务也不可能提交。 redo log 内部结构是基于页的记录了这个页的字段值变化只要crash后读取redo log进行重放就可以恢复数据。 这就是为什么 redo log 具有 crash-safe 的能力而 binlog 不具备。
16、当数据库 crash 后如何恢复未刷盘的数据到内存中 根据 redo log 和 binlog 的两阶段提交未持久化的数据分为几种情况 change buffer 写入redo log 虽然做了 fsync 但未 commitbinlog 未 fsync 到磁盘这部分数据丢失。 change buffer 写入redo log fsync 未 commitbinlog 已经 fsync 到磁盘先从 binlog 恢复 redo log再从 redo log 恢复 change buffer。 change buffer 写入redo log 和 binlog 都已经 fsync直接从 redo log 里恢复。
17、redo log 写入方式 redo log包括两部分内容分别是内存中的日志缓冲(redo log buffer)和磁盘上的日志文件(redo log file)。 MySQL 每执行一条 DML 语句会先把记录写入 redo log buffer用户空间 再保存到内核空间的缓冲区 OS-buffer 中后续某个时间点再一次性将多个操作记录写到 redo log file刷盘 。这种先写日志再写磁盘的技术就是WAL。
可以发现redo log buffer写入到redo log file是经过OS buffer中转的。其实可以通过参数 innodb_flush_log_at_trx_commit进行配置参数值含义如下 0称为延迟写事务提交时不会将redo log buffer中日志写入到OS buffer而是每秒写入OS buffer并调用写入到redo log file中。 1称为实时写实时刷”事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。 2称为实时写延迟刷。每次事务提交写入到OS buffer然后是每秒将日志写入到redo log file。
18、redo log 的执行流程? 我们来看下Redo log的执行流程假设执行的 SQL 如下 update T set a 1 where id 666
MySQL 客户端将请求语句 update T set a 1 where id 666发往 MySQL Server 层。 MySQL Server 层接收到 SQL 请求后对其进行分析、优化、执行等处理工作将生成的 SQL 执行计划发到 InnoDB 存储引擎层执行。 InnoDB 存储引擎层将a修改为1的这个操作记录到内存中。 记录到内存以后会修改 redo log 的记录会在添加一行记录其内容是需要在哪个数据页上做什么修改。 此后将事务的状态设置为 prepare 说明已经准备好提交事务了。 等到 MySQL Server 层处理完事务以后会将事务的状态设置为 commit也就是提交该事务。 在收到事务提交的请求以后redo log 会把刚才写入内存中的操作记录写入到磁盘中从而完成整个日志的记录过程。
19、binlog 的概念是什么起到什么作用 可以保证 crash-safe 吗? binlog 是归档日志属于 MySQL Server 层的日志。可以实现主从复制和数据恢复两个作用。 当需要恢复数据时可以取出某个时间范围内的 binlog 进行重放恢复。 但是 binlog 不可以做 crash safe因为 crash 之前binlog 可能没有写入完全 MySQL 就挂了。所以需要配合 redo log 才可以进行 crash safe。
20、什么是两阶段提交 MySQL 将 redo log 的写入拆成了两个步骤prepare 和 commit中间再穿插写入binlog这就是两阶段提交。 备注两阶段提交的对象时redolog)
而两阶段提交就是让这两个状态保持逻辑上的一致。 redolog 用于恢复主机故障时的未更新的物理数据binlog 用于备份操作。 两者本身就是两个独立的个体要想保持一致就必须使用分布式事务的解决方案来处理。 为什么需要两阶段提交呢? 如果不用两阶段提交的话可能会出现这样情况 先写 redo logcrash 后 bin log 备份恢复时少了一次更新与当前数据不一致。 先写 bin logcrash 后由于 redo log 没写入事务无效所以后续 bin log 备份恢复时数据不一致。 两阶段提交就是为了保证 redo log 和 binlog 数据的安全一致性。只有在这两个日志文件逻辑上高度一致了才能放心的使用。 在恢复数据时redolog 状态为 commit 则说明 binlog 也成功直接恢复数据如果 redolog 是 prepare则需要查询对应的 binlog事务是否成功决定是回滚还是执行。
21、MySQL 怎么知道 binlog 是完整的? 一个事务的 binlog 是有完整格式的 statement 格式的 binlog最后会有 COMMIT row 格式的 binlog最后会有一个 XID event。
22、什么是 WAL 技术有什么优点 WAL中文全称是 Write-Ahead Logging它的关键点就是日志先写内存再写磁盘。 MySQL 执行更新操作后在真正把数据写入到磁盘前先记录日志。 好处是不用每一次操作都实时把数据写盘就算 crash 后也可以通过redo log 恢复所以能够实现快速响应 SQL 语句。
23、binlog 日志的三种格式 binlog 日志有三种格式 Statement基于SQL语句的复制((statement-based replication,SBR)) Row基于行的复制。(row-based replication,RBR) Mixed混合模式复制。(mixed-based replication,MBR) Statement格式 每一条会修改数据的 SQL 都会记录在 binlog 中 优点不需要记录每一行的变化减少了binlog日志量节约了IO提高性能。 缺点由于记录的只是执行语句为了这些语句能在备库上正确运行还必须记录每条语句在执行的时候的一些相关信息以保证所有语句能在备库得到和在主库端执行时候相同的结果。 Row格式 不记录 SQL 语句上下文相关信息仅保存哪条记录被修改。 优点binlog 中可以不记录执行的 SQL 语句的上下文相关的信息仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。 缺点:可能会产生大量的日志内容。 Mixed格式 实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog如一些函数statement 无法完成主从复制的操作则采用 row 格式保存 binlogMySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。
24、redo log日志格式
redo log buffer (内存中)是由首尾相连的四个文件组成的它们分别是ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。 write pos 是当前记录的位置一边写一边后移写到第 3 号文件末尾后就回到 0 号文件开头。 checkpoint 是当前要擦除的位置也是往后推移并且循环的擦除记录前要把记录更新到数据文件。 write pos 和 checkpoint 之间的是“粉板”上还空着的部分可以用来记录新的操作。 如果 write pos 追上 checkpoint表示“粉板”满了这时候不能再执行新的更新得停下来先擦掉一些记录把 checkpoint 推进一下。 有了 redo log当数据库发生宕机重启后可通过 redo log将未落盘的数据check point之后的数据恢复保证已经提交的事务记录不会丢失这种能力称为crash-safe。
25、原本可以执行得很快的 SQL 语句执行速度却比预期的慢很多原因是什么如何解决 原因从大到小可分为四种情况 MySQL 数据库本身被堵住了比如系统或网络资源不够。 SQL 语句被堵住了比如表锁行锁等导致存储引擎不执行对应的 SQL 语句。 确实是索引使用不当没有走索引。 表中数据的特点导致的走了索引但回表次数庞大。 解决 考虑采用 force index 强行选择一个索引 考虑修改语句引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” 语义的逻辑是相同的。 第三种方法是在有些场景下可以新建一个更合适的索引来提供给优化器做选择或删掉误用的索引。 如果确定是索引根本没必要可以考虑删除索引。 (备注索引优化)
26、InnoDB 数据页结构 一个数据页大致划分七个部分 File Header表示页的一些通用信息占固定的38字节。 page Header表示数据页专有信息占固定的56字节。 inimumSupermum两个虚拟的伪记录分别表示页中的最小记录和最大记录占固定的26字节。 User Records真正存储我们插入的数据大小不固定。 Free Space页中尚未使用的部分大小不固定。 Page Directory页中某些记录的相对位置也就是各个槽对应的记录在页面中的地址偏移量。 File Trailer用于检验页是否完整占固定大小 8 字节。
数据相关 27、MySQL 是如何保证数据不丢失的 只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据 在恢复数据时redolog 状态为 commit 则说明 binlog 也成功直接恢复数据如果 redolog 是 prepare则需要查询对应的 binlog事务是否成功决定是回滚还是执行。
28、误删数据怎么办 DBA 的最核心的工作就是保证数据的完整性先要做好预防预防的话大概是通过这几个点 权限控制与分配(数据库和服务器权限) 制作操作规范 定期给开发进行培训 搭建延迟备库 做好 SQL 审计只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核 做好备份。备份的话又分为两个点 (1)如果数据量比较大用物理备份 xtrabackup。定期对数据库进行全量备份也可以做增量备份。 (2)如果数据量较少用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的 如果发生了数据删除的操作又可以从以下几个点来恢复: DML 误操作语句造成数据不完整或者丢失。可以通过 flashback美团的 myflash也是一个不错的工具本质都差不多 都是先解析 binlog event然后在进行反转。把 delete 反转为insertinsert 反转为 deleteupdate前后 image 对调。 所以必须设置binlog_formatrow 和 binlog_row_imagefull切记恢复数据的时候应该先恢复到临时的实例然后在恢复回主库上。 DDL语句误操作(truncate和drop)由于DDL语句不管 binlog_format 是 row 还是 statement 在 binlog 里都只记录语句不记录 image 所以恢复起来相对要麻烦得多。 只能通过全量备份应用 binlog 的方式来恢复数据。一旦数据量比较大那么恢复时间就特别长 rm 删除使用备份跨机房或者最好是跨城市保存。
29、drop、truncate 和 delete 的区别 DELETE 语句执行删除的过程是每次从表中删除一行并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。 TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。 drop语句将表所占用的空间全释放掉。 在速度上一般来说drop truncate delete。 如果想删除部分数据用 delete注意带上 where 子句回滚段要足够大 如果想删除表当然用 drop如果想保留表而将所有数据删除如果和事务无关用 truncate 即可 如果和事务有关或者想触发 trigger还是用 delete如果是整理表内部的碎片可以用 truncate 跟上 reuse stroage再重新导入/插入数据。
30、在 MySQL 中有两个 kill 命令 一个是 kill query 线程 id表示终止这个线程中正在执行的语句 一个是 kill connection 线程 id这里 connection 可缺省表示断开这个线程的连接 kill 不掉的原因 kill命令被堵了还没到位 kill命令到位了但是没被立刻触发 kill命令被触发了但执行完也需要时间
31、如何理解 MySQL 的边读边发 如果客户端接受慢会导致 MySQL 服务端由于结果发不出去这个事务的执行时间会很长。 服务端并不需要保存一个完整的结果集取数据和发数据的流程都是通过一个 next_buffer 来操作的。 内存的数据页都是在 Buffer_Pool中操作的。 InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法使用链表实现实现上按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。
32、MySQL 的大表查询为什么不会爆内存 由于 MySQL 是边读变发因此对于数据量很大的查询结果来说不会再 server 端保存完整的结果集所以如果客户端读结果不及时会堵住 MySQL 的查询过程但是不会把内存打爆。 InnoDB 引擎内部由于有淘汰策略InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法使用链表实现实现上按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描影响也能做到可控制。
33、MySQL 临时表的用法和特性 只对当前session可见。 可以与普通表重名。 增删改查用的是临时表。 show tables 不显示普通表。 在实际应用中临时表一般用于处理比较复杂的计算逻辑。 由于临时表是每个线程自己可见的所以不需要考虑多个线程执行同一个处理时临时表的重名问题在线程退出的时候临时表会自动删除。
34、MySQL 存储引擎介绍InnoDB、MyISAM、MEMORY InnoDB 是事务型数据库的首选引擎支持事务安全表 (ACID)支持行锁定和外键。MySQL5.5.5 之后InnoDB 作为默认存储引擎 MyISAM 基于 ISAM 的存储引擎并对其进行扩展。它是在 Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM 拥有较高的插入、查询速度但不支持事务。在 MySQL5.5.5 之前的版本中MyISAM 是默认存储引擎 MEMORY 存储引擎将表中的数据存储到内存中为查询和引用其他表数据提供快速访问。
35、都说 InnoDB 好那还要不要使用 MEMORY 引擎 内存表就是使用 memory 引擎创建的表 为什么我不建议你在生产环境上使用内存表。这里的原因主要包括两个方面锁粒度问题数据持久化问题。 由于重启会丢数据如果一个备库重启会导致主备同步线程停止如果主库跟这个备库是双 M aster架构还可能导致主库的内存表数据被删掉。
36、如果数据库误操作, 如何执行数据恢复? 数据库在某个时候误操作就可以找到距离误操作最近的时间节点的bin log重放到临时数据库里然后选择误删的数据节点恢复到线上数据库。
主从备份相关 37、MySQL 是如何保证主备同步 主备关系的建立 一开始创建主备关系的时候是由备库指定的比如基于位点的主备关系备库说“我要从binlog文件A的位置P”开始同步主库就从这个指定的位置开始往后发。 而主备关系搭建之后是主库决定要发给数据给备库的所以主库有新的日志也会发给备库。 MySQL 主备切换流程 客户端读写都是直接访问A而节点B是备库只要将A的更新都同步过来到本地执行就可以保证数据是相同的。 当需要切换的时候就把节点换一下A的节点B的备库 一个事务完整的同步过程 备库B和主库A建立来了长链接主库A内部专门线程用于维护了这个长链接。 在备库B上通过change master命令设置主库A的IP 端口 用户名 密码以及从哪个位置开始请求binlog包括文件名和日志偏移量 在备库B上执行start-slave命令备库会启动两个线程io_thread和sql_thread分别负责建立连接和读取中转日志进行解析执行 备库读取主库传过来的binlog文件备库收到文件写到本地成为中转日志 后来由于多线程复制方案的引入sql_thread演化成了多个线程。
38、什么是主备延迟 主库和备库在执行同一个事务的时候出现时间差的问题主要原因有 有些部署条件下备库所在机器的性能要比主库性能差。 备库的压力较大。 大事务一个主库上语句执行10分钟那么这个事务可能会导致从库延迟10分钟。
39、为什么要有多线程复制策略 因为单线程复制的能力全面低于多线程复制对于更新压力较大的主库备库可能是一直追不上主库的带来的现象就是备库上seconds_behind_master值越来越大。 在实际应用中建议使用可靠性优先策略减少主备延迟提升系统可用性尽量减少大事务操作把大事务拆分小事务。
40、MySQL 的并行策略有哪些 按表分发策略 如果两个事务更新不同的表它们就可以并行。因为数据是存储在表里的所以按表分发可以保证两个 worker 不会更新同一行。 缺点如果碰到热点表比如所有的更新事务都会涉及到某一个表的时候所有事务都会被分配到同一个 worker 中就变成单线程复制了。 按行分发策略 如果两个事务没有更新相同的行它们在备库上可以并行。 显然这个模式要求 binlog 格式必须是 row。 缺点相比于按表并行分发策略按行并行策略在决定线程分发的时候需要消耗更多的计算资源。 41、MySQL的一主一备和一主多从有什么区别 在一主一备的双 M 架构里主备切换只需要把客户端流量切到备库 而在一主多从架构里主备切换除了要把客户端流量切到备库外还需要把从库接到新主库上。
42、主库出问题如何解决? 基于位点的主备切换存在找同步位点这个问题 MySQL 5.6 版本引入了 GTID彻底解决了这个困难。那么GTID 到底是什么意思又是如何解决找同步位点这个问题呢 GTID全局事务 ID是一个事务在提交的时候生成的是这个事务的唯一标识它由两部分组成格式是GTIDserver_uuid:gno 每个 MySQL 实例都维护了一个 GTID 集合用来对应“这个实例执行过的所有事务”。 在基于 GTID 的主备关系里系统认为只要建立主备关系就必须保证主库发给备库的日志是完整的。因此如果实例 B 需要的日志已经不存在A’就拒绝把日志发给 B。
43、MySQL 读写分离涉及到过期读问题的几种解决方案? 强制走主库方案 sleep 方案 判断主备无延迟方案 配合 semi-sync 方案半同步 等主库位点方案 GTID 方案。 实际生产中先客户端对请求做分类区分哪些请求可以接受过期读而哪些请求完全不能接受过期读然后对于不能接受过期读的语句再使用等 GTID 或等位点的方案。
44、MySQL的并发链接和并发查询有什么区别 在执行show processlist的结果里看到了几千个连接指的是并发连接。而当前正在执行的语句才是并发查询。 并发连接数多影响的是内存并发查询太高对CPU不利。一个机器的CPU核数有限线程全冲进来上下文切换的成本就会太高。 所以需要设置参数innodb_thread_concurrency 用来限制线程数当线程数达到该参数InnoDB就会认为线程数用完了会阻止其他语句进入引擎执行。
性能相关 45、短时间提高 MySQL 性能的方法 第一种方法先处理掉那些占着连接但是不工作的线程。或者再考虑断开事务内空闲太久的连接。kill connection id 第二种方法减少连接过程的消耗慢查询性能问题在 MySQL 中会引发性能问题的慢查询大体有以下三种可能索引没有设计好SQL 语句没写好MySQL 选错了索引force index。
46、为什么 MySQL 自增主键 ID 不连续 唯一键冲突 事务回滚 自增主键的批量申请 深层次原因是MySQL 不判断自增主键是否存在从而减少加锁的时间范围和粒度这样能保持更高的性能确保自增主键不能回退所以才有自增主键不连续。 自增主键怎么做到唯一性自增值加1来通过自增锁控制并发。
47、InnoDB 为什么要用自增 ID 作为主键 自增主键的插入模式符合递增插入每次都是追加操作不涉及挪动记录也不会触发叶子节点的分裂。 每次插入新的记录就会顺序添加到当前索引节点的后续位置当一页写满就会自动开辟一个新的页。 而有业务逻辑的字段做主键不容易保证有序插入由于每次插入主键的值近似于随机 因此每次新纪录都要被插到现有索引页得中间某个位置 频繁的移动、分页操作造成了大量的碎片得到了不够紧凑的索引结构写数据成本较高。
48、如何最快的复制一张表 为了避免对源表加读锁更稳妥的方案是先将数据写到外部文本文件然后再写回目标表 一种方法是使用 mysqldump 命令将数据导出成一组 INSERT 语句 另一种方法是直接将结果导出成.csv 文件。 MySQL 提供语法用来将查询结果导出到服务端本地目录select * from db1.t where a900 into outfile ‘/server_tmp/t.csv’;得到.csv 导出文件后你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中load data infile ‘/server_tmp/t.csv’ into table db2.t; 物理拷贝在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法可以通过导出 导入表空间的方式实现物理拷贝表的功能。
49、grant 和 flush privileges语句 grant语句会同时修改数据表和内存判断权限的时候使用的内存数据因此规范使用是不需要加上 flush privileges 语句。 flush privileges 语句本身会用数据表的数据重建一份内存权限数据所以在权限数据可能存在不一致的情况下再使用。
50、要不要使用分区表 分区并不是越细越好。实际上单表或者单分区的数据一千万行只要没有特别大的索引对于现在的硬件能力来说都已经是小表了。 分区也不要提前预留太多在使用之前预先创建即可。比如如果是按月分区每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区要及时的 drop 掉。
51、join 用法 使用 left join 左边的表不一定是驱动表 如果需要 left join 的语义就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断必须都写在 on 里面 标准的 group by 语句是需要在 select 部分加一个聚合函数比如select a,count(*) from t group by a order by null;
52、MySQL 有哪些自增ID各自场景是什么 表的自增 ID 达到上限之后在申请值不会变化进而导致联系插入数据的时候报主键冲突错误。 row_id 达到上限之后归 0 在重新递增如果出现相同的 row_id 后写的数据会覆盖之前的数据。 Xid 只需要不在同一个 binlog 文件出现重复值即可理论上会出现重复值但概率极小可忽略不计。 InnoDB 的 max_trx_id 递增值每次 MySQL 重启会保存起来。 Xid 是由 server 层维护的。InnoDB 内部使用 Xid就是为了能够在 InnoDB 事务和 server 之间做关联。但是InnoDB 自己的 trx_id是另外维护的。 thread_id 是我们使用中最常见的而且也是处理得最好的一个自增 id 逻辑了。使用了insert_unique算法
53、Xid 在 MySQL 内部是怎么生成的呢 MySQL 内部维护了一个全局变量 global_query_id每次执行语句包括select语句的时候将它赋值给 Query_id然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。 而 global_query_id 是一个纯内存变量重启之后就清零了。所以你就知道了在同一个数据库实例中不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件这就保证了同一个 binlog 文件里Xid 一定是惟一的。
锁相关 54、说一下 MySQL 的锁 MySQL 在 server 层 和 存储引擎层 都运用了大量的锁 MySQL server 层需要讲两种锁第一种是MDL(metadata lock) 元数据锁第二种则 Table Lock 表锁。 MDL 又名元数据锁那么什么是元数据呢任何描述数据库的内容就是元数据比如我们的表结构、库结构等都是元数据。那为什么需要 MDL 呢 主要解决两个问题事务隔离问题数据复制问题 InnoDB 有五种表级锁IS意向读锁IX意向写锁S读X写AUTO-INC 在对表进行select/insert/delete/update语句时候不会加表级锁 IS和IX的作用是为了判断表中是否有已经被加锁的记录 自增主键的保障就是有 AUTO-INC 锁是语句级别的为表的某个列添加 AUTO_INCREMENT 属性之后在插⼊记录时可以不指定该列的值系统会⾃动为它赋上单调递增的值。 InnoDB 4 种行级锁 RecordLock记录锁 GapLock间隙锁解决幻读前一次查询不存在的东西在下一次查询出现了其实就是事务A中的两次查询之间事务B执行插入操作被事务A感知了 Next-KeyLock锁住某条记录又想阻止其它事务在改记录前面的间隙插入新纪录 InsertIntentionLock插入意向锁;如果插入到同一行间隙中的多个事务未插入到间隙内的同一位置则无须等待 行锁和表锁的抉择 全表扫描用行级锁 备注全表扫描锁表容易冲突行级锁降低冲突
55、什么是幻读 值在同一个事务中存在前后两次查询同一个范围的数据第二次看到了第一次没有查询到的数据。 幻读出现的场景 事务的隔离级别是可重复读且是当前读。 幻读指新插入的行。 幻读带来的问题 对行锁语义的破坏 破坏了数据一致性 解决 加间隙锁锁住行与行之间的间隙阻塞新插入的操作。 带来的问题降低并发度可能导致死锁。
其它为什么系列 56、为什么 MySQL 会抖一下 脏页会被后台线程自动 flush也会由于数据页淘汰而触发 flush而刷脏页的过程由于会占用资源可能会让你的更新和查询语句的响应时间长一些。
57、为什么删除了表表文件的大小还是没变 数据项删除之后 InnoDB 某个页 page A 会被标记为可复用。 delete 命令把整个表的数据删除结果就是所有的数据页都会被标记为可复用。但是磁盘上文件不会变小。 经过大量增删改的表都是可能是存在空洞的。这些空洞也占空间所以如果能够把这些空洞去掉就能达到收缩表空间的目的。 重建表就可以达到这样的目的。可以使用 alter table A engineInnoDB 命令来重建表。
58、count()实现方式以及各种 count 对比 对于 count(主键 id) 来说InnoDB 引擎会遍历整张表把每一行的 id 值都取出来返回给 server 层。server 层拿到 id 后判断是不可能为空的就按行累加。 对于 count(1) 来说InnoDB 引擎遍历整张表但不取值。server 层对于返回的每一行放一个数字“1”进去判断是不可能为空的按行累加。单看这两个用法的差别的话你能对比出来count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行以及拷贝字段值的操作。 对于 count(字段) 来说如果这个“字段”是定义为 not null 的话一行行地从记录里面读出这个字段判断不能为 null按行累加如果这个“字段”定义允许为 null那么执行的时候判断到有可能是 null还要把值取出来再判断一下不是 null 才累加。也就是前面的第一条原则server 层要什么字段InnoDB 就返回什么字段。 但是 count * 是例外并不会把全部字段取出来而是专门做了优化不取值。count() 肯定不是 null按行累加。 所以结论是按照效率排序的话count(字段)count(主键 id)count(1)≈count(※)所以建议尽量使用 count()。 备注count()有问题吧会进行全表扫描
59、order by 排序内部原理 MySQL 会为每个线程分配一个内存sort-buffer用于排序该内存大小为 sort_buffer_size 如果排序的数据量小于 sort_buffer_size排序就会在内存中完成 内部排序分为两种 全字段排序到索引树上找到满足条件的主键ID根据主键ID去取出数据放到sort_buffer然后进行快速排序 rowid排序通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据 如果数据量很大内存中无法存下这么多就会使用磁盘临时文件来辅助排序称为外部排序 外部排序MySQL会分为好几份单独的临时文件来存放排序后的数据一般是磁盘文件中进行归并然后将这些文件合并成一个大文件
60、如何高效的使用 MySQL 显式随机消息 随机取出 Y1,Y2,Y3之后算出Ymax,Ymin 得到id集后算出Y1、Y2、Y3对应的三个id 最后 select * from t where id in (id1, id2, id3) 这样扫描的行数应该是CYmax3 mysql select count(*) into C from t; set Y1 floor(C * rand()); set Y2 floor(C * rand()); set Y3 floor(C * rand()); Ymax max(Y1,Y2,Y3) Ymin min(Y1,Y2,Y3) select id from t limit Ymin(Ymax - Ymin)
