mysql索引 实战_mysql索引 - 实战篇

索引主要用于快速的检索数据，对于一万条以上的数据不使用索引查找是灾难性的。从不同的维度上有不同的索引定义：如B-tree索引，hash索引，位图索引，链接索引，聚集索引，非聚集索引，覆盖索引....等等。本文主要讨论mysql innodb和mysian 下的B-Tree索引，第一部分主要讨论常用SQL语句中索引的使用，包括普通的条件查询，count,order by,group by等。

一、准备工作。

首先创建一张测试表，建表语句如下：

1 create table buyer_seller_detail( //创建买家卖家浏览表

2 id int not null primary key auto_increment, //自增id

3 uid int not null, //买家id

4 seller_id int not null, //卖家id

5 pv int not null, //浏览量

6 fee int not null, //成交金额

7 pg_time int not null, //停留时间

8 pg_type int not null)engine=innodb default charset=gbk; //页面类型

为了方便测试，创建一个存储过程插入数据：

1 dilimiter $ //由于默认的语句结束符是;，写存储过程时应该将其改为其他的符号。

2 create procedure insertdata(in bs_num int,in bs_data_num int)

3 begin

4 declare sid int;

5 declare uid int;

6 declare pt int;

7 declare i int default 0;

8 declare j int;

9 declare k int;

10 declare count int default 0;

11 while i

12 set uid=rand()*10000000;

13 set sid=rand()*1000000;

14 set pt=mod((rand()*100),3);

15 set j = mod(bs_data_num,rand()*10);

16 set k = 0;

17 while k <= j do

18 insert into buyer_seller_detail(uid,seller_id,pv,fee,pg_time,pg_type) values(uid,sid,rand()*10000,rand()*1000,rand()*1000,pt);

19 set k=k+1;

20 set count=count+1;

21 if mod(count,1000)=0 then commit;

22 end if;

23 end while;

24 set i=i+1;

25 end while;

26 end;

创建数据，索引

1 delimiter ;

2 set autocommit=0;

3 call insertdata(1000000,100);

4 set autocommit=1;

5 alter table buyer_seller_detail add index uid_sid_pt(uid,seller_id,pg_type);

6 alter table buyer_seller_detail add index pt(pg_type);

数据准备好之后，就开始测试我们的SQL。

二、使用索引的查询

使用索引查询之前，先介绍一下explain命令，这个命令的格式为explain [extended]  select ....，使用了extended之后，可以使用show warnings查看优化信息。

执行可以看到以下信息：

1 explain select id from buyer_seller_detail where id=1

其中比较有用的就是type，从这个参数可以看出查询是否走了索引，性能由高到低为效率从高到低const、eq_reg、ref、range、index和all，通常情况下辅助索引的匹配通常是ref类型。

key表示mysql实际使用的索引，如果没有使用，则为Null。

key_len表示使用的索引的长度。

rows表示选出来的行数。

extra是一个额外的参数，通常可以用来观察order by ,group by等语句的执行。

打开profiling开关，用于观察sql的执行情况：

1 set profiling=1;

OK，下面开始我们的SQL索引实战。

1.全列匹配

1 explain select * from buyer_seller_detail where uid=29034508 and seller_id=4636 and pg_type=1;

从图中我们可以看出查询使用了索引type=ref，key=uid_sid_pt,key_len=12，最后一个参数说明了该查询使用了全部的索引字段。

2.最左前缀

看如下的SQL

explain select * from buyer_seller_detail where uid=8790 and seller_id=80987

explain select * from buyer_seller_detail where uid=8790 and pg_type=56983

explain select * from buyer_seller_detail where seller_id=23556 and pg_type=45634