数据库索引创建索引

在数据库中，创建索引是提高查询性能的重要一步。
最常见的情况是对WHERE子句中包含的字段建立索引。
例如，创建一个名为mytable的表，如下所示：

sqlCREATETABLEmytable(idserialprimarykey,category_idintnotnulldefault0,user_idintnotnulldefault0,adddateintnotnulldefault0);

如果常用查询语句类似：

sqlSELECT*FROMmytableWHEREcategory_id=1;

sqlSELECT*FROMmytableWHEREcategory_id=1;

类别ID创建一个简单索引：

sqlCREATEINDEXmytable_categoryidONmytable(category_id);

对于多个选择条件，例如：

sqlSELECT*FROMmytableWHEREcategory_id=1ANDuser_id=2;

>

错误的方法是创建单一选择条件。
user_ids列表而不是单个索引，需要创建多个索引：

sqlCREATEINDEXmytable_categoryid_useridONmytable(category_id,user_id);

命名时，一般使用“表名_字段名1_字段名2”。
您可以快速识别正在使用的引是否正确。

要测试您的数据库是否使用这些索引，请使用EXPLAIN命令。
以Postgres为例：

sqlEXPLAINSELECT*FROMmytableWHEREcategory_id=1ANDuser_id=2;

Postgres返回：

plaintextNOTICE:QUERYPLAN:IndexScanusingmytable_categoryid_useridonmytable(cost=0.00..2.02rows=1width=16)

这表明数据库正在使用该索引并将使用创建的第二个索引。
命名约定有助于快速识别正确的索引。

如果有ORDERBY子句，则在该字段上创建索引。
示例：

sqlSELECT*FROMmytableWHEREcategory_id=1ANDuser_id=2ORDERBYadddateDESC;

在adddate上创建索引：

sqlCREATEINDEXmytable_categoryid_userid_adddateONmytable(category_id,user_id,adddate);

创建索引名称时，它被截断为“mytable_categoryid_userid_addda”。

测试结果：

sqlEXPLAINSELECT*FROMmytableWHEREcategory_id=1ANDuser_id=2ORDERBYadddateDESC;

返回值：

IndexScanusingmytable_categoryid_userid_adddaonmytable(cost=0.00..2.02rows=1width=16)

数据库执行不必要的排序操作，降低了性能它显示了你的情况正在做。
提供了其他提示以提高效率。

通过调整查询语句，数据库优化器可以使用预期的索引，例如：

sqlEXPLAINSELECT*FROMmytableWHEREcategory_id=1ANDuser_id=2ORDERBYcategory_idDESC,user_idDESC,adddateDESC;

返回返回值：

plaintextNOTICE:QUERYPLAN:IndexScanBackwardusingmytable_categoryid_userid_adddaonmytable(cost=0.00..2.02rows=1width=16)

使用预期索引并从后到前避免排序读取。

在处理复杂查询时，例如组合多个表查询，尤其是当WHERE子句中的字段来自多个表时，请避免这种情况。
如果这是不可避免的，请为每个表创建单独的索引并使用EXPLAIN进行验证。
必须使用。
当使用预期索引时，查询性能会提高。

请注意，索引过多会减慢更新和插入速度，因为必须更新每个索引文件。
对于经常更新和插入的表，不需要为很少使用的WHERE子句创建单独的索引。
对于小表，排序成本低廉，并且不需要额外的索引。

索引创建的基本原理是根据查询需求建立索引，并通过EXPLAIN命令验证其使用情况。
每个数据库都有不同的优化器，因此性能优化必须根据您的情况进行定制。

如果您的数据库表不大，您可能不需要立即创建索引。
可以使用OPTIMIZETABLE等命令来优化现有表。
这意味着索引创建和使用决策应基于您的特定需求和查询性能。

扩展信息

索引是一种对数据库表中一个或多个列的值进行排序的结构可用于快速访问数据库表中的特定信息。

三大准则11个技巧,手把手教你创建数据库索引

创建有效的数据库索引需要遵循三个关键准则和11条建议，以确保最佳的查询性能和数据保留效率。
本文以B+树索引来详细分析索引创建的最佳实践，同时也指出了不同索引类型的相关条件和限制。
第一的，索引创建应该基于工作负载而不是建表，以确保数据库执行的所有SQL语句都是一致的。
第二，索引创建应考虑SQL结构以优化数据放置。
最左优先原则指导如何高效利用索引快速查找数据，范围条件帮助快速访问数据。
避免查询中的排序是使用索引的关键。
对于分区表，本文深入讨论创建和维护区域分区的方法。
此外，使用功能索引和条件索引以及索引组合技术是提高查询性能的有效方法。
通过在外键上创建索引；表连接性能可以显着提高，尤其是在支持索引的数据库中。
最后，确保最佳整体系统性能的指标数量；创建索引时需要考虑限制，包括磁盘空间使用和写入性能影响。
综合应用上述原理和技术；可以实现数据库索引的优化配置；从而提高查询性能和数据存储效率。