mysql调优

参考资料：2024B站高质量面试题：MySQL面试夺命连环50问（B+树、mysql索引、索引优化、SQL优化）7天刷完从12.8K涨到30K！_哔哩哔哩_bilibili

一、mysql基本

1、B树和B+树的区别？

（1）B树和B+树都是小的索引在左边，大的索引在右边，每一次节点的寻址都要进行一次磁盘I/O，I/O次数越少，性能越好，B树每一个节点都存储了索引和数据，导致树更高，B+树把所有的数据都放在了叶子结点，非叶子结点只存储索引，这样会导致树更宽更矮，I/O检索的次数更少。

（2）B+树通过双向链表连接每一个叶子结点，范围查询性能更高，减少I/O次数。

B+tree 结构实现数据索引具有如下优点：

1. 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘VO的次数就会大大减少，数据查询的效率也会更快。
2. 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
3. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。

2、B+树的生成过程

（1）依此插入数据，建立主键索引进行自动排序

（2）mysql为数据进行分组，把组里面最小的索引放到页目录中（空间换时间），查询的时候根据二分查找法先对索引进行定位，这一堆东西放到数据页中进行存储，每一页都有页头（包括表头，上一页下一页等信息）和页尾， 每一页mysql默认大小为16kb，页与页之间根据表头，上一页下一页形成双向链表。

（3）为每一个数据页增加一个目录，叫索引页，把每一个数据页最小的索引提取到索引页中，也形成页目录。每一个索引都有一个页指针指向对应的数据页。

3、mysql引擎层是如何工作的

首先关闭自动提交，执行修改语句 update employee set name = 'guangzhijiayou' where id = 1;

（1）客户端连接mysql的服务层，然后执行器调用innoDB引擎

（2）去BufferPool缓存找id=1的数据有没有在bufferPool中

1）bufferPool中有则更新对应数据

2）bufferPool中没有则去磁盘idb文件中加载对应数据，根据索引查找对应数据所属的数据页，找到之后会把这一条数据所属的一整页缓存到bufferPool中，然后把修改之前的缓存页放到undolog中进行备份。undolog用于数据回滚和事务隔离。

（3）更新完之后bufferPool中的页称为脏页（因为此时bufferPool中的这条数据和磁盘中的这条数据已经不一样了）， 执行commit提交，把数据同步到磁盘，此时才会恢复到正常页。

4、sql的执行过程

（1）建立数据库连接

（2）首先来到缓存来进行处理

key：当前的sql语句，value：当前的数据

通常存在缓存中的数据通常是不变的数据，但是数据经常变，所以弃用

（3）解析器：看下sql语法是否正确

（4）预处理器：判断表名、字段名是否正确

（5）优化器：优化sql，例如查询条件使用联合索引，通常我们遵循最左前缀原则，但是如果我们写的sql不说按照最左写的，优化器会把条件放到最左边，符合最左前缀原则。

（6）执行器操作引擎，返回执行结果。

5、单表多少算大？

阿里代码规范：单表行数超过500万或者单表容量超过2G，就推荐分库分表。

如果预计三年后的数据都到不了就不要分库分表。

6、mysql是怎么做到Redolog崩溃恢复的

BufferPool中的数据还没有同步到磁盘就挂掉了，怎么保证事务不丢失呢

（1）磁盘中的数据页同步到BufferPool中，BufferPool中的新数据就会到Redolog中，如果这时候mysql崩溃了，会把Redolog中的新数据恢复到BufferPool 中，保证数据不丢失。

（2）写到Redolog中的性能比写到idb（磁盘）中的性能更快。Redolog是顺序写，空间满了会回到第一个位置继续写，磁盘是存到一个不固定位置

（3）提供Redolog Buffer，通过刷盘策略（0、1、2）把数据同步到Redolog中

刷盘策略

（1）参数设置为0，每隔一秒写入（延迟写，延迟刷）

会丢失1s内的数据

（2）参数设置为1（默认参数），提交时写入（实时写，实时刷，强一致性，一致性是最高的），通过fsync函数刷到redolog中

