为字符串类型构建MySQL自己的hash索引
这是一个真实的场景，表的定义如下：
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 |
+--------------------------------+----------+----------+------------+--------------+---------------+
通过profile我们可以明显的看到使用user_nick的时候，耗时主要用在opening tables和Sending data，在一些字符类型过滤较高的条件上，我们可以采用这种方法来优化查询。
参考:《High Performance MySQL》
1、form提交时应注意“&”符号
2、第二阶段冲刺08
3、Win10 BackgroundTask
4、npm --save-dev --save 的区别
5、[转]Windows7 无法访问局域网共享文件服务器 提示密码错误