MySql体系结构

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

从mysql5.5以后，默认的存储引擎是InnoDb

建表时指定使用的存储引擎

create table my_isam (
    id int,
    name char(4)
) engine = MyISAM;

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySOL5.5之后，InnoDB是默认的 MySQL 存储引擎。

DML操作遵循ACID模型,支持事务
行级锁，提高并发访问性能
支持外键 FOREIGN KEY约束，保证数据的完整性和正确性，

InnoDB逻辑存储结构

MyISAM

MyISAM是Mysql早期的默认存储引擎。

不支持事务, 不支持外键
支持表锁，不支持行锁
访问速度快

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

数据存储内存中
hash索引

主要区别

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB:是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致2件，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MYISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的优缺点

优点	缺点
提高数据索引的速度，降低数据库IO的成本	索引列也是需要占空间的
通过索引列对数据进行排序，降低数据库排序的成本，降低CPU的消耗	索引提高了查询效率，但是也降低了更新表的速度。如对表进行增删改操作时，效率降低。

索引结构

索引为什么不用二叉树/红黑树？

二叉树顺序插入时形成链表
二叉树/红黑树度数为2，大数据量的情况下，层级过深

MySql B+树

所有的数据都存储在叶子节点，非叶子节点只起索引数据的作用。

叶子节点之间有指向相邻节点的指针,就形成了带有顺序的B+树

Mysql Hash索引

Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，<，无法利用索引完成排序操作

查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

索引分类

聚集索引选取规则：

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

什么是回表查询？
查询数据时，查询条件不能直接走聚集索引，要先走二级索引获取记录ID，再走聚集索引获取到数据，称为回表查询。

索引的操作语法

SQL性能分析

sql执行频率

执行结果如下:

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query time，单位:秒，默认10秒)的所有SQL语句的日志。

查看慢查询日志是否开启：

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息

# 开启MySQL慢日志查询开关
slow_query_log=1 
# 设置慢日志的时间为2秒，SOL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

可以通过如下命令查看慢sql的日志文件位置。

1	show variables like 'show_%'

Profile详情

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:

1	set profiling = 1

查询profiling是否开启

1	select @@profiling

explain执行计划

各个字段的含义：

Id: select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
select_type: 表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)UNION(UNION 中的第二个或者后面的查询语句)、SUBOUERY(SELECT/WHERE之后包含了子查询)等
type: 表示连接类型，性能由好到差的连接类型为NULL、system、const、eg_ref、ref、range、index、all。
1. null不查询数据库表时才会出现，业务系统不太可能优化为null
2. system查询系统表时出现
3. const在使用了主键索引或者唯一索引时出现
4. ref使用了非唯一索引时出现
5. all的性能最差，代表全表扫描
possible_key: 显示可能应用在这张表上的索引，一个或多个。
key:实际使用的索引，如果为NULL，则没有使用索引。
Key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows:MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered:表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

索引的使用原则

最左前缀法则

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

如果跳跃了某一列，索引将部分失效。后面的字段索引失效

索引失效的案例

例如：我们有两个sql语句。

1	select * from ai_company where simpleness_name = '景旺电子'

这条sql是用来查询公司的简称为景旺电子的公司信息。

我们可以使用explain关键字来分析sql的执行情况。explain的执行结果如下：

发现并没有走联合索引的逻辑！！！

此时我们添加一个查询参数，将sql改写为：

1	select * from ai_company where simpleness_name = '景旺电子' and company_name = '江西景旺精密电路有限公司'

同样用explain进行分析：

可以看到我们此时走了联合索引的逻辑。

范围查询

在使用了联合索引的情况下，并且范围查询的字段是索引的第一个字段。

在业务允许的情况下，使用**>=或者<=**来提到**>、<**运算符。

举个栗子：

我们编写如下两个sql：

1
2
3

select * from ai_company where simpleness_name = '景旺电子' and company_name = '江西景旺精密电路有限公司' and insured_people > 200  

select * from ai_company where simpleness_name = '景旺电子' and company_name = '江西景旺精密电路有限公司' and insured_people >= 200

使用explain来分析sql:

使用>的sql语句的分析结果如图所示：

可以看到只有insured_people字段走了索引，其他两个字段仍然需要回表扫描。

使用>=的sql语句的分析结果如图所示：

可以看到索引的三个字段全部生效了。

索引列运算

不要再索引列上进行运算操作，索引将失效

可以看下面这个sql：

1	select * from ai_company where insured_people - 50 > 200

使用explain进行分析

可以看到索引没有生效

将sql优化：

1	select * from ai_company where insured_people > 150

使用explain进行分析

索引生效！

字符串类型查询必须要带引号

编写如下sql：

1	select * from ai_company_economics where data_year = 2024

查看sql执行计划：

发现索引不生效

我们将2024带上引号，再查看执行计划

1	select * from ai_company_economics where data_year = '2024'

可以看大索引生效了。

模糊查询

如果是从字符串头部开始模糊匹配，索引失效。仅仅是尾部匹配，索引不会失效。

编写sql:

1	select * from ai_company where company_name like '%公司'

查看sql的执行计划：

很明显索引失效。

更新sql:

1	select * from ai_company where company_name like '江西%'

可以看到索引成功生效了。

or连接条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

编写sql:

1	select * from ai_company where id = 1862855422431858690 or simpleness_name = '景旺电子'

其中id是主键。查看执行计划

发现连主键索引都失效了。

调整sql，让or后面的条件为有索引的字段。

1	select * from ai_company where id = 1862855422431858690 or company_name = '江西景旺精密电路有限公司'

查看执行计划：

发现索引生效

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

sql提示

是优化数据库的一个重要手段，简单来说，就是在sql语句中添加一些人为的提示来达到优化操作的目的。

use index:建议mysql使用给出的索引，至于mysql接不接受建议，还需要mysql自行判断是否使用

ignore index:忽略索引

force index: 强制使用索引

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *

知识小贴士:
usingindex condition:查找使用了索引，但是需要回表查询数据
using where;using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

使用select * 很容易出现回表查询

前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查
询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

通过前缀拿到数据id之后，还需要进行一次回表查询。

回表查询拿到数据列row，还需要匹配与查询条件是否完全一致

还需要查看前缀索引的下一个元素是否也是满足查询条件的，满足的话也需要将其数据拿到进行匹配。最后组装数据返回。

单列索引和联合索引的选择

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议选择联合索引。

索引的设计原则

其他SQL语句的优化

插入数据

insert

使用批量插入替代单条数据循环插入
手动控制事务，在所有数据插入完成后统一提交事务
主键顺序插入

load

如果一次性要插入大批量数据，使用insert的性能较低，此时可以使用mysql数据库提供的load命令插入

主键优化

页分裂：页分裂是指当向已满的B+树数据页插入新数据时，InnoDB将原页拆分为两个新页，并将部分数据迁移到新页，以维持索引的有序性和平衡性。例如，若主键为随机值（如UUID），频繁插入可能导致相邻页数据无序，触发分裂。

页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTOINCREMENT自增主键
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。

order by优化

Using filesor:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbufer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。