提升BLOB和TEXT查询性能

在MySQL中经常用到大对象对于如歌提高它的查询性能.这里提供一种方式,利用合成索引提高大字段的查询性能,模糊匹配还是会产生全表扫描的效果
什么是合成索引呢?
简单来说就是根据大字段的内容建立一个散列值,并把值单独存储在数据列中,接下来通过检索散列值找到数据.
例:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.14 MySQL Community Server (GPL)


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)


mysql> use test;
Database changed


mysql> create table test (id varchar(100),con blob ,hash_value varchar(50));
ERROR 1050 (42S01): Table 'test' already exists


mysql> drop table test;
Query OK, 0 rows affected (0.19 sec)


mysql> create table test (id varchar(100),con blob ,hash_value varchar(50));
Query OK, 0 rows affected (0.69 sec)


mysql> insert into test values (1,repeat('shanghai',10),md5(con));
Query OK, 1 row affected (0.07 sec)


mysql> insert into test values (2,repeat('shanghai',10),md5(con));
Query OK, 1 row affected (0.05 sec)


mysql> insert into test values (3,repeat('shanghai',10),md5(con));
Query OK, 1 row affected (0.20 sec)


mysql> insert into test values (3,repeat('shanghai shibohui',10),md5(con));
Query OK, 1 row affected (0.06 sec)


mysql> select * from test;
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+----------------------------------+
| id   | con


                    | hash_value                       |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+----------------------------------+
| 1    | shanghaishanghaishanghaishanghaishanghaishanghaishanghaishanghaishangha
ishanghai
                    | 1ff6a4dfbbe27f2cc0f182fe01366ec3 |
| 2    | shanghaishanghaishanghaishanghaishanghaishanghaishanghaishanghaishangha
ishanghai
                    | 1ff6a4dfbbe27f2cc0f182fe01366ec3 |
| 3    | shanghaishanghaishanghaishanghaishanghaishanghaishanghaishanghaishangha
ishanghai
                    | 1ff6a4dfbbe27f2cc0f182fe01366ec3 |
| 4    | shanghai shibohuishanghai shibohuishanghai shibohuishanghai shibohuisha
nghai shibohuishanghai shibohuishanghai shibohuishanghai shibohuishanghai shiboh
uishanghai shibohui | 51e282733395676b7311ca06c0b73bdd |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+----------------------------------+
4 rows in set (0.00 sec)


mysql>

如果要查询shanghai shibohui,那么可以通过散列值来查询
mysql> select * from test where hash_value=md5(repeat('shanghai shibohui',10));
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+----------------------------------+
| id   | con


                    | hash_value                       |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+----------------------------------+
| 4    | shanghai shibohuishanghai shibohuishanghai shibohuishanghai shibohuisha
nghai shibohuishanghai shibohuishanghai shibohuishanghai shibohuishanghai shiboh
uishanghai shibohui | 51e282733395676b7311ca06c0b73bdd |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+----------------------------------+
1 row in set (0.00 sec)


mysql>

上面的例子利用合成索引来实现,在一定程度上减少I/O提高查询效率,可惜只能用于精确匹配.为了在一次提高效率可以建立索引在一次提高效率

mysql> desc select * from test where hash_value=md5(repeat('shanghai shibohui',1
0));
+----+-------------+-------+------+---------------+------+---------+------+-----
--+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----
--+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 9737
0 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
--+-------------+
1 row in set (0.00 sec)

为了在一次提高效率可以建立索引在一次提高查询效率.
mysql> create index hash_idx on test (hash_value(10));
Query OK, 0 rows affected (3.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from test where hash_value=md5(repeat('shanghai shibohui',1
0));
+----+-------------+-------+------+---------------+----------+---------+-------+
-------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   |
 rows  | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+
-------+-------------+
|  1 | SIMPLE      | test  | ref  | hash_idx      | hash_idx | 33      | const |
 32202 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+
-------+-------------+
1 row in set (0.00 sec)

现在已经用到 hash_idx索引会扫描更少的行效率更高,这样就OK了..