0%

面试题-Mysql

本章属于持续学习、长期更修。

数据库的三范式是什么

  • 第一范式:列不可再分

  • 第二范式:行可以唯一区分,主键约束

  • 第三范式:表的非主属性不能依赖与其他表的非主属性 外键约束

  • 且三大范式是一级一级依赖的,第二范式建立在第一范式上,第三范式建立第一第二范式上。

  • 第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。

  • 第二范式(2NF):数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖(部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况),也即所有非关键字段都完全依赖于任意一组候选关键字。

  • 第三范式(3NF):在第二范式的基础上,数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖,指的是如 果存在”A → B → C”的决定关系,则C传递函数依赖于A。因此,满足第三范式的数据库表应该不存在如下依赖关系: 关键字段 → 非关键字段 x → 非关键字段y

说下mysql有几种数据库引擎

不清楚,因为每个版本发布都会新增一些。但是我最熟悉是的innodb。

说下mysql Master slave 的原理

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

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

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

MySQL中myisam与innodb的区别

  • InnoDB支持事物,而MyISAM不支持事物
  • InnoDB支持行级锁,而MyISAM支持表级锁
  • InnoDB支持MVCC, 而MyISAM不支持
  • InnoDB支持外键,而MyISAM不支持
  • InnoDB不支持全文索引,而MyISAM支持。

innodb引擎的4大特性

  • 插入缓冲(insert buffer),
  • 二次写(double write),
  • 自适应哈希索引(ahi),
  • 预读(read ahead)
  • 支持事务操作,具有事务 ACID 隔离特性,默认的隔离级别是可重复读(repetable-read)、通过MVCC(并发版本控制)来实现的。能够解决脏读不可重复读的问题。
  • InnoDB 支持外键操作。
  • InnoDB 默认的锁粒度行级锁,并发性能比较好,会发生死锁的情况。
  • 和 MyISAM 一样的是,InnoDB 存储引擎也有 .frm文件存储表结构 定义,但是不同的是,InnoDB 的表数据与索引数据是存储在一起的,都位于 B+ 数的叶子节点上,而 MyISAM 的表数据和索引数据是分开的。
  • InnoDB 有安全的日志文件,这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题,保证数据的一致性。
  • InnoDB 和 MyISAM 支持的索引类型相同,但具体实现因为文件结构的不同有很大差异。
  • 增删改查性能方面,如果执行大量的增删改操作,推荐使用 InnoDB 存储引擎,它在删除操作时是对行删除,不会重建表。

什么是事务?

  • 事务(Transaction)是并发控制的基本单位。所谓的事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。

覆盖索引和回表

  • 覆盖索引指的是在一次查询中,如果一个索引包含或者说覆盖所有需要查询的字段的值,我们就称之为覆盖索引,而不再需要回表查询。而要确定一个查询是否是覆盖索引,我们只需要explain sql语句看Extra的结果是否是“Using index”即可。

MySQL 事务四大特性

一说到 MySQL 事务,你肯定能想起来四大特性:原子性一致性隔离性持久性。什么是 MySQL 中的事务?

事务是一组操作,组成这组操作的各个单元,要不全都成功要不全都失败,这个特性就是事务。

在 MySQL 中,事务是在引擎层实现的,只有使用 innodb 引擎的数据库或表才支持事务。

  • 原子性(Atomicity): 原子性指的就是 MySQL 中的包含事务的操作要么全部成功、要么全部失败回滚,因此事务的操作如果成功就必须要全部应用到数据库,如果操作失败则不能对数据库有任何影响。
  • 一致性(Consistency):一致性指的是一个事务在执行前后其状态一致。比如 A 和 B 加起来的钱一共是 1000 元,那么不管 A 和 B 之间如何转账,转多少次,事务结束后两个用户的钱加起来还得是 1000,这就是事务的一致性。
  • 持久性(Durability): 持久性指的是一旦事务提交,那么发生的改变就是永久性的,即使数据库遇到特殊情况比如故障的时候也不会产生干扰。
  • 隔离性(Isolation):当多个事务同时进行时,就有可能出现脏读(dirty read)不可重复读(non-repeatable read)幻读(phantom read) 的情况,为了解决这些并发问题,提出了隔离性的概念。
    • 重点说下隔离性它们分别是读未提交(read uncommitted)读已提交(read committed)可重复读(repetable read)串行化(serializable)。下面分别来解释一下。
    • 读未提交:读未提交指的是一个事务在提交之前,它所做的修改就能够被其他事务所看到。
    • 读已提交:读已提交指的是一个事务在提交之后,它所做的变更才能够让其他事务看到。
    • 可重复读:可重复读指的是一个事务在执行的过程中,看到的数据是和启动时看到的数据是一致的。未提交的变更对其他事务不可见。
    • 串行化:顾名思义是对于同一行记录,会加写锁会加读锁。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。

