MySQL 隔离数据列和前缀索引的使用总结

Nice MySql

2022-05-15 0 190

隔离数据列

通常，我们会发现查询语句会妨碍MySQL使用索引。除非在查询语句中列是独立的，否则MySQL不会使用这些列的索引。“隔离”的意思是索引列不应该成为表达式的一部分或者在一个查询函数体中。例如下面的例子就不会命中actor_id这个索引。

SELECT `actor_id` FROM `actor` WHERE `actor_id` + 1 = 2;

对于人来说，很容易知道查询条件实际是actor_id = 4，但是MySQL不会这么处理，因此养成简化WHERE判决条件的习惯，这意味着索引列独立地在比较操作符的一侧。下面是另外一个普遍错误的案例：

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

前缀索引和索引的选择性

有时候需要在很长字符的列上建立索引，但这样会导致索引占据的空间很大且查询变慢。一个策略是使用哈希索引模拟，但有时候这未必是足够好，这个时候该怎么做？

通常是可以将索引列前面的部分字符建立索引来替换全字段索引提高性能和节省空间。但这种方式会使得选择性变差。索引的选择性是指独立的索引值筛选出的数据占整个数据集合的比例。高选择性的索引可以让MySQL过滤掉更多无关的数据。例如，一个唯一索引的选择性是1。列的前缀通常在选择性方面已经能够提供足够好的性能。如果使用BLOB或TEXT或非常长的VARCHAR字段列，你必须定义前缀索引，以为MySQL不允许做全长度索引。

你需要在使用更长的前缀以获得更好的选择性和足够短的前缀以节省存储空间之间平衡。为了确定一个合适的前缀长度，查找出最高频的值，然后和最频繁的前缀进行比较。例如以城市数据表为例，我们可以使用如下的语句统计：

SELECT COUNT(*) as cnt, `name` FROM `common_city` GROUP BY `name` ORDER BY cnt DESC LIMIT 10

MySQL 隔离数据列和前缀索引的使用总结

可以看到这些城市名称出现的次数比较多。现在我们可以使用1个字的前缀查找最为频繁的城市名称前缀。

SELECT COUNT(*) as cnt, LEFT(`name`, 1) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

MySQL 隔离数据列和前缀索引的使用总结

可以看到1个字找出来的数据集更多了，这会导致独立选中的机会越少，因此需要调整一下前缀的长度。例如调到3个字。

SELECT COUNT(*) as cnt, LEFT(`name`, 3) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

MySQL 隔离数据列和前缀索引的使用总结

可以看到这和全长度的相差不多，那实际三个字的前缀就够了（原文使用的是英文城市数据表，字符会更多）。另外一种方式是使用不同长度的前缀数量与全字段数量的比例评估多少合适。例如：

SELECT 
  COUNT(DISTINCT LEFT(`name`, 1)) / COUNT(`name`) as pref1, 
  COUNT(DISTINCT LEFT(`name`, 2)) / COUNT(`name`) as pref2, 
  COUNT(DISTINCT LEFT(`name`, 3)) / COUNT(`name`) as pref3, 
  COUNT(DISTINCT LEFT(`name`, 4)) / COUNT(`name`) as pref4 
FROM `common_city`

MySQL 隔离数据列和前缀索引的使用总结

数值越接近于1效果越好，但是也可以看到，随着前缀长度的加长改善的空间越小。只看平均值并不是一个好主意，还需要检查一下最坏情况。也许会觉得3-4个字足够了，但是如果数据分布很不均匀，那可能会存在陷阱。因此还需要检查一下前缀少的是不是存在一个前缀对应的数据与其他相比极其多的情况。最后可以给指定的列加前缀索引。

ALTER TABLE `common_city` ADD KEY (name(3));

前缀索引在节省空间和提高效率方面表现不错，但是也有缺陷，那就是在ORDER BY和GROUP BY上无法使用索引（实际验证在MySQL 5.7以上版本也有用）。另外一种常见的场景是在较长的十六进制字符串中，例如存储的sessionId，取前8位前缀做索引将过滤很多无关数据，效果很好。

以上就是MySQL 隔离数据列和前缀索引的使用总结的详细内容，更多关于MySQL 隔离数据列和前缀索引的资料请关注NICE源码其它相关文章！

目录

隔离数据列

前缀索引和索引的选择性

微信