mysql对字符串建立索引 mysql字符串索引如何排序

转载

AI智行者 2023-08-06 13:30:53

文章标签 mysql对字符串建立索引字符串 mysql 数据库 java 文章分类 MySQL 数据库

给不固定内容字符串类型的字段加索引, 通常有这么几种做法:

前缀索引
倒序索引:
加hash字段做为索引列

可以参考丁奇老师的专栏文章: 怎么给字符串字段加索引?

这里我们只讨论hash方式.

使用md5 short, 栏位设置为 char(16), 这样一个索引列占用空间是 16*3+1=49 bytes.
使用 crc32, 栏位设置为 bigint, 这样一个索引列占用空间是 8+ 1= 9 bytes.

表 charset=utf8mb3. 如果是utf8mb4, 则md5 short索引长度为 16*4+1=69 bytes
crc32的值超过了4 bytes的int, 必须使用big int

生成模拟数据

我们以用户email+电话tel两个栏位进行数据模拟. email前面部分长度为6-20

建表

USE `test`;
DROP TABLE IF EXISTS `hash_index`;
CREATE TABLE `hash_index` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
   `email` varchar(50) DEFAULT NULL,
   `email_md5` char(16) DEFAULT NULL COMMENT 'email的短md5',
   `email_hash` bigint DEFAULT NULL COMMENT 'email的crc32',-- 注意: 必须用bigint, 4字节的int存不下
   `tel` varchar(50) DEFAULT NULL,
   `tel_md5` char(16) DEFAULT NULL COMMENT 'tel的短md5',
   `tel_hash` bigint DEFAULT NULL COMMENT 'tel的crc32',-- 注意: 必须用bigint, 4字节的int存不下
   PRIMARY KEY (`id`),
   KEY `md5` (`email_md5`,`tel_md5`),
   KEY `hashs` (`email_hash`,`tel_hash`)
) ENGINE=InnoDB CHARSET=utf8mb3;

php生成sql文件

function genRandString($length){
    $arr_source = array_merge(range('a', 'z'), range(0,9));
    $super = count($arr_source) - 1;
    $arr = [];
    for($i = 0; $i < $length; $i++){
        $ix = rand(0, $super);
        $arr[] = $arr_source[$ix];
    }
    return implode('', $arr);
}
function genRandDomain(){
    $arr_domain = [
        'gmail.com',
        'qq.com',
        '163.com',
        '126.com',
        'gmail.com',
    ];
    $rt = $arr_domain[rand(0, count($arr_domain) - 1)];
    return $rt;
}

/**
 * 生成一个电话号码(非国内)
 *
 * @return string
 */
function genRandTel(){
    $length = 9;
    $arr = [];
    for($i = 0; $i < $length; $i++){
        $arr[] = rand(0, 9);
    }
    $first = rand(1, 9);
    return $first. implode('', $arr);
}

/**
 * 生成一个email地址
 * @return string
 */
function genRandEmail(){
    return genRandString(rand(6,20)) . '@' . genRandDomain();
}

/**
 * 生成短md5
 * @param string $str
 * @return string
 */
function md5_short($str=''){
    return substr(md5($str),8,16);
}

/**
 * 生成sql
 * 
 * @param int $length
 * @param bool $auto_commit 是否自动提交
 * @param bool $batch_insert 是否使用批量插入
 */
function genSQL($length, $auto_commit=true, $batch_insert=false) {
    // region 数据插入优化设置
    $step = 500;
    $batch_insert_size = 50;
    // endregion

    $file_name = 'hash_index_'.$length.'.sql';
    $table_name = 'hash_index';

    //数据太长, 容易超出内存限制, 故分批次写入
    file_put_contents($file_name, 'USE `test`;' . PHP_EOL);//重置文件
    for ($i = 0, $n = ceil($length / $step); $i < $n; $i++) {
        $lot_data = [];//分批数据, 元素直接是values的字符串
        for($j=0; $j < $step; $j++){
            $email = genRandEmail();
            $email_md5 = md5_short($email);
            $email_hash = crc32($email);//注意: 这里不同于hash('crc32', $str) !!!!

            $tel = genRandTel();
            $tel_md5 = md5_short($tel);
            $tel_hash = crc32($tel);//注意: 这里不同于hash('crc32', $str) !!!!

            $lot_data[] = '(\''.$email.'\', \''.$email_md5.'\', '.$email_hash.', \''.$tel.'\', \''.$tel_md5.'\', '.$tel_hash.')';
        }

        $arr_sql = [];
        if (!$auto_commit) {
            $arr_sql[] = 'SET autocommit = 0;';
        }
        if ($batch_insert) {
            //这里再分成50个一份
            for ($j = 0, $n2 = ceil(count($lot_data) / $batch_insert_size); $j < $n2; $j++) {
                $arr_v_sliced = array_slice($lot_data, $batch_insert_size * $j, $batch_insert_size);
                $arr_sql[] = 'INSERT INTO `' . $table_name . '`(`email`,`email_md5`,`email_hash`,`tel`,`tel_md5`,`tel_hash`) VALUES ' . implode(',', $arr_v_sliced) . ';';
            }
        } else {
            foreach ($lot_data AS $row) {
                $arr_sql[] = 'INSERT INTO `' . $table_name . '`(`email`,`email_md5`,`email_hash`,`tel`,`tel_md5`,`tel_hash`) VALUES '.$row.';';
            }
        }

        if (!$auto_commit) {
            $arr_sql[] = 'commit;';
        }
        $sql_all = implode(PHP_EOL, $arr_sql) . ($i < $n - 1 ? PHP_EOL : '');
        file_put_contents($file_name, $sql_all, FILE_APPEND);
    }
}

