《2021最新Java面试题全集-2021年第二版》不断更新完善!

    

第八章 MySql

1:数据库的三范式是什么?

第一范式:1NF 是对属性的原子性约束,强调的是列的原子性,即数据库表的每一列都是不可分割的原子数据项。

第二范式:2NF 是对记录的惟一性约束,要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式:3NF 是对字段冗余性的约束,任何非主属性不依赖于其它非主属性。即任何字段不能由其他字段派生出来, 它要求字段没有冗余。

 

2:一张自增表里面总共有 7 条数据,删除了最后 2 条数据,重启 mysql 数据库,又插入了一条数据,此时 id 是多少?

表类型如果是 MyISAM ,那 id 就是 8

表类型如果是 InnoDB,那 id 就是 6

InnoDB 表只会把自增主键的最大 id 记录在内存中,所以重启之后会导致最大 id 丢失。

 

3:如何获取当前数据库版本?

使用 select version() 获取当前 MySQL 数据库版本

 

4:完整性约束包括哪些?

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)

分为以下四类:

1:实体完整性:规定表的每一行在表中是惟一的实体。

2:域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括 取值范围、精度等规定。

3:参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间 的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。

4:用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需 要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束 条件,它反映某一具体应用必须满足的语义要求。

与表有关的约束:包括列约束(NOT NULL(非空约束))和表约束(PRIMARY KEY foreign keycheckUNIQUE)

 

5:说一下 ACID 是什么?

1Atomicity(原子性):

一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。

2Consistency(一致性):

在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

3Isolation(隔离性):

数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

4Durability(持久性):

事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失

 

6:说一下数据库的事务隔离?

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的,在文件的最后添加:transaction-isolation = REPEATABLE-READ

可用的配置值:READ-UNCOMMITTEDREAD-COMMITTEDREPEATABLE-READSERIALIZABLE

READ-UNCOMMITTED

未提交读,最低隔离级别、事务未提交前,就可被其他事务读取(会出现幻读、脏读、不可重复读)。

READ-COMMITTED

提交读,一个事务提交后才能被其他事务读取到(会造成幻读、不可重复读)。

REPEATABLE-READ

可重复读,默认级别,保证多次读取同一个数据时,其值都和事务开始时候的内容是一致,禁止读取到别的事务未提交的数据(会造成幻读)。

SERIALIZABLE

序列化,代价最高最可靠的隔离级别,该隔离级别能防止脏读、不可重复读、幻读。

脏读:表示一个事务能够读取另一个事务中还未提交的数据。比如,某个事务尝试插入记录 A,此时该事务还未提交,然后另一个事务尝试读取到了记录 A

不可重复读:是指在一个事务内,多次读同一数据,发现数据不一样

幻读:指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录,但是第二次同等条件下查询却有 n+1 条记录,这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据,同一个记录的数据内容被修改了,所有数据行的记录就变多或者变少了。

 

7:char varchar 的区别是什么?

char(n)

固定长度类型,比如 char(10),当你输入"abc"三个字符的时候,它们占的空间还是 10 个字节,其他 7 个是空字节。

char优点:效率高;缺点:占用空间;适用场景:存储密码的 md5 值,固定长度的,使用 char 非常合适。

 

varchar(n)

可变长度,存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以,从空间上考虑 varcahr 比较合适;从效率上考虑 char 比较合适,二者使用需要权衡

 

8:mysql 的内连接、左连接、右连接有什么区别?

内连接关键字:inner join;左连接:left join;右连接:right join

内连接是把匹配的关联数据显示出来;左连接是左边的表全部显示出来,右边的表显示出符合条件的数据;右连接正好相反

 

9:mysql 索引是怎么实现的?

索引是满足某种特定查找算法的数据结构,而这些数据结构会以某种方式指向数据,从而实现高效查找数据。

MySQL 中的索引,不同的数据引擎实现有所不同,但目前主流的数据库引擎的索引都是 B+ 树实现的,B+ 树的搜索效率,可以到达二分法的性能,找到数据区域之后就找到了完整的数据结构了,所以有索引的性能会更好。

 