（3）参数设置为2，写入系统缓存（延迟写，延迟刷），先写入操作系统的pageCache中，由操作系统调度fsync函数持久化到磁盘

7、binlog刷盘机制

8、binlog和redolog缺一不可吗？

（1）Redolog是InnoDB存储（早期mysql没有Inno DB存储引擎，InnoDB引擎出现才有事务的概念，才能通过Redolog来恢复事务数据的持久性）引擎独有的

（2）binlog整个mysql都能用，用于恢复磁盘数据，

binlog记录所有的日志，Redolog只记录事务的

二、索引

1、索引介绍

索引是关系型数据库中能实现快速定位数据的一种存储结构，提前按照一定的规则进行排序和组织，能够快速定位到目标数据，其设计思想是以空间换时间。

2、索引的种类

mysql默认B+tree索引

3、索引数据结构

（1）二叉树

（2）红黑树（平衡二叉树）解决二叉树不分叉的问题，降低树的高度

（3）B-树（平衡多路）

一个节点存储多个元素

（4）B+树

mysql实现的是16阶的B+树

1）所有的数据都存储到叶子节点，在非叶子节点冗余寻址节点，树更矮

2）非叶子节点不存储数据

3）在叶子节点每一个节点上放双向指针，解决B树范围查询不好的问题

B+tree 结构实现数据索引具有如下优点：

1. 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘VO的次数就会大大减少，数据查询的效率也会更快。
2. 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
3. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。

4、如果一个表没有主键索引还会创建B+树吗？

答案是会的！！！

InnoDB是MySQL中的一种存储引擎，它会为每个表创建一个主键索引。如果表没有明确的主键索引，InnoDB会使用一个隐藏的、自动生成的主键（rowid）来创建索引。这个隐藏的主键索引使用的就是B+树结构。因此，在InnoDB中，即使表没有明确的主键索引，也会创建一个B+树索引。

5、hash索引

使用不多，只支持自适应hash索引，Memory引擎支持hash索引

有点类似于HashMap底层的数据结构，存的是键值对，hash计算取模放到对应的整数的槽位上。

6、聚集（簇）索引和非聚集索引

（1）聚集索引（主键索引）

B+树，叶子结点中包括索引和数据（索引和数据聚集到一起到所以叫聚集索引）

1. 聚簇索引将数据存储在索引树的叶子节点上。
2. 聚簇索引可以減少一次查询，因为查询索引树的同时就能获取到数据。
3. 聚簇索引的缺点是，对数据进行修改或删除操作时需要更新索引树，会增加系统的开销。
4. 聚簇索引通常用于数据库系统中，主要用于提高查询效率。

注：InnoDB默认的数据结构上聚簇索引，MyISAM是非聚簇索引

（2）非聚集索引（非主键索引，又称为二级索引/辅助索引）

叶子节点存储索引和索引对应的主键，例如如果要查这个名字对应的索引以外的其他数据，这个时候涉及到回表，回到聚集索引的树。

如果使用非聚集索引查询索引以外的数据，性能较差，如果只是查索引的数据，称为索引覆盖，性能差不多。

（3）覆盖索引：查询的字段都在索引列中的情况，所以在日常开发中，尽量不要用select *，需要查什么字段就写什么字段，如果出现覆盖索引的情况，查询会快很多。

（4）索引下推：Mysql5.6针对扫描二级索引的一项优化改进，二级索引定位到范围数据之后，一次性回表。用来在范围查询时减少回表的次数。适用于MyISAM和InnoDB

7、单列索引和联合索引

（1）单列索引

索引只有一个字段，可根据建立数据库选择的排序规则进行排序

（2）联合索引

索引有多个字段，排序时先排最左侧的字段，然后依此往后排序，注意，只有第一个字段时有序的。

最左前缀原则

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。

如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了

当创建（a,b,c）复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！

例如：where a=''，必须有字段a，否则需要进行全表扫描。

8、单列索引和联合索引分别什么场景创建，优势是什么

联合索引的优势

（1）减少开销

