Mysql – 性能优化之子查询

记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的.

那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理.

当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划", 然后根据所得到的执行计划通过调用存储引擎接口来获取相应数据. 再对存储引擎返回的数据进行相关的处理, 并一Client端所要求的格式作为结果集, 返回给Client.

注 : 这里所说的统计数据, 是我们通过 Analyze table命令通知Mysql对表的相关数据作分析之后, 所获取到的一些数据统计量. 这些数据对Mysql优化器而言是非常重要的, 优化器所生成的执行计划的好坏, 主要是由这些统计数据所决定的.

1. 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table User(
  Id int not null PRIMARY key auto_increment ,
  NickName varchar(50) comment '用户昵称',
  Sex int comment '性别',
  Sign varchar(50) comment '用户签名',
  Birthday datetime comment '用户生日',
  CreateTime datetime comment '创建时间'
default charset=utf8 comment '用户表';
  
create table UserGroup(
  Id int not null PRIMARY key auto_increment ,
  UserId int not null comment 'user Id',
  GroupId int not null comment '用户组Id',
  CreateTime datetime comment '创建时间',
  -- key index_groupid(GroupId) using btree,
  key index_userid(groupid, UserId) using btree
default charset=utf8 comment '用户组表';

2. 准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
var conStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
using (IDbConnection conn = new MySqlConnection(conStr))
{ Stopwatch watch = new Stopwatch();
 var sql = string.Empty;
 var names = new string[] { "非""想""红""帝""德""看""梅""插""兔" };
 Random ran = new Random(); 
 var insertSql = @" insert into User(NickName,Sex,Sign, Birthday, CreateTime) values(@NickName,@Sex,@Sign, @Birthday, @CreateTime); 
 INSERT INTO usergroup (UserId, GroupId, CreateTime ) VALUES (LAST_INSERT_ID() , @GroupId, @CreateTime);";
 watch.Start();
 if (conn.State == ConnectionState.Closed)
 {
  conn.Open();
 }
 var tran = conn.BeginTransaction();
 for (int i = 0; i < 100000; i++)
 {
  var param = new { NickName = names[ran.Next(9)] + names[ran.Next(9)] + i, Sign = names[ran.Next(9)] + names[ran.Next(9)], CreateTime = DateTime.Now, Birthday = DateTime.Now.AddYears(ran.Next(10, 30)), Sex = i % 2, GroupId = ran.Next(1, 100) };
  conn.Execute(insertSql, param, tran);
 }
 tran.Commit();
 conn.Dispose();
 watch.Stop();
 Console.WriteLine(watch.ElapsedMilliseconds);
}

这里我插入了5000条数据, group分了99个组, 随机的.

3. 查询sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
explain
select user.id, user.nickname from usergroup 
left join user on usergroup.UserId = user.Id
where usergroup.groupid = 1 
order by usergroup.UserId desc
limit 100, 20;
 
 
explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid limit 100, 20) t
left join user on t.UserId = user.id ;
 
 
explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid ) t
left join user on t.UserId = user.id 
limit 100, 20;

第二句和第三句都使用到了子查询, 不同之处再与, 第二句是先得到20条数据, 然后以此来与user表关联的

4. 分析

100000条数据情况下 :

先看第一句

再看第二句

第三句

从上面三幅图看, 好像能看出点什么了.

首先看他们的 rows, 第二句最多, 加起来有1000多了, 另两句加起来都是996. 但是我想说的是, 这里并不是看rows的和是多少. 正确的方式是, 从id大的语句开始看, id相同的语句, 从上到下依次执行.

那先看第二句的id=2的语句和第一句的id=1的语句, 一模一样的. 他们都是从usergroup表中筛选数据, 并且能得到相同的结果集A.

看来他们都是基于相同的结果集去进行操作, 接下来就有区别了.

先看第一句, 再结果集A的基础上, 去左连接表user, 并筛选出最后的数据, 返回给客户端.

那第二句呢, 是在A的基础上, 再次筛选数据, 得到需要的数据, 然后拿这些数据, 去与user表左连接, 得到最终结果.

从上面来看, 执行计划中, 第二种执行计划, 更加高效.

 如果能够通过子查询, 大幅度缩小查询范围, 可以考虑使用子查询语句.

