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

Mysql存储引擎

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

---

inno DB

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

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

---

MySAM

MySAM是MySQL的早期引擎

特点:

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

---

Memory

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

特点:

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

---

索引

高效获取数据的数据结构

索引概述

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

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

索引分类

1. 主键索引(Primary Key Index):

   • 基于表的主键列创建的索引
   • 主键值是唯一的,不能有重复
   • 主键索引通常也是聚集索引

2. 唯一索引(Unique Index):

   • 基于一个或多个列创建的索引
   • 索引列的值必须是唯一的,不能有重复
   • 可以是聚集索引或二级索引

3. 常规索引(Normal Index):

   • 基于一个或多个列创建的索引
   • 索引列的值可以重复
   • 通常是二级索引

4. 全文索引(Full-text Index):

   • 针对文本类型的数据创建的索引
   • 支持全文搜索,可以快速查找包含指定词语的记录
   • 通常适用于新闻、论坛等包含大量文本内容的应用

5. 聚集索引(Clustered Index):

   • 数据本身的物理存储顺序与索引顺序一致
   • 查询速度快,但修改数据时需要重新排序
   • 通常主键索引就是聚集索引

6. 二级索引(Secondary Index):

   • 数据的物理存储顺序与索引顺序不一致
   • 查询速度相对较慢,但修改数据时开销小
   • 大多数索引都是二级索引

B+Tree索引

最常见的索引类型

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

BTree

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

1. 数据组织:

   • BTree 将数据组织成有序的多叉树结构,每个节点可以包含多个键值对。
   • 每个节点的键值对都是有序的,左子树的所有键值小于该节点,右子树的所有键值大于该节点。

2. 自平衡:

   • BTree 是自平衡的数据结构,通过节点的拆分和合并,可以保证树的高度不会过高,从而提高查找效率。
   • 在插入或删除数据时,BTree 会自动调整节点,保持整棵树的平衡。

3. 多叉结构:

   • 与二叉树不同,BTree 的每个节点可以拥有多个子节点,通常称为 "m 阶" BTree。
   • 更多的子节点意味着更少的树高,提高了查找效率。

B+Tree

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

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

1. 节点结构:

   • B+Tree 的非叶子节点只包含键值和指向子节点的指针,不存储实际数据。
   • 所有的数据都存储在叶子节点中,叶子节点之间通过链表结构相连。

2. 查找过程:

   • 从根节点开始查找,沿着键值进行搜索,直到找到叶子节点。
   • 在叶子节点中进行最终的数据查找和访问。

3. 数据组织:

   • 所有的数据都存储在叶子节点中,按键值有序排列。
   • 叶子节点之间通过链表结构相连,便于顺序访问。

4. 优点:

   • 查找效率高,因为非叶子节点只需要进行键值比较,不需要访问实际数据。
   • 支持范围查询,可以通过遍历叶子节点的链表高效地完成范围查找。
   • 插入和删除操作相对简单,不需要维护非叶子节点中的数据。

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.意向锁

---

表锁

对于表锁，分为两类:

1. 表共享读锁(read lock):

   • 语法: LOCK TABLES tbl_name READ [, tbl_name2 READ]
   • 当事务获得表的读锁时,允许其他事务也获得该表的读锁,但不允许获得写锁。
   • 读锁可以防止其他事务对该表进行写操作,但不能防止其他事务读取该表数据。

