性能文章>【全网首发】数据库系列:前缀索引和索引长度的取舍>

【全网首发】数据库系列:前缀索引和索引长度的取舍原创

1年前
271978

数据库系列:MySQL慢查询分析和性能优化
数据库系列:MySQL索引优化总结(综合版)
数据库系列:高并发下的数据字段变更
数据库系列:使用高区分度索引列提升性能

1 背景

有时候我们的字段是个长字符串,在长字段上建立索引,会让索引变得大而且慢。
根据我们之前的了解,每个磁盘块(disk)存储的内容是有限的(InnoDB存储引擎中页的大小为16KB),如果一个页中可以存储的索引记录越多,那么查询效率就会提高。
但是索引整个字符列让我们索引内容特别大,这会导致单个页存储的索引记录减少,就需要更多的页去存储,B+Tree的树深度变深,查询效率大幅降低。
比较有效的一种办法是指定索引的字段长度,比如可以索引开始的部分字符,这样可以大大节约索引空间(每个页),从而提高索引效率。

# enp_name 如果太长会导致索引空间爆棚
create index idx_emp_empname on emp(emp_name)

# 可能合适的做法
create index idx_emp_empname on emp(emp_name(5))

2 关于索引选择性

索引的选择性是指不重复的索引值(也称为基数,cardinality) 和 数据表的记录总数的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
以下图为例,字段emp_name的索引长度设置为4。如果不设置长度,一旦有大量长字符串元素,会占据大量的空间,而单个Disk的空间是有限的。
就会导致树的层级很高,搜索的IO次数变多,索引性能降低。
image.png

★ 说明:#T 指数据表的记录总数
★ 参考书籍《高性能MySQL》 的5.3.2小节的第3段 :
一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于BLOB,TEXT,或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。

3 如何计算索引的选择性

3.1 分析

提供一个具有500w数据的雇员表emp,准备在雇员姓名 emp_name 字段在做索引。
emp_name有些值还是挺长的,如果全部拿来做索引,那势必导致索引表很庞大。我们先来检索下这个emp_name,看看有没有什么特征。

1  mysql> select distinct emp_name from emp;
2  +----------+
3  | emp_name |
4  +----------+
5  |  ali_user |
6  |  brandy_user |
7  |  cancys_user |
8  |  davide_user |
9  |  elasne_user |
10 |  finest_user |
11 |  ......... |
12 |  gagnannan_user |
13 |  halande_user |
14 |  ivil_user |
15 |  kikol_user |
16 |  Cecilia_user |
17 |  Abigail_user |
18 |  Blanche_user |
19 |  Elizabeth_user |
20 |  ....... |
21 +----------+
22 4999850 row in set 

从上面的数据进行分析,可以得到以下结论:

  • 索引长度肯定不能太短(比如2或者3),前6个字符的区分度非常高,如果选择2或者3,会扫出大量的数据,增加计算成本。
  • 不能不设置索引的长度,后缀 _user 是一致的,没有任何的区分度优势,反而会占用很多无效的存储空间,增加 I/O次数。
  • 目前看,最合适的可能是6或者7,前几个字符的区分度很高,这只是推断,需要用算法判定。

3.2 前缀索引的长度的判断方法

判断的算法:

select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;

可以套入emp表进行计算,如下:

SELECT
    count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
    count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
    count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
    count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
    count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
FROM
    emp

3.3 添加前缀索引

下面是添加带有索引长度的前缀索引语法:

ALTER TABLE t_name ADD KEY (c_name[(lenth)]); 

测试emp_name最合适的长度,因为empname的长度基本维持在6个字符左右,少数量超过6长度,所以指定empname索引长度时6是最建议的

mysql>  SELECT
     count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
     count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
     count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
     count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
     count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
 FROM
     emp;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set

我们可以使用 不同的长度来测试检索效率
当长度为2的时候,匹配度低于 0.0012,检索效率自然比较慢

mysql> create index idx_emp_empname on emp(emp_name(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | emp_name | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (1.793 sec)

当长度为6的时候,匹配度低于 0.1713,检索效率就比较高

mysql> create index idx_emp_empname on emp(emp_name(6));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | emp_name | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (0.003 sec)

4 总结

选择索引长度应该要选择足够长的前缀以保证较高的选择性,同时又不能太长,以便节约空间。
最好的要求就是前缀的基数(cardinality)接近于完整列的基数,以达到足够优秀的性能。

欢迎关注公众号【架构与思维】,一群bat一线资深研发、架构师的技术分享!

image.png

点赞收藏
Pinocao
请先登录,查看7条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

数据库系列:事务的4种隔离级别

数据库系列:事务的4种隔离级别

数据库系列:RR和RC下,快照读的区别

数据库系列:RR和RC下,快照读的区别

8
7