Hasan Can Saral's questions

我有下表：

CREATE TABLE transactions
(
    id                                    NUMERIC(20, 0)                           NOT NULL DEFAULT NEXTVAL('transactions_sequence') PRIMARY KEY,
    transaction_date                      TIMESTAMP      DEFAULT NULL              NULL,
    transaction_type                      VARCHAR(255)   DEFAULT NULL              NULL,
    merchant_id                           VARCHAR(255)   DEFAULT NULL              NULL,
    transaction_type                      VARCHAR(255)   DEFAULT NULL              NULL,
    -- Some more columns here
);

以及以下索引：

CREATE INDEX transactions_merchant_id_idx ON transactions (merchant_id, transaction_type, transaction_date DESC, id) WHERE merchant_id IS NOT NULL;

我有以下疑问：

SELECT id, transaction_date
FROM transactions
WHERE merchant_id = 'some_merchant_id'
  AND transaction_type = 'a'
  AND transaction_date >= '2025-01-01'
  AND transaction_date < '2025-03-28'
ORDER BY transaction_date DESC
LIMIT 100

这个查询工作正常并且我得到了仅索引扫描：

Limit  (cost=0.29..7.47 rows=1 width=13) (actual time=1.119..1.120 rows=0 loops=1)
  ->  Index Scan using transactions_transaction_type_idx on transactions  (cost=0.29..7.47 rows=1 width=13) (actual time=1.118..1.118 rows=0 loops=1)
        Index Cond: (((transaction_type)::text = 'a'::text) AND (transaction_date >= '2025-01-01 00:00:00'::timestamp without time zone) AND (transaction_date < '2025-03-28 00:00:00'::timestamp without time zone))
        Filter: ((merchant_id)::text = 'some_merchant_id'::text)
Planning Time: 0.311 ms
Execution Time: 1.139 ms

但是，当我需要transaction_type独立结果时：

SELECT id, transaction_date
FROM transactions
WHERE merchant_id = 'some_merchant_id'
  AND transaction_date >= '2025-01-01'
  AND transaction_date < '2025-03-28'
ORDER BY transaction_date DESC
LIMIT 100

我仍然只得到索引扫描：

Limit  (cost=38.08..38.19 rows=44 width=13) (actual time=0.108..0.115 rows=47 loops=1)
  ->  Sort  (cost=38.08..38.19 rows=44 width=13) (actual time=0.107..0.110 rows=47 loops=1)
        Sort Key: transaction_date DESC
        Sort Method: quicksort  Memory: 27kB
        ->  Index Only Scan using transactions_merchant_id_idx on transactions  (cost=0.29..36.88 rows=44 width=13) (actual time=0.029..0.093 rows=47 loops=1)
              Index Cond: ((merchant_id = 'some_merchant_id'::text) AND (transaction_date >= '2025-01-01 00:00:00'::timestamp without time zone) AND (transaction_date < '2025-03-28 00:00:00'::timestamp without time zone))
              Heap Fetches: 0
Planning Time: 0.228 ms
Execution Time: 0.161 ms

我确实有一个所有潜在 transaction_type 值的列表，所以我最初认为这会更好：

SELECT id, transaction_date
FROM transactions
WHERE merchant_id = 'some_merchant_id'
  AND transaction_type IN ('a', 'b', 'c', ...) -- all the potential values here
  AND transaction_date >= '2025-01-01'
  AND transaction_date < '2025-03-28'
ORDER BY transaction_date DESC
LIMIT 100

但是，根据子句中的值的数量，我可能会在查询计划中IN获得额外的值：filter

Limit  (cost=38.29..38.40 rows=43 width=13) (actual time=0.110..0.118 rows=47 loops=1)
  ->  Sort  (cost=38.29..38.40 rows=43 width=13) (actual time=0.109..0.112 rows=47 loops=1)
        Sort Key: transaction_date DESC
        Sort Method: quicksort  Memory: 27kB
        ->  Index Only Scan using transactions_merchant_id_idx on transactions  (cost=0.31..37.13 rows=43 width=13) (actual time=0.030..0.097 rows=47 loops=1)
              Index Cond: ((merchant_id = 'some_merchant_id'::text) AND (transaction_date >= '2025-01-01 00:00:00'::timestamp without time zone) AND (transaction_date < '2025-03-28 00:00:00'::timestamp without time zone))