mysql性能优化之索引优化

  作为免费又高效的数据库,mysql基本是首选。良好的安全连接,自带查询解析、sql语句优化,使用读写锁(细化到行)、事物隔离和多版本并发控制提高并发,完备的事务日志记录,强大的存储引擎提供高效查询(表记录可达百万级),如果是InnoDB,还可在崩溃后进行完整的恢复,优点非常多。即使有这么多优点,仍依赖人去做点优化,看书后写个总结巩固下,有错请指正。

  完整的mysql优化需要很深的功底,大公司甚至有专门写mysql内核的,sql优化攻城狮,mysql服务器的优化,各种参数常量设定,查询语句优化,主从复制,软硬件升级,容灾备份,sql编程,需要的不是一星半点的知识与时间来掌握,作为一名像俺这样的菜鸟开发,强吃这么多消化不了也没意义:没地儿用啊,况且还有运维和dba,还不如把手头的业务写好,也就是写好点的sql,而且很多sql语句优化跟索引还是有很大关系的。

  首先,mysql的查询流程大致是:mysql客户端通过协议与mysql服务器建立连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,有一系列预处理,比如检查语句是否写正确了,然后是查询优化(比如是否使用索引扫描,如果是一个不可能的条件,则提前终止),生成查询计划,然后查询引擎启动,开始执行查询,从底层存储引擎调用API获取数据,最后返回给客户端。怎么存数据、怎么取数据,都与存储引擎有关。然后,mysql默认使用的BTREE索引,并且一个大方向是,无论怎么折腾sql,至少在目前来说,mysql最多只用到表中的一个索引。

  mysql通过存储引擎取数据,自然跟存储引擎有很大关系,不同的存储引擎索引也不一样,如MyISAM的全文索引,即便索引叫一个名字内部组织方式也不尽相同,最常用的当然就是InnoDB了(还有完全兼容mysql的MariaDB,它的默引擎是XtraDB,跟InnoDB很像),这里写的是InnoDB引擎。而索引的实现也跟存储引擎,按照实现方式分,InnoDB的索引目前只有两种:BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引,InnoDB的BTREE索引,实际是用B+树实现的,因为在查看表索引时,mysql一律打印BTREE,所以简称为B树索引。至于B树与B+树的区别,原谅的俺数据结构没好好学,也是需要补的地方。

  使用了BTREE索引,意味着所有的索引是按顺序排列存储的(升序),mysql就是这么干的,mysl中的BTREE索引抽象结构如下图(参考高性能mysql)。

  结构中,每一层节点均从左往右从小到大排列,key1 < key2 < … < keyN,对于小于key1或在[key1,key2)或其他的值的节点,在进入叶子节点查找,是一个范围分布,同时,同一层节点之间可直接访问,因为他们之间有指针指向联系(MyISAM的BTREE索引没有)。每次搜索是一个区间搜索,有的话就找到了,没有的话就是空。索引能加快访问速度,因为有了它无需全表扫描数据(不总是这样),根据查找的值,跟节点中的值比较,通常使用二分查找,对于排好序的数值来说,平均速度几乎是最快的。

  val指向了哪里,对于InnoDB,它指向的就是表数据,因为InnoDB的表数据本身就是索引文件,这是与MyISAM索引的显著区别,MyISAM的索引指向的是表数据的地址(val指向的是类似于0x7DFF..之类)。比如对于InnoDB一个主键索引来说,可能是这样

        

  InnoDB的索引节点val值直接指向表数据,即它的叶子节点就是表数据,它们连在一起,表记录行没有再单独放在其他地方,叶子节点(数据)之间可访问。

  前面在BTREE的抽象结构中,索引值的节点是放在页中的,这里有两个需注意的问题:

  1. 叶子页、页中的值(上上图),即所谓的页是啥,俺加了个节点注释,即这里的页最小可近似当做是单个节点。我们知道计算机的存储空间是一块一块的,通常一块用完了再用另一块,如果上一块只剩余5kb,但这里刚好要申请8kb的空间,就得在一个新的块上申请这个空间,然后以后的申请又接在这个8kb后面,只要这个块的空间足够,那么上一块的5kb通常就成了所谓的“碎片”,电脑用多了会有很多这样零散的碎片空间,因此有碎片整理。在mysql中,这里的页可理解为块存储空间,即索引的树节点是存放在页中的,每一页(称为逻辑页)有固定大小,InnoDB目前是16kb,一页用完了,当继续插入表生成新的索引节点时,就去新的页中存储这个节点,再有新的节点就继续放在这个新的页的节点后面。

  2. 页分裂问题,一页总要被存满,然后新开一页继续,这种行为被称作页分裂。何时开辟新的页,mysql规定了一个分裂因子,达到页存储空间的15/16则存到下一页。页分裂的存在可能极大影响性能维护索引的性能。通常提倡的是,设定一个无意义的整数自增索引,有利于索引存储

    

  如果非自增或不是整数索引,如非自增整数、类似MD5的字符串,以他们作为索引值时,因为待插入的下一条数据的值不一定比上一条大,甚至比当前页所有值都小,需要跑到前几页去比较而找到合适位置,InnoDB无法简单的把新行插入到上一行后面,而找到并插入索引后,可能导致该页达到分裂因子阀值,需要页分裂,进一步导致后面所有的索引页的分裂和排序,数据量小也许没什么问题,数据量大的话可能会浪费大量时间,产生许多碎片。

    

  主键总是唯一且非空,InnoDB自动对它建立了索引(primary key),对于非主键字段上建立的索引,又称辅助索引,索引排列也是顺序排列,只是它还附带一个本条记录的主键值的数据域,不是指向本数据行的指针,在使用辅助索引查找时,先找到对应这一列的索引值,再根据索引节点上的另一个数据域—主键值,来查找该行记录,即每次查找实际经过查找了两次。额外的数据域存储主键值的好处是,当页分裂发生时,无需修改数据域的值,因为即使页分裂,该行的主键值是不变的,而地址就变了。比如name字段的索引简示如下 

      

   包含一列的索引称为单列索引,多列的称为复合索引,因为BTREE索引是顺序排列的,所以比较适合范围查询,但是在复合索引中,还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

  比如有这样一张表

