MySQL进阶指南：索引、存储引擎与事务优化-优快云博客

MySQL

参考视频： MySQL数据库天花板，目前B站最好的MySQL面试夺命连环50问（700分钟高质量面试题及解析）哔哩哔哩bilibili

1.B树和B+树区别，为什么MySQL使用B+树？

2.InnoDB如何实现事务？

3.MySQL的ACID靠什么保证？

4.MySQL的集群是如何搭建的？读写分离是怎么做的？

11.MySQL数据库中，什么情况下设置了索引但无法使用？

12.存储拆分后如何解决唯一主键问题？

13.MyISAM和InnoDB区别？

14.MySQL中索引类型对数据库性能的影响？

一、知识梳理

1.索引

索引是帮助MySQL高效获取数据的排好序的数据结构

索引的数据结构：

（1）二叉树

出现顺序索引时，二叉树会变成链表，效率低下。

（2）红黑树

本质是平衡树，优化了二叉树的问题。但数据量大的情况下，树的高度过高，效率依然较低。（500万条数据，高度为20，即最坏进行20次IO操作，效率低）

（3）B树

预先给节点分配更大磁盘空间，存储更多的索引元素。在高度可控的情况下，存储更多的元素。

（4）B+树

非叶子节点不存储data，只存储索引（冗余），可以放更多索引
叶子节点包含所有索引字段
叶子节点用指针连接，提高区间访问的性能

设树高度为3，默认每个节点分配的空间是16kb。

h=1：bigint是8个字节，下级节点地址6个字节，放满时16kb/(8+6)b=1170个索引。

h=2：同1，每个节点也能存储1170个索引。

h=3：假设索引加data占1kb，节点可以放16个。

1170*1170*16 = 两千多万，

即一个高度仅为三的B+树可以存储两千多万个索引。

查询数据时，从磁盘将节点拷贝到内存。

因为索引从小到大排序，进行折半查找，按同样步骤，对2层、3层进行查找。

相比于IO操作，内存中的折半查找几乎不消耗时间，最多只进行3次IO操作。

实际上，在MySQL高版本中，冗余（1、2层节点）在启动时就加载到了内存中，所以查询仅需要进行一次IO操作。

这是目前数据库最常使用的索引数据结构。

B树和B+树区别？

① B树每个节点都存储key和data，B+树只有叶子节点存储data。

② B树叶节点之间没有双向指针，不方便支持范围查询操作。

如查询 20<col<50，如上面两张图，B+树查询到20，再查询到该叶子节点中的30，再通过双向指针查询到下一个叶子节点，查询49、50，结束。

但B树中，想要查询下一叶子节点的内容，必须再次从根节点查询。

（5）Hash

对索引的key进行hash计算，得到数据存储的位置
很多时候hash索引比B+Tree更高效
仅能满足“=”、“IN”，不支持范围查询
hash冲突

2.存储引擎

（1）MyISAM

MyISAM包含三个文件：

xxx.frm：表框架
xxx.MYD： MyISAM Data 数据文件
xxx.MYI： MyISAM Index 索引

MyISAM的索引文件和数据文件是分离的（非聚集）。

查找过程：

SELECT * FROM t WHERE Col1=30

判断查询条件是否走索引，使用MYI中的B+树进行查找，找到对应的地址。再到MYD中使用地址找到对应数据。

（2）InnoDB

InnoDB是MySQL现在更常用的存储引擎，它包含两个文件：

xxx.frm：表框架
xxx.ibd：数据文件和索引（聚集）

主键索引（直接查找，叶节点存储所有数据）：

非主键索引（查找到主键，再通过主键索引获取全部数据，2次查找）：

表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引：叶节点包含了完整的数据记录

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

主键：MySQL会查找一个唯一索引，以此构建B+Tree来组织整张表的数据。如果没找到，会自动维护一个隐藏列rowid，作为唯一索引。

整形：在查找过程中，存在大量的比大小操作，整形比大小效率远大于字符串。

自增：每次向B+树中添加新元素时，都要维护数据顺序排列。一个节点的空间有限，如果加入元素后会空间不足，则会使用双向指针添加到下一节点。此时，如果主键是自增的，每次都会添加到最后，减少MySQL很多操作。

