MySQL面试

mysql面试题

一口气搞懂 MySQL 索引所有知识点

1.什么是索引？

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。索引是一种用于快速查询和检索数据的数据结构。

我们可以简单理解为：快速查找排好序的一种数据结构。

索引存储在文件系统中，索引的文件存储形式与存储引擎有关
Mysql索引主要有两种结构：B+Tree索引和Hash索引。

我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。

2.为什么要有索引呢？

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

建立索引的目的是：希望通过索引进行数据查找减少随机 IO，增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

索引的优缺点

优点 ：

• 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

如何选择索引列的顺序

• 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
• 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
• 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

索引类型

1、主键索引：数据表的主键列使用的就是主键索引。
一张数据表只能有一个主键，并且主键不能为 null。在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的 自增主键。

2、二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

唯一索引：索引列中的值必须是唯一的，但是允许为空值。

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

前缀索引：前缀索引是对文本的前几个字符创建索引。前缀索引只适用于字符串类型的数据

全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。

组合索引：组合索引的使用，需要遵循最左前缀匹配原则。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

聚集索引与非聚集索引

1、聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

优点：聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点

• 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

2、非聚集索引

非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚集索引的叶子节点是不存放数据的，更新代价比聚集索引要小。

缺点：可能会回表查询， 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

非聚集索引一定回表查询吗(覆盖索引)?

非聚集索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='guang19';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

磁盘I/O

磁盘IO与预读
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，innodb每次读取16KBd的数据也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

局部预读性原理

磁盘预读每次从磁盘读取4K的整数倍的数据，innoDB默认4K*4=16K的数据

向磁盘中存储和读取的过程叫磁盘 I/O。磁盘的读取方式和速度会严重影响到整个业务的计算性能。

局部性原理：数据和程序都有聚集成群的倾向，分为空间局部性和时间局部性

磁盘预读：内存跟磁盘在进行交互的时候要保证每次读取需要一个逻辑单位，而这个逻辑单位叫做页，或者叫datapage，一般是4K或者8K，在进行读取的时候一般都是4K的整数倍，innodb每次读取16kb数据

• 尽量减少 I/O 次数，比如可以使用缓存；
• 每次 I/O 尽量获取更多的数据；
• 每次 I/O 尽量获取有用的数据，当然相应的也间接减少总 I/O 次数；

索引的数据结构

哈希表

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据，Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

哈希索引的限制

• 基于哈希表的实现，只有精确匹配索引所有列的查询才有效，不支持任何范围查询
• 在mysql中，只有memory的存储引擎显式支持哈希索引
• 哈希索引自身只需要存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快
• 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
• 哈希索引数据并不是按住索引值的顺序存储的，所以无法进行排序
• 哈希索引不支持部分列匹配查找，哈希索引式使用索引列的全部内容来计算哈希值
• 哈希索引支持等值比较查询，不支持任何范围查询
• 哈希索引的数据非常快，除非有很大哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
• 哈希冲突比较多的话，维护的代价也会很高
• Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高。

哈希索引使用场景：如果一个表存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引

但是！哈希算法有个 Hash冲突问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

Hash 索引不支持顺序和范围查询是它最大的缺点

二叉树

二叉搜索树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

无论是二叉树还是红黑树，都会因为树的深度过深而造成io次数变多，影响数据读取的效率。

B树（改造二叉树）

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

索引数据与树排序查找无关的业务一起维护在节点的平衡多叉树称为 B- 树（ B 树）。

多叉树是在二分查找树的基础上，增加单个节点的数据存储数量，同时增加了树的子节点数，一次计算可以把查找范围缩小更多。

查找数据28


优点：二叉平衡树的基础上，使加载一次节点，可以加载更多路径数据，同时把查询范围缩减到更小。

缺点：

• 不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。
• 业务数据的大小可能远远超过了索引数据的大小，每次为了查找对比计算，需要把数据加载到内存以及 CPU 高速缓存中时，都要把索引数据和无关的业务数据全部查出来。本来一次就可以把所有索引数据加载进来，现在却要多次才能加载完。如果所对比的节点不是所查的数据，那么这些加载进内存的业务数据就毫无用处，全部抛弃。
• B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
• 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树

B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

做为数据库的索引，无论用什么样的数据结构维护，这些数据最终都会存储到磁盘中。