SQL语句的执行顺序

我们在编写一个查询语句的时候

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
  1. FROM 连接
  • 首先,对 SELECT 语句执行查询时,对FROM 关键字两边的表执行连接,会形成笛卡尔积,这时候会产生一个虚表VT1(virtual table)

首先先来解释一下什么是笛卡尔积

现在我们有两个集合 A = {0,1} , B = {2,3,4}

那么,集合 A * B 得到的结果就是

A * B = {(0,2)、(1,2)、(0,3)、(1,3)、(0,4)、(1,4)};

B * A = {(2,0)、{2,1}、{3,0}、{3,1}、{4,0}、(4,1)};

上面 A * B 和 B * A 的结果就可以称为两个集合相乘的 笛卡尔积

我们可以得出结论,A 集合和 B 集合相乘,包含了集合 A 中的元素和集合 B 中元素之和,也就是 A 元素的个数 * B 元素的个数

  1. ON 过滤
  • 然后对 FROM 连接的结果进行 ON 筛选,创建 VT2,把符合记录的条件存在 VT2 中。
  1. JOIN 连接

第三步,如果是 OUTER JOIN(left join、right join) ,那么这一步就将添加外部行,如果是 left join 就把 ON 过滤条件的左表添加进来,如果是 right join ,就把右表添加进来,从而生成新的虚拟表 VT3。

  1. WHERE 过滤
  • 第四步,是执行 WHERE 过滤器,对上一步生产的虚拟表引用 WHERE 筛选,生成虚拟表 VT4。

  • WHERE 和 ON 的区别

    • 如果有外部列,ON 针对过滤的是关联表,主表(保留表)会返回所有的列;
    • 如果没有添加外部列,两者的效果是一样的;
  • 应用

    • 对主表的过滤应该使用 WHERE;
    • 对于关联表,先条件查询后连接则用 ON,先连接后条件查询则用 WHERE;
  1. GROUP BY
  • 根据 group by 字句中的列,会对 VT4 中的记录进行分组操作,产生虚拟机表 VT5。果应用了group by,那么后面的所有步骤都只能得到的 VT5 的列或者是聚合函数(count、sum、avg等)。
  1. HAVING
  • 紧跟着 GROUP BY 字句后面的是 HAVING,使用 HAVING 过滤,会把符合条件的放在 VT6
  1. SELECT
  • 第七步才会执行 SELECT 语句,将 VT6 中的结果按照 SELECT 进行刷选,生成 VT7
  1. DISTINCT
  • 在第八步中,会对 TV7 生成的记录进行去重操作,生成 VT8。事实上如果应用了 group by 子句那么 distinct 是多余的,原因同样在于,分组的时候是将列中唯一的值分成一组,同时只为每一组返回一行记录,那么所以的记录都将是不相同的。
  1. ORDER BY
  • 应用 order by 子句。按照 order_by_condition 排序 VT8,此时返回的一个游标,而不是虚拟表。sql 是基于集合的理论的,集合不会预先对他的行排序,它只是成员的逻辑集合,成员的顺序是无关紧要的。

什么是临时表,何时删除临时表