创建整型的自增主键，会减少MySQL的工作，大大提高效率。

联合索引的底层数据结构

联合索引排序：逐个对每个字段排序（上图，先对name排序，相同再用age，最后position）

最左前缀原则

下面三条语句只有第一条走索引

EXPLAIN SELECT * FROM employee WHERE name = 'Bi11' and age=31;

EXPLAIN SELECT * FROM employee WHERE age =30 AND position = 'dev';

EXPLAIN SELECT * FROM employee WHERE position = 'manager';

为什么使用最左前缀原则：

因为 联合索引是逐个对每个字段排序，最左前缀原则能保证每个字段都是顺序的。

EXPLAIN用法和结果分析王洪玉的博客-优快云博客explain的用法

3.InnoDB页结构-索引

页是InnoDB磁盘管理的最小单位，默认每页为16KB，从InnoDB1.2.x开始，可以通过innodb_page_size参数将页设置为4K、8K、16K。常见的页有数据页（B-tree Node）、undo页（undo Log Page）、插入缓冲位图页（Insert Buffer Bitmap）等。下文主要分析数据页。

（1）数据的插入查询

insert into t1 values(4,3,1,1,'d');

insert into t1 values(1,1,1,1,'a');

insert into t1 values(8,8,8,8,'h');

数据插入时，会自动根据主键进行排序。如上述语句，主键为第一列，insert语句不是升序，但插入到用户数据区域后，进行了升序排序，使用指针连接。

同时，插入新数据到已有的两数据之间，需要对前后指针进行修改。自增主键则可以直接插入到最后，大大提高效率。

执行查询语句

SELECT * FROM t1 WHERE a=3

查询第一条a!=3，查询第二条4已经大于3，不会继续向下查询。

（2）页目录

利用页目录优化链表的查询，以空间换时间。

对数据根据主键进行分组，取每组较小主键存入页目录。

当一页存满后，就会开辟新页，用指针将每一页连接。

为方便找到数据在哪一页，会创建一个页目录，称为索引页，下面的称为数据页。

可以发现，上述存储结构就是一棵B+树。

（3）索引的使用

回表：

如表a,b,c,d,...，a为主键，需要对bcd进行查询获取所有字段，可以创建bcd字段的联合索引（最左前缀原则）。

使用联合索引查询到的数据是主键索引叶子节点的地址，使用该地址前往主键索引的叶子节点，拿到完整的数据，这个过程就是回表。

覆盖索引：

是一种情况，不是一种索引。当需要查询的字段在索引的B+树上都有，就不需要回表了。

order by 需要排序的情况：

SELECT * FROM t1 ORDER BY b,c,d;

1.走索引，不用额外排序，较少次回表

2.全表扫描，额外排序(内存)，不用回表

这种情况下，全表扫描更快

SELECT b FROM t1 ORDER BY b,c,d;

1.走索引，不用额外排序，不用回表

2.全表扫描，额外排序(内存)，不用回表

这种情况下，走索引更快

4.事务

（1）Buffer Pool（free链表）

MySQL的缓存，将磁盘中页读取到内存中，就会存储在这里。大小默认为128M。

free链表：

Buffer Pool分成多个块，用来存储读取的页。free链表是一个存储空闲块的链表。

头节点（元素个数）-> 控制块（指针：指向当先空闲的BufferPool区域，也就是一个空页）-> 控制块...

当新的页需要存入Buffer Pool时，就会读取free链表，找到空闲块，进行存储，之后删除该控制块。

（2）淘汰机制（lru链表）

Buffer Pool已满，存入新页时需要进行淘汰，使用 lru（最近最少使用）

lru链表：

头节点（元素个数）-> 控制块（指针：指向一个BufferPool区域）-> 控制块...

新控制块添加到最前，控制块对应页使用后移动到最前，淘汰最后一个控制块，这样淘汰最近最少使用。

换血：

SELECT * FROM t1

当查询内容较多时，很有可能导致Buffer Pool中所有内容淘汰掉，也就是换血。

但实际上，换血会导致Buffer Pool中存储了很多没用的数据，而有用的数据却被淘汰掉。

lru链表优化：

