石家庄住房城乡建设厅网站,做一个官网需要多少钱,山西建设网站公司,茶叶手机网站建设基础
详细说一下一条 MySQL 语句执行的步骤 Server 层按顺序执行 SQL 的步骤为#xff1a;
客户端请求 - 连接器#xff08;验证用户身份#xff0c;给予权限#xff09; 查询缓存#xff08;存在缓存则直接返回#xff0c;不存在则执行后续操作#xff09; 分析器…基础
详细说一下一条 MySQL 语句执行的步骤 Server 层按顺序执行 SQL 的步骤为
客户端请求 - 连接器验证用户身份给予权限 查询缓存存在缓存则直接返回不存在则执行后续操作 分析器对 SQL 进行词法分析和语法分析操作 优化器主要对执行的 SQL 优化选择最优的执行方案方法 执行器执行时会先看用户是否有执行权限有才去使用这个引擎提供的接口- 去引擎层获取数据返回如果开启查询缓存则会缓存查询结果 引擎
MyISAM索引与InnoDB索引的区别 InnoDB索引是聚簇索引MyISAM索引是非聚簇索引。 InnoDB的主键索引的叶子节点存储着行数据因此主键索引非常高效。 MyISAM索引的叶子节点存储的是行数据地址需要再寻址一次才能得到数据。 InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据因此查询时做到覆盖索引会非常高效。
InnoDB引擎的4大特性 插入缓冲insert buffer) 二次写(double write) 自适应哈希索引(ahi) 预读(read ahead)
InnoDB存储引擎的锁的算法有三种 Record lock单个行记录上的锁 Gap lock间隙锁锁定一个范围不包括记录本身 Next-key lockrecordgap 锁定一个范围包含记录本身 索引
MySQL 使用索引的原因 根本原因
索引的出现就是为了提高数据查询的效率就像书的目录一样。 对于数据库的表而言索引其实就是它的“目录”。 扩展
创建唯一性索引可以保证数据库表中每一行数据的唯一性。 帮助引擎层避免排序和临时表 将随机 IO 变为顺序 IO加速表和表之间的连接
关系型和非关系型数据库的区别 关系型数据库的优点
容易理解因为它采用了关系模型来组织数据。 可以保持数据的一致性。 数据更新的开销比较小。 支持复杂查询带 where 子句的查询 非关系型数据库NOSQL的优点
无需经过 SQL 层的解析读写效率高。 基于键值对读写性能很高易于扩展 可以支持多种类型数据的存储如图片文档等等。 扩展可分为内存性数据库以及文档型数据库比如 RedisMongoDBHBase 等适合场景数据量大高可用的日志系统/地理位置存储系统
索引的三种常见底层数据结构以及优缺点 三种常见的索引底层数据结构分别是哈希表、有序数组和搜索树。
哈希表这种适用于等值查询的场景比如 memcached 以及其它一些 NoSQL 引擎不适合范围查询。 有序数组索引只适用于静态存储引擎等值和范围查询性能好但更新数据成本高。 N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。 扩展以 InnoDB 的一个整数字段索引为例这个 N 差不多是 1200。棵树高是 4 的时候就可以存 1200 的 3 次方个值这已经 17 亿了。考虑到树根的数据块总是在内存中的一个 10 亿行的表上一个整数字段的索引查找一个值最多只需要访问 3 次磁盘。其实树的第二层也有很大概率在内存中那么访问磁盘的平均次数就更少了。
Hash索引和B树所有有什么区别或者说优劣呢? 首先要知道Hash索引和B树索引的底层实现原理
hash索引底层就是hash表进行查找时调用一次hash函数就可以获取到相应的键值之后进行回表查询获得实际数据。B树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发查找到叶子节点方可以获得所查键值然后根据查询判断是否需要回表查询数据。
那么可以看出他们有以下的不同
hash索引进行等值查询更快(一般情况下)但是却无法进行范围查询。 因为在hash索引中经过hash函数建立索引之后索引的顺序与原顺序无法保持一致不能支持范围查询。而B树的的所有节点皆遵循(左节点小于父节点右节点大于父节点多叉树也类似)天然支持范围。
hash索引不支持使用索引进行排序原理同上。 hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。 hash索引任何时候都避免不了回表查询数据而B树在符合某些条件(聚簇索引覆盖索引等)的时候可以只通过索引完成查询。 hash索引虽然在等值查询上较快但是不稳定。性能不可预测当某个键值存在大量重复的时候发生hash碰撞此时效率可能极差。而B树的查询效率比较稳定对于所有的查询都是从根节点到叶子节点且树的高度较低。 因此在大多数情况下直接选择B树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。
数据库为什么使用B树而不是B树 B树只适合随机检索而B树同时支持随机检索和顺序检索 B树空间利用率更高可减少I/O次数磁盘读写代价更低。一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上。这样的话索引查找过程中就要产生磁盘I/O消耗。B树的内部结点并没有指向关键字具体信息的指针只是作为索引使用其内部结点比B树小盘块能容纳的结点中关键字数量更多一次性读入内存中可以查找的关键字也就越多相对的IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素 B树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束越靠近根节点的记录查找时间越短只要找到关键字即可确定记录的存在其性能等价于在关键字全集内做一次二分查找。而在B树中顺序检索比较明显随机检索时任何关键字的查找都必须走一条从根节点到叶节点的路所有关键字的查找路径长度相同导致每一个关键字的查询效率相当。 B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B树的叶子节点使用指针顺序连接在一起只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的而B树不支持这样的操作。 增删文件节点时效率更高。因为B树的叶子节点包含所有关键字并以有序的链表结构存储这样可很好提高增删效率。
什么是聚簇索引何时使用聚簇索引与非聚簇索引 聚簇索引将数据存储与索引放到了一块找到索引也就找到了数据 非聚簇索引将数据存储于索引分开结构索引结构的叶子节点指向了数据的对应行myisam通过key_buffer把索引先缓存到内存中当需要访问数据时通过索引访问数据在内存中直接搜索索引然后通过索引找到磁盘相应数据这也就是为什么索引不在key buffer命中时速度慢的原因
非聚簇索引一定会回表查询吗 不一定这涉及到查询语句所要求的字段是否全部命中了索引如果全部命中了索引那么就不必再进行回表查询。
联合索引是什么为什么需要注意联合索引中的顺序 MySQL可以使用多个字段同时建立一个索引叫做联合索引。在联合索引中如果想要命中索引需要按照建立索引时的字段顺序挨个使用否则无法命中索引。
具体原因为:
MySQL使用索引时需要索引有序假设现在建立了nameageschool的联合索引那么索引的排序为: 先按照name排序如果name相同则按照age排序如果age的值也相等则按照school进行排序。
当进行查询时此时索引仅仅按照name严格有序因此必须首先使用name字段进行等值查询之后对于匹配到的列而言其按照age字段严格有序此时可以使用age字段用做索引查找以此类推。因此在建立联合索引的时候应该注意索引列的顺序一般情况下将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
事物
什么是数据库事务 事务是一个不可分割的数据库操作序列也是数据库并发控制的基本单位其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作要么都执行要么都不执行。
事物的四大特性(ACID)介绍一下? 关系性数据库需要遵循ACID规则具体内容如下 原子性 事务是最小的执行单位不允许分割。事务的原子性确保动作要么全部完成要么完全不起作用 一致性 执行事务前后数据保持一致多个事务对同一个数据读取的结果是相同的 隔离性 并发访问数据库时一个用户的事务不被其他事务所干扰各并发事务之间数据库是独立的 持久性 一个事务被提交之后。它对数据库中数据的改变是持久的即使数据库发生故障也不应该对其有任何影响。
什么是脏读幻读不可重复读 脏读(Drity Read)某个事务已更新一份数据另一个事务在此时读取了同一份数据由于某些原因前一个RollBack了操作则后一个事务所读取的数据就会是不正确的。 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致这可能是两次查询过程中间插入了一个事务更新的原有的数据。 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致例如有一个事务查询了几列(Row)数据而另一个事务却在此时插入了新的几列数据先前的事务在接下来的查询中就会发现有几列数据是它先前所没有的。 什么是事务的隔离级别MySQL的默认隔离级别是什么 为了达到事务的四大特性数据库定义了4种不同的事务隔离级别由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
隔离级别 脏读 不可重复读 幻影读 READ-UNCOMMITTED √ √ √ READ-COMMITTED × √ √ REPEATABLE-READ × × √ SERIALIZABLE × × × 锁
从锁的类别上来讲有共享锁和排他锁。
共享锁: 又叫做读锁。 当用户要进行数据的读取时对数据加上共享锁。共享锁可以同时加上多个。
排他锁: 又叫做写锁。 当用户要进行数据的写入时对数据加上排他锁。排他锁只可以加一个他和其他的排他锁共享锁都相斥。
什么是死锁怎么解决 死锁是指两个或多个事务在同一资源上相互占用并请求锁定对方的资源从而导致恶性循环的现象。
常见的解决死锁的方法
1、如果不同程序会并发存取多个表尽量约定以相同的顺序访问表可以大大降低死锁机会。
2、在同一个事务中尽可能做到一次锁定所需要的所有资源减少死锁产生概率
3、对于非常容易产生死锁的业务部分可以尝试使用升级锁定颗粒度通过表级锁定来减少死锁产生的概率
如果业务处理不好可以用分布式事务锁或者使用乐观锁
数据库的乐观锁和悲观锁是什么怎么实现的 数据库管理系统DBMS中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制乐观锁和悲观并发控制悲观锁是并发控制主要采用的技术手段。
悲观锁假定会发生并发冲突屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来直到提交事务。实现方式使用数据库中的锁机制
乐观锁假设不会发生并发冲突只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来通过version的方式来进行锁定。实现方式乐一般会使用版本号机制或CAS算法实现。
MySQL中InnoDB引擎的行锁是怎么实现的 答InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id 1 for update;
for update 可以根据条件来完成行锁锁定并且 id 是有索引键的列如果 id 不是索引键那么InnoDB将完成表锁并发将无从谈起 视图
为什么要使用视图什么是视图 为了提高复杂SQL语句的复用性和表操作的安全性MySQL数据库管理系统提供了视图特性。所谓视图本质上是一种虚拟表在物理上是不存在的其内容与真实的表相似包含一系列带有名称的列和行数据。但是视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表并且在具体引用视图时动态生成。
视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务只能看到视图中所定义的数据而不是视图所引用表中的数据从而提高了数据库中数据的安全性。
视图有哪些特点 视图的特点如下:
视图的列可以来自不同的表是表的抽象和在逻辑意义上建立的新关系。
视图是由基本表(实表)产生的表(虚表)。
视图的建立和删除不影响基本表。
对视图内容的更新(添加删除和修改)直接影响基本表。
当视图来自多个基本表时不允许添加和删除数据。
视图的操作包括创建视图查看视图删除视图和修改视图。
视图的使用场景有哪些 视图根本用途简化sql查询提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。
下面是视图的常见使用场景
重用SQL语句
简化复杂的SQL操作。在编写查询后可以方便的重用它而不必知道它的基本查询细节
使用表的组成部分而不是整个表
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
视图的优点 查询简单化。视图能简化用户的操作 数据安全性。视图使用户能以多种角度看待同一数据能够对机密数据提供安全保护 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性 视图的缺点 性能。数据库必须把视图的查询转化成对基本表的查询如果这个视图是由一个复杂的多表查询所定义那么即使是视图的一个简单查询数据库也把它变成一个复杂的结合体需要花费一定的时间。
修改限制。当用户试图修改视图的某些行时数据库必须把它转化为对基本表的某些行的修改。事实上当从视图中插入或者删除时情况也是这样。对于简单视图来说这是很方便的但是对于比较复杂的视图可能是不可修改的
这些视图有如下特征1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图其中有些例外
性能
大表数据查询怎么优化 优化shema、sql语句索引 第二加缓存memcached, redis 主从复制读写分离 垂直拆分根据你模块的耦合度将一个大的系统分为多个小的系统也就是分布式系统 水平切分针对数据量大的表这一步最麻烦最能考验技术水平要选择一个合理的sharding key, 为了有好的查询效率表结构也要改动做一定的冗余应用也要改sql中尽量带sharding key将数据定位到限定的表上去查而不是扫描全部的表
超大分页怎么处理 超大的分页一般从两个方向上来解决.
数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据. 从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击. 解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库
优化查询过程中的数据访问 访问数据太多导致查询性能下降 确定应用程序是否在检索大量超过需要的数据可能是太多行或列 确认MySQL服务器是否在分析大量不必要的数据行 避免犯如下SQL语句错误 查询不需要的数据。解决办法使用limit解决 多表关联返回全部列。解决办法指定列名 总是返回全部列。解决办法避免使用SELECT * 重复查询相同的数据。解决办法可以缓存数据下次直接读取缓存 是否在扫描额外的记录。解决办法 使用explain进行分析如果发现查询需要扫描大量的数据但只返回少数的行可以通过如下技巧去优化 使用索引覆盖扫描把所有的列都放到索引中这样存储引擎不需要回表获取对应行就可以返回结果。 改变数据库和表的结构修改数据表范式 重写SQL语句让优化器可以以更优的方式执行查询。 优化长难的查询语句 一个复杂查询还是多个简单查询 MySQL内部每秒能扫描内存中上百万行数据相比之下响应数据给客户端就要慢得多 使用尽可能小的查询是好的但是有时将一个大的查询分解为多个小的查询是很有必要的。 切分查询 将一个大的查询分为多个小的相同的查询 一次性删除1000万的数据要比一次删除1万暂停一会的方案更加损耗服务器开销。 分解关联查询让缓存的效率更高。 执行单个查询可以减少锁的竞争。 在应用层做关联更容易对数据库进行拆分。 查询效率会有大幅提升。 较少冗余记录的查询。 优化特定类型的查询语句 count(*)会忽略所有的列直接统计所有列数不要使用count(列名) MyISAM中没有任何where条件的count(*)非常快。 当有where条件时MyISAM的count统计不一定比其它引擎快。 可以使用explain查询近似值用近似值替代count(*) 增加汇总表 使用缓存
如何最快的复制一张表 为了避免对源表加读锁更稳妥的方案是先将数据写到外部文本文件然后再写回目标表 一种方法是使用 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) 的方法可以通过导出 导入表空间的方式实现物理拷贝表的功能。
orderby 排序内部原理 MySQL 会为每个线程分配一个内存sort-buffer用于排序该内存大小为 sort_buffer_size 如果排序的数据量小于 sort_buffer_size排序就会在内存中完成 内部排序分为两种 全字段排序到索引树上找到满足条件的主键ID根据主键ID去取出数据放到sort_buffer然后进行快速排序 rowid排序通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据 如果数据量很大内存中无法存下这么多就会使用磁盘临时文件来辅助排序称为外部排序 外部排序MySQL会分为好几份单独的临时文件来存放排序后的数据一般是磁盘文件中进行归并然后将这些文件合并成一个大文件 日志
MySQL 的 change buffer 是什么 当需要更新一个数据页时如果数据页在内存中就直接更新而如果这个数据页还没有在内存中的话在不影响数据一致性的前提下InnoDB 会将这些更新操作缓存在 change buffer 中。 这样就不需要从磁盘中读入这个数据页了在下次查询需要访问这个数据页的时候将数据页读入内存然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。 注意唯一索引的更新就不能使用 change buffer实际上也只有普通索引可以使用。 适用场景 - 对于写多读少的业务来说页面在写完以后马上被访问到的概率比较小此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。 - 反过来假设一个业务的更新模式是写入之后马上会做查询那么即使满足了条件将更新先记录在 change buffer但之后由于马上要访问这个数据页会立即触发 merge 过程。这样随机访问 IO 的次数不会减少反而增加了 change buffer 的维护代价。
MySQL 是如何判断一行扫描数的 MySQL 在真正开始执行语句之前并不能精确地知道满足这个条件的记录有多少条。 而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度
mysql 的redo log 和binlog区别
redo log 用于崩溃 binlog 主从复制和数据恢复
binlog 的概念是什么起到什么作用 可以保证 crash-safe 吗? binlog 是归档日志属于 MySQL Server 层的日志。可以实现主从复制和数据恢复两个作用。 当需要恢复数据时可以取出某个时间范围内的 binlog 进行重放恢复。 但是 binlog 不可以做 crash safe因为 crash 之前binlog 可能没有写入完全 MySQL 就挂了。所以需要配合 redo log 才可以进行 crash safe。
MySQL的binlog有有几种录入格式分别有什么区别 有三种格式statementrow和mixed。
statement模式下每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化减少了binlog日志量节约了IO提高性能。由于sql的执行是有上下文的因此在保存的时候需要保存相关的信息同时还有一些使用了函数之类的语句无法被记录复制。 row级别下不记录sql语句上下文相关信息仅保存哪条记录被修改。记录单元为每一行的改动基本是可以全部记下来但是由于很多操作会导致大量行的改动(比如alter table)因此这种模式的文件保存的信息太多日志量太大。 mixed一种折中的方案普通操作使用statement记录当无法使用statement的时候使用row。 此外新版的MySQL中对row级别也做了一些优化当表结构发生变化的时候会记录语句而不是逐行记录。
为什么需要 redo log redo log 主要用于 MySQL 异常重启后的一种数据恢复手段确保了数据的一致性。 其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 进行更新操作为了能够快速响应所以采用了异步写回磁盘的技术写入内存后就返回。但是这样会存在 crash后 内存数据丢失的隐患而 redo log 具备 crash safe 的能力。
为什么 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 不具备。
当数据库 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 里恢复。
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。 备份
MySQL 是如何保证主备同步 主备关系的建立
一开始创建主备关系的时候是由备库指定的比如基于位点的主备关系备库说“我要从binlog文件A的位置P”开始同步主库就从这个指定的位置开始往后发。 而主备关系搭建之后是主库决定要发给数据给备库的所以主库有新的日志也会发给备库。 MySQL 主备切换流程
客户端读写都是直接访问A而节点B是备库只要将A的更新都同步过来到本地执行就可以保证数据是相同的。 当需要切换的时候就把节点换一下A的节点B的备库 一个事务完整的同步过程
备库B和主库A建立来了长链接主库A内部专门线程用于维护了这个长链接。 在备库B上通过changemaster命令设置主库A的IP端口用户名密码以及从哪个位置开始请求binlog包括文件名和日志偏移量 在备库B上执行start-slave命令备库会启动两个线程io_thread和sql_thread分别负责建立连接和读取中转日志进行解析执行 备库读取主库传过来的binlog文件备库收到文件写到本地成为中转日志 后来由于多线程复制方案的引入sql_thread演化成了多个线程。
什么是主备延迟 主库和备库在执行同一个事务的时候出现时间差的问题主要原因有
有些部署条件下备库所在机器的性能要比主库性能差。 备库的压力较大。 大事务一个主库上语句执行10分钟那么这个事务可能会导致从库延迟10分钟。
为什么要有多线程复制策略 因为单线程复制的能力全面低于多线程复制对于更新压力较大的主库备库可能是一直追不上主库的带来的现象就是备库上seconds_behind_master值越来越大。 在实际应用中建议使用可靠性优先策略减少主备延迟提升系统可用性尽量减少大事务操作把大事务拆分小事务。
MySQL 的并行策略有哪些 按表分发策略如果两个事务更新不同的表它们就可以并行。因为数据是存储在表里的所以按表分发可以保证两个 worker 不会更新同一行。缺点如果碰到热点表比如所有的更新事务都会涉及到某一个表的时候所有事务都会被分配到同一个 worker 中就变成单线程复制了。 按行分发策略如果两个事务没有更新相同的行它们在备库上可以并行。如果两个事务没有更新相同的行它们在备库上可以并行执行。显然这个模式要求 binlog 格式必须是 row。缺点相比于按表并行分发策略按行并行策略在决定线程分发的时候需要消耗更多的计算资源。
MySQL的一主一备和一主多从有什么区别 在一主一备的双 M 架构里主备切换只需要把客户端流量切到备库而在一主多从架构里主备切换除了要把客户端流量切到备库外还需要把从库接到新主库上。
主库出问题如何解决? 基于位点的主备切换存在找同步位点这个问题 MySQL 5.6 版本引入了 GTID彻底解决了这个困难。那么GTID 到底是什么意思又是如何解决找同步位点这个问题呢 GTID全局事务 ID是一个事务在提交的时候生成的是这个事务的唯一标识它由两部分组成格式是GTIDserver_uuid:gno 每个 MySQL 实例都维护了一个 GTID 集合用来对应“这个实例执行过的所有事务”。 在基于 GTID 的主备关系里系统认为只要建立主备关系就必须保证主库发给备库的日志是完整的。因此如果实例 B 需要的日志已经不存在A’就拒绝把日志发给 B。
MySQL 读写分离涉及到过期读问题的几种解决方案? 强制走主库方案 sleep 方案 判断主备无延迟方案 配合 semi-sync 方案 等主库位点方案 GTID 方案。 实际生产中先客户端对请求做分类区分哪些请求可以接受过期读而哪些请求完全不能接受过期读然后对于不能接受过期读的语句再使用等 GTID 或等位点的方案。
MySQL的并发链接和并发查询有什么区别 在执行show processlist的结果里看到了几千个连接指的是并发连接。而当前正在执行的语句才是并发查询。 并发连接数多影响的是内存并发查询太高对CPU不利。一个机器的CPU核数有限线程全冲进来上下文切换的成本就会太高。 所以需要设置参数innodb_thread_concurrency 用来限制线程数当线程数达到该参数InnoDB就会认为线程数用完了会阻止其他语句进入引擎执行。
