mysql

已于 2023-08-02 11:36:14 修改
阅读量118 收藏
点赞数
文章标签： mysql 数据库 database
于 2022-06-22 09:03:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_52174905/article/details/125318247
版权

列的别名

1、在原字段后使用空格，空格后放置别名 使用，分割字段

2、使用as关键字

3、使用双引号，将别名引起来

着重号

当列名或者表名和关键字重名的时候可以使用``来解决

IFNULL

IFNULL（id，0）：如果id为null，就替换成0。NULL值参与运算结果为NULL：100+NULL=NULL

查询常量

查询表结构：

DESCRIBE finance_project;

DESC也可以。

UNION和UNION ALL：

两者都是对相同列数的数据对两个结果集进行并集操作，union会去除重复行，并使用默认规则排序；union all  不会去除重复行，不排序

HAVING

1、如果过滤条件中，使用了聚合函数，则必须使用HAVING来替换WHERE

2、HAVING必须放在GROUP BY 的后边

MyISAM和Innodb的区别

1、Inodb支持外键

2、Innodb支持事务，可以确保事务提交或者回滚，MyISAM不支持事务，崩溃后无法恢复。

3、Innodb是行锁，MyISAM是表锁

4、Innodb是为处理巨大数据量的最佳设计。

5、MyISAM使用count（*）的效率很高，因为自己额外维护了一个字段，只读或者以读为主的业务建议使用MyISAM。.myd存储数据   .myi存储索引  .frm 存储表结构

缺点就是：Innodb对于新增和查询的性能不如MyISAM，而且对内存的要求比较高，因为Innodb索引和数据存放在一起，MyISAM数据和索引不存放在一起。

聚集索引和非聚集索引

聚集索引：定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

区别

区别一：

聚集索引：就是以主键创建的索引，在叶子节点存储的是表中的数据，缺点是插入速度严重依赖插入顺序，更新主键的代价很高，二级索引访问需要两次索引查找。

非聚集索引：就是以非主键创建的索引(也叫做二级索引)，在叶子节点存储的是主键和索引列。

区别二：

聚集索引中表记录的排列顺序和索引的排列顺序一致；所以查询效率快。

非聚集索引中表记录的排列顺序和索引的排列顺序不一致。

区别三：

聚集索引每张表只能有一个，非聚集索引可以有多个。

MySQL使用InnoDb存储引擎，InnoDb将数据划分为若干个页，页的默认大小为16kb，页与页在物理地址上不相连，通过双向链表链接，每个数据页中的数据会按照主键值由小到大组成单向链表。

 根页面位置万年不动

根页面的位置是不会发生变化的，最开始一颗B+树，存储记录，当存储满了之后，会把根页面的数据复制，根页面不在存储数据，存储页记录。我们还要保证内节点目录项的记录唯一。、

Innodb本身不支持Hash索引，但是提供自适应Hash索引，如果某个数据经常被访问，当满足一定条件时，就会将这个数据也的地址放到hash表中，这样下次查询的时候，就可以直接找到这个页面的位置，时B+树具备了hash索引的优势。

覆盖索引

        覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

        我们可以看到下边这个很简单的查询语句，对name字段建立了一个普通索引，这个语句正常找到name列之后会进行回表操作，从主键索引上查询出age列的值。

EXPLAIN SELECT name,age from ceshi where name='张三';

        下边我们将age列去掉，我们发现执行计划输出的Extra中出现了Using index这样一个提示，说明使用了覆盖索引，没有进行回表操作。因为我们需要查询出的列的数据，就在name索引上。无需回表。

EXPLAIN SELECT name from ceshi where name='张三';

 索引下推

         Extra 中出现了Using index condition 表示使用了索引下推，下边这个例子建立了联合索引，（age，name），正常来讲%号放在前面查询到age等于1的记录之后就会回表，查询出所有数据进行比较，使用索引下推之后，就会将name的条件也进行筛选之后在进行回表。

EXPLAIN SELECT * from ceshi where age =1 AND name LIKE '%张';

索引失效的场景

1、最左前缀原则