"              Filter: ((transaction_type)::text = ANY ('{a,b,c,d,e,f}'::text[]))"
              Heap Fetches: 0
Planning Time: 0.340 ms
Execution Time: 0.142 ms

因此，即使我跳过中间transaction_type列，我的索引也会被使用。但是，使用哪种查询更好呢？是使用包含所有潜在值的IN on 查询transaction_type，还是不使用过滤器？如果没有过滤器，我的索引如何仍然可用transaction_type？

我有下表：

CREATE TABLE users (
 first_name VARCHAR(256)  DEFAULT NULL NULL,
 last_name  VARCHAR(256)  DEFAULT NULL NULL,
 full_name  VARCHAR(1024) GENERATED ALWAYS AS
        (CASE
             WHEN first_name IS NULL AND
                  last_name IS NULL THEN NULL
             ELSE
                 (TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''))) END) STORED;
);

我有以下“规范化”功能：

CREATE OR REPLACE FUNCTION NORMALIZE_FUNC(IN source VARCHAR)
    RETURNS VARCHAR
    LANGUAGE PLPGSQL
AS
$$
BEGIN

    source := (SELECT COALESCE(source, ''));

    source := (SELECT REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      LOWER(source),
                      'ş', 's'),
                      'ç', 'c'),
                      'ı', 'i'),
                      'ü', 'u'),
                      'ö', 'o'),
                      'ğ', 'g'))::VARCHAR;

    RETURN source;

END
$$
IMMUTABLE;

以及以下索引和查询，其中soundex()来自fuzzystrmatch扩展：

CREATE INDEX users_full_name_soundex_idx ON users (SOUNDEX(NORMALIZE_FUNC(full_name))) WHERE full_name IS NOT NULL;

SELECT *
FROM users
WHERE SOUNDEX(NORMALIZE_FUNC(full_name)) = SOUNDEX(NORMALIZE_FUNC('name surname'))

我对表进行了顺序扫描users。

当我尝试不使用用户定义时NORMALIZE_FUNC：

CREATE INDEX users_full_name_soundex_idx ON users (SOUNDEX(full_name)) WHERE full_name IS NOT NULL;

SELECT *
FROM users
WHERE SOUNDEX(full_name) = SOUNDEX('name surname')

我看到users_full_name_soundex_idx使用了索引，并且查询很快。

我错过了什么？

我有下表：

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PARIMARY KEY,
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id)
);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

我想将其分区transaction_date如下：

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence'),
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT transactions_pkey PRIMARY KEY (id, transaction_date),
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id, transaction_date)
) PARTITION BY RANGE (transaction_date);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

这种分区transaction_id并不是真正唯一的，而是每个分区唯一的，因为唯一键必须具有分区键，即transaction_date。

使用此表的应用程序不知道分区，并使用 23505 SQL 状态执行更新而不是插入。

我每天插入约 30 万行，当前表大小约为 3000 万行。

以下触发器也是如此：

CREATE OR REPLACE FUNCTION transactions_validate() RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF EXISTS (SELECT id FROM transactions WHERE transaction_id = NEW.transaction_id AND id <> NEW.id) THEN
        RAISE EXCEPTION USING ERRCODE = '23505';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER transactions_validate_trigger
    BEFORE INSERT OR UPDATE
    ON transactions
    FOR EACH ROW
EXECUTE PROCEDURE transactions_validate();

一个坏主意？它对性能有何影响？

假设我有一张桌子：

CREATE TABLE transactions (
    id NUMERIC(20, 0) PRIMARY_KEY,
    account_id VARCHAR(512),
    ...
    transaction_date TIMESTAMP
);

因此，对于 SQL Server，索引：

CREATE NONCLUSTERED INDEX my_index ON transactions (account_id, transaction_date, id);

和一个查询：

SELECT id FROM transactions 
WHERE account_id = '123' 
AND transaction_date > '2023-01-01'

执行索引查找，这意味着查询是从索引完成的，而不是引用表，因为它需要的所有数据都存在于表中。如果我的索引是：