控制块的前5/8存储热数据，后3/8存储冷数据。

冷数据区：

新增数据都在冷数据区进行添加与淘汰。

热数据区：

添加：控制块会维护 t1(数据第一次被访问的时间)，t2(数据第二次被访问的时间)，当 t2-t1>1s 时，说明该数据可能是热点数据，此时将该数据加入热数据区。避免如上述SQL语句，一个数据被很快的被多次访问的情况。

淘汰：热数据区满，冷数据区有新数据加入，淘汰热数据区最后一块。

但注意，热数据区中前1/4控制块不会因为被访问而移动到最前面，后3/4才会进行位置移动。因为高热度的数据经常被访问，可能会出现大量的移动操作，且lru链表专注的是淘汰，而不是最近最少使用情况的维护。

（3）update（flush链表）

执行update语句时，不仅要对Buffer Pool中的数据进行修改，还要将修改内容更新到磁盘中。

此时，只修改了Buffer Pool，还未对磁盘修改的页，称为脏页。

MySQL中有一个线程，会定时的对脏页进行持久化。

flush链表：头节点（元素个数）-> 控制块（指针：有改动的页）-> 控制块...

当到持久化的时间时，就会通过flush链表，查看那些数据需要持久化。

（4）事务底层原理

begin;

update语句

commit;

1．修改Buffer Pool里面的页的数据

2．生成一个BinLog对象：mysql层面的对象，保存sql语句，维护主从数据库一致。

3．生成一个RedoLog对象：InnoDB层面的对象，只记录数据的修改，用于持久化。

4．生成一个undoLog：保存旧的数据，用于回滚。

5．事务提交持久化RedoLog

6．后台定时任务去持久化脏页

双写缓冲区

一页16kb，操作系统一次能处理4kb，持久化时要分四次写入，此时如果系统挂掉，内存数据丢失，磁盘数据已经乱掉了，页也不知道写到了哪里，这种情况下数据是很难恢复的。

所以，每次持久化会写存到磁盘另开辟的一片空间——双写缓冲区，当系统挂掉，老数据是没有影响的，只需要用RedoLog重新持久化即可。正常持久化结束，将双写缓冲区中的数据覆盖老数据即可。

但覆盖老数据的时候还是要写四次，同样有可能挂掉，但最新的数据仍在双写缓冲区，所以可以恢复。

原子操作：目前很多硬盘支持原子写入，要么全部写入，要么全部写入失败。

二、面试题总结

1.B树和B+树区别，为什么MySQL使用B+树？

B树特点：

节点排序
一个节点可以存多个元素，多个元素也排序了

B+树特点：

拥有B树特点
叶子节点之间指针
非叶子节点上的元素在叶子节点上都冗余了，也就是叶子节点上存储了所有元素，并且排好序

区别：

B树每个节点都存储key和data，B+树只有叶子节点存储data。
B树叶节点之间没有双向指针，不方便支持范围查询操作。

为什么使用B+树：

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

2.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语句生成undolog日志，用于事务回滚

（5）如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中

这里有三种持久化设置：

默认的是提交时立刻持久化

还有提交时由后台线程持久化，可以减少持久化时间提高事务效率

以及提交时立刻将RedoLog写到操作系统的缓冲区，当数据库挂了，操作系统没挂，还可以继续持久化

（6）如果事务回滚，则利用undolog日志进行回滚

3.MySQL的ACID靠什么保证？

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

C（一致性）由其他三大特性、程序代码保证

I（隔离性）由MVCC保证

D（持久性）由内存+redo log保证，mysql修改数据同时在内存和redo log记录这次操作，宕机时可以从redo log恢复。

InnoDB redo log写盘，InnoDB事务进入prepare状态，

如果前面prepare成功，binlog写盘，再继续将事务日志持久化到binlog，如果持久化成功，那么InnoDB事务进入commit状态（正在redolog中写一个commit记录）

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

4.MySQL的集群是如何搭建的？读写分离是怎么做的？

MySQL通过将主节点的binlog同步给从节点完成主从之间的数据同步。

MySQL的主从集群只会从主节点同步到从节点，而不会反过来同步。由此也就引申出了读写分离的问题。

