MySQL高级

MySQL高级

1、MySQL聚簇索引和非聚簇索引的区别是什么？

索引一定是存储在磁盘的

InnoDB存储引擎

• .frm：表结构

• .idb：数据文件和索引文件

MyISAM存储引擎

• .frm：表结构

• .MYD：数据文件

• .MYI：索引文件

区别

​ MySQL的索引类型跟存储引擎是相关的，Innodb存储引擎数据文件跟索引文件全部都放在idb文件中，而myisam存储引擎的数据文件放在myd文件中，索引文件放在myi文件中，其实区分聚簇索引和非聚簇索引非常简单，只有判断数据跟索引是否存储在一起就可以了。

​ innodb存储引擎在进行数据插入的时候，数据必须要跟索引文件在一起，如果有主键就使用主键，没有主键就使用唯一键，没有唯一键就使用6字节的rowid，因此跟数据在一起的就是聚簇索引，而为了避免数据冗余存储，其他的索引的叶子节点存储的都是聚簇索引的key值，因此innodb中既有聚簇索引也有非聚簇索引，而myisam中只有非聚簇索引。

2、mysql索引结构有哪些，各自的优势是什么？

​ 索引的数据结构和具体存储引擎的实现有关，mysql中使用较多的索引有hash索引，B+树索引，innodb的索引实现为B+树索引，memory存储引擎索引实现为hash索引。

​ B+树是一个平衡的多叉树，从根节点到每个叶子结点的高度差值不超过1，而且同层级的二节点间有指针相关连接，在B+树的常规检索，从根节点到叶子结点的搜索效率基本相当，不会出现大幅度的波动，而且基于索引的顺序扫描时，也可以利用双向指针快速的左右移动，效率非常高。因此，B+树索引被广泛用于数据库，文件系统等场景。

​ hash索引就是采用一定的哈希算法吧键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

​ 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值，前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键值所在的位置，然后跟根据链表往后扫描，直到找到对应的数据。

​ 如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过hash算法之后，又可以变成不连续的了，就没办法再利用索引完成范围查询检索。

​ hash索引也没办法利用索引完成排序，以及like这样的部分模糊查询。

​ hash索引也不支持多列联合索引的最左匹配规则。

​ B+树索引的关键字索引效率比较平均，不像B树哪样波动大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞的问题。

3、简述mysql中索引类型有哪些，以及对数据库性能的影响？

类型

• 普通索引：允许被索引的数据列包含重复的值
• 唯一索引：可以保证数据记录的唯一性
• 主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用来唯一标识一条记录，使用关键字primary key来创建
• 联合索引：索引可以覆盖多个数据列
• 全文索引：通过建立倒排索引，可以极大的提升检索的效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键性的技术

影响

1. 使用可以极大的提高数据的查询速度
2. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
3. 但是对降低插入、删除、更新表的速度，因为在执行这些写操作的时候，还要更新索引文件
4. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间如果要遍历聚簇索引，那么需要的空间就会更大，如果非聚簇索引很多，一旦聚簇索引改变，那么所有的非聚簇索引都会跟着改变，因为非聚簇索引存储了聚簇索引的key值。

4、mysql执行计划怎么看？

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

explain执行计划包含的信息

其中最重要的字段为：id、type、key、rows、Extra

各字段详解

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
1、id相同：执行顺序由上至下

2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3、id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

1、SIMPLE：简单的select查询，查询中不包含子查询或者union
2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
3、SUBQUERY：在select 或 where列表中包含了子查询
4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
6、UNION RESULT：从union表获取结果的select

type

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的sql查询至少达到range级别，最好能达到ref

1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const

3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

注意：ALL全表扫描的表记录最少的表如t1表

4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

7、ALL：Full Table Scan，遍历全表以找到匹配的行

possible_keys

查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为NULL，则没有使用索引。
查询中如果使用了覆盖索引，则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

ref

显示索引的那一列被使用了，如果可能，是一个常量const。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

不适合在其他字段中显示，但是十分重要的额外信息

1、Using filesort ：
mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”

由于索引是先按email排序、再按address排序，所以查询时如果直接按address排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”

2、Using temporary：
使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by

3、Using index：
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图）
如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作

