innodb count优化测试

　　对于索引优化真的是门课题，先来研究下最平常的问题，innodb引擎下 怎么让count(*)快一点。

　　首先需要清楚

　　innodb 默认是对主键建立聚簇索引,如果没有主键，那就是对具有唯一且非空值的索引来代替，如果也没有，innodb内部就会自己维护一个这样的索引。

聚簇索引存的是索引+数据,二级索引存的是对主键即聚簇索引的指向。

　　所以通过上面的猜想

　　1.表中聚簇索引如果有varchar,text的字段，如果存储数据比较多，聚簇索引就会非常的大，就会照成大量数据页分裂。(这里数据页是什么，我也理解的不是很深刻，请自行百度)

　　2.根据二级索引去count(*) 会快很多。

　　想到就干，开始实践

#只有基本的字段
create table a1(
    id int not null auto_increment,
    user_name varchar(20) not null,
    user_pwd char(32) not null,
    regtime datetime not null default CURRENT_TIMESTAMP,
    primary key (id)
)charset=utf8;


#有个varchar(255)这样存大数据的字段
create table a2(
    id int not null auto_increment,
    user_name varchar(20) not null,
    user_pwd char(32) not null,
    description varchar(255) not null comment '描述',
    img_url varchar(255) not null comment '头像地址',
    regtime datetime not null default CURRENT_TIMESTAMP,
    primary key (id)
)charset=utf8;　　

#建立存储过程写入50w条数据
DELIMITER //
create procedure insert_a(in t int)
begin
    set @num=1;
    if t=2 then
        set @description = '工作式的自我介绍的内容，应当包括本人姓名、供职的单位及其部门、担负的职务或从事的具体工作等三项。他们叫作工作式自我介绍内容的三要素，通常缺一不可。其中，第一项姓名，应当一口报出，不可有姓无名，或有名无姓。第二项供职的单位及其部门，有可能最好全部报出，具体工作部门有时也可以暂不报出。第三项担负的职务或从事的具体工作，有职务最好报出职务，职务较低或者无职务，则可报出所从事的具体工作。';
        set @img_url = 'https://gss1.bdstatic.com/5eN1dDebRNRTm2_p8IuM_a/res/img/0617jd.jpg';
    end if;

    while @num<=500000 do
        if t=1 then
            insert into a1 (user_name,user_pwd) values(concat('user',@num),'e10adc3949ba59abbe56e057f20f883ezc');
        else
            insert into a2 (user_name,user_pwd,description,img_url) values(concat('user',@num),'e10adc3949ba59abbe56e057f20f883ezc',@description,@img_url);
        end if;
        set @num=@num+1;
    end while;
end //

DELIMITER ;

CALL insert_a(1);
CALL insert_a(2);

　　准备工作都已经做好了

　　1.首先通过以下2条语句来测试下

1.count(*) , a2中description存入了比较大的数据
select sql_no_cache count(*) from a1;  #平均0.12 sec
select sql_no_cache count(*) from a2;  #平均0.65 sec

　　2.接下来给user_name加上普通索引

alter table a1 add index user_name (`user_name`);
alter table a2 add index user_name (`user_name`);

　　执行以下语句来验证

select sql_no_cache count(*) from a1 where user_name>'user0';
select sql_no_cache count(*) from a2 where user_name>'user0';

　　

　　通过测试，对a1的count(*)变慢了，但是a2的count(*)快了几倍。

　　以上都是自己猜想实验，也许中间是其他原因导致猜想测试不准确，欢迎高手指点。

　　参考：http://www.t086.com/article/5083