关于MySQL索引选择，先看看这十条建议

2024-03-06 20:44:28 浏览数 (1)

1. 根据查询频率选择索引

如果某个字段在查询中经常被用作过滤条件，那么在这个字段上创建索引可能会提高查询性能。例如，如果你经常根据员工的姓氏查询，那么在姓氏字段上创建索引可能是有益的。

示例：

假设电商系统的商品表（products）包含下面的列

product_id

product_name

price

description

1

Product A

100

This is a great product A.

2

Product B

200

This is a great product B.

3

Product C

300

This is a great product C.

...

...

...

...

商品名称（product_name）和价格（price）列可能会经常被作为查询条件，因而适合创建索引，但是描述列（description）可能不适合创建索引。

2. 根据数据唯一性选择索引

如果表中的某个字段包含唯一值（例如，员工ID或社会保障号），那么在这个字段上创建索引可能会提高查询性能。唯一索引不仅可以提高查询性能，还可以防止插入重复的数据。

示例：

当然可以，以下是一个用户表的示例：

user_id

user_name

gender

1

anne

Female

2

windeal

Male

3

lipl

Male

在这个用户表中，user_id和user_name都可以作为索引，因为它们都具有唯一性。

gender不适合作为索引，因为它的值可能不是唯一的（即有多个用户可能都是"Male"或"Female"）。在大多数情况下，索引应该是唯一的，以便快速有效地查找特定的记录。

3. 根据数据分布和查询范围选择索引

如果表中的数据分布不均匀，或者查询通常涉及到数据的一个小范围，那么在这个范围内的字段上创建索引可能会提高查询性能。

示例：

例如，你有一个订单信息表，如果你经常查询过去一周的订单，那么在订单日期字段上创建索引可能是有益的。

order_id

customer_id

product_id

order_date

1

101

201

2022-01-01

2

102

202

2022-01-02

3

103

203

2022-01-03

4

104

204

2022-01-04

5

105

205

2022-01-05

在这个表中，如果你的查询通常涉及到最近的订单（例如，"查找过去一周的所有订单"），那么在order_date字段上创建索引可能会提高查询性能。

创建索引后，数据库可以快速定位到特定日期范围的订单，而不需要扫描整个表。这对于大型表来说尤其重要，因为全表扫描可能会非常耗时。

4. 使用短索引

如果可能，应该使用短索引，尽量选择数据类型小的列作为索引。

例如，选择INT而不是VARCHAR。因为数据类型小的列，索引的大小就小，查询速度就快。

这是因为数据库对短索引的搜索速度更快，而且短索引占用的磁盘空间也更少。

示例：

以下是一个员工表的示例：

employee_id

employee_name

birth_date

1

windeal

1990-01-01

2

lipl

1991-02-02

3

anne

1992-03-03

4

jane

1993-04-04

在这个表中，如果你经常需要根据员工ID（employee_id）或员工姓名（employee_name）来查找员工，那么你可能会考虑在这两个字段上创建索引。

然而，employee_id是一个整数字段，而employee_name是一个字符串字段。整数字段通常占用的空间比字符串字段少，因此在employee_id上创建索引可能会更有效。这个索引会占用更少的磁盘空间，而且查询速度也可能更快。

请注意，这并不意味着你永远不应该在字符串字段上创建索引。如果你的应用经常需要根据员工姓名来查找员工，那么在employee_name上创建索引可能仍然是有益的。你应该根据你的应用的实际需求来选择索引。

5. 利用前缀索引

如果某个字符串列的前几个字符已经足够区分大部分值，那么就可以只对这个列的前缀部分建立索引，而不是整个字符串。这样可以减少索引的大小，提高查询速度。

示例：

以下是一个员工表的示例：

employee_id

employee_addr

1

GuangDong.ShenZhen.FuTian

2

ZheJiang.HangZhou.XiHu

3

JiangSu.NanJing.JianYe

4

SiChuan.ChengDu.JinNiu

在这个表中，employee_addr字段是一个字符串字段，可能会很长。如果你经常需要根据员工的地址来查找员工，那么你可能会考虑在这个字段上创建索引。

然而，如果地址的前几个字符已经足够区分大部分员工，那么你可以只对这个字段的前缀部分创建索引。例如，你可以创建一个只包含前10个字符的前缀索引。

在MySQL中，你可以使用以下语句创建前缀索引：

代码语言：javascript复制
CREATE INDEX idx_employee_addr ON employees (employee_addr(10));

这个索引会占用更少的磁盘空间，而且查询速度也可能更快。然而，这种方法的缺点是，如果你需要根据地址的后半部分来查找员工，那么这个索引可能就不太有用了。你应该根据你的应用的实际需求来选择索引。

