BLOB或TEXT字段使用散列值和前缀索引优化提高查询速度

1.创建表，存储引擎为myisam，对大文本字段blob使用MD5函数建立一个散列值

create table t2(id varchar(60), content blob, hash_value varchar(40))engine=myisam default charset=utf8;

 

2.插入数据

insert into t2 values(1, repeat('world1', 20), md5(content));

insert into t2 values(2, repeat('world2', 40), md5(content));

insert into t2 values(3, repeat('world3', 40), md5(content));

insert into t2 values(4, repeat('world4', 70), md5(content));

insert into t2 values(5, repeat('world5', 100), md5(content));

 

3.使用散列值查询，只用于精确匹配

select * from t2 where hash_value=md5(repeat('world4', 70));

使用散列值查询比使用blob文本字段查询速度更快，因为使用散列值匹配的长度要短得多。

 

4.若要进行模糊查询，可以对blob字段的前n列做前缀索引

create index idx_blob on t2(content(150));

在t2表的字段content字段的前150字节做前缀索引

 

5.使用explain或desc查看执行计划，是否使用了索引

mysql> explain select * from t2 where content like 'world4%';

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

| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |

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

|  1 | SIMPLE      | t2    | range | idx_blob      | idx_blob | 153     | NULL |    1 | Using where |

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

1 row in set (0.01 sec)

mysql> desc select * from t2 where content like 'world4%';

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

| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |

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

|  1 | SIMPLE      | t2    | range | idx_blob      | idx_blob | 153     | NULL |    1 | Using where |

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

1 row in set (0.00 sec)

可以看出，使用了前缀索引idx_blob。