在查询的过程中，联合索引遵循最左前缀原则，联合索引中，在进行数据检索时从索引的最左端开始匹配。联合索引有多个列，对于多列索引，查询过滤条件的字段，必须顺序的包含索引中的字段，一旦跳过某个字段，则索引后面的字段就会失效。如果过滤条件中没有使用联合索引中的第一个字段，则这个索引不会被使用到。

2、计算\函数会使索引失效

3、类型转换导致索引失效

4、查询条件出现<>/！=会使索引失效（不一定）

例如：age!=5 ,其实查询是（负无穷，5）与（5，到正无穷）这两个区间 ，例如有10条数据，9条都是5，这个时候只需要回表一次，是有可能会走二级索引的

5、is not null/is null 可能走索引也可能不走索引，主要看数据分布

6、模糊匹配前边是%会使索引失效

7、or的前后列不是索引列会使索引失效，or必须前后全是索引才会生效

回表

        在上边多次出现一个操作叫做回表，回表就是先通过数据库二级索引扫描出数据所在的行，再通过行主键id到主键索引上取出二级索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树.

分析表

分析关键字的分布, 分析并存储MyISAM和BDB表中键的分布：

我们可以看到现在数据的分布是5，代表着name有5个不同的值，我们更改一下数据，是表中不同的name达到6个，再次运行show index 语句发现数据没有发生变化。

 我们使用下面的语句对表进行分析：

ANALYZE TABLE ceshi（表名）;

再次show index 发现数据已经改变

检查表

CHECK table ceshi;

检查表的错误，并且为MyISAM更新键的统计内容

优化表

OPTIMIZE TABLE ceshi;

优化表主要作用是消除删除或者更新造成的空间浪费，清除空间碎片

 redo日志

 为什么需要REDO日志

        我们都知道数据库读取数据是从磁盘加载到内存中，在内存中进行读取，因为操作内存的速度要快很多，写入的时候同样是要先写入到内存中，然后刷盘到磁盘。但是如果在这个过程中发生宕机，就会造成内存和磁盘的数据不一致，或者数据丢失，这是非常可怕的。所以我们需要使用redo log 日志来记录，内存中数据发生的变化。还有一点就是刷盘也会占用性能，我们也不需要时时刻刻进行刷盘。所以redo log也提供了rodo log buffer 来在内存中先记录，然后写入到磁盘中的redo log file 中；

 REDO日志的好处、特点

1. 好处

  • redo日志降低了刷盘频率

  • redo日志占用的空间非常小

2. 特点

  • redo日志是顺序写入磁盘的

  • 事务执行过程中，redo log不断记录

 redo的组成

Redo log可以简单分为以下两个部分：

  • 重做日志的缓冲 (redo log buffer)，保存在内存中，是易失的。

参数设置：innodb_log_buffer_size：

redo log buffer 大小，默认16M，最大值是4096M，最小值为1M。

  • 重做日志文件 (redo log file)，保存在硬盘中，是持久的。

  • 参数设置：

参数描述

innodb_log_group+home_dir

指定 redo log 文件组所在的路径，默认值为./，表示在数据库的数据目录下。MySQL的默认数据目录（var/lib/mysql）下默认有两个名为ib_logfile0和ib_logfile1的文件，log buffer中的日志默认情况下就是刷新到这两个磁盘文件中。此redo日志文件位置还可以修改。

innodb_log_files_in_group

指明redo log file的个数，命名方式如：ib_logfile0，ib_logfile1... ib_logfilen。默认2个，最大100个。

innodb_flush_log_at_trx_commit

控制 redo log 刷新到磁盘的策略，默认为1。

innodb_log_file_size

单个 redo log 文件设置大小，默认值为 48M 。最大值为512G，注意最大值指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G。

 刷盘策略

        redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存（page cache）或者叫（os cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。

针对这种情况，InnoDB给出innodb_flush_log_at_trx_commit参数，该参数控制 commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：

  • 设置为0：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）

  • 设置为1：表示每次事务提交时都将进行同步，刷盘操作（默认值）

  • 设置为2：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

1.4 redo的整体流程

第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝

第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值

第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式

第4步：定期将内存中修改的数据刷新到磁盘中

