MySQL 索引的数据结构

MySQL是使用B+树的数据结构实现主键索引、唯一索引和非主键索引

B+树是对

B树

的一种变形树

1、非叶结点仅具有索引作用，也就是说，非

叶子结点

只存储key，不存储value。

2、树的所有叶子结点构成一个有序链表，可以按照key排序的

遍历

全部数据。

为什么使用B+树，与其他索引相比有什么优点？

1、由于mysql通常将数据存放在磁盘中，读取数据就会产生磁盘IO消耗。而B+树的非叶子节点中不保存数据，B树中非叶子节点会保存数据，通常一个节点大小会设置为磁盘页大小，这样B+树每个节点可放更多的key，B树则更少。这样就造成了，B树的高度会比B+树更高，从而会产生更多的磁盘IO消耗。

非叶子节点都放到内存中叶子节点都放到磁盘中

B+树叶子节点构成链表，更利用范围查找和排序。而B树进行范围查找和排序则要对树进行递归遍历

B树与B+树比较？

B+树层级更少，查找更快

B+树查询速度稳定：由于B+树所有数据都存储在叶子节点，所以查询任意数据的次数都是树的高度h

B+树有利于范围查找

B+树全节点遍历更快：所有叶子节点构成链表，全节点扫描，只需遍历这个链表即可

B树优点：如果在B树中查找的数据离根节点近，由于B树节点中保存有数据，那么这时查询速度比B+树快。

各种索引之间的区别

主键索引：与唯一索引之间的区别就在于不允许有空值，创建主键时会自动创建此索引。

普通索引：最基本的索引，没有什么特殊的限制。

唯一索引：与普通索引之间的区别就在于索引列的值必须是唯一的，但是可以有空值。

全文索引：仅可在使用了Myisam存储引擎的表中使用，针对较大的数据列。

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

B+ 树在进行范围查找时怎么处理

B+Tree因为他的叶子节点是从左向右递增的双向指针所以可以方便我们快速的进行范围查找例如20<q<50 先找到20的叶子结点然后向右开始遍历查找到50 节点之间是可以直接指向下一个节点的磁盘空间地址

B+树和Hash索引比较起来有什么优缺点吗？

Hash索引在不存在hash碰撞的情况下，只需一次读取，查询复杂度为O(1)，比B+数快

Hash索引是无序的，只适用于等值查询，而不能用于范围查询，也不具备排序性。根据hash索引查询出来的数据，还要再次进行排序

B+树索引的复杂度等于树的高度，一般为3-5次IO。B+树子节点上的数据是排过序的，可以用于范围查找

数据库的多列索引中，只能用B+数索引

联合索引（复合索引）的底层实现

在创建多列索引时，根据业务需求，where子句使用最频繁的一列放到最左边，因为mysql索引查询会遵循最左前缀匹配的原则，在检索数据时从联合索引的最左边开始匹配。当我们创建一个联合索引的时候，当创建(col1,col2,col3)联合索引时，相当于创建了(col)单列索引只能使用col1和col1,col2和col1,col2,col3三种组合；当然，col1,col3组合也可以，但实际上只用到了col1的索引，col3并没有用到！因为节点就失去了有序性，这就是最左前缀原则。

索引排序就是按照联合字段的顺序来逐一排序，就是先根据name来排第一次序，然后sex第二次，age第三次

打乱了联合索引的顺序。那么在查找数据时，就无法按照索引排好的顺序去查找数据，需要查找所有的子节点和叶子节点，此时产生全表扫描，造成索引失效，这就是最左匹配原则产生的原理。

MySQL 如何锁住一行数据

SELECT 语句能加互斥锁吗

begin ;

select * from 数据表 for update;

多个事务同时对一行数据进行 SELECT FOR UPDATE 会阻塞还是异常

阻塞

MySQL 使用的版本和执行引擎

5.6

InnoDB存储引擎 5.5之前都是MyISAM存储引擎

MySQL 不同执行引擎的区别

MyISAM引擎(非聚集索引)

三个文件分别为：frm:表结构 MYD:data数据 MYI:索引

InnoDB存储引擎（聚集索引）

两个文件分别为：frm:表结构 idb:数据加索引

InnoDb的表如果不主动创建主键的话他会自动去每一列去找都不重复的数据作为这个表主键去搭建一个B+tree的数据结构如果表中每一列都没有唯一的 mysql会自动生成一个隐藏列当做主键自增索引

innodb和myisam的区别

1、innodb支持事务，而myisam不支持事务。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务

2、innodb支持外键，而myisam不支持外键。

3、innodb默认表锁，使用索引检索条件时是行锁，而myisam是表锁（每次更新增加删除都会锁住表）。

4、innodb和myisam的索引都是基于b+树，但他们具体实现不一样，innodb的b+树的叶子节点是存放数据的，myisam的b+树的叶子节点是存放指针的。

5、innodb是聚簇索引，必须要有主键，一定会基于主键查询，但是辅助索引就会查询两次，myisam是非聚簇索引，索引和数据是分离的，索引里保存的是数据地址的指针，主键索引和辅助索引是独立的。

