MySQL: 低基数/选择性列 = 如何建立索引?
MySQL: 低基数/选择性列 = 如何建立索引?
我需要给我的表(列)添加索引,然后我偶然看到了这篇文章:How many database indexes is too many?。
引用:
“话虽如此,你可以向一个表中添加很多毫无意义的索引,这样做是没有任何作用的。在只有两个不同值的列上添加B-Tree索引是没有意义的,因为它在查找数据方面没有任何帮助。一个列中的唯一值越多,它从索引中受益就越多。”
如果只有两个不同的值,那么索引真的毫无意义吗?给定一个如下的表(MySQL数据库,InnoDB)
Id(BIGINT) fullname(VARCHAR) address(VARCHAR) status(VARCHAR)
进一步条件:
- 数据库包含3亿条记录
- 状态只能是“enabled”和“disabled”
- 1.5亿条记录的状态为“enabled”,1.5亿条记录的状态为“disabled”
我的理解是,如果在status上没有索引,使用where status='enabled'
进行查询将导致对3亿条记录进行全表扫描,处理效率如何?
使用BTREE索引在查找时有多高效?
我应该给这个列建立索引吗?
在具有非常低的唯一性/选择性值的情况下,MySQL InnoDB提供了哪些其他的索引来有效地通过“where status='enabled'”子句查询记录的方式?
MySQL中,添加索引的目的是限制全记录搜索的数量,从而限制IO,因为IO通常是瓶颈。但是添加索引并不是免费的,在插入/更新时需要更新索引,并且在搜索本身时,需要加载索引文件(如果索引文件超过内存大小)。因此,添加索引可能会增加IO,而不是减少IO。
对于二进制变量,最好将其存储为布尔值或tinyint,因为这样可以减少行的长度,从而减少磁盘IO,并且数字比较更快。
如果需要速度并且很少使用禁用的记录,可以考虑使用两个表,一个用于启用的记录,一个用于禁用的记录,并在状态更改时移动记录。尽管这会增加复杂性和风险,但这应该是最后的选择。如果选择这种方法,请务必在一个事务中进行移动。
可以使用explain语句来检查索引是否实际被使用。这将显示MySQL如何优化查询。如果有一个与真实数据库大小和数据相似的数据库副本,可以在该表上创建索引,查看它是否实际被使用。我不确定MySQL如何优化查询,但我知道在postgresql中,你应该在一个与真实数据库大小和数据相似的数据库上解释查询。所以,如果有一个数据库的副本,请在该表上创建索引,并查看是否实际使用了索引。虽然我对此表示怀疑,但我绝对不是什么都不知道的:)
另外,对于status字段,可以考虑使用分区或者两个单独的表。分区适用于无论状态如何都需要记录的查询,而两个单独的表适用于每个查询都涉及到status字段。在正确调优的MySQL服务器上,插入操作的惩罚可以被选择的提升性能所抵消。将status字段改为tinyint,并且在一段时间后,索引文件(对于300M条记录)可以轻松地适应键缓冲区。
当查询只是where status = disabled时,索引可能会更差。
总结一下,对于低基数/选择性的列,如何添加索引的问题,可以考虑以下几点解决方法:
1. 考虑添加索引的代价,包括插入/更新时的代价和搜索时的代价。
2. 对于二进制变量,最好将其存储为布尔值或tinyint,以减少行的长度和提高比较速度。
3. 如果很少使用禁用的记录,可以考虑使用两个表,一个用于启用的记录,一个用于禁用的记录,并在状态更改时移动记录。
4. 使用explain语句来检查索引是否实际被使用,以了解MySQL如何优化查询。
5. 对于status字段,可以考虑使用分区或者两个单独的表,根据具体查询涉及到status字段的情况选择适合的方法。
从上面的内容可以看出,出现问题的原因是在使用MySQL中的索引时,如果针对低基数/选择性的列进行索引,将导致索引几乎没有作用。索引在查询结果集占总行数的比例很小时才能发挥最佳效果。
这是因为数据库在访问表时有两种方式:全表扫描和使用行ID或键查找。全表扫描是逐个读取和处理每个数据块,而行ID或键查找是通过索引获取到具体需要的行。
当使用基于主键或其他唯一索引的where子句时,例如`where id = 1`,数据库可以使用索引直接获取到行数据的存储位置的准确引用。这显然比进行全表扫描并处理每个数据块更有效率。
然而,如果where子句是`where status = 'enabled'`,索引将返回150m行,数据库将不得不使用单独的小读取逐行读取每一行。而通过全表扫描访问表可以利用更高效的大读取。
在某种程度上,直接进行全表扫描而不使用索引可能更好。在MySQL中,可以在查询中使用`FORCE INDEX (idx_name)`来允许对比每种表访问方法。
对于低基数/选择性的列,使用索引可能没有太大的好处。在某些情况下,直接进行全表扫描可能更为高效。