时间戳

首页 > 解决方案 > MySQL 索引 + 数学运算符

mysql - MySQL 索引 + 数学运算符

问题描述

我有评级表：

UserID int,
Rating int,
BanMask int,
index rating_index (Rating DESC),
index ban_index (BanMask ASC)

假设该表中有超过 500 万行，并且只有大约 100 个真正被禁止的用户。

如果我对索引字段使用位数学运算，选择查询是否仍会得到优化？这 2 个查询会使用索引优化吗？

SELECT * FROM ProfileTable 
WHERE BanMask > 0 
ORDER BY Rating DESC LIMIT 10;

对比

SELECT * FromProfileTable 
WHERE (BanMask & (1 << 2)) > 0 
ORDER BY Rating DESC LIMIT 10;

第二个问题。我应该在 Rating + BanMask 字段上添加索引以获得更好的优化吗？像这样：

CREATE INDEX rating_ban_index ON ProfileTable (Rating DESC, BanMask ASC)

标签： mysqloptimizationindexing

解决方案

您可以使用EXPLAIN自己确认哪些索引用于给定查询。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RatingTable
         type: index
possible_keys: ban_index
          key: rating_index
      key_len: 5
          ref: NULL
         rows: 10
        Extra: Using where

您应该研究此手册页以获取对输出的解释：https ://dev.mysql.com/doc/refman/8.0/en/explain-output.html

我希望没有索引可以用于使用表达式的查询。

WHERE (BanMask & (1 << 2)) > 0

EXPLAIN 报告显示：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RatingTable
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where; Using filesort

通常，如果比较运算符左侧的索引列在表达式或函数中被引用，则不能使用索引。它必须是“裸”列。

当您搜索按索引的排序顺序排列在一起的值时，索引会起作用。您的示例搜索 BanMask 中的每 4 个值，即那些在 4 的位置集中具有该位的值。这些值不是连续的，它们是分散的。MySQL 不会使用索引来搜索整个值的范围，因为最终这将与扫描整个表一样昂贵。

至于你的第二个问题，关于在 上添加索引(Rating DESC, BanMask ASC)，答案是它可能有助于避免文件排序。但它无助于搜索 BanMask。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RatingTable
   partitions: NULL
         type: index
possible_keys: NULL
          key: Rating
      key_len: 10
          ref: NULL
         rows: 10
        Extra: Using where

推荐阅读