因为要保证主从间数据一致，写数据的操作只能在主节点完成，而读数据可以在任意节点完成。

5.MySQL聚簇和非聚簇索引的区别

都是B+树的数据结构

聚簇索引：将数据存储和索引放到一起，都是按照一定的顺序组织，找到索引也找到了数据，数据的物理存储和索引顺序是一致的，即：只要索引相连，那么对应的数据一定也是相邻存储在磁盘上的

非聚簇索引：叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据，这个有点类似一本书的目录。

优势：

1、根据聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询效率要高

2、聚簇索引对于范围查询效率更高，因为其数据是按大小排列的

3、聚簇索引适合用在排序的场合，非聚簇索引不适合

劣势：

1、维护索引很昂贵，特别是插入新行或者主键被更新导致要分页的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片。

2、表因为使用UUID（随机ID）作为主键，使用数据存储稀疏，这就会出现聚簇索引可能比全表扫描更慢，所以建议使用int的auto_increment作为主键。

3、如果主键比较大的时候，其辅助索引将会变得更大，因为辅助索引的叶子存储的是主键值，过长的主键值，会导致非叶子节点占用更多的物理内存

InnoDB中一定有主键，主键一定是聚簇索引。不手动设置，则会使用unique索引，没有unique索引，则会使用数据库内部一个行的隐藏id来当做主键索引。在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查询。非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列

MyISAM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两棵B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键索引无需访问主键的索引树。

如果涉及到大量数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

6.MySQL慢查询如何优化？

1．检查是否走了索引，如果没有则优化SQL利用索引

2．检查所利用的索引，是否是最优索引

3．检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据

4．检查表中数据是否过多，是否应该进行分库分表了

5．检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

7.MySQL索引的数据结构及各自优劣？

在MySQL中使用较多的索引有Hash索引，B＋树索引等。

InnoDB存储引擎的默认索引实现为：B＋树索引。

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择B+Tree索引。

8.MySQL锁的类型

基于锁的属性分类：共享锁、排他锁

基于锁的粒度分类：行级锁、表级锁、页级锁、记录锁、间隙锁、临建锁。

基于锁的状态分类：意向共享锁、意向排他锁。

共享锁

读锁，简称S锁，当一个事务为数据加上该锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁。直到所有读锁释放后才能加写锁。

共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读。

排他锁

写锁，简称X锁，当一个事务为数据加上写锁时，其他请求不能为数据加任何锁，直到该锁释放。

排他锁的目的是在数据修改时，不允许其他人同时修改，也不允许其他人读取，避免出现脏数据和脏读的问题。

表锁

上锁时锁住整个表，当下一个事务访问该表时，必须等先前一个事务释放才能对表进行访问。

特点：粒度大，加锁简单，容易冲突。

行锁

行锁是指上锁的时候锁住的是表的一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问。

特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。

记录锁

记录锁也是行锁的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。

精准条件命中，并且命中的条件字段是唯一索引。

加了记录锁之后数据可以避免在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

页锁

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

特点：开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。

间隙锁

属于行锁的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。

临建锁

也属于行锁的一种，记录锁和间隙锁的组合，临建锁会把查询出的记录锁住，同时也会把该范围内的所有间隙空间也锁住。

意向共享锁

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

一个事务试图对整个表进行加共享锁之后，首先需要获得这个表的意向其享锁

意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它领。

9.MySQL执行计划怎么看？

EXPLAIN用法和结果分析王洪玉的博客-优快云博客explain的用法

这里写图片描述

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数

EXPLAIN SELECT * from A where X=? and Y=?

id ：是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为 NULL最后执行。
selectType 表示查询中每个select子句的类型：

SIMPLE：表示此查询不包含 UNION 查询或子查询
PRIMARY：表示此查询是最外层的查询（包含子查询）
SUBQUERY：子查询中的第一个 SELECT
UNION：表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询 UNION RESULT, UNION 的结果
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询

table：表示该语句查询的表
type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：

const：通过索引一次命中，匹配一行数据
system: 表中只有一行记录，相当于系统表；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref: 非唯一性索引扫描,返回匹配某个值的所有
range: 只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
index: 只遍历索引树；
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。那么基本就是随着表的数量增多，执行效率越慢。
执行效率： ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index

possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。
key：此字段是 mysql 在当前查询时所真正使用到的索引。他是possible_keys的子集
key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标
ref：显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
rows：mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大
filtered：返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集很少
extra
- using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
- using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
- using temporary：查询有使用临时表, 一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。
- using where ：sql使用了where过滤,效率较高。

10.MySQL主从同步？

Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I／0 thread、SQL thread），Master一条线程和Slave中的两条线程。

主节点binlog，主从复制的基础是主库记录数据库的所有变更记录到binlog。binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
主节点log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
从节点I／O线程接收binlog内容，并将其写入到relay log文件中。
从节点的SQL线程读取relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

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

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

（1）全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

（2）半同步复制

从库写入日志成功后，返回ACK确认给主库，主库至少收到一个从库的确认即为同步完成。

11.MySQL数据库中，什么情况下设置了索引但无法使用？

没有符合最左前缀原则

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

（1）对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。

（2）= 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。

（3）如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

字段进行了隐式的数据转换

数据转换会调用函数，而 InnoDB 存储引擎中，在索引列上使用函数会导致索引失效，所以最后导致了全表扫描。

走索引没有走全表效率高

12.存储拆分后如何解决唯一主键问题？

在单库单表时，业务ID可以依赖数据库自增主键实现，但分库分表后，如果使用自增主键，会导致主键重复。

（1）UUID

它是在一定的范围内（从特定的名字空间到全球）唯一的机器生成的标识符。

优点：简单、性能好。

缺点：没有顺序，没有业务含义，存在泄露mac地址的风险。

（2）数据库主键

数据库初始值不同，步长相同（如DB1初始值1，DB2初始值2，每次都自增2）

优点：实现简单，单调递增，具有一定的业务可读性。

缺点：强依赖DB（步长为2，如果添加DB3，之前DB的ID都需要修改，需要提前规划好步长），存在性能瓶颈，存在暴露业务信息风险。

（3）采用redis,mongodb,zk等中间件：增加了系统复杂度和稳定性

（4）雪花算法

雪花算法生成的ID是一个64bit的long型的数字且按时间趋势递增。大致有首位符号位(无效位), 时间戳插值, 机器编码, 序列号四部分组成.。

首位无效符：主要用做为符号位,因为一般都是生成正数,所以符号位统一都是0
时间戳：占用41bit,精确到毫秒. 41bit位最好可以表示2^41-1毫秒，转化成单位年为69年。
机器编码：占用10bit,其中高位5bit是数据中心ID,低位5bit是工作节点ID,最多可以容纳1024个节点.
序列号：占用12bit,每个节点每毫秒0开始不断累加,最多可以累加到2^12-1,一共可以生成4096个ID(包括了0)

13.MyISAM和InnoDB区别？

MyISAM：

不支持事务，但每次查询都是原子的
支持表级锁，即每次操作都是对整个表加锁
储存表的总行数
一个MyISAM有三个文件：索引文件、表结构文件、数据文件
非聚集索引：索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但辅索引不用保证唯一性。

InnoDB：

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

14.MySQL中索引类型对数据库性能的影响？

普通索引：允许被索引的数据列包含重复值。

唯一索引：不允许被索引的数据列包含重复值，保证数据记录额唯一性。

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

联合索引：索引可以覆盖多个数据列，如像INDEX（columnA， columnB）索引。

全文索引：通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT （column）；创建全文索引

LIKE %的模糊查询，虽然用不到索引，在文本内容比较少时是比较合适，但是对于大量的文本数据检索，全文索引在大量的数据面前，能比 LIKE % 快很多。

实际上，MySQL的全文索引效果一般，需要使用模糊查询的场合一般使用ES。

索引可以极大的提高数据的查询速度。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。

15.如何实现分库分表？

前提：数据库吞吐量越来越大，单库承受不住，在业务和SQL优化后，没有办法的一种选择。

将原本存储于单个数据库上的数据拆分到多个数据库，把原来存储在单张数据表的数据拆分到多张数据表中，实现数据切分，从而提升数据库操作性能。分库分表的实现可以分为两种方式：垂直切分和水平切分。

