主页 / user-4628

artfulrobot's questions

Martin Hope
artfulrobot
Asked: 2021-03-17 07:50:59 +0800 CST
  • 0

我有这个查询：

SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
    /* ➊ Inner query */
    SELECT eq.contact_id contact_id, COUNT(*) sent
    FROM mailing_job j
    INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
    WHERE j.end_date > NOW() - INTERVAL 3 MONTH
    GROUP BY eq.contact_id
  ) engagement ON engagement.contact_id = c.id

 WHERE
  /* ➋ various conditions on table 'c' */
 do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0
   AND contact_type = 'Individual'

   /* ➌ */
   AND EXISTS (
      SELECT gc.contact_id
      FROM group_contact gc
      WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
    )

   /* ➍ AND c.id=1 */
ORDER BY c.id
  • 内部查询 ➊ 自行运行时会产生结果，例如sent: 15 for contact 1。这是正确的、预期的数据。
  • 但是，运行整个查询会为整个发送的列提供NULL！这让我很烦恼。
  • 消除 ➋ 处的 WHERE 子句，或消除 ➌ 处的 EXISTS，或包括注释掉的 ➍ 子句会导致其运行并返回sent: 15用于测试联系人。

我在 MariaDB 10.3 上。

编辑

希望增加清晰度。从根本上说，这很奇怪——我认为——而且可能是 MariaDB 中的一个错误，尽管我希望不会！

内部查询 ➊ 返回行，例如

contact_id  sent
1           15
...

主查询还使用 ID 获取联系人1。

现在在我所有的岁月里，当我离开加入一个主键（即c.id）上的表时，子查询中的记录匹配，它从来没有改变它的值。

这些值随着其他 WHERE 子句的变化而变化，但在某种程度上仍然意味着外部查询会生成匹配的联系人 ID，我认为这很奇怪。

我真的希望我做了一些愚蠢的事情，但我看不到它。

mysql mariadb
Martin Hope
artfulrobot
Asked: 2020-12-15 10:10:36 +0800 CST
  • 0

我很高兴看到窗口函数登陆 MariaDB 10.2。我认为它们会很好地解决第一组问题，但我正在努力了解它们的效率。我有这样的事情：

CREATE TABLE email (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,  
                    contact_id INT UNSIGNED NOT NULL, /* FK */            
                    email VARCHAR(200),                                   
                    is_primary TINYINT(1) UNSIGNED NOT NULL DEFAULT 0)    
;                                                                         
                                                                          
INSERT INTO email (id, contact_id, email, is_primary)  VALUES                 
  (1, 1, '[email protected]', 0),                                         
  (2, 1, '[email protected]', 1),                                     
  (3, 1, '[email protected]', 0),
  (4, 2, '', 1),
  (5, 2, '[email protected]', 0),
;

我想要每个联系人的列表以及最适合他们的电子邮件。“最佳”被定义为：is_primary如果存在的话，更喜欢他们的。

我要这个：

Contact ID     Email ID             Email
-------------- -------------------- ------------------
1              2                    [email protected]
2              5                    [email protected]

使用窗口功能，我可以获得最好的电子邮件，例如

SELECT contact_id,
  FIRST_VALUE(email) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email,
  FIRST_VALUE(id) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email_id 
FROM email
WHERE email != ''
;                                                                                       

+------------+--------------------------+---------------+ 
| contact_id | best_email               | best_email_id | 
+------------+--------------------------+---------------+ 
|          1 | [email protected] |             2 |
|          1 | [email protected] |             2 |
|          1 | [email protected] |             2 | 
|          2 | [email protected]        |             5 | 
+------------+--------------------------+---------------+

但我注意到

  1. 使用n封有效（或至少非空）电子邮件，我得到n行输出。
  2. 我不得不复制逻辑：partition by contact_id对于每个SELECT; 这感觉效率低下：如果我在电子邮件表中有 12 个其他列需要其数据，我将运行 12 次，除非我只是在 ID 字段上执行此操作，然后在该最佳 ID 上再次进行 INNER JOINED 电子邮件。

所以为了得到我需要的东西，我最终是这样的：

SELECT contact_id, MIN(best_email) best_email, MIN(best_email_id) best_email_id
FROM (                                        
  SELECT  contact_id,                                                                 
    FIRST_VALUE(email) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email,
    FIRST_VALUE(id) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email_id 
  FROM email                                  
  WHERE email != ''                                                                        
) q                                                                                        
GROUP BY contact_id
;

但这感觉效率很低：MIN()需要检查每一行，即使它们都是一样的。我可以这样做：

SELECT contact_id, best_email, best_email_id                             
FROM (                                                                   
  SELECT contact_id, row_number() OVER (PARTITION BY contact_id) r,      
    FIRST_VALUE(email) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email,
    FIRST_VALUE(id) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email_id 
  FROM email                                                                              
  WHERE email != ''                                                                       
) q                                                                                       
WHERE q.r=1;

但它仍然感觉次优。

这似乎更有效：

