MySQL存储引擎、索引、SQL优化、视图、存储过程、触发器、锁、MySQL管理

架构师成长进阶空间

已于 2025-04-26 19:02:32 修改

阅读量883

点赞数 27

文章标签： mysql sql 数据库

于 2024-07-11 21:23:24 首次发布

本文链接：https://blog.csdn.net/qq_30398499/article/details/140362242

版权

存储引擎

MySQL体系结构

⚫ 连接层

最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户

端验证它所具有的操作权限。

⚫ 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存

储引擎的功能也在这一层实现，如过程、函数等。

⚫ 引擎层

存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我

们可以根据自己的需要，来选取合适的存储引擎。

⚫ 存储层

主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎简介

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

1.在创建表时，指定存储引擎

create table 表名(
    字段1 字段1类型 [comment 字段1注释],
    ...
    字段n 字段n类型 [comment 字段n注释],
)engine=innodb [comment 表注释];Copy to clipboardErrorCopied

2.查看当前数据库支持的存储引擎

show engines;Copy to clipboardErrorCopied

存储引擎特点

⚫ InnoDB

➢ 介绍

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

➢ 特点

DML操作遵循ACID模型，支持事务；

行级锁，提高并发访问性能；

支持外键 FOREIGN KEY约束，保证数据的完整性和正确性；

➢ 文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table

⚫ MyISAM

➢ 介绍

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

➢ 特点

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

➢ 文件

xxx.sdi：存储表结构信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

⚫ Memory

➢ 介绍

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

➢ 特点

内存存放

hash索引（默认）

➢ 文件

xxx.sdi：存储表结构信息

存储引擎选择

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

➢ InnoDB : 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

➢ MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

➢ MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引概述

⚫ 介绍

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

⚫ 演示

备注：上述二叉树索引结构的只是一个示意图，并不是真实的索引结构。

⚫ 优缺点

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进

索引结构

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

⚫ 二叉树

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

红黑树：大数据量情况下，层级较深，检索速度慢。

⚫ B-Tree（多路平衡查找树）

以一颗最大度数（max-degree）为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针)：

知识小贴士: 树的度数指的是一个节点的子节点个数。

⚫ B+Tree

以一颗最大度数（max-degree）为4（4阶）的b+tree为例：

相对于B-Tree区别:

①. 所有的数据都会出现在叶子节点

②. 叶子节点形成一个单向链表

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

⚫ Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

➢ Hash索引特点

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

➢ 存储引擎支持

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

思考：为什么InnoDB存储引擎选择使用B+tree索引结构?

➢ 相对于二叉树，层级更少，搜索效率高；

➢ 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一

页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的

高度，导致性能降低；

➢ 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

➢ 如果存在主键，主键索引就是聚集索引。

➢ 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

➢ 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

思考：InnoDB主键索引的B+tree高度为多高呢?

假设:

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。

高度为2：

n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170

1171* 16 = 18736

高度为3：

1171 * 1171 * 16 = 21939856

索引语法

⚫ 创建索引

create [unique | fulltext] index index_name on table_name (index_col_name,...);Copy to clipboardErrorCopied

⚫ 查看索引

show index from table_name;Copy to clipboardErrorCopied

⚫ 删除索引

drop index index_name on table_name;Copy to clipboardErrorCopied

案例：按照下列的需求，完成索引的创建

name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
为profession、age、status创建联合索引。
为email建立合适的索引来提升查询效率。

create index idx_user_name on tb_user(name);
create unique index idx_user_phone on tb_user(name);
create index idx_user_pro_age_sta on tb_user(profession, age, status);
create index idx_email on tb_user(email);Copy to clipboardErrorCopied

SQL性能分析

⚫ SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

show global status like 'Com_______'; #7个_Copy to clipboardErrorCopied

⚫ 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

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

#开启mysql慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2s，SQL语句执行时间超过2s，就会视为慢查询，记录慢查询日志
long_query_time=2Copy to clipboardErrorCopied

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

⚫ profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持

profile操作：

select @@have_pr

最低0.47元/天解锁文章