CREATE NONCLUSTERED INDEX my_index ON transactions (account_id, transaction_date);

查询将转到表来完成查询（索引扫描）。以下哪个数据库：

1. 甲骨文
2. MySQL
3. PostgreSQL

支持以上行为吗？

我有以下表格：

CREATE TABLE acl_sid (
 id bigserial not null primary key,
 principal boolean not null,
 sid varchar(100) not null,
 constraint unique_uk_1 unique(sid,principal) );

CREATE TABLE users(
 id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('users_sequence') PRIMARY KEY,
 email VARCHAR(255) NOT NULL,
    -- some ~10 columns omitted
 );

CREATE TABLE authorities (
 user_id NUMERIC(20, 0) NOT NULL,
 sid_id  BIGINT         NOT NULL,
 CONSTRAINT fk_authorities_users FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE,
 CONSTRAINT fk_authorities_sids FOREIGN KEY (sid_id) REFERENCES acl_sid (id) ON UPDATE CASCADE ON DELETE CASCADE );


CREATE SEQUENCE decidables_sequence START WITH 1 NO MAXVALUE;


CREATE TABLE decidables (
 id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('decidables_sequence') PRIMARY KEY,
 assigned_to NUMERIC(20, 0) DEFAULT NULL NULL,
 CONSTRAINT fk_decidables_assigned_to FOREIGN KEY (assigned_to) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL );

CREATE TABLE decidables_transactions (
 decidable_id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
 transaction_date TIMESTAMP DEFAULT NULL NULL,
 transaction_id VARCHAR(255) DEFAULT NULL,
 -- some 100 columns here
 CONSTRAINT uq_decidables_transactions_transaction_id UNIQUE (transaction_id),
 CONSTRAINT fk_decidables_transactions_decidable_id FOREIGN KEY (decidable_id) REFERENCES decidables (id) ON UPDATE CASCADE ON DELETE CASCADE );

CREATE TABLE decidables_transactions_mv (
 decidable_id NUMERIC(20, 0) NOT NULL,
 transaction_date TIMESTAMP DEFAULT NULL NULL,
 -- some 10 columns here, excerpt from decidables_transactions
 assigned_to NUMERIC(20, 0) DEFAULT NULL NULL,
 CONSTRAINT fk_decidables_transactions_mv_decidable_id_idx FOREIGN KEY (decidable_id) REFERENCES decidables (id) ON UPDATE CASCADE ON DELETE CASCADE,
 CONSTRAINT fk_decidables_transactions_mv_assigned_to_idx FOREIGN KEY (assigned_to) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL );

CREATE INDEX decidable_id_mv_idx ON decidables_transactions_mv (decidable_id);
-- few more indexes on omitted columns, that are not being used in the following queries
CREATE INDEX transaction_date_mv_idx ON decidables_transactions_mv (transaction_date, decidable_id);

我有这个键集分页查询，它的性能非常好：

SELECT d.*,
       t.*,
       u.id    AS ass_to_id,
       u.email AS ass_to_email,
       NULL    AS ass_to_authority
FROM decidables d
         JOIN decidables_transactions t ON d.id = t.decidable_id
         LEFT JOIN users u ON d.assigned_to = u.id AND u.deleted_at IS NULL
         -- some more joins omitted for simplicity
         JOIN (SELECT t.decidable_id, t.transaction_date
               FROM decidables_transactions_mv t
               ORDER BY t.transaction_date DESC, t.decidable_id DESC
               LIMIT 10) AS decidables_table ON d.id = decidables_table.decidable_id
ORDER BY t.transaction_date DESC, t.decidable_id DESC;

但是当我实际尝试选择用户的权限时：

SELECT d.*,
       t.*,
       u.id  AS ass_to_id,
       u.email AS ass_to_email,
       s.sid AS ass_to_authority