1
2
3
4
在 MySQL 中,有三种类型的表
一种是`永久表`,永久表就是创建以后用来长期保存数据的表
一种是`临时表`,临时表也有两类,一种是和永久表一样,只保存临时数据,但是能够长久存在的;还有一种是临时创建的,SQL 语句执行完成就会删除。
一种是`虚表`,虚表其实就是`视图`,数据可能会来自多张表的执行结果。
  • MySQL 会在下面这几种情况产生临时表
    • 使用 UNION 查询:UNION 有两种,一种是UNION ,一种是 UNION ALL ,它们都用于联合查询;区别是 使用 UNION 会去掉两个表中的重复数据,相当于对结果集做了一下去重(distinct)。使用 UNION ALL,则不会排重,返回所有的行。使用 UNION 查询会产生临时表。
    • 使用 TEMPTABLE 算法或者是 UNION 查询中的视图。TEMPTABLE 算法是一种创建临时表的算法,它是将结果放置到临时表中,意味这要 MySQL 要先创建好一个临时表,然后将结果放到临时表中去,然后再使用这个临时表进行相应的查询。
    • ORDER BY 和 GROUP BY 的子句不一样时也会产生临时表。
    • DISTINCT 查询并且加上 ORDER BY 时;
    • SQL 用到 SQL_SMALL_RESULT 选项时;如果查询结果比较小的时候,可以加上 SQL_SMALL_RESULT 来优化,产生临时表
    • FROM 中的子查询;
    • EXPLAIN 查看执行计划结果的 Extra 列中,如果使用 Using Temporary 就表示会用到临时表。

介绍下InnoDB存储引擎的B+树索引

  • 每个索引都对应一棵B+树,B+树分为好多层,最下边一层是叶子节点,其余的是内节点。所有用户记录都存储在B+树的叶子节点,所有目录项记录都存储在内节点。
  • InnoDB存储引擎会自动为主键(如果没有它会自动帮我们添加)建立聚簇索引,聚簇索引的叶子节点包含完整的用户记录。
  • 我们可以为自己感兴趣的列建立二级索引,二级索引的叶子节点包含的用户记录由索引列 + 主键组成,所以如果想通过二级索引来查找完整的用户记录的话,需要通过回表操作,也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
  • B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表,而且每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话,则页面和记录先按照联合索引前边的列排序,如果该列值相同,再按照联合索引后边的列排序。
  • 通过索引查找记录是从B+树的根节点开始,一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory(页目录),所以在这些页面中的查找非常快。
  • 二者SELECT COUNT(*)哪个更快,为什么
    • myisam更快,因为myisam内部维护了一个计数器,可以直接调取。

MySQL 常见索引类型

索引是存储在一张表中特定列上的数据结构,索引是在列上创建的。并且,索引是一种数据结构。

  • 在 MySQL 中,主要有下面这几种索引
    • 全局索引(FULLTEXT):全局索引,目前只有 MyISAM 引擎支持全局索引,它的出现是为了解决针对文本的模糊查询效率较低的问题。
    • 哈希索引(HASH):哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构,很适合作为索引。HASH 索引具有一次定位的好处,不需要像树那样逐个节点查找,但是这种查找适合应用于查找单个键的情况,对于范围查找,HASH 索引的性能就会很低。
    • B-Tree 索引:B 就是 Balance 的意思,BTree 是一种平衡树,它有很多变种,最常见的就是 B+ Tree,它被 MySQL 广泛使用。
    • R-Tree 索引:R-Tree 在 MySQL 很少使用,仅支持 geometry 数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种,相对于 B-Tree 来说,R-Tree 的优势在于范围查找。

主键和候选键有什么区别?

  • 表格的每一行都由主键唯一标识,一个表只有一个主键。
  • 主键也是候选键。按照惯例,候选键可以被指定为主键,并且可以用于任何外键引用。

超键、候选键、主键、外键分别是什么?

  • 1、超键:在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
  • 2、候选键:是最小超键,即没有冗余元素的超键。
  • 3、主键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。
  • 4、外键:在一个表中存在的另一个表的主键称此表的外键。

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

  • varchar与char的区别
    • CHAR和VARCHAR类型在存储和检索方面有所不同,CHAR是一种固定长度的类型,VARCHAR则是一种可变长度的类型。
    • CHAR列长度固定为创建表时声明的长度,长度值范围是1到255当CHAR值被存储时,它们被用空格填充到特定长度,检索CHAR值时需删除尾随空格。
  • varchar(50)中50的涵义
    • 最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样)
  • int(20)中20的涵义
    • 是指显示字符的长度但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001 ~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
    • 20表示最大显示宽度为20,但仍占4字节存储,存储范围不变。
  • mysql为什么这么设计
    • 对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样。

