性别列上真的不需要建索引吗

性别列上真的不需要建索引吗，我们假设有100w数据里只有10个女生，要找到这10个女生，看看有索引和没索引的差别。

建表

use test;
create table people(`id` int(11) auto_increment, `gender` char(1), primary key(`id`));

建一个用于填充数据的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
delimiter // 

CREATE PROCEDURE filldata()
begin
    declare v int default 0;

    while v < 50000
    do
        insert into people(gender)  values ('M');
        set v = v + 1;
    end while;

    insert into people(gender)  values ('F');

    while v < 100000
    do
        insert into people(gender)  values ('M');
        set v = v + 1;
    end while;

end
//

delimiter ;

填充数据，执行10次这个存储过程,大概每10w男生里有一个女生。

1
2
3
4
5
6
7
8
9
10
call filldata();
call filldata();
call filldata();
call filldata();
call filldata();
call filldata();
call filldata();
call filldata();
call filldata();
call filldata();

确认数据

1
2
3
4
5
6
7
mysql> select count(*) from people;
+----------+
| count(*) |
+----------+
|  1000010 |
+----------+
1 row in set (0.15 sec)

找出所有女生, 花费0.23秒

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from people where gender = 'F' limit 10;
+--------+--------+
| id     | gender |
+--------+--------+
|  50001 | F      |
| 150002 | F      |
| 250003 | F      |
| 350004 | F      |
| 450005 | F      |
| 550006 | F      |
| 650007 | F      |
| 750008 | F      |
| 850009 | F      |
| 950010 | F      |
+--------+--------+
10 rows in set (0.23 sec)

在性别上建个索引再找出所有女生，花费0秒

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> create index idx_people_gender on people(gender);
Query OK, 0 rows affected (2.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from people where gender = 'F' limit 10;
+--------+--------+
| id     | gender |
+--------+--------+
|  50001 | F      |
| 150002 | F      |
| 250003 | F      |
| 350004 | F      |
| 450005 | F      |
| 550006 | F      |
| 650007 | F      |
| 750008 | F      |
| 850009 | F      |
| 950010 | F      |
+--------+--------+
10 rows in set (0.00 sec)

那这种情况下还是有索引查询快。