10:怎么验证 mysql 的索引是否满足需求?

使用 explain 查看 SQL 是如何执行查询语句的,从而分析你的索引是否满足需求

explain 语法:explain select * from table where type=1

 

11:你是怎么使用索引的?

可以回答一些基本的使用索引的原则,常见的有:

1)最左前缀匹配原则,组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(><betweenlike)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2)较频繁作为查询条件的字段才去创建索引

3)更新频繁字段不适合创建索引

4)若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)

5)尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

6)定义有外键的数据列一定要建立索引。

7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

8)对于定义为textimagebit的数据类型的列不要建立索引。

 

12:说一下前缀索引?

语法:index(field(10)),使用字段值的前10个字符建立索引,默认是使用字段的全部内容建立索引。

前提:前缀的标识度高。比如密码就适合建立前缀索引,因为密码几乎各不相同。

实操的难度:在于前缀截取的长度。

我们可以利用select count(*)/count(distinct left(password,prefixLen));,通过从调整prefixLen的值(从1自增)查看不同前缀长度的一个平均匹配度,接近1时就可以了(表示一个密码的前prefixLen个字符几乎能确定唯一一条记录)

 

13:什么时候最左前缀原则?什么是最左匹配原则?

顾名思义,就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(><betweenlike)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

=in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

 

14:说说B树和B+树的区别?

  • B树中,你可以将键和值存放在内部节点和叶子节点;但在B+树中,内部节点都是键,没有值,叶子节点同时存放键和值。
  • B+树的叶子节点有一条链相连,而B树的叶子节点各自独立。

B树可以在内部节点同时存储键和值,因此,把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

由于B+树的内部节点只存放键,不存放值,因此,一次读取,可以在内存页中获取更多的键,有利于更快地缩小查找范围。B+树的叶节点由一条链相连,因此,当需要进行一次全数据遍历的时候,B+树只需要使用O(logN)时间找到最小的一个节点,然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历,这会需要更多的内存置换次数,因此也就需要花费更多的时间

 

15:联合索引是什么?为什么要注意联合索引中的顺序?

MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序,假设现在建立了"nameageschool"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序。

当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推。

因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

 

16:MySQLbinlog有有几种录入格式?分别有什么区别

有三种格式,statementrowmixed

  • statement模式下,每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化,减少了binlog日志量,节约了I/O,提高性能。由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制。
  • row级别下,不记录sql语句上下文相关信息,仅保存哪条记录被修改。记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。
  • mixed,一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row

此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录。

 

17:说一下 mysql 常用的引擎?

InnoDB 引擎:InnoDB 引擎提供了对数据库 acid 事务的支持,并且还提供了行级锁和外键的约束,它的设计的目标就是处理大数据容量的数据库系统。

MySQL 运行的时候,InnoDB 会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎是不支持全文搜索,同时启动也比较的慢,它是不会保存表的行数的,所以当进行 select count(*) from table 指令的时候,需要进行扫描全表。由于锁的粒度小,写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。

MyIASM 引擎:MySQL 的默认引擎,但不提供事务的支持,也不支持行级锁和外键。因此当执行插入和更新语句时,即执行写操作的时候需要锁定这个表,所以会导致效率会降低。不过和 InnoDB 不同的是,MyIASM 引擎是保存了表的行数,于是当进行 select count(*) from table 语句时,可以直接的读取已经保存的值而不需要进行扫描全表。所以,如果表的读操作远远多于写操作时,并且不需要事务的支持的,可以将 MyIASM 作为数据库引擎的首选。

 

18:MyISAM索引与InnoDB索引的区别

  • InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效。

 

19:InnoDB引擎的特性

·       插入缓冲(insert buffer)

·       二次写(double write)

·       自适应哈希索引(ahi)

·       预读(read ahead)

20:存储引擎的选择?

如果没有特别的需求,使用默认的InnoDB即可。

MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。

Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键。

 

21:说一下 mysql 的行锁和表锁?