鉴于磁盘 I/O 的性能问题，以及每次 I/O 获取数据量上限所限，提高索引本身 I/O 的方法最好是，减少 I/O 次数和每次获取有用的数据。

B-tree 已经大大改进了树家族的性能，它把多个数据集中存储在一个节点中，本身就可能减少了 I/O 次数或者寻道次数。

但是仍然有一个致命的缺陷，那就是它的索引数据与业务绑定在一块，而业务数据的大小很有可能远远超过了索引数据，这会大大减小一次 I/O 有用数据的获取，间接的增加 I/O 次数去获取有用的索引数据。

B+ 树中，非叶子节点只保存索引数据，叶子节点保存索引数据与业务数据。这样即保证了叶子节点的简约干净，数据量大大减小，又保证了最终能查到对应的业务数。既提高了单次 I/O 数据的有效性，又减少了 I/O 次数，还实现了业务。

B+ 树就是为了拆分索引数据与业务数据的平衡多叉树。

每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

• 多叉树
• 节点有序
• 每一个节点可以存储多条记录
  
  

B+树可以保证等值和范围查询的快速查找

B 树& B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

Mysql的索引实现

MyIsam索引

MySAM B+Tree 叶子节点存放地址

MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。


辅助索引

在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

InnoDB的B+Tree 叶子节点直接存放数据

InnoDB索引

平衡二叉树、B树、B+树、B*树
MySQL索引原理以及查询优化

能说下myisam 和 innodb的区别吗？

MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。

InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址

myisam引擎是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。

innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

1. innodb支持事务，myisam不支持
2. innodb支持外键，myisam不支持
3. innodb支持表锁和行锁，但是myisam支持表锁
4. innodb在5.6版本之后支持全文索引
5. innodb索引的叶子节点直接存放数据，而myisam存放地址

为什么推荐使用主键自增

如果不是主键自增，插入数据时会导致频繁页分裂

mysql InnoDB 引擎底层数据结构是 B+ 树，所谓的索引其实就是一颗 B+树 ，mysql 中的数据都是按顺序保存在 B+ 树上的(所以说索引本身是有序的)。

然后 mysql 在底层又是以数据页为单位来存储数据的，一个数据页大小默认为 16k，当然你也可以自定义大小，也就是说如果一个数据页存满了，mysql 就会去申请一个新的数据页来存储数据。

如果主键为自增 id 的话，mysql 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。

如果主键是非自增 id，为了确保索引有序，mysql 就需要将每次插入的数据都放到合适的位置上。

创建索引

1. 尽量选择区分度高的列作为索引
2. 注意避免冗余索引 。
3. 尽可能的考虑建立联合索引而不是单列索引,因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’
   就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
//创建索引
alter table projectfile drop index s2123;//删除索引

回表，索引覆盖，最左匹配，组合索引，索引下推

回表

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引.
InnoDB普通索引的叶子节点存储主键值。(注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。)

普通索引的查询需要扫码两遍索引树:

1. 先通过普通索引定位到主键值id=？；
2. 再通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录。

索引覆盖

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

覆盖索引指的是在一次查询中，如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。

select * from s1 where id=123;
该sql命中了索引，但未覆盖索引。利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。

但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够，还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id，就减去了这份苦恼，如下
select id from s1 where id=123;

这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快

而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可。

explain select * from user where age=1; //查询的name无法从索引数据获取
explain select id,age from user where age=1; //可以直接从索引获取

最左匹配

最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，指的是索引以最左边为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、between、like）就会停止匹配。

最左前缀匹配原则，非常重要的原则，

create index ix_name_email on s1(name,email,)

-最左前缀匹配：必须按照从左到右的顺序匹配

select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
select * from s1 where email='asdf' and name='egon'; #可以(优化器)

• mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
  比如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的查询优化器 会帮你优化成索引可以识别的形式

最左前缀示范

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.39 sec)

mysql> create index idx on s1(id,name,email,gender); #未遵循最左前缀
Query OK, 0 rows affected (15.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.43 sec)


mysql> drop index idx on s1;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx on s1(name,email,gender,id); #遵循最左前缀
Query OK, 0 rows affected (15.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.03 sec)

select * from staffs where name ='Mary' and age =10 and pos =20;//索引失效，中间有范围查找，只用到name,age

索引下推

select * from table where name=1 and age = 1 

