MySQL架构和性能优化

文章目录

  • 一、MySQL架构
    • 架构图
    • 存储引擎
    • MyISAM引擎特点
    • InnoDB引擎特点
    • 管理存储引擎
  • 二、性能优化
    • 索引
    • 索引管理
    • EXPLAIN 工具
    • 使用profile工具 监控

一、MySQL架构

架构图

在这里插入图片描述
在这里插入图片描述

存储引擎

MySQL提供了多种存储引擎供用户选择，每种存储引擎都有自己的特点和使用场景。
InnoDB:

  • 默认存储引擎,支持事务、行级锁、外键等特性。
  • 适合处理大量数据的应用程序,提供高并发性和数据完整性。

MyISAM:

  • 早期版本的默认存储引擎,不支持事务和行级锁。
  • 适合处理大量只读数据的应用程序,查询速度快。

Memory(HEAP):

  • 数据存储在内存中,访问速度快,但是数据不会持久化。
  • 适合储存临时数据或者缓存数据。

Archive:

  • 专门设计用于存储和检索大量历史数据。
  • 数据压缩率高,但是不支持索引和事务。

CSV:

  • 将数据以 CSV 格式存储在文件系统中。
  • 适合与其他应用程序交换数据。

NDB (Cluster):

  • 用于 MySQL 集群环境,提供高可用性和线性扩展性。
  • 适合需要高可用性和可伸缩性的应用程序。

在选择存储引擎时,需要根据具体的应用需求,考虑性能、可靠性、可扩展性等因素,选择最合适的存储引擎。一般情况下,InnoDB 是首选,因为它提供了事务支持、外键等特性,并且性能也很优秀。

在这里插入图片描述
MyISAM和InnoDB的区别

  • storage limits（存储上限）： myisam （256t ）innodb（64t） 但是mysql 根本达不到这么大的数据量
  • transactions（事务）： myisam 不支持 innodb 支持
  • locking granularity（锁级别）： myiasam 表级 innodb 行级
  • 提高mvcc（多版本的并发控制） ： 提高数据库的并发性
  • data caches （数据缓存）： myisam 不支持 innodb 支持

MVCC 是 Multiversion Concurrency Control 的缩写,即多版本并发控制。它是 InnoDB 存储引擎实现并发控制和事务隔离级别的一种机制。

MVCC 的工作原理如下:

  1. 每行数据都会保存两个隐藏的列,分别记录了行的创建时间和行的过期时间(或删除时间)。
  2. 在读取数据时,MVCC 会根据事务的隔离级别,选择对应版本的数据进行返回。
  3. 在写入数据时,MVCC 会创建一个新版本的行,并更新行的过期时间。
  4. 通过维护多个版本的数据,MVCC 可以做到在不加锁的情况下实现非阻塞的并发控制。

MVCC 主要优点如下:

  1. 读不阻塞写,写不阻塞读,大幅提高了并发性能。
  2. 实现了可重复读的隔离级别,避免了幻读问题。
  3. 无需对普通的 SELECT 语句加锁,简化了应用开发。

MyISAM引擎特点

  • 不支持事务
  • 表级锁定
  • 读写相互阻塞，写入不能读，读时不能写
  • 只缓存索引
  • 不支持外键约束
  • 不支持聚簇索引
  • 读取数据较快，占用资源较少
  • 不支持MVCC（多版本并发控制机制）高并发
  • 崩溃恢复性较差
  • MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

  • 只读（或者写较少）
  • 表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

  • tbl_name.frm 表格式定义
  • tbl_name.MYD 数据文件
  • tbl_name.MYI 索引文件

InnoDB引擎特点

  • 行级锁
  • 支持事务，适合处理大量短期事务
  • 读写阻塞与事务隔离级别相关
  • 可缓存数据和索引
  • 支持聚簇索引
  • 崩溃恢复性更好
  • 支持MVCC高并发
  • 从MySQL5.5后支持全文索引
  • 从MySQL5.5.5开始为默认的数据库引擎
    在这里插入图片描述

管理存储引擎

show engines;
查看mysql支持的存储引擎

在这里插入图片描述

查看默认的存储引擎

show variables like '%storage_engine%';

在这里插入图片描述
修改默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine = InnoDB

查看库中所有表使用的存储引擎

show table status from   ;

在这里插入图片描述
查看库中指定表的存储引擎

show table status like  'tb_name';
show create table tb_name;

设置表的存储引擎：

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

二、性能优化

索引