FROM decidables d
         JOIN decidables_transactions t ON d.id = t.decidable_id
         LEFT JOIN users u ON d.assigned_to = u.id AND u.deleted_at IS NULL
         LEFT JOIN authorities a ON a.user_id = u.id
         LEFT JOIN acl_sid s ON a.sid_id = s.id
         -- some more joins omitted for simplicity
         JOIN (SELECT t.decidable_id, t.transaction_date
               FROM decidables_transactions_mv t
               ORDER BY t.transaction_date DESC, t.decidable_id DESC
               LIMIT 10) AS decidables_table ON d.id = decidables_table.decidable_id
ORDER BY t.transaction_date DESC, t.decidable_id DESC;

开始需要几分钟。我在 ,和中有 ~10 行users~10 行和acl_sid~200 万行。decidablesdecidables_transactionsdecidables_transactions_mv

执行计划如下：

• 快速查询
• 慢查询

为什么查询的执行效果非常不同并且速度显着减慢？

我有以下两个表：

CREATE TABLE entries
(
    id                  NUMBER(20, 0)                           NOT NULL PRIMARY KEY,        
    first_name          VARCHAR2(512) DEFAULT NULL              NULL,
    last_name           VARCHAR2(512) DEFAULT NULL              NULL,
    last_updated_at     TIMESTAMP     DEFAULT NULL              NULL
);

CREATE TABLE entry_details
(
    id       NUMBER(20, 0)              NOT NULL PRIMARY KEY,
    entry_id NUMBER(20, 0)              NOT NULL,
    value    VARCHAR2(512) DEFAULT NULL NULL,
    CONSTRAINT fk_details_entry_id FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
);

只要or表last_updated_at中有更改/删除，我就需要更新，所以我有这个触发器：entriesentry_details

CREATE OR REPLACE TRIGGER detail_delete_trigger
    BEFORE DELETE
    ON entry_details
    FOR EACH ROW
BEGIN
    UPDATE entries
    SET last_updated_at = CURRENT_TIMESTAMP
    WHERE id = :OLD.entry_id;
END;

entry_details当直接从中删除时，这很好用。但是， ORA-04091: entries table is mutating当 delete from entriestable 级联到entry_details. 我努力了：

1. PRAGMA AUTONOMOUS_TRANSACTIONS屈服于ORA-00060: deadlock detected while waiting for resource
2. 使用AFTER DELETE再次给出的触发器，表正在发生变异错误
3. 无法使用INSTEAD OF DELETE，因为它不适用于表格ORA-25002: cannot create INSTEAD OF triggers on tables
4. 我删除CASCADE并添加了以下触发器：

ALTER TABLE entry_details DROP CONSTRAINT fk_details_entry_id;
ALTER TABLE entry_detailsADD CONSTRAINT fk_details_entry_idFOREIGN KEY (entry_id) REFERENCES entries(id);

CREATE OR REPLACE TRIGGER aml_entries_delete_trigger
    BEFORE DELETE -- or AFTER DELETE, same result
    ON entries
    FOR EACH ROW
BEGIN
    DELETE FROM entry_details WHERE entry_id = :OLD.id;
END;

但我仍然得到ORA-04091 table is mutating trigger/function may not see错误。我该如何克服这个问题？

我有一张桌子：

CREATE TABLE IF NOT EXISTS blacklist
(
    id                    NUMERIC(20, 0)                           NOT NULL DEFAULT NEXTVAL('blacklist_sequence') PRIMARY KEY,
    device_id             VARCHAR(512)   DEFAULT NULL              NULL,
    phone_number          VARCHAR(512)   DEFAULT NULL              NULL
);

CREATE INDEX index_phone ON blacklist (phone_number);
CREATE INDEX index_device ON blacklist (device_id);

我想使用这个黑名单来查看是否有客户device_id在phone_number其中。所以我的查询是：

SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END
FROM blacklist b
         JOIN
     (SELECT id
      FROM blacklist 
      WHERE device_id = :deviceId
      UNION
      SELECT id
      FROM blacklist 
      WHERE phoneNumber = :phoneNumber) filtered_table ON b.id = filtered_table.id

基本上将OR条件拆分为子查询UNION以使用索引，但我发现我们有太多的黑名单行phone_number填充了行，而使用device_id.

所以，我正在寻找一种进一步并行化的方法，以便在device_id找到匹配项时，我不必继续寻找收集和条件UNION的结果phone_number（因为那时我已经知道我SELECT将返回TRUE）到想出一个更大的COUNT，因为我只对行是否存在感兴趣。