SET @nth=0, @c=null;                                                          
SELECT id, email FROM (
  SELECT @nth := IF(@c = contact_id, @nth + 1, 1) r, id, email, @c:=contact_id dummy
   FROM email
  WHERE email != ''                                                              
  ORDER BY contact_id, is_primary DESC                                           
) sq
WHERE sq.r = 1;

我错过了什么吗？也许这不是窗口函数的正确位置？

mysql mariadb
Martin Hope
artfulrobot
Asked: 2018-07-18 00:51:45 +0800 CST
  • 1

我有一个主从设置工作正常，直到从属断电。现在我明白了

连接到主机 '[email protected]:50000' 时出错 - 重试时间：60
最大重试次数：86400
消息：用户 'repl'@'localhost' 的访问被拒绝（使用密码：是）

密码没有改变，我可以使用登录到数据库

slave-host$ mysql -h10.0.0.1 -P50000 --user=repl --password='secret'

但是，一旦登录，我就看不到任何数据库，除了information_schema. 尝试use <my_db_name>给出拒绝访问错误。

上主

master-host: mysql> show grants for repl@localhost;
GRANT REPLICATION SLAVE ON *.* TO
  'repl'@'localhost' IDENTIFIED BY PASSWORD '*xxxxx'

我已经尝试以各种组合重新授予访问权限，FLUSH PRIVILEGES但START/STOP SLAVE;我没有取得任何进展。

注意。位于 10.0.0.1 的主机具有到数据库主机的 SSH 隧道设置，因此授予repl@localhost是适当的，因为主机将其视为本地流量。

主从都在：10.1.26-MariaDB-0+deb9u1

mariadb
Martin Hope
artfulrobot
Asked: 2013-02-28 02:35:18 +0800 CST
  • 2

我有一张这样的桌子：

+----------------------------+
| user  | date     | current |
+----------------------------+
|    1  | 20010101 |       1 |
|    1  | 20020202 |       1 |
|    1  | 20030303 |       1 |
|    2  | 20010101 |       1 |
|    2  | 20020202 |       1 |
+----------------------------+

我想更新当前列，以便只有每个用户的最新条目有 1。

我曾计划：

UPDATE table t1 SET current = 0 
WHERE EXISTS (
    SELECT id
      FROM table t2
     WHERE t2.user = t1.user
       AND t2.date > t1.date )
)

但这告诉我ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

有没有办法绕过 MySQL 的这个限制？

mysql
Martin Hope
artfulrobot
Asked: 2012-12-21 05:22:44 +0800 CST
  • 0

我有一个entity_relationship描述实体之间关系的表。每个关系将涉及 2 个以上的实体。

在某些情况下，可以说一个实体是选区的一个组成部分。这存储在entity_relationship表中。

然后我打算将实体添加到基于此的关系中：如果实体Joe Bloggs是选区University of Life的组成部分，而University of Life本身是选区Made Up Universities的组成部分，那么我会将 Joe Bloggs 添加到第二个关系中作为隐含成分。

当所有隐含成分关系都被填充时（通过运行查询直到没有记录被添加）我将有一个快速的方法来找出谁链接到什么而不需要在那个阶段进行递归。

entity_relationship 表如下所示：

+-----------------+---------------+
| Field           | Type          |
+-----------------+---------------+
| entity_id       | int(10)       |
| relationship_id | int(10)       |
| type            | enum(...)     |
+-----------------+---------------+

并且有钥匙（目前有很多钥匙，正在努力优化！）

+------------+----------+--------------+-----------------+-------------+------+
| Non_unique | Key_name | Seq_in_index | Column_name     | Cardinality | Null |
+------------+----------+--------------+-----------------+-------------+------+
|          0 | PRIMARY  |            1 | entity_id       |      179429 |      |
|          0 | PRIMARY  |            2 | relationship_id |      179429 |      |
|          1 | r_t_e    |            1 | relationship_id |      179429 |      |
|          1 | r_t_e    |            2 | type            |      179429 | YES  |
|          1 | r_t_e    |            3 | entity_id       |      179429 |      |
|          1 | t_r_e    |            1 | type            |           8 | YES  |
|          1 | t_r_e    |            2 | relationship_id |      179429 |      |
|          1 | t_r_e    |            3 | entity_id       |      179429 |      |
|          1 | t_e_r    |            1 | type            |           6 | YES  |
|          1 | t_e_r    |            2 | entity_id       |      179429 |      |
|          1 | t_e_r    |            3 | relationship_id |      179429 |      |
+------------+----------+--------------+-----------------+-------------+------+

然后我正在尝试的查询是：

INSERT INTO entity_relationship  
SELECT lt.entity_id entity_id, 
       py.relationship_id relationship_id,
       'implied-constituent' `type` 
FROM entity_relationship lt,
     entity_relationship ly,
     entity_relationship pt,
     entity_relationship py
WHERE lt.type='constituent'
  AND lt.relationship_id = ly.relationship_id
  AND ly.type='constituency'
  AND ly.entity_id = pt.entity_id 
  AND pt.type='constituent' 
  AND pt.relationship_id = py.relationship_id 
  AND py.type='constituency';

