MySQL高级
文章平均质量分 91
MySQL架构、原理与调优那些事儿
流烟默
这个作者很懒,什么都没留下…
展开
-
MySQL8.0新特性之公用表表达式学习
公用表表达式(或通用表表达式)简称为CTE(Common Table Expressions)。CTE是一个命名的临时结果集,作用范围是当前语句。CTE可以理解成一个可以复用的子查询,当然跟子查询还是有点区别的,CTE可以引用其他CTE,但子查询不能引用其他子查询。所以,可以考虑代替子查询。依据语法结构和执行方式的不同,公用表表达式分为普通公用表表达式和递归公用表表达式 2 种。# 【1】普通公用表表达式普通公用表表达式的语法结构是:WITH CTE名称AS (子查询)SELECT|D原创 2016-07-30 09:44:28 · 2503 阅读 · 1 评论 -
MySQL8.0新特性之窗口函数学习
MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组,不同的是,分组操作会把分组的结果聚合成一条记录,而窗口函数是将结果置于每一条数据记录中。窗口函数可以分为静态窗口函数和动态窗口函数。* 静态窗口函数的窗口大小是固定的,不会因为记录的不同而不同;* 动态窗口函数的窗口大小会随着记录的不同而变化。MySQL官方网站窗口函数的网址为[https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions原创 2016-11-15 10:44:15 · 828 阅读 · 1 评论 -
认真研究MySQL的主从复制
# 【1】主从复制概述## ① 如何提升数据库并发能力在实际工作中,我们常常将Redis作为缓存与MySQL配合使用,当有请求的时候,首先会从缓存中进行查找。如果存在就直接取出,如果不存在再访问数据库。这样就提升了读取的效率,也减少了对后端数据库的访问压力。Redis的缓存结构是高并发架构中非常重要的一环。![在这里插入图片描述](https://img-blog.csdnimg.cn/49baf4ea895449a8bdea2af92d825467.png)此外,一般应用对数据库而言都是“读多写原创 2023-01-10 17:25:07 · 1076 阅读 · 1 评论 -
认真学习MySQL中的二进制日志(binlog)与中继日志(Relay log)
binlog即binary log,二进制日志文件,也叫作变更日志(update log)。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句,但是不包含没有修改任何数据的语句(如数据查询语句select、show等)。它以事件形式记录并保存在二进制文件中。通过这些信息我们可以再现数据更新操作的全过程。>如果想要记录所有语句(例如,为了识别有问题的查询),需要使用通用查询日志。**binlog主要应用场景:*** 一是用于数据恢复,如果MySQL数据库意外停止,可以通过二进制日志文件来原创 2023-01-07 15:43:35 · 3378 阅读 · 0 评论 -
认真学习MySQL中的MVCC机制
什么是MVCC?MVCC(Multiversion Concurrency Control),多版本并发控制。顾名思义,MVCC是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性操作有了保证。换言之,就是为了查询一些正在被另一个事务更新的行,并且可以看到它们被更新之前的值,这样在做查询的时候就不用等待另一个事务释放锁。MVCC没有正式的标准,在不同的DBMS中MVCC的实现方式可能是不同的,也不是普遍使用的。本文学习InnoDB中MVCC的实现机制(M原创 2022-12-28 16:47:19 · 773 阅读 · 0 评论 -
认真学习MySQL中锁机制(二)
接上文[认真学习MySQL中锁机制(一)](https://blog.csdn.net/J080624/article/details/78656428)我们继续学习MySQL中的锁机制。# 【5】按加锁的方式划分:显示锁、隐式锁## ① 隐式锁一个事务在执行insert操作时,如果即将插入的间隙已经被其他事务加了gap锁,那么本次insert操作会阻塞,并且当前事务会在该间隙上加一个插入意向锁,否则一般情况下insert操作是不加锁的。那如果一个事务首先插入了一条记录(此时并没有在内存生成与该记录原创 2022-12-28 10:31:46 · 850 阅读 · 0 评论 -
认真学习MySQL的事务日志-Undo日志
redo log是事务持久性的保证,undo log是事务原子性的保证。在事务中更新数据的前置操作时要先写入一个undo log。事务需要保证原子性,也就是事务中的操作要么全部完成,要么什么也不做。但有时候事务执行到一半会出现一些情况,比如:* 情况一:事务执行过程中可能遇到各种错误,比如服务器本身的错误,操作系统错误,甚至是突然断电导致的错误。* 情况二:程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前事务的执行。以上情况出现,我们需要把数据改回原先的样子,这个过程称之为回滚。这原创 2022-12-20 17:48:27 · 1007 阅读 · 1 评论 -
认真学习MySQL的事务日志-Redo日志
事务有4中特性:原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢?* 事务的隔离性由锁机制执行。* 事务的原子性、一致性和持久性由事务的redo日志和undo日志来保证。redo log称为重做日志,提供再写入操作,恢复提交事务修改的页操作,用来保证事务的持久性。undo log称为回滚日志,回滚行记录到某个特定版本,用来保证事务的原子性、一致性。需要说明的是,undo 不是redo的逆过程,redo 和 undo都可以视为一种恢复操作,但是有所区别。* redo原创 2022-11-30 12:31:39 · 1242 阅读 · 0 评论 -
MySQL中的分析表、检查表与优化表如何操作?
MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布,检查表主要是检查表是否存在错误,优化表主要是消除删除或者更新造成的空间浪费。# 【1】分析表MySQL中提供了 ANALYZE TABLE 语句分析表,analyze table 语句的基本语法如下:```sqlanalyze [local | no_write_to_binlog] table table_name[,tbl_name]....```默认的,MySQL服务会将analyze table语句写到bi原创 2018-03-15 18:26:34 · 4939 阅读 · 0 评论 -
count(1)、count(具体字段)和count(*)究竟有什么区别?
在MySQL中统计数据表的行数,可以使用三种方式:`select count(*) select count(1) 和 select count(具体字段)`,使用这三者之间的查询效率是怎样的?>前提:如果你要统计的是某个字段的非空数据行数,则另当别论,毕竟比较执行效率的前提是结果一样才可以。`count(*)` 和 `count(1)` 都是对所有结果进行count,二者本质丧没有区别(二者执行时间可能略有差别,不过你还是可以把它两的执行效率看成是相等的)。如果有 where 子句,则是对所有符合原创 2016-10-08 08:56:36 · 2868 阅读 · 0 评论 -
MySQL调优之关联查询、子查询优化
**多表 JOIN 连接操作时,创建索引注意事项总结**首先,连接表的数量尽量不要超过 3 张,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率。其次,对 WHERE 条件创建索引,因为 WHERE 才是对数据条件的过滤。最后,对用于`连接的字段创建索引`,并且该`字段在多张表中的类型必须一致`。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型,而不能一个为 int 另一个为 varchar 类型。如果原创 2022-10-31 21:38:03 · 4262 阅读 · 0 评论 -
MySQL调优之索引在什么情况下会失效?
MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快查询的速度,因此索引对查询的速度有着至关重要的影响。* 使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。* 如果查询时没有使用索引,查询语句就会扫描表中的所有记录。在数据量大的情况下,这样查询的速度回很慢。大多数情况下都(默认)采用B+树来构建索引。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。其实,用不用索引,最终都是优化器说了算。优化原创 2022-10-30 14:02:24 · 2420 阅读 · 2 评论 -
Explain的四种格式与查看优化器重写SQL
# 【1】explain四种输出格式explain可以输出四种格式:传统格式、json格式、tree格式以及可视化输出。用户可以根据需要选择适用于自己的格式。## ① 传统格式传统格式即默认格式,输出是一个表格形式,概要说明查询计划。```sqlexplain select s1.key1,s2.key1 from s1 left join s2 on s1.key1=s2.key1 where s2.common_field is not null;```![在这里插入图片描述](ht原创 2016-09-29 09:05:10 · 9939 阅读 · 1 评论 -
MySQL调优之大表处理探索那些事
当MySQL单表记录数过大时,数据库的CRUD性能会明显下降,一些常见的优化措施如下:* 限定数据的范围: 务必禁止不带任何限制数据范围条件的查询语句。比如:我们当用户在查询订单历史的时候,我们可以控制在一个月的范围内。;* 优化SQL和索引* 缓存: 使用MySQL的缓存,另外对重量级、更新少的数据可以考虑使用应用级别的缓存如Redis;* 读/写分离: 经典的数据库拆分方案,主库负责写,从库负责读;* MySQL自带的分区表。无需更改代码,但是sql语句是需要针对分区表做优化的,sql条件中要原创 2022-10-24 09:53:57 · 1056 阅读 · 0 评论 -
认真学习InnoDB的数据存储结构中的区、段与表空间
**关联博文:**[认真学习InnoDB的数据存储结构](https://janus.blog.csdn.net/article/details/127192889)[认真学习InnoDB的行格式](https://janus.blog.csdn.net/article/details/127234661)[认真学习InnoDB的数据存储结构中的区、段与表空间](https://janus.blog.csdn.net/article/details/78859646)前文[认真学习InnoDB的数原创 2017-12-21 09:12:37 · 3195 阅读 · 0 评论 -
认真学习InnoDB的行格式
我们平时的数据以行为单位来向表中插入数据,这些记录在磁盘上的存放方式也被称为`行格式`或者`记录格式`。InnoDB存储引擎设计了4中不同类型的行格式,分别是Compact、Redundant、Dynamic和Compressed行格式。查看MySQL默认行格式:```sql# 查看变量show VARIABLES like '%innodb_default_row_format%'# 查看MySQL系统变量select @@innodb_default_row_format```也可原创 2022-10-09 21:54:07 · 1229 阅读 · 0 评论 -
认真学习InnoDB的数据存储结构
索引结构给我们提供了高效的索引方式,不过索引信息以及数据记录都是保存在文件上的,确切说是存储在页结构中。另一方面,索引是在存储引擎中实现的,MySQL服务器上的存储引擎负责对表中数据的读取和写入工作。不同存储引擎中存放的格式一般是不同的,甚至有的存储引擎比如Memory都不用磁盘来存储数据。由于InnoDB是MySQL的默认存储引擎,所以我们有必要认真学习。# 【1】数据库的存储结构-页## ① 概述**InnoDB将数据划分为若干个页,InnoDB中页的大小默认为16KB**。以页作为磁盘和内原创 2022-10-07 12:48:02 · 752 阅读 · 0 评论 -
认真学习数据库缓冲池(buffer pool)
InnoDB存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面、写页面、创建新页面等操作)。而磁盘IO需要消耗的时间很多,而在内存中进行操作效率则会高很多。为了能让数据表或索引中的数据随时被我们所用,`DBMS会申请占用内存来作为数据缓冲池`,在真正访问页面之前,需要把在磁盘上的页缓存到内存中的`Buffer Pool`之后才可以访问。这样做的好处是可以让磁盘活动最小化,`从而减少与磁盘直接进行IO的时间`。要知道,这种策略对提升SQL语句的查询性能来说至关原创 2017-01-03 16:26:41 · 12780 阅读 · 2 评论 -
认真学习MySQL的逻辑架构
MySQL是典型的CS架构,即client/Server架构,服务器端程序使用的 mysqld。不论客户端进程和服务器进程是采用哪种方式进行通信,最后实现的效果都是:客户端进程向服务器进程发送一段文本(SQL语句),服务器进程处理后再向客户端进程发送一段文本(处理结果)。那服务器进程对客户端进程发送的请求做了什么处理,才能产生最后的处理结果呢?这里以查询请求为例展示:![在这里插入图片描述](https://img-blog.csdnimg.cn/fd59b1eee29c46fab42297296原创 2022-09-20 16:36:04 · 698 阅读 · 0 评论 -
认真学习MySQL中的那些日志文件-通用查询日志&错误日志
前面我们学习了MySQL的两种事务日志:重做日志、回滚日志。MySQL8.0下如下图所示:![在这里插入图片描述](https://img-blog.csdnimg.cn/f87a02bbef9c4c819a40b495cf3009d4.png)下面我们学习一下MySQL的其他日志,为什么要重视日志呢?对于线上数据库应用系统,突然遭遇数据库宕机怎么办?在这种情况下,`定位宕机的原因`就非常关键,我们可以查看数据库的`错误日志`。日志中记录了数据库运行中的诊断信息,包括了错误、警告和注释信息。比如:从日志原创 2017-02-14 15:59:52 · 1409 阅读 · 1 评论 -
MySQL中的存储引擎详解
为了管理方便,人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL Server的功能,把真实存取数据的功能划分为存储引擎的功能。所以在MySQL Server完成了查询优化后,只需按照生成的执行计划调用底层存储引擎提供的API,获取到数据后返回给客户端就好了。MySQL中提到了存储引擎的概念。简而言之,存储引擎就是指表的类型。其实存储引擎以前叫做表处理器,后来改名为存储引擎,它的功能就是接收上层传下来的指令,然后对表中的数据进行提取或写入操作。# 【1】 M原创 2017-03-27 10:40:31 · 5572 阅读 · 0 评论 -
MySQL不同版本下表结构和数据存储总结
本文我们尝试研究MySQL在5.7和8.0版本下Innodb引擎与MyISAM引擎下数据库文件存储形式。# 【1】MySQL5.7## ① InnoDB引擎如下图所示,我们随意选择一个数据库进行查看,可以看到有三种文件形式:opt、frm、ibd。**opt:** 包含了该数据库的各种属性,比如数据库的字符集和比较规则。**frm :** 存放了表的结构。>表结构就是该表的名称,表里面有多少列,每个列的数据类型、约束条件和索引,使用的字符集和比较规则等各种信息,这些信息都体现在了我们的原创 2017-06-05 14:14:37 · 2670 阅读 · 1 评论 -
Linux下搭建MySQL主从复制之一主一从架构
MySQL主从复制简单来讲就是slave会从master读取binlog来进行数据同步,其是异步且串行化的。**主从复制过程*** master将改变记录到二进制日志(binary log)文件,这些记录过程叫做二进制日志事件,binary log events。* slave将master的binary log event拷贝到它的中继日志(relay log)。* slave重做中继日志中的事件,将改变应用到自己的数据库中**复制的基本原则*** 每个slave只有一个master*原创 2022-09-15 15:25:32 · 827 阅读 · 0 评论 -
数据库事务特性、传播行为和隔离级别总结
# **【1】 事务的四个关键属性(ACID)****原子性(atomicity):** 事务是一个原子操作, 由一系列动作组成。事务的原子性确保动作要么全部完成要么完全不起作用。原子性强调事务的不可分割。**一致性(consistency):** 事务的执行的前后数据的完整性保持一致。一旦所有事务动作完成, 事务就被提交。数据和资源就处于一种满足业务规则的一致性状态中。**隔离性(isolation):** 一个事务执行的过程中,不应该受到其他事务的干扰。可能有许多事务会同时处理相同的数据,原创 2016-11-29 17:25:37 · 3272 阅读 · 0 评论 -
MySQL调优之show profile 应用
show profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以帮助进行SQL问题的诊断。**查看配置**```sqlshow variables like '%profiling%'# 本文这里如下have_profiling YES //是否支持profileprofiling OFF // OFF表示关闭,默认关闭profiling_history_size 15 // 记录最近15条语句```可以使用命令`set globa原创 2022-09-14 15:55:21 · 751 阅读 · 0 评论 -
MySQL调优之慢查询日志应用
慢查询日志是用于记录SQL执行时间超过某个临界值的SQL日志文件,可用于快速定位慢查询,为我们的SQL优化做参考。具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的SQL语句。**查看是否开启**```sqlshow variables like '%slow_query_log%'# 本文这里结果如下slow_query_log ONslow_query_log_file DESK原创 2022-09-14 13:49:40 · 907 阅读 · 0 评论 -
MySQL查询优化之order by 、 group by与分页查询优化
order by 子句尽量使用index方式排序(即using index),避免使用filesort方式排序(即using filesort)。Index方式效率高,它指MySQL扫描索引本身完成排序,filesort则效率低。**order by满足两种情况,会使用 index 方式排序:*** order by语句使用索引最左前列(最左匹配法则)* where子句和order by子句条件列组合满足最左匹配法则下面给出几个实例来说明,如下所示我们创建表并为其创建组合索引(c1,c2,c3)原创 2022-09-07 18:04:59 · 3411 阅读 · 1 评论 -
MySQL中最左匹配相关实践总结
首先回顾一下什么是最左匹配(也有称之为最左前缀)?顾名思义:最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、原创 2022-09-05 11:01:33 · 589 阅读 · 0 评论 -
MySQL中SQL生命周期与执行流程
# **【1】MySQL完整语法与执行顺序****如下:**```sql(7) SELECT (8) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY原创 2018-06-15 12:46:44 · 4775 阅读 · 1 评论 -
认真学习MySQL中锁机制(一)
MySQL的InnoDB锁机制分为表级锁和行级锁,官网文档:[点击查看](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html)# 【1】有哪些锁?## ① 按照锁的粒度划分在关系型数据库中,可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。MyISAM和InnoDB存储引擎使用的锁:* MyISAM采用表级锁(table-level locking)。* Inno原创 2017-11-28 16:24:28 · 9696 阅读 · 1 评论 -
MySQL中的(非)聚簇索引与索引覆盖详解
这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的。**每个InnoDB表都有一个特殊的索引,称为聚簇索引,用于存储行数据。*** 如果创建了一个主键,InnoDB会将其用作聚簇索引(如果主键没有逻辑唯一且非空的列或列集,最好是设置成自动递增的)* 如果没有为表创建主键,则MySQL会在所有键列都不为NULL的情况下找到第一个UNIQUE索引,InnoDB会将其用作聚集索引* 如果表没有PRIMARY KEY或合适原创 2017-11-01 16:02:37 · 1227 阅读 · 0 评论 -
MySQL索引基础入门详解
索引:系统根据某种算法,将已有的数据(未来可能新增的数据),单独建立一个文件;文件能够实现快速的匹配数据,并且能够快速的找到对应表中的记录。【1】索引的意义① 提升查询数据的效率; ② 约束数据的有效性(唯一性等);【2】索引增加的前提条件索引本身会产生索引文件,有时候甚至会比数据文件还大,非常耗费磁盘空间。如果某个字段要作为查询条件经常使用,那么可以使用索引;如果某个字段需要进行数据的有效性约束...................................................原创 2017-05-14 11:53:05 · 1599 阅读 · 0 评论 -
MySQL数据库调优进阶详解
**关联博文:**[MySQL优化之自身配置的优化实践](https://blog.csdn.net/J080624/article/details/88065417)[认真学习MySQL中的explain分析SQL](https://janus.blog.csdn.net/article/details/52785223)[MySQL中常见的优化策略详解](https://janus.blog.csdn.net/article/details/53199410)[MySQL中常见的优化策略详解(原创 2016-11-17 14:20:26 · 1876 阅读 · 0 评论 -
认真学习MySQL中的explain分析SQL
对于低性能的SQL语句的定位, 重要也是 有效的方法就是使用执行计划, MySQL提供了explain命令来查看语句的执行计划。 我们知道,不管是哪种数据库,或者是哪种数据库引擎,在对一条SQL语句进行执行的过程中都会做很多相关的优化,对于查询语句,最重要的优化方式就是使用索引。 而执行计划,就是显示数据库引擎对于SQL语句的执行的详细情况,其中包含了是否使用索引,使用什么索引,使用的索引的相关信息等。如下所示,我们使用explain分析一条带有子查询的SQL。```sqlexplain sel原创 2016-10-11 09:36:14 · 6912 阅读 · 1 评论 -
Trace分析优化器执行计划与Sys schema视图的使用详解
# 【1】分析优化器执行计划:trace`OPTIMIZER_TRACE`是MySQL5.6引入的一项跟踪功能,它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等),并将跟踪结果记录到`INFORMATION_SCHEMA.OPTIMIZER_TRACE`表中。此功能默认关闭,需要手动开启。开启trace,并设置格式为json,同时设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示。```sqlset optimizer_trace='e原创 2017-07-14 08:55:22 · 3757 阅读 · 1 评论 -
MySQL中锁等待超时与information_schema的三个表
【1】抛个异常异常如下:Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction翻译:锁等待超时,尝试重启事务。【2】information_schema的三个表① information_schema.innodb_trx–当前运行的所有事务 F...原创 2018-06-06 16:26:04 · 15644 阅读 · 1 评论 -
MySQL中wait_timeout与interactive_timeout详解
【1】分别是什么① interactive_timeout官网说明如下:MySQL服务器关闭交互式连接前等待的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。参数默认值:28800秒(8小时)② wait_timeoutMySQL服务器关闭非交互连接之前等待的秒数。在会话启动时,根据全局wai...原创 2018-06-05 18:07:59 · 20408 阅读 · 0 评论 -
MySQL单表膨胀优化之MyCat分库分表
MySQL的单表达到多少量级时性能会下降?宽表在千万量级,窄表要好一点在1200W左右。但是MySQL单表达到1500W时性能开始急剧下降!这里先抛出一道面试题:Q1:一亿的用户表,怎么做优化?Q2:两个实体,用户和订单,数据量都过亿要求:1.从用户角度快速查询订单2.从订单角度快速查用户怎么设计表?【1】常见数据库中间件分布式数据库中间件有TDDL、Sharding-JDBC...原创 2019-01-14 13:37:42 · 4399 阅读 · 3 评论 -
认真学习MySQL中的索引条件下推(ICP)
Index Condition Pushdown(ICP)是MySQL5.6中新特性,是一种在存储引擎层使用索引过滤数据的优化方式。如果没有ICP,存储引擎会遍历索引以定位基表中的行,并将它们返回给MySQL服务器,由MySQL服务器评估where后面的条件是否保留行。启用ICP后,如果部分 `where` 条件可以仅使用索引中的列进行筛选,则MySQL服务器会把这部分 `where` 条件放到存储引擎筛选。然后,存储引擎通过使用索引条目来筛选数据,并且只有在满足这一条件时才从表中读取行。* 好处原创 2019-01-21 18:20:58 · 940 阅读 · 0 评论 -
搞懂MySQL中索引的数据结构究竟是什么
【1】数据结构及算法基础① 索引的本质MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。我们知道,数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)........................................................转载 2019-02-15 18:21:44 · 1075 阅读 · 1 评论