如何重写此查询以实现它？虽然我主要是在寻找 Postgres 解决方案，但我有兴趣了解其他数据库可以做什么。

我有这张桌子：

CREATE TABLE transactions
(
    id                                    NUMERIC(20, 0)              NOT NULL PRIMARY KEY,
    amount                                NUMERIC(18, 2) DEFAULT NULL NULL,
    -- Some 100 columns
    customer_msisdn                       VARCHAR(255)   DEFAULT NULL              NULL,
    customer_email                        VARCHAR(255)   DEFAULT NULL              NULL,
    payment_date                          DATETIME2                   NOT NULL
);

CREATE NONCLUSTERED INDEX msisdn_idx ON transactions (customer_msisdn, payment_date, id);
CREATE NONCLUSTERED INDEX email_idx ON transactions (customer_email, payment_date, id);

我每月索引大约 100 万行。非常频繁地，我需要选择过去 3 个月的每笔交易customer_msisdn或每笔交易customer_email，即 99% 的时间 50 - 1000 条记录。

这是我的查询以获得更多见解：

SELECT t.*
FROM transactions t
         JOIN (SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate
               UNION
               SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

而且我觉得既然:endDate总是现在（如果不是，可以容忍错误）并且:startDate总是三个月前，我还有一些改进的空间。这就是我的想法：

创建一个带有过滤器的索引视图payment_date：

CREATE VIEW [dbo].transactions_iv
    WITH SCHEMABINDING AS
SELECT [t].id,
       -- All the rows
       [t].customer_msisdn,
       [t].customer_phone,
       [t].payment_date
FROM [dbo].[transactions] [t]
WHERE [t].payment_date >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP);

和我的索引：

CREATE NONCLUSTERED INDEX msisdn_iv_idx ON transactions_iv (customer_msisdn, id);
CREATE NONCLUSTERED INDEX phone_iv_idx ON transactions_iv (customer_phone, id);

并从查询中完全删除AND t.payment_date >= :startDate AND t.payment_date < :endDate子句。查询变为：

SELECT t.*
FROM transactions_iv t
         JOIN (SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
               UNION
               SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

由于该视图仅包含最近 3 个月的交易，我假设索引也是如此。这个假设正确吗？是否会仅更新索引以涵盖最近 3 个月的记录，并且我的性能会得到提升吗？

另一种选择是：

1. 创建另一个相同的表，
2. 用主表上的触发器填充它
3. 使用 cron 作业，每晚删除 3 个月以上的记录。

此选项与前一个选项相比如何？

我有以下表格：

CREATE TABLE base_event (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_by ... -- some columns
);

CREATE TABLE transaction_events (
    event_id BIGINT UNSIGNED NOT NULL,
    transaction_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    merchant_id BIGINT UNSIGNED NULL DEFAULT NULL,   
    merchant_city VARCHAR (...) NULL DEFAULT NULL, -- Denormalize
    customer_id BIGINT UNSIGNED NULL DEFAULT NULL,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL, -- Denormalize
    ...
    FOREIGN KEY (event_id) REFERENCES base_event(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
);

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL,
    ...
);

CREATE TABLE merchants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...
);

我的SELECT：

(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.customer_ip_address = 'abc' AND t.transaction_time > 'abc')
UNION DISTINCT
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.merchant_city = 'abc' AND t.transaction_time > 'abc')

我的索引是：

ALTER TABLE transaction_events 
ADD INDEX index_1 (customer_ip_address, transaction_time),
ADD INDEX index_2 (merchant_city, transaction_time);

1. 我的查询采用这种形式以避免OR.
2. 为了索引，我已经在一定程度上进行了非规范化。
3. 我不需要base_event为此查询引用我的表。
4. 与transaction_eventstocustomers和的关系merchants不是 1 对 1 而是 1 对 0 或 1。

我的问题：

1. 我可以摆脱通配符，但transaction_events有大约 20 列（这有助于创建任何进一步的索引以加快查询速度吗？
2. 我是否需要放置任何其他复合索引（可能引用我的 FK）来进一步改进此查询？