1
2
3
4
5
6
7
create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
  ) charset utf8 comment '员工记录表';

  添加三列的复合索引

1
alter table staffs add index idx_nap(name, age, pos);

  在BTREE索引的使用上,以下几种情况可以用到该索引或索引的一部分(使用explain简单查看使用情况):

  1. 全值匹配

  如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ,key字段显示使用了idx_nap索引

  2. 匹配最左列,对于复合索引来说,不总是匹配所有字段列,但是可以匹配索引中靠左的列

  如select * from staffs where name = 'July' and age = '23',key字段显示用到了索引,注意,key_len字段(表示本次语句使用的索引长度)数值比上一条小了,意思是它并未使用全部索引列(通常这个长度可估摸着用了哪些索引列,埋个坑),事实上只用到了name和age列

  再试试select * from staffs where name = 'July',它也用了索引,key_len值更小,实际只用到了索引中的name列

  3. 匹配列前缀,即一个索引中列的前一部分,主要用在模糊匹配,如select * fromstaffs where name like 'J%',explain信息的key字段表示使用了索引,但是mysql的B树索引不能非列前缀的模糊匹配,如select * from staffs where name like '%y' 或者 like '%u%',据说是由于底层存储引擎的API限制

  4. 匹配范围,如select * from staffs where name > 'Mary',但俺在测试时发现>可以,>=却不行,至少在字符串列上不行(测试mysql版本5.5.12),然而在时间类型(timestamp)上却可以,不测试下还真不能确定说就用到了索引==

  出于好奇测了下整型字段的索引(idx_cn(count, name),count为整型),发现整型受限制少很多,下面的都能用到索引,连前模糊匹配的都行

1
2
3
4
5
6
select from indexTest1 where count '10'
  select from indexTest1 where count >= '10'
  select from indexTest1 where count '10%'
  select from indexTest1 where count >= '10%'
  select from indexTest1 where count '%10%'
  select from indexTest1 where count >= '%10%'

  5. 精确匹配一列并范围匹配右侧相邻列,即前一列是固定值,后一列是范围值,它用了name与age两个列的索引(key_len推测)

  如select * from staffs where name = 'July' and age > 25

  6. 只访问索引的查询,比如staffs表的情况,索引建立在(name,age,pos)上面,前面一直是读取的全部列,如果我们用到了哪些列的索引,查询时也只查这些列的数据,就是只访问索引的查询,如