MyISAM 只支持表锁,InnoDB 支持表锁和行锁,默认为行锁。

表级锁:开销小,加锁快,不会出现死锁。锁定粒度大,发生锁冲突的概率最高,并发量最低。

行级锁:开销大,加锁慢,会出现死锁。锁力度小,发生锁冲突的概率小,并发度最高

 

22:说一下隔离级别和锁的关系?

Read Uncommitted级别下,读取数据不需要加共享锁,这样就不会跟被修改的数据上的排他锁冲突

Read Committed级别下,读操作需要加共享锁,但是在语句执行完以后释放共享锁;

Repeatable Read级别下,读操作需要加共享锁,但是在事务提交之前并不释放共享锁,也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别,因为该级别锁定整个范围的键,并一直持有锁,直到事务完成。

 

23:数据库锁有哪些?

从锁的类别上来讲,有共享锁和排他锁。

1)共享锁: 又叫做读锁。当用户要进行数据的读取时,对数据加上共享锁。共享锁可以同时加上多个。

2)排他锁: 又叫做写锁。当用户要进行数据的写入时,对数据加上排他锁。排他锁只可以加一个,他和其他的排他锁,共享锁都相斥。

锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁。

1行级锁

行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。行级锁分为共享锁 排他锁。

特点:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

 

2)表级锁

表级锁是MySQL中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAMINNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

特点:开销小,加锁快;不会出现死锁;锁定粒度大,发出锁冲突的概率最高,并发度最低。

 

3)页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。

特点:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

 

24:说一下乐观锁和悲观锁?

乐观锁:每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

悲观锁:每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻止,直到这个锁被释放。

数据库的乐观锁需要自己实现,在表里面添加一个 version 字段,每次修改成功值加 1,这样每次修改的时候先对比一下,自己拥有的 version 和数据库现在的 version 是否一致,如果不一致就不修改,这样就实现了乐观锁

25:什么情况下设置了索引但无法使用

比如:

1)以“%”开头的 LIKE 语句,模糊匹配

2OR 语句前后没有同时使用索引

3)数据类型出现隐式转化( varchar 不加单引号的话可能会自动转换为 int )

 

26:mysql 问题排查都有哪些手段?

1)使用 show processlist 命令查看当前所有连接信息。

2)使用 explain 命令查询 SQL 语句执行计划

对于低性能的SQL语句的定位,最重要也是最有效的方法就是使用执行计划,可以显示数据库引擎对于SQL语句的执行的详细情况,其中包含了是否使用索引,使用什么索引,使用的索引的相关信息等。

3)开启慢查询日志,查看慢查询的 SQL

 

27:如何做 mysql 的性能优化?

为搜索字段创建索引

避免使用 select *,列出需要查询的字段

垂直分割分表

选择正确的存储引擎

 

28:百万级别或以上数据,如何删除?

由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增//删的执行效率。

所以,在我们删除数据库百万级别数据的时候,查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

(1)  我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)

(2)  然后删除其中无用数据(此过程需要不到两分钟)

3)删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。

与之前的直接删除绝对是要快速很多,更别说万一删除中断,一切删除会回滚。那更是坑了

 

29:超大分页如何处理?

超大的分页一般从两个方向上来解决.

1)数据库层面,这也是我们主要集中关注的(虽然收效没那么大)

类似于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的数据.

2)从需求的角度减少这种请求

主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击。

3)解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至Redisk-V数据库中,直接返回即可.

 

30:关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?

在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?

所以优化也是针对这三个方向来的,

  • 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写。
  • 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引。
  • 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表

 

31:常见的SQL优化需关注哪些方面?

  • 访问数据太多导致查询性能下降

确定应用程序是否在检索大量超过需要的数据,可能是太多行或列

确认MySQL服务器是否在分析大量不必要的数据行

  • 避免犯如下SQL语句错误

查询不需要的数据。解决办法:使用limit解决

多表关联返回全部列。解决办法:指定列名

总是返回全部列。解决办法:避免使用SELECT *

