likes
comments
collection
share

求求了，数据库索引就该这么建！

作者站长头像
站长
· 阅读数 13

前言

网上各种文档教程看了一堆，总是在说“建索引”、“建索引”，各种索引类型，可为啥我建了索引，它还是慢呢！

别着急，耐心看完，你一定有所收获。

求求了，数据库索引就该这么建！

正文

废话不多说，直接上要点。

1. 在经常用于查询条件的字段上创建索引

这类字段在where条件里面经常用到，如果不加索引，每次查询都要全表扫描，随着数据量增长查询效率迅速下降。加了索引可以避免扫描大部分数据。

CREATE INDEX idx_status ON table_name(status);
SELECT * FROM table_name WHERE status = 1;

以下条件查询大概率会走索引：

  1. 等值查询：例如 WHERE column_name = 'value'
  2. 范围查询： WHERE column_name BETWEEN 'value1' AND 'value2' 或 WHERE column_name > 'value'，这种查询也可能会走索引，特别是当被索引的列的范围较小时。
  3. 部分模糊查询：主要是右模糊查询，例如 WHERE column_name LIKE 'value%'
  4. 排序和分组：当查询涉及到 ORDER BY 和 GROUP BY 子句时，如果被排序或分组的列上有索引，数据库可能会选择使用索引来加速查询。
  5. 连接查询：在连接查询中，如果连接的字段有索引，数据库可能会利用这些索引来优化查询。

求求了，数据库索引就该这么建！

2. 对于查找单条记录的字段创建唯一索引

唯一键对应的数据只有一条,要找到这条记录只能全表扫描,数据量大会很慢。加唯一索引可以直接定位到记录。

CREATE UNIQUE INDEX idx_uid ON table_name(uid);
SELECT * FROM table_name WHERE uid = 123;

3. 在表与表的关联键上创建索引

表JOIN时依靠这个键进行关联,如果不索引就会扫描全表去对比键值,数据量越大关联效率越低。加索引可以快速匹配关联键值。

CREATE INDEX idx_post_id ON comments(post_id);

SELECT * FROM comments WHERE post_id = 12345;

4. 在经常需要排序的字段建立索引

排序时如果不加索引,需要全表扫描获取所有记录再排序,数据量大时排序开销巨大。索引可以按序直接读取记录,避免全表扫描。

CREATE INDEX idx_create_time ON table_name(create_time);
SELECT * FROM table_name ORDER BY create_time DESC;

5. 对于频繁用于left join的表的关联键创建索引

左连表时都会全表扫描右表去匹配关联条件,数据量大时非常慢。加索引可以快速过滤出匹配的记录。

CREATE INDEX idx_user_id ON table_a(user_id); 

SELECT * FROM table_a
LEFT JOIN table_b ON table_a.user_id = table_b.user_id;

6. 在表的数据量巨大时对主键创建索引

主键默认会有索引,如果去掉索引,增删改查所有操作都会变慢,特别是主键查询需要全表扫描。

CREATE INDEX idx_id ON large_table(id);

7. 对于组合查询的字段建立联合索引

单列过滤效果不好时,加联合索引可以多列组合过滤提升效率。

这里的使用场景太广泛，请注意。

求求了，数据库索引就该这么建！

假设我们有一个示例表格products，包含以下列：

  • product_id - 产品ID（唯一标识）
  • product_name - 产品名称
  • category - 产品类别

我们希望对product_name列进行右模糊查询，以找到以特定字符串结尾的产品名称。

在这种情况下，我们可以创建一个联合索引，将category列放在product_name之前。这是因为MySQL和一些其他数据库系统的索引优化算法能更好地利用联合索引的前缀部分。下面是创建联合索引的示例 SQL 语句：

CREATE INDEX idx_product_name_category ON products (product_name, category);

以上命令将在products表上创建一个名为idx_product_name_category的联合索引，该索引涵盖了product_name和category两列。

现在，假设我们要执行右模糊查询，找到以字符串"xyz"开头且属于某个特定类别的产品名称：

SELECT product_name
FROM products
WHERE product_name LIKE 'xyz%' AND category = 'some_category';

在这个查询中，MySQL可以有效地利用联合索引idx_product_name_category，首先根据category过滤记录，然后在满足category条件的记录中使用联合索引来加速product_name的右模糊查询。

8. 在文本字段创建前缀索引

上面一点其实说到了模糊查询的问题，针对模糊查询，有几点需要提及：

  • 左模糊查询：如果LIKE查询的模式以通配符开头（例如：'%abc'），大多数数据库系统将无法使用普通B-tree索引来加速查询。这是因为B-tree索引对于模式开头的通配符查询效果较差。
  • 右模糊查询：如果LIKE查询的模式以通配符结尾（例如：'abc%'），大多数数据库系统可以使用普通B-tree索引来加速查询。这是因为B-tree索引可以快速找到匹配模式前缀的记录。
  • 左右模糊查询：如果LIKE查询的模式既以通配符开头又以通配符结尾（例如：'%abc%'），通常无法有效使用普通B-tree索引。
  • 不以通配符开头或结尾的模糊查询：如果LIKE查询的模式不以通配符开头或结尾（例如：'abc'或'abcde'），并且不包含其他特殊字符（如转义字符``），大多数数据库系统可以使用普通B-tree索引来加速查询。

因此在给文字字段创建索引后，应尽量：

要使LIKE查询走索引，可以考虑以下方法：

  • 右模糊查询：如果可能，将通配符放在模式的末尾，例如：'abc%'。这样，数据库系统可以使用B-tree索引来快速定位匹配前缀的记录。
  • 使用其他索引类型：一些数据库系统支持针对模糊查询的其他特殊索引类型。例如，PostgreSQL支持Trigram索引，用于处理模糊查询。
  • 搜索引擎：使用外部搜索引擎，比如ELasticSearch等
CREATE INDEX idx_title_prefix ON table(title(10));
SELECT * FROM table WHERE title LIKE 'key%';

收尾

讲到这里其实也差不多了，但还有一个至关重要的问题，怎样才能知道索引有没有生效？

这就得用到EXPLAIN大法了。

我们下文分解。

求求了，数据库索引就该这么建！