【MYSQL】

已于 2024-09-06 10:41:44 修改
阅读量554 收藏 20
点赞数 20
分类专栏： 数据库 文章标签： mysql 数据库
于 2024-09-06 10:38:03 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ABC1234567890ABM/article/details/141866559
版权

事务

四大特性（ACID）
  • 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性(isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
并发事务问题
  • 脏读：一个事务读到另外一个事务还没有提交的数据。
  • 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影”。
事务隔离级别

在这里插入图片描述

存储引擎：InnoDB

描述：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL5.5之后，InnoDB是默认的 MySQL 存储引擎。
特性：DML操作遵循ACID模型，支持事务;行级锁，提高并发访问性能;支持 外键 FOREIGN KEY约束，保证数据的完整性和正确性;

索引

结构

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

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

分类

在这里插入图片描述
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
在这里插入图片描述
聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
SQL性能

EXPLAIN 执行计划：EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

在这里插入图片描述
EXPLAIN 执行计划各字段含义：

  • id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
  • select type：表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等。
  • type：表示连接类型，性能由好到差的连接类型为NULL、system、cnst、eq_ref、ref、range、index、al。
  • possible_key：显示可能应用在这张表上的索引，一个或多个。
  • key：实际使用的索引，如果为NULL，则没有使用索引。
  • key len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
  • rows：MVSOL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
  • filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

索引失效情况
  • 联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效。
  • 在索引列上进行运算操作，索引将失效。
  • 字符串类型字段使用时，不加引号，索引将失效。
  • 模糊查询中，如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
  • 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select*，避免回表。

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

前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘!0，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

在这里插入图片描述

  • 前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高,唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
    在这里插入图片描述

在这里插入图片描述

单列索引与联合索引
  • 单列索引:即一个索引只包含单个列。
  • 联合索引:即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况：根据下图可以看到，查询条件中有phone和name，他们都有单列索引，但是执行explain后发现真正只走了phone的索引，因为phone索引中没有name值，故还需回表走一次聚簇索引。
在这里插入图片描述
多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

联合索引情况：
在这里插入图片描述

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

在这里插入图片描述

索引设计原则
  • 针对于数据量较大，且查询比较频繁的表建立索引。
  • 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  • 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  • 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  • 如果索引列不能存储NULL值，请在创建表时使用NOT
    NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

insert优化

  • 批量插入
    在这里插入图片描述

  • 手动提交事务
    在这里插入图片描述

  • 主键顺序插入
    在这里插入图片描述

  • 大批量插入数据：如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MVSQL数据库提供的load指令进行插入。操作如下:
    在这里插入图片描述

order by优化

①.Using fileson:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort bufer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
②.Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为 usina index，不需要额外排序，操作效率高。
在这里插入图片描述
在这里插入图片描述

group by优化

①.在分组操作时，可以通过索引来提高效率。
②.分组操作时，索引的使用也是满足最左前缀法则的。
在这里插入图片描述

limit优化

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

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

count优化
  • count(主键)：InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)。
  • count(字段)：没有notnul约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为nul，计数累加有not null约束:InnoD8 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
  • count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
  • count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、I/0)的争用以外，数据也是-种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

分类

MySQL中的锁，按照锁的粒度分，分为以下三类:

  • 全局锁:锁定数据库中的所有表。

  • 表级锁:每次操作锁住整张表。

  • 行级锁:每次操作锁住对应的行数据。

全局锁

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

在这里插入图片描述

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类:

  • 表锁：分为表共享读锁和表独占写锁
    在这里插入图片描述

  • 元数据锁(meta datalock，MDL)：

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。
在这里插入图片描述

  • 意向锁：

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

在这里插入图片描述

  • 意向共享锁(IS):由语句 select… lock in share mode添加。
  • 意向排他锁(IX):由insert、update、delete、select … for update 添加。
    在这里插入图片描述