重复查询相同的数据。解决办法:可以缓存数据,下次直接读取缓存

  • 是否在扫描额外的记录。解决办法:
  • 使用explain进行分析,如果发现查询需要扫描大量的数据,但只返回少数的行,可以通过如下技巧去优化:
  • 使用索引覆盖扫描,把所有的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果。
  • 改变数据库和表的结构,修改数据表范式
  • 重写SQL语句,让优化器可以以更优的方式执行查询

 

常见SQL语句优化的方法:

1Where 子句中:where 表之间的连接必须写在其他 Where 条件之前,那些可 以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。

2:用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN

3:避免在索引列上使用计算

4:避免在索引列上使用 IS NULL IS NOT NULL

5:对查询进行优化,应尽量避免全表扫描,首先应考虑在 where order by 涉及的列上建立索引。

6:应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描

7:应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描

32:MySQL数据库cpu飙升到500%,怎么处理?

cpu 飙升到 500%时,先用操作系统命令 top 命令观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,并进行相关处理。

如果是 mysqld 造成的, show processlist,看看里面跑的 session 情况,是不是有消耗资源的 sql 在运行。找出消耗高的 sql,看看执行计划是否准确, index 是否缺失,或者实在是数据量太大造成。

一般来说,肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降),等进行相应的调整(比如说加索引、改 sql、改内存参数)之后,再重新跑这些 SQL

也有可能是每个 sql 消耗资源并不多,但是突然之间,有大量的 session 连进来导致 cpu 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等

 

33:大表怎么优化?某个表有近千万数据,CRUD比较慢,如何优化?

MySQL单表记录数过大时,数据库的CRUD性能会明显下降,一些常见的优化措施如下:

  • 限定数据的范围:务必禁止不带任何限制数据范围条件的查询语句。比如:我们当用户在查询订单历史的时候,我们可以控制在一个月的范围内。;
  • /写分离:经典的数据库拆分方案,主库负责写,从库负责读;
  • 缓存:使用MySQL的缓存,另外对重量级、更新少的数据可以考虑使用应用级别的缓存;
  • 还有就是通过分库分表的方式进行优化,主要有垂直分表和水平分表

 

34:什么是分库分表,是怎么做的?

分库就是:将一个库的数据拆分到多个库中,访问的时候就访问一个库就好了。

分表就是:把一个表的数据放到多个表中,然后查询的时候你就查一个表。比如按照用户id来分表,将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内,比如每个表就固定在200万以内。

 

1:垂直分区:

根据数据库里面数据表的相关性进行拆分。例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表。 如下图所示,这样来说大家应该就更容易理解了。

垂直拆分的优点:可以使得行数据变小,在查询时减少读取的Block数,减少I/O次数。此外,垂直分区可以简化表的结构,易于维护。

垂直拆分的缺点:主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应用层进行Join来解决。此外,垂直分区会让事务变得更加复杂。

垂直拆分的意思,就是把一个有很多字段的表给拆分成多个表,或者是多个库上去。每个库表的结构都不一样,每个库表都包含部分字段。一般来说,会将较少的访问频率很高的字段放到一个表里去,然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的,你访问频率高的行字段越少,就可以在缓存里缓存更多的行,性能就越好。

 

2:垂直分表

把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中

适用场景

(1)  如果一个表中某些列常用,另外一些列不常用

2)可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数

缺点

1)有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差

(2)  对于应用层来说,逻辑算法增加开发成本

3)管理冗余列,查询所有数据需要join操作

 

3:水平分区:

保持数据表结构不变,通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中,达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。举个例子:我们可以将用户信息表拆分成多个用户信息表,这样就可以避免单一表数据量过大对性能造成影响。

水平拆分的意思,就是把一个表的数据给弄到多个库的多个表里去,但是每个库的表结构都一样,只不过每个库表放的数据是不同的,所有库表的数据加起来就是全部数据。

水平拆分的意义,就是将数据均匀放更多的库里,然后用多个库来抗更高的并发,还有就是用多个库的存储容量来进行扩容。