（1）水平：将数据分散到多张表，涉及分区键，

策略一：前一半数据留在原节点，后一半数据放入新节点。

    优点：简单

    缺点：数据分布不均匀，新节点中可能新数据较多，压力较大。

策略二：Hash

    优点：数据分布均匀

    缺点：操作复杂

分库：每个库结构一样，数据不一样，没有交集。库多了可以缓解io和cpu压力
分表：每个表结构一样，数据不一样，没有交集。表数量减少可以提高sql执行效率、减轻cpu压力

（2）垂直：将字段拆分为多张表，需要一定的重构

分库：每个库结构、数据都不一样，所有库的并集为全量数据
分表：每个表结构、数据不一样，至少有一列交集，用于关联数据，所有表的并集为全量数据

16.什么是MVCC？

【MVCC多版本并发控制】MVCC 机制的原理及实现，什么是MVCC,多版本并发控制_花野的博客-优快云博客mvcc 机制

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

MVCC只在READ COMMITTED 和REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

聚簇索引记录中有两个必要的隐藏列：

trx_id：用来存储每次对某条聚簇索引记录进行修改的时候的事务id。
roll_pointer：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。（注意插入操作的undo日志没有这个属性，因为它没有老版本）

基于undo log版本链：

已提交读和可重复读的区别就在于他们生成ReadView的策略不同。

开始事务时创建readview，readView维护当前活动的事务id，即未提交的事务id，排序生成一个数组

访问数据，获取数据中的事务id（获取的是事务id最大的记录），对比readview：

如果在readview的左边（比readview都小），可以访问（在左边意味着该事务已经提交）

如果在readview的右边（比readview都大）或者就在readview中，不可以访问，获取roll＿pointer，取上一版本重新对比（在右边意味着，该事务在readview生成之后出现，在readview中意味着该事务还未提交）

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView，而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

这就是Mysql的MVCC，通过版本链，实现多版本，可并发读—写，写—读。通过ReadView生成策略的不同实现不同的隔离级别。

17.什么是脏读、幻读、不可重复读？

脏读：在事务进行过程中，读到了其他事务未提交的数据。

不可重复读：在一个事务过程中，多次查询结果不一致。

幻读：在一个事务过程中，多次查询结果记录数不一致。

处理方法：加锁、事务隔离、MVCC

加锁：

1、脏读：在修改时加排他，直到事务提交再释放。读取时加共享锁，读完释放。

2、不可重复读：同上。

3、幻读：加范围锁。

18.事务饿顶基本特性和隔离级别？

1）原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2）一致性（Consistency）：一个事务中，事务前后数据的完整性必须保持一致。

3）隔离性（Isolation）：多个事务，事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

4）持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

可读取未确认 read uncommitted : 读取尚未提交的数据：哪个问题都不能解决
可读取确认 read committed：读取已经提交的数据：可以解决脏读 ---- oracle默认的
可重复读repeatable read：重读读取：可以解决脏读和不可重复读 ---mysql默认的
可串行化 serializable ：可以解决脏读不可重复读和虚读---相当于锁表

19.索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

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

索引的基本原理也就是B+ 树的基本原理，通过B+树的结构优点查询数据。

20.索引的设计原则

查询更快、占用空间更少。

1．适合索引的列是出现在where子句中的列，或者连接子句中指定的列

2．基数较小的类，索引效果较差，没有必要在此列建立索引

3．使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词

超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。

4．不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至

重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

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

6．更新频繁字段不适合创建索引

7．若是不能有效区分数据的列不适合做索引列（如性别，男女未知，最多也就三种，区分度实在太低）

8．尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加（a，b）的索引，那么只需要修改原来的索

引即可。

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

10．对于定义为text、image和bit的数据类型的列不要建立索引。

21.索引覆盖是什么？

索引覆盖就是一个SQL在执行时，可以利用索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL走完索引后不用回表了，所需要的字段都在当前索引的叶子节点上存在，可以直接作为结果返回了。

22.最左前缀原则？

当一个SQL想要利用索引时，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a，b，c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a，b，c三个字段的联合索引时，底层的B+树是按照a，b，c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找，也得符合这个规则。