MySql原理及优化

---

前言

MySql索引原理、工作中怎么进行sql调优：sql规范检查、表结构索引检查。再结合案例走一波，算是给自己做个学习回顾总结吧🤣希望对你也有一些帮助。
如果有什么不足之处，欢迎大神留言指出。。。

---

一、MySql索引原理

MySql索引底层数据结构用的是B+树，相信大家都能说出来。那为什么不用B树呢？
别急，先来看下B+树是怎么实现的，知道他的原理，这样才好跟B树有个比较嘛

如图所示是 B+ Tree 的数据结构。是由一个一个的磁盘块组成的树形结构，每个磁盘块由数据项和指针组成。

⚠️所有的数据都是存放在叶子节点，非叶子节点（也称索引结点）不存放数据。

基于这个特点，再结合图可以看出，B+树每次查询数据的IO次数都很均匀。那么我们是不是就可以简单的总结出B树和B+树的区别了呢？

• B+树的数据都放在叶子结点，内部节点并没有指向关键字具体信息的指针。因此其内部节点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
• B+树IO查询的次数相当，说明它查询过程更加稳定。
• B 树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）。

二、sql调优

1.查询优化

1.1慢查询定位
查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：

SHOW VARIABLES LIKE 'slow_query_log%'

通过如下命令开启慢查询日志：

SET global slow_query_log = ON; 
SET global slow_query_log_file = 'OAK-slow.log'; 
SET global log_queries_not_using_indexes = ON; 
SET long_query_time = 10;

long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。

1.2慢查询优化
Q：如何判断是否为慢查询？
A：MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

Q：如何判断是否应用了索引？
A：SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL。

优化慢查询可以通过提高索引过滤性：
举个🌰
表：student
字段：id,name,sex,age
造数据：insert into student (name,sex,age) select name,sex,age from student;
SQL案例：select * from student where age=18 and name like ‘张%’;（全表扫 描）
优化一：
alter table student add index(name); //追加name索引
优化二：
alter table student add index(age,name); //追加age,name索引
优化三：
可以看到，index condition pushdown 优化的效果还是很不错的。再进一步优化，我们可以把名 字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。

//为user表添加first_name虚拟列，以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age);
explain select * from student where first_name=‘张’ and age=18;

原因总结：

• 全表扫描：explain分析type属性all
• 全索引扫描：explain分析type属性index
• 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
• 频繁的回表查询开销：尽量少用select *，使用覆盖索引

2.索引分析优化

1.组合索引的最左前缀问题
最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

2.字段的默认值不要为null
虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。
3.like查询
MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。
select * from user where name like ‘%o%’; //不起作用
select * from user where name like ‘o%’; //起作用
select * from user where name like ‘%o’; //不起作用

三、分库分表

拆分方式

一个表中字段过多，还有有些字段经常使用，有些字段不经常使用，或者还有text等字段信息。可以考虑使用垂直分表方案。
1.垂直拆分
按列进行垂直拆分，即把一条记录分开多个地方保存，每个子表的行数相同。把主键和一些列放到一个表，然后把主键和另外的列放到另一个表中。

优点：

• 拆分后业务清晰，拆分规则明确；

• 易于数据的维护和扩展；

• 可以使得行数据变小，一个数据块 (Block) 就能存放更多的数据，在查询时就会减少 I/O 次数；

• 可以达到最大化利用 Cache 的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起；

• 便于实现冷热分离的数据表设计模式。
  缺点：

• 主键出现冗余，需要管理冗余列；

• 会引起表连接 JOIN 操作，可以通过在业务服务器上进行 join 来减少数据库压力，提高了系统的复杂度；

• 依然存在单表数据量过大的问题；

• 事务处理复杂。

如果一张表中的记录数过多，那么会对数据库的读写性能产生较大的影响，虽然此时仍然能够正确地读写，但读写的速度已经到了业务无法忍受的地步，请考虑此方法。
2.水平拆分
将一张含有很多记录数的表水平切分，不同的记录可以分开保存，拆分成几张结构相同的表。
水平拆分重点考虑拆分规则：例如范围、时间或Hash算法等。
优点：

• 拆分规则设计好，join 操作基本可以数据库做；
• 不存在单库大数据，高并发的性能瓶颈；
• 切分的表的结构相同，应用层改造较少，只需要增加路由规则即可；
• 提高了系统的稳定性和负载能力。
  缺点：
• 拆分规则难以抽象；
• 跨库Join性能较差；
• 分片事务的一致性难以解决；
• 数据扩容的难度和维护量极大。

---

总结

个人感觉MySql的知识点还是挺多的，为了更好的将知识点串联起来，建议要先对其整体逻辑架构有个了解。
我是油条，如果不想吃生活的苦，就跟我一起吃学习的苦吧，我们下期见！