6、innodb不存储表的行数，所以select count( * )的时候会全表查询，而myisam会存放表的行数，select count(*）的时候会查的很快。

总结:mysql默认使用innodb，如果要用事务和外键就使用innodb，如果这张表只用来查询，可以用myisam。如果更新删除增加频繁就使用innodb。

MySQL 的事务概念

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

不可重复读

不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操

幻读

当同一个查询在不同的时间产生不同的行集时，所谓的虚幻问题就会在事务中发生。例如，如果执行了两次 SELECT，但第二次返回第一次未返回的行，则该行为“幻影”行。

注意：

1）幻读是针对数据插入（INSERT）操作来说的。

2）这里注意不要将不可重复读和幻读两者混淆了，不可重复读是指，同一事务内在不同时刻读到的同一批数据可能是不一样的，这是针对数据更新操作。而对于其他事务新插入的数据可以读到，这就引发了幻读问题。

MySQL 的事务隔离级别

读未提交（READ UNCOMMITTED）

读提交（READ COMMITTED）

可重复读（REPEATABLE READ）

串行化（SERIALIZABLE）

MySQL 的可重复读是怎么实现的

可重复读是指，同一事务内不会读到其他事务对已有数据的修改，即使其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。

1）MVCC

为了解决不可重复读，或者为了实现可重复读，MySQL 采用了 MVCC (多版本并发控制) 的方式（快照读）。MVCC 核心原理主要是版本链，undo日志和

快照

来实现的。

2）undo日志版本链

每次对记录进行改动，都会记录一条 undo 日志，每条 undo 日志也都有一个 roll_pointer 属性（INSERT 操作对应的 undo 日志没有该属性，因为该记录并没有更早的版本），可以将这些 undo 日志都连起来，串成一个链表

该记录每次更新后，都会将旧值放到一条 undo 日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。

可重复读和不可重复读的主要区别

这里面要提到一个重要的词：

快照

可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。

事务的四大特性

原子性一致性隔离性持久性

MySQL 是否会出现幻读

在重复读级别下, 快照读是通过MVCC(多版本控制)和undo log来实现的, 当前读是通过手动加record lock(记录锁)和gap lock(间隙锁)来实现的。所以从上面的显示来看，如果需要实时显示数据，还是需要通过加锁来实现。这个时候会使用Next-key锁来实现。

这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入。

InnoDB 存储引擎默认支持的隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB 存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock锁的算法，因此避免幻读的产生。

MySQL 的 gap 锁

Record Lock：单个行记录上的锁

Gap Lock：间隙锁，锁定一个范围，但不包含记录本身

Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

主键索引-等值查询-键存在

结论：T1对a=10的数据加了记录锁，记录锁与记录锁互斥。

唯一索引-等值查询-键存在

结论：T1对a=10的数据加了记录锁，记录锁与记录锁互斥。

主键索引-等值查询-键不存在

结论：T1对a为(10,20)的区间加了GAP锁，GAP锁与GAP锁之间不互斥。

唯一索引-等值查询-键不存在

结论：T1对a为(10,20)的区间加了GAP锁，GAP锁与GAP锁之间不互斥。

普通索引-等值查询-键存在

结论：T1对a为(10,20)的区间加了GAP锁

普通索引-等值查询-键不存在

结论：T1对a为(10,20)的区间加了GAP锁

唯一索引-范围查询

结论：T1对a为[10,20]的区间数据加了next-key锁。

普通索引-范围查询

结论：T1对a为[10,20]的区间数据加了next-key锁。

无索引

结论: 锁全表

普通索引可以使用limit减少锁区间范围。

MySQL 的主从同步原理

分库分表的实现方案

分表操作

如果表的字段太多那就垂直分表

垂直分表就是把一张表按列分为多张表，多张表通过主键进行关联，从而组成完整的数据。

如果表记录数太多那就水平分表

一般可以有范围法和hash法来进行水平分表。

假设现在有30万行数据，需要对它们进行水平分表：

范围法很好理解，可以让第1-100000行数据存放在表1，第100001-200000行数据存放在表2，第200001-300000行数据存放在表3，就完成了水平分表。

hash法也不难理解，就是选择一个合适的hash函数，比如说使用取模操作(%)，把%3结果为0的数据存放在表1，结果为1的存放在表2，结果为2的存放在表3即可。

按照月份分表

分库操作

按业务分库

按表分库

分布式唯一 ID 方案

基于ZooKeeper的节点版本号生成ID

Redis的INCRBY操作

SnowFlake算法(雪花算法重点)：

这 64 个 bit 中，其中 1 个 bit 是不用的，然后用其中的 41 bit 作为毫秒数，用 10 bit 作为工作机器 id，12 bit 作为序列号一个机房的一台机器上，在同一毫秒内，生成了一个唯一的 id。可能一个毫秒内会生成多个 id，但是有最后 12 个 bit 的序号来区分开来。

优点:

(1）高性能高可用：生成时不依赖于数据库，完全在内存中生成。

（2）容量大：每秒中能生成数百万的自增ID。

（3）ID自增：存入数据库中，索引效率高。

缺点：依赖与系统时间的一致性，如果系统时间被回调，或者改变，可能会造成id冲突或者重复。

实际中我们的机房并没有那么多，我们可以改进改算法，将10bit的机器id优化，成业务表或者和我们系统相关的业务。

原子操作类AtomlcXX

基于时间戳

UUID的方式

批量预生成ID

数据库的自增主键

如何优化慢查询

利用explain关键字可以模拟优化器执行sql查询语句来分析慢sql

索引没起作用的情况

1在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。

2 MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。

3查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

4 关联字段类型不一致导致索引失效

5 列运算（id+1=2） where条件后面使用函数

优化数据库结构

1 对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

2 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

分解关联查询

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。

explain 中每个字段的意思

1: id：id是select的执行顺序 id越大优先级越高越先被执行 id相同时下面的先执行

2:select_type 通过类型判断sql语句执行的顺序是否达到预期

3:table:表名已经衍生名

4:type:

5: partitions:匹配的分库分表的分区

6：possible_keys：预计可能使用的索引

7：key：实际查询的过程中使用的索引

8：ref 显示该表的索引字段关联了哪张表的哪个字段

9：rows：这是mysql估算的需要扫描的行数（不是精确值）。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好

10：extra: Using index Using where Using whereUsing index Using temporary Using filesort

extra

额外信息字段

：

Using index

查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高

mysql> explain select film_id from film_actor where film_id = 1;

Using where

查询的列未被索引覆盖，where筛选条件非索引的前导列

mysql> explain select * from actor where name = ‘a’;

Using where Using index

查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据

mysql> explain select film_id from film_actor where actor_id = 1;

NULL

查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引

mysql>explain select * from film_actor where film_id = 1;

Using filesort

mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行

缓冲池(buffer pool)

缓冲池(buffer pool)

是一种常见的降低磁盘访问的机制；

缓冲池通常以

页(page)

为单位缓存数据；

缓冲池的原理

:当执行一条sql语句的时候先去内存中的缓冲池里面查找数据页如果有直接返回如果没有就会从磁盘中读取数据页并加载到内存的缓存池中去然后在返回

有缓冲池的存在很大程度的减少了磁盘io带来的开销

缓冲池的预读机制

:通过提前读取多个数据页来优化io的一种方式就是说它会预加载相邻的数据页

传统的LRU算法

:会把数据页放入LRU链表里面每次读取某个数据页都会把当前页放到链表头部如果缓存池满了并且缓存池中没有当前数据页那么当前数据页会插入链表头部淘汰链表尾部的数据页

缓冲池优化的LRU算法(冷热数据分离)

:会分

为冷热两块数据区

入缓存池的页会先进入冷数据区如果当前数据被访问才会把当前页放到热数据区头部以解决预读机制失效问题

缓冲池污染

：当加载到大量数据页的时候会大量覆盖缓冲池里的一些热点数据所以可以设置在老年代停留的时间超过配置的阈值的时候才会进入新生代已解决大量热点数据被淘汰的问题

Mysql深度分页问题

1 通过子查询解决问题(因为普通索引需要

回表

那在

子查询

中直接

覆盖索引

然后

外层

通过

主键

去查)

优化前： select id,name from user where update_time> ‘2020-09-19’ limit 100000,10;

优化后: select id,name FROM user where id >= (select a.id from user a where a.update_time >= ‘2020-09-19’ limit 100000, 1) LIMIT 10;

2 INNER JOIN 延迟关联跟

子查询

的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是：延迟关联使用了inner join代替子查询。

SELECT acct1.id,acct1.name,acct1.balance FROM account acct1

INNER JOIN

(

SELECT a.id FROM account a WHERE a.update_time >= ‘2020-09-19’ ORDER BY a.update_time LIMIT 100000, 10)

AS acct2 on

acct1.id= acct2.id

; 与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

Mybatis的一级缓存和二级缓存

Mybatis一级缓存(默认开启) 一个sqlSession会话里面的所有查询操作都会保存到缓存中，一个请求中的所有增删改查操作都是在同一个sqlSession里面的，所以我们可以认为每个请求都有自己的一级缓存，执行了2次查询,但实际运行时只查询了一次数据库，第二次获取数据时直接从缓存中读取 Mybatis

二级缓存如果同一个sqlSession会话中2 个查询中间有一个 insert 、update或delete 语句，那么之前查询的所有缓存都会刷新。

mysql binlog详解

MySQL的二进制日志binlog可以说是MySQL最重要的日志，

它记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

DDL:主要的命令有create、alter、drop等，ddl主要是用在定义或改变表(table)的结构,数据类型，表之间的连接和约束等初始工作上，他们大多在建表时候使用

DML:主要命令是slect,update,insert,delete

binlog日志有两个最重要的使用场景

mysql主从复制

：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves 来达到master-slave数据一致的目的。

数据恢复

：通过

mysqlbinlog工具

来恢复数据。

binlog日志包括两类文件：

1)、二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。

2)、二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

原文链接：https://blog.csdn.net/weixin_41004756/article/details/127408325