mysql – try66

MySQL 不走索引的一种情况

这周收到一个 sentry 报警，如下 SQL 查询超时了。 select * from order_info where uid = 5837661 order by id asc limit 1 执行show create table order_info 发现这个表其实是有加索引的 CREATE TABLE `order_info` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned, `order_status` tinyint(3) DEFAULT NULL, … 省略其它字段和索引 PRIMARY KEY (`id`), KEY `idx_uid_stat` (`uid`,`order_status`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 理论上执行上述 SQL 会命中 idx_uid_stat 这个索引，但实际执行 explain 查看 explain select * from order_info where uid = 5837661 order by id asc limit 1 可以看到它的 possible_keys（此 SQL 可能涉及到的索引）是 idx_uid_stat，但实际上（key）用的却是全表扫描我们知道 MySQL 是基于成本来选择是基于全表扫描还是选择某个索引来执行最终的执行计划的，所以看起来是全表扫描的成本小于基于 idx_uid_stat 索引执行的成本，不过我的第一感觉很奇怪，这条 SQL 虽然是回表，但它的 limit 是 1，也就是说只选择了满足 uid = 5837661 中的其中一条语句，就算回表也只回一条记录，这种成本几乎可以忽略不计，优化器怎么会选择全表扫描呢。为了查看 MySQL 优化器为啥选择了全表扫描，我打开了 optimizer_trace 来一探究竟画外音：在MySQL 5.6 及之后的版本中，我们可以使用 optimizer… Continue reading MySQL 不走索引的一种情况

57张图，13个实验，干死 MySQL 锁！

你好，我是yes。前段时间写了一篇关于 MySQL 锁的文章，一些小伙伴们在阅读之后产生了一些疑问，这些问题还挺有代表性的，所以在这里做个实验，来用事实探究一番。那篇文章提到了记录锁（Record Locks），顾名思义锁的是记录，作用在索引上的记录。锁是作用在索引上这句话可能不太好理解，并且对于在可重复读和读提交两个隔离级别下，关于是否命中二级索引的锁之间的阻塞也不太清晰。这句话读着可能有点拗口，没事，我来给你看几个实验，对这一切就异常清晰了。实验的 MySQL 版本为：5.7.26。实验一：隔离级别为读提交，锁定非索引列的实验先建个非常简单的表，只有主键索引，没有二级索引。 CREATE TABLE `yes` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `address` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 隔离级别如下：关闭自动提交事务：已经准备好的数据：此时，发起事务 A，执行如下语句，且事务未提交：接着，再发起事务 B，执行如下语句：你可能以为事务 B 不会被阻塞，因为事务 B 锁的是name=xx和事务A锁name=yes讲道理相互之间没有冲突，但是从结果来看，事务 B 被阻塞了，调用select * from innodb_lock_waits;看下谁等谁可以看到，事务6517(B)在等待事务6516(A)。此时，调用 SELECT * FROM innodb_locks; 查看相关锁的信息锁的类型就是行级锁，此时的锁为 X 锁，锁的索引就是主键索引，这个结果表明的意思是事务 B（6517）想要 id 为 1 的记录锁，但是这个记录此时被事务A（6516）占有。是的，这里的 1 其实不是指第一个记录的意思，是 id… Continue reading 57张图，13个实验，干死 MySQL 锁！

一个MySQL锁和面试官大战三十回合，我霸中霸！

我，小Y。又来面试了，还是之前那家公司，即将和之前那个老面试官进行第二次 battle，心情还是xue微有点忐忑。没看过第一次 battle 的同学可以看这里，一个MVCC和面试官大战三十回合又一抹光亮闪过，面试官推门而入，我抬头望去，没错，还是那味儿。看到面试官头上那“傲然矗立”的头发，差点又想站起来给他敬了个礼，算了先稳住，低调一点。面试官瞥了我一眼：来吧，咱们继续面试，上次没办法，女朋友就是粘人，这次问 MySQL InnoDB 的锁喔。我：…..(行，我知道你有女朋友了)，好的面试官，您请。面试官：MySQL InnoDB 的锁和 MyISAM 的锁有什么区别？我：MyISAM 只支持表锁，一锁就锁整张表，而 InnoDB 不仅支持表锁，还支持粒度更低的行锁，仅对相关的记录上锁即可，所以对于写入操作来说 InnoDB 的性能更高。面试官：那不论表锁还是行锁，其实有分为两类的，你知道是哪两类吗？我：你指的是 shared (S) locks 和 exclusive (X) locks 吗？ S锁，称为共享锁，事务在读取记录的时候获取 S 锁，它允许多个事务同时获取 S 锁，互相之间不会冲突。 X锁，称为独占锁，事务在修改记录的时候获取 X 锁，且只允许一个事务获取 X 锁，其它事务需要阻塞等待。所以 S 锁之间不冲突，X 锁则为独占锁，所以 X 之间会冲突， X 和 S 也会冲突。… Continue reading 一个MySQL锁和面试官大战三十回合，我霸中霸！

关于MySQL的酸与MVCC和面试官小战三十回合

我，小Y。此刻，正坐在办公室里等待面试，心情xue微有点忐忑，不知道待会儿老面试官经不经得住我的折磨。只见一抹光亮闪过，面试官推门而入，我抬头望去，强者的气息铺面而来，没错是那味儿。看到面试官头上那“傲然矗立”的头发，脑海中止不住幻想他在无数个凌晨于电脑前挑灯夜码的高大形象，一种敬佩感油然而生，竟忍不住站起来给他敬了个礼。面试官：有病？我：没没没，我谢顶反应综合征犯了，面试官好，我是小 Y ，请多多指教。面试官：哦哦，确实是有病啊，没事，记得吃药就行。我看你简历写你 MySQL 挺懂的，那我先问问你 MySQL 吧。我：好嘞，您请。面试官：你知道什么是 MySQL 的酸吗？这一来就这么猛的吗？脑海中一顿搜索，只能想起张含韵的我喜欢酸的甜这就是真的我之《酸酸甜甜就是我》，算了蒙一个。我：事务? 面试官：哟，最近好多谐音梗，我特意玩了个英语单词短语梗，脑子转的挺快啊小伙子。酸，英文 acid，说的就是事务！这都蒙对了，等下就去买彩票！趁这个机会再表现一下！我：是啊，国外的人就有拼凑单词的习惯，其实事务主要是为了实现 C ，也就是一致性，具体是通过AID，即原子性、隔离性和持久性来达到一致性的目的，所以这四个不应该相提并论，但是他们就想拼成单词，就把它们排好序搞在一起来念。嘿嘿，这个B装的我有点舒服，果然面试官有点惊讶。面试官：可以呀，那你知道 MVCC 吧？我：知道，Multi-Version Concurrency Control （多版本并发控制）。面试官：能先简短的解释下什么是 MVCC 吗？我：多版本并发控制，其实指的是一条记录会有多个版本，每次修改记录都会存储这条记录被修改之前的版本，多版本之间串联起来就形成了一条版本链。这样不同时刻启动的事务可以无锁地获得不同版本的数据(普通读)。此时读(普通读)写操作不会阻塞，写操作可以继续写，无非就是多加了一个版本，历史版本记录可供已经启动的事务读取。（为保持简短，简化了SQL语句，下文也同样简化）面试官：那你知道事务四种隔离级别吧？我：读未提交、读已提交、可重复读、可串行化。面试官：MVCC 用来实现哪几个隔离级别？我：用来实现读已提交和可重复读。首先隔离级别如果是读未提交的话，直接读最新版本的数据就行了，压根就不需要保存以前的版本。可串行化隔离级别事务都串行执行了，所以也不需要多版本，因此 MVCC 是用来实现读已提交和可重复读的。面试官：那为什么需要 MVCC ？如果没有 MVCC 会怎样？我：如果没有 MVCC 读写操作之间就会冲突。想象一下有一个事务1正在执行，此时一个事务2修改了记录A，还未提交，此时事务1要读取记录A，因为事务2还未提交，所以事务1无法读取最新的记录A，不然就是发生脏读的情况，所以应该读记录A被事务2修改之前的数据，但是记录A已经被事务2改了呀，所以事务1咋办？只能用锁阻塞等待事务2的提交，这种实现叫… Continue reading 关于MySQL的酸与MVCC和面试官小战三十回合

值得收藏，揭秘 MySQL 多版本并发控制实现原理

▲ 点击上方“架构精进之路”关注公众号回复“01”领取「程序员进阶大礼包」架构精进之路十年研发风雨路，大厂架构师，CSDN博客专家。专注软件架构研究，技术学习与职业成长，坚持分享接地气儿的架构技术干货文章！ 78篇原创内容公众号这是「架构精进之路」公众号的第73篇原创文章 MySQL 中多版本并发控制（MVCC），是现代数据库引擎实现中常用的处理读写冲突的手段，MVCC 作为 MySQL 高级应用特性，目的在于提高数据库高并发场景下的吞吐性能。一、MVCC出现背景是什么？事务的4个隔离级别以及对应的3种异常：脏读：一个事务读取到了另外一个事务没有提交的数据；不可重复读：在同一事务中，两次读取同一数据，得到内容不同；幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同。在 MySQL 中，默认的隔离级别是可重复读，可以解决脏读和不可重复读的问题，但不能解决幻读问题。如果我们想要解决幻读问题，就需要采用串行化的方式，也就是将隔离级别提升到最高，但这样一来就会大幅降低数据库的事务并发能力。而MVCC就是通过乐观锁的方式来解决不可重复读和幻读问题，它可以在大多数情况下替代行级锁，降低系统的开销。 MySQL 并发事务会引起更新丢失问题，解决办法是锁，主要分两类：乐观锁：其实现如同它的名字一样，是假设比较好的情况。每次取数据的时候都认为他人不会对其修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。悲观锁：悲观锁也如同它的名字一样，总是假设比较坏的情况，每次取数据的时候都认为他人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。二、什么是MVCC，它解决了什么问题？ MVCC 是通过数据行的多个版本管理来实现数据库的并发控制，简单来说它的思想就是保存数据的历史版本。我们可以通过比较版本号决定数据是否显示出来（具体的规则后面会介绍到），读取数据的时候不需要加锁也可以保证事务的隔离效果。通过 MVCC 我们可以解决以下几个问题：（1）读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。（2）降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。（3）解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。解释一下可能难以理解的几个词汇：快照读：读取的是快照数据，不加锁的简单的SELECT都属于快照读（只是普通的读操作）。当前读：当前读就是读取最新数据，而不是历史版本的数据。加锁的SELECT，或者对数据进行增删改都会进行当前读（包括加锁的读取和DML操作）。三、应用举例分析为了更好地让大家理解MVCC，我们用一个示例场景来说明。假设有个账户金额表 user_balance，包括三个字段，分别是 username… Continue reading 值得收藏，揭秘 MySQL 多版本并发控制实现原理

MySQL Nested Loop Join

Nested Loop Join分为 Index Nested Loop JOIN 和 Block Nested Loop Join两种 INJ全称Index Nested Loop JOIN 将 “驱动表/外部表” 的结果集作为循环基础数据，然后循环该结果集，每次获取一条数据作为下一个表的过滤条件查询数据，然后合并结果，获取结果集返回给客户端。Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内层循环便要执行多少次，效率非常差。一. 被驱动表有可用索引的情况: 如果内层循环(被驱动表)利用到了索引，可以视为一种新的算法Index Nested_Loop JOIN，简称为 INJ 。示例(t1有100条记录,t2有1000条记录)： 1. 执行select * from t1，查出表 t1 的所有数据，这里有 100 行； 2. 循环遍历这 100 行数据： 2.1 从每一行 R 取出字段 a 的值 $R.a； 2.2 执行select * from t2 where a=$R.a；需要检查t2中利用到了索引 2.3 把返回的结果和… Continue reading MySQL Nested Loop Join

MySQL MMR

MMR全程Multi-Range Read，5.6新特性当表很大的时候，使用二级索引进行范围的读取行回表导致磁盘的随机访问。原理：先查询满足条件的索引元组，然后按照数据行ID进行排序，对排序后的元组回表检索数据行使用场景：可以基于InnoDB和MyISAM引擎进行优化可以基于集群多范围索引扫描表使用MRR的时候,Extra 显示Using MRR select * from user where uid > 100 在没有MMR的情况下先获取数据集，然后根据集合回表，返回满足的数据使用MMR情况下先获取数据集，然后对数据集排序（主键排序），最后回表查询完整行

MySQL降序索引

什么是降序索引大家可能对索引比较熟悉，而对降序索引比较陌生，事实上降序索引是索引的子集。我们通常使用下面的语句来创建一个索引： create index idx_t1_bcd on t1(b,c,d); 上面sql的意思是在t1表中，针对b,c,d三个字段创建一个联合索引。但是大家不知道的是，上面这个sql实际上和下面的这个sql是等价的： create index idx_t1_bcd on t1(b asc,c asc,d asc); asc表示的是升序，使用这种语法创建出来的索引叫做升序索引。也就是我们平时在创建索引的时候，创建的都是升序索引。可能你会想到，在创建的索引的时候，可以针对字段设置asc，那是不是也可以设置desc呢？当然是可以的，比如下面三个语句： create index idx_t1_bcd on t1(b desc,c desc,d desc); create index idx_t1_bcd on t1(b asc,c desc,d desc); create index idx_t1_bcd on t1(b asc,c asc,d desc); 这种语法在mysql中也是支持的，使用这种语法创建出来的索引就叫降序索引，关键问题是：在Mysql8.0之前仅仅只是语法层面的支持，底层并没有真正支持。我们分别使用Mysql7、Mysql8两个版本来举例子说明一下：在Mysql7、Mysql8中分别创建一个表，有a,b,c,d,e五个字段： create table t1 ( a int primary… Continue reading MySQL降序索引

MySQL索引下推

一、简介 ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。二、原理为了理解ICP是如何工作的，我们先了解下没有使用ICP的情况下，MySQL是如何查询的：存储引擎读取索引记录；根据索引中的主键值，定位并读取完整的行记录；存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。使用ICP的情况下，查询过程如下：读取索引记录（不是完整的行记录）；判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。三、实践先创建一张表，并插入记录 CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT COMMENT “主键”, name varchar(32) COMMENT “姓名”, city varchar(32) COMMENT “城市”, age int(11) COMMENT “年龄”, primary key(id), key idx_name_city(name, city) )engine=InnoDB default charset=utf8; insert into user(name, city, age) values(“ZhaoDa”, “BeiJing”,… Continue reading MySQL索引下推

MYSQL前缀索引

这里主要介绍 MySQL 的前缀索引。从名字上来看，前缀索引就是指索引的前缀，当然这个索引的存储结构不能是 HASH，HASH 不支持前缀索引。先看下面这两行示例数据：你是中国人吗？是的是的是的是的是的是的是的是的是的是的确定是中国人？是的是的是的是的是的是的是的是的是的是的这两行数据有一个共同的特点就是前面几个字符不同，后面的所有字符内容都一样。那面对这样的数据，我们该如何建立索引呢？大致有以下 3 种方法：拿整个串的数据来做索引这种方法来的最简单直观，但是会造成索引空间极大的浪费。重复值太多，进而索引中无用数据太多，无论写入或者读取都产生极大资源消耗。将字符拆开，将一部分做索引把数据前面几个字符和剩余的部分字符分拆为两个字段 r1_prefix，r1_other，针对字段 r1_prefix 建立索引。如果排除掉表结构更改这块影响，那这种方法无疑是最好的。把前面 6 个字符截取出来的子串做一个索引能否不拆分字段，又能避免太多重复值的冗余？我们今天讨论一下前缀索引。前缀索引前缀索引就是基于原始索引字段，截取前面指定的字符个数或者字节数来做的索引。 MySQL 基本上大部分存储引擎都支持前缀索引，目前只有字符类型或者二进制类型的字段可以建立前缀索引。比如：CHAR/VARCHAR、TEXT/BLOB、BINARY/VARBINARY。字符类型基于前缀字符长度，f1(10) 指的前 10 个字符；二进制类型基于字节大小，f1(10) 指的是前 10 个字节长度； TEXT/BLOB 类型只支持前缀索引，不支持整个字段建索引。举个简单例子，表 t1 有两个字段，针对字段 r1 有两个索引，一个是基于字段 r1 的普通二级索引，另外一个是基于字段r1的前缀索引。 <localhost|mysql>show create table t1\G *************************** 1. row *************************** Table: t1 Create… Continue reading MYSQL前缀索引