• 数据存储在磁盘
• mysql有自己的服务
• mysql服务要跟磁盘发生交互

1.没有索引下推时
先从存储引擎中拉取数据（根据name筛选），再mysql server 根据age进行数据的筛选
2.有索引下推 mysql5.6
会在拉取数据的时候直接根据name,age来获取数据，不需要server做任何数据筛选(可以减少IO次数)

索引下推唯一的缺点是需要在磁盘上多做数据筛选，原来的筛选是放在内存中的，现在放到了磁盘上查找数据的环节，这样看起来成本比较高，但是由于数据是排序的，所有的数据是聚集存放的，所以性能不会有影响，而且整体的io量会大大减少，反而会提升性能。

MRR，全称「Multi-Range Read Optimization」。

简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

mysql > set optimizer_switch='mrr=on';

对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。

对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。
顺序读带来了几个好处：

1. 磁盘和磁头不再需要来回做机械运动
2. 可以充分利用磁盘预读

比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理：

1、空间局部性：当一个数据被用到时，其附近的数据也通常会马上被使用。
2、时间局部性：如果一个数据正在被访问，那么在近期它很可能还会被再次访问。

在一次查询中，每一页的数据只会从磁盘读取一次
顺序读就是通过这三个方面，最大的优化了索引的读取。
索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大。

事务的基本特性和隔离级别

原子性：原子性指的是一个事务中的操作要么全部成功，要么全部失败。

用undo log实现。 保存的是跟执行操作相反的操作，undo log也参与了mvcc。MVCC（Multi-Version Concurrency Control）在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

一致性：指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。

隔离性：指的是一个事务的修改在最终提交前，对其他事务是不可见的。

持久性：指的是一旦事务提交，所做的修改就会永久保存到数据库中。

用redo log实现，为了保证crash safe
如果发生异常情况，就算数据没有持久化成功，只要日志持久化成功了，依然可以进行恢复

隔离性有4个隔离级别

• 读未提交： read uncommit ,可能会读到其他事务未提交的数据，也叫做脏读
  

• 读已提交： read commit ,两次读取结果不一致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。
  不可重复读(在同一个事务中执行相同的sql语句，查询到的结果是不同的)，读取的是最新的一致性的快照版本
  

• 可重复读：repeatable read, 这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读，事务开启之前的版本

• 串行化：serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

//MySQL 事务隔离级别 mysql.cnf 文件里设置的
transaction-isolation = REPEATABLE-READ

隔离级别越低，效率越高，越不安全，
隔离级别越高，效率越低，越安全

一致性非锁定读

select * from table where id=1 for update;

一致性锁定读

select * from table where id=1;

那ACID靠什么保证的呢？

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

C一致性一般由代码层面来保证

I隔离性由MVCC来保证

D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

什么是幻读，什么是MVCC

1、幻读： 同一事务下，连续执行两次同样的SQL可能得到不同的结果，第二次的SQL可能返回了不存在的行。

官方给出了一个例子：child表只有id为90和102的记录，执行这样一条查询语句：

select * from child where id > 100 for update;

如果此时没有锁锁定90到102这个范围的话，另一个线程可能会成功插入一条id为101的数据，那么再次执行这个查询，就会出现一条id为101的记录，这就是幻读问题。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

InnoDB采用Next-Key Lock解决幻读问题。

2、MVCC，MVCC叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照。

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

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

我们每行数实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增。

事务读数据的原则：

• 读版本号小于等于当前版本的数据(意思就是读不到在当前事务之后修改的数据 避免了不可重复读)

• 读删除事务版本号大于等于当前版本的数据(意思就是如果这条数据在之后的事务里删了，当前事务也不能再读了)

InnoDB实现mvcc 是通过 readview+undolog 来实现

MySQL的InnoDB引擎默认情况的隔离级别是可重复读

这种隔离级别让脏读和不可重复读不可能发生，还可能解决幻读的问题，就是MVCC加上间隙锁的方式，MVCC是并发一致性控制，工作在两种隔离级别下，在可重复读的情况下，它的工作机制是另一个事务读取一个事务时，读取这个事务的快照，

锁的类型有哪些呢

mysql锁分为共享锁和排他锁，也叫做读锁和写锁。

读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。

写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种。

表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。

行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

forupdate

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

只有当出现如下之一的条件，才会释放共享更新锁：