水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题,但由于表的数据还是在同一台机器上,其实对于提升MySQL并发能力没有什么意义,所以 水平拆分最好分库

水平拆分能够 支持非常大的数据量存储,应用端改造也少,但 分片事务难以解决 ,跨界点Join性能较差,逻辑复杂。

不过,尽量不要对数据进行分片,因为拆分会带来逻辑、部署、运维的各种复杂度 ,一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择客户端分片架构,这样可以减少一次和中间件的网络I/O

4:水平分表:

表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询次数

适用场景

1)表中的数据本身就有独立性,例如表中分表记录各个地区的数据或者不同时期的数据,特别是有些数据常用,有些不常用。

2)需要把数据存放在多个介质上。

水平切分的缺点

1)给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需UNION操作

2)在许多数据库应用中,这种复杂度会超过它带来的优点,查询时会增加读一个索引层的磁盘次数

 

35:数据库分片的两种常见方案?

  1. 客户端代理:分片逻辑在应用端,封装在jar包中,通过修改或者封装JDBC层来实现。当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
  2. 中间件代理:在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。比如 Mycat 360Atlas、网易的DDB等等都是这种架构的实现。

 

36:分表分库后有什么问题?

1事务支持

    分库分表后,就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务,将付出高昂的性能代价;如果由应用程序去协助控制,形成程序逻辑上的事务,又会造成编程方面的负担。

2)跨库join

只要是进行切分,跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。

解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。分库分表方案产品

3)跨节点的count,order by,group by以及聚合函数问题

    这些是一类问题,因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案:与解决跨节点join问题的类似,分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行,因此很多时候它的速度要比单一大表快很多。但如果结果集很大,对应用程序内存的消耗是一个问题。

4)数据迁移,容量规划,扩容等问题

    来自淘宝综合业务平台团队,它利用对2的倍数取余具有向前兼容的特性(如对4取余得1的数对2取余也是1)来分配数据,避免了行级别的数据迁移,但是依然需要进行表级别的迁移,同时对扩容规模和分表数量都有限制。总得来说,这些方案都不是十分的理想,多多少少都存在一些缺点,这也从一个侧面反映出了Sharding扩容的难度。

5ID问题

一旦数据库被切分到多个物理结点上,我们将不能再依赖数据库自身的主键生成机制。一方面,某个分区数据库自生成的ID无法保证在全局上是唯一的;另一方面,应用程序在插入数据之前需要先获得ID,以便进行SQL路由. 一些常见的主键生成策略

UUID 使用UUID作主键是最简单的方案,但是缺点也是非常明显的。由于UUID非常的长,除占用大量存储空间外,最主要的问题是在索引上,在建立索引和基于索引进行查询时都存在性能问题。

Twitter的分布式自增ID算法Snowflake 在分布式系统中,需要生成全局UID的场合还是比较多的,twittersnowflake解决了这种需求,实现也还是很简单的,除去配置信息,核心代码就是毫秒级时间41 机器ID 10 毫秒内序列12位。

6)跨分片的排序分页

一般来讲,分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候,我们通过分片规则可以比较容易定位到指定的分片,而当排序字段非分片字段的时候,情况就会变得比较复杂了。为了最终结果的准确性,我们需要在不同的分片节点中将数据进行排序并返回,并将不同分片返回的结果集进行汇总和再次排序,最后再返回给用户。如下图所示:

 

37:现在有一个未分库分表的系统,未来要分库分表,如何设计才可以让系统从未分库分表动态切换到分库分表上?

1:停机迁移方案

先来个最简单的方案,假设大家伙儿凌晨12点开始运维,网站或者app挂个公告,说0点到早上6点进行运维,无法访问。。。。。。

接着到0点,停机,系统停掉,没有流量写入了,此时老的单库单表数据库静止了。

然后你之前得写好一个导数的一次性工具,此时直接跑起来,然后将单库单表的数据哗哗哗读出来,写到分库分表里面去。

