sql怎样创建索引提升查询效率 sql索引创建与查询优化的基础技巧

正确创建索引可显著提升查询效率，应选择WHERE、JOIN、ORDER BY和GROUP BY中高频使用的高选择性列，优先为数值和日期类型建索引，合理设计复合索引并遵循最左前缀原则，通过EXPLaiN分析执行计划，关注type、key、rows和Extra字段，确保索引被有效利用，避免全表扫描和额外排序，平衡读写性能开销。

sql索引是提升数据库查询效率的利器，它本质上就像一本书的目录，能让你快速定位到需要的信息，而不是从头到尾翻阅。正确地创建和使用索引，能够显著减少数据库在查找数据时所需扫描的数据量，从而极大加快查询响应速度。但它并非万能药，滥用或错误使用反而会拖慢写入操作，甚至适得其反。

解决方案

创建索引的核心在于识别那些频繁用于查询条件的列。这通常包括

WHERE

子句中的过滤条件、

JOIN

操作中的连接键、以及

ORDER BY

和

GROUP BY

子句中涉及的列。语法上，这很简单：

CREATE INDEX index_name ON table_name (column1, column2, ...);

。关键在于选择合适的列，并理解不同类型索引的适用场景，比如单列索引、复合索引、唯一索引等。索引的创建是一个权衡的过程，它会加速读操作，但会增加写操作（插入、更新、删除）的开销，因为每次数据变动，索引也需要同步更新。所以，对那些写入远多于读取的表，或者数据量非常小的表，索引带来的收益可能不值得其维护成本。

什么样的列最适合创建索引？

选择哪些列来创建索引，这事儿真得好好琢磨一下。我个人的经验是，那些在查询中经常作为筛选条件的列，也就是

WHERE

子句后面跟着的，或者用于连接不同表的

JOIN

条件中的列，是首选。比如，如果你经常按用户ID查询订单，那么用户ID列就非常适合建索引。

此外，列的“选择性”或者说“基数”是个非常重要的考量。简单来说，就是该列中不重复值的数量占总行数的比例。高选择性的列（比如身份证号、电子邮件地址）通常能带来更好的索引效果，因为索引能更快地缩小查找范围。如果一个列的值大部分都一样（比如性别，只有男和女），那它的选择性就很低，即使建了索引，数据库可能还是觉得全表扫描更划算，因为索引能排除掉的数据量太小了。

再一个，数据类型也有些影响。数值类型和日期类型的索引通常比字符串类型更高效，因为它们比较起来更快。对于字符串，如果只查询前缀匹配（

LIKE 'abc%'

），索引也能派上用场，但如果是中间匹配（

LIKE '%abc%'

）或者后缀匹配，索引就基本无能为力了。

最后，别忘了那些经常用于

ORDER BY

和

GROUP BY

的列。这些操作在没有索引的情况下，往往需要对数据进行排序，这可是个耗时的大工程。有了合适的索引，数据库可以直接利用索引的有序性，避免额外的排序操作。

复合索引（联合索引）在多条件查询中如何发挥作用？

复合索引，也就是在多个列上创建的索引，它在处理多条件查询时显得尤为重要，但这里有个非常关键的“潜规则”——最左前缀原则。这个原则决定了复合索引能否被有效利用。

想象一下，你有一个索引建在

(A, B, C)

这三列上。那么，这个索引可以支持以下几种查询模式：

1. 只查询A列：索引能用上。
2. 查询A和B列：索引能用上。
3. 查询A、B和C列：索引也能用上。

但如果你的查询条件是：

1. 只查询B列：索引就用不上了，或者只能用上索引的一部分，效率不高。
2. 查询B和C列：同样，索引也用不上。
3. 查询A和C列：索引只能用到A列的部分，C列则需要回表或全表扫描。

这就是最左前缀原则的体现：数据库会从索引的最左边的列开始匹配。只有当查询条件包含了索引的最左边列，或者从最左边列开始连续的若干列时，这个复合索引才能发挥作用。因此，在设计复合索引时，把那些最常用作查询条件的列放在前面，是提升效率的关键。

举个例子，如果你的系统经常需要根据

用户ID

和

订单状态

来查询订单，那么创建一个

(用户ID, 订单状态)

的复合索引通常比单独创建两个单列索引效果更好。因为它能同时满足

WHERE 用户ID = ?

和

WHERE 用户ID = ? AND 订单状态 = ?

这两种查询模式。

如何通过SQL查询优化器和执行计划分析索引效果？

仅仅创建了索引，并不意味着万事大吉。数据库的查询优化器会根据各种因素（比如数据量、索引统计信息、查询复杂度）来决定是否使用索引以及如何使用。这时候，查看查询的执行计划就成了我们诊断和优化查询性能的“X光片”。

几乎所有的关系型数据库都提供了查看执行计划的命令，比如mysql的

EXPLAIN

，postgresql的

EXPLAIN ANALYZE

，SQL Server的

SHOWPLAN

或图形化执行计划。

当你对一个查询语句执行

EXPLAIN

（以MySQL为例）后，你会得到一张表格，里面包含了优化器对该查询的执行策略。这里有几个关键的字段需要关注：

• type

  : 这是最重要的字段之一，它表示了查找数据的方式。

  • ALL

    : 全表扫描，这是最糟糕的情况，通常意味着索引没起作用或者没建好。

  • index

    : 全索引扫描，比全表扫描好，但仍需扫描整个索引。

  • range

    : 范围扫描，比如

    WHERE id > 100

    ，效率不错。

  • ref

    : 非唯一索引查找，通过索引查找匹配的值。

  • eq_ref

    : 唯一索引查找，通常用于连接操作，效率很高。

  • const

    ,

    system

    : 查询优化器直接将查询转换为常量，效率极高。

• key

  : 实际使用的索引名称。如果这里是

  NULL

  ，说明没有使用索引。

• key_len

  : 使用的索引字节长度，可以判断复合索引中哪些列被使用了。

• rows

  : 预估需要扫描的行数，这个值越小越好。

• Extra

  : 额外信息，这里面有很多有用的提示。比如：

  • using filesort

    : 数据需要额外排序，通常可以通过添加索引来避免。

  • Using temporary

    : 需要创建临时表来存储中间结果，也应尽量避免。

  • Using index

    : 表示查询是“覆盖索引”查询，即所有需要的数据都可以在索引中找到，无需回表，效率极高。

  • Using where

    : 表示使用了

    WHERE

    子句来过滤数据。

通过分析这些信息，你可以判断索引是否被有效利用，是进行了全表扫描、全索引扫描，还是精准的索引查找。如果发现

type

是

ALL

，

rows

值很大，或者

Extra

中出现

Using filesort

或

Using temporary

，那么就说明你的查询可能存在性能瓶颈，这时候就需要考虑是否需要调整索引策略，或者优化sql语句了。有时候，一个小小的索引调整，就能让一个跑了十几秒的查询瞬间完成，那种感觉，真的挺棒的。