• 执行提交（COMMIT）语句
• 退出数据库（LOG　OFF）
• 程序停止运行

在数据库中执行select … for update ,大家会发现会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

//明确指定主键，并且数据真实存在，row lock
SELECT * FROM user WHERE id=3 FOR UPDATE;

FOR UPDATE仅适用于InnoDB，且必须在事务处理模块(BEGIN/COMMIT)中才能生效。

Myisam 只支持表级锁，是表级锁时，不管是否查询到记录，都会锁定表。

使用场景

比如火车票订票，在屏幕上显示有票，而真正进行出票时，需要重新确定一下这个数据没有被其他客户端修改。所以，在这个确认过程中，可以使用for update。

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像for update，再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

SQL查询语句中的 limit 与 offset 的区别

select * from t_record where age > 10 offset 10000 limit 10

可以使用索引覆盖

主从复制，读写分离，分库分表，锁，日志系统

索引优化

• 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
• 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
• 使用前缀索引（有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤更多的行。）

alter table citydemo add key(city(7));

• 使用索引扫描来排序
• union all, in ,or都能够使用索引，但是推荐使用in（如果是单列索引，or会使用索引，如果是组合索引：1、全部的列都是组合索引的时候，会使用全部列所对应的索引。2、如果部分列是组合索引，那么不会走索引）
• 范围列可以用到索引（范围条件是：<,<=,>=,between。范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列）
• 强制类型转换会全表扫描
• 更新十分频繁，数据区分度不高（性别列）的字段上不宜建立索引（1、更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能；2、类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据；3、一般区分度在80%以上的时候就可以建立索引，区分度可以使用count(distinct（列名）)/count（*）计算）
• 创建索引的列，不允许未null，可能会得到不符合预期的结果
• 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
• 能使用limit的时候尽量使用limit
• 单表索引建议控制在5个以内
• 单索引字段数不允许超过5个（组合索引）

一条sql的执行过程

MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。

引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。

查询语句的执行流程如下：权限校验（如果命中缓存）—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎

CBO:基于成本的优化
RBO：基于规则的优化

MySQL 执行一条查询的流程如下：

• 客户端先通过连接器连接到 MySQL 服务器；
• 连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器；
• 分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器；
• 优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好；
• 执行器：当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

mysql主从同步

MySQL 查询缓存有什么优缺点？

MySQL 查询缓存功能是在连接器之后发生的，它的优点是效率高，如果已经有缓存则会直接返回结果。

查询缓存的缺点是失效太频繁导致缓存命中率比较低，任何更新表操作都会清空查询缓存，因此导致查询缓存非常容易失效。

唯一索引和普通索引哪个性能更好？

• 对于查询来说两者都是从索引树进行查询，性能几乎没有任何区别；
• 对于更新操作来说，因为唯一索引需要先将数据读取到内存，然后需要判断是否有冲突，因此比唯一索引要多了判断操作，从而性能就比普通索引性能要低。

left join 和 right join 的区别是什么？

left join（左联结），返回左表全部记录和右表联结字段相等的记录；

right join（右联结），返回右表全部记录和左表联结字段相等的记录。

以下 or 查询有什么问题吗？该如何优化？

select * from t where num=10 or num=20;

如果使用 or 查询会使 MySQL 放弃索引而全表扫描，可以改为：

select * from t where num=10
union
select * from t where num=20;

MySQL 是如何处理死锁？

• 通过 innodb_lock_wait_timeout 来设置超时时间，一直等待直到超时；
• 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其他事务继续执行。

InnoDB 存储引擎有几种锁算法？

Record Lock — 单个行记录上的锁；
Gap Lock — 间隙锁，锁定一个范围，不包括记录本身；
Next-Key Lock — 锁定一个范围，包括记录本身。

InnoDB 如何实现行锁？

只有通过索引条件检索数据，InnoDB 才使用行级锁，否则 InnoDB 将使用表锁。使用 for update 来实现行锁，具体脚本如下：

select * from t where id=1 for update

其中 id 字段必须有索引。

MySQL 性能指标都有哪些？如何得到这些指标？

• QPS（Queries Per Second），每秒查询数，一台数据库每秒能够处理的查询次数；
• TPS（Transactions Per Second），每秒处理事务数。

MySQL 中的重要日志分为哪几个？