覆盖索引（Covering Index）：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
注意：
a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select *
b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能

4、Using where ：
使用了where过滤

5、Using join buffer ：
使用了链接缓存

6、Impossible WHERE：
where子句的值总是false，不能用来获取任何元祖

7、select tables optimized away：
在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

8、distinct：
优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作

综合Case

执行顺序
1（id = 4）、【select id, name from t2】：select_type 为union，说明id=4的select是union里面的第二个select。

2（id = 3）、【select id, name from t1 where address = ‘11’】：因为是在from语句中包含的子查询所以被标记为DERIVED（衍生），where address = ‘11’ 通过复合索引idx_name_email_address就能检索到，所以type为index。

3（id = 2）、【select id from t3】：因为是在select中包含的子查询所以被标记为SUBQUERY。

4（id = 1）、【select d1.name, … d2 from … d1】：select_type为PRIMARY表示该查询为最外层查询，table列被标记为 “derived3”表示查询结果来自于一个衍生表（id = 3 的select结果）。

5（id = NULL）、【 … union … 】：代表从union的临时表中读取行的阶段，table列的 “union 1, 4”表示用id=1 和 id=4 的select结果进行union操作。

5、mysql 幻读的详解、实例及解决办法

脏读/不可重复读的概念都比较容易理解和掌握，这里不在讨论

事务隔离级别（tx_isolation）

mysql 有四级事务隔离级别 每个级别都有字符或数字编号

级别	symbol	值	描述
读未提交	READ-UNCOMMITTED	0	存在脏读、不可重复读、幻读的问题
读已提交	READ-COMMITTED	1	解决脏读的问题，存在不可重复读、幻读的问题
可重复读	REPEATABLE-READ	2	mysql 默认级别，解决脏读、不可重复读的问题，存在幻读的问题。使用 MMVC机制 实现可重复读
序列化	SERIALIZABLE	3	解决脏读、不可重复读、幻读，可保证事务安全，但完全串行执行，性能最低

我们可以通过以下命令 查看/设置 全局/会话 的事务隔离级别

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

# 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下
# SET [GLOABL] config_name = 'foobar';
# SET @@[session.|global.]config_name = 'foobar';
# SELECT @@[global.]config_name;

SET @@gloabl.tx_isolation = 0;
SET @@gloabl.tx_isolation = 'READ-UNCOMMITTED';

SET @@gloabl.tx_isolation = 1;
SET @@gloabl.tx_isolation = 'READ-COMMITTED';

SET @@gloabl.tx_isolation = 2;
SET @@gloabl.tx_isolation = 'REPEATABLE-READ';

SET @@gloabl.tx_isolation = 3;
SET @@gloabl.tx_isolation = 'SERIALIZABLE';

幻读

首先我们要搞明白何谓幻读，目前网上的众多解释幻读的博文个人感觉仔细设想一下就能找出推翻的例子，就像博文把 非阻塞IO 等同为 异步IO，然后好多文章都纷纷借用，其实这俩货是完全不同，非阻塞IO 是 同步IO 中的一种模式，并非 异步IO。错误的观点都被大众认同的 “正确化” 了，扯远了，回归主题。

幻读会在 RU / RC / RR 级别下出现，SERIALIZABLE 则杜绝了 幻读，但 RU / RC 下还会存在脏读、不可重复读，故我们就以 RR 级别来研究 幻读，排除其他干扰。

注意：RR 级别下存在幻读的可能，但也是可以使用对记录手动加 X锁 的方法消除幻读。SERIALIZABLE 正是对所有事务都加 X锁 才杜绝了 幻读，但很多场景下我们的业务 sql 并不会存在 幻读 的风险。SERIALIZABLE 的一刀切虽然事务绝对安全，但性能会有很多不必要的损失。故可以在 RR 下根据业务需求决定是否加锁，存在幻读风险我们加锁，不存在就不加锁，事务安全与性能兼备，这也是 RR 作为 mysql 默认隔是个事务离级别的原因，所以需要正确的理解 幻读。

幻读错误的理解：说幻读是 事务A 执行两次 select 操作得到不同的数据集，即 select 1 得到 10 条记录，select 2 得到 11 条记录。这其实并不是幻读，这是不可重复读的一种，只会在 R-U R-C 级别下出现，而在 mysql 默认的 RR 隔离级别是不会出现的。

