mysql|索引键的长度对查询性能的影响 database|数据库

先看数据表

文章图片

其中红色框圈住的是新加的字段以及索引

这张表的主要作用是，存储每种类型的doc的操作日志，用于审计用。其中每个doc_type_name代表某种类型的实体，而doc_id则是这个实体在对应的表里的主键ID。
由于admin有分页的需求，因此这张表的一种查询case是
select count(id) from slow_query_tmp_tbl where doc_type_name='biz_prefix_hot_search_keyword'.
LIVE环境这个sql语句命中了120w+行，耗时偶尔会超过500ms，超过了数据库读超时设定的500ms限制，直接导致接口报错。

文章图片

我们需要在测试环境分析这个问题，上图是的数据情况，LIVE环境的数据更多一些（表里总数据行数2kw+），不过由于测试环境的MySQL数据库服务器性能更糟糕，因此测试环境可以用来分析问题以及优化方案。
有个背景信息需要说下，就是doc_type_name和doc_type_code一一对应。最初这张表里没有idx_doc_type_code_and_id这个索引，也没有doc_type_code字段。
毫无疑问，最初这张表的设计是不合理的。
我们首先Explain下这个语句：

文章图片

1. 我们可以看到，key_len=258(尽管我们用的是varchar(64)，但是数据库引擎为索引键分配了定长的存储空间)，这说明这个索引的效率并不高。
2. 这张表的查询场景决定了，应该建一个联合索引(doc_type_name, doc_id)，因为这样适用于两种查询：一是同时制定了doc_type_name和doc_id，另外一个是仅仅指定了doc_type_name。
3. 数据库查询只可能使用一个索引加速（表的创建者可能没有意识到这一点）。
4. 如果进一步优化，这张表应该维护一个联合索引足够idx_doc_type_code_and_id(doc_type_code, doc_id)，先别提主键。因为我们创建索引时，应该尽量让索引的key的长度足够短，提高索引效率。
5. 加上新索引和新列后，查询语句等价于
select count(id) from slow_query_tmp_tbl where doc_type_code=6;

a. 表中原有数据有311486行，先分别使用两种语句查询10遍，统计平均耗时
b. 然后删除掉10万行，再分别使用两种语句查询10遍，统计平均耗时

attempt	idx_doc_type_code_and_id, 211486 rows	idx_doc_type_name, 211486 rows	idx_doc_type_name, 311486 rows
1	0.24	0.26	0.61
2	0.16	0.37	0.39
3	0.17	0.31	0.7
4	0.13	0.21	0.51
5	0.14	0.5	0.26
6	0.19	0.31	0.49
7	0.12	0.22	0.5
8	0.22	0.45	0.42
9	0.11	0.2	0.38
10	0.26	0.42	0.51
avg	0.174	0.325	0.477

我们可以发现
1. 索引键长度越短越好。varchar字段在索引中占的空间并不会变(char类型也是如此，我亲自试了下，而且对于较新版本数据库来说，无论varchar还是char，这里单位都是字符)。
2. 这里doc_type_name更适合用blob数据类型。因为blob长度的单位是字节，这样有利于降低索引键长度。
3. doc_id字段如果INSERT时取值都是INT，应该尽量改为INT类型而不是varchar。因为这会导致索引键长度很长。
4. 我们可以仔细想下，对比表格里第二列和第四列，可以看到这里耗时原因更像是不同长度索引键的匹配，而不是索引数据的读取（索引大多数情况下都在内存中）。

【mysql|索引键的长度对查询性能的影响】