索引介绍

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现。

索引的概念

  1. 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。
  2. 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
  3. 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
  4. 索引是表中一列或者若干列值排序的方法。
  5. 建立索引的目的是加快对表中记录的查找或排序。

索引的作用优点

  1. 加快查询速度，提高数据库性能。
  2. 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
  3. 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。避免排序和使用临时表。
  4. 可以降低数据库的IO成本（减少io次数），并且索引还可以降低数据库的排序成本。将随机I/O转为顺序I/O。
  5. 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
  6. 可以加快表与表之间的连接。
  7. 在使用分组和排序时，可大大减少分组和排序的时间。
  8. 建立索引在搜索和恢复数据库中的数据时能显著提高性能。

缺点：

占用额外的磁盘空间，影响插入速度。

创建索引的原则依据
索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

  1. 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。
  2. 记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。
  3. 经常与其他表进行连接的表，在连接字段上应该建立索引。
  4. 唯一性太差的字段不适合建立索引。
  5. 更新太频繁地字段不适合创建索引。
  6. 经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
  7. 在经常进行 GROUP BY、ORDER BY 的字段上建立索引；
  8. 索引应该建在选择性高的字段上。
  9. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

索引优化

  • 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）。
  • 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度。
  • 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引。
  • 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧。
  • 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引。
  • 对于经常在where子句使用的列，最好设置索引。
  • 对于有多个列where或者order by子句，应该建立复合索引
  • 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引。
  • 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
  • 不要使用RLIKE正则表达式会导致索引失效
  • 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;
  • 大部分情况连接效率远大于子查询。
  • 在有大量记录的表分页时使用limit。
  • 对于经常使用的查询，可以开启查询缓存。
  • 多使用explain和profile分析查询语句。
  • 查看慢查询日志，找出执行时间长的sql语句优化。

索引类型：

  • B+ TREE、HASH、R TREE、FULL TEXT
  • 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
  • 主键索引、二级（辅助）索引
  • 稠密索引、稀疏索引：是否索引了每一个数据项
  • 简单索引、组合索引: 是否是多个字段的索引
  • 左前缀索引：取前面的字符做索引
  • 覆盖索引：从索引中即可取出要查询的数据，性能高

索引结构

参考链接 : https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树

链接: https://www.cs.usfca.edu/~galles/visualization/BST.html

在这里插入图片描述
红黑树

参考链接:https://www.cs.usfca.edu/~galles/visualization/RedBlack.html

在这里插入图片描述
B-Tree 索引

参考链接: https://www.cs.usfca.edu/~galles/visualization/BTree.html

在这里插入图片描述
缺点：

1.连续范围查找都要从头开始，效率不稳定，快的很快，慢的就比较慢。

2.所存数据量越大，查找次数越多。既要存索引也要存数据。

B+Tree索引

参考链接: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

在这里插入图片描述

B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据。

索引管理

查看索引

show index from 表名;
show keys from 表名;

在这里插入图片描述
建立索引
格式

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
create index idx_name on students(name(5));

在这里插入图片描述
再查看就多了条索引。

explain select * from students where name='xu xian';
可以看到已经使用了索引。

在这里插入图片描述
删除索引

DROP INDEX 索引名 ON 表名;

EXPLAIN 工具

通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询。

参考资料： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

语法：

EXPLAIN SELECT clause
explain select * from students where name='xu xian';

在这里插入图片描述

列名说明
id执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type简单查询：SIMPLE|复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询）
table访问引用哪个表（引用某个查询，如“derived3”）
type关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys查询可能会用到的索引
key显示mysql决定采用哪个索引来优化查询
key_len显示mysql在索引里使用的字节数
ref当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值
Extra额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。

类型说明
All最坏的情况,全表扫描
index和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）
const当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system这是const连接类型的一种特例，表仅有一行满足条件。
Null意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

使用profile工具 监控

set profiling = ON;
打开后，会显示语句执行详细的过程
show profiles ;
查看语句,注意结果中的query_id值
show profile for query 1;
"show profile" 表示要查看或显示一个查询的性能分析信息。
"for query 1" 表示要查看的是第 1 个查询的性能分析信息。

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/763279.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

算法刷题之路之链表初探（二）Leecode21合并两个有序链表

算法刷题之路之链表初探（二）Leecode21合并两个有序链表