6. 多列索引（联合索引）

如果经常需要通过多个列来进行查询，那么可以考虑创建多列索引。但是要注意，多列索引并不等于多个单列索引。

假设我们有一个名为 orders 的表，它有 customer_id 和 order_date 两个列。如果我们经常需要通过 customer_id 和 order_date 来查询数据，那么我们可以创建一个多列索引。

在 SQL 中，创建多列索引的语法如下：

代码语言：javascript复制
CREATE INDEX idx_orders_on_customer_id_and_order_date 
ON orders (customer_id, order_date);

这将创建一个名为 idx_orders_on_customer_id_and_order_date 的索引，它基于 orders 表的 customer_id 和 order_date 列。

然而，这并不等于创建了两个单列索引，一个基于 customer_id，另一个基于 order_date。多列索引是基于列值的组合进行索引的。

例如，

  • 如果你有一个查询是这样的： SELECT * FROM orders WHERE customer_id = 1 AND order_date = '2020-01-01'; 这个查询将能够利用我们创建的多列索引。
  • 如果你的查询只涉及到其中一个列，例如： SELECT * FROM orders WHERE customer_id = 1; 那么这个查询只能利用到 customer_id 的部分索引。
  • 如果你的查询只涉及到 order_date，例如： SELECT * FROM orders WHERE order_date = '2020-01-01'; 那么这个查询将无法利用我们创建的多列索引，因为多列索引是按照列的顺序来建立的，order_date 是索引中的第二列，不能单独被利用。

7. 外键索引

在外键上创建索引可以加快JOIN操作的速度。

假设我们有两个表，orders 和 customers，其中 orders 表有一个 customer_id 列，这是 customers 表的外键。

在 SQL 中，我们可以在 customer_id 列上创建一个索引，以加快 JOIN 操作的速度。创建索引的语法如下：

代码语言：javascript复制
CREATE INDEX idx_orders_on_customer_id ON orders (customer_id);

这将创建一个名为 idx_orders_on_customer_id 的索引，它基于 orders 表的 customer_id 列。

现在，当我们执行以下 JOIN 操作时：

代码语言：javascript复制
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

数据库可以使用 idx_orders_on_customer_id 索引来快速找到匹配的行，从而加快 JOIN 操作的速度。

8. 考虑索引的排序

索引的排序顺序会影响查询的性能。例如，如果你经常执行范围查询（如WHERE column BETWEEN value1 AND value2），那么应该选择能够在这个范围内提供最快搜索速度的排序顺序。

假设我们有一个 orders 表，其中有一个 order_date 列。如果我们经常需要查询在特定日期范围内的订单，例如：

代码语言：javascript复制
SELECT * FROM orders WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31';

那么我们可以在 order_date 列上创建一个索引，并选择一个能够在这个日期范围内提供最快搜索速度的排序顺序。在大多数数据库系统中，日期类型的列通常默认按照升序排序，这对于日期范围查询来说是最有效的。

创建索引的 SQL 语句如下：

代码语言：javascript复制
CREATE INDEX idx_orders_on_order_date ON orders (order_date);

这将创建一个名为 idx_orders_on_order_date 的索引，它基于 orders 表的 order_date 列，并默认按照升序排序。

现在，当我们执行上述的日期范围查询时，数据库可以使用 idx_orders_on_order_date 索引来快速找到在指定日期范围内的订单，从而提高查询的性能。

9. 避免过度索引

每个额外的索引都会占用额外的磁盘空间，并且在插入和更新数据时需要额外的时间来维护索引。因此，应该避免对不经常用于搜索或排序的列进行索引。

10. 避免在频繁更新的列上建立索引

如果一个列的值经常变化，那么每次值变化都需要更新索引，这将导致数据库的性能下降。因此，尽量避免在更新频率高的列上建立索引。

示例：

假设我们有一个 users 表，其中有一个 last_login 列，这个列记录了用户最后一次登录的时间。这个列的值可能会经常变化，因为每次用户登录时，都会更新这个列的值。

在这种情况下，如果我们在 last_login 列上创建一个索引，那么每次用户登录时，都需要更新这个索引。这将导致数据库的性能下降，因为更新索引是一个相对耗时的操作。

因此，尽管在 last_login 列上创建一个索引可能会加快某些查询的速度（例如，查找最近登录的用户），但由于这个列的值经常变化，所以最好避免在这个列上创建索引。

以下是在 last_login 列上创建索引的 SQL 语句，但是我们通常不推荐这样做：

代码语言：javascript复制
CREATE INDEX idx_users_on_last_login ON users (last_login);

相反，我们应该考虑在其他不太可能经常变化的列上创建索引，例如 email 或 username 列。

mysql 索引

0 人点赞