大表上子字符串的替代项

wqnecbli  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(524)

我的表中有一个名为dev\u resolution的字段,表的大小约为5gb。我需要分离分辨率并检查是否有任何值大于1000。
例子:


**Dev_Resolution**

455x500
1024x200
1080x450

所以,我需要将455和500分开,然后进一步检查是否有任何值大于1000。
我编写了以下查询来分隔这些值:

SELECT SUBSTRING_INDEX(resolution,'x',1) AS first_resolution, SUBSTRING_INDEX(resolution,'x',-1) AS second_resolution, resolution 
FROM table

但执行起来时间太长了。使用子字符串索引的替代方法是什么?

t9aqgxwy

t9aqgxwy1#

你甚至需要把绳子剪在哪里?我认为你可以单独用数学来做

SELECT * FROM
  table 
WHERE 
  CHAR_LENGTH(res) >= 8 OR   
  (
    CHAR_LENGTH(res) >= 6 AND 
    LOCATE('x', resolution, 2) - CHAR_LENGTH(res)/2) NOT BETWEEN 0 and 1
  )

模式axb的任何字符串8或更长必须至少有a或b为4位或更多数字
任何长度小于6的字符串都无法匹配
只有当locate len/2超出范围0-1时,才匹配任何长度为6或7的字符串

1x10000, length 7, locate 2, calc -1.5 KEEP
10x1000, length 7, locate 3, calc -0.5 KEEP
100x100, length 7, locate 4, calc 0.5 THROW
1000x10, length 7, locate 5, calc 1.5 KEEP
10000x1, length 7, locate 6, calc 2.5 KEEP
1000x1, length 6, locate 5, calc 2 KEEP
100x10, length 6, locate 4, calc 1 THROW
10x100, length 6, locate 3, calc 0 THROW
1x1000, length 6, locate 2, calc -1 KEEP
gcuhipw9

gcuhipw92#

你有索引吗 resolution 列?因为查询中没有where条件,所以它必须读取整个表。因此,这取决于您的表有多大,但索引仍有很大帮助:
测试表:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resolution` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_resolution` (`resolution`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

试验数据:

select * from t1 order by id;
+----+------------+
| id | resolution |
+----+------------+
|  6 | 455x500    |
|  7 | 1000x500   |
|  8 | 200x600    |
|  9 | 2000x800   |
| 10 | 1400x800   |
| 11 | 600x600    |
+----+------------+

如果检查查询的执行计划:

explain SELECT SUBSTRING_INDEX(resolution,'x',1) AS first_resolution, SUBSTRING_INDEX(resolution,'x',-1) AS second_resolution, resolution  FROM t1\G

***************************1. row***************************

           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_resolution
      key_len: 23
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

我有一个索引 resolution 而在 Extra 你可以看到 Using Index ,这意味着innodb可以使用索引返回结果它根本不需要读取磁盘(如果您有足够的内存来保存索引)。
过滤大于1000的值:

SELECT SUBSTRING_INDEX(resolution,'x',1) AS first_resolution, SUBSTRING_INDEX(resolution,'x',-1) AS second_resolution, resolution  FROM t1 having first_resolution > 1000 or second_resolution > 1000;
+------------------+-------------------+------------+
| first_resolution | second_resolution | resolution |
+------------------+-------------------+------------+
| 1400             | 800               | 1400x800   |
| 2000             | 800               | 2000x800   |
+------------------+-------------------+------------+

正如你所看到的,我使用的是过滤大于1000的分辨率。
结论:
如果没有索引的话 resolution innodb必须扫描整个表,这可能需要磁盘读取。如果有索引,innodb可以使用索引并从内存返回结果。
速度取决于表的大小,因为没有额外的过滤。
有什么更好的解决办法?
如果您计划定期运行此查询,我建议您考虑将此列拆分为两列: width 以及 height 如果两者都有索引,则可以在where条件下轻松地对其进行筛选。
另一个好的解决方案是使用生成的列。
新建表:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resolution` varchar(20) DEFAULT NULL,
  `width` smallint(4) GENERATED ALWAYS AS (SUBSTRING_INDEX(resolution,'x',1)),
  `height` smallint(4) GENERATED ALWAYS AS (SUBSTRING_INDEX(resolution,'x',-1)),
  PRIMARY KEY (`id`),
  KEY `idx_resolution` (`resolution`),
  KEY `idx_w` (width),
  KEY `idx_h` (height)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

如你所见,我生成 width 以及 height 原始列 resolution 列。这不需要更改应用程序,只需要一个alter表。我还为这些列添加了索引。

explain select resolution from t2 where width > 1000 or height > 1000\G

***************************1. row***************************

           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index_merge
possible_keys: idx_w,idx_h
          key: idx_w,idx_h
      key_len: 3,3
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using sort_union(idx_w,idx_h); Using where

如您所见,它使用索引,但也进行索引合并。如果你总是在这两个条件下过滤,你可以添加一个复合索引(宽度,高度),这样你就可以避免索引合并。

相关问题