建一个联合索引（a,b,c），实际相当于建了（a）,（a,b），（a,b,c）三个索引，但是只生成一个B+树，如果建立三个索引，需要生成三颗B+树。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

（2）覆盖索引

对联合索引（a,b,c）如果有如下sql的

select a,b,c from table where a='xxx' and b='xx;

那么mysql可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别是随机io其实DBA主要的优化策略.所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段

（3）效率高

索引列多，通过联合索引筛选出的数据越少.比如有1000w条数据的表，有如下sql：

select col1, col2,col3 from table where col1=1 and col2=2 and col3=3;

假设：假设每个条件可以筛选出10%的数据

A：如果只有单列索引，那么通过该索引能筛选出1000w*10%=100w条数据，然后再回表从100w调数据中找到符合col2=2 and col3=3的数据，然后再排序，再分页，以此类推（递归）；

B：如果是（col1,col2,col3）联合索引，通过三列索引筛选出1000w*10%*10%*10%=1w，效率提升。

9、索引的优缺点，什么时候用什么时候不用

优点：

1. 提高检索效率
2. 降低排序成本，索引对应的字段是会有一个自动排序功能的，默认是升序asc。

它缺点是

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占用物理空间，数据量越大，占用空间越大，现在磁盘便宜，还好。
3. 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

索引有坏有好，什么时候需要索引，什么时候不需要？

适合：

1.较频繁的作为查询条件的字段应该创建索引

不适合：

1. 字段值的唯一性太差不适合单独做索引
2. 更新非常频繁的字段不适合
3. 不会出现在where句中的字段不适合。

三、优化

关于SQL优化方法，包括5点

1）创建索引减少扫描量；

2）调整索引减少计算量；

3）索引覆盖（减少不必访问的列，避免回表查询）；

4）干预执行计划；

1、索引优化

1.全值匹配

MySQL全值匹配是指在使用复合索引时，查询条件要包含索引的所有列，才能最大程度地利用索引。

2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

-带头大哥不能死，中间兄弟不能断；

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列范围查询会使后面字段无序，造成部分索引失效。

-范围之后全失效；

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句覆盖索引不写星；

6.不等空值还有or，索引失效要少用；

mysql在使用不等于（！=或者<>），not in,not exists 的时候无法使用索引会导致全表扫描＜小于、＞大于、=这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

is null,is not null 一般情况下也无法使用索引

7.like百分号写到右边，前模糊会进行全表扫描，后模糊会优化到rang级别

a）如果非要模糊查，可以使用覆盖索引，可以优化到index级别（比range级别差）

b）或者使用搜索引擎

8.字符串不加单引号，底层会进行类型转换导致全表扫描

2、范围查询优化

优化方法：将大范围拆分成小范围

索引使用总结：

全值匹配我最爱，（最左前缀要遵守带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

3、trace工具（mysql5.6）

查看mysql底层做了什么事情

第一阶段：SQL准备阶段，格式化sql

第二阶段：SQL优化阶段，条件处理

第三阶段：预估表的访问成本，预估全表扫描情况和使用索引的情况，执行SQL

4、SQL改写

（1）避免select * ，增加查询解析器成功，不走覆盖索引，增加网络消耗

（2）小表驱动大表

主表选择数据量较小的表

（3）用连接查询代替子查询

子查询需要执行两次数据库查询，一次外部查询，一次嵌套子查询。

连接查询可以更好的利用数据库索引

（4）为group by字段设置索引

（5）批量插入或批量删除

insert into order（字段1，字段2） values（1，2），（1，2），（1，2）

（6）使用limit优化分页查询

可以通过索引（覆盖索引和主键索引）再进一步优化

（7）使用表连接代替in

（8）用union all代替union，因为union all 不会去重

（9）join不要太多

但是join 代替in可以

不利于后期维护，例如分库分表拆分要改sql

总结

SQL优化是提高数据库性能的重要方法，在实际开发中我们的SQL要尽量遵守以下几点原则，避免留下技术债：

1. 减少数据扫描
2. 返回更少数据
3. 减少交互次数
4. 减少服务器CPU及内存开销