行级锁

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

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

  • 行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
    在这里插入图片描述
    在这里插入图片描述
    默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。
    1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
    2.InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么!nnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

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

  • 临键锁(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
    默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
    1.索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。
    2.索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
    3.索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

乖怪查理
关注
  • 20
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL安装配置教程（超级详细、保姆级）
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
mysql jdbc连接驱动jar包（适用于mysql5.7版本，亲测可用）
08-17
MySQL JDBC连接驱动是Java应用程序与MySQL数据库之间通信的桥梁，使得开发者可以使用Java语言来执行SQL语句，处理数据库中的数据。本驱动适用于MySQL 5.7版本，并且经过实际测试，确认可用。这个jar包是完全免费的，...
2021MySql-8.0.26安装详细教程（保姆级）
热门推荐
铁锈侠的博客
07-30 73万+
MySql-8.0.26安装详细教程保姆级下载安装包安装配置配置环境变量 下载安装包 下载安装包： 下载网址： https://dev.mysql.com/downloads/ 选择这个 进入后选择 直接下载第一个 点击这里，开始下载 安装配置 解压安装包 我这里解压到d盘 打开 编写MySQL配置文件 在解压目录下新建my.ini文件 将下面文本拷贝进my,ini文件中 [mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 ----------是你的文件路径-
MySql 安装与使用（非常详细）
余识-
05-11 9万+
文章目录前言一、安装MySql二、认识MySql数据库软件的文件结构三、MySql Shell的使用四、建立对MySql数据库的认知五、常见MySql指令1、增2、删3、改4、查六、指令实战七、VS连接MySql 前言 所谓数据库，不过是一个别人写好的存储数据的软件，并向我们这些使用者提供对数据的操作接口，如提供与语言无关性的命令语句 而对数据我们一般需要做哪些操作呢？不过增、删、改、查而已 所以不要将数据库想的太难了 一、安装MySql 点击这里前往官网下载 下载完成后直接双点击安装 等待一段时间后
Linux 安装Mysql 详细教程（图文教程）
bai_shuang的博客
02-15 25万+
Linux 阿里云 安装Mysql 详细教程,XShell免费版(解决官网打不开的问题)
安装mysql
weixin_46371752的博客
05-10 3460
一、到官网下载mysql 地址：https://dev.mysql.com/downloads/mysql/ 二、将mysql压缩包采访到要安装的文件夹中【我要安装到 D:\softwareAPP中】 三、在D:\softwareAPP\mysql-8.0.28-winx64 路径下新建data文件夹和my.ini文件 四、打开my.ini文件，进行如下配置 ，【下面是路径的，都改成你自己的路径】 [mysqld] # 设置3306端口 port=3306 # 设置mysql..
Ubuntu deb文件 安装 MySQL
桃李不言下自成蹊
11-15 7万+
sudo apt -y install vim net-tools wget gcc make cmake lrzsz sudo apt -y install libmecab2 libjson-perl
mysql-connector-java-8.0.27
12-24
MySQL Connector/J 8.0.27 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）接口的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql驱动jar 文件适用MySQL5.7
01-18
MySQL驱动jar文件是Java应用程序与MySQL数据库之间通信的关键组件，主要功能是提供Java Database Connectivity (JDBC) API，使得Java开发者能够通过编写Java代码来执行SQL语句，从而操作MySQL数据库。标题提到的...
mysql驱动（适用于mysql5.7版本）
12-07
MySQL驱动是Java应用程序连接到MySQL数据库的关键组件，它允许程序通过Java Database Connectivity (JDBC) API与MySQL服务器进行通信。本压缩包包含了两个适用于MySQL 5.7版本的驱动文件：`mysql-connector-java-...
MySQL数据库增删查改（基础）CRUD
chendemingxxx的博客
09-02 1219
比如说：创建一张学生表，有姓名，学号。插入两个学生。可能有点草率看看例子： 顺序不是问题，后面参数和前面括号参数对等就行。比如说上面的例子：把id和name参数倒过来了，后面参数一一对应也可以。不推荐使用全列查询原因：比如说上面例子我只要id或者说只要name；当然也可以查询多个列： 比如说我们创建一个这样的表命令：第一个 效果：第三个总分效果：拓展一个现象： 拓展：语法：比较运算符：逻辑运算符：解释：%在mysql这里代表的是多个任意字符，_代表的是 一个任意字符。揭晓来才是修改硬盘里面的内容。案例：
MySQL递归获取商品分类以及所有下级分类商品
cl617287的博客
09-02 343
MySQL通过递归获取所有下级分类数据
mysql用时间戳还是时间存储比较好
qq_42572322的博客
09-05 371
TIMESTAMP 通常占用的空间比 DATETIME 少，如果你对存储空间有限制，或者你需要存储大量的时间戳数据，TIMESTAMP 可能是更好的选择。- 如果你的应用需要在不同的时区中使用时间数据，或者需要自动处理夏令时的变化，使用 TIMESTAMP 可能更方便。- 考虑到不同的数据库系统和应用程序可能对时间类型的支持不同，选择一个能够满足你当前和未来需求的时间类型是很重要的。- 对于需要频繁进行日期和时间范围查询的场景，DATETIME 类型可能更适合，因为它允许更灵活的日期和时间计算。
MySQL表操作及约束
最新发布
2301_77479435的博客
09-06 677
MySQL表操作及约束
如何进行不同数据库的集群操作？--从部署谈起，今天来看MySQL和NoSql数据库Redis的集群
yizhhouyyds的博客
09-06 608
详细介绍了MYSQL和Redis的集群技术，原理理解以及详细的实验步骤。
MySQL Workbench 的入门指南
m0_67187271的博客
09-06 641
MySQL Workbench 是一个官方的图形化工具，用于开发、管理和设计 MySQL 数据库服务器。它提供了丰富的功能，可以帮助数据库管理员、开发者以及DBA们高效地工作。下面是一个MySQL Workbench的入门指南，介绍如何安装和使用它。
数据库课程设计mysql
数字人生
09-06 1461
以上是一个简单的数据库课程设计示例，涵盖了数据库设计、表结构、SQL查询和一些基本的操作。你可以根据自己的需求扩展这个示例，例如添加更多的实体和关系，增加更多的查询和操作等。希望这个示例能帮助你更好地理解和掌握数据库设计和MySQL的使用。数据库课程设计是一个重要的实践环节，它可以帮助你更好地理解数据库系统的设计和应用。以MySQL为例，下面是一个可能的数据库课程设计的基本步骤和要点。### 1. 选题首先，你需要确定一个具体的设计题目。这个题目应该具有一定的实际应用背景。
MySQL系列—7.内存结构
md54333的博客
09-02 708
对于聚集索引页，DML操作带来的数据磁盘读取和缓存修改往往是顺序的、集中的，这就很好 地利用了聚集索引的优点，但随着聚集索引页的顺序IO，各个二级索引页的读取和修改通常是随 机IO的。而Change buffer将分散的、未缓存的二级索引页的变化进行缓存而非直接从磁盘中读 取索引页，待到其他操作需要访问二级索引时，只需将change buffer中的变化缓存合并然后加 入缓冲池(buffer pool)就行了，这样就大大地减少了磁盘随机读取的IO。可以控制change buffer只对部分类操作生效。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值