问题是这需要 42 秒才能运行（即使查询导致插入零行）。EXPLAIN 的输出（在 SELECT 上）显示：

+--------+-----+----+-------+---+------------------------+-----+------------------------+
| s._type|table|type|key    |len|ref                     |rows |Extra                   |
+--------+-----+----+-------+---+------------------------+-----+------------------------+
| SIMPLE |lt   |ref |t_r_e  |2  |const                   |89714|Using where; Using index|
| SIMPLE |ly   |ref |r_t_e  |6  |lt.relationship_id,const|    1|Using where; Using index|
| SIMPLE |pt   |ref |PRIMARY|4  |ly.entity_id            |    1|Using where             |
| SIMPLE |py   |ref |r_t_e  |6  |pt.relationship_id,const|    1|Using where; Using index|
+--------+-----+----+-------+---+------------------------+-----+------------------------+

看起来不错——除了第三行没有说使用索引。

任何人都可以看到优化这个的方法吗？

mysql optimization
Martin Hope
artfulrobot
Asked: 2012-09-25 07:43:26 +0800 CST
  • 1

我正在将数据从人员和组织是单独表的模式迁移到人员和组织都被视为联系人（他们有很多共同点）的模式。目前，people 表有大约 90k 条记录，与 10k 组织的 80k 关系。

新模式：

Contact           Relationship              (Contact table again)
--------          -------------             ---------------------
cid      11----0< cid_a              /---11 cid
name              cid_b          >0-/       name
                  details
                  start_date
                  end_date
                  relationship_type

如果我想查询Wilma的当前关系（假设 Wilma 有cid = 2），我可以在 上设置 2 个键relationship，一个(cid_a, cid_b)和(cid_b, cid_a)。

SELECT friend.name FROM contact friend, relationship 
WHERE 
     (
       ( cid_a = 2 AND cid_b = friend.cid )
       OR
       ( cid_b = 2 AND cid_a = friend.cid )
     ) 
     AND
     ( start_date IS NULL OR start_date <= CURRENT_DATE )
     AND
     ( end_date IS NULL OR end_date >= CURRENT_DATE )

但我不确定它是否有效，因为重复的键会很长。

联系人可能与各种组织、其他联系人等有 3、4 或更多关系，例如

  • 威尔玛是X大学的学生——
  • 威尔玛是Y组织的成员
  • 威尔玛之前是Z组织的联系人
  • 威尔玛嫁给了弗雷德。

这是独一真道吗？或者什么都不是？！

join optimization
Martin Hope
artfulrobot
Asked: 2012-06-01 01:28:21 +0800 CST
  • 4

我有一个包含基本联系信息的人员表。我有一堆标准可以从人员表中选择 Id 值（称为 pid 的字段）。

  1. 我想要匹配（（标准 1 或标准 2）和标准 3）的记录。

  2. 我希望条件语句将列添加到结果中。

  3. 我需要一种标准方法来组合查询，因为我正在制作“搜索构建器”；所以我不能优化每个组合。可以假设每个标准将返回零或一行。

EG1：我第一次尝试

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       (criterion3 SELECT) c3
 WHERE people.id=c3.pid
   AND ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

这太慢了——它从来没有在我允许的那一刻返回记录！

EG2：接下来我尝试了

SELECT people.*
  FROM people,
       (       (criterion1 SELECT) 
         UNION (criterion2 SELECT) ) c12
 WHERE people.id=c12.pid
   AND people.id IN (criterion3 SELECT)

这个在 0.6 秒内返回 2000+ 行，这是可以接受的。但是你不能从标准 3 添加任何列，也不能从 c1 和 c2 访问列，真的，因为一个会掩盖另一个。

EG3：出于兴趣，我将 c12 移到 WHERE 子句中：

SELECT people.*
  FROM people
 WHERE id IN (criterion3 SELECT)
   AND id IN ( (criterion1 SELECT) 
               UNION (criterion2 SELECT) )

但是，在我终止查询之前，这再次抛出了超过一分钟。

EG4：所以我回到了 EG2 代码，做了一件丑陋的事情：

SELECT * 
  FROM ( all the EG2 SQL ) src
       LEFT JOIN (criterion1 SELECT) c1 ON src.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON src.id=c2.pid

所以 c1 和 c2 查询被添加了两次！这将在 6 秒内返回（相同数量的）结果以及额外的字段。太慢了，真的，但至少它运行。而且真的很丑！

谁能给我任何指示来解释这一切？当他们都在做同样的事情时，为什么 EG3 比 EG1/EG4 快得多？为什么运行查询两次（EG4）比运行一次（EG1）要快？！

编辑：使用 MySQL 5.1，InnoDB 表。

mysql optimization
Martin Hope
artfulrobot
Asked: 2011-11-23 02:03:43 +0800 CST
  • 4

我有一个主从设置。令人尴尬的是，我设法在 Slave 上执行了一些数据更改语句（跨 3 个表的 INSERT 和 UPDATE）。显然这停止了从属复制。

我不需要我不小心在 Slave 上进行的更新。我怎样才能让它与主同步？

mysql replication