![](https://img-blog.csdnimg.cn/20201014180756780.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
MySQL
sxlxwyr
不断学习才能让人更聪明
展开
-
联合索引使用是否生效例子
假设index(a,b,c)上图中where后是否用到了索引,如果用了,是否充分用到索引,用到了哪些列?为什么?命中了索引a 命中了索引a和b 索引充分利用,包含三列的联合索引都命中了 因为最左前缀原则,三个都没有命中索引 命中一个a 命中两个,a和b,也应该关注数据量,如果数据量少的话,范围索引b可能不会被命中 命中三个,但是也要看数据量,如果数据量太少的话,like范围...原创 2020-03-10 23:07:13 · 797 阅读 · 0 评论 -
MySQL的一些参数,以及参数查询方法
innodb_change_buffer_max_size:设置change buffer的大小,这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%。 innodb_stats_persistent:索引统计结果的存储方式,设置为on表示统计信息会持久化;设置为off表示只存储在内存中。 innodb_file_per_table :控制表...原创 2020-01-16 16:54:18 · 110 阅读 · 0 评论 -
39 | 自增主键为什么不是连续的?
自增主键可以让主键尽量地保持递增顺序插入,避免了也分裂,因此索引更紧凑。但是必须说明,自增主键不能保证连续递增。那什么情况下自增主键会出现“空洞”?创建一个表t,id是主键索引、c是唯一索引:CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) DEFAULT NULL, `d` in...转载 2019-08-30 10:17:28 · 642 阅读 · 0 评论 -
38 | 都说InnoDB好,那还要不要使用Memory引擎
前面group by语句都用了order by null,为什么使用内存临时表得到的语句结果里,0这个值在最后一行;而使用磁盘临时表得到的结果里0这个值在第一行?内存表的数据组织结构一下两张表,t1使用Memory引擎,t2使用InnoDB引擎。create table t1(id int primary key, c int) engine=Memory;create table...转载 2019-08-30 10:17:40 · 300 阅读 · 0 评论 -
37 | 什么时候会使用内部临时表
排序的时候会用到sort bufferjoin的时候会用到join buffer内存临时表内存临时表是怎么工作的,什么情况下会使用内存临时表:union执行流程(select 1000 as f) union (select id from t1 order by id desc limit 2);取这两个子查询结果的并集。并集的意思就是这两个集合加起来,重复的行...转载 2019-08-30 10:17:51 · 237 阅读 · 0 评论 -
36 | 为什么临时表可以重名
前面,在join优化查询的时候用到了临时表:create temporary table temp_t like t1;alter table temp_t add index(b);insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on t1...转载 2019-08-30 10:18:06 · 314 阅读 · 0 评论 -
35 | join语句怎么优化
Multi-Range Read(MRR)优化优化思路:顺序读盘比随机读盘效率高一般MySQL主键都是自增的,按照主键递增读取数据近似顺序读。优化后的语句执行流程:根据索引取出主键id,将id放入一块临时空间中(read_rnd_buffer); 对read_rnd_buffer中的id递增排序; 排序后的id数组依次到主键id索引中查记录,将结果放到结果集中。如果一次放不下...转载 2019-08-30 10:18:19 · 314 阅读 · 0 评论 -
34 | 到底可不可以使用join?
实际生产中长会碰到两个问题:DBA不让使用join,使用join有什么问题? 如果有两个大小不同的表做join,应该使用哪个表做驱动表呢?示例表:CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`...转载 2019-08-30 10:18:30 · 462 阅读 · 0 评论 -
33 | 我查这么多数据,会不会把数据库内存打爆
我的主机内存只有100G,现在要对一个200G的大表做全表扫描,会不会把数据库主机的内存用光?答案是对大表的扫描肯定是没问题,要不逻辑备份的时候不就是做整库扫描吗,不是早就挂了。全表扫描对server层的影响假设,我们现在要对一个200G的InnoDB表db1.t,执行一个全表扫描。当然,你要把扫描结果保存在客户端,会使用类似这样的命令:mysql -h$host -p$port...转载 2019-08-30 10:18:47 · 336 阅读 · 0 评论 -
25 | MySQL是怎么保证高可用的?
主备延迟“同步延迟”,与数据同步有关的时间点主要包括以下三个:主库A执行完一个事务,写入binlog,把这个时刻记为T1; 之后传给备库B,我们把备库B接收完这个binlog的时刻记为T2; 备库B执行完这个事务,这个时刻记为T3。主备延迟,就是同一个事务,在备库执行完成的时间和主库执行完成的时间之间的差值,也就是T3-T1。备库上执行show slave status命令,返回...转载 2019-08-30 10:20:31 · 181 阅读 · 0 评论 -
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
短连接风暴正常的短连接模式就是连接到数据库后,执行很少的SQL语句就断开,下次需要的时候再重连。如果使用的是短连接,在业务高峰期的时候,就可能出现连接数突然暴涨的情况。MySQL建立连接的过程,成本是很高的,除了正常的网络连接三次握手外,还需要做登录权限判断和获得这个连接的数据读写权限。在数据库压力比较小的时候,这些额外的成本不明显。但是,短连接模型存在一个风险,就是一旦数据库处理的慢...转载 2019-08-30 10:21:20 · 223 阅读 · 0 评论 -
40 | insert语句的锁为什么这么多?
一般来说,insert语句是一个很轻量级的操作,不过,这个结论对于“普通的insert语句”才有效。也就是说,对于那些“特殊情况”的insert,在执行过程中需要给其他资源加锁,或者无法再申请到自增id后就立马释放自增锁。insert ... select 语句在可重复读隔离级别下,binlog_format=statement时执行:insert into t2(c,d) sele...转载 2019-08-30 10:17:14 · 380 阅读 · 0 评论 -
41 | 怎么最快的复制一张表
如果可以控制对源表的扫描行数和加锁范围很小的话,我们简单地使用insert ... select 语句即可实现。为了避免对源表加读锁,更稳妥的方案是先将数据写到外部文件,然后再写回目标表。有两种方法。创建一个表db1.t,插入1000行数据,同时创建一个相同结构的表db2.t。如果要把db1.t中a>900的数据导出来,插入db2.t中。mysqldump方法使用mysq...转载 2019-08-30 10:16:55 · 254 阅读 · 0 评论 -
10 | MySQL为什么有时候会选错索引?
可能会出现一种情况,一条本来可以执行的很快的语句,却由于MySQL选错了索引,而导致执行速度变的很慢。先建一个表:CREATE TABLE `t` ( `id` INT(11) NOT NULL, `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY...转载 2019-08-30 10:29:06 · 292 阅读 · 0 评论 -
03 | 事务隔离:为什么你改了我还看不见?
MySQL是一个支持多引擎的系统,但并不是所有的引擎都支持事务。比如MySQL原生的MyISAM引擎就不支持事务,这也是MyISAM被InnoDB取代的重要原因之一。提到事务,肯定会想到ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性),这儿主要讲“隔离性”。隔离级别包括:读未提交、读提交、可重复度、串行...转载 2019-08-30 10:33:56 · 236 阅读 · 0 评论 -
01 | 基础架构:一条SQL查询语句是如何执行的
目录概括连接器查询缓存分析器执行器概括MySQL基本架构示意图:大体来说,MySQL可以分为Server层和存储引擎层两部分。Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨引擎的功能都在这一层实现,比如存储过程、触发器、视图等。存储引擎层负责数据...转载 2019-08-30 10:33:16 · 419 阅读 · 0 评论 -
02 | 日志系统:一条SQL更新语句是如何执行的
目录概述重要的日志模块:redo log重要的日志模块:binlog两阶段提交比如这一条语句:update T set c=c+1 where ID=2; 假设c原来的值是1。概述上一节中查询语句的执行流程,更新语句也同样会走一遍。执行语句之前先要连接数据库,这是连接器的工作。将表T上所有缓存结果都清空(这也是一般不建议使用查询缓存的原因)。接下来...转载 2019-08-30 10:31:58 · 351 阅读 · 0 评论 -
04 | 深入浅出索引(上)
索引的作用:提高数据查询效率,就像书的目录一样。索引模型常见的索引模型:哈希表、有序数组、搜索树。哈希表:一种以键-值(key-value)存储的数据结构。哈希思路:把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置哈希冲突的处理办法:链表哈希表适用场景:哈希表的好处是新增速度会很快,只需要往后追加;缺点是,因为不是有序的,...转载 2019-08-30 10:31:40 · 257 阅读 · 0 评论 -
05 | 深入浅出索引(下)
覆盖索引ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表,也就是说,在这个查询里面,索引k已经“覆盖了”我们的查询需求,称为覆盖索引。覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。最左前缀原则B+树这种索引结构,可以利用索引的“最左前缀”来定位记录。建立联合索引的时候,如何安排索引内的字段顺序?第一原则是,如...转载 2019-08-30 10:31:25 · 268 阅读 · 0 评论 -
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
数据库锁设计的初衷是处理并发问题。当出现并发访问的时候,数据库需要合理地控制资源访问规则,而锁就是来实现这些访问规则的重要数据结构。根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。全局锁顾名思义,就是对整个数据库实例加锁。加锁命令Flush tables with read lock(FTWRL)。加上锁以后,数据更新语句、数据定义语句和更新类食物的提交语...转载 2019-08-30 10:31:08 · 208 阅读 · 0 评论 -
07 | 行锁功过:怎么减少行锁对性能的影响?
MySQL的行锁时在引擎层有各个引擎自己实现的,但是并不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁。不支持行锁意味着只能使用表锁。InnoDB是支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。两阶段锁在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放,这就是两阶段锁协议。对我们的帮助就是,如果事务中...转载 2019-08-30 10:30:48 · 192 阅读 · 0 评论 -
08 | 事务到底是隔离的还是不隔离的?
问题:可重复度隔离级别下,当前事务不受其他事务的影响,好像与世无争。但是有行锁的时候,一个事务要更新一行,如果刚好有另外一个事务拥有这一行的行锁,它又不会这么超然,会被锁住,进入等待状态。问题是,既然进入了等待状态,那么等到这个事务自己获取到行锁要更新数据的时候,它读到的值又是什么呢?事务启动时机:begin/start transaction 该命令并不是一个事务的起点,...转载 2019-08-30 10:30:25 · 200 阅读 · 0 评论 -
09 | 普通索引和唯一索引,应该怎么选择?
在前面的基础篇文章中,介绍过索引的基本概念,相信你已经了解了唯一索引和普通索引的区别。今天我们就继续谈谈,在不同业务场景下,应该选择普通索引,还是唯一索引?假设你在维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查询姓名,就会执行类似这样的SQL语句:select name from CUser wher...转载 2019-08-30 10:29:57 · 267 阅读 · 0 评论 -
21 | 为什么我只改一行的语句,锁这么多?
间隙锁加锁规则的前提说明:MySQL后面的版本可能会改变加锁策略,所以这个规则只限于截止到现在的最新版本,即5.x系列<=5.7.24,8.0系列<=8.0.13。没有特殊说明,默认是可重复读隔离级别。规则里面包含了两个“原则”、两个“优化”和一个“bug”。原则1:加锁的基本单位是next-key lock,而且是前开后闭区间。 原则2:查找过程中访问到...转载 2019-08-30 10:21:33 · 167 阅读 · 0 评论 -
20 | 幻读是什么,幻读有什么问题?
示例表:CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),...转载 2019-08-30 10:21:49 · 182 阅读 · 0 评论 -
30 | 答疑文章(二):用动态的观点看加锁
加锁规则的两个“原则”,两个“优化”,和一个“bug”:原则1:加锁的基本单位是next-key lock。next-key lock是前开后闭区间。 原则2:查询过程中访问到的对象才会加锁。 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁...转载 2019-08-30 10:19:24 · 173 阅读 · 0 评论 -
29 | 如何判断一个数据库是不是出问题了?
主备切换有两种场景,一种是主动切换,一种是被动切换。被动切换,往往是因为主库出问题了,有HA熊发起的。怎么判断一个主库出问题了?连上MySQL执行一个select 1,成功返回了,就表示主库没问题吗?select 1判断成功返回,只能说明这个库的进程还在,并不能说明主库没问题。查表判断一般做法是,在系统里(mysql库)创建一个表,比如health_check,里面只放一行...转载 2019-08-30 10:19:34 · 299 阅读 · 0 评论 -
28 | 读写分离有哪些坑
读写分离上图中是客户端主动做负载均衡,这种模式下一般会把数据库的链接信息放在客户端的连接层。也就是说,由客户端来选择后端数据库进行查询。还有一种结构是,在MySQL和客户端之间有一个中间代理层proxy,客户端只连接proxy,由proxy根据请求类型和上下文决定请求的分发路由。两种方式各自的特点:客户端直连,因为少了一层proxy,查询性能稍微好一点,并且整体架构简单,排...转载 2019-08-30 10:19:49 · 222 阅读 · 0 评论 -
27 | 主库出问题了,从库怎么办?
大多数互联网场景是读多写少,会部署一主多从结构。如下:虚线箭头表示的是主备关系,也是A和A'互为主备,从库B、C、D指向的是主库A。一主多从的设置,一般用于读写分离,主库负责所有的写入和一部分读,其他请求则由从库分担。主库发生故障,主备切换后的结果:A'会成为新的主库。基于位点的主备切换GTID基于GTID的主备切换GTID和在线DDL总结:在主备切换过...转载 2019-08-30 10:19:59 · 213 阅读 · 0 评论 -
26 | 备库为什么会延迟好几个小时
前面提到的场景里,不论是偶发的查询压力,还是备份,对备库延迟的影响一般都是分钟级的,而且在备库恢复正常以后都能够追上来。但是,如果备库的执行日志的速度持续低于主库生成日志的速度,那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说,备库很可能永远都追不上主库的节奏。备库并行复制能力。主备流程图:备库日志执行比主库生成日志慢,意思就是图中上面的黑色箭头比下面黑色箭...转载 2019-08-30 10:20:13 · 329 阅读 · 0 评论 -
24 | MySQL是怎么保证主备一致的?
MySQL主备的基本原理基本的主备切换流程readonly设置对超级(super)权限的用户是无效的,用于同步更新的线程,就拥有超级权限。节点A到B这条线的内部流程:备库B跟主库A之间维持了一个长连接。主库A内部有一个线程,专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的:在备库B上通过change master命令,设置主库A的ip、端口、用户名、密...转载 2019-08-30 10:20:45 · 254 阅读 · 0 评论 -
44 | 答疑(三)
join写法如果用left join的话,左边的表一定是驱动表吗? 如果两个表的join包含多个条件的等值匹配,是都要写到on里面呢,还是只把一个条件写到on里面,其他条件写到where部分?构造两个表a、b:create table a(f1 int, f2 int, index(f1))engine=innodb;create table b(f1 int, f2 int)en...转载 2019-08-30 10:16:15 · 187 阅读 · 0 评论 -
23 | MySQL是怎么保证数据不丢的?
只要保证redo log和binlog持久化到磁盘,就能确保MySQL异常重启后数据可以恢复。binlog的写入机制binlog的写入逻辑比较简单:事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。系统给binlog cache分配了一片内存,每个线程一个,参数binlog_cache_size用于控制单个线程bi...转载 2019-08-30 10:21:03 · 244 阅读 · 0 评论 -
43 | 要不要使用分区表
分区表是什么?创建一个表t:CREATE TABLE `t` ( `ftime` datetime NOT NULL, `c` int(11) DEFAULT NULL, KEY (`ftime`)) ENGINE=InnoDB DEFAULT CHARSET=latin1PARTITION BY RANGE (YEAR(ftime))(PARTITION p_2017...转载 2019-08-30 10:16:27 · 231 阅读 · 0 评论 -
45 | 自增id用完了怎么办
无符号整型(unsigned int)是4个字节,上限就是2^32-1;当自增id用完了会怎么样呢?答案是:再申请下一个id时,得到的值保持不变。2^32-1(4294967295)不算是一个特别大的数,对于一个频繁插入删除数据的表来说,是可能会用完的。因此在建表的时候就需要考虑自己的表是否有可能达到这个上限,如果有可能,就应该创建成8个字节的bigint unsigned。Inn...转载 2019-08-30 10:16:00 · 302 阅读 · 0 评论 -
31 | 误删数据后除了跑路,还能怎么办?
先对和MySQL相关的误删数据做分类:使用delete语句误删数据行; 使用drop table或者truncate table语句误删数据表; 使用drop database语句误删数据库; 使用rm命令误删整个MySQL实例。误删行如果是使用delete语句误删了数据行,可以用Flashback工具通过闪回把数据恢复回来。Flashback恢复数据的原理,是修改binlog...转载 2019-08-30 10:19:08 · 276 阅读 · 0 评论 -
32 | 为什么还有kill不掉的语句?
MySQL中有两个kill命令:一个是kill query + 线程id,表示终止这个线程中正在执行的语句;一个是kill connection + 线程id,connection可缺省,表示断开这个线程的连接,如果这个线程有语句正在执行,也是要先停止正在执行的语句。问题描述:使用了kill命令,却没能断开这个连接。再执行show processlist命令,看到这条语句的Comman...转载 2019-08-30 10:18:58 · 452 阅读 · 0 评论 -
19 | 为什么我只查一行的语句,也执行这么慢?
有些情况下,“查一行”也会执行的特别慢,需要说明,如果MySQL数据本身就有很大的压力,导致数据库服务器CPU占用率很高或ioutil(IO利用率高)很高,这种情况下所有语句的执行都有可能变慢,不在今天的讨论范围。示例SQL(插入了10万行记录):mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) D...转载 2019-08-30 10:22:03 · 138 阅读 · 0 评论 -
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
对一些语句使用不当的话,就会不经意间导致整个数据库的压力变大。案例一:条件字段函数操作假设一个交易系统,包含交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段,简化的表结构如下:mysql> CREATE TABLE `tradelog` ( `id` int(11) NOT NULL, `tradeid` varcha...转载 2019-08-30 10:22:21 · 164 阅读 · 0 评论 -
17 | 如何正确地显示随机消息?
首页随机展示三个单词。数据表:create table `words` ( id int(11) not null auto_increment, word varchar(64) default null, primary key (`id`))engine=innodb;delimiter;;create procedure idata...转载 2019-08-30 10:22:36 · 189 阅读 · 0 评论