算法刷题之路之链表初探&#xff08;二&#xff09; 今天来学习的算法题是leecode141环形链表&#xff0c;是一道简单的入门题&#xff0c;话不多说&#xff01;直接上&#xff01; 条件&#xff08;Leecode21&#xff09; 重点&#xff01;&#xff01;&#xff01; 我直接把…
阅读更多...
primetime中cell和net的OCV

primetime中cell和net的OCV

文章目录 前言一、Cell OCV1. POCV coefficient file2. POCV Slew-Load Table in Liberty Variation Format&#xff08;LVF lib&#xff09; 二、Net OCV三、如何check OCV是否已加上&#xff1f;总结 前言 在生产中&#xff0c;外界环境的各种变化&#xff0c;比如PVT&#…
阅读更多...
代码随想录第40天|动态规划

代码随想录第40天|动态规划

完全背包 完全背包物品可以无限使用 01背包核心代码 01背包中的二维dp数组的两个for遍历可颠倒, 而一维dp数组的一定先遍历物品再遍历背包容量状态转移方程(背包容量一定为递减) 完全背包核心代码 (只在完全背包中一维dp数组嵌套顺序可颠倒, 实际题目需要确定遍历顺序) 状…
阅读更多...
云计算与生成式AI的技术盛宴！亚马逊云科技深圳 Community Day 社区活动流程抢先知道！

云计算与生成式AI的技术盛宴！亚马逊云科技深圳 Community Day 社区活动流程抢先知道！

小李哥最近要给大家分享7月7日在深圳的即将举办的亚马逊云科技生成式AI社区活动Community Day &#xff0c;干货很多内容非常硬核&#xff0c;不仅有技术分享学习前沿AI技术&#xff0c;大家在现场还可以动手实践沉浸式体验大模型&#xff0c;另外参与现场活动还可以领取诸多精…
阅读更多...
API-本地存储

API-本地存储

学习目标&#xff1a; 掌握本地存储 学习内容&#xff1a; 本地存储介绍本地存储分类存储复杂数据类型 本地存储介绍&#xff1a; 以前我们页面写的数据一刷新页面就没有了&#xff0c;是不是? 随着互联网的快速发展&#xff0c;基于网页的应用越来越普遍&#xff0c;同时也…
阅读更多...
中医药文化传承进校园活动授牌仪式在石家庄主办举办

中医药文化传承进校园活动授牌仪式在石家庄主办举办

青春闪“药”&#xff0c;我心向党。2024年6月30日&#xff0c;由河北省药品医疗器械检验研究院主办的”中医药文化传承进校园活动在石家庄主办。来自河北省各地24所学校作为示范学校现场接牌。 河北省科协科普部部长范玉鑫、河北省教育厅学位管理与研究生处副处长耿立艳、河北…
阅读更多...
Springboot项目实训--day1

Springboot项目实训--day1

目录 一、软件安装 二、软件的简单了解 三、基础知识应用 1、四个常用注释 2、尝试新建类 3、控制反转&#xff08;IOC容器&#xff09; 4、返回数据给浏览器 5、浏览器传回数据给服务器 易错点 一、软件安装 需要安装的软件是idea专业版&#xff0c;刚使用的时候可以使…
阅读更多...
mac｜浏览器链接不上服务器但可以登微信

mac｜浏览器链接不上服务器但可以登微信

千万千万千万不要没有关梯子直接关机&#xff0c;不然就会这样子呜呜呜 设置-网络&#xff0c;点击三个点--选择--位置--编辑位置&#xff08;默认是自动&#xff09; 新增一个&#xff0c;然后选中点击完成 这样就可以正常上网了
阅读更多...
Python 异常

Python 异常

文章目录 捕获异常捕获常规异常捕获指定异常捕获多个异常 else语法finally语法异常的传递 捕获异常 假设某处可能会出现异常&#xff0c;提前做好准备。 捕获常规异常 所有的异常都会被捕获&#xff0c;不指定异常。 语法&#xff1a; try:可能出错的代码 except:出现异常后…
阅读更多...
Open3D 点云快速全局配准FGR算法（粗配准）

Open3D 点云快速全局配准FGR算法（粗配准）

目录 一、概述 1.1原理和步骤 1.2关键技术和优势 1.3应用场景 二、代码实现 2.1 关键代码 2.1.1.函数&#xff1a;execute_fast_global_registration 2.1.2调用registration_fgr_based_on_feature_matching函数 2.2完整代码 三、实现效果 3.1原始点云 3.2粗配准后点…
阅读更多...
写代码，为什么还需要作图？

写代码，为什么还需要作图？

