提高索引的检索效率

来源：互联网发布：淘宝店铺怎么营销编辑：程序博客网时间：2024/05/18 02:28

创建测试表

create table mytab(

id int(10) unsigned not nullauto_increment,

c1 int(11) not null default 0,

c2 int(10) unsigned default null,

c5 int(10) unsigned not null default 0,

c3 timestamp not null defaultcurrent_timestamp on update current_timestamp,

c4 varchar(200) not null default '',

primary key(id),

key idx_c1(c1),

key key_c2(c2)

)engine=innodb auto_increment=2686347;

插入数据

for ((i=1;i<=100000;i++));do `mysql wwb-e "insert into mytab(c1,c2,c5,c4)values($i,floor($i+rand()*$i),$i,'user1'))"`;done

可以直接从索引返回记录避免回表

>desc select c1 from mytabwhere c1=1234 limit 1;

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.02 sec)

对无索引的列扫描则需要回表

root@localhost:mysql.sock 04:42:56 [wwb]>desc select c1 from my_tabwhere c5=1234 limit 1;

+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

提高聚合函数效率

>select min(c2),max(c2) frommytab;

+---------+---------+

| min(c2) | max(c2) |

+---------+---------+

| 1 | 199762 |

+---------+---------+

1 row in set (0.00 sec)

没有索引需要走全表扫描

root@localhost:mysql.sock 04:46:18 [wwb]>desc select min(c5),max(c5)from mytab;

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

1 row in set (0.00 sec)

有点慢

>select min(c5),max(c5) from mytab;

+---------+---------+

| min(c5) | max(c5) |

+---------+---------+

| 1 | 100000 |

+---------+---------+

1 row in set (0.03 sec)

求平均值扫描索引即可无需全表扫描

>desc select avg(c1) frommytab;

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.00 sec)

>select min(c5),max(c5) from mytab;

+---------+---------+

| min(c5) | max(c5) |

+---------+---------+

| 1 | 100000 |

+---------+---------+

1 row in set (0.03 sec)

求平均值无论怎么样总是全表扫描

>desc select avg(c5) frommytab;

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

1 row in set (0.00 sec)

>select avg(c5) from mytab;

+------------+

| avg(c5) |

+------------+

| 50000.5000 |

+------------+

1 row inset (0.03 sec)

3.提高排序效率

>desc select c5 from mytabwhere c5>100 order by c5 limit 10;

+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+

+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+

+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+

1 row in set (0.00 sec)

>desc select c5 from mytab where c1>100 order by c1 limit 10;

+----+-------------+-------+-------+---------------+--------+---------+------+-------+-----------------------+

+----+-------------+-------+-------+---------------+--------+---------+------+-------+-----------------------+

+----+-------------+-------+-------+---------------+--------+---------+------+-------+-----------------------+

1 row in set (0.00 sec)

减少多表关联时扫描行数

>desc select c1 from mytabt1 left join mytab t2 using(c1);

+----+-------------+-------+-------+---------------+--------+---------+-----------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+-----------+--------+-------------+

| 1 |SIMPLE | t2 | ref | idx_c1 | idx_c1 | 4 | wwb.t1.c1 | 1 | Using index |

+----+-------------+-------+-------+---------------+--------+---------+-----------+--------+-------------+

2 rows in set (0.00 sec)

>desc select c5 from mytabt1 left join mytab t2 using(c5);

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+

字段类型定义为not null 索引的结构也会比较小

>desc select count(c1) frommytab;

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

1 row in set (0.00 sec)

root@localhost:mysql.sock 06:27:04 [wwb]>desc select count(c2) frommytab;

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+

1 row in set (0.00 sec)

直接impossible 非常快

>desc select count(c2) from mytab where c2 is null;

+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+

1 row in set (0.01 sec)

>desc select count(c1) frommytab where c1 is null;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

1 row in set (0.01 sec)

0 0