Write-Ahead Log(预先日志持久化)：在持久化一个数据页之前，先将内存中相应的日志页持久化。

checkpoint

  这是真正将数据从缓冲池中写到磁盘中，也提供了几种策略：

查看系统默认的存储引擎：

SHOW VARIABLES LIKE 'default_storage_engine%'；

mysql事务的七种传播行为

什么是事务的传播行为:事务传播行为用来描述由某一个事务传播行为修饰的方法被嵌套进另一个方法的时事务如何传播。

  • PROPAGATION_REQUIRED 表示当前方法必须在一个具有事务的 上下文中运行,如有客户端有事务在进行，那么被调用端将在该事务中运行，否则的话重新开启一个事务。( 如果被调用端发生异常,那

    么调用端和被调用端事务都将回滚)

  • PROPAGATION_SUPPORTS 表示当前方法不必需要具有一个事务 上下文,但是如果有一个事务的话,它也可以在这个事务中运行

  • PROPAGATION_MANDATORY 表示当前方法必须在一个事务中运行，如果没有事务，将抛出异常

  • PROPAGATION_REQUIRES_NEW 总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起。

  • PROPAGATION_NOT_SUPPORTED 总是非事务地执行，并挂起任何存在的事务。

  • PROPAGATION_NEVER 总是非事务地执行，如果存在一个活动事务，则抛出异常

  • PROPAGATION_NESTED表示如果当前方法正有一个事务在运行中,则该方法应该运行在一个嵌套事务中 ,被嵌套的事务可以独立于被封装的事务中进行提交或者回滚。如果封装事务存在,并且外层事务抛出异常回滚，那么内层事务必须回滚,反之,内层事务并不影响外层事务。如果封装事务不存在,则同propagation. required的一样

 间隙锁