• 错误日志：用来记录 MySQL 服务器运行过程中的错误信息，比如，无法加载MySQL 数据库的数据文件，或权限不正确等都会被记录在此，还有复制环境下，从服务器进程的信息也会被记录进错误日志。
• 查询日志：查询日志在 MySQL 中被称为 general log（通用日志），查询日志里的内容不要被“查询日志”误导，认为里面只存储 select 语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录。因此如果不是在调试环境下，
  是不建议开启查询日志功能的。
• 慢日志：慢查询会导致 CPU、IOPS、内存消耗过高，当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。使用如下命令记录当前数据库的慢查询语句：

set global slow_query_log='ON';

使用 MySQL 自带功能，开启慢查询日志，在 MySQL 的安装目录下找到my.cnf 文件设置 slow-query-log=On 开启慢查询，慢查询默认时长为10s，默认存储文件名为 host_name-slow.log。

• redo log （重做日志）：为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。
• undo log（回滚日志） ：用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。
  undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete一条记录时，undo log 中会记录一条对应的 insert 记录，
• bin log（二进制日志）：是一个二进制文件，主要记录所有数据库表结构变更，比如，CREATE、ALTER TABLE 等，以及表数据修改，比如，INSERT、UPDATE、DELETE 的所有操作，bin log 中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。

binlog 的作用如下：

恢复（recovery）：某些数据的恢复需要二进制日志。

复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或者 standby）与一台 MySQL数据库（一般称为 master 或者 primary）进行实时同步；

redo log与binlog区别

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；

• binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”；

• redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用；

• redo log 是循环写的，空间固定会用完，binlog 是可以追加写入的，“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

由binlog和redo log的区别可知：binlog日志只用于归档，只依靠binlog是没有crash-safe（崩溃恢复）能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用，在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared（准备状态）和 commit（提交状态）两种，对于prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考binlog，如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

undo log

数据库事务四大特性中有一个是原子性，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。

同时，undo log也是MVCC(多版本并发控制)实现的关键

使用 MySQL 中的 explain 分析执行语句

• id — 选择标识符，id 越大优先级越高，越先被执行
• select_type — 表示查询的类型。
• type — 表示表的连接类型
  all — 扫描全表数据
  index — 遍历索引
  range — 索引范围查找
• possible_keys — 表示查询时，可能使用的索引
• key — 表示实际使用的索引
• key_len — 索引字段的长度
• ref— 列与索引的比较
• rows — 大概估算的行数
• filtered — 按表条件过滤的行百分比
• Extra — 执行情况的描述和说明

表的优化策略有哪些？

• 读写分离，主库负责写，从库负责读
• 垂直分区，根据数据属性单独拆表甚至单独拆库。
• 水平分区，保持表结构不变，根据策略存储数据分片，这样每一片数据被分散到不同的表或者库中。水平拆分只是解决了单一表数据过大的问题，表数据还在同一台机器上，对于并发能力没有什么意义，因此水平拆分最好分库。另外分片事务难以解决，跨节点 join 性能较差。

查询语句的优化方案有哪些？

• 不做列运算，把计算都放入各个业务系统实现；
• 避免数据类型的隐式转换（select name,phone from customer where id = ‘111’）
• 查询语句尽可能简单，大语句拆小语句，减少锁时间；
• 不使用 select * 查询；
• or 查询改写成 in 查询；
• 尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描；
• 避免使用双%号的查询条件。如：a like ‘%123%’，（如果无前置%,只有后置%，是可以用到列上的索引的）；一个 SQL 只能利用到复合索引中的一列进行范围查询。如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到。

1、对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引

覆盖索引的好处：

• 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。 而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
• 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

2、减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

sql注入

SQL注入是一种将SQL代码添加到用户的输入参数中的攻击，之后再将这些参数传递给后台的sql服务器加以解析和执行。由于sql语句本身的多样性，以及可用于构造sql语句的编程方法很多，因此凡是构造sql语句的步骤均存在被攻击的潜在风险。

Sql注入的方式主要是直接将代码插入参数中，这些参数会被置入sql命令中加以执行。间接的攻击方式是将恶意代码插入字符串中，之后将这些字符串保存到数据库的数据表中或将其当成元数据。当将存储的字符串置入动态sql命令中时，恶意代码就将被执行。

解决方法：

• 使用正则表达式过滤传入的参数，Pattern.matches(CHECKSQL,targerStr);判断是否匹配
• 字符串过滤