这里给出我对幻读的比较白话的理解：

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

---

这里给出 mysql 幻读的比较形象的场景（借用我在知乎上的回答）：

table users: id primary key
事务T1

事务T2

step1 T1: SELECT * FROM `users` WHERE `id` = 1;
step2 T2: INSERT INTO `users` VALUES (1, ‘big cat’);
step3 T1: INSERT INTO `users` VALUES (1, ‘big cat’);
step4 T1: SELECT * FROM `users` WHERE `id` = 1;

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。

T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。

在 RR 隔离级别下，step1、step2 是会正常执行的，step3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为 T1 在 step1 中读取的数据状态并不能支撑后续的业务操作，T1：“见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以”。T1 不敢相信的又执行了 step4，发现和 setp1 读取的结果是一样的（RR下的 MMVC机制）。此时，幻读无疑已经发生，T1 无论读取多少次，都查不到 id = 1 的记录，但它的确无法插入这条他通过读取来认定不存在的记录（此数据已被T2插入），对于 T1 来说，它幻读了。

其实 RR 也是可以避免幻读的，通过对 select 操作手动加 行X锁（SELECT ... FOR UPDATE 这也正是 SERIALIZABLE 隔离级别下会隐式为你做的事情），同时还需要知道，即便当前记录不存在，比如 id=1 是不存在的，当前事务也会获得一把记录锁（因为InnoDB的行锁锁定的是索引，故记录实体存在与否没关系，存在就加 行X锁，不存在就加 next-key lock间隙X锁），其他事务则无法插入此索引的记录，故杜绝了幻读。

在 SERIALIZABLE 隔离级别下，step1 执行时是会隐式的添加 行(X)锁 / gap(X)锁的，从而 step2 会被阻塞，step3 会正常执行，待 T1 提交后，T2 才能继续执行（主键冲突执行失败），对于 T1 来说业务是正确的，成功的阻塞扼杀了扰乱业务的T2，对于T1来说他前期读取的结果是可以支撑其后续业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

RR级别下防止幻读

RR级别下只要对 SELECT 操作也手动加行（X）锁即可类似 SERIALIZABLE 级别（它会对 SELECT 隐式加锁），即大家熟知的：

# 这里需要用 X锁， 用 LOCK IN SHARE MODE 拿到 S锁 后我们没办法做 写操作
SELECT `id` FROM `users` WHERE `id` = 1 FOR UPDATE;

如果 id = 1 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。

这里我们就展示下 id = 1 的记录不存在的场景，FOR UPDATE 也会对此 “记录” 加锁，要明白，InnoDB 的行锁（gap锁是范围行锁，一样的）锁定的是记录所对应的索引，且聚簇索引同记录是直接关系在一起的。

id = 1 的记录不存在，开始执行事务：
step1: T1 查询 id = 1 的记录并对其加 X锁
step2: T2 插入 id = 1 的记录，被阻塞
step3: T1 插入 id = 1 的记录，成功执行（T2 依然被阻塞中），T1 提交（T2 唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

SERIALIZABLE级别杜绝幻读

在此级别下，我们便不需要对 SELECT 操作显式加锁，InnoDB会自动加锁，事务安全，但性能很低

step1: T1 查询 id = 2 的记录，InnoDB 会隐式的对齐加 X锁
step2: T2 插入 id = 2 的记录，被阻塞
step3: T1 插入 id = 2 的记录，成功执行（T2 依然被阻塞中）
step4: T1 成功提交（T2 此时唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

总结

RR 级别作为 mysql 事务默认隔离级别，是事务安全与性能的折中，可能也符合二八定律（20%的事务存在幻读的可能，80%的事务没有幻读的风险），我们在正确认识幻读后，便可以根据场景灵活的防止幻读的发生。

SERIALIZABLE 级别则是悲观的认为幻读时刻都会发生，故会自动的隐式的对事务所需资源加排它锁，其他事务访问此资源会被阻塞等待，故事务是安全的，但需要认真考虑性能。

