![](https://img-blog.csdnimg.cn/0f9bd75b139a4c32b51cdc977b12e3aa.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
(待完善)mysql实战42讲——读书笔记
文章平均质量分 59
mysql实战42讲
蓝盾9527
这个作者很懒,什么都没留下…
展开
-
41 | 怎么最快地复制一张表?
mysqldump方法 使用mysqldump命令将数据导出成一组INSERT语句。你可以使用下面的命令:mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql主要参数含义如下:–single-transaction的作用原创 2021-11-14 23:52:27 · 374 阅读 · 0 评论 -
38 | 都说InnoDB好,那还要不要使用Memory引擎?
内存表的数据组织结构两个引擎的一些典型不同:InnoDB表的数据总是有序存放的,而内存表的数据就是按照写入顺序存放的;当数据文件有空洞的时候,InnoDB表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而内存表找到空位就可以插入新值;数据位置发生变化的时候,InnoDB表只需要修改主键索引,而内存表需要修改所有索引;InnoDB表用主键索引查询时需要走一次索引查找,用普通索引查询的时候,需要走两次索引查找。而内存表没有这个区别,所有索引的“地位”都是相同的。InnoDB支持原创 2021-11-14 23:49:53 · 485 阅读 · 0 评论 -
37 | 什么时候会使用内部临时表?
union 执行流程 union的语义是,取这两个子查询结果的并集。并集的意思就是这两个集合加起来,重复的行只保留一行。 内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键id的唯一性约束,实现了union的语义。但是如果把上面这个语句中的union改成union all的话,就没有了“去重”的语义。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了。group by 执行流程group by 优化方法 --索引 group b原创 2021-11-14 23:48:27 · 278 阅读 · 0 评论 -
36 | 为什么临时表可以重名?
临时表与内存表内存表,指的是使用Memory引擎的表,建表语法是create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。除了这两个特性看上去比较“奇怪”外,从其他的特征上看,它就是一个正常的表。而临时表,可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用Memory引擎。临时表只能被创建它的session访问,所以在这个session结束的时候,会自动原创 2021-11-14 23:46:30 · 194 阅读 · 0 评论 -
34 | 到底可不可以使用join?
使用join语句,性能比强行拆成多个单表执行SQL语句的性能要好;如果使用join语句的话,需要让小表做驱动表。 在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。 Index Nested-Loop Join(NLJ)和Block Nested-Loop Join(BNL)算法 第一个问题:能不能使用join语句?如果可以使用Index Nested-Loop Join算法,也原创 2021-11-14 23:45:02 · 314 阅读 · 0 评论 -
33 | 我查这么多数据,会不会把数据库内存打爆?
全表扫描对server层的影响 MySQL是“边读边发的”,这个概念很重要。这就意味着,如果客户端接收得慢,会导致MySQL服务端由于结果发不出去,这个事务的执行时间变长。 对于正常的线上业务来说,如果一个查询的返回结果不会很多的话,我都建议你使用mysql_store_result这个接口,直接把查询结果保存到本地内存。全表扫描对InnoDB的影响 由于MySQL采用的是边算边发的逻辑,因此对于数据量很大的查询结果来说,不会在server端保存完整的结果集。所以,如果客户端读结果不及时,会堵原创 2021-11-14 23:43:06 · 687 阅读 · 0 评论 -
32 | 为什么还有kill不掉的语句?
一个是kill query +线程id,表示终止这个线程中正在执行的语句;一个是kill connection +线程id,这里connection可缺省,表示断开这个线程的连接,当然如果这个线程有语句正在执行,也是要先停止正在执行的语句的。 跟Linux的kill命令类似,kill -N pid并不是让进程直接停止,而是给进程发一个信号,然后进程处理这个信号,进入终止逻辑。只是对于MySQL的kill命令来说,不需要传信号量参数,就只有“停止”这个命令。 kill无效的第一类情况,即:线程没有原创 2021-11-14 23:41:20 · 1197 阅读 · 0 评论 -
31 | 误删数据后除了跑路,还能怎么办?
误删行 恢复数据比较安全的做法,是恢复出一个备份,或者找一个从库作为临时库,在这个临时库上执行这些操作,然后再将确认过的临时库的数据,恢复回主库。这是因为,一个在执行线上逻辑的主库,数据状态的变更往往是有关联的。可能由于发现数据问题的时间晚了一点儿,就导致已经在之前误操作的基础上,业务代码逻辑又继续修改了其他数据。所以,如果这时候单独恢复这几行数据,而又未经确认的话,就可能会出现对数据的二次破坏。 可以把sql_safe_updates参数设置为on。这样一来,如果我们忘记在delete或者upda原创 2021-11-14 23:39:52 · 629 阅读 · 0 评论 -
23 | MySQL是怎么保证数据不丢的?
binlog的写入机制 事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。redo log的写入机制 redo log的三种状态分别是:存在redo log buffer中,物理上是在MySQL进程内存中,就是图中的红色部分;写到磁盘(write),但是没有持久化(fsync),物理上是在文件系统的page cache里面,也就是图中的黄色部分;持久化到磁盘,对应的是hard disk,也就是图中的绿色部分。 实际原创 2021-11-14 23:37:27 · 186 阅读 · 0 评论 -
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
短连接风暴第一种方法:先处理掉那些占着连接但是不工作的线程。 在show processlist的结果里,kill显示为sleep的线程,但是如果线程中存在事务,事务只能回滚,所以优先kill事务外空闲的连接。 通过查information_schema库的innodb_trx表,查看事务的具体状态。第二种方法:减少连接过程的消耗。 跳过权限验证,重启数据库,并使用–skip-grant-tables参数启动。这样,整个MySQL会跳过所有的权限验证阶段,包括连接过程和语句执行过程在内。但是原创 2021-11-14 23:35:10 · 250 阅读 · 0 评论 -
19 | 为什么我只查一行的语句,也执行这么慢?
第一类:查询长时间不返回 大概率是表被锁住了。一般都是首先执行一下show processlist命令,看看当前语句处于什么状态。等MDL锁 show processlist命令,显示Waiting for table metadata lock。表示现在有一个线程正在表t上请求或者持有MDL写锁,把select语句堵住了。 通过查询sys.schema_table_lock_waits这张表,我们就可以直接找出造成阻塞的process id,把这个连接用kill 命令断开即可。等flush原创 2021-11-10 23:38:52 · 360 阅读 · 0 评论 -
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
案例一:条件字段函数操作mysql> select count(*) from tradelog where month(t_modified)=7; 对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。因为对索引字段做函数操作后,得到的函数值在索引树中没有意义,优化器不知道怎么进行树搜索,最终会扫描索引字段的所有值。案例二:隐式类型转换 在MySQL中,字符串和数字做比较的话,是将字符串转换成数字。tradeid是varchar(32)类型mysql&原创 2021-11-10 23:37:11 · 458 阅读 · 0 评论 -
17 | 如何正确地显示随机消息?
全字段排序 MySQL会给每个线程分配一块内存用于排序,称为sort_buffer。 参数sort_buffer_size,就是MySQL为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。rowid排序 在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字原创 2021-11-10 23:34:02 · 445 阅读 · 0 评论 -
14 | count(*)这么慢,我该怎么办?
count(*)的实现方式 没有过滤条件的count()查询时,MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count()的时候会直接返回这个数,效率很高;而InnoDB引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。 因为InnoDB支持事务,可重复读是它默认的隔离级别(一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。),在代码上就是通过多版本并发控制,也就是MVCC来实现的。即使是在同一个时刻的多个查询,如果在事务原创 2021-11-05 09:15:11 · 369 阅读 · 0 评论 -
13 | 为什么表数据删掉一半,表文件大小不变?
参数innodb_file_per_table 表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的:这个参数设置为OFF表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;这个参数设置为ON表示的是,每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。 从MySQL 5.6.6版本开始,它的默认值就是ON了。 我建议你不论使用MySQL的哪个版本,都将这个值设置为ON。因为,一个表单独存储为一个文件更容原创 2021-11-05 09:15:43 · 384 阅读 · 0 评论 -
12 | 为什么我的MySQL会“抖”一下?
当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。 平时执行很快的更新操作,其实就是在写内存和日志,而MySQL偶尔“抖”一下的那个瞬间,可能就是在刷脏页(flush)。触发flush的场景InnoDB的redo log写满了。这时候系统会停止所有更新操作,把checkpoint往前推进,redo log留出空间可以继续写。系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出原创 2021-11-04 09:10:53 · 57 阅读 · 0 评论 -
11 | 怎么给字符串字段加索引?
前缀索引相比于普通索引,占用的空间会更小。但是,使用前缀索引时,因为前缀索引只存索引字段的前几个字节,索引在查询时,可能导致匹配到的行数变多,导致查询语句读数据的次数变多。 那么如何选择前缀索引,原则是区分度越高越好。因为区分度越高,意味着重复的键值越少。可以通过distinct 统计索引上有多少个不同的值来判断要使用多长的前缀。前缀索引对覆盖索引的影响 因为使用前缀索引,不确定是否截断了完整信息,所以必须回表,不能使用覆盖索引的查询优化。其他方式 遇到前缀的区分度不够好的情况时,原创 2021-11-04 09:10:33 · 101 阅读 · 0 评论 -
10 | MySQL为什么有时候会选错索引?
优化器的逻辑 优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。索引选择异常和处理一种方法是,像我们第一个例子一样,采用force index强行选择一个索引。 MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫原创 2021-11-03 23:30:41 · 157 阅读 · 0 评论 -
09 | 普通索引和唯一索引,应该怎么选择?
文章目录两种索引对查询和更新的性能影响查询更新change buffer的使用场景索引选择和实践change buffer 和 redo log小结两种索引对查询和更新的性能影响查询更新change buffer的使用场景索引选择和实践change buffer 和 redo log小结...原创 2021-11-03 23:17:20 · 71 阅读 · 0 评论 -
08 | 事务到底是隔离的还是不隔离的?
文章目录“快照”在MVCC里是怎么工作的?更新逻辑小结“快照”在MVCC里是怎么工作的?更新逻辑小结原创 2021-10-30 21:42:34 · 42 阅读 · 0 评论 -
07 | 行锁功过:怎么减少行锁对性能的影响?
文章目录两阶段锁死锁和死锁检测热点行更新导致的性能问题:小结 MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB是支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。 行锁就是针对数据表中行记录的锁。这很好理解,比如事务A更新了一行,而这时候事务B也要更新同一行,则必须等事务A的操作完成后才能进行原创 2021-10-29 13:22:04 · 90 阅读 · 0 评论 -
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
文章目录概念全局锁表级锁表锁表级锁与行级锁的比较元数据锁小结概念 数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则,而锁就是用来实现这些访问规则的重要数据结构。全局锁 全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。表级锁表锁 表锁的语法是 lock tables … read/write。表级锁与行级锁的比较 表级锁原创 2021-10-29 13:18:20 · 63 阅读 · 0 评论 -
05 深入浅出索引(下)
文章目录覆盖索引最左前缀原则索引下推小结覆盖索引最左前缀原则 在建立联合索引的时候,合理安排索引内的字段顺序。如果通过调整顺序,可以少维护一个索引,可以增强索引的复用能力,那么这个顺序往往就是需要优先考虑采用的。如果不得不维护两个索引,就考虑空间问题,优先创建占用空间小的单字段索引。索引下推 索引下推主要是应用于查询优化,MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,如果需要回表,会对索引中包含的字段先做判断,根据s原创 2021-10-28 22:31:20 · 124 阅读 · 0 评论 -
04 深入浅出索引(上)
文章目录索引索引的常见模型哈希数组+链表有序数组二叉树InnoDB 的索引模型主键索引非主键索引回表索引维护页的分裂与合并小结索引索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。索引的常见模型在MySQL中,索引是在存储引擎层实现的,所以并没有统一的索引标准,即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。哈希数组+链表哈希的思路很简单,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位原创 2021-10-28 19:06:31 · 80 阅读 · 0 评论 -
03 | 事务隔离:为什么你改了我还看不见?
文章目录事务隔离性与隔离级别概念问题:长事务存在的风险?多版本并发控制(MVCC)事务的启动方式小结事务保证一组数据库操作,要么全部成功,要么全部失败。在MySQL中,事务支持是在引擎层实现的。MyISAM引擎不支持事务,这也是MyISAM被InnoDB取代的重要原因之一。隔离性与隔离级别概念ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)原子性(Automicity):强调事务的不可分割性,同一个事务中的操作要原创 2021-10-26 23:02:28 · 53 阅读 · 0 评论 -
02 日志系统:一条SQL更新语句是如何执行的?
文章目录一条update语句的执行过程redo log(重做日志,物理日志)WAL技术crash-safebinlog(归档日志,逻辑日志)redo log与binlog的比较两阶段提交小结一条update语句的执行过程首先,连接器连接数据库;接下来,分析器会通过词法和语法解析知道这是一条更新语句;然后,优化器决定是否使用索引;最后,执行器负责具体执行,找到这一行,更新。redo log(重做日志,物理日志)Redo log是InnoDB引擎特有的日志。InnoDB的redo log是固定大小的,比原创 2021-10-26 22:57:30 · 68 阅读 · 0 评论 -
01 | 基础架构:一条SQL查询语句是如何执行的?
文章目录01 | 基础架构:一条SQL查询语句是如何执行的?MySQL的基本架构示意图Server层存储引擎层连接器问题:使用长连接内存上升很快?问题:长连接导致mysql异常重启查询缓存分析器优化器执行器小结01 | 基础架构:一条SQL查询语句是如何执行的?MySQL的基本架构示意图Server层Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程原创 2021-10-26 00:05:44 · 78 阅读 · 0 评论