Mysql
文章平均质量分 79
私念
PHP GOLANG
展开
-
mysql中超大表的删除方法
参考:https://www.phpmianshi.com/?id=235在mysql中遇到一个大表,大概有17G左右,删除这张表。通常的删除操作可以通过delete、drop、truncate操作,但是有可能导致mysql hang住,必须使用些特殊的方法。1、建立硬链接找到mysql的数据文件,找到这张表在硬盘上的名称,我的是tmplst.frm和tmplst.ibd,通过查看tmplst.ibd的大小到了17G左右,这就是无法删除的原因。其中tmplst是表名。在这个文件夹下,为tmp原创 2021-02-05 19:45:50 · 946 阅读 · 0 评论 -
mysql中count(x)到底慢不慢
参考:https://www.phpmianshi.com/?id=181总结MyISAM 表不支持事务,但是count(*) 很快,因为他直接记录了一个总数MyISAM在统计表的总行数的时候会很快,但是有个大前提,不能加有任何WHERE条件。这是因为:MyISAM对于表的行数做了优化,具体做法是有一个变量存储了表的行数,如果查询条件没有WHERE条件则是查询表中一共有多少条数据,MyISAM可以做到迅速返回,所以也解释了如果加WHERE条件,则该优化就不起作用了。细心的同学会发现,inn.原创 2020-11-03 13:34:55 · 156 阅读 · 0 评论 -
mysql中找出无用的索引或重复索引
参考:https://www.phpmianshi.com/?id=177背景MySQL允许在相同列上创建重复的索引,但这样做对数据库却是有害而无利的,需要定期检查此类重复索引以改善数据库性能。可减少磁盘空间占用、减少磁盘IO、减少优化器优化查询时需要比较的索引个数、减少数据库维护冗余索引的各类开销、提高数据库性能(插入、更新、删除)重复索引检测pt-duplicate-key-checker:通过SHOW CREATE TABLE输出的表定义检测MySQL表中重复或者冗余的索引..原创 2020-11-02 09:25:42 · 769 阅读 · 0 评论 -
mysql中performance_schema(三) 实践篇
参考:https://www.phpmianshi.com/?id=180背景前一篇文章我们分析了performance_schema中每个表的用途,以及主要字段的含义,比较侧重于理论的介绍。这篇文章我主要从DBA的角度出发,详细介绍如何通过performance_schema得到DBA关心的数据,比如哪个SQL执行次数最多,哪个表访问最频繁,哪个锁最热等信息。通过充分利用performance_schema表的数据,让DBA更了解DB的运行状态,也更有助于排查定位问题。本文主要从..原创 2020-10-27 13:36:52 · 355 阅读 · 0 评论 -
mysql中performance_schema(二) 理论篇
参考:https://www.phpmianshi.com/?id=179背景mysql performance_schema中总共包含52个表,主要分为几类:Setup表,Instance表,Wait Event表,Stage Event表Statement Event表,Connection表和Summary表。上一篇文章已经着重讲了Setup表,这篇文章将会分别就每种类型的表做详细的描述。Instance表 instance中主要包含了5张表:cond_instances...原创 2020-10-26 13:50:04 · 313 阅读 · 0 评论 -
mysql中performance_schema(一)配置篇
背景 performance_schema最早在MYSQL 5.5中出现,而现在5.6,5.7中performance_schema又添加了更多的监控项,统计信息也更丰富,真乃DBA童鞋进行性能诊断分析的福音。检查当前数据库版本是否支持performance_schema被视为存储引擎。如果该引擎可用,则应该在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中都可以看到它的SUPPORT值为YES 但知道我们的实例支...原创 2020-10-23 09:32:43 · 1565 阅读 · 0 评论 -
mysql常见面试题第一讲
参考:https://www.phpmianshi.com/?id=176一、为什么用自增列作为主键1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。2、数据记录本身被原创 2020-10-12 16:48:18 · 118 阅读 · 0 评论 -
mysql中行锁、两阶段锁协议、死锁以及死锁检测
参考:https://www.phpmianshi.com/?id=175行锁MySQL的行锁都是在引擎层实现的,但是 MyISAM 不支持行锁,意味着并发控制只能使用表锁,同一张表任何时刻只能被一个更新在执行,影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替换的重要原因之一。行锁就是针对数据库中表的行记录的锁,这很好理解,比如事务 A 更新了一行,而这时候,事务 B 也要更新一行,则必须等事务 A 的操作完成后才能更新。两阶段锁 (Two-Phase原创 2020-09-28 15:31:45 · 991 阅读 · 0 评论 -
mysql中长事务详解
参考:https://www.phpmianshi.com/?id=174什么是长事务运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。下面我将演示下如何开启事务及模拟长事务:#假设我们有一张stu_tb表,结构及数据如下stu_id唯一索引mysql>select*fromstu_tb;+--------------+--------+----------+-------------...原创 2020-09-25 16:01:05 · 591 阅读 · 0 评论 -
mysql中CPU或内存利用率过高问题
参考:https://www.phpmianshi.com/?id=173CPU 利用率过高原因在 MySQL 使用过程中,出现 CPU 利用率过高甚至超过100%时,与数据库存在低效 SQL 或大量行锁冲突有非常大的关系,一般都是由于大量低效的 SQL 导致,出现行锁冲突的概率非常低。风险若 MySQL CPU 的利用率长时间处于100%,会严重影响数据库的整体性能,极端情况下可能会出现实例 HANG 住的情况。为避免业务因 CPU 资源不足而受影响,建议提前对 CPU 利用率过高的原创 2020-09-24 15:13:04 · 5502 阅读 · 1 评论 -
InnoDB体系结构及工作原理
参考:https://www.phpmianshi.com/?id=172概念InnoDB主要包括了内存池、后台线程以及存储文件。INNODB的三大特性:插入缓存,两次写,自适应hash内存池又是由多个内存块组成的,主要包括Buffer Pool、redo log缓冲等,解决cpu速度和磁盘速度的严重不匹配问题后台线程则包括了Master Thread、IO Thread以及Purge Thread等,后台进程保证缓存池和磁盘数据的一致性(读取、刷新),并保证数据异常宕机时能恢复到正常状态。原创 2020-09-22 19:34:44 · 963 阅读 · 0 评论 -
mysql中一条sql语句是如何执行的
参考:https://www.phpmianshi.com/?id=171架构mysql是一种单进程多线程的架构,mysql server层的核心组件:连接器、查询缓存、分析器、优化器、执行器一条sql语句是如何执行的 客户端发送一条查询给服务器 服务器先检查查询缓存,如果命中缓存,则返回缓存结果 服务器进行sql解析、预处理、再有优化器生成对应的执行计划 mysql根据优化器生成的执行计划,调用存储引擎的API来执行查询 返回结果 ..原创 2020-09-16 21:26:29 · 190 阅读 · 0 评论 -
mysql中AnalyzeTable优化
参考:https://phpmianshi.com/?id=165Analyze TableMySQL的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度,那么索引就基本失效了。我们可以使用SHOW INDEX语句来查看索引的散列程度语法ANALYZETABLE表名1[,表名2…];...原创 2020-09-15 20:11:50 · 2536 阅读 · 0 评论 -
mysql中QueryCache优化
参考:https://phpmianshi.com/?id=156原理MySQL 的 Query Cache 实现原理实际上并不是特别的复杂,简单的来说就是将客户端请求的 Query语句(当然仅限于 SELECT 类型的 Query)通过一定的 hash 算法进行一个计算而得到一个 hash 值,存放在一个 hash 桶中。同时将该 Query 的结果集(Result Set)也存放在一个内存 Cache 中的。存放 Queryhash 值的链表中的每一个 hash 值所在的节点中同时还存放...原创 2020-09-11 14:57:38 · 136 阅读 · 0 评论 -
mysql中distinct的实现与优化
参考:https://phpmianshi.com/?id=155概念DISTINCT 实际上和 GROUP BY的操作非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现,当然,在无法仅仅使用索引即能完成 DISTINCT 的时候,MySQL只能通过临时表来完成。但是,和 GROUP BY 有一点差别的是,DISTINCT 并不需要进行排序。.原创 2020-09-09 20:04:08 · 2901 阅读 · 0 评论 -
mysql中group by的实现与优化
参考:https://phpmianshi.com/?id=154概念由于 GROUP BY 实际上也同样需要进行排序操作,而且与 ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。所以,在 GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引。在 MySQL 中,GROUP BY 的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来...原创 2020-09-08 18:39:35 · 862 阅读 · 0 评论 -
mysql中order by的实现与优化
概念在 MySQL 中,ORDER BY 的实现有如下两种类型:◆ 一种是通过有序索引而直接取得有序的数据,这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端;◆ 另外一种则需要通过 MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端;示例首先分析一下第一种不用排序的实现方式EXPLAINSELECTm.id,m.subject,c.contentFROMgroup_messagem...原创 2020-09-07 09:42:08 · 361 阅读 · 0 评论 -
mysql中Join的实现原理及优化思路
参考:https://phpmianshi.com/?id=152实现原理在MySQL中,只有一种 Join 算法,就是大名鼎鼎的 Nested Loop Join,他没有其他很多数据库所提供的 Hash Join,也没有 Sort Merge Join。顾名思义,Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与 Join,则再通过前两个表的 Join 结果集作为.原创 2020-09-04 15:47:52 · 277 阅读 · 0 评论 -
mysql中Query语句优化基本思路和原则
参考:https://phpmianshi.com/?id=151基本思路和原则在分析如何优化 MySQL Query 之前,我们需要先了解一下 Query 语句优化的基本思路和原则。一般来说,Query 语句的优化思路和原则主要体现在以下几个方面:1. 优化更需要优化的 Query;2. 定位优化对象的性能瓶颈;3. 明确的优化目标;4. 从 Explain 入手;5. 多使用 profile6. 永远用小结果集驱动大的结果集;7. 尽可能在索...原创 2020-09-03 20:26:19 · 222 阅读 · 0 评论 -
mysql中Query语句对系统性能的影响
参考:https://phpmianshi.com/?id=150背景我们重点分析实现同样功能的不同 SQL语句在性能方面会产生较大的差异的根本原因,并通过一个较为典型的示例来对我们的分析做出相应的验证。为什么返回完全相同结果集的不同SQL语句,在执行性能方面存在差异呢?这里我们先从SQL 语句在数据库中执行并获取所需数据这个过程来做一个大概的分析了。详解当 MySQL Server 的连接线程接收到 Client 端发送过来的 SQL 请求之后,会经过一系列的分解Pars...原创 2020-09-02 19:54:05 · 183 阅读 · 0 评论 -
mysql中优化器是如何选择索引的
参考:https://phpmianshi.com/?id=148一:概念 - 在 索引建立之后,一条语句可能会命中多个索引,这时,索引的选择,就会交由优化器 来选择合适的索引。 -优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。二:优化器选择索引的原则? -在数据库里面,扫描行数是影响执行代价的因素之一。 -扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。 -当然,扫描行数并不是唯一的判断标准,优化器还...原创 2020-08-31 16:21:20 · 587 阅读 · 0 评论 -
mysql中explain分析sql详解
参考:https://phpmianshi.com/?id=147Explain举例 mysql>explainselect*fromevent; +—-+————-+——-+——+—————+——+———+——+——+——-+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +—-+————-+——-+—...原创 2020-08-28 15:17:23 · 98 阅读 · 0 评论 -
innodb中内存管理机制BufferPool
参考:https://phpmianshi.com/?id=143内存结构InnoDB主索引是聚簇索引,索引与数据共用表空间,对于InnoDB而言,数据就是索引,索引就是数据。InnoDB缓存机制和MyISAM缓存机制的最大区别就是在于,InnoDB不仅仅是缓存索引,还会缓存数据。InnoDB缓存池InnoDB缓存池(InnoDB buffer pool)是InnoDB性能提升的核心,它既可以缓存数据还可以缓存索引,甚至其他管理数据(例如元数据信息,行级锁信息)。查看InnoDB.原创 2020-08-26 09:28:19 · 420 阅读 · 0 评论 -
innodb中统计数据是如何收集的
参考:https://www.phpmianshi.com/?id=142InnoDB 统计数据如何查看1. 通过SHOW TABLE STATUS可以看到关于表的统计数据2. 通过SHOW INDEX可以看到关于索引的统计数据InnoDB提供了两种存储统计数据的方式1. 永久性的统计数据:这种统计数据存储在磁盘上,也就是服务器重启之后这些统计数据还在。2. 非永久性的统计数据:这种统计数据存储在内存中,当服务器关闭时这些这些统计数据就都被清除掉...原创 2020-08-24 09:45:06 · 402 阅读 · 0 评论 -
mysql中性能分析Profiling
参考:https://www.phpmianshi.com/?id=138概念Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于Sql调优的测量。示例1、先查看是否开启了此功能,默认情况下,参数处于关闭状态,为OFF状态show variables like 'profiling%';2、开启Profiling:setprofiling=1;3、展示最近的SQL执行情况:s...原创 2020-08-21 18:25:16 · 289 阅读 · 0 评论 -
mysql中eq_range_index_dive_limit参数学习
参考:https://www.phpmianshi.com/?id=137概念官方文档如下描述:This variable indicates the number of equality ranges in an equality comparison condition when the optimizer should switch from using index dives to index statistics in estimating the number of qualifyin原创 2020-08-18 20:53:20 · 2891 阅读 · 0 评论 -
mysql中数据页的相关概念
参考:https://www.phpmianshi.com/?id=136概念在 InnoDB 存储引擎中,所有的数据都被逻辑地存放在表空间中,表空间(tablespace)是存储引擎中最高的存储逻辑单位,在表空间的下面又包括段(segment)、区(extent)、页(page),他们之间的关系如下:数据页页是mysql中磁盘和内存交换的基本单位,也是mysql管理存储空间的基本单位。同一个数据库实例的所有表空间都有相同的页大小;默认情况下,表空间中的页大小都为 16K..原创 2020-08-17 20:24:29 · 5656 阅读 · 0 评论 -
mysql中index_merge索引合并优化
参考:https://phpmianshi.com/?id=135前言深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。MySQL在分析执行计划时发现走单个索引的过滤效果都不是很好,对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行 AND 或者 OR,那么此时就有可能会使用到 in原创 2020-08-14 12:01:13 · 1793 阅读 · 0 评论 -
MySQL可重复读隔离级别的实现原理
参考:https://www.phpmianshi.com/?id=23原理MySQL默认的隔离级别是可重复读,即:事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交,那么事务A再读该数据,读到的还是原来的内容。 那么MySQL可重复读是如何实现的呢?使用的的一种叫MVCC的控制方式 ,即Mutil-Version Concurrency Control,多版本并发控制,类似于乐观锁的一种实现方式实现方式InnoDB在每行记录后面保存两个隐藏的列来,分别保存了这个行的创.原创 2020-08-13 13:36:51 · 227 阅读 · 0 评论 -
mysql中优化必读
参考:https://www.phpmianshi.com/?id=24通用规则多读少写加缓存,少读多写加队列带事务的不要整体commit,分段commit一、要保证数据库的效率,要做好以下四个方面的工作:① 数据库设计② sql语句优化③ 数据库参数配置④ 恰当的硬件资源和操作系统二、通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解):第一范式:1.原创 2020-08-12 09:52:32 · 116 阅读 · 0 评论 -
InnoDB的七种锁
参考:https://www.phpmianshi.com/?id=22InnoDB的七种锁1. 自增锁(Auto-inc Locks)2. 共享/排他锁(Shared and Exclusive Locks)3. 意向锁(Intention Locks)4. 插入意向锁(Insert Intention Locks)5. 记录锁(Record Locks)6. 间隙锁(Gap Locks)7. 临键锁(Next-Key Locks)相关名词|--表级锁(..原创 2020-08-11 17:06:51 · 459 阅读 · 0 评论 -
Mysql B+树索引常见面试题
参考:https://www.phpmianshi.com/?id=21概念一个经典的B+树索引数据结构见下图:B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。因此,B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下,xfs文件系统比ext3/ext4效率高很多...原创 2020-08-09 19:38:48 · 2144 阅读 · 0 评论 -
MyISAM与InnoDB的区别
参考:https://www.phpmianshi.com/?id=77MyISAM与InnoDB的区别是什么?这是一个常见的面试题,我们要具从不同的角度解读一下。1、存储结构MyISAM:每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD(MYData)。索引文件的扩展名是.MYI(MYIndex)。InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),I...原创 2020-08-09 13:00:04 · 68 阅读 · 0 评论 -
MySQL可重复读隔离级别的实现原理
1、原理MySQL默认的隔离级别是可重复读,即:事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交,那么事务A再读该数据,读到的还是原来的内容。 那么MySQL可重复读是如何实现的呢?使用的的一种叫MVCC的控制方式 ,即Mutil-Version Concurrency Control,多版本并发控制,类似于乐观锁的一种实现方式实现方式:InnoDB在每行记录后面保存...原创 2020-04-06 12:33:04 · 256 阅读 · 0 评论 -
InnoDB的七种锁
InnoDB的七种锁自增锁(Auto-inc Locks)共享/排他锁(Shared and Exclusive Locks)意向锁(Intention Locks)插入意向锁(Insert Intention Locks)记录锁(Record Locks)间隙锁(Gap Locks)临键锁(Next-Key Locks)默认事务隔离级别为可重复读(Repeated Read,...原创 2019-11-14 09:57:56 · 166 阅读 · 0 评论 -
Mysql B+树索引
备注:先说下,在MySQL文档里,实际上是把B+树索引写成了BTREEunique key(uid) USINGBTREE,一个经典的B+树索引数据结构见下图:B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以...原创 2019-11-14 09:55:36 · 244 阅读 · 0 评论