1
2
select name,age,pos from staffs where name 'July' and age = 25 and pos = 'dev'  
select name,age from staffs where name = July and age > 25

  第一句用到了全部索引列,第二句只用了索引前两列,select的字段就最多只能是这两列,这种查询情况的索引,mysql称为覆盖索引,就是索引包含(覆盖)了查询的全部字段。是不是用到了索引查询,在explain中需要看最后一个Extra列的信息,Using index表明使用了覆盖索引,同时Using where表明也使用了where过滤

  7. 前缀索引

  区别于列前缀(类似like 'J%'形式的模糊匹配)和最左列索引(顺序取索引中靠左的列的查询),它只取某列的一部分作为索引。通常在说InnoDB跟MyISAM的区别时,一个明显的区别是:MyISAM支持全文索引,而InnoDB不行,甚至对于text、blob这种超长的字符串或二进制数据时,MyISAM会取前多少个字符作为索引,InnoDb的前缀索引跟这个类似,某些列,一般是字符串类型,很长,全部作为索引大大增加存储空间,索引也需要维护,对于长字符串,又想作为索引列,一个可取的办法就是取前一部分(前缀),代表一整列作为索引串,问题是:如何确保这个前缀能代表或大致代表这一列?所以mysql中有个概念是索引的选择性,是指索引中不重复的值的数目(也称基数)与整个表该列记录总数(#T)的比值,比如一个列表(1,2,2,3),总数是4,不重复值数目为3,选择性为3/4,因此选择性范围是[1/#T, 1],这个值越大,表示列中不重复值越多,越适合作为前缀索引,唯一索引(UNIQUE KEY)的选择性是1。

  比如有一列a varchar(255),以它作前缀索引,比如以7个测试,逐个增加看看选择性值增长到那个数基本不变,就表示可以代表整列了,再结合这个长度的索引列是否存储数据太多,做个权衡,基本就行了。但如果这个选择性本来就小的可怜还是算了

1
select count(distinct left(a, 7))/count(*) as non_repeat from tab;

  定好一个前缀数目,如9,添加索引时可以这样

1
2
alter table tab add index idx_pn(name(9)) --单独前缀索引  
alter table tab add index idx_cpn(countname(9)) --复合前缀索引

  以上为常见的使用索引的方式,有这么些情况不能用或不能全用,有的就是上面情况的反例,以key(a, b, c)为例

  1. 跳过列,where a = 1 and c = 3,最多用到索引列a;where b = 2 and c = 3,一个也用不到,必须从最左列开始

  2. 前面是范围查询,where a = 1 and b > 2 and c = 3,最多用到 a, b两个索引列;

  3. 顺序颠倒,where c = 3 and b = 2 and a = 1,一个也用不到;

  4. 索引列上使用了表达式,如where substr(a, 1, 3) = 'hhh',where a = a + 1,表达式是一大忌讳,再简单mysql也不认。有时数据量不是大到严重影响速度时,一般可以先查出来,比如先查所有有订单记录的数据,再在程序中去筛选以'cp1001'开头的订单,而不是写sql过滤它;

  5. 模糊匹配时,尽量写 where a like 'J%',字符串放在左边,这样才可能用得到a列索引,甚至可能还用不到,当然这得看数据类型,最好测试一下。

  排序对索引的影响

  order by是经常用的语句,排序也遵循最左前缀列的原则,比如key(a, b),下面语句可以用到(测试为妙)

1
2
3
select from tab where a > 1 order by b
select from tab where a > 1 and b > '2015-12-01 00:00:00' order by b
select from tab order by a, b

  以下情况用不到

  1. 非最左列,select * from tab order by b;

  2. 不按索引列顺序来的,select * from tab where b > '2015-12-01 00:00:00' order by a;

  3. 多列排序,但列的顺序方向不一致,select * from tab a asc, b desc。

  聚簇索引与覆盖索引

  前面说到,mysql索引从结构上只有两类,BTREE与HASH,覆盖索引只是在查询时,要查询的列刚好与使用的索引列完全一致,mysql直接扫描索引,然后就可返回数据,大大提高效率,因为不需再去原表查询、过滤,这种形式下的索引称作覆盖索引,比如key(a,b),查询时select a,b from tab where a = 1 and b > 2,本质原因:BTREE索引存储了原表数据。

  聚簇索引也不是单独的索引,前面简要写到,BTREE索引会把数据放在索引中,即索引的叶子页中,包括主键,主键是跟表数据紧挨着放在一起的,因为表数据只有一份,一列键值要跟每一行数据都紧挨在一起,所以一张表只有一个聚簇索引,对于mysql来说,就是主键列,它是默认的。

  聚簇索引将表数据组织到了一起(参考前面主键索引简略图),插入时严重依赖主键顺序,最好是连续自增,否则面临频繁页分裂问题,移动许多数据。

  哈希索引

  简要说下,类似于数据结构中简单实现的HASH表(散列表)一样,当我们在mysql中用哈希索引时,也是对索引列计算一个散列值(类似md5、sha1、crc32),然后对这个散列值以顺序(默认升序)排列,同时记录该散列值对应数据表中某行的指针,当然这只是简略模拟图

      

  比如对姓名列建立hash索引,生成hash值按顺序排列,但是顺序排列的hash值并不对应表中记录,从地址指针可反应出来,而且,hash索引可能建立在两列或者更多列上,取得是多列数据后的hash值,它不存储表中数据。它先计算列数据的hash值,与索引中的hash值比较,找到了然后比对列数据是否相等,可能涉及其他列条件,然后返回数据。hash当然会有冲突,即碰撞,除非有很多冲突,一般hash索引效率很高,否则hash维护成本较高,因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点:

  1. hash索引只是hash值顺序排列,跟表数据没有关系,无法应用于order by;

  2. hash索引是对它的所有列计算哈希值,因此在查询时,必须带上所有列,比如有(a, b)哈希索引,查询时必须 where a = 1 and b = 2,少任何一个不行;

  3. hash索引只能用于比较查询 = 或 IN,其他范围查询无效,本质还是因不存储表数据;

  4. 一旦出现碰撞,hash索引必须遍历所有的hash值,将地址所指向数据一一比较,直到找到所有符合条件的行。

  填坑

  前面提到通过explain的key_len字段,可大致估计出用了哪些列,索引列的长度跟索引列的数据类型直接相关,一般,我们说int是4字节,bigint8字节,char是1字节,考虑到建表时要指定字符集,比如utf8,还跟选的字符集有关(==!),在utf8下边,一个char是3字节,但是知道这些仍不能说key_len就是将用到的索引列的数据类型代表字节数一加不就完啦?事实总有点区别,测试方法比较机械(以下基于mysql 5.5.2)

  建表,加索引,int型

1
2
3
4
5
6
7
8
--测试表
  create table keyLenTest1(
    id int primary key auto_increment,
    typeKey int default 0 ,
    add_time timestamp not null default current_timestamp
  ) charset utf8
  --添加索引
  alter table keyLenTest1 add index idx_k(typeKey);

  可知int型索引默认长度为5,在4字节基础上+1

  char型

1
2
--改为char型,1个字符
 alter table keyLenTest1 modify typeKey char(1);

1
2
--改为char型,2个字符
  alter table keyLenTest1 modify typeKey char(2);

  可知,char型初始是4字节(3+1 bytes),后续按照3字节递增

  varchar型

1
2
--改为varchar型,1个字符
  alter table keyLenTest1 modify typeKey varchar(1);

1
2
--改为varchar型,2个字符
  alter table keyLenTest1 modify typeKey varchar(2);

  可知,varchar型,1个字符时,key_len为6,以后以3字节递增

  所以,如果一个语句用到了int、char、varchar,key_len如何计算以及用了哪些索引列应该很清楚了。

  如果想了解的更详细点,explain各字段意义,索引的更多细节,除了explain,还有show profiles、慢查询日志等(没细看)。

MYSQL JOIN 的性能优化

单独从之前的文章里拿出来,方便以后查找。

 

1.显示(explicit) inner join VS 隐式(implicit) inner join

如:

select * fromtable a inner join table bon a.id = b.id;

VS

select a.*, b.*from table a, table bwhere a.id = b.id;

我在数据库中比较(10w数据)得之,它们用时几乎相同,第一个是显示的inner join,后一个是隐式的inner join。

参照:Explicit vs implicit SQL joins

2.left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join(或right join)时,应该清楚的知道以下几点:

(1). on与 where的执行顺序

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。如:

PASS

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.nameleft join D on D.id = C.idwhere C.status>1 and D.status=1;

Great

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.name and C.status>1left join D on D.id = C.id and D.status=1

从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。

(2).注意ON 子句和 WHERE 子句的不同

如作者举了一个列子:

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+4 rows in set (0.00 sec)
 
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+1 row in set (0.01 sec)

从上可知,第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

(3).尽量避免子查询,而用join

往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。如下:

PASS

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

Great

insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null;

这个可以参考mysql的exists与inner join 和 not exists与 left join 性能差别惊人

补充:MySQL STRAIGHT_JOIN 与 NATURAL JOIN的使用

长话短说:straight_join实现强制多表的载入顺序,从左到右,如:

...A straight_join B on A.name = B.name

straight_join完全等同于inner join 只不过,join语法是根据“哪个表的结果集小,就以哪个表为驱动表”来决定谁先载入的,而straight_join 会强制选择其左边的表先载入。

往往我们在分析mysql处理性能时,如(Explain),如果发现mysql在载入顺序不合理的情况下,可以使用这个语句,但往往mysql能够自动的分析并处理好。

MySQL延迟关联性能优化方法

 代码如下:

1
SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;

表的数据量大致有36w左右,该sql是一个非常典型的排序+分页查询:order by col limit N,OFFSET M , MySQL 执行此类sql时需要先扫描到N行,然后再去取 M行。对于此类大数据量的排序操作,取前面少数几行数据会很快,但是越靠后,sql的性能就会越差,因为N越大,MySQL 需要扫描不需要的数据然后在丢掉,这样耗费大量的时间。
【分析】针对limit 优化有很多种方式,
1 前端加缓存,减少落到库的查询操作
2 优化SQL
3 使用书签方式 ,记录上次查询最新/大的id值,向后追溯 M行记录。
4 使用Sphinx 搜索优化。
对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作,何谓"延迟关联" :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。【解决】根据延迟关联的思路,修改SQL 如下:优化前
复制代码代码如下:

1
root@xxx 12:33:48>explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =\'0\' AND end_time >=\'2014-05-29\' ORDER BY id asc LIMIT 149420 ,20;

+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+
| id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows   | Extra                       |
+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+
| 1  | SIMPLE      | relation    | range | ind_endtime   | ind_endtime | 9       | NULL | 349622 | Using where; Using filesort |
+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+
1 row in set (0.00 sec)

其执行时间:优化后:
复制代码代码如下:

1
SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id

复制代码代码如下:

1
root@xxx 12:33:43>explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;

+—-+————-+————-+——–+—————+———+———+——+——–+——-+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |
+—-+————-+————-+——–+—————+———+———+——+——–+——-+
| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |
| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |
| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |
+—-+————-+————-+——–+—————+———+———+——+——–+——-+
3 rows in set (0.36 sec)

MySql中常用的hint

对于经常使用Oracle的朋友可能知道,oracle的hint功能种类很多,对于优化sql语句提供了很多方法。同样,在MySQL里,也有类似的hint功能。下面介绍一些常用的。
强制索引 FORCE INDEX 
SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。
忽略索引 IGNORE INDEX 
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中,TABLE1表中FIELD1和FIELD2上的索引不被使用。 
关闭查询缓冲 SQL_NO_CACHE 
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
有一些SQL语句需要实时地查询数据,或者并不经常使用(可能一天就执行一两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找,每次都会执行它。
强制查询缓冲 SQL_CACHE
SELECT SQL_CALHE * FROM TABLE1;
如果在my.ini中的query_cache_type设成2,这样只有在使用了SQL_CACHE后,才使用查询缓冲。

优先操作 HIGH_PRIORITY
HIGH_PRIORITY可以使用在select和insert操作中,让MYSQL知道,这个操作优先进行。
SELECT HIGH_PRIORITY * FROM TABLE1;
滞后操作 LOW_PRIORITY
LOW_PRIORITY可以使用在insert和update操作中,让mysql知道,这个操作滞后。
update LOW_PRIORITY table1 set field1= where field1= …
延时插入 INSERT DELAYED
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。当mysql有空余时,再插入。另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多。坏处是,不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。

强制连接顺序 STRAIGHT_JOIN
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。
强制使用临时表 SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE … 
当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。

MySQL exists的用法介绍

有一个查询如下:

1
2
3
4
5
SELECT c.CustomerId, CompanyName  
FROM Customers c  
WHERE EXISTS(  
SELECT OrderID FROM Orders o  
WHERE o.CustomerID = cu.CustomerID)

这里面的EXISTS是如何运作呢?子查询返回的是OrderId字段,可是外面的查询要找的是CustomerID和CompanyName字段,这两个字段肯定不在OrderID里面啊,这是如何匹配的呢?

EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。

EXISTS 指定一个子查询,检测行的存在。语法:EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。结果类型为 Boolean,如果子查询包含行,则返回 TRUE。

在子查询中使用 NULL 仍然返回结果集

这个例子在子查询中指定 NULL,并返回结果集,通过使用 EXISTS 仍取值为 TRUE。

1
2
3
4
SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC

比较使用 EXISTS 和 IN 的查询

这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。

1
2
3
4
5
6
7
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')

1
2
3
4
5
6
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')

比较使用 EXISTS 和 = ANY 的查询

本示例显示查找与出版商住在同一城市中的作者的两种查询方法:第一种方法使用 = ANY,第二种方法使用 EXISTS。注意这两种方法返回相同的信息。

1
2
3
4
5
6
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

1
2
3
4
5
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
(SELECT city
FROM publishers)

比较使用 EXISTS 和 IN 的查询

本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:

1
2
3
4
5
6
7
SELECT title
FROM titles
WHERE EXISTS
(SELECT *
FROM publishers
WHERE pub_id = titles.pub_id
AND city LIKE 'B%')
1
2
3
4
5
6
SELECT title
FROM titles
WHERE pub_id IN
(SELECT pub_id
FROM publishers
WHERE city LIKE 'B%')

使用 NOT EXISTS

NOT EXISTS 的作用与 EXISTS 正相反。如果子查询没有返回行,则满足 NOT EXISTS 中的 WHERE 子句。本示例查找不出版商业书籍的出版商的名称:

1
2
3
4
5
6
7
8
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
ORDER BY pub_name

又比如以下 SQL 语句:

1
2
3
4
5
6
7
select distinct 姓名 from xs
where not exists (
select from kc
where not exists (
select from xs_kc
where 学号=xs.学号 and 课程号=kc.课程号
)

把最外层的查询xs里的数据一行一行的做里层的子查询。

中间的 exists 语句只做出对上一层的返回 true 或 false,因为查询的条件都在 where 学号=xs.学号 and 课程号=kc.课程号这句话里。每一个 exists 都会有一行值。它只是告诉一层,最外层的查询条件在这里成立或都不成立,返回的时候值也一样回返回上去。直到最高层的时候如果是 true(真)就返回到结果集。为 false(假)丢弃。

1
2
3
where not exists
select from xs_kc
where 学号=xs.学号 and 课程号=kc.课程号

这个 exists 就是告诉上一层,这一行语句在我这里不成立。因为他不是最高层,所以还要继续向上返回。

select distinct 姓名 from xs where not exists (这里的 exists 语句收到上一个为 false 的值。他在判断一下,结果就是为 true(成立),由于是最高层所以就会把这行的结果(这里指的是查询条件)返回到结果集。

几个重要的点:

最里层要用到的醒询条件的表比如:xs.学号、kc.课程号等都要在前面的时候说明一下select * from kc,select distinct 姓名 from xs

不要在太注意中间的exists语句.

把exists和not exists嵌套时的返回值弄明白

mysql 重复记录只选取最后一条

// sid相同,create_time不同,取create_time最后一条

1. 使用 NOT EXISTS参数

1
SELECT id, sid FROM table_name a WHERE NOT EXISTS (SELECT FROM table_name where a.sid = sid AND a.create_time < create_time)

2. 使用子查询

1
SELECT id, sid FROM table_name WHERE create_time IN (SELECT max(create_time) FROM table_name group by sid)

Mysql Join语法解析与性能分析

一.Join语法概述

join 用于多表中字段之间的联系,语法如下:

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

table1:左表;table2:右表。

JOIN 按照功能大致分为如下三类:

INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。

LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。

RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。

注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.

接下来给出一个列子用于解释下面几种分类。如下两个表(A,B)

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
+----+-----------+-------------+
| id | name       | name             |
+----+-----------+-------------+
|  1 | Pirate       | Rutabaga      |
|  2 | Monkey    | Pirate            |
|  3 | Ninja         | Darth Vader |
|  4 | Spaghetti  | Ninja             |
+----+-----------+-------------+4 rows in set (0.00 sec)

二.Inner join

内连接,也叫等值连接,inner join产生同时符合A和B的一组数据。

mysql> select * from A inner join B on A.name = B.name;
+----+--------+----+--------+
| id | name   | id | name   |
+----+--------+----+--------+
|  1 | Pirate |  2 | Pirate |
|  3 | Ninja  |  4 | Ninja  |
+----+--------+----+--------+

三.Left join

mysql> select * from A left join B on A.name = B.name;
#或者:select * from A left outer join B on A.name = B.name;

+----+-----------+------+--------+
| id | name      | id   | name   |
+----+-----------+------+--------+
|  1 | Pirate    |    2 | Pirate |
|  2 | Monkey    | NULL | NULL   |
|  3 | Ninja     |    4 | Ninja  |
|  4 | Spaghetti | NULL | NULL   |
+----+-----------+------+--------+4 rows in set (0.00 sec)

left join,(或left outer join:在Mysql中两者等价,推荐使用left join.)左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

如果想只从左表(A)中产生一套记录,但不包含右表(B)的记录,可以通过设置where语句来执行,如下:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
+----+-----------+------+------+
| id | name      | id   | name |
+----+-----------+------+------+
|  2 | Monkey    | NULL | NULL |
|  4 | Spaghetti | NULL | NULL |
+----+-----------+------+------+2 rows in set (0.00 sec)

同理,还可以模拟inner join. 如下:

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
+----+--------+------+--------+
| id | name   | id   | name   |
+----+--------+------+--------+
|  1 | Pirate |    2 | Pirate |
|  3 | Ninja  |    4 | Ninja  |
+----+--------+------+--------+2 rows in set (0.00 sec)

求差集:

根据上面的例子可以求差集,如下:

SELECT * FROM A LEFT JOIN B ON A.name = B.nameWHERE B.id IS NULLunionSELECT * FROM A right JOIN B ON A.name = B.nameWHERE A.id IS NULL;
# 结果
    +------+-----------+------+-------------+| id   | name      | id   | name        |
+------+-----------+------+-------------+|    2 | Monkey    | NULL | NULL        |
|    4 | Spaghetti | NULL | NULL        |
| NULL | NULL      |    1 | Rutabaga    |
| NULL | NULL      |    3 | Darth Vader |
+------+-----------+------+-------------+

四.Right join

mysql> select * from A right join B on A.name = B.name;
+------+--------+----+-------------+
| id   | name   | id | name        |
+------+--------+----+-------------+
| NULL | NULL   |  1 | Rutabaga    |
|    1 | Pirate |  2 | Pirate      |
| NULL | NULL   |  3 | Darth Vader |
|    3 | Ninja  |  4 | Ninja       |
+------+--------+----+-------------+4 rows in set (0.00 sec)

同left join。

五.Cross join

cross join:交叉连接,得到的结果是两个表的乘积,即笛卡尔积

笛卡尔(Descartes)乘积又叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。

mysql> select * from A cross join B;
+----+-----------+----+-------------+
| id | name      | id | name        |
+----+-----------+----+-------------+
|  1 | Pirate    |  1 | Rutabaga    |
|  2 | Monkey    |  1 | Rutabaga    |
|  3 | Ninja     |  1 | Rutabaga    |
|  4 | Spaghetti |  1 | Rutabaga    |
|  1 | Pirate    |  2 | Pirate      |
|  2 | Monkey    |  2 | Pirate      |
|  3 | Ninja     |  2 | Pirate      |
|  4 | Spaghetti |  2 | Pirate      |
|  1 | Pirate    |  3 | Darth Vader |
|  2 | Monkey    |  3 | Darth Vader |
|  3 | Ninja     |  3 | Darth Vader |
|  4 | Spaghetti |  3 | Darth Vader |
|  1 | Pirate    |  4 | Ninja       |
|  2 | Monkey    |  4 | Ninja       |
|  3 | Ninja     |  4 | Ninja       |
|  4 | Spaghetti |  4 | Ninja       |
+----+-----------+----+-------------+16 rows in set (0.00 sec)

#再执行:mysql> select * from A inner join B; 试一试#在执行mysql> select * from A cross join B on A.name = B.name; 试一试

实际上,在 MySQL 中(仅限于 MySQL) CROSS JOIN 与 INNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。
INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字,因此下面的 SQL 效果是一样的:

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2

六.Full join

mysql> select * from A left join B on B.name = A.name 
    -> union 
    -> select * from A right join B on B.name = A.name;
+------+-----------+------+-------------+
| id   | name      | id   | name        |
+------+-----------+------+-------------+
|    1 | Pirate    |    2 | Pirate      |
|    2 | Monkey    | NULL | NULL        |
|    3 | Ninja     |    4 | Ninja       |
|    4 | Spaghetti | NULL | NULL        |
| NULL | NULL      |    1 | Rutabaga    |
| NULL | NULL      |    3 | Darth Vader |
+------+-----------+------+-------------+6 rows in set (0.00 sec)

全连接产生的所有记录(双方匹配记录)在表A和表B。如果没有匹配,则对面将包含null。

七.性能优化

1.显示(explicit) inner join VS 隐式(implicit) inner join

如:

select * fromtable a inner join table bon a.id = b.id;

VS

select a.*, b.*from table a, table bwhere a.id = b.id;

我在数据库中比较(10w数据)得之,它们用时几乎相同,第一个是显示的inner join,后一个是隐式的inner join。

参照:Explicit vs implicit SQL joins

2.left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join(或right join)时,应该清楚的知道以下几点:

(1). on与 where的执行顺序

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。如:

PASS

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.nameleft join D on D.id = C.idwhere C.status>1 and D.status=1;

Great

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.name and C.status>1left join D on D.id = C.id and D.status=1

从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。

(2).注意ON 子句和 WHERE 子句的不同

如作者举了一个列子:

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+4 rows in set (0.00 sec)
 
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+1 row in set (0.01 sec)

从上可知,第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

(3).尽量避免子查询,而用join

往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。如下:

PASS

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

Great

insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null;

这个可以参考mysql的exists与inner join 和 not exists与 left join 性能差别惊人

补充:MySQL STRAIGHT_JOIN 与 NATURAL JOIN的使用

长话短说:straight_join实现强制多表的载入顺序,从左到右,如:

...A straight_join B on A.name = B.name

straight_join完全等同于inner join 只不过,join语法是根据“哪个表的结果集小,就以哪个表为驱动表”来决定谁先载入的,而straight_join 会强制选择其左边的表先载入。

往往我们在分析mysql处理性能时,如(Explain),如果发现mysql在载入顺序不合理的情况下,可以使用这个语句,但往往mysql能够自动的分析并处理好。

更多内容参考:MySQL STRAIGHT_JOIN 与 NATURAL JOIN
MySQL优化的奇技淫巧之STRAIGHT_JOIN

八.参考:

A Visual Explanation of SQL Joins

五种提高 SQL 性能的方法

关于 MySQL LEFT JOIN 你可能需要了解的三点

mysql的exists与inner join 和 not exists与 left join 性能差别惊人

由于数据量越来越大,在实践中让我发现mysql的exists与inner join 和 not exists与 left join 性能差别惊人。

我们一般在做数据插入时,想插入不重复的数据,或者盘点数据在一个表,另一个表否有存在相同的数据会用not exists和exists,例如:

1
insert into t1(a1) select b1 from t2 where not exists(select from t1 where t1.id = t2.r_id);

如果t1的数据量很大时,性能会非常慢。经过实践,用以下方法能提高很多。

1
2
3
4
insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null;

1
select from t1 where exists(select from t2 where t1.id=t2.r_id);

 替换为:

1
2
select t1.* from t1   
inner join (select distinct r_id from t2) t2 on t1.id= t2.r_id

这是实践的得出的结果。不知否有其他更好的方法,或则这个只是特例而已。 

MySQL延迟关联性能优化方法

【背景】

  某业务数据库load 报警异常,cpu usr 达到30-40 ,居高不下。使用工具查看数据库正在执行的sql ,排在前面的大部分是:

复制代码 代码如下:

1
2
3
4
5
6
7
SELECT id, cu_id, name, info, biz_type, gmt_create, 
gmt_modified,start_time, end_time, market_type, 
back_leaf_category,item_status,picuture_url 
FROM relation where biz_type ='0' 
AND end_time >='2014-05-29' 
ORDER BY id asc 
LIMIT 149420 ,20;

表的数据量大致有36w左右,该sql是一个非常典型的排序+分页查询:order by col limit N,OFFSET M , MySQL 执行此类sql时需要先扫描到N行,然后再去取 M行。对于此类大数据量的排序操作,取前面少数几行数据会很快,但是越靠后,sql的性能就会越差,因为N越大,MySQL 需要扫描不需要的数据然后在丢掉,这样耗费大量的时间。

【分析】

针对limit 优化有很多种方式,

1 前端加缓存,减少落到库的查询操作

2 优化SQL

3 使用书签方式 ,记录上次查询最新/大的id值,向后追溯 M行记录。

4 使用Sphinx 搜索优化。

对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作,何谓"延迟关联" :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

【解决】

根据延迟关联的思路,修改SQL 如下:

优化前

1
explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =\'0\' AND end_time >=\'2014-05-29\' ORDER BY id asc LIMIT 149420 ,20;

+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+

| id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows   | Extra                       |

+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+

| 1  | SIMPLE      | relation    | range | ind_endtime   | ind_endtime | 9       | NULL | 349622 | Using where; Using filesort |

+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+

1 row in set (0.00 sec)

其执行时间:

优化后:

1
SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id
1
explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;

+—-+————-+————-+——–+—————+———+———+——+——–+——-+

| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |

+—-+————-+————-+——–+—————+———+———+——+——–+——-+

| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |

| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |

| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |

+—-+————-+————-+——–+—————+———+———+——+——–+——-+

3 rows in set (0.36 sec)

执行时间:

优化后 执行时间 为原来的1/3 。