索引覆盖的概念

索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回航到磁盘再找数据,这种查询速度非常快,称之为索引覆盖

理想的索引

  • 查询频繁
  • 区分度高
  • 长度小
  • 尽量覆盖常用的查询字段

索引长度对索引效果的影响

  索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。
针对列中的值,从左到右截取部分来建索引

  1. 截的越短,重复度越高区分度越小,索引效果越不好。
  2. 截的越长,重复度月底区分度越高,索引效果越好,但带来的影响也越大(增删改变慢,并间接影响查询速度)

我们需要在 区分度 和 索引长度 两者上取得一个平衡。

使用常用方式测试索引长度

如下截取不同长度,测试却分度:

select count(distinct left(`word`,1)) / count(*) from tableName;
select count(distinct left(`word`,2)) / count(*) from tableName;

其中word为我们要建立索引的列。

指定长度增加索引

alter table tableName add index title(title(6));

对于左前缀不易区分的列

建立索引的技巧,可以使用如下两个办法来区分。

字符串颠倒顺序存储

如存储如下:

http://www.zixue.it
http://www.webfad.com

把列内容倒过来存储,并建立索引

如下存入数据库内容

ti.euxiz.www//:ptth
moc.dafbew.www//:ptth

这样处理的话左前缀区分度大。

伪哈希效果

具体操作如下:

create table hash_url(
id mediumint not null primary key auto_increment,
url varchar(30) not null default ''
)engine innodb charset utf8;

insert into hash_url values (null,'http://www.baidu.com'),(NULL,'http://www.webfsd.com');

alter table hash_url add crc_url int unsigned not null default 0 comment '使用crc32()函数处理过的url列对应值';

update hash_url set crc_url = crc32(url);

-- 增加索引,测试效果
alter table hash_url add key url(url(16));
alter table hash_url add key crc_url(crc_url);

---- 用crc32函数来构造为哈希列,转成整型后,降低索引长度,从而提高查询效率

explain select * from hash_url where url='http://www.baidu.com'\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: hash_url
             type: ref
    possible_keys: url
              key: url
          key_len: 50
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

explain select * from hash_url where crc_url=crc32('http://www.baidu.com')\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: hash_url
             type: ref
    possible_keys: crc_url
              key: crc_url
          key_len: 4
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

翻页效果优化

大数据量下的分页效果,在MySQL中我们的通常做法是使用limit进行查询。

  1. 比如:每页显示page_number条,当前是N页,那么查询时的limit条件为

    select .. from tableName where .. limit (N-1)*page_number,N;
    

    我们使用limit查询到越后面的数据时候明显发现会耗时较多,那是因为limit offset 并不是先跳过再查询,而是先查询再过。

  2. 从业务上解决办法是不允许翻过100页
    比如以百度为例,一般翻页到70页左右。google翻页一般能翻页页左右。

  3. 不使用offset,用条件进行查询
    使用offset 如下:

    select id,c1 from t3  limit 500000,2;
    +----+----+
    | id | c1 |
    +----+----+
    | 500001  | 2  |
    | 500002  | 2  |
    +----+----+
    

    不使用offset 如下:

    select id,c1 from t3 id>500000 limit 2;
    +----+----+
    | id | c2 |
    +----+----+
    | 500001  | 2  |
    | 500002  | 2  |
    +----+----+
    

    上述条件查询的前提是没有对数据进行过物理删除,而是使用逻辑删除。

  4. 非要物理删除,还要用offset精确查找,还不限制用户分页,怎么办?
    分析:优化思路是不查,少查,查索引,少取。
    我们现在必须要查,则只查索引,不差数据得到id主键,再通过id主键去查询具体的条目,这种技巧就是延迟索引

select id,c1 from t3 inner join (select id from t3 limit 500000,2) as tmp on t3.id = tmp.id;

最后编辑: 于 3年前

标签

评论列表(0)

    暂无评论