了解MySQL中的驱动表

一、为什么要用小表驱动大表

1、驱动表的定义

当进行多表连接查询时, [驱动表] 的定义为:

1)指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]

2)未指定联接条件时,行数少的表为[驱动表](重要)

忠告:如果你搞不清楚该让谁做驱动表、谁 join 谁,请让 MySQL 运行时自行判断

既然“未指定联接条件时,行数少的表为[驱动表]”了,而且你也对自己写出的复杂的 Nested Loop Join 不太有把握(如下面的实例所示),就别指定谁 left/right join 谁了,请交给 MySQL优化器 运行时决定吧。

2、mysql关联查询的概念:

MySQL 表关联的算法是 Nest Loop Join(嵌套循环),是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。

例: user表10000条数据,class表20条数据

SELECT * FROM user u LEFT JOIN class c u.userid=c.userid

这样则需要用user表循环10000次才能查询出来,而如果用class表驱动user表则只需要循环20次就能查询出来

SELECT * FROM class c LEFT JOIN user u c.userid=u.userid

小结果集驱动大结果集

de.cel 在2012年总结说,不管是你,还是 MySQL,优化的目标是尽可能减少JOIN中Nested Loop的循环次数。

以此保证:永远用小结果集驱动大结果集(Important)!

相关算法参考:https://blog.csdn.net/caomiao2006/article/details/52205177

二、优化联表查询

优化第一步之:根据驱动表的字段排序

left join不变,干嘛要根据非驱动表的字段排序呢?我们前面说过“对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序!”的。

explain

SELECT mb.id……

FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid

WHERE 1=1

ORDER BY mb.id DESC

limit 0,10

也满足业务场景,做到了rows最小:

优化第二步:去除所有JOIN,让MySQL自行决定,explain第一张表就是驱动表,数据量比其它两张表都要小!

explain
SELECT mb.id…… 
FROM mb,mbei,u   
WHERE 
    mb.id=mbei.mb_id
    and mb.uid=u.user_id
order by mbei.apply_time desc
limit 0,10

立竿见影,驱动表一样是小表 mbei:

id select_type table   type    possible_keys      key          key_len  ref                rows    Extra
1  SIMPLE        mbei ALL      mb_id  (NULL)      (NULL)      (NULL)                         13388 Using filesort
1  SIMPLE        mb      eq_ref  PRIMARY,userid  PRIMARY 4            mbei.mb_id   1
1  SIMPLE        u         eq_ref  PRIMARY            PRIMARY 4            mb.uid           1  Using index

 

三、总结

1、不要过于相信你的运气!

2、不要相信你的开发环境里SQL的执行速度!

3、请拿起 explain 武器,如果你看到以下现象,请优化:

1)出现了Using temporary

2)rows过多,或者几乎是全表的记录数

3)key 是 (NULL)

4)possible_keys 出现过多(待选)索引.

推荐阅读:什么情况下会用到临时表

 

MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析

InnoDB是一个支持行锁的存储引擎,锁的类型有:共享锁(S)、排他锁(X)、意向共享(IS)、意向排他(IX)。为了提供更好的并发,InnoDB提供了非锁定读:不需要等待访问行上的锁释放,读取行的一个快照。该方法是通过InnoDB的一个特性:MVCC来实现的。

InnoDB有三种行锁的算法:

1,Record Lock:单个行记录上的锁。

2,Gap Lock:间隙锁,锁定一个范围,但不包括记录本身。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。

3,Next-Key Lock:1+2,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。

锁的是索引,并不是记录。
记录锁(Record Lock): 单个索引行记录上的锁
间隙锁(Gap Lock):一般是针对非唯一索引而言的.
后码锁(Next-Key Lock):记录锁和间隙锁的结合,对于InnoDB中,更新非唯一索引对应的记录,会加上Next-Key Lock。如果更新记录为空,就不能加记录锁,只能加间隙锁。

Next-Key Lock是行锁与间隙锁的组合,这样,当InnoDB扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务T1加了锁,其它事务是不能在这个间隙插入记录的。

https://blog.csdn.net/zhanghongzheng3213/article/details/51721903

http://blog.sina.com.cn/s/blog_a1e9c7910102vnrj.html

https://www.cnblogs.com/zhoujinyi/p/3435982.html

http://hedengcheng.com/?p=771

聚簇索引概念(Myisam与Innodb索引的区别)转推荐

myisam的主索引和次索引都指向物理行,下面来进行讲解

innodb的主键下存储该行的数据,此索引指向对主键的引用

myisam的索引存储图如下,可以看出,无论是id还是cat_id,下面都存储有存储物理地址的值。通过主键索引或者次索引来查询数据的时候,都是先查找到数据地址,然后再到物理位置上去寻找数据

innodb的索引存储图如下,我们会发现,主键索引下面直接存储有数据,而次索引下,存储的是主键的id(不同于MyISAM,存储的是内容数据的物理地址。通过主键查找数据的时候,就会很快查找到数据,但是通过次索引查找数据的时候,需要先查找到对应的主键id,然后才能查找到对应的数据。 Continue reading

查看InnoDB的磁盘空间利用率

这周阿里集团DBA内部分享时,支付宝的黄忠同学提了一个问题,关于InnoDB索引page 的利用率。

page利用率

主要是指btee里面每个page的使用被使用的空间大小。我们知道InnoDB默认一个page大小是16k。但实际使用情况不会总用满

我们定义为所有page的总使用字节除以总字节数。

在理论分析之前,我们要先弄个工具,查一下。

Continue reading

关于InnoDB表的page利用率和optimize table

上一篇我们介绍了ibd_used这个工具,我们用来量化看表数据文件的page使用率。这里用来说明optimize table这个命令的问题和优化。

实例准备

建一个这样的表

CREATE TABLE `tb` (

`seq_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`a` varchar(32) DEFAULT NULL,

`b` varchar(32) DEFAULT NULL,

`c` varchar(32) DEFAULT NULL,

`d` char(255) DEFAULT NULL,

Primary key (seq_id),
KEY a (a),

KEY bc (b,c),

KEY cb (c,b)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

执行语句为“insert into tb(a,b,c) values(randstr, randstr, randstr);” randstr是客户端程序生成的长度30字节的随机字符串。30个线程并发,每个线程插入1w条记录。

等待更新完成后(包括purge完成,从系统的vmstat上看无任何io),执行./ibd_used tb.ibd 0 100000000,可以从最后4行看到各个索引的page平均利用率如下图。

说明: 你会发现即使是主键索引,利用率也不一定很高。原因是什么?

Optimize table 效果

我们知道Optimize table是用来作表整理的, 执行一下 optimize table tb,再看ibd_used的结果。 Continue reading

关于 InnoDB 索引长度限制的 tips

有同学问到InnoDB的索引长度问题,简单说几个tips。

关于3072

大家经常碰到InnoDB单列索引长度不能超过767bytes,实际上联合索引还有一个限制是3072。

可以看到,由于每个字段占用255*3, 因此这个索引的大小是3825(255*3*5)>3072,报错。

为什么3072

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织,要求叶子节点上一个page至少要包含两条记录(否则就退化链表了)。 Continue reading