MySQL索引、基础补充以及优化笔记-上

MySQL索引、基础补充以及优化笔记-上

MyISAM存储引擎索引实现

MyISAM中为非聚集索引，也就是：索引，数据分开存储。索引存储在MYI文件中，数据存储在MYD文件中。在搜索数据时，先判断查找字段是否有索引，如果有则开始从MYI文件中的根节点开始，定位索引元素。一个一个节点查找，内部折半查找，最终找到叶子节点。叶子节点中存的就是索引所在行的磁盘文件地址。在根据这个地址，在MYD文件中快速定位到对应行的位置，将数据进行取出。

---

Innodb存储引擎索引实现

Innodb中为聚集索引，也就是：索引和数据合并存储，表数据文件本身就是按照B+树组织的一个索引结构文件，叶节点包含了完整的数据记录。一张数据表对应两个文件，一个是表结构文件FRM文件，另一个是数据以及索引的合并存储文件IBD文件。Innodb的每张表自带主键索引，其IBD文件中的存储结构为：

叶子节点中存放的是索引所在行的其他列数据。

非主键索引结构为：

同样也是使用B+树进行构建，叶子节点存放的是索引所在行的主键。

标准B+树，相邻叶子节点之间是一个单向连接，而mysql中，是B+树一个变种，相邻叶子节点之间存放的是一个双向连接。并且每个叶子节点中还会有很小的一部分区域用来存储相邻叶子节点的地址。

---

Hash索引存储结构

很多时候Hash索引要比B+树索引更高效，仅满足“=”，“IN”，不支持范围查询。但是工作中基本不用Hash索引，因为它不支持范围查询，不支持模糊查询，并且可能存在hash冲突问题。

---

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

答：如果没有建立主键索引，则会由Innodb帮你寻找一个主键，逐列寻找一个所有元素都不相同的列。也可以说对这个列添加了一个唯一索引。然后会使用这个列来组织整张表的所有数据。如果所有列都不满足元素不同这个要求，mysql会帮这张表维护一个整型自增的隐藏列，类似于Rowid，并使用这个列维护整张表的结构。所以，如果没有主键MySQL会多做很多事情，浪费数据库资源。

​ 查询数据的时候，免不了数据大小比对。使用整型比大小会更快，效率更高。其次，正式开发环境下使用的SSD高速存储硬盘会十分昂贵，所以应该尽量减少数据存储的空间量。如果使用UUID作为主键，则会多出很多不必要的存储量。**那为何需要自增呢？**方便数据的插入。当我们没有使用主键递增ID的时候，会多一个平衡索引树的操作。当我们使用主键递增ID的时候，所有新增的主键都为当前主键的最大值，所以只需要在索引树的最右边加上一个记录即可。

---

联合索引的底层存储结构

底层也为B+树。根据条件依次对比，先后分别比对name,age,position。如果结果唯一则停止后续条件比对。

**相关优化操作：**如果将条件拆开分别使用三条查询语句:

SELECT * FROM employees WHERE name = 'Bill' and age = 31;
SELECT * FROM employees WHERE age = 31 and position = 'dev';
SELECT * FROM employees WHERE position = 'manager';

则只有第一条数据会使用索引，剩余两条都不会使用索引，可以通过EXPLAIN命令进行查询。所以为了在查询时使用索引，则必须按照建立索引的顺序进行查询。本例中必须按照name,age,position的顺序进行查询，才会使用索引。

SELECT * FROM employees WHERE name = 'Bill' and age = 31 and position = 'dev';

在最左前缀索引中，在相同的前置条件中，后续条件会依次排序。在本例子中，只有相同name条件下的age才是有序排列的。

---

MySQL读写分离

主从同步

主节点先将数据存储到data库中，再将sql语句存入到binlog文件中。从节点中存在两个线程，第一个线程为I/O线程，用来进行io读写，将主节点中的数据进行拷贝，放入从节点中的relay binlog文件中（中继日志）。第二个线程为SQL线程，用来从relay binlog中一条一条读出sql语句，并对从节点中的data库进行数据操作。

show binary logs; // 列出服务器上的二进制日志文件
show master status; //显示主节点正在使用的二进制日志文件以及状态
show binlog events in 'binlog name'; //显示binlog文件的日志内容

binlog文件中只会存储对数据文件的修改操作。binlog文件还可以用来恢复数据库文件。

弊端：牺牲了数据库之间的一致性可能导致脏读，并且存在一定的延迟。可能存在数据库之间同步中断的问题（因为网络问题导致数据并没有同步）。