InnoDB 的行锁锁定的是索引，而不是记录本身，这一点也需要有清晰的认识，故某索引相同的记录都会被加锁，会造成索引竞争，这就需要我们严格设计业务 sql，尽可能的使用主键或唯一索引对记录加锁。索引映射的记录如果存在，加行锁，如果不存在，则会加 next-key lock / gap 锁 / 间隙锁，故 InnoDB 可以实现事务对某记录的预先占用，如果记录存在，它就是本事务的，如果记录不存在，那它也将是本是无的，只要本是无还在，其他事务就别想占有它。

6、什么是mysql的主从复制？

mysql的主从复制是指数据可以从一个mysql数据库服务器主节点复制到一个或多个从节点。mysql默认采用的是异步复制的方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定数据库，或者特定的表。

7、mysql复制原理是什么?

1. master服务器将数据的改变记录在二进制binlog日志文件中，当master上的数据发生改变时，则将其改变写入到二进制日志文件中。
2. slave服务器会在一定的时间间隔内对master二进制日志进行探测器是否发生改变，如果发生了改变，则开启一个I/O Thread线程请求master二进制事件。
3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发生二进制事件，并保存至从节点本地的中继日志中，从节点将启动一个SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread 和SQL Thread将进入到睡眠状态，等待下一次被唤醒。

也就是说：

• 从库会生成两个线程，一个I/O线程，一个SQL线程
• I/O线程回去请求主库的binlog，并将得到的binlog写到本地的relay-log（中继日志）文件中；
• 主库会生成一个log dump线程，用来给从库I/O线程传binlog；
• SQL线程，会读取relay-log文件中的日志，并解析成sql语句逐一执行。

具体步骤

1. 从库通过手工执行change master to语句连接主库，提供了连接的用户一切条件（user、password、port、ip），并且让从库知道，二进制日志的起始位置（file名 position号）；start slave
2. 从库的IO线程和主库的dump线程建立连接
3. 从库根据change master to语句提供的file名和position号，IO线程向主库发起binlog的请求
4. 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
5. 从库IO线程接受binlog events，并存放到本地relay-log中，传过来的信息，会记录在master.info中。
6. 从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay会自动被清理purge。

8、mysql锁的类型有哪些？

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

基于锁的粒度分类：行级锁（innodb）、表级锁（innodb、myisam）、页级所（innodb）、记录锁、间隙锁、临键锁。

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

共享锁（share lock）：共享锁又称为读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能进行加写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现复读的问题。

排它锁（exclusive lock）：排它锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改的时候，不允许其他人同时修改，也不允许其他人读取，避免了出现脏数据和脏读的问题。

表锁（table lock）：表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；特点：粒度大，加锁简单，容易冲突；

行锁：行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可以正常的访问，特点：粒度小，加锁比标所麻烦，不容易冲突，相比表锁支持的并发要高。

记录锁（record lock）：记录锁也属于行锁的一种，只不过记录锁的范围只是表中某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录，加了记录锁之后数据可以避免数据在查询的时候被修改的重复度的问题，也避免了修改的事务未提交前被其他事务读取的脏读的问题。

页锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级所congruent少，但速度慢，所以取了折中的页级所，一次锁定相邻的一组记录。特点：开销和加锁时间介于表锁和行锁之间，会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

间隙锁：是属于行锁的一种，间隙锁是在事务加锁之后其锁住的是表记录的某个区间，当表的相邻ID之间出现了空隙则会形成一个区间，遵循左开右闭的原则。范围查询并且查询未命中记录，查询条件必须命中索引，间隙锁只会出现在Repeatable_read（可重复读）的事务级别。

临键锁（next-key lock）：也属于行锁的一种，并且它是innodb的行锁的默认算法，总结来说它就是间隙锁和记录锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

9、深入理解 Mysql 索引底层原理

一步一步推导出 Mysql 索引的底层数据结构。

Mysql 作为互联网中非常热门的数据库，其底层的存储引擎和数据检索引擎的设计非常重要，尤其是 Mysql 数据的存储形式以及索引的设计，决定了 Mysql 整体的数据检索性能。