引言 古人云 &#xff1a;一图胜千言&#xff0c;闲人说&#xff1a;无图无真相。 在日常的聊天工具当中&#xff0c;无论是使用微信&#xff0c;还是钉钉。使用图片或表情包的频次越来越高&#xff0c;那是为什么呢&#xff1f;其实在互联网没有那么发达的时候&#xff0c;我…
阅读更多...
算法题笔记

算法题笔记

主要记录python的力扣题解 参考的优质网站&#xff1a; 算法通关手册&#xff08;LeetCode&#xff09; | 算法通关手册&#xff08;LeetCode&#xff09; (itcharge.cn) 代码随想录 (programmercarl.com) 2024.6.28 题目&#xff1a;轮转数组 官网连接&#xff1a;189. …
阅读更多...
Linux环境安装配置nginx服务流程

Linux环境安装配置nginx服务流程

Linux环境的Centos、麒麟、统信操作系统安装配置nginx服务流程操作&#xff1a; 1、官网下载 下载地址 或者通过命令下载 wget http://nginx.org/download/nginx-1.20.2.tar.gz 2、上传到指定的服务器并解压 tar -zxvf nginx-1.20.1.tar.gzcd nginx-1.20.1 3、编译并安装到…
阅读更多...
武汉星起航：跨境电商流量红利爆发，2023年出海企业迎突破增长

武汉星起航：跨境电商流量红利爆发，2023年出海企业迎突破增长

在数字时代的浪潮中&#xff0c;中国跨境电商以惊人的爆发力崭露头角&#xff0c;成为全球贸易的璀璨新星。2023年数据显示&#xff0c;跨境电商出口额高达1.83万亿元&#xff0c;同比增长19.6%&#xff0c;这一显著增速不仅刷新纪录&#xff0c;更为众多出海企业带来了前所未有…
阅读更多...
vscode搭建suricata调试环境

vscode搭建suricata调试环境

一、环境 windows10 wsl2 $ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 20.04.2 LTS Release: 20.04 Codename: focal二、编译 2.1 下载源码 wget https://www.openinfosecfoundation.org/download/suri…
阅读更多...
配电智能网关赋能电力系统智能化运行维护

配电智能网关赋能电力系统智能化运行维护

随着智能电网和物联网技术的不断发展&#xff0c;两者之间的融合应用成为电力行业的重要趋势。配电智能网关作为连接两者的关键设备&#xff0c;在智能电网的物联网应用中发挥着重要作用。 配电智能网关能够实现对电力系统的实时监控、数据采集、远程控制等功能&#xff0c;为…
阅读更多...
【Vue】微信禁止打开，可弹出提示：请用360、搜狗浏览器的极速模式打开。

【Vue】微信禁止打开，可弹出提示：请用360、搜狗浏览器的极速模式打开。

需求 某网站链接&#xff0c;使用微信端打开&#xff0c;某些材料自动下载会造成泄密。所以添加限制&#xff1a;微信禁止打开&#xff0c;可弹出提示&#xff1a;请用360、搜狗浏览器的极速模式打开。 处理前 微信访问该链接&#xff0c;点击【继续访问】可直接跳转到该网站 处…
阅读更多...
苍穹外卖项目 常用注解 + 动态sql

苍穹外卖项目 常用注解 + 动态sql

常用注解 常见的注解解析方法有两种&#xff1a; 编译期直接扫描&#xff1a;编译器在编译 Java 代码的时候扫描对应的注解并处理&#xff0c;比如某个方法使用Override 注解&#xff0c;编译器在编译的时候就会检测当前的方法是否重写了父类对应的方法。运行期通过反射处理&…
阅读更多...
ROS2使用C++开发动作通信

ROS2使用C++开发动作通信

1.开发接口节点 cd chapt4_ws/ ros2 pkg create robot_control_interfaces --build-type ament_cmake --destination-directory src --maintainer-name "joe" --maintainer-email "1027038527qq.com" mkdir -p src/robot_control_interfaces/action touch…
阅读更多...
C#中的时间数据格式化详解与应用示例

C#中的时间数据格式化详解与应用示例

文章目录 1、基本概念基本格式化方法 2、实用的时间格式化方法格式化日期格式化时间格式化时间戳解析日期时间字符串 3、实际应用4、应用示例结论 在软件开发中&#xff0c;时间数据是无处不在的。无论是用户登录时间、数据备份时间&#xff0c;还是日志记录&#xff0c;都需要…
阅读更多...
最新文章

Copyright @ 2022~2023