来一点mysql的面试题

来一点mysql的面试题

数据库的三范式是什么

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。

在关系型数据库中这种规则就称为范式。

范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式

• 第一范式：每列保持原子性
  • 即：列不可再分
  • 如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式
  • 例如：将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便
• 第二范式：表中的每列都和主键相关
  • 即：行可以唯一区分——主键约束
  • 比如要设计一个订单信息表，因为订单中可能会有多种商品，如果将订单编号和商品编号作为数据库表的联合主键，看起来可以，但是不满足第二范式
    • 这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
    • 而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就满足第二范式了
• 第三范式：每一列数据都和主键直接相关，而不能间接相关
  • 表的非主属性不能依赖与其他表的非主属性——外键约束
  • 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。
    • 而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段，减小了数据冗余

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

简述索引的类型和对性能的影响

• 普通索引：

  • 允许被索引的数据列包含重复的值。

• 唯一索引：

  • 可以保证数据记录的唯一性。

• 主键索引：

  • 是一种特殊的唯一索引， 在一张表中只能定义一个主键索引，主键用于唯一地标识一条记录
  • 使用关键字PRIMARY KEY来创建。

• 联合索引：

  • 索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引

• 全文索引：

  • 通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎ES使用的一种关键技术。
  • 倒排索引：一个索引由文档中所有不重复的列表组成，对于每一个词都有包含它的一个文档列表
    • 即：将所有文档拆分成一个个独立的单词(称之为词条或者tokens)，然后根据这些词条创建一个了包含了所有词条与之对应的文档且不重复的排序列表
    • 当进行搜索时，会根据词条匹配到对应的文档，当匹配度越高的文档，权重越高，即score越高
  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

优点：

• 索引可以极大的提高数据的查询速度
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

• 会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间
  • 如果要建立聚簇索引，那么需要的空间就会更大
  • 如果非聚集索引很多, 一旦聚集索引改变，那么所有非聚集索引都会跟着变。

索引失效的7种条件

索引失效是建立了索引，但是没有用上

可以看一下博文《索引失效和注意事项》，总结的比较全面

下面是可能出现索引失效的情况

索引失效口诀：模型数空运最快

• 模：模糊查询LIKE以%开头
• 型：数据类型错误
• 数：对索引字段使用内部函数
• 空：索引列是NULL
• 运：索引列进行四则运算
• 最：复合索引不按索引列最左开始查找
• 快：全表查找预计比索引更快

1、单独引用复合索引里非第一位置的索引列

复合索引遵守“最左前缀”原则

即在查询条件中使用了复合索引的第一个字段，索引才会被使用。

因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

假如有INDEX(a,b,c)，
当条件为 a 或 a,b 或 a,b,c 时都可以使用索引，
但是当条件为 b,c 时将不会使用索引。

因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减

实际上是会去索引文件里查的，type为index

2、对索引列进行运算

运算包括（+、-、*、/、！、<>、!=、%、or、in、exist等），导致索引失效

or导致索引失效：使用union

注意or也是会导致索引失效的，所以要么在业务中进行运算，要么在mysql中使用union

union 用于把来自多个select 语句的结果组合到一个结果集合中

当使用union时，mysql 会把结果集中重复的记录删掉

使用union all时，mysql 会把所有的记录返回，且效率高于union

select * from t_index where uid ='921930193014' or balance = 499010

# ↑改为↓，使用union

select * from t_index where uid ='921930193014'
UNION 
select * from t_index where balance = 499010

错误的例子：select * from test where id-1=9;

正确的例子：select * from test where id=10;

注意！！

sql 中如果使用了 not in ， not exists ， <> ， ！= 这些不走索引 range类型的查询条件后的字段索引无效

< ， > ， <= ， >= ，这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引

range类型的范围查询字段及其后面的素引无效

什么是range类型的查询条件及其后面的字段索引无效？

我们来看一个例子

t_index表有140w的数据，结构如下

CREATE TABLE `t_index` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `balance` int(10) unsigned zerofill NOT NULL,
  `views` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1400001 DEFAULT CHARSET=utf8;

