MySQL的事务、存储引擎、索引结构及SQL优化方向

已于 2023-02-03 13:22:37 修改
阅读量667 收藏 1
点赞数 1
文章标签： mysql 数据库 sql
于 2023-01-31 10:51:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41057525/article/details/128803364
版权

MySQL的事务、存储引擎及索引结构

1、MySQL的底层结构图

MYSQL结构图

2、MySQL的特性及事务

2.1 MySQL的四大特性

  1. 原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
  2. 一致性：事务开始前和结束后，数据库的完整性约束没有被破坏
  3. 隔离性：个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
  4. 持久化：指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响

2.2 MySQL事务的并发问题

  1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
  3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

2.3 事务的隔离级别

在这里插入图片描述
默认的隔离级别为：可重复读
查看MySQL当前的隔离级别：

select @@transaction_isolation;

设置全局的隔离级别（修改之后重新连接MySQL查看）：

set global transaction isolation level 隔离级别名称;

设置会话隔离级别：

set session transaction isolation level 隔离级别名称;

设置一次操作的级别：

set transaction isolation level 隔离级别名称;

说明：

  1. 事务隔离级别为读提交时，写数据只会锁住相应的行
  2. 事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
  3. 事务隔离级别为串行化时，读写数据都会锁住整张表
  4. 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

3、存储引擎

3.1 InnoDB（默认）

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

InnoDB 的特性主要有：

  1. DML 操作(增、删、改)遵循 ACID(事务安全表) 模型，支持事务。
  2. 行级锁，提高并发访问性能。
  3. 支持外键（FOREIGN KEY）约束，保证数据的完整性和正确性。

InnoDB的逻辑存储结构：
在这里插入图片描述

3.2 MyISAM

MyISAM不支持事务，也不支持外键，其优势是访问的速度，对事务完整性没有要求或者以SELECT，INSERT为主的应用基本上都可以使用这个引擎来创建表，每个MyISAM引擎在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是
　　　　.frm(存储定义)
　　　　.MYD(MYDdata,存储数据)
　　　　.MYI(MYIndex,存储索引)
数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度，要制定索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，也就是说不同Myisam表的索引文件和数据文件可以放置到不同的路径下，文件路径需要的是绝对路径，并且具有访问权限。
注意：MyISAM类型的表可能会损坏，原因会有很多种，损坏后的表不能访问，会提示需要修复或者访问后返回错误结果，可以使用CHECK TABLE语句来检查Myisam表的健康，并用REPAIR TABLE 语句修复一个损坏的MyISAM表。

MyISAM的特点：

  1. 不支持事务
  2. 不支持外键
  3. 访问速度快
  4. 不支持行锁，支持表锁

3.3 Memory

Memory存储引擎是在内存中来创建表，每个memory表只实际对应一个磁盘文件格式是.frm. 该引擎的表访问非常得快，因为数据是放在内存中，且默认是hash索引，其速度比使用B型树(BTREE)索引快。如果我们需要使用B型树索引，可以在创建索引时选择使用。但服务关闭，表中的数据就会丢失掉。每个基于Memory存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型。该文件只存储表的结构，而其数据文件，都是存储在内存中的，这样有利于对数据的快速的处理，提高整个表的处理效率。
Memory表的大小是受到限制的，表的大小主要取决于2个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定，max_heap_table_size的大小默认为16MB，可以按需要进行扩大。

Memory的特性：

  1. 访问速度快
  2. 支持Hash索引
  3. 支持表锁

3.4 三种存储引擎的对比

功能InnoDBMyISAMMemory
存储限制64TB256TBRAM
事务支持不支持不支持
全文索引不支持支持不支持
树索引支持支持支持
Hash索引不支持不支持支持
数据缓存支持N/A不支持
外键支持不支持不支持

4、索引结构

4.1 B-Tree

在这里插入图片描述

B-Tree的特点：

  1. B树是一种完全平衡的2-3树的结构，即一个节点可以派生2或3个节点，通过树的合并和分裂，最终达到绝对平衡性。
  2. 树的每个节点代表索引的行记录的数据。
  3. 关键字匹配后，会根据递归的二分法查询子节点。
  4. 数据区有两种存储方案：存储行记录的位置、存储行记录的索引数据。
  5. 树形结构在数据的增加、删除过程中需要进行重新调整，使其达到平衡。因此，对于使用较少的字段、或者多变的字段，一般不建议使用索引。

