为字符串类型构建MySQL自己的hash索引

最新推荐文章于 2025-02-15 00:15:00 发布

weixin_34026484

最新推荐文章于 2025-02-15 00:15:00 发布

阅读量142

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/sunss/archive/2012/01/17/2323644.html

本文介绍了一种利用CRC32校验和替代字符串匹配的方法，以减少MySQL中字符串比较带来的性能开销。通过对user_nick字段计算CRC32值并建立索引，实现了快速查找，显著提高了查询效率。

这是一个真实的场景，表的定义如下：

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `user_nick` varchar(32),
  `user_source` tinyint(4) NOT NULL ,
  `user_type` tinyint(4) NOT NULL,
........  
  `version` int(11) DEFAULT '0' COMMENT '版本',
  `crc_user_nick` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_nick` (`user_nick`),
KEY `idx_crc_user_nick` (`crc_user_nick`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

为了显示对比结果，我为user_nick列添加了一个索引，同时添加了一个存放crc32的字段crc_user_nick列，同时在这列上创建了一个索引，

update user set crc_user_nick=crc32(user_nick);

表中有将近200w的数据，并且user_nick不允许重复，随机抽取一条查询，根据crc查询：

root@test 01:25:34>set profiling=1;
Query OK, 0 rows affected (0.00 sec)

root@test 01:25:37>select * from user where crc_user_nick=3741129210;

耗时：1 row in set (0.40 sec)

root@test 01:25:42>show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000042 | 0.000000 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000089 | 0.001000 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.388417 | 0.014998 |   0.004999 |            0 |             0 |
| System lock                    | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
| Table lock                     | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| init                           | 0.000084 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     | 0.003755 | 0.000000 |   0.000000 |            0 |             0 |
| preparing                      | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| executing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data                   | 0.001021 | 0.000000 |   0.000000 |            0 |             0 |
| end                            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end                      | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000043 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+

对比同一条记录使用user_nick查询，

root@test 10:59:32>set profiling=1;
Query OK, 0 rows affected (0.00 sec)

root@test 10:59:41>select * from user_nick where user_nick='one001008104075924';

耗时:1 row in set (0.55 sec)

root@test 01:24:24>show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000042 | 0.000000 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000089 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.542834 | 0.011998 |   0.003000 |            0 |             0 |
| System lock                    | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
| Table lock                     | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| init                           | 0.000090 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     | 0.000315 | 0.000000 |   0.000000 |            0 |             0 |
| preparing                      | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| executing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data                   | 0.007554 | 0.000000 |   0.000000 |            0 |             0 |
| end                            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end                      | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000043 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+