在长字符串上创建索引

目录

• 背景
• 解决方案
  • 1、创建示例表
  • 2、初始化数据
  • 3、查询
    • 3.1、确定区分度
    • 3.2、创建索引

背景

当在很长的字符串的字段上创建索引时，索引会变得很大而且低效。

解决方案

1、创建示例表

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `domain` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、初始化数据

INSERT INTO `test`(`name`, `domain`)
VALUES('京东', 'https://www.jd.com/')
     ,('淘宝', 'https://www.taobao.com/')
     ,('爱奇艺', 'https://www.iqiyi.com/')
     ,('腾讯视频', 'https://v.qq.com/')
     ,('网易云音乐', 'https://music.163.com/')
     ,('qq音乐', 'https://y.qq.com/')
     ,('酷狗音乐', 'https://www.kugou.com/')
     ,('知乎', 'https://www.zhihu.com/')
     ,('微博', 'https://weibo.com/')
     ,('哔哩哔哩', 'https://www.bilibili.com/')
     ,('终身教育平台', 'https://le.ouchn.cn/')
     ,('中国大学MOOC(慕课)', 'https://www.icourse163.org/')
     ,('MOOC中国', 'https://www.cmooc.com/')
     ,('百度网盘', 'https://pan.baidu.com/')
     ,('阿里云盘', 'https://www.aliyundrive.com/')
     ,('qq邮箱', 'https://mail.qq.com/')
     ,('网易邮箱', 'https://email.163.com/')
     ,('腾讯软件中心', 'https://pc.qq.com/category/c0.html')
     ,('360软件宝库', 'https://baoku.360.cn/')
     ,('快递投诉', 'https://sswz.spb.gov.cn/')
     ,('垃圾信息举报', 'https://www.12321.cn/')
     ,('黑猫投诉', 'https://tousu.sina.com.cn/')
     ,('中国政府网', 'http://www.gov.cn/hudong/index.htm')
     ,('办公人导航', 'https://www.bgrdh.com/')
     ,('12306', 'https://www.12306.cn/')
     ,('携程旅行', 'https://www.ctrip.com/')
     ,('飞猪', 'https://www.fliggy.com/')
     ,('中国法律服务网', 'https://ai.12348.gov.cn/pc/');

3、查询

此时查询无论是通过name字段，还是通过domain字段，都会全表扫描，在name、domain上创建索引，索引会变得很大而且低效。
要解决改问题，其中一个办法是创建前缀索引（prefix index），前缀索引的创建语法是：col_name(length)，前缀索引是对字符串的前面一部分创建索引，支持的数据类型包括：CHAR、VARCHAR、BINARY 和 VARBINARY。创建前缀索引的关键是选择前缀的字符串的长度，长度越长，索引的选择性越高，但存储的空间也越大。

3.1、确定区分度

以name列为例说明，下面的SQL语句查询在不同长度时索引的选择性。观察结果可知，当取前三个字符，就可完全区分各列。

mysql> SELECT 
    ->     COUNT(DISTINCT (LEFT(`name`, 1))) / COUNT(1) sel_1,
    ->     COUNT(DISTINCT (LEFT(`name`, 2))) / COUNT(1) sel_2,
    ->     COUNT(DISTINCT (LEFT(`name`, 3))) / COUNT(1) sel_3,
    ->     COUNT(DISTINCT (LEFT(`name`, 4))) / COUNT(1) sel_4,
    ->     COUNT(DISTINCT (LEFT(`name`, 5))) / COUNT(1) sel_5,
    ->     COUNT(DISTINCT (LEFT(`name`, 7))) / COUNT(1) sel_7,
    ->     COUNT(DISTINCT (LEFT(`name`, 9))) / COUNT(1) sel9,
    ->     COUNT(DISTINCT `name`) / COUNT(*) sel_all
    -> FROM
    ->     `test`;
+--------+--------+--------+--------+--------+--------+--------+---------+
| sel_1  | sel_2  | sel_3  | sel_4  | sel_5  | sel_7  | sel9   | sel_all |
+--------+--------+--------+--------+--------+--------+--------+---------+
| 0.8276 | 0.8276 | 1.0000 | 1.0000 | 1.0000 | 1.0000 | 1.0000 |  1.0000 |
+--------+--------+--------+--------+--------+--------+--------+---------+
1 row in set (0.00 sec)

mysql> 

3.2、创建索引

ALTER TABLE `test` 
ADD INDEX `idx_name` USING BTREE (`name`(3));