4.2 B+Tree（MySQL）

在这里插入图片描述
B+Tree是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+Tree的特点：

  1. 只有叶子节点上才会存储数据，根节点和子节点存储的为主键索引值
  2. 在叶子节点上形成一个单向链表，每个节点都有指针指向下一个节点

与B-Tree的区别：

  1. B+树的IO效率高于B-树。
  2. 基于索引的表扫描性能高于B-树。
  3. 排序能力强于B-树。
  4. 基于索引的查询B+树更趋于稳定。

4.3 Hash

在这里插入图片描述
哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希素引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

Hash索引的特点：

  1. 只支持等值匹配，不支持范围匹配
  2. 不支持排序操作

5、SQL的性能分析

5.1 查看SQL语句的执行频率

show global status like 'Com_______';#7个下划线

在这里插入图片描述

5.2 慢日志查询

慢查询日志记录了所有执行时间超过指定参数(long_ query_ time, 单位:秒,默认10秒)的所有SQL语句的日志，它可以记录所有执行效率很低的SQL语句，可以针对这些语句进行优化。 MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf) 中进行配置，所需命令行如下:

  1. 查看慢日志是否开启：show variables like ‘slow_query_log’;
  2. MySQL的配置文件(/etc/my.cnf) 中添加：slow_query_log=1 long_query_time=2，开启慢日志且设置长查询时间为2s；
  3. 直接使用命令 set global slow_query_log = ON; 开启慢日志。
    查看慢SQL执行：SHOW PROCESSLIST;

5.3 SQL性能分析

Show profiles是MySql用来分析当前会话SQL语句执行的资源消耗情况，可以用于SQL的调优测量。
Show profiles默认状态下是关闭的，执行**set profiling=true;**打开状态
在这里插入图片描述
可以用show profile;查看最近执行的SQL语句执行情况
在这里插入图片描述
其中Query_ID为每条执行SQL的编号，Duration为执行时间，Query为SQL语句。
使用SQL命令show profile for query 6;可以查询每个Query_ID对应的SQL语句的资源耗费情况
在这里插入图片描述
其中Status表示Query_ID为6的那条SQL语句执行过程中经历的所有状态，Duration表时每个状态期间的耗费时间，执行该SQL的总时间即为所有的Duration加起来的总和。

5.4 explain执行计划

使用方法：explain + SQL语句
在这里插入图片描述

字段解释：

  1. id：select 查询序列号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行
  2. select_type：查询数据的操作类型，其值如下：
    – simple：简单查询，不包含子查询或 union – primary:包含复杂的子查询，最外层查询标记为该值
    – subquery：在 select 或 where 包含子查询，被标记为该值
    – derived：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表
    –union：若第二个 select 出现在 union 之后，则被标记为该值。若 union 包含在 from 的子查询中，外层
    select 被标记为 derived
    – union result：从 union 表获取结果的 select
  3. table：显示该行数据是关于哪张表
  4. partitions：匹配的分区
  5. type：表的连接类型，其值，性能由高到底排列如下：
    – system：表只有一行记录，相当于系统表
    – const：通过索引一次就找到，只匹配一行数据
    – eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描
    – ref：非唯一性索引扫描，返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
    – range：只检索给定范围的行，使用一个索引来选择行。一般使用between、>、<情况
    – index：只遍历索引树
    – ALL：全表扫描，性能最差
    注：前5种情况都是理想情况的索引使用情况。通常优化至少到range级别，最好能优化到 ref
  6. possible_keys：指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能
  7. key：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询
  8. key_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好 显示的是索引字段的最大长度，并非实际使用长度
  9. ref：显示该表的索引字段关联了哪张表的哪个字段
  10. rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，并不一定准确
  11. filtered：返回结果的行数占读取行数的百分比，值越大越好
  12. extra：包含不合适在其他列中显示但十分重要的额外信息，常见的值如下：
    – using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQL
    – using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值，应该优化 SQL
    – using index：表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错
    – using where：where 子句用于限制哪一行
    – using join buffer：使用连接缓存
    – distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行
    注意：出现前 2 个值，SQL 语句必须要优化。

6、索引失效的情况