对于数据库不满足一致性而导致脏读情况的问题，可以使用强制查询主库的方式来解决。

对于同步中断问题的解决方法是：采用半同步方法，使用了第三发的插件来实现。具体过程为，web服务端将语句发送到主节点进行运行，要等到结果存入主节点的data库并且将sql语句存入到从节点的relay binlog文件后返回给主节点一个通知，此时主节点才会返回语句成功运行的结果通知。

---

企业中常用的架构方案

一主一从：用来做数据的备份，提高数据可用性。不需要考虑数据一致性。但是不能代替数据的备份，因为如果在主节点清空了表，那么从节点也会清空表。所以只能用来做容灾，数据该备份还是要备份。如果数据丢失了，则可以使用快照恢复。binlog文件虽然可以备份，但不是恢复数据的主要手段。

一主多从：从节点不适应过多，一般为2-4个从节点。否则会在主从同步上耗费大量的事件。如果为一主四从，则将三个从节点作为正常的读写，另外一个从节点做另外的特殊操作，比如十分耗时的整表操作等等。三个从节点中还需要选举一个节点来作为主节点的备份节点，当主节点宕机以后来充当主节点。

双主：业务场景中拥有大量的写操作，单个主节点承受不来，则使用双主架构。根据写操作的id进行一个取模操作，或者id是单数则在一个主节点完成，为双数则在另外一个主节点完成。如果是一个字符串，则可以先hash，再把结果取模。如果一个节点宕机，整个数据都会很混乱。

**级联同步：**解决master节点压力过大，主节点只将数据同步给一个从节点。这个从节点再见数据同步给另外的多个从节点。好处是：分散主节点的压力，其次如果主节点宕机以后，剩下的部分是一个天然的主从结构。弊端是：如果主节点同步的从节点宕机以后，二级从节点就变成了单独节点。

环形多主：多个主节点都可以接多个从节点，从而形成一个大型环形多主多从结构。弊端是：一个主节点宕机以后，整个结构全部不可使用。

读写分离正式结构：增加一个代理节点：

app server中所有的读与写的请求全部往代理节点中发送。代理节点去解析请求，如果语句是写入操作，则把该请求发送至主节点，如果是读操作，则把请求以负载均衡的方式发送至从节点。我们需要将数据源的连接配置到代理节点中即可。代理节点也会存在宕机和单点故障以及其他一些故障的可能性，可以通过横向扩充代理节点弥补。

---

基于Atlas代理实现读写分离

实施流程：

1. 修改主节点配置my.cnf
2. 修改从节点配置my.cnf ，配置主节点地址、端口、密码
3. 安装atlas
4. 配置atlas

master my.cnf:

server-id=99  //master id 不能和集群中其他mysql实列的id相同
log-bin=mysql-bin //binlog 文件前缀
binlog-do-db = xxx //对应需要同步的数据库
//以下都是不同步的数据库
binlog-ignore-db = information_schema 
binlog-ignore-db = mysql
binlog-ignore-db = personalsite
binlog-ignore-db = test

配置完成后重启数据库。可以通过show master status;查看主节点正在使用的二进制日志文件。

slave my.cnf:

server-id=99  //slave id 
log-bin=mysql-bin //可加可不加，如果这个节点需要向其他节点同步数据时，需要添加这项
replicate-do-db = xxx //对应需要同步的数据库
//以下都是不同步的数据库
replicate-ignore-db = information_schema 
replicate-ignore-db = mysql
replicate-ignore-db = personalsite
replicate-ignore-db = test

动态配置主节点连接信息

change master to master_host='127.0.0.1:3306',master_user='root',master_password='123456789';

配置前必须通过stop slave; 命令关闭从节点后，才可以运行此项动态配置。配置完后使用start slave;命令再次开启从节点。使用命令show slave status;查看从节点状态。

将配置文件放入对应的文件夹中：

编写初始化脚本 init.sql ：

change master to master_host='127.0.0.1:3306',master_user='root',master_password='123456789';
reset slave;
start slave;

使用docker-compose 编写yaml文件：

slave2节点仿照slave1节点编写，注意更换端口号。下面提前给出atlas的相关yaml配置：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5SlawK45-1633872308608)(…/…/…/…/个人图片/笔记图片/image-20211010155456812.png)]

atlas：下载相关的包，进入conf文件夹中修改test.conf配置文件：

注意这边的端口号使用的是容器中的端口号。这边使用了名字来代替，实际使用应该转换成相对应的地址。

pwds项中格式为 [用户名]:[密码]，密码是经过加密的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dMINFA4S-1633872308610)(…/…/…/…/个人图片/笔记图片/image-20211010160450169.png)]