杰克.凯鲁亚克
关注
MySql 8.0.37
05-30
MySql 8.0.37
mysql8.0升级版本
12-12
MySQL 8.0 升级版本 在本文中，我们将详细介绍 MySQL 8.0 升级版本的步骤和注意事项。MySQL 是一个流行的开源关系数据库管理系统，广泛应用于各种Web 应用程序中。升级 MySQL 版本可以提高数据库性能、修复 bugs 和...
某客时间-MySQL实战45讲.zip
05-20
某客时间-MySQL实战45讲某客时间-MySQL实战45讲某客时间-MySQL实战45讲某客时间-MySQL实战45讲某客时间-MySQL实战45讲某客时间-MySQL实战45讲某客时间-MySQL实战45讲某客时间-MySQL实战45讲某客时间-MySQL实战45讲...
MySQL5.5.62
11-06
MySQL 5.5.62 是 MySQL 数据库管理系统的一个重要版本，主要针对 Windows 操作系统提供了32位和64位的安装包。MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司维护。它以其高效、稳定和...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
MySQL面试篇章——MySQL核心SQL（基本语法）
itzzan的博客
07-17 938
扩展：group by 查询慢，explain 分析发现是 Using temporary，即产生了一个临时表，因此最好给分组列添加索引。数据控制语句，用于控制不同的许可和访问级别的语句。每次的 insert 操作都会有一个数据库连接操作，是占用资源的，一次连接完成的事情肯定比多次连接完成的事情所花费的时间短。数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括。低，pageNum的偏移所花费的性能，要先偏移到对应的位置，在进行分页拿去后面的数据记录。
MySQL：SELECT 语句
Zachyy的博客
07-20 330
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 467
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 671
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
mysql中ONLY_FULL_GROUP_BY报错
最新发布
zzzzzucc的博客
07-21 133
禁用会话级别的 ONLY_FULL_GROUP_BY SET SESSION sql_mode = ‘’;– 或者禁用全局级别的 ONLY_FULL_GROUP_BY SET GLOBAL sql_mode = ‘’;在 MySQL 中，sql_mode是一个服务器变量，用于定义 SQL 语句的运行模式。是sql_mode中的一个选项，它影响 SQL 查询中GROUP BY子句的行为。
使用 Vue3、Node.js、MySQL、Electron 和 Express 实现用户登录、文章管理和截屏功能
liyananweb的博客
07-15 962
通过本文，我们详细介绍了如何使用 Vue3、Node.js、MySQL、Electron 和 Express 实现一个完整的用户登录、文章管理和截屏功能的应用。希望这篇文章能为你提供有价值的参考，帮助你更好地理解和实现前后端分离的应用开发。如果你有任何问题或建议，欢迎在评论区留言讨论。
Mysql----内置函数
m0_74979625的博客
07-18 315
Mysql--内置函数
Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
xzzteach的博客
07-21 171
【代码】Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
Mysql的insert批量插入性能问题
m0_54123999的博客
07-17 231
同时分批插入也有数据一致性风险，如果在批次中途出现失败，部分数据可能已经插入，而部分数据可能未插入，需要额外处理数据一致性；当需要往数据库插入大量数据是，一条一条插入会导致插入时间长，性能不佳，所以我们一般会进行insert批量插入，一次性插入较大批次的数据，而不是每条记录单独插入，这通常会提高性能，因为批量操作减少了上下文切换和 SQL 解析的开销。事务期间，所有涉及的表或行会被锁定（根据具体的存储引擎），可能会导致更长时间的锁定，从而影响系统的并发性能，且资源占用高，大事务可能会占用较多的数据库资源。
使用Docker 实现 MySQL 循环复制（一）
m0_60511809的博客
07-18 1026
复制是 MySQL 的一项非常重要的技术，它使服务器能够将更改从一个实例复制到另一个实例，复制技术被广泛应用在水平横向扩展、商业智能和分析、地理数据分布、高可用性与容灾等关键性场景中。在某些地区或网络环境下，直接从官方 Docker Hub 下载镜像可能会出现速度慢、不稳定甚至无法连接等问题。典型的复制拓扑是主/从属服务器的一对多关系，高级复杂复制拓扑有双向复制、循环复制以及多源复制等。本次实验将在一台虚拟机中使用 Docker 容器构建三个 MySQL 循环复制拓扑。
【Mysql关于读已提交和可重复读（Read Committed）隔离级别下解决幻读的方案】
vbgesab的博客
07-19 936
在“读已提交”隔离级别下，虽然默认的MVCC机制不能直接解决幻读问题，但可以通过上述方法来解决。每种方法都有其适用场景和优缺点，选择合适的方法需要根据具体的应用需求和系统环境来决定。在高并发系统中，通常需要综合考虑性能和一致性的需求，选择最合适的解决方案。
MySQL第七次作业
taolichao30的博客
07-16 345
触发器名称分别为product_bf_insert、product_af_update和product_af_del。执行语句部分是向operate表插入操作方法和操作时间。在product表上分别创建BEFORE INSERT、AFTER UPDATE和AFTER DELETE 3个触发器，3.删除 product_bf_insert 和 product_af_update 这两个触发器。对 product 表执行 INSERT、UPDATE 和 DELETE 操作。2.1 INSERT 操作。
mysql的索引、事务和存储引擎
Hai990218的博客
07-17 1793
索引是一个排序的列表，在列表当中存储索引的值以及索引值对应数据所在的物理行。索引值和数据是一个映射关系。
Python正则表达式中的分组
weixin_44245323的博客
07-16 373
分组的后向引用，当我们想要在正则表达式中引用前面的分组时，可以通过"\数字"的方式或者通过命名分组"(?需要注意的是，如果在匹配的过程中，需要同时用到前向肯定断言和后向肯定断言，那么必须将后向肯定断言写在正则语句的前面，前向肯定断言写在正则语句的后面，表示后向肯定模式之后，前行肯定模式之前。它是可以通过" () “来进行分组，更专业的表达就是捕获组，每个完整的” () “可以分为一组，同时，” () “中还可以嵌套” () "，即组之间还可以存在更小的组。后向肯定断言的语法:(?前向肯定断言的语法：(?
MySQL查询语句与数据库管理实践
"MySQL SQL查询语句大全集锦，涵盖了从数据库的基本操作到数据的增删改查等核心功能，适合初学者进行每日练习。同时，还包括了MySQL服务的启动与安全设置建议，有助于提升数据库的安全性。" MySQL是世界上最流行的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值