1. 联合索引，违背最左侧法则（联合索引的第一列必须有，且不能跳过索引字段）
2. 联合索引，有>、<符号时，使用>、<符号索引字段的右侧索引字段失效
3. 在索引列上进行计算操作
4. 字符串类型的字段不加引号‘’
5. 模糊查询，尾部模糊查询，索引不会失效；头部模糊查询，索引则会失效
6. 用or查询时，or所连接的所有字段都有索引时，索引不会失效，只要有一个字段没有索引，则索引失效
7. 查询结果绝大部分满足查询索引条件时，索引失效，如：表中共有100条数据，使用索引条件查询出来的结果有80条时

7、SQL优化方向

7.1 索引优化

7.1.1 指定索引

  1. 联合索引和其他索引冲突时，use index(index_name) 使用这个索引
  2. ignore index(index_name) 忽略这个索引
  3. force index(index_name) 必须使用这个索引

7.1.2 减少回表查询

  1. 尽量减少使用select * (会有回表查询)，尽量使用select index_name（减少回表查询）
  2. 如果有多个查询条件，可以将查询条件改成联合索引，而不是单列索引

7.1.3 前缀索引

针对字符串类型（varchar、text）的字段，将字符串的前缀加索引（create index index_name on table (字段名（N）)）

7.2 插入优化

  1. 一条SQL语句批量插入
  2. 手动提交事务
  3. 主键顺序插入避免页分裂
  4. 大批量插入数据，可以使用load指令

7.3 主键优化

  1. 尽量降低主键的长度，避免二级索引查询时增加磁盘IO
  2. 尽量不要使用UUID或者身份证号作为主键
  3. 不要修改主键，避免修改索引结构

7.4 order by优化

  1. 尽量使用顺序索引进行排序
  2. 多字段排序时，也遵循最左前缀法则
  3. 尽量使用覆盖索引
  4. 如果避免不了，可以增加排序缓冲区大小，sort_buffer_size（默认256K）

7.5 group by优化

  1. 多字段分组时，也遵循最左前缀法则
  2. 可以使用索引来提高效率
