1 简介：

如果沒有索引，您的程序将可能很慢，因为每个操作都必须扫描整个表。

因此，索引是开发人员最有兴趣的话题，但也是最复杂的一个。

有很多关于数据库索引的內容，但是这里不想重复它们。这里只是分享更多非凡的方法和您以前可能沒有见过的功能。

索引章节將向您展示许多特殊的索引方法，例如唯一性软刪除表的约束，多列索引的简单规则，查找和刪除的方法刪除未使用的索引等等。

2 函数和表达式的索引

    SELECT * FROM users WHERE lower(email) = 'demo@hello.com';
    -- MySQL
    CREATE INDEX users_email ON users ((lower(email)));

    -- PostgreSQL
    CREATE INDEX users_email ON users (lower(email));

大多开发人员感到困惑的是，他們在列上的索引在转换时沒有被通过函数或表达式使用。

Google 搜索将有产生无数 StackOverflow 文章申明在这些情況下您不能使用索引，但此类信息是错误的！

你可以在函数或表达式上创建专门的索引，只要确切的在您的 WHERE 中应用了相同的转换。

更多内容请搜索： Function-Based Indexes

3 查找未使用的索引

    -- MySQL
    SELECT
     object_schema AS `database`,
     object_name AS `table`,
     index_name AS `index`,
     count_star as `io_operations`
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE object_schema NOT IN('mysql', 'performance_schema') AND index_name
    IS NOT NULL AND index_name != 'PRIMARY'
    ORDER BY object_schema, object_name, index_name;

    -- PostgreSQL
    SELECT
     pg_tables.schemaname AS schema,
     pg_tables.tablename AS table,
     pg_stat_all_indexes.indexrelname AS index,
     pg_stat_all_indexes.idx_scan AS number_of_scans,
     pg_stat_all_indexes.idx_tup_read AS tuples_read,
     pg_stat_all_indexes.idx_tup_fetch AS tuples_fetched
    FROM pg_tables
    LEFT JOIN pg_class ON(pg_tables.tablename = pg_class.relname)
    LEFT JOIN pg_index ON(pg_class.oid = pg_index.indrelid)
    LEFT JOIN pg_stat_all_indexes USING(indexrelid)
    WHERE pg_tables.schemaname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY pg_tables.schemaname, pg_tables.tablename;

根据功能或业务需求对模式进行多次更改后，以前的索引可能不再时候更改查询。

你可能已添加了很多新的缺失索引但从未刪除已有索引。

因为每次插入都需要更新所有，您浪費时间更新的索引却不再需要。

数据库保留有关索引使用情況的统计信息，您可以随着时间比较这些信息以帮助查找未使用的情況，然後可以刪除的索引。

注意： 某些索引访问可能不會更新统计信息。你可以评估这些結果要慎重而不是盲目相信它們。

4 安全删除未使用的索引 仅支持MYSQL

    -- MySQL
    ALTER TABLE website_visits ALTER INDEX twitter_referrals INVISIBLE;
    ALTER TABLE website_visits ALTER INDEX twitter_referrals VISIBLE;

刪除架构中未使用的索引始终是一项紧张的任务。如果你错了，一些查询会非常慢，

直到您在凌晨 3 点收到关于速度变慢的通知並且已重新创建索引。

对于巨大的表，创建索引可能需要超过 30分钟或数小時。

最后，您选择安全的方法并將索引保持为没有一个人想在晚上被传呼。但不是刪除它，你可以让它不可见所以它是不再使用了。

如果不再需要它，您可以安全地刪除它或使其可见或一秒內再次删除。这是一个更安全的方法。

尽管您可能仍需要在凌晨 3 点，你对它的重要性的猜測是错误的，但它會更容易解決現在的问题。

5 通过包含更多列的仅索引操作

    SELECT SUM(price) FROM invoices WHERE customer_id = 42 AND year = 2022;
    -- MySQL
    CREATE INDEX ON invoices (customer_id, year, price);
    -- PostgreSQL
    CREATE INDEX ON invoices (customer_id, year) INCLUDE (price);    

索引的功能从快速查找行扩展到更高的性能数据库可以做的优化。

当索引包含行过滤的所有列时添加和查詢的选定列，它不必再查找任何行。

完整的查询仅由來自索引的信息执行。它被称为仅索引查询，是您可以获得的性能最高的查询。

使用 PostgreSQL，您可以使用包含附加列的 INCLUDES 选项，而在 MySQL 上你必须將它们添加到索引。
因此，对于 MySQL，您不能將此技巧用于唯一索引作为唯一条件將被附加列更改.

6 减少索引大小的部分索引 仅支持PGSQL

    SELECT * FROM invoices WHERE specialcase_law3421 = TRUE;
    -- PostgreSQL
    CREATE INDEX invoices_specialcase_law3421
    ON invoices (specialcase_law3421)
    WHERE specialcase_law3421 = TRUE;    

一个鮮为人知的特性是限制索引行的部分索引，因为许多数据库不支持它。

您可以代替每行都有一个条目的标准索引指定应该包括哪些。

值得注意的是像发票这样的用例

示例：只有少数发票需要以不同方式处理，因为规则 #3421

需要一個新的公式來计算稅收。列上的标准索引必須包括数千个不受法律影响的发票条目，而只有少数条目一百个受影响的人。

尽管索引大小小很多，但仍用以查询在他們的 WHERE 中使用部分索引条件。此外，未受影响的行不必更新減少插入时间的部分索引。