CREATE index index_tindex_username_balance_views on t_index(username,balance,views)

EXPLAIN
select * from t_index where username = 'while' and balance >= 499008 ORDER BY views DESC limit 1

建立username,balance,views3个字段的索引，sql查询如上，我们来查看这个sql的效果

可以看见，虽然使用到了3字段索引，可是查询的type还是range级别的，而且extra中是using filesort外部文件排序，没有使用到views的索引排序，当然效率会低一些

type变成了range,这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

但是我们已经建立了索引,为啥没用索引的排序呢?

这是因为按照BTree索引的工作原理,

先排序username

如果遇到相同的username，则再排序balance，如果遇到相同的balance，则再排序views

当balance字段在联合索引里处于中间位置时,

因balance > 499008 的条件是一个范围值(所谓range)

MySQL无法利用索引再对后面的views部分进行检索，即range类型范围查询字段及其后面的素引无效

那我们如何解决这个问题呢？去掉范围条件字段的索引，即：减去balance的索引列

只添加username和views的索引

DROP index index_tindex_username_balance_views on t_index

CREATE index index_tindex_username_views on t_index(username,views)

EXPLAIN
select * from t_index where username = 'while' and balance >= 499008 ORDER BY views DESC limit 1

可以看到type变为ref，ref显示const常量，extra也没有了using filesort

优化器根据索引顺序优化

当然，mysql的优化器是会根据索引顺序进行对应的优化的

（mysql不完全顺从sql语句的条件顺序，会根据索引来执行最优顺序达到最优结果）

我们来看这样一张表，有字段a ，b， c， d

并为abcd同时创建多列索引

