MYSQL数据库设计，大表间查询

Question

我有什么:

tableA 约有 2.5 亿行，例如:

A_id     thing_id     thing            
----     --------     -----
001      345          foo
002      567          bar
003      678          poo
004      789          gar

和

tableB 约有 5000 万行，例如:

B_id     thing_id     user_id     action            
----     --------     -------     -----
001      123          001         some
002      234          002         thing
003      345          001         dont
004      567          002         matter

现在 thing_id 只能在tableB 中出现一次

稍后:我需要能够在 tableB 中拥有来自不同 user_id 的多个 thing_id

我需要做什么:

现在:从 tableA 中选择一个 thing_id，它不在 tableB

中

稍后从 tableA 中选择 tableB 少于 X 次的 thing_id

我是自学 SQL 的，但我找不到一种方法可以有效地处理非常大的表。也许我只是没有索引？我一直在使用 MariaDB 进行测试，看看这是否有所作为。

**

编辑(按要求)

**

测试表A:

/*Table: TEST_tableA*/
----------------------

/*Column Information*/
----------------------

Field      Type         Collation    Null    Key     Default  Extra           Privileges                       Comment  
---------  -----------  -----------  ------  ------  -------  --------------  -------------------------------  ---------
tableA_id  int(11)      (NULL)       NO      PRI     (NULL)   auto_increment  select,insert,update,references           
thing_id   int(11)      (NULL)       YES     UNI     (NULL)                   select,insert,update,references           
thing      varchar(32)  utf8mb4_bin  YES     MUL     (NULL)                   select,insert,update,references           

/*Index Information*/
---------------------

Table        Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
-----------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
TEST_tableA           0  PRIMARY              1  tableA_id    A                97561    (NULL)  (NULL)          BTREE                               
TEST_tableA           0  thing_id             1  thing_id     A                97561    (NULL)  (NULL)  YES     BTREE                               
TEST_tableA           1  thing                1  thing        A                97561    (NULL)  (NULL)  YES     BTREE                               

/*DDL Information*/
-------------------

CREATE TABLE `TEST_tableA` (
  `tableA_id` int(11) NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) DEFAULT NULL,
  `thing` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`tableA_id`),
  UNIQUE KEY `thing_id` (`thing_id`),
  KEY `thing` (`thing`)
) ENGINE=InnoDB AUTO_INCREMENT=2050005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

测试表B:

/*Column Information*/
----------------------

Field      Type         Collation    Null    Key     Default  Extra           Privileges                       Comment  
---------  -----------  -----------  ------  ------  -------  --------------  -------------------------------  ---------
tableB_id  int(11)      (NULL)       NO      PRI     (NULL)   auto_increment  select,insert,update,references           
thing_id   int(11)      (NULL)       YES     MUL     (NULL)                   select,insert,update,references           
user_id    int(11)      (NULL)       YES     MUL     (NULL)                   select,insert,update,references           
action     varchar(32)  utf8mb4_bin  YES             (NULL)                   select,insert,update,references           

/*Index Information*/
---------------------

Table        Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
-----------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
TEST_tableB           0  PRIMARY              1  tableB_id    A                25092    (NULL)  (NULL)          BTREE                               
TEST_tableB           1  thing_id             1  thing_id     A                25092    (NULL)  (NULL)  YES     BTREE                               
TEST_tableB           1  user_id              1  user_id      A                25092    (NULL)  (NULL)  YES     BTREE                               

/*DDL Information*/
-------------------

CREATE TABLE `TEST_tableB` (
  `tableB_id` int(11) NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`tableB_id`),
  KEY `thing_id` (`thing_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

查询:

SELECT a.thing_id 
FROM TEST_tableA a
LEFT JOIN TEST_tableB b ON a.thing_id = b.thing_id
WHERE b.thing_id IS NULL
LIMIT 1

Answer 1

这两个表似乎都不需要 AUTO_INCREMENT。它会使磁盘困惑并减慢查询速度(对于这种大小的表)。

CREATE TABLE `TEST_tableA` (
  `thing_id` int(11) DEFAULT NULL,
  `thing` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`thing_id`),
  KEY `thing` (`thing`)
) ENGINE=InnoDB;

CREATE TABLE `TEST_tableB` (
  `thing_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`thing_id`, user_id),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB 

如果其中一个真的是多对多映射表，请参阅我的 many:many tips .

注意:我建议的 PK 允许您“稍后”

现在:从表 A 中选择一个不在表 B 中的 thing_id

SELECT A.thing_id
    FROM A
    LEFT JOIN B  ON A.thing_id = B.thing_id
    WHERE B.thing_id IS NULL;  -- meaning that it is missing from B

稍后:从tableA中选择一个thing_id在tableB中出现次数少于X次

SELECT A.thing_id
    FROM A
    LEFT JOIN B  ON A.thing_id = B.thing_id
    GROUP BY A.thing_id
    HAVING COUNT(*) < X ; 

我认为这两者都将涉及对 A 的全表扫描，以及对 B 的探测。

(提示:不要使用不必要的前缀使表名或列名困惑。)