7 唯一性约束的部分索引

    -- MySQL
    CREATE UNIQUE INDEX email_unique
    ON users (email, (IF(deleted_at, NULL, 1)));
    -- PostgreSQL
    CREATE UNIQUE INDEX email_unique
    ON users (email)
    WHERE deleted_at IS NULL;

在某些情況下，您想要例如用戶的电子邮件地址是唯一的，但您也在使用软刪除。

已刪除用戶使用的任何电子邮件地址（deleted_at 不再為空）将导致錯誤，因为电子邮件地址存在多次。

使用部分索引，您可以將唯一索引的行限制為仅未刪除的用戶。沒有任何重复了。

对于不支持部分索引的 MySQL，您可以模拟该行为。一个特別的是，当至少有一个空值时，索引可以有许多具有相同信息条目下。

因此，需要为索引转换值：

刪除联系人時，其在索引中的 deleted_at 时间戳將替换为NULL 值允許多次使用相同的电子邮件地址。

当联系人人未被刪除时，使用静态值代替 NULL 值区強制执行独特的违规行为，例如多个值 (info@example.com, 1)电子邮件地址。

更多相关内容请搜索： Unique Indexes With Some Rows Excluded

8 通配符搜索的索引支持 仅支持PGSQL

    SELECT * FROM speakers WHERE name LIKE '%Tobias%';
    -- PostgreSQL
    CREATE EXTENSION pg_trgm;
    CREATE INDEX trgm_idx ON speakers USING GIN (name gin_trgm_ops);

每个数据库都可以使用索引进行尾随通配符搜索。

但是当你使用PostgreSQL，您还可以在使用索引的同时进行前导通配符搜索：

您的列的文本被分成许多將使用的三個字符長的序列（三元組）搜素时。

像 %Tobias% 这样的通配符將搜索具有 trigrams 的值Tob、obi、bia 和 ias 具有完整的索引支持。

然後在第二步中过滤所有找到的行检查它们是否真的包含子字符串 Tobias 因为这些三字母組也可以用于不同的組合以形成另一个名称.

9 多列索引规则

    SELECT *
    FROM shop_articles
    WHERE tenant_id = 6382 AND category = 'books' AND price < 49.99;
    CREATE INDEX shop_articles_key ON shop_articles (
     tenant_id, -- type = equality, different_values = 7293
     category, -- type = equality, different_values = 628
     price -- type = range, different_values = 142
    );

将多列索引排序的复杂规则简化为一个简单的技巧是是复杂的。我可以就这个主題写一本完整的书。

你会感到兴奋吗？你可以用三个简单的规则覆盖 80% 的多列索引需求。基础的想法是最大程度地減少每个添加列的可能表行數。

这个称之为选择性。

最好从与之相等性检查进行比较並具有最独特的价值观。这样，您可以非常快速地減少剩余行数。

然后，您可以使用更多相等性检查列來減少不同的数量值。

范围列，如日期或數字，通常最好在最后使用。

注意： 这是一组简单但仍然非常准确的多列规则指标。它們不适用於所有用例，但我相信它在 80% 的场景都有效。

不要让我受制于这些规则；每当它们不符合您的查询时跳过它们。

10 散列索引以减小索引大小

    -- PostgreSQL
    CREATE INDEX invoices_uniqid ON invoices USING HASH (uniqid)

当您计划仅使用 UUID 或字符串等相等性检查來搜索列时，您可以使用哈希索引优化索引。

与标准的 b-tree 索引相比，哈希索引对于插入和查询数据会快一些。但是指数要小得多，这是一个显著的改进。

然而，唯一的哈希索引还沒有受支持，因此您不能使用它们來強制执行唯一约束。

注意： 在 PostgreSQL 10 之前，不鼓励哈希索引。如果你仍然使用过时的版本，你不应该使用它们或升级你的数据库。

11 排序依据的降序索引

    SELECT *
    FROM highscores
    ORDER BY score DESC, created_at ASC
    LIMIT 10;
    CREATE INDEX highscores_correct ON highscores (
     score DESC, created_at ASC
    );

创建多列索引加速排序查询是很复杂的。多数情況在这个情況下，您的索引將被使用，您不必做任何特別的事情。

无论您使用查询中的升序或降序不会有任何区别。
但混合订单查詢更复杂，因为尽管您的索引已对数据进行排序被使用过。

要跳过这个不必要的操作，您必须使用相同的方式创建索引排序順序作为您的查询.

更多内容请搜索： Descending Indexes

12 针对未索引列的隐式条件

    -- Before
    SELECT *
    FROM shipments
    WHERE status = 'open' AND transportinsurance = 1;
    -- After
    SELECT *
    FROM shipments
    WHERE status = 'open' AND transportinsurance = 1 AND type = IN(3, 6,11);

为例如创建正确的索引用戶定定义的数据过滤或很少使用的条件是最具挑战性的任务之一。

您不能为每一列创建索引，必須猜哪一个是最重要的。但是您仍然会错过一些导致查询緩慢的信息。

一个优秀的方法是將“隐式條件”添加到索引支持的查詢中，並且不要改变結果。

添加这些条件只是为了帮助数据库找到查询数据的最有效方式。

在示例中，我们正在搜索有运输的未结算货物。

这个精切的查询沒有匹配的多列索引，但您知道一些业务规则数据库并不知道。
比如只允许运送特定类型的包裹保险，因此将这些类型添加到查询中。

多列索引(status, type) 现在可以用來过滤比单個索引 (status) 更多的数据本來可以做的。