目录
3、SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?
1、简述在MySQL数据库中MyISAM和InnoDB的区别
11、Hash 索引和 B+ 树索引区别是什么?你在设计索引是怎么抉择的?
13、简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)
2、怎么看执行计划(explain),如何理解其中各个字段的含义?
一、MySQL原理
1、说说MySQL 的基础架构图
Mysql逻辑架构图主要分三层:
- 连接层:负责连接处理,授权认证,安全等等
- 服务层:负责编译,优化SQL
- 存储引擎层:负责存储数据,提供读写接口
2、SQL查询语句在MySQL中如何执行的?
- 先检查该语句是否有权限,如果没有权限,直接返回错误信息,如果有权限会先查询缓存(MySQL8.0 版本以前)。
- 如果没有缓存,分析器进行词法分析,提取 sql 语句中 select 等关键元素,然后判断 sql 语句是否有语法错误,比如关键词是否正确等等。
- 最后优化器确定执行方案进行权限校验,如果没有权限就直接返回错误信息,如果有权限就会调用数据库引擎接口,返回执行结果。
二、SQL语句
1、SQL语言包括哪几部分?每部分都有哪些操作关键字?
答:SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询(DQL)四个部分。
- 数据定义:Create Table,Alter Table,Drop Table, Craete/Drop Index等
- 数据操纵:Select ,insert,update,delete,
- 数据控制:grant,revoke
- 数据查询:select
2、解释MySQL外连接、内连接与自连接的区别
- 交叉连接: 交叉连接又叫笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配。
- 内连接 则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中,即内连接只连接匹配的行。
- 外连接 其结果集中不仅包含符合连接条件的行,而且还会包括左表、右表或两个表中
- 的所有数据行,这三种情况依次称之为左外连接,右外连接,和全外连接。
- 左外连接,也称左连接,左表为主表,左表中的所有记录都会出现在结果集中,对于那些在右表中并没有匹配的记录,仍然要显示,右边对应的那些字段值以NULL来填充。右外连接,也称右连接,右表为主表,右表中的所有记录都会出现在结果集中。左连接和右连接可以互换,MySQL目前还不支持全外连接。
3、SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?
子查询:嵌套在其他查询中的查询称之。
子查询又称内部,而包含子查询的语句称之外部查询(又称主查询)。
所有的子查询可以分为两类,即相关子查询和非相关子查询。
- 非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
- 相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。故非相关子查询比相关子查询效率高
三、MySQL语言基础
1、如何通俗地理解三个范式?
- 第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;
- 第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
- 第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。。
2、范式化设计优缺点
优点
可以尽量得减少数据冗余,使得更新快,体积小
缺点
对于查询需要多个表进行关联,减少写得效率增加读得效率,更难进行索引优化
3、反范式化
优点
可以减少表得关联,可以更好得进行索引优化
缺点
数据冗余以及数据异常,数据得修改需要更多的成本
4、char和varchar的区别?
是一种固定长度的类型,varchar则是一种可变长度的类型,它们的区别是:
char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足。(在检索操作中那些填补出来的空格字符将被去掉)在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)。
varchar得适用场景
字符串列得最大长度比平均长度大很多 2.字符串很少被更新,容易产生存储碎片 3.使用多字节字符集存储字符串
Char得场景
存储具有近似得长度(md5值,身份证,手机号),长度比较短小得字符串(因为varchar需要额外空间记录字符串长度),更适合经常更新得字符串,更新时不会出现页分裂得情况,避免出现存储碎片,获得更好的io性能
5、为表中得字段选择合适得数据类型(物理设计)
字段类型优先级: 整型>date,time>enum,char>varchar>blob,text
优先考虑数字类型,其次是日期或者二进制类型,最后是字符串类型,同级别得数据类型,应该优先选择占用空间小的数据类型
6、存储时期
- Datatime:以 YYYY-MM-DD HH:MM:SS 格式存储时期时间,精确到秒,占用8个字节得存储空间,datatime类型与时区无关
- Timestamp:以时间戳格式存储,占用4个字节,范围小1970-1-1到2038-1-19,显示依赖于所指定得时区,默认在第一个列行的数据修改时可以自动得修改timestamp列得值
- Date:(生日)占用得字节数比使用字符串.datatime.int储存要少,使用date只需要3个字节,存储日期月份,还可以利用日期时间函数进行日期间得计算
- Time:存储时间部分得数据
注意:不要使用字符串类型来存储日期时间数据(通常比字符串占用得储存空间小,在进行查找过滤可以利用日期得函数)
使用int存储日期时间不如使用timestamp类型
7、主键、外键和索引的区别?
主键--唯一标识一条记录,不能有重复的,不允许为空
外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引--该字段没有重复值,但可以有一个空值
作用
- 主键--用来保证数据完整性
- 外键--用来和其他表建立联系用的
- 索引--是提高查询排序的速度
个数
- 主键--主键只能有一个
- 外键--一个表可以有多个外键
- 索引--一个表可以有多个唯一索引
四、存储引擎
1、简述在MySQL数据库中MyISAM和InnoDB的区别
区别于其他数据库的最重要的特点就是其插件式的表存储引擎。
切记:存储引擎是基于表的,而不是数据库。
InnoDB与MyISAM的区别:
InnoDB存储引擎: 主要面向OLTP(Online Transaction Processing,在线事务处理)方面的应用,是第一个完整支持ACID事务的存储引擎(BDB第一个支持事务的存储引擎,已经停止开发)。
MyISAM存储引擎: 是MySQL官方提供的存储引擎,主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。
- InnoDB 支持事务;MyISAM 不支持事务
- InnoDB 支持行级锁;MyISAM 支持表级锁
- InnoDB 支持 MVCC(多版本并发控制);MyISAM 不支持
- InnoDB 支持外键,MyISAM 不支持
- MySQL 5.6 以前的版本,InnoDB 不支持全文索引,MyISAM 支持;MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引
- InnoDB 不保存表的总行数,执行 select count(*) from table 时需要全表扫描;MyISAM 用一个变量保存表的总行数,查总行数速度很快
- InnoDB 是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。辅助索引需要两次查询,先查询到主键,再通过主键查询到数据。主键太大,其他索引也会很大;MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针,主键索引和辅助索引是独立的
五、索引
1、哪些字段需要建立索引
SQL语句中的条件和排序里面用到的字段建立索引。
2、什么情况需要给字段建立索引?
- 表的主键、外键必须有索引;
- 数据量超过300的表应该有索引;
- 经常与其他表进连接的表,在连接字段上应该建索引;
- 经常出现在Where句 order by中的字段,特别是表的字段,应该建索
- 引;
- 索引应该建在选择性的字段上;
- 索引应该建在字段上,对于的本字段甚超字段,不要建索引;
3、索引失效
口诀:模型属空运最快
- 使用LIKE关键字进行模糊查询时,以%开头
- 数据类型错误
- 对索引字段使用内部函数,应该建立基于函数的索引
- 不限制索引列是NOT NULL,数据库认为索引列可能为空
- 对索引列进行加减乘除等运算
- 在复合索引中,不遵循最左原则进行查找
- 数据库使用全表扫描时预计比使用索引更快
4、什么是最左前缀原则?什么是最左匹配原则?
最左前缀原则,就是最左优先,在创建多列索引时,要根据业务需求,where 子句中使用最频繁的一列放在最左边。
最左匹配原则,就是当我们创建一个组合索引的时候,如 (a1,a2,a3),相当于创建了(a1)、(a1,a2)和(a1,a2,a3)三个索引。
5、索引不适合哪些场景?
- 数据量少的不适合加索引
- 更新比较频繁的也不适合加索引
- 区分度低的字段不适合加索引(如性别)
6、索引有哪些优缺点?
优点
- 唯一索引可以保证数据库表中每一行的数据的唯一性
- 索引可以加快数据查询速度,减少查询时间
缺点
- 创建索引和维护索引要耗费时间
- 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间
- 以表中的数据进行增、删、改的时候,索引也要动态维护。
7、为什么要用 B+ 树,为什么不用普通二叉树?
可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少,以及查找磁盘次数,为什么不是普通二叉树,为什么不是平衡二叉树,为什么不是B树,而偏偏是 B+ 树呢?
8、为什么不是普通二叉树?
如果二叉树特殊化为一个链表,相当于全表扫描。平衡树相比于查找树来说,总体的查找速度更快,查找效率也更稳定。
9、为什么不是平衡二叉树呢?
在磁盘比在内存的数据,查询效率慢得多。如果树这种数据结构作为索引,那我们每查找一次数据就需要从磁盘中读取一个节点。平衡二叉树可是每个节点只存储一个键值和数据的,如果是B树(平衡多叉树),可以存储更多的节点数据,树的高度也会降低,因此读取磁盘的次数就降下来啦,查询效率就快啦。
10、为什么不是 B 树而是 B+ 树呢?
B+ 树非叶子节点上是不存储数据的,仅存储键值,而B树节点中不仅存储键值,也会存储数据。innodb中页的默认大小是16KB,如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的IO次数有会再次减少,数据查询的效率也会更快。
B+ 树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的,链表连着的。那么 B+ 树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。
11、Hash 索引和 B+ 树索引区别是什么?你在设计索引是怎么抉择的?
- B+ 树可以进行范围查询,Hash 索引不能。
- B+ 树支持联合索引的最左侧原则,Hash 索引不支持。
- B+ 树支持 order by 排序,Hash 索引不支持。
- B+ 树使用 like 进行模糊查询的时候,like 后面(比如%开头)的话可以起到优化的作用,Hash 索引根本无法进行模糊查询。
- Hash 索引在等值查询上比 B+ 树效率更高。
12、聚集索引与非聚集索引的区别
- 一个表中只能拥有一个聚集索引,而非聚集索引一个表可以存在多个。
- 聚集索引,索引中键值的逻辑顺序决定了表中相应行的物理顺序;非聚集索引,索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
- 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。
- 聚集索引:物理存储按照索引排序;非聚集索引:物理存储不按照索引排序;
13、简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说,唯一索引可以保证数据记录的唯一性。
主键,是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引,这就是联合索引。
索引可以极大的提高数据的查询速度,但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件。
14、索引对数据库系统的负面影响是什么?
- 创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加;
- 索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间;
- 当对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。
15、为数据表建立索引的原则有哪些?
- 在最频繁使用的、用以缩小查询范围的字段上建立索引。
- 在频繁使用的、需要排序的字段上建立索引
16、什么情况下不宜建立索引?
- 对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引。
- 对于一些特殊的数据类型,不宜建立索引,比如文本字段(text)等
六、数据库对象
1、什么是基本表?什么是视图?
基本表是本身独立存在的表,在 SQL 中一个关系就对应一个表。 视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中,是一个虚表
2、试述视图的优点?
- 视图能够简化用户的操作
- 视图使用户能以多种角度看待同一数据
- 视图为数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
3、完整性约束包括哪些?
答:数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。
分为以下四类:
- 实体完整性:规定表的每一行在表中是惟一的实体。
- 域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括取值范围、精度等规定。
- 参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。
- 用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。
与表有关的约束:包括列约束(NOT NULL(非空约束))和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。
4、什么叫视图?游标是什么?
视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。
游标:是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。
5、什么是存储过程?用什么来调用?
存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。
6、可以用什么来确保表格里的字段只接受特定范围里的值?
Check限制,它在数据库表格里被定义,用来限制输入该列的值。
触发器也可以被用来限制数据库表格里的字段能够接受的值,但是这种办法要求触发器在表格里被定义,这可能会在某些情况下影响到性能。
七、数据库事务
1、什么是事务?及其特性?
答:事务:是一系列的数据库操作,是数据库应用的基本逻辑单位。
2、Myql中的事务回滚机制概述
事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,是一个不可分割的工作单位,事务回滚是指将该事务已经完成的对数据库的更新操作撤销。
要同时修改数据库中两个不同表时,如果它们不是一个事务的话,当第一个表修改完,可能第二个表修改过程中出现了异常而没能修改,此时就只有第二个表依旧是未修改之前的状态,而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候,当第一个表修改完,第二表修改出现异常而没能修改,第一个表和第二个表都要回到未修改的状态,这就是所谓的事务回滚
3、MySQL事务得四大特性以及实现原理
- 原子性: 事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
- 一致性: 指在事务开始之前和事务结束以后,数据不会被破坏,假如A账户给B账户转10块钱,不管成功与否,A和B的总金额是不变的。
- 隔离性: 多个事务并发访问时,事务之间是相互隔离的,即一个事务不影响其它事务运行效果。简言之,就是事务之间是进水不犯河水的。
- 持久性: 表示事务完成以后,该事务对数据库所作的操作更改,将持久地保存在数据库之中。
4、事务的隔离级别有哪些?MySQL的默认隔离级别是什么?
- 读未提交(Read Uncommitted)
- 读已提交(Read Committed)
- 可重复读(Repeatable Read)
- 串行化(Serializable)
Mysql默认的事务隔离级别是可重复读(Repeatable Read)
5、什么是幻读,脏读,不可重复读呢?
事务A、B交替执行,事务A被事务B干扰到了,因为事务A读取到事务B未提交的数据,这就是脏读。
在一个事务范围内,两个相同的查询,读取同一条记录,却返回了不同的数据,这就是不可重复读。
事务A查询一个范围的结果集,另一个并发事务B往这个范围中插入/删除了数据,并静悄悄地提交,然后事务A再次查询相同的范围,两次读取得到的结果集不一样了,这就是幻读。
八、锁
1、什么是锁?
数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。
加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。
基本锁类型:锁包括行级锁和表级锁
2、MySQL 遇到过死锁问题吗,你是如何解决的?
- 查看死锁日志 show engine innodb status;
- 找出死锁Sql
- 分析sql加锁情况
- 模拟死锁案发
- 分析死锁日志
- 分析死锁结果
3、说说数据库的乐观锁和悲观锁是什么以及它们的区别?
悲观锁
悲观锁只属于当前事务,一个事务拥有(获得)悲观锁后,其他任何事务都不能对数据进行修改,只能等待锁被释放才可以执行。
乐观锁
乐观锁的“乐观情绪”体现在,它认为数据的变动不会太频繁。因此,它允许多个事务同时对数据进行变动。
实现方式:乐观锁一般会使用j版本号机制或kCAS算法实现。
4、MVCC 熟悉吗,知道它的底层原理?
MVCC (Multiversion Concurrency Control),即多版本并发控制技术。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能,用更好的方式去处理读-写冲突,做到即使有读写冲突时,也能做到不加锁,非阻塞并发读。
5、如何设计一个高并发的系统
- 数据库的优化,包括合理的事务隔离级别、SQL语句优化、索引的优化
- 使用缓存,尽量减少数据库 IO
- 分布式数据库、分布式缓存
- 服务器的负载均衡
6、锁的优化策略
- 读写分离
- 分段加锁
- 减少锁持有的时间
- 多个线程尽量以相同的顺序去获取资源
九、优化
1、SQL怎么优化
优化表结构
- 尽量使用数字型字段
- 若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
- 尽可能的使用 varchar 代替 char
- 变长字段存储空间小,可以节省存储空间。
- 当索引列大量重复数据时,可以把索引删除掉
优化查询
- 应尽量避免在 where 子句中使用!=或<>操作符
- 应尽量避免在 where 子句中使用 or 来连接条件
- 避免在 where 子句中对字段进行 null 值判断
- 任何查询也不要出现select *
优化索引
- 对作为j查询条件和 korder by的字段建立索引
- 避免建立过多的索引,多使用组合索引
2、怎么看执行计划(explain),如何理解其中各个字段的含义?
在 select 语句之前增加 explain 关键字,会返回执行计划的信息。
id 列:是 select 语句的序号,MySQL将 select 查询分为简单查询和复杂查询。
select_type列:表示对应行是是简单还是复杂的查询。
table 列:表示 explain 的一行正在访问哪个表。
type 列:最重要的列之一。表示关联类型或访问类型,即 MySQL 决定如何查找表中的行。 从最优到最差分别为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys 列:显示查询可能使用哪些索引来查找。
key 列:这一列显示 mysql 实际采用哪个索引来优化对该表的访问。
key_len 列:显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。
ref 列:这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),func,NULL,字段名。
rows 列:这一列是 mysql 估计要读取并检测的行数,注意这个不是结果集里的行数。
Extra 列:显示额外信息。比如有 Using index、Using where、Using temporary等。
3、慢查询优化
- 分析语句,是否加载了不必要的字段/数据
- 分析 SQL 执行句,是否命中索引等
- 如果 SQL 很复杂,优化 SQL 结构
- 如果表数据量太大,考虑分表
4、优化数据库的方法
- 选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL,例如'省份'、'性别'最好适用ENUM
- 使用连接(JOIN)来代替子查询
- 适用联合(UNION)来代替手动创建的临时表
- 事务处理
- 锁定表、优化事务处理
- 适用外键,优化锁定表
- 建立索引
- 优化查询语句
5、MySQL数据库cpu飙升的话,要怎么处理呢?
排查过程
使用top 命令观察,确定是mysqld导致还是其他原因。
如果是mysqld导致的,show processlist,查看session情况,确定是不是有消耗资源的sql在运行。
找出消耗高的 sql,看看执行计划是否准确, 索引是否缺失,数据量是否太大。
处理
kill 掉这些线程(同时观察 cpu 使用率是否下降)
进行相应的调整(比如说加索引、改 sql、改内存参数)
重新跑这些 SQL。
其他情况
也有可能是每个 sql 消耗资源并不多,但是突然之间,有大量的 session 连进来导致 cpu 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等
6、实践中如何优化MySQL
我当时是按以下四条依次回答的,他们四条从效果上第一条影响最大,后面越来越小。
- SQL语句及索引的优化
- 数据库表结构的优化
- 系统配置的优化
- 硬件的优化
十、分区
1、如果让你做分库与分表的设计,简单说说你会怎么做?
分库分表方案
- 水平分库:以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中。
- 水平分表:以字段为依据,按照一定策略(hash、range等),将一个表中的数据拆分到多个表中。
- 垂直分库:以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。
- 垂直分表:以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。
常用的分库分表中间件
- sharding-jdbc
- Mycat
分库分表可能遇到的问题
- 事务问题:需要用分布式事务
- 跨节点Join的问题:解决这一问题可以分两次查询实现
- 跨节点的count,order by,group by以及聚合函数问题:分别在各个节点上得到结果后在应用程序端进行合并。
- 数据迁移,容量规划,扩容等问题
- ID问题:数据库被切分后,不能再依赖数据库自身的主键生成机制啦,最简单可以考虑UUID
- 跨分片的排序分页问题
十一、集群
1、MYSQL的主从延迟,你怎么解决?
主从复制分了五个步骤进行
- 主库的更新事件(update、insert、delete)被写到binlog
- 从库发起连接,连接到主库。
- 此时主库创建一个binlog dump thread,把binlog的内容发送到从库。
- 从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
- 还会创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db
主从同步延迟的原因
一个服务器开放N个链接给客户端来连接的,这样有会有大并发的更新操作, 但是从服务器的里面读取binlog的线程仅有一个,当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致,主服务器的SQL大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟。
主从同步延迟的解决办法
- 主服务器要负责更新操作,对安全性的要求比从服务器要高,所以有些设置参数可以修改,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之类的设置等。
- 选择更好的硬件设备作为slave。
- 把一台从服务器当度作为备份使用, 而不提供查询, 那边他的负载下来了, 执行relay log 里面的SQL效率自然就高了。
- 增加从服务器,这个目的还是分散读的压力,从而降低服务器负载。