后面的配置与日志相关。

使用命令dc up -d（docker compose 命令）启动。后续连接数据库，如果在从节点进行修改操作的话，确实可以修改从节点中的数据，但是这个操作并不会同步到主节点中。

---

MySQL基础补充

SQL语句执行流程

连接器：负责管理连接。流程为：建立一个新的连接，接着通过系统库中的user表加载当前用户对应的权限，然后将权限加载进连接管理对象，进行权限校验。知道当前连接可以进行哪些操作。连接建立完成以后，更改权限相对应的操作时，此连接所对应的权限操作并不会改变。必须要新连接重新建立时重新加载权限时才会被更改。

之后会去到缓存区中查询是否有这条语句的缓存结果（缓存数据形式为键值对，key为查询sql，value为sql运行结果）。如果缓存区中存在对应的键值对，就直接从缓存区中读出结果集并返回。

如果缓存中没有找到，则会进入词法分析器：进行词法分析，语法分析。分析是什么操作，校验语法等使用方法是否正确。如果不正确就返回语法错误结果。

分析完成确保语句没有问题后，进入语句优化器：执行计划生产索引选择。比如联表查询中会判断查A表和查B表那个更快，以此来判断先查哪个表来作为条件。那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

接着进入执行器：调用数据库引擎接口获取查询结果。

MySQL数据库引擎都是按照插件形式，提供给了服务层调用接口。所以数据库引擎可以根据需要动态扩展。

数据库引擎会调用对应的数据库文件进行查找并记录结果记录集，结束后将结果记录集返回给执行器。执行器则会将结果集放入到缓存区中。最后将结果返回给客户端。

每次对数据库进行update等写操作，对数据库有修改的操作时，就会清除缓存区。所以缓存区适合读多写少的场景。需要注意的是mysql query cache 是对大小写敏感的，因为Query Cache 在内存中是以 HASH 结构来进行映射，HASH 算法基础就是组成 SQL 语句的字符，所以 任何sql语句的改变重新cache,这也是项目开发中要建立sql语句书写规范的原因吧

MySQL开启缓存操作为：my.cnf文件中：配置query_cache_type 参数，设置为0 的时候则是永远都不会使用缓存，设置为1的时候无论查询哪张表缓存中存在对应数据就从缓存中拿取结果集，设置为2的时候为按需使用缓存，例如我需要给test表设置缓存则需要运行SQL语句：select SQL_CACHE * from test;

show status like 'Qca%';可以用来查看缓存的情况。

注意：查询缓存这个功能在MySQL 8.0 版本后被移除（鸡肋）。

如果是一条更新语句，执行过程与查询类似如果有缓存，也是会用到缓存。然后拿到查询的语句，进行权限校验，接着进行相关更新操作，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。更新完成。

更新语句执行流程如下：分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

---

日志模块

Bin Log 归档日志（逻辑日志）

• 二进制日志，采用二进制编写
• Binlog在MySQL的Server层实现（引擎共用）
• Binlog为逻辑日志，记录的是一条语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”
• Binlog不限大小，追加写入，不会覆盖以前的日志

使用binlog，必须要前开启这个功能。通过语句show variables like '%log_bin%;'查询是否开启此项功能：

binlog 日志有三种格式，可以通过binlog_format参数指定。

• statement，记录的内容是SQL语句原文[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rMaSwu87-1633872308614)(…/…/…/…/个人图片/笔记图片/20211010204842.png)]但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。为了解决这种问题，我们需要指定为row。
• row，记录的内容还包含操作的具体数据，记录内容如下。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xvPw7Kj7-1633872308615)(…/…/…/…/个人图片/笔记图片/20211010204954.png)]row格式记录的内容看不到详细信息，要通过mysqlbinlog工具解析出来。update_time=now()变成了具体的时间。通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性。但是需要大量的容量来记录，占用空间大，恢复与同步时会更消耗IO资源，影响执行速度。
• mixed，记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

---

Redo Log 重做日志（物理日志）

• Innodb引擎特有，是记录InnoDB存储引擎的事务日志
• 记录的是运行的结果
• 有一个大小限定，不会无限大
• MySQL的WAL机制（Write-Ahead-Logging），先写日志再写磁盘
• 保存的文件名为：ib_logfile*
• 以循环的方式写入日志文件。不断的写与擦除，文件1写满时，切换到文件2，文件2写满时，切换到文件1

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

刷盘策略：InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
• 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
• 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

日志策略：redo log存储文件采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。