mysql调优
参考资料：2024B站高质量口试题：MySQL口试夺命连环50问（B+树、mysql索引、索引优化、SQL优化）7天刷完从12.8K涨到30K！_哔哩哔哩_bilibili一、mysql根本
1、B树和B+树的区别？
https://i-blog.csdnimg.cn/direct/2a975eaf35c84d6a9dc73c289b53081d.png
（1）B树和B+树都是小的索引在左边，大的索引在右边，每一次节点的寻址都要进行一次磁盘I/O，I/O次数越少，性能越好，B树每一个节点都存储了索引和数据，导致树更高，B+树把所有的数据都放在了叶子结点，非叶子结点只存储索引，这样会导致树更宽更矮，I/O检索的次数更少。
（2）B+树通过双向链表连接每一个叶子结点，范围查询性能更高，减少I/O次数。
B+tree 结构实现数据索引具有如下优点：
[*]非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘VO的次数就会大大减少，数据查询的服从也会更快。
[*]所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得非常简朴。
[*]数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操纵。
2、B+树的天生过程
https://i-blog.csdnimg.cn/direct/0630c9ac94474667a708ba585ac6847d.png
（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提交，把数据同步到磁盘，此时才会规复到正常页。
https://i-blog.csdnimg.cn/direct/cb324009817e44028db0aff4424fd522.png
4、sql的执行过程
https://i-blog.csdnimg.cn/direct/66a73796052b48bc94f16170221d8fd0.png
（1）建立数据库连接
（2）首先来到缓存来进行处理惩罚
key：当前的sql语句，value：当前的数据
通常存在缓存中的数据通常是不变的数据，但是数据经常变，以是弃用
（3）解析器：看下sql语法是否精确
（4）预处理惩罚器：判定表名、字段名是否精确
（5）优化器：优化sql，比方查询条件使用联合索引，通常我们遵循最左前缀原则，但是如果我们写的sql不说按照最左写的，优化器会把条件放到最左边，符合最左前缀原则。
（6）执行器操纵引擎，返回执行结果。
5、单表多少算大？
阿里代码规范：单表行数凌驾500万或者单表容量凌驾2G，就保举分库分表。
如果预计三年后的数据都到不了就不要分库分表。
https://i-blog.csdnimg.cn/direct/2df0eb38081f44dcaa3eeeacec6ec957.png
6、mysql是怎么做到Redolog瓦解规复的
BufferPool中的数据还没有同步到磁盘就挂掉了，怎么保证事件不丢失呢
（1）磁盘中的数据页同步到BufferPool中，BufferPool中的新数据就会到Redolog中，如果这时候mysql瓦解了，会把Redolog中的新数据规复到BufferPool 中，保证数据不丢失。
（2）写到Redolog中的性能比写到idb（磁盘）中的性能更快。Redolog是顺序写，空间满了会回到第一个位置继续写，磁盘是存到一个不固定位置
（3）提供Redolog Buffer，通过刷盘策略（0、1、2）把数据同步到Redolog中
https://i-blog.csdnimg.cn/direct/34b09b9cda0e472ba06ea672bdd64374.png
刷盘策略
（1）参数设置为0，每隔一秒写入（延迟写，延迟刷）
会丢失1s内的数据
（2）参数设置为1（默认参数），提交时写入（及时写，及时刷，强一致性，一致性是最高的），通过fsync函数刷到redolog中
（3）参数设置为2，写入系统缓存（延迟写，延迟刷），先写入操纵系统的pageCache中，由操纵系统调治fsync函数长期化到磁盘
7、binlog刷盘机制
https://i-blog.csdnimg.cn/direct/183d19d55a044595b31f8312b6987d96.png
8、binlog和redolog缺一不可吗？
（1）Redolog是InnoDB存储（早期mysql没有Inno DB存储引擎，InnoDB引擎出现才有事件的概念，才气通过Redolog来规复事件数据的长期性）引擎独有的
（2）binlog整个mysql都能用，用于规复磁盘数据，
binlog记录所有的日志，Redolog只记录事件的
二、索引
1、索引先容
索引是关系型数据库中能实现快速定位数据的一种存储结构，提前按照一定的规则进行排序和构造，能够快速定位到目标数据，其筹划头脑是以空间换时间。
2、索引的种类
mysql默认B+tree索引
https://i-blog.csdnimg.cn/direct/e07658d3b6824133ae64bd1330e162e2.png
3、索引数据结构
（1）二叉树
https://i-blog.csdnimg.cn/direct/a7363867ada74ae49e5903bbad8fb7b1.png
（2）红黑树（均衡二叉树）办理二叉树不分叉的问题，降低树的高度
https://i-blog.csdnimg.cn/direct/e2fd12e183fe4cfaa457461fa7cdd739.png
（3）B-树（均衡多路）
一个节点存储多个元素
（4）B+树
mysql实现的是16阶的B+树
1）所有的数据都存储到叶子节点，在非叶子节点冗余寻址节点，树更矮
2）非叶子节点不存储数据
3）在叶子节点每一个节点上放双向指针，办理B树范围查询不好的问题
B+tree 结构实现数据索引具有如下优点：
[*]非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘VO的次数就会大大减少，数据查询的服从也会更快。
[*]所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得非常简朴。
[*]数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操纵。
4、如果一个表没有主键索引还会创建B+树吗？
答案是会的！！！
InnoDB是MySQL中的一种存储引擎，它会为每个表创建一个主键索引。如果表没有明确的主键索引，InnoDB会使用一个隐藏的、主动天生的主键（rowid）来创建索引。这个隐藏的主键索引使用的就是B+树结构。因此，在InnoDB中，即使表没有明确的主键索引，也会创建一个B+树索引。
5、hash索引
使用不多，只支持自适应hash索引，Memory引擎支持hash索引
有点雷同于HashMap底层的数据结构，存的是键值对，hash计算取模放到对应的整数的槽位上。
6、聚集（簇）索引和非聚集索引
（1）聚集索引（主键索引）
B+树，叶子结点中包罗索引和数据（索引和数据聚集到一起到以是叫聚集索引）
[*]聚簇索引将数据存储在索引树的叶子节点上。
[*]聚簇索引可以減少一次查询，因为查询索引树的同时就能获取到数据。
[*]聚簇索引的缺点是，对数据进行修改或删除操纵时必要更新索引树，会增加系统的开销。
[*]聚簇索引通常用于数据库系统中，主要用于提高查询服从。
注：InnoDB默认的数据结构上聚簇索引，MyISAM好坏聚簇索引
https://i-blog.csdnimg.cn/direct/aaa09071eb2b4af3bd4701e6584eba75.png
https://i-blog.csdnimg.cn/direct/ba872291703c48d09ddb0066599b7264.png
（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、索引的优缺点，什么时候用什么时候不用
优点：
[*]提高检索服从
[*]降低排序本钱，索引对应的字段是会有一个主动排序功能的，默认是升序asc。
它缺点是
[*]创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
[*]索引必要占用物理空间，数据量越大，占用空间越大，如今磁盘自制，还好。
[*]会降低表的增编削的服从，因为每次增编削索引，都必要进举措态维护
索引有坏有好，什么时候必要索引，什么时候不必要？
适合：
1.较频仍的作为查询条件的字段应该创建索引
不适合：
[*]字段值的唯一性太差不适合单独做索引
[*]更新非常频仍的字段不适合
[*]不会出如今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要只管遵守以下几点原则，避免留下技术债：
[*]减少数据扫描
[*]返回更少数据
[*]减少交互次数
[*]减少服务器CPU及内存开销
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]