qq_41057525
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
超详细图解！【MySQL进阶篇】SQL优化-索引-存储引擎
2401_84103844的博客
05-04 973
Java面试核心知识点一共有30个专题，足够读者朋友们应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！Java面试核心知识点},“steps”: [[外链图片转存中…(img-aH86Y16X-1714803252350)]Java面试核心知识点一共有30个专题，足够读者朋友们应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！[外链图片转存中…(img-xE20TtlA-1714803252350)]Java面试核心知识点本文已被。
深入探索MySQL---事务，SQL优化，索引，存储引擎
szy2333的博客
09-02 188
事务 就是把多条sql语句看做一个整体来执行，是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作系列组成，有四大特性ACID（原子性，一致性，隔离性，持久性） 原子性-Atomicity：指事务所包含的所有操作要么全部成功应用到数据库，要么失败回滚不会对数据库产生任何影响 一致性-Consistency:一个事务执行之前和执行之后必须处于一致性状态(比如A,B一共有5000元，...
MySQL核心知识【索引，事务，锁，日志，集群】
_zhy_cq
10-22 634
索引，事务，锁，日志，集群核心知识
Mysql面试题，sql优化，存储引擎，数据结构，基础知识等
Promise_J_Z
10-21 2995
一、基础篇 1、相关知识 什么是MySQL？SQL是什么？ MySQL：是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品，是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。 SQL：是一种结构化查询语言，是一种数据库查询语言，用.
MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）
热门推荐
努力让自己发光，对的人才能迎着光而来
01-24 13万+
目录0 存储引擎介绍1 SQL性能分析2 常见通用的JOIN查询SQL执行加载顺序七种JOIN写法3 索引介绍3.1 索引是什么3.2 索引优劣势3.3 索引分类和建索引命令语句3.4 索引结构与检索原理3.5 哪些情况适合建索引3.6 哪些情况不适合建索引4 性能分析4.1 性能分析前提知识4.2 Explain使用简介4.3 执行计划包含的信息字段解释（重中之重）id（表的读取顺序）select_type（ 数据读取操作的操作类型）table（显示执行的表名）type（访问类型排列）possible_k
MySQL面试知识点追命连环问（二）事务、索引及SQL优化
12-14
MySQL是世界上最流行的关系型数据库管理系统之一，其在面试中经常涉及的话题包括事务处理、索引以及SQL优化。本文将深入探讨这些关键知识点。 1. **MySQL事务** - **事务** 是数据库操作的基本单位，确保数据的...
Mysql InnoDB引擎的索引与存储结构详解
12-16
在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。 而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的...
MySQL学习笔记，包括SQL基本语法、MySQL基础知识，包括存储引擎、索引、视图、锁、优化、存储结构等MySQL主要的内容
07-27
笔记可能包含对查询优化、存储优化、索引优化等方面的讨论，可能还会提及使用EXPLAIN分析查询执行计划，以及如何通过调整MySQL配置参数来优化性能。 最后，关于存储结构，笔记可能会讲解MySQL的数据文件布局，如...
MySQL数据库增删查改（基础）CRUD
chendemingxxx的博客
09-02 555
比如说：创建一张学生表，有姓名，学号。插入两个学生。可能有点草率看看例子： 顺序不是问题，后面参数和前面括号参数对等就行。比如说上面的例子：把id和name参数倒过来了，后面参数一一对应也可以。不推荐使用全列查询原因：比如说上面例子我只要id或者说只要name；当然也可以查询多个列： 比如说我们创建一个这样的表命令：第一个 效果：第三个总分效果：拓展一个现象： 拓展：语法：比较运算符：逻辑运算符：解释：%在mysql这里代表的是多个任意字符，_代表的是 一个任意字符。揭晓来才是修改硬盘里面的内容。案例：
mysql-day02
m0_64370841的博客
08-29 2230
【代码】mysql-day02。
MySQL 函数、约束、多表查询与事务详解
A_cot的博客
08-30 1648
在 MySQL 数据库中，函数、约束、多表查询和事务是非常重要的概念，它们可以帮助我们更好地管理和操作数据。本文将详细介绍这些概念，并通过代码演示来帮助你更好地理解。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 1329
MySQL——子查询（5）带比较运算符的子查询
MySQL空间函数ST_Distance_Sphere()的使用
程序人生
08-29 1256
MySQL提供了一系列的空间函数，其中ST_Distance_Sphere()函数可以计算两个经纬度坐标之间的距离，返回单位为米的浮点数。这个函数使用的是球形大地模型，因此对于较短的距离，计算结果的精度比较高。注意，在使用ST_Distance_Sphere()函数时，参数必须是经度和纬度的点对象。函数来创建表示经纬度坐标的点对象，然后将这两个点作为参数传递给。这个函数会计算这两个点之间的距离，并将结果作为。在上面的SQL中，我们将经度和纬度的值作为参数传递给。函数，然后将返回的点对象作为参数传递给。
MySQL系列—7.内存结构
最新发布
md54333的博客
09-02 496
对于聚集索引页，DML操作带来的数据磁盘读取和缓存修改往往是顺序的、集中的，这就很好 地利用了聚集索引的优点，但随着聚集索引页的顺序IO，各个二级索引页的读取和修改通常是随 机IO的。而Change buffer将分散的、未缓存的二级索引页的变化进行缓存而非直接从磁盘中读 取索引页，待到其他操作需要访问二级索引时，只需将change buffer中的变化缓存合并然后加 入缓冲池(buffer pool)就行了，这样就大大地减少了磁盘随机读取的IO。可以控制change buffer只对部分类操作生效。
MySQL5.7.36之主从复制延迟复制-centos7
龙哥·三年风水从2011年开始做IT工作，从ps画图到前端开发->后端开发->框架开发->业务架构设计及开发->业务需求整理、开发->技术经理->技术总监
08-31 619
MySQL5.7.36之主从复制延迟复制-centos7
MySQL 使用C语言链接
JK01WYX的博客
09-02 1213
mysql_get_client_info() ; msql_real_connect() ; mysql_query() ; mysql_store_result() ; mysql_num_rows() ; mysql_num_fields() ; mysql_fetch_fields() ; mysql_fetch_fields() ; mysql_close()
docker里装mysql
m0_74744788的博客
08-31 280
: 你已经将容器的 3306 端口映射到宿主机的 3306 端口，这是正确的配置，允许你从宿主机访问 MySQL 服务。-v mysql:/var/lib/mysql 挂载点。--name mysql 容器名字。dd3b2a5dcb48 镜像id。拉取mysql镜像后。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值