CREATE TABLE `abcd` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `a` varchar(255) NOT NULL,
  `b` varchar(255) NOT NULL,
  `c` varchar(255) NOT NULL,
  `d` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `i` (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

# 多执行几次插入用于测试数据
insert into abcd(a,b,c,d) values('a','b','c','d');

CREATE index i on abcd(a,b,c,d)

我们来看一下下面4条sql的执行情况

1、正常顺序a-b-c-d

a='a' and b='b' and c='c' and d='d'

EXPLAIN
select * from abcd where a='a' and b='b' and c='c' and d='d'

那当然是type为ref啦，正常使用索引，ref显示4个const，表示这4个全都走了索引

2、条件倒序d-c-b-a

d='d' and c='c' and b='b' and a='a'

EXPLAIN
select * from abcd where d='d' and c='c' and b='b' and a='a'

和正常顺序没有区别，因为优化器将顺序调整为了和索引一致的a-b-c-d

3、a-b 范围d 再c

上面我们提到，调整为了与索引一致的a-b-c-d顺序，那么我们如果将d提到c前面，并使用范围索引，那会不会导致c的索引失效呢？

结果是：不会的

EXPLAIN
select * from abcd where a='a' and b='b' and d>='d' and c='c'

我们可以看见，ref还有3个const，因为ref显示索引的哪一列被使用了，所以我们可以得出

这条sql一定是被优化成了where a='a' and b='b' and c='c' and d>='d'

所以a、b、c都走了索引，d因为范围查询没有走

4、a-b 范围c 再d

如果还是a-b-c-d的顺序，然后c使用范围查询，d还走索引嘛？

那当然不走了，上文提到了范围查询字段后面的素引无效，所以c和d都不走索引了

那ref应该只剩2个const了，即a和b，让我们来看看结果

EXPLAIN
select * from abcd where a='a' and b='b' and c>='c' and d='d'

结果无误，ref只有2个const，即a和b走索引，c范围查询不走且导致后面的d也不走索引

5、a-b-d 并根据c排序

直接上代码

EXPLAIN
select * from abcd where a='a' and b='b' and d='d' ORDER BY c

此时会用到几个索引？2个、3个还是4个？

答案可以说是3个

可能有人会说这不是只有2个const嘛，其实严格上来说，用到了c字段索引来排序而不是查找，故没统计

（如果c没走索引，应该是显示using filesort，见例6）

而这个const指的是a和b的常量

6、a-b 根据d排序

EXPLAIN
select * from abcd where a='a' and b='b' ORDER BY d

中间c断了，d当然不走索引了，所以只有a和b走了索引

所以在extra中显示了using filesort

7、a 根据c、b排序

我们先说一下where a='a' ORDER BY b,c用到了几个索引？

3个，a、b、c都用到了

根据顺序来的，a用于查找，b、c用于排序，无Using filesort，用到了3个索引，没毛病把

进入正题，下列sql用到了几个索引？

1个，只有a用到了，c和b没有用到，因为不符合顺序，在order by中严格遵守顺序

所以出现了Using filesort

EXPLAIN
select * from abcd where a='a' ORDER BY c,b

3、对索引应用内部函数

这种情况下应该建立基于函数的索引

select * from template t where ROUND(t.logicdb_id) = 1;

此时应该建ROUND(t.logicdb_id)为索引。

4、类型错误

如字段类型为varchar，where条件用number

例：template_id字段是varchar类型。

错误写法：select * from template t where t.template_id = 1

正确写法：select * from template t where t.template_id = ‘1’

值得一提的是，这个类型错误的失效条件并不是类型不同就一定索引失效

我们写一个例子，t_user表的uid的类型为int，student表的id的类型为varchar

分析一下下面两个语句是否使用了索引

# 失效
EXPLAIN select * from student where id = 1;

# 主键索引
EXPLAIN select * from t_user where uid = '1';

另外提一嘴：

varchar的查询条件为数字时，会变成字符串截取数字来查询

int的查询条件为字符串时，会隐式地将数字转换为字符串来查询，所以会走索引

在mysql中的varchar并不是使用的equals的比较，而是字符串截取了

此时我们存在一条数据id='1'的数据，我们使用where id = 1能不能查询出来数据呢？

是可以的

如果id='123xxx'的数据，使用where id = 123能不能查询出来呢？

同样是可以的

可是id = 'xx123'使用where id = 123就查询不出来了

存在一个uid = 1，使用where uid = '1'时，是能查询出来的，且能走索引（见前几张带explain查询的图）

5、如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

6、like的模糊查询以%开头，索引失效

like “%aaa%” 不会使用索引

like “aaa%” 可以使用索引

7、索引列没有限制 not null

只要列中包含有NULL值都将不会被包含在索引中

复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的

为什么mysql要使用B+树（B和B+树的区别）（重要）

B树和B+树的区别

B树图示

B+树图示

背景板浅蓝色的块我们称之为一个磁盘块

可以看到每个磁盘块包含几个数据项(深蓝色小块)和指针(黄色小块) ,

如磁盘块1包含数据项17和35，包含指针P1、 P2、 P3

B树的特点：每个节点都有数据和指针

• 节点排序
• 一个节点可以存多个元索，多个元索也排序了
• 每个节点都存储key和data

B+树的特点：数据只存在于叶子节点

• 拥有B树的特点

• 非叶子节点上的都是索引指针，叶子节点中存储了所有的元素与索引，并且排好了顺序

• 叶子节点之间有指针

  • 增加了顺序访问指针 ，每个叶子节点增加一个指向相邻叶子节点的指针

• 只有叶子节点存储data，叶子节点包含了这棵树的所有索引和数据

在B+树中

真实的数据存在于叶子节点，即5,8,9,10,15,18,20,26,27,28,30,33,35,38,50,56,60,63,65,73,79,80,85,88,90,96,99

非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，例如5、28、65并不真实存在于数据表中

使用了B+树有什么优势

• 索引使用的是B+树，因为索引是用来加快查询的，而B+树通过对数据进行排序所以是可以提高查询速度的
• 然后通过存储多个元索在叶子节点中，从而可以使得B+树的高度不会太高
• 在Mysql中一个Innodb页就是一个B+树节点，一个Innodb页默认16 KB，所以一般情况下一颗两层的B+树可以存2000万行左右的数据
• 通过利用B+树叶子节点存储了所有数据并且进行了排序，并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等sql语句

索引的基本原理

索引是一种排好序的快速查找的数据结构

索引的原理：把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

mysql聚簇索引和非聚簇索引是什么？有什么区别？

聚簇索引

聚簇索引是物理索引，数据表就是按顺序存储的，物理上是连续的。

一旦创建了聚簇索引，表中的所有列都根据构造聚簇索引的关键列来存储。

**所有的记录行都根据聚簇索引顺序存储，如按照主键Id递增方式依次物理顺序存储 **

因为聚簇索引是按该列的排序存储的，因此一个表只能有一个聚簇索引

非聚簇索引

所有不是聚簇索引的索引都叫非聚簇索引或者辅助索引。

在InnoDB存储引擎中，每个非聚簇索引的每条记录都包含主键，也包含非聚簇索引指定的列

MySQL使用这个主键值来检索聚簇索引，这个过程其实就是回表

不懂的话可以看下面的那个主键索引树和字段索引树的图片，清晰明了

区别

• 数据存放顺序不同
  • 聚簇索引，索引的顺序就是数据存放的顺序，即聚簇索引是物理顺序，只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。
  • 非聚簇索引通过叶子节点指针找到数据页中的数据，所以非聚簇索引是逻辑顺序
• 个数不同
  • 一张数据表只能有一个聚簇索引
  • 一张数据表可以有多个聚簇索引

聚簇索引的特点

• 如果一个主键被定义了，那么这个主键就是作为聚集索引
• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增（row_id）
• 自增主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题
• 聚集索引的排序必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的。
• 而如果聚集索引上的值可以改动的话，那么也会触发物理磁盘上的移动，于是就可能出现page分裂， 表碎片横生。所以不应该修改聚集索引

为什么主键索引比普通索引性能高？mysql的回表是什么？

数据假设

我们以下表的数据为例

主键id的索引树

包含了一个列中所有的数据

字段address的普通索引树

只包含了address字段和主键，根据字段拿到id后，再去上图的主键id树中拿到其他列的值，这就有了两次查询

第二次的从主键索引树中的查询就是回表查询

什么是回表？

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，需要查询两遍树

即基于非主键索引的查询需要查询出主键后，再去扫描主键索引树拿到值，这个从主键索引树的查询就称之为回表

以查询address字段数据chengdu为例，从address索引树中拿到id为8，再根据id=8去主键索引树中，拿到其他的字段数值

主键索引效率更快的原因

而主键索引树包含了列所有的数据，所以根据主键查询时可以一次性拿到所有数据，只需要查询一次，比普通索引的两次查询效率当然更快

索引覆盖

所以对于优化来说

• 可以通过主键id，直接在主键索引树获取到行中的字段数据，总共只需查询一遍树
  • 即通过主键查询提高MySQL查询速度
• 如果只需主键及其索引的字段，直接在普通索引树中就可以获取到数据，而无需回表，我们称之为索引覆盖
  • 在某些场景下，将需要的列都加入到组合索引，则可以实现索引覆盖，而无回表查询

MyISAM与InnoDB对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁。即使操作一条记录也会锁住整个表，不适合高并发！	行锁。操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能，读	事务，写
默认安装	Y	Y
适合场景	对事务的完整性没有要求，或以select、insert为主的应用基本都可以选用MYISAM。在Web、数据仓库中应用广泛	应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除、更新，那InnoDB是最好的选择。在计费系统、财务系统等对数据的准确性要求较高的系统中被广泛应用

mysql锁有哪些？如何理解

按锁粒度分类:

• 行锁：锁某行数据，锁粒度最小,并发度高
• 表锁：锁整张表，锁粒度最大,并发度低
• 间隙锁：锁的是一个区间

还可以分为:

• 共享锁：也就是读锁，一个事务给某行数据加了读锁，其他事务也可以读，但是不能写
• 排它锁：也就是写锁，一个事务给某行数据加了写锁，其他事务不能读，也不能写

还可以分为:

• 乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的
• 悲观锁：上面所的行锁、表锁等都是悲观锁

在事务的隔离级别实现中，就需要利用锁来解决幻读

事务的基本特性和隔离级别（重要）

基本特性 ACID

事务的基本特性就是ACID

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：

• 原子性（atomicity，[ætəˈmɪsəti]，或称不可分割性）
  • 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
• 一致性（consistency，[kənˈsɪstənsi]）
  • 在事务开始之前和事务结束以后，数据库的完整性没有被破坏
• 隔离性（isolation，[aɪsəˈleɪʃn]，又称独立性）
  • 一个事务的修改在最终提交前，对其他事务是不可见的
  • 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以**防止多个事务并发执行时由于交叉执行而导致数据的不一致 **
    • 事务隔离分为4个不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
• 持久性（durability，[djʊərəˈbɪlɪti]）
  • 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

我们需要根据事务的原子性、隔离性和持久性来保证数据的一致性

那么多个事务访问同一个资源时，如何保证资源的可见性呢，这里需要谈到隔离级别了

4个隔离级别

• read uncommit ：读未提交也叫做脏读，可能会读到其他事务未提交的数据
  • A向B转账，B本来有300，如果收了200就为500了，但是有可能事务会出现异常则修改失败应该回滚为300，可是此时另一个事务读取B的余额为500的脏数据，这就是脏读
• read commit ：读已提交也叫做不可重复读，两次读取结果不一致，oracle的默认级别
  • 不可重复读解决了脏读的问题，他只会读取已经提交的事务
  • 用户开启事务读取id=1的用户，查询到age=10，其他事务修改并提交了之后，再次读取发现结果=20
  • 在同一个事务里同一个查询读取到不同的结果叫做不可重复读
• repeatable read ：可重复读，这是mysql的默认级别，就是每次读取结果都一样
  • 但是有可能产生幻读，使用间隙锁解决
• serializable ：串行，一般不使用。给每一行读取的数据加锁， 会导致大量超时和锁竞争的问题

隔离级别中可能出现的问题

• 脏读(Drity Read)：可以给数据上写锁，让其他事务不能并发读和并发写，解决了脏数据的问题
  • 某个事务已更新一份数据， 另一个事务在此时读取了同一 份数据，由于某些原因，前一个回滚了操作，则后一个事务所读取的数据就会是不正确的。
• 不可重复读(Non-repeatable read)：可以给数据上读锁，让其他事务可以并发读，但是不能写，解决了读取结果不一致的问题
  • 在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读(Phantom Read)：使用间隙锁解决幻读问题（for update）
  • 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做幻行
  • 例如事务1查询到3条记录但是不commit提交，事务2insert后就commit提交了，事务1再查询还是3条，但是执行全表update时却更新了4条，在更新完后再查询select，发现查询出来了4条数据，那么这就是产生了幻读，多出来的那条数据就是幻行
    • 因为select是快照读，而update是当前读
    • 如果加上锁之后，别的事务无法操作数据，也就不会出现幻读问题了
    • 间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的

ACID是靠什么保证的

• 原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

• 一致性由其他三大特性保证，程序代码要保证业务层面上的数据一致性

• 隔离性由MVCC来保证

• 持久性由内存、redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

  • redo log的刷盘会在系统空闲时进行

  • 将新数据写入内存，然后对InnoDB的redo log写盘，redolog中事务进入prepare状态

    如果前面prepare成功，bin log写盘，将事务日志持久化到bin log

    如果持久化成功，那么在redo log里面写一个commit 记录，表示InnoDB事务进入commit状态

  • 如果redolog写入完，变为prepare状态，可是断电了，没来得及写入bin log的话，在重新开启后会抛弃已经prepare的事务（因为它没有完成提交，所以不作数）

在innodb中有两个日志，undo log和redo log

在mysql server中有一个叫bin log的日志，也和事务有关

事务的成功不仅要在bin log中留下记录，而且也在redo log中写入commit提交状态的记录，表示事务已经完成提交

什么是MVCC

MVCC，全称 Multi-Version Concurrency Control ，即多版本并发控制

读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据——版本链

MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存

MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

MVCC只在RC和RR两钟隔离级别下工作，即read commit不可重复读和repeatable read可重复读

脏读每次读的都是最新的数据，不符合

串行会给所有读取的行都加锁，也不符合

一般我们认为MVCC有下面几个特点：

• 每行数据都存在一个版本，每次事务更新数据时都更新该版本号
• 修改时Copy出当前版本随意修改，和事务之间无干扰
• 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

在InnoDB中，会在每行数据后添加两个额外的隐藏的字段来实现MVCC ，一条记录除了包括各个字段值，还包括了当前事务id（trx_id）和一个回滚指针（roll_pointer）。

• trx_id：生成这条记录（update/delete）的事务id
• roll_pointer：指向了undo_log中保存着的原来的记录，根据一条条的记录中的回滚指针从而构成版本链

注：一个事务的事务id在第一次insert/delete/update时生成

readview是什么

其实Read View的最大作用是用来做可见性判断的

也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据

有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据

事务开启后会创建readview，用于维护当前活动的事务的id（活动=未提交的事务），并排序生成一个数组

readview中存储了当前Read View中最大的ID及最小的ID

之前提到了在数据的隐藏列中trx_id用于储存了事务id（获取的是最大的那个id，即最新的id），在读取数据时，会拿到这个事务id去和readview中的比对

• up_limit_id是当时活跃事务列表的最小事务id

  • 如果row的db_trx_id<up_limit_id，说明这些数据在事务创建id的时都已经提交，如注释中的描述，这些数据均可见
  • 小于 可见

• ow_limit_id是当时活跃事务的最大事务id

  • 如果读到row的db_trx_id>=low_limit_id，说明这些id在此之前的数据都没有提交，如注释中的描述，这些数据都不可见
  • 大于等于 不可见

MVCC是如何实现不可重复读和可重复读的

读已提交（不可重复读）隔离级别下的事务在查询的开始会创建一个独立的readview

可重复读隔离级别下的事务在第一次读的时候生成一个readview，之后的读都是复用的这个readview

即：通过不同的readview生成策略来实现不同的隔离级别

mysql慢查询如何优化

• 检查是否走了索引，如果没有则优化SQL利用索引
• 检查所利用的索引，是否为最优索引
• 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
• 检查表中数据是否过多，是否应该进行分库分表了
• 检查数据库实例所在机器的性能配置，如果太低是否可以适当增加资源

EXPLAIN字段中分别代表什么意思

列名	描述
id	执行等级。MySQL会为每个select都分配一个的id值，id越大的table越先执行
select_type	SELECT关键字对应的那个查询的类型
table	表名
type	显示用到的查询类型。一般来说，至少保证查询要在range级别，最好能达到ref
possible_keys	可能用到的索引
key	实际用到的索引
key_len	实际用到的索引长度
ref	显示被使用到的索引列
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
extra	一些额外的信息，比如using filesort等

索引覆盖是什么

索引覆盖就是一个SQL在执行时，可以利用索引来快速查找，并且此SQL所要查询的字段在当前匪聚簇索引对应的字段中都包含了，那么就表示此SQL走完索引后不用回表了

所需要的字段都在当前索引的叶子节点上存在,可以直接作为结果返回了

什么是索引下推

对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数

提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能

举例

name和age联合索引情况下

select * from table where name= zhangsan' and age=12;

没有索引下推之前：

先根据name从存储引擎中拉取数据到server层，然后在server层中对age进行数据过滤

有了索引下推之后：

根据name和age两个条件储存引擎中来做数据筛选，将筛选之后的结果返回给server层

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来回表查询过滤记录；

在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

最左前缀原则是什么

当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段

比如针对a，b，c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引

这是由于在建立a，b，c三个字段的联合索引时，底层的B+树是按照a，b，c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则

InnoDB是如何实现事务的

Innodb通过Buffer Pool，LogBuffer，Redo Log，Undo Log来实现事务

以一个update语句为例：

1. Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中
2. 执行update语句，修改Buffer Pool中的数据，也就是内存中的数据
3. 针对update语句，生成一个RedoLog对象， 并存入LogBuffer中
4. 针对update语句，生成undo log日志,用于事务回滚
5. 如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中
6. 如果事务回滚，则利用undo log日志进行回滚

redis和mysql数据如何保持一致

• 先m再r：
  • 先更新Mysql，再更新Redis，如果更新Redis失败，可能仍然不一致
• 先r再m：
  • 先删除Redis缓存数据，再更新Mysql，再次查询的时候在将数据添加到缓存中，这种方案能解决1方案的问题，但是在高并发下性能较低
  • 而且仍然会出现数据不一致的问题，比如线程1删除了Redis缓存数据，正在更新Mysql，此时另外一个线程再查询，那么就会把Mysql中老数据又查到了Redis中
• 延时双删：先r再m再r
  • 先删除Redis缓存数据，再更新Mysql, 延迟几百毫秒再删除Redis缓存数据，这样就算在更新Mysql时，有其他线程读了Mysql,把老数据读到了Redis中，那么也会被删除掉，从而把数据保持一致

mysql主从同步原理

在单机遇到性能瓶颈时，就会用到主从部署或者读写分离、集群等

当涉及到多个节点时，就会有一个数据不一致的问题需要解决，那么我们就可以使用主从同步来解决数据不一致的问题

mysql主从同步的过程：

Mysql的主从复制中主要有三个线程：

• master (binlog dump thread)，master节点的一个线程

• slave (I/O thread 、SQL thread)，slave节点的两个线程

• 主节点binlog日志

  • 是主从复制的基础，主库记录数据库的所有变更记录到binlog
  • binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。

• 主节点log dump线程

  • 当binlog有变动时，log dump线程读取其内容并发送给从节点。

• 从节点I/O线程

  • 接收binlog内容，并将其写入到relay log中继日志文件中。

• 从节点的SQL 线程

  • 读取relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用binlog文件+ position偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从position的位置发起同步。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理

这样会产性一个问题，假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

• 全同步复制
  • 主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响
• 半同步复制
  • 和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成

MyISAM与InnoDB区别

MyISAM：

• 不支持事务，但是每次查询都是原子的
• 支持表级锁，即每次操作是对整个表加锁
• 存储表的总行数
• 一个MYISAM表有三个文件：索引文件、表结构文件、数据文件
• 采用非聚集索引，因为是非聚集索引，所以索引和数据是分开的
  • 索引文件的数据域存储指向数据文件的指针，所以每次查询都一定会有回表查询（通过查索引的数据地址拿到数据）
  • 辅索引与主索引基本一致,但是辅索引不用保证唯一性

InnoDb：

• 支持ACID的事务，支持事务的四种隔离级别
• 支持行级锁及外键约束：因此可以支持写并发
• 不存储总行数
• 一个InnoDb引擎存储在一个或多个文件空间
  • 一个文件空间：共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里
  • 多个文件空间：设置为独立表空，表大小受操作系统文件大小限制，一般为2G
• 主键索引采甩聚集索引(索引的数据域存储数据文件本身)
  • 辅索引的数据域存储主键的值
  • 因此从辅索引查找数据,需要先通过辅索引找到主键值，再访问辅索引
  • 最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁。即使操作一条记录也会锁住整个表，不适合高并发！	行锁。操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能，读	事务，写
默认安装	Y	Y

可以看出来，因为myisam是表锁，占用小，所以会更适合读的操作一些

而innodb的行锁对于事务并发等更优秀，适合写操作

所以读写分离时，myisam更适合做读库，innodb更适合做写库

什么是视图

视图是一种虚拟的表，具有和物理表相同的功能。

可以对视图进行增，改,查,操作，视图通常是有一个表或者多个表的行或列的子集。

对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易

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

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

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