[MySQL]02 存储引擎与索引,锁机制,SQL优化

桃乃木同学

已于 2024-07-22 22:14:20 修改

阅读量189

点赞数 3

文章标签： mysql sql java

于 2024-07-21 02:45:46 首次发布

本文链接：https://blog.csdn.net/tq411810317/article/details/140581160

版权

Mysql存储引擎

可插拔式存储引擎
索引是在存储引擎底层上实现的

inno DB

MySQL默认存储引擎: inno DB
高可靠性和高性能的存储引擎

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

MySAM

MySAM是MySQL的早期引擎

特点:

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

Memory

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

特点:

内存存放
hash索引（默认)>文件

索引

高效获取数据的数据结构

索引概述

优势
提高数据检索的效率，降低数据库的IO成本
索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引分类

主键索引(Primary Key Index):
- 基于表的主键列创建的索引
- 主键值是唯一的,不能有重复
- 主键索引通常也是聚集索引
唯一索引(Unique Index):
- 基于一个或多个列创建的索引
- 索引列的值必须是唯一的,不能有重复
- 可以是聚集索引或二级索引
常规索引(Normal Index):
- 基于一个或多个列创建的索引
- 索引列的值可以重复
- 通常是二级索引
全文索引(Full-text Index):
- 针对文本类型的数据创建的索引
- 支持全文搜索,可以快速查找包含指定词语的记录
- 通常适用于新闻、论坛等包含大量文本内容的应用
聚集索引(Clustered Index):
- 数据本身的物理存储顺序与索引顺序一致
- 查询速度快,但修改数据时需要重新排序
- 通常主键索引就是聚集索引
二级索引(Secondary Index):
- 数据的物理存储顺序与索引顺序不一致
- 查询速度相对较慢,但修改数据时开销小
- 大多数索引都是二级索引

B+Tree索引

最常见的索引类型

这里补充一下BTree和B+Tree数据结构

BTree

非叶子节点只存储键值和指向子节点的指针
所有数据都存储在叶子节点中,叶子节点之间通过链表相连

数据组织:
- BTree 将数据组织成有序的多叉树结构,每个节点可以包含多个键值对。
- 每个节点的键值对都是有序的,左子树的所有键值小于该节点,右子树的所有键值大于该节点。
自平衡:
- BTree 是自平衡的数据结构,通过节点的拆分和合并,可以保证树的高度不会过高,从而提高查找效率。
- 在插入或删除数据时,BTree 会自动调整节点,保持整棵树的平衡。
多叉结构:
- 与二叉树不同,BTree 的每个节点可以拥有多个子节点,通常称为 "m 阶" BTree。
- 更多的子节点意味着更少的树高,提高了查找效率。

B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

每个节点包含多个键值和指向子节点的指针
键值有序排列,左子树小于该节点,右子树大于该节点

节点结构:
- B+Tree 的非叶子节点只包含键值和指向子节点的指针,不存储实际数据。
- 所有的数据都存储在叶子节点中,叶子节点之间通过链表结构相连。
查找过程:
- 从根节点开始查找,沿着键值进行搜索,直到找到叶子节点。
- 在叶子节点中进行最终的数据查找和访问。
数据组织:
- 所有的数据都存储在叶子节点中,按键值有序排列。
- 叶子节点之间通过链表结构相连,便于顺序访问。
优点:
- 查找效率高,因为非叶子节点只需要进行键值比较,不需要访问实际数据。
- 支持范围查询,可以通过遍历叶子节点的链表高效地完成范围查找。
- 插入和删除操作相对简单,不需要维护非叶子节点中的数据。

BTree 与B+Tree 对比

Hash索引

底层数据结构是hash表,只有精确匹配索引列的查询才有效,不支持范围查询

Hash

Hash索引特点
1.Hash索引只能用于对等比较(=，in)，不支持范围查询(betweenbetween,>, <, ...)
2.无法利用索引完成排序操作
3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

出现hash碰撞需要在链表中查找比对

R-tree空间索引

空间索引是MySAM引擎的一个特殊索引类型
主要是用于地理空间数据类型,通常使用较少

Full-text全文索引

倒立排序索引,快速匹配文档的方式.类似于Lucene,Solr,ES

索引使用规范

索引遵循最左前缀原则

最左前缀原则指的是,在使用联合索引时,MySQL 会一从最左边的索引列开始匹配查询条件。也就是说,索引列的顺序非常重要。

如果查询条件只包含索引的右边部分,MySQL 将无法使用索引,而是会进行全表扫描,效率非常低下。

范围查询

联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效

在索引列上进行函数运算会使索引列失效

字符串加引号

在查询条件中使用字符串时,一定要加上单引号，可能会存在隐式类型转换

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

使用 LIKE 进行模糊查询时,索引的使用情况会有所不同:
- 如果仅是尾部模糊匹配,如 name LIKE 'abc%'，索引仍然可以生效。
- 但如果是头部模糊匹配,如 name LIKE '%abc'，索引就会失效,需要进行全表扫描。

or连接的条件

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

数据分布影响

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

is null 和 is not null 也是一样的

回表查询

使用select * 很容易出现回表查询影响程序性能

除非有联合索引包含这个表的所有字段

锁机制

updata表锁情况

在事务中,进行字段更新的时候如果没有加索引的话,被判断/更新的字段会把整张表锁住
影响并发效率

如果其中的字段有索引,那么只会变成行级锁

间隙锁

间隙锁像只有一半的查锁,就是查的时候给他上锁
查询一个范围,后面的未赋值的数据就加锁

REPEATABLE_READ（可重复读)给查的数据加上锁,在事务结束之前都不能修改,以此来解决不可重复读问题.