问了innodb的事务与日志的实现方式

  1. 有多少种日志?
    1. 错误日志:记录出错信息,也记录一些警告信息或者正确的信息。
    2. 查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。
    3. 慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
    4. 二进制日志:记录对数据库执行更改的所有操作。
    5. 中继日志:中继日志也是二进制日志,用来给slave 库恢复
    6. 事务日志:重做日志redo和回滚日志undo
  2. 事务是如何通过日志来实现的。
  3. 事务日志是通过redo和innodb的存储引擎日志缓冲(Innodb log buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log sequence number)号; 当事务执行时,会往InnoDB存储引擎的日志的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”。

SQL优化explain

  • explain出来的各种item的意义;
1
select_type
  • 表示查询中每个select子句的类型,有几种值:simple(表示简单的select,没有union和子查询),primary(有子查询,最外面的select查询就是primary),union(union中的第二个或随后的select查询,不依赖外部查询结果),dependent union(union中的第二个或随后的select查询,依赖外部查询结果)
1
type
  • 表示MySQL在表中找到所需行的方式,又称“访问类型”,有几种值:system(表仅有一行(=系统表),这是const连接类型的一个特例),const(常量查询), ref(非唯一索引访问,只有普通索引),eq_ref(使用唯一索引或组件查询),all(全表查询),index(根据索引查询全表),range(范围查询)
1
possible_keys
  • 指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
1
key
  • 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
1
key_len
  • 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
1
ref
  • 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
1
Extra
  • 包含不适合在其他列中显示但十分重要的额外信息

  • profile的意义以及使用场景;

查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等。

innodb的读写参数优化

  • 读取参数
1
global buffer pool以及 local buffer;
  • 写入参数;
1
2
3
innodb_flush_log_at_trx_commit

innodb_buffer_pool_size
  • 与IO相关的参数;
1
2
3
4
5
innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_thread_concurrency = 0
  • 缓存参数以及缓存的适用场景。
1
query cache/query_cache_type

并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更

  • 第一个:读操作多的话看看比例,简单来说,如果是用户清单表,或者说是数据比例比较固定,比如说商品列表,是可以打开的,前提是这些库比较集中,数据库中的实务比较小。

  • 第二个:我们“行骗”的时候,比如说我们竞标的时候压测,把query cache打开,还是能收到qps激增的效果,当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多,访问量并不多,那么就不要打开,例如社交网站的,10%的人产生内容,其余的90%都在消费,打开还是效果很好的,但是你如果是qq消息,或者聊天,那就很要命。

  • 第三个:小网站或者没有高并发的无所谓,高并发下,会看到 很多 qcache 锁 等待,所以一般高并发下,不建议打开query cache。

表中有大字段X(例如:text类型),且字段X不会经常更新,以读为为主,请问

拆带来的问题:连接消耗 + 存储拆分空间;不拆可能带来的问题:查询性能;

  • 1、如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
  • 2、如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择。

MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的?

InnoDB是基于索引来完成行锁

1
2
3
4
-- 例:  --
select * from tab_with_index where id = 1 for update;
-- for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列--
-- 如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起--

什么是存储过程?有哪些优缺点?

存储过程是一些预编译的SQL语句。

  • 1、更加直白的理解:存储过程可以说是一个记录集,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候调用他就行了。
  • 2、存储过程是一个预编译的代码块,执行效率比较高,一个存储过程替代大量T_SQL语句 ,可以降低网络通信量,提高通信速率,可以一定程度上确保数据安全。

索引是什么?有什么作用以及优缺点?

  • 1、索引是对数据库表中一或多个列的值进行排序的结构,是帮助MySQL高效获取数据的数据结构

  • 2、索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

  • MySQL数据库几个基本的索引类型:普通索引、唯一索引、主键索引、全文索引

    1. 索引加快数据库的检索速度。
    2. 索引降低了插入、删除、修改等维护任务的速度。
    3. 唯一索引可以确保每一行数据的唯一性。
    4. 通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能。
    5. 索引需要占物理和数据空间。

使用索引查询一定能提高查询的性能吗?为什么

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

  • 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
  • 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
  • 基于非唯一性索引的检索

简单说一说drop、delete与truncate的区