用上面的代码, 我们来生成50w笔数据:

genSQL(500000, false, true);

数据查询

先看以下使用两种hash索引的执行计划:

EXPLAIN SELECT * FROM `hash_index`
WHERE email_md5='a2dce0b8e5f97c82' AND tel_md5='60890e62c19aefe2' 
AND email='wxnpjbf752blke3z@163.com' AND tel='8326347684';

mysql对字符串建立索引 mysql字符串索引如何排序_字符串

crc32

EXPLAIN SELECT * FROM `hash_index` 
WHERE email_hash=1168974173 AND tel_hash=775416052 
AND email='wxnpjbf752blke3z@163.com' AND tel='8326347684';

mysql对字符串建立索引 mysql字符串索引如何排序_java_02

注意上面的sql中, 除了使用索引, 还加了原栏位的值判断, 这是避免md5或crc32值重复带来的问题.

注意看explain结果中的key_len, 这个表示使用到的索引的长度.

crc32的重复概率是比md5要高很多的, 到底有多高呢?

SELECT email_hash, COUNT(email_hash) AS e 
FROM `hash_index`
GROUP BY email_hash HAVING COUNT(email_hash) >1
ORDER BY e DESC;

mysql对字符串建立索引 mysql字符串索引如何排序_mysql_03

SELECT tel_hash, COUNT(tel_hash) AS e
FROM `hash_index`
GROUP BY tel_hash HAVING COUNT(tel_hash) >1
ORDER BY e DESC;

mysql对字符串建立索引 mysql字符串索引如何排序_mysql_04

从目前来看, 50w随机数据的重复概率还是很低的.

其实本栗子中的tel都是数值, 直接使用bigint存储即可, 不需要再做额外的索引, 只是为了和之前的查询业务做比较而添加的.

就目前windows下单个请求测试, 两种hash的查询性能看不出来差异, 但是理论上是crc32更快的.

关于索引空间大小的计算问题

索引使用的硬盘空间大小, 与explain计算出来的差异比较大. 按上面计算的, md5 short占用的索引空间是98 bytes, crc32的是9bytes.

我们导入一个5w的数据作为测试, 建2个单独索引的表做比较.

DROP TABLE IF EXISTS `hash_index__md5`;
CREATE TABLE `hash_index__md5` (
   `id` int unsigned NOT NULL, -- 这里不要加PK
   `email` varchar(50) DEFAULT NULL,
   `email_md5` char(16) DEFAULT NULL COMMENT 'email的短md5',
   `email_hash` bigint DEFAULT NULL COMMENT 'email的crc32',-- 注意: 必须用bigint, 4字节的int存不下
   `tel` varchar(50) DEFAULT NULL,
   `tel_md5` char(16) DEFAULT NULL COMMENT 'tel的短md5',
   `tel_hash` bigint DEFAULT NULL COMMENT 'tel的crc32',-- 注意: 必须用bigint, 4字节的int存不下
   KEY `md5` (`email_md5`,`tel_md5`)
   -- KEY `hashs` (`email_hash`,`tel_hash`)
) ENGINE=InnoDB CHARSET=utf8mb3;

DROP TABLE IF EXISTS `hash_index__crc`;
CREATE TABLE `hash_index__crc` (
   `id` int unsigned NOT NULL,
   `email` varchar(50) DEFAULT NULL,
   `email_md5` char(16) DEFAULT NULL COMMENT 'email的短md5',
   `email_hash` bigint DEFAULT NULL COMMENT 'email的crc32',-- 注意: 必须用bigint, 4字节的int存不下
   `tel` varchar(50) DEFAULT NULL,
   `tel_md5` char(16) DEFAULT NULL COMMENT 'tel的短md5',
   `tel_hash` bigint DEFAULT NULL COMMENT 'tel的crc32',-- 注意: 必须用bigint, 4字节的int存不下
   -- KEY `md5` (`email_md5`,`tel_md5`),
   KEY `hashs` (`email_hash`,`tel_hash`)
) ENGINE=InnoDB CHARSET=utf8mb3;

我们从表hash_index导入数据到这两个单独索引的表:

INSERT INTO `test`.`hash_index__md5`(`id`,`email`,`email_md5`,`email_hash`,`tel`,`tel_md5`,`tel_hash`)
SELECT * FROM `test`.`hash_index`;
INSERT INTO `test`.`hash_index__crc`(`id`,`email`,`email_md5`,`email_hash`,`tel`,`tel_md5`,`tel_hash`)
SELECT * FROM `test`.`hash_index`;

然后看着三个表的空间使用:

-- 查看表空间
SELECT `TABLE_NAME`, `ROW_FORMAT`, `TABLE_ROWS`, `AVG_ROW_LENGTH`
, CONCAT(ROUND(`DATA_LENGTH`/1024/1024, 4), ' MB') AS `DATA_LENGTH`
, CONCAT(ROUND(`INDEX_LENGTH`/1024/1024, 4), ' MB') AS `INDEX_LENGTH`
, CONCAT(ROUND(`DATA_FREE`/1024/1024, 4), ' MB') AS `DATA_FREE`
, `TABLE_COMMENT`
FROM `information_schema`.`tables`
WHERE table_schema='test' AND table_name IN('hash_index', 'hash_index__md5', 'hash_index__crc');

mysql对字符串建立索引 mysql字符串索引如何排序_字符串_05