REPEATABLE_READ（可重复读）+间隙锁就能解决幻读问题了.
这样不就构造了一个查锁吗,查的数据被锁了不查的空数据也被锁了.

全局锁

介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表级锁

介绍
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁，主要分为以下三类:

1.表锁
2.元数据锁( meta data lock,MDL)
3.意向锁

表锁

对于表锁，分为两类:

表共享读锁(read lock):
- 语法: LOCK TABLES tbl_name READ [, tbl_name2 READ]
- 当事务获得表的读锁时,允许其他事务也获得该表的读锁,但不允许获得写锁。
- 读锁可以防止其他事务对该表进行写操作,但不能防止其他事务读取该表数据。
表独占写锁(write lock):
- 语法: LOCK TABLES tbl_name WRITE [, tbl_name2 WRITE]
- 当事务获得表的写锁时,该事务拥有对表的独占访问权,不允许其他事务获得该表的任何锁(读锁或写锁)。
- 写锁可以防止其他事务对该表进行任何读写操作。

元数据锁( meta data lock,MDL)

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

1.意向共享锁（ IS):由语句select ... lock in share mode添加.
2.意向排他锁（IX)︰由insert、update、delete、select ... for update添加。

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

1.行锁(Record Lock)∶锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

2．间隙锁(Gap Lock)∶锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下
都支持。

3．临键锁(Next-Key Lock)︰行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

lnnoDB实现了以下两种类型的行锁:
1．共享锁(S)∶允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2．排他锁(X)∶允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

SQL优化

主键优化

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

页分裂

页分裂是指当 MySQL 在 InnoDB 存储引擎中插入新记录或更新现有记录时,由于记录大小超过了页的可用空间,就需要将页分裂成两个新的页。
页分裂的过程如下:
1. MySQL 试图在当前页插入新记录或更新现有记录。
2. 由于记录大小超过了页的可用空间,无法直接插入或更新。
3. MySQL 会将当前页分裂成两个新的页,并将记录插入到合适的页中。
4. 页分裂会增加页的数量,从而增加 InnoDB 存储引擎的磁盘占用空间。
页分裂的主要原因是记录的大小超过了页的可用空间。为了避免页分裂,可以适当调整记录的大小或者增加页的大小。

页合并

页合并是指当 MySQL 在 InnoDB 存储引擎中删除记录后,如果相邻的页剩余空间足够容纳被删除的记录,则会将这些页合并成一个新的页。
页合并的过程如下:
1. MySQL 在删除记录后,检查相邻页是否有足够的空间容纳被删除的记录。
2. 如果有足够的空间,则将这些页合并成一个新的页。
3. 页合并会减少页的数量,从而减少 InnoDB 存储引擎的磁盘占用空间。
页合并的主要目的是为了减少页的数量,从而提高数据访问效率。

order by 优化

ORDER BY 优化:
- ORDER BY 是用于对查询结果进行排序的关键字,但它会增加查询的开销。
- 优化 ORDER BY 的几个关键点:
1. 利用索引: 如果查询条件中包含了可以用于排序的索引列,MySQL 可以直接利用索引进行排序,而无需进行额外的排序操作。
2. 避免全字段排序: 如果只需要返回部分字段,可以只对这些字段进行排序,而不是对全部字段进行排序。这样可以减少排序的开销。
3. LIMIT 优化: 如果只需要返回前 N 条记录,可以使用 LIMIT N 来限制返回的记录数,减少排序的开销。
4. 利用覆盖索引: 使用覆盖索引可以避免回表操作,减少 I/O 开销。
5. 优化 ORDER BY 表达式: 如果 ORDER BY 中的表达式过于复杂,可以考虑提前计算好结果,然后直接排序。

group by 优化

GROUP BY 优化:
- GROUP BY 是用于对查询结果进行分组统计的关键字,也会增加查询的开销。
- 优化 GROUP BY 的几个关键点:
1. 利用索引: 如果查询条件中包含了可以用于分组的索引列,MySQL 可以直接利用索引进行分组,而无需进行额外的分组操作。
2. 避免全字段分组: 如果只需要返回部分字段,可以只对这些字段进行分组,而不是对全部字段进行分组。这样可以减少分组的开销。
3. LIMIT 优化: 如果只需要返回前 N 条分组结果,可以使用 LIMIT N 来限制返回的记录数,减少分组的开销。
4. 利用覆盖索引: 使用覆盖索引可以避免回表操作,减少 I/O 开销。
5. 预先计算聚合函数: 如果查询中使用了聚合函数,可以考虑先计算好结果,然后再进行分组。这样可以减少分组的开销。

limit优化

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000的记录，其他记录丢弃，查询排序的代价非常大。

优化思路:一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

创建一个覆盖索引,其索引列包含了查询所需的所有列。
在查询语句中,使用子查询的方式先查询出需要的记录的 ID。
然后再根据 ID 从原表中查询出完整的记录。

这样做的好处是:
- 索引查询速度快,不需要全表扫描。
- 只需要查询必要的记录,不会浪费资源查询无用的记录。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

cont优化

explain select count(*) from tb_user ;
MyISAM引擎把一个表的总行数存在了磁盘上，因此执行coun的时候会直接返回这个数，效率很高;InnoDB引擎就麻烦了，它执行count的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路:自己计数。

count的几种用法count（主键)
InnoDB引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。

count（字段)
没有not null约束:lnnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。

有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count ( 1)
InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

count (*)
lnnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

大批量插入数据优化

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下:

#客户端连接服务端时，加上参数--local-infile
mysql --local-infile -u root -p

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关set global 
local_infile = 1;

#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user’fields terminated by ',' lines terminated by '\n';

主键顺序插入的性能要高于乱序插入

聚集索引下挂的是主键