我们知道，索引的作用是做数据的快速检索，而快速检索的实现的本质是数据结构。通过不同数据结构的选择，实现各种数据快速检索。在数据库中，高效的查找算法是非常重要的，因为数据库中存储了大量数据，一个高效的索引能节省巨大的时间。比如下面这个数据表，如果 Mysql 没有实现索引算法，那么查找 id=7 这个数据，那么只能采取暴力顺序遍历查找，找到 id=7 这个数据需要比较 7 次，如果这个表存储的是 1000W 个数据，查找 id=1000W 这个数据那就要比较 1000W 次，这种速度是不能接受的。

一、Mysql 索引底层数据结构选型

1. 哈希表（Hash）

哈希表是做数据快速检索的有效利器。

哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。

考虑这个数据库表 user，表中一共有 7 个数据，我们需要检索 id=7 的数据，SQL 语法是：

select \* from user where id=7;

哈希算法首先计算存储 id=7 的数据的物理地址 addr=hash(7)=4231，而 4231 映射的物理地址是 0x77，0x77 就是 id=7 存储的额数据的物理地址，通过该独立地址可以找到对应 user_name='g’这个数据。这就是哈希算法快速检索数据的计算过程。

但是哈希算法有个数据碰撞的问题，也就是哈希函数可能对不同的 key 会计算出同一个结果，比如 hash(7)可能跟 hash(199)计算出来的结果一样，也就是不同的 key 映射到同一个结果了，这就是碰撞问题。解决碰撞问题的一个常见处理方式就是链地址法，即用链表把碰撞的数据接连起来。计算哈希值之后，还需要检查该哈希值是否存在碰撞数据链表，有则一直遍历到链表尾，直达找到真正的 key 对应的数据为止。

从算法时间复杂度分析来看，哈希算法时间复杂度为 O（1），检索速度非常快。比如查找 id=7 的数据，哈希索引只需要计算一次就可以获取到对应的数据，检索速度非常快。但是 Mysql 并没有采取哈希作为其底层算法，这是为什么呢？

因为考虑到数据检索有一个常用手段就是范围查找，比如以下这个 SQL 语句：

select \* from user where id \>3;

针对以上这个语句，我们希望做的是找出 id>3 的数据，这是很典型的范围查找。如果使用哈希算法实现的索引，范围查找怎么做呢？一个简单的思路就是一次把所有数据找出来加载到内存，然后再在内存里筛选筛选目标范围内的数据。但是这个范围查找的方法也太笨重了，没有一点效率而言。

所以，使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

1. 二叉查找树(BST)

二叉查找树是一种支持数据快速查找的数据结构，如图下所示:

二叉查找树的时间复杂度是 O(lgn)，比如针对上面这个二叉树结构，我们需要计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上看来是能做到高速检索的。此外二叉树的结构能不能解决哈希索引不能提供的范围查找功能呢？

答案是可以的。观察上面的图，二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也算是比较容易实现。

但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡了，已经退化为链表了，检索速度大大降低。此时检索 id=7 的数据的所需要计算的次数已经变为 7 了。

在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题，是不能直接用于实现 Mysql 底层索引的。

1. AVL 树和红黑树

二叉查找树存在不平衡问题，因此学者提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

首先简单介绍红黑树，这是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低。比如从 1 到 7 升序插入数据节点，如果是普通的二叉查找树则会退化成链表，但是红黑树则会不断调整树的形态，使其保持基本平衡状态，如下图所示。下面这个红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。

红黑树拥有不错的平均查找效率，也不存在极端的 O(n)情况，那红黑树作为 Mysql 底层索引实现是否可以呢？其实红黑树也存在一些问题，观察下面这个例子。

红黑树顺序插入 1~7 个节点，查找 id=7 时需要计算的节点数为 4。

红黑树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 6 次。观察一下这个树的形态，是不是当数据是顺序插入时，树的形态一直处于“右倾”的趋势呢？从根本上上看，红黑树并没有完全解决二叉查找树虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张，但是数据库中的基本主键自增操作，主键一般都是数百万数千万的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗，我们数据库不可能忍受这种无意义的等待的。

现在考虑另一种更为严格的自平衡二叉树 AVL 树。因为 AVL 树是个绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

AVL 树顺序插入 1~7 个节点，查找 id=7 所要比较节点的次数为 3。

AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题。

总结一下 AVL 树的优点：

1. 不错的查找性能（O（logn）），不存在极端的低效查找的情况。
2. 可以实现范围查找、数据排序。