2. 表独占写锁(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的记录，其他记录丢弃，查询排序的代价非常大。

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

1. 创建一个覆盖索引,其索引列包含了查询所需的所有列。
2. 在查询语句中,使用子查询的方式先查询出需要的记录的 ID。
3. 然后再根据 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';

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

聚集索引下挂的是主键

---

目录

Mysql存储引擎

inno DB

MySAM

Memory

索引

索引概述

索引分类

B+Tree索引

BTree

B+Tree

BTree 与B+Tree 对比

Hash索引

Hash

R-tree空间索引

Full-text全文索引

索引使用规范

索引遵循最左前缀原则

范围查询

字符串加引号

模糊查询

or连接的条件

数据分布影响

回表查询

锁机制

updata表锁情况

间隙锁

全局锁

表级锁

表锁

元数据锁( meta data lock,MDL)

意向锁

行级锁

SQL优化

主键优化

页分裂

页合并

order by 优化

group by 优化

limit优化

cont优化

大批量插入数据优化

分区表

复习

为什么选择B+Tree作为索引结构

sql优化问题

---

分区表

1. 分区表的定义:

   • 分区表是将一张逻辑上的大表,根据某种规则(如时间、地理位置等)划分成多个物理上独立的小表。每个小表称为一个分区。

2. 分区类型:

   • MySQL 支持多种分区类型,包括 RANGE 分区、LIST 分区、HASH 分区、KEY 分区等。

3. 分区的优点:

   • 提高查询效率: 只需扫描对应的分区,而不是全表扫描,大幅提高查询速度。
   • 改善备份和恢复: 可以单独对某个分区进行备份和恢复,效率更高。
   • 简化数据管理: 可以轻松地删除或归档旧的数据分区。
   • 提高可用性: 可以针对某个分区进行维护操作,而不影响其他分区的使用。

查询的时候

1. 利用分区字段进行查询:

   • 如果查询语句中包含分区字段的条件,数据库会自动识别并只扫描相关的分区,而不是全表扫描。
   • 例如,对于按 id 分区的表,查询 WHERE id BETWEEN 1000 AND 2000 时,数据库会只扫描 p1 分区。
   • 这种情况下,查询语句不需要做任何修改,数据库会自动优化查询计划。

2. 不利用分区字段进行查询:

   • 如果查询语句中没有包含分区字段的条件,数据库无法利用分区信息,只能进行全表扫描。
   • 例如,对于按 id 分区的表,查询 WHERE username = 'itcast' 时,数据库会扫描所有分区。
   • 这种情况下,可以考虑修改查询语句,添加分区字段的条件,以便利用分区的优势。

---

复习

为什么选择B+Tree作为索引结构

简要版:

B+Tree 叶子节点非常适合按照索引进行顺序遍历.
叶子节点之间又是通过指针连接特殊的数据结构使得查询时间复杂度为log(n).
B+Tree 的非叶子节点只存储索引信息非常节省空间.

• 由于 B+Tree 的叶子节点是通过指针连接的链表结构,非常适合范围查找操作。在叶子节点按照索引顺序遍历,可以快速找到所有满足条件的数据。
• B+Tree 的所有数据都存储在叶子节点上,叶子节点之间又是通过指针连接的, 且B+Tree 的高度随着数据量的增加而缓慢增加,即使数据量很大,也只需要 2-4 次磁盘 I/O 就可以找到目标数据。这种 log(n) 查找复杂度非常高效。
• B+Tree 的非叶子节点只存储索引信息,不存储实际数据,这大大减少了索引占用的存储空间,同时也提高了缓存命中率。

sql优化问题

一张表,有四个字段(id, username, password, status),
由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案:
select id,username,password from tb user where username ='itcast';

1. 添加索引:

   • 对 username 字段建立索引,可以大幅提高查询效率。
   • 索引创建语句如下:

     sql

     复制

     CREATE INDEX idx_user_username ON tb_user (username);
     

2. 使用覆盖索引:

   • 由于查询只需要返回 id、username 和 password 三个字段,可以利用覆盖索引来避免回表操作,进一步提高查询效率。
   • 修改索引创建语句如下:

     sql

     复制

     CREATE INDEX idx_user_username_password ON tb_user (username, password, id);
     

3. 优化 SQL 语句:

   • 可以考虑使用 EXPLAIN 命令查看查询计划,进一步优化 SQL 语句。
   • 例如,可以尝试使用 SELECT username, password FROM tb_user WHERE username = 'itcast' 来利用覆盖索引。

4. 优化数据库配置:

   • 根据实际情况,可以适当调整数据库的缓存配置,如 innodb_buffer_pool_size、query_cache_size 等,以提高查询性能。

5. 分区表:

   • 如果数据量非常大,可以考虑将表进行分区,按照 username 字段进行分区。这样可以减少扫描的数据量,提高查询效率。

6. 读写分离:

   • 如果业务场景允许,可以将读写操作分离,使用主从复制架构。这样可以将查询请求分散到从库上,减轻主库的压力,提高整体的系统吞吐量。

---

 哈，谢谢各位同志的阅读，然后呢如果觉得本文对您有所帮助的话，还给个免费的赞捏
Thanks♪(･ω･)ﾉ