导数完了之后,就ok了,修改系统的数据库连接配置啥的,包括可能代码和SQL也许有修改,那你就用最新的代码,然后直接启动连到新的分库分表上去。

验证一下,ok了,完美,回家吧!

这个方案比较low,谁都能干,来看看高大上一点的方案

 

2:双写迁移方案

简单来说,就是在线上系统里面,之前所有写库的地方,增删改操作,都除了对老库增删改,都加上对新库的增删改,这就是所谓双写,同时写俩库,老库和新库。

然后系统部署之后,新库数据差太远,用之前说的导数工具,跑起来读老库数据写新库,写的时候要根据gmt_modified这类字段判断这条数据最后修改的时间,除非是读出来的数据在新库里没有,或者是比新库的数据新才会写。

接着导完一轮之后,有可能数据还是存在不一致,那么就程序自动做一轮校验,比对新老库每个表的每条数据,接着如果有不一样的,就针对那些不一样的,从老库读数据再次写。反复循环,直到两个库每个表的数据都完全一致为止。

 

接着基于使用分库分表的最新代码,重新部署一次,不就基于分库分表在操作了么,停机时间很短。

 

38:如何设计可以动态扩容缩容的分库分表方案?

1:停机扩容

这个方案就跟停机迁移一样,步骤几乎一致。

 

2:优化后的方案

一开始上来就是32个库,每个库32个表,1024张表

这个分法,无论是并发支撑还是数据量支撑都没问题。

每个库正常承载的写入并发量是1000,那么32个库就可以承载32 * 1000 = 32000的写并发,如果每个库承载1500的写并发,32 * 1500 = 48000的写并发,接近5/s的写入并发,前面再加一个MQ,削峰,每秒写入MQ 8万条数据,每秒消费5万条数据。

除非是国内排名非常靠前的这些公司,他们的最核心的系统的数据库,可能会出现几百台数据库的这么一个规模。

1024张表,假设每个表放500万数据,在MySQL里可以放50亿条数据,每秒的5万写并发,总共50亿条数据,对于国内大部分的互联网公司来说,都够了。

谈分库分表的扩容,第一次分库分表,就一次性给他分个够,32个库,1024张表,可能对大部分的中小型互联网公司来说,已经可以支撑好几年了。

一个实践是利用32 * 32来分库分表,即分为32个库,每个库里一个表分为32张表。一共就是1024张表。根据某个id先根据32取模路由到库,再根据32取模路由到库里的表。

刚开始的时候,这个库可能就是逻辑库,建在一个数据库上的,就是一个mysql服务器可能建了n个库,比如16个库。后面如果要拆分,就是不断在库和mysql服务器之间做迁移就可以了。然后系统配合改一下配置即可。

 

比如说最多可以扩展到32个数据库服务器,每个数据库服务器是一个库。如果还是不够?最多可以扩展到1024个数据库服务器,每个数据库服务器上面一个库一个表。因为最多是1024个表么。

这么搞,是不用自己写代码做数据迁移的,都交给dba来搞好了,但是dba确实是需要做一些库表迁移的工作,但是总比你自己写代码,抽数据导数据来的效率高得多了。

哪怕是要减少库的数量,也很简单,其实说白了就是按倍数缩容就可以了,然后修改一下路由规则。

 

 

39:MySQL的复制原理以及流程?

主从复制:

将主数据库中的DDLDML操作通过二进制日志(BINLOG)传输到从数据库上,然后将这些日志重新执行(重做);从而使得从数据库的数据与主数据库保持一致。

主从复制的作用

  1. 主数据库出现问题,可以切换到从数据库。
  2. 可以进行数据库层面的读写分离。
  3. 可以在从数据库上进行日常备份。

MySQL主从复制解决的问题

  • 数据分布:随意开始或停止复制,并在不同地理位置分布数据备份
  • 负载均衡:降低单个服务器的压力
  • 高可用和故障切换:帮助应用程序避免单点失败
  • 升级测试:可以用更高版本的MySQL作为从库

