不忘初心

少年,专心练剑吧

使用覆盖索引优化SQL语句

发表于 更新于 分类于

某同学写了以下的查询sql，通过每次对limit的offset进行增大，取到不同批数据做处理，比如，第一次是limit 0,100，第二次是limit100,100，现在该同学发现，随着offset的增大，sql变得越来越慢，这个时候让你进行优化，你会怎么做呢？ 

1
2
3
4
5
6
create table 'account'{
    'id' bigint(20) not null auto_increment,
    'account' varchar(100) not null,
    'balance' decimal(10,0) not null,
    primary key('id')
}engine=innoDB;
1
select * from account limit ?,?

优化方案：

  • id >= 的形式

    1
    select * from account where id >= (select id from account limit 80000,1) limit 100

  • 利用join

    1
    select * from account a join(select id from account limit 80000,100) b on a.id = b.id;

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子结点存放的数据为（primary key1, primary key2, …, key1, key2, …）。

例如，下列语句都可仅使用一次辅助联合索引来完成查询：

1
select key2 from table where key1=xxx;

覆盖索引的另一个好处是对某些统计问题而言的。

1
select count(*) from buy_log;

InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于bug_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择为userid索引。

聚集索引(clustered index)就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样， 每个数据页都通过一个双向链表来进行链接。

辅助索引(Secondary Index，也称非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应
行数据的聚集索引键。图5-15显示了InnoDB存储引擎中辅助索引与聚集索引的关系。.