看起来 AVL 树作为数据查找的数据结构确实很不错，但是 AVL 树并不适合做 Mysql 数据库的索引数据结构，因为考虑一下这个问题：

数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

1. B 树

下面这个 B 树，每个节点限制最多存储两个 key，一个节点如果超过两个 key 就会自动分裂。比如下面这个存储了 7 个数据 B 树，只需要查询两个节点就可以知道 id=7 这数据的具体位置，也就是两次磁盘 IO 就可以查询到指定数据，优于 AVL 树。

下面是一个存储了 16 个数据的 B 树，同样每个节点最多存储 2 个 key，查询 id=16 这个数据需要查询比较 4 个节点，也就是经过 4 次磁盘 IO。看起来查询性能与 AVL 树一样。

但是考虑到磁盘 IO 读一个数据和读 100 个数据消耗的时间基本一致，那我们的优化思路就可以改为：尽可能在一次磁盘 IO 中多读一点数据到内存。这个直接反映到树的结构就是，每个节点能存储的 key 可以适当增加。

当我们把单个节点限制的 key 个数设置为 6 之后，一个存储了 7 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。

一个存储了 16 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。相对于 AVL 树而言磁盘 IO 次数降低为一半。

所以数据库索引数据结构的选型而言，B 树是一个很不错的选择。总结来说，B 树用作数据库索引有以下优点：

1. 优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；
2. 尽可能少的磁盘 IO，加快了检索速度；
3. 可以支持范围查找。
4. B+树

B 树和 B+树有什么不同呢？

第一，B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

二、Innodb 引擎和 Myisam 引擎的实现

Mysql 底层数据引擎以插件形式设计，最常见的是 Innodb 引擎和 Myisam 引擎，用户可以根据个人需求选择不同的引擎作为 Mysql 数据表的底层引擎。我们刚分析了，B+树作为 Mysql 的索引的数据结构非常合适，但是数据和索引到底怎么组织起来也是需要一番设计，设计理念的不同也导致了 Innodb 和 Myisam 的出现，各自呈现独特的性能。

MyISAM 虽然数据查找性能极佳，但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能，而且他支持行级锁。Mysql 建立表的时候就可以指定引擎，比如下面的例子，就是分别指定了 Myisam 和 Innodb 作为 user 表和 user2 表的数据引擎。

执行这两个指令后，系统出现了以下的文件，说明这两个引擎数据和索引的组织方式是不一样的。

Innodb 创建表后生成的文件有：

• frm:创建表的语句
• idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有

• frm:创建表的语句
• MYD:表里面的数据文件（myisam data）
• MYI:表里面的索引文件（myisam index）

从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样，MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb 引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。下面将从底层实现角度分析这两个引擎是怎么依靠 B+树这个数据结构来组织引擎实现的。

1. MyISAM 引擎的底层实现（非聚集索引方式）

MyISAM 用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

1. Innodb 引擎的底层实现（聚集索引方式）

InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name=‘Bob’。

这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY！拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

问题来了，为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

其实很简单，因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

在进行 InnoDB 和 MyISAM 特点对比时谈到，MyISAM 查询性能更好，从上面索引文件数据文件的设计来看也可以看出原因：MyISAM 直接找到物理地址后就可以直接定位到数据记录，但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，那当然 MyISAM 查询性能更高。

本文首先探讨了哪种数据结构更适合作为 Mysql 底层索引的实现，然后再介绍了 Mysql 两种经典数据引擎 MyISAM 和 InnoDB 的底层实现。最后再总结一下什么时候需要给你的表里的字段加索引吧：

1. 较频繁的作为查询条件的字段应该创建索引；
2. 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；
3. 更新非常频繁的字段不适合创建索引。

10、mysql为什么需要主从同步？

1. 在业务复杂的系统中，有这么一个情景，有一句sq语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即时主库出现了锁表的情景，通过读从库可以保证业务的正常运作。
2. 做数据的热备。
3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘的I/O访问的频率，提高单个机器的I/O性能。

11、怎么处理mysql 的慢查询？

1. 开启慢查询日志，准确定位到哪个sql语句出现了问题。
2. 反洗sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
3. 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
4. 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横行或者纵向的分库分表。