MySQL主从复制工作原理

  • 在主库上把数据更高记录到二进制日志
  • 从库将主库的日志复制到自己的中继日志
  • 从库读取中继日志的事件,将其重放到从库数据中

基本原理流程,3个线程以及之间的关联

主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;

从:io线程——在使用start slave 之后,负责从master上拉取 binlog 内容,放进自己的relay log中;

从:sql执行线程——执行relay log中的语句;

复制过程

Binary log:主数据库的二进制日志

Relay log:从服务器的中继日志

第一步:master在每个事务更新数据完成之前,将该操作记录串行地写入到binlog文件中。

第二步:salve开启一个I/O Thread,该线程在master打开一个普通连接,主要工作是binlog dump process。如果读取的进度已经跟上了master,就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步:SQL Thread会读取中继日志,并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致。

 

40:Mysql主从同步延时问题

show statusSeconds_Behind_Master,你可以看到从库复制主库的数据落后了几ms

其实这块东西在大数据量、高并发场景下很容易碰到,比如说用了Mysql主从架构之后,可能会发现,刚写入库的数据结果没查到,结果就完蛋了。。。。

所以实际上你要考虑好应该在什么场景下来用这个mysql主从同步,建议是一般在读远远多于写,而且读的时候一般对数据时效性要求没那么高的时候,用mysql主从同步。

所以这个时候,我们可以考虑的一个事情就是,你可以用mysql的并行复制,但是问题是那是库级别的并行,所以有时候作用不是很大

所以这个时候,我们会对于那种写了之后立马就要保证可以查到的场景,采用强制读主库的方式,这样就可以保证你肯定的可以读到数据了吧。其实用一些数据库中间件是没问题的。

 

一般来说,如果主从延迟较为严重的话,可以:

1:分库,将一个主库拆分为4个主库,每个主库的写并发就500/s,此时主从延迟可以忽略不计

2:打开mysql支持的并行复制,多个库并行复制,如果说某个库的写入并发就是特别高,单库写并发达到了2000/s,并行复制还是没意义。2/8法则,很多时候比如说,就是少数的几个订单表,写入了2000/s,其他几十个表10/s

3:重写代码,插入数据之后,直接就更新,不要查询

4:如果确实是存在必须先插入,立马要求就查询到,然后立马就要反过来执行一些操作,对这个查询设置直连主库。不推荐这种方法,你这么搞导致读写分离的意义就丧失了

 

41:读写分离有哪些解决方案?

读写分离是依赖于主从复制,而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读(如果对slave执行写操作,那么show slave status将会呈现Slave_SQL_Running=NO,此时你需要按照前面提到的手动同步一下slave)。

方案一,使用mysql-proxy代理

优点:直接实现读写分离和负载均衡,不用修改代码,masterslave用一样的帐号,mysql官方不建议实际生产中使用

缺点:降低性能, 不支持事务

方案二,使用AbstractRoutingDataSource+aop+annotationdao层决定数据源。

如果采用了mybatis 可以将读写分离放在ORM层,比如mybatis可以通过mybatis plugin拦截sql语句,所有的insert/update/delete都访问master库,所有的select 都访问salve库,这样对于dao层都是透明。plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题, 也就是不支持事务,所以我们还需要重写一下DataSourceTransactionManager read-only的事务扔进读库, 其余的有读有写的扔进写库。

方案三,使用AbstractRoutingDataSource+aop+annotationservice层决定数据源,可以支持事务

缺点:类内部方法通过this.xx()方式相互调用时,aop不会进行拦截,需进行特殊处理

 

42:MySQL 数据库,一天五万条以上的增量,预计运维三年,怎么优化?

1)设计良好的数据库结构,允许部分数据冗余,尽量避免 join 查询,提高效率。

2)选择合适的表字段数据类型和存储引擎,适当的添加索引。

3MySQL 库主从读写分离。

4)找规律分表,减少单表中的数据量提高查询速度。

5)添加缓存机制,比如 Redis等。

6)书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1,field_2, field_3 FROM TABLE.