SQL中的drop、delete、truncate都表示删除,但是三者有一些差别

  • delete和truncate只删除表的数据不删除表的结构
  • 速度,一般来说: drop> truncate >delete
  • delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
  • 如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。

drop、delete与truncate分别在什么场景之下使用?

  • 1、不再需要一张表的时候,用drop
  • 2、想删除部分数据行时候,用delete,并且带上where子句
  • 3、保留表而删除所有数据的时候用truncate

数据库的乐观锁和悲观锁是什么?

数据库通过锁机制来解决并发场景-共享锁(读锁)和排他锁(写锁)。读锁是不阻塞的,多个客户端可以在同一时刻读取同一个资源。写锁是排他的,并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。

  • 悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。

    • 表锁,锁定整张表,开销最小,但是会加剧锁竞争。
    • 行锁,锁定行级别,开销最大,但是可以最大程度的支持并发。
    • 共享锁(多个事物共享)共享锁也叫读锁或 S 锁。
    • 排它锁(单个事物占有)排它锁也独占锁、写锁或 X 锁
  • 乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。

    • 不过通过数据库的版本机制实现,通过版本号字段或者时间戳字段来实现。

InnoDB的MVCC的机制是什么

MVCC 的英文全称是 Multiversion Concurrency Control,中文翻译过来就是多版本并发控制技术。从名字中也能看出来,MVCC 是通过数据行的多个版本管理来实现数据库的并发控制,简单来说它的思想就是保存数据的历史版本。这样我们就可以通过比较版本号决定数据是否显示出来(具体的规则后面会介绍到),读取数据的时候不需要加锁也可以保证事务的隔离效果。

  • 通过 MVCC 我们可以解决以下几个问题:
  1. 读写之间阻塞的问题,通过 MVCC 可以让读写互相不阻塞,即读不阻塞写,写不阻塞读,这样就可以提升事务并发处理能力。
  2. 降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式,读取数据时并不需要加锁,对于写操作,也只锁定必要的行。
  3. 解决一致性读的问题。一致性读也被称为快照读,当我们查询数据库在某个时间点的快照时,只能看到这个时间点之前事务提交更新的结果,而不能看到这个时间点之后事务提交的更新结果。

在了解 InnoDB 中 MVCC 的实现方式之前,我们需要了解 InnoDB 是如何存储记录的多个版本的。这里的多版本对应的就是 MVCC 前两个字母的释义:Multi Version,我们需要了解和它相关的数据都有哪些,存储在哪里。这些数据包括事务版本号、行记录中的隐藏列和 Undo Log。

  1. 事务版本号每开启一个事务,我们都会从数据库中获得一个事务 ID(也就是事务版本号),这个事务 ID 是自增长的,通过 ID 大小,我们就可以判断事务的时间顺序。
  2. 行记录的隐藏列InnoDB 的叶子段存储了数据页,数据页中保存了行记录,而在行记录中有一些重要的隐藏字段。

MVCC 的核心就是 Undo Log+ Read View,“MV”就是通过 Undo Log 来保存数据的历史版本,实现多版本的管理,“CC”是通过 Read View 来实现管理,通过 Read View 原则来决定数据是否显示。同时针对不同的隔离级别,Read View 的生成策略不同,也就实现了不同的隔离级别。

表空间、段、区、数据页是什么?

记录是按照行来存储的,但是数据库的读取并不以行为单位,否则一次读取(也就是一次 I/O 操作)只能处理一行数据,效率会非常低。因此在数据库中,不论读一行,还是读多行,都是将这些行所在的页进行加载。也就是说,数据库管理存储空间的基本单位是页(Page)。

  1. 一个页中可以存储多个行记录(Row),同时在数据库中,还存在着区(Extent)、段(Segment)和表空间(Tablespace)。行、页、区、段、表空间的关系如下图所示:

快照读与当前读区别

  1. 快照读读取的是快照数据。不加锁的简单的 SELECT 都属于快照读,比如这样:
1
SELECT * FROM player_users WHERE ...
  1. 当前读就是读取最新数据,而不是历史版本的数据。加锁的 SELECT,或者对数据进行增删改都会进行当前读,比如:
1
2
-- 这里加的是读锁。
SELECT * FROM player_users LOCK IN SHARE MODE;

谈谈 SQL 优化的经验

  • 查询语句无论是使用哪种判断条件 等于、小于、大于WHERE 左侧的条件查询字段不要使用函数或者表达式
  • 使用 EXPLAIN 命令优化你的 SELECT 查询,对于复杂、效率低的 sql 语句,我们通常是使用 explain sql 来分析这条 sql 语句,这样方便我们分析,进行优化。
  • 当你的 SELECT 查询语句只需要使用一条记录时,要使用 LIMIT 1
  • 不要直接使用 SELECT *,而应该使用具体需要查询的表字段,因为使用 EXPLAIN 进行分析时,SELECT * 使用的是全表扫描,也就是 type = all
  • 为每一张表设置一个 ID 属性
  • 避免在 WHERE 字句中对字段进行 NULL 判断
  • 避免在 WHERE 中使用 !=<> 操作符
  • 使用 BETWEEN AND 替代 IN
  • 为搜索字段创建索引,大字段类型不要建立索引。
  • 选择正确的存储引擎,InnoDB 、MyISAM 、MEMORY 等
  • 使用 LIKE %abc% 不会走索引,而使用 LIKE abc% 会走索引
  • 对于枚举类型的字段(即有固定罗列值的字段),建议使用ENUM而不是VARCHAR,如性别、星期、类型、类别等
  • 拆分大的 DELETE 或 INSERT 语句
  • 选择合适的字段类型,选择标准是 尽可能小、尽可能定长、尽可能使用整数
  • 字段设计尽可能使用 NOT NULL
  • 进行水平切割或者垂直分割

开放性问题

一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

  • 1、如果A表TID是自增长,并且是连续的,B表的ID为索引
1
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
  • 2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
1
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

MySQL binlog的几种日志录入格式以及区别

Statement:每一条会修改数据的sql都会记录在binlog中。

  • 优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能 与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条 件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所 产生的日志量会增加多少,以及带来的IO性能问题。)

  • 缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的 一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题(如sleep()函数, last_insert_id(),以及user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制

  • LOAD_FILE()
  • UUID()
  • USER()
  • FOUND_ROWS()
  • SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

同时在INSERT …SELECT 会产生比 RBR 更多的行级锁

Row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。

  • 优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题

  • 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比 如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

  • Mixedlevel: 是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择 一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。

MySQL数据库cpu飙升到500%的话他怎么处理?

  • 列出所有进程 show processlist,观察所有进程 ,多秒没有状态变化的(干掉)
  • 查看超时日志或者错误日志 (一般会是查询以及大批量的插入会导致cpu与i/o上涨,当然不排除网络状态突然断了,导致一个请求服务器只接受到一半,比如where子句或分页子句没有发送,,当然的一次被坑经历)。

备份计划,mysqldump以及xtranbackup的实现原理

  • 备份计划

    • 这里每个公司都不一样
  • 备份恢复时间,这里跟机器,尤其是硬盘的速率有关系,以下列举几个仅供参考

    • 20G的2分钟(mysqldump)

    • 80G的30分钟(mysqldump)

    • 111G的30分钟(mysqldump)

    • 288G的3小时(xtra)

    • 3T的4小时(xtra)

    • 逻辑导入时间一般是备份时间的5倍以上

  • xtrabackup实现原理

    • 在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

mysqldump中备份出来的sql,如果我想sql文件中,一行只有一个insert….value()的话,怎么办?如果备份需要带上master的复制点信息怎么办?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--skip-extended-insert

[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insert

Enter password:

KEY `idx_c1` (`c1`),

KEY `idx_c2` (`c2`)

) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `helei`

--

LOCK TABLES `helei` WRITE;

/*!40000 ALTER TABLE `helei` DISABLE KEYS */;

INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');

INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');

INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');

你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?

  • 主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等。

你们数据库是否支持emoji表情,如果不支持,如何操作?

  • 如果是utf8字符集的话,需要升级至utf8_mb4方可支持。

什么是视图?以及视图的使用场景有哪些?

  • 1、视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。
  • 2、只暴露部分字段给访问者,所以就建一个虚表,就是视图。
  • 3、查询的数据来源于不同的表,而查询者希望以统一的方式查询,这样也可以建立一个视图,把多个表查询结果联合起来,查询者只需要直接从视图中获取数据,不必考虑数据来源于不同表所带来的差异。