mysql学习教程，从入门到精通，SQL 创建索引(CREATE INDEX 语句)（35）

embedded/2024/12/23 2:05:20/

1、SQL 创建索引(CREATE INDEX 语句)

在SQL中，创建索引（CREATE INDEX）是一种用于提高数据库查询性能的方法。索引类似于书的目录，通过它可以更快地定位到表中的特定行。以下是一个创建索引的示例，以及对其各部分的解释。
假设我们有一个名为 employees 的表，表结构如下：

sql">CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,hire_date DATE
);

我们希望在 last_name 列上创建一个索引，以便更快地执行基于姓氏的查询。以下是创建索引的SQL语句：

sql">CREATE INDEX idx_last_name
ON employees (last_name);

解释

  • CREATE INDEX：这是创建索引的SQL命令。
  • idx_last_name：这是索引的名称。在创建索引时，为其指定一个有意义的名称是一个好习惯，这有助于在将来管理索引。
  • ON employees (last_name)：这指定了索引要创建的表和列。在这个例子中，索引将创建在 employees 表的 last_name 列上。

创建复合索引

有时，你可能希望在一个或多个列上创建复合索引，以优化涉及这些列的多个列的查询。例如，我们希望在 last_name 和 first_name 列上创建一个复合索引：

sql">CREATE INDEX idx_full_name
ON employees (last_name, first_name);

创建唯一索引

如果你希望确保列中的值是唯一的，可以创建唯一索引。例如，我们希望在 employee_id 列上创建一个唯一索引（虽然在这个例子中 employee_id 已经是主键，自然唯一，但这里仅作为示例）：

sql">CREATE UNIQUE INDEX idx_unique_employee_id
ON employees (employee_id);

创建索引时的注意事项

  1. 性能：虽然索引可以显著提高查询性能，但它们也会增加插入、更新和删除操作的成本，因为数据库需要维护索引。
  2. 空间：索引会占用额外的存储空间。
  3. 选择性：索引在选择性高的列上更有效。选择性是指列中不同值的数量与总行数之比。例如，性别列（只有“男”和“女”两个值）的选择性较低，而身份证号列的选择性较高。

当然可以，以下是几个使用CREATE INDEX语句创建索引的具体案例：

案例一：创建普通索引

假设我们有一个名为students的表，包含以下字段：student_id（学生ID）、first_name（名字）、last_name（姓氏）、grade（年级）和enrollment_date（入学日期）。我们希望在last_name字段上创建一个索引，以便加快基于姓氏的查询速度。

sql">CREATE INDEX idx_last_name
ON students (last_name);

案例二：创建复合索引

现在，我们希望在students表上创建一个复合索引，以便能够同时加快基于姓氏和名字的查询速度。复合索引是在多个字段上创建的索引，当查询条件包含这些字段时，数据库可以利用复合索引来提高查询性能。

sql">CREATE INDEX idx_full_name
ON students (last_name, first_name);

案例三：创建唯一索引

假设我们有一个名为courses的表，包含以下字段：course_id（课程ID）、course_name（课程名称）和course_code（课程代码）。我们希望确保course_code字段中的值是唯一的，因此可以在该字段上创建一个唯一索引。

sql">CREATE UNIQUE INDEX idx_unique_course_code
ON courses (course_code);

唯一索引不仅提高了查询性能，还确保了数据的唯一性。如果尝试插入一个已经存在的course_code值，数据库将拒绝该操作并返回错误。

案例四：在大数据表上创建索引

假设我们有一个名为orders的表，该表包含大量的订单数据，并且我们经常需要基于customer_id和order_date字段来查询订单。为了提高查询性能，我们可以在这两个字段上创建一个复合索引。

sql">CREATE INDEX idx_customer_order_date
ON orders (customer_id, order_date);

这个复合索引将帮助数据库更快地定位到特定客户的订单，特别是当查询条件同时包含customer_id和order_date字段时。

注意事项

  1. 索引的选择性：索引在选择性高的列上更有效。选择性是指列中不同值的数量与总行数之比。例如，在性别列上创建索引可能并不是一个好的选择，因为性别列通常只有几个不同的值（如“男”和“女”）。
  2. 索引的维护成本：虽然索引可以显著提高查询性能，但它们也会增加插入、更新和删除操作的成本。因为数据库需要在这些操作发生时维护索引。
  3. 索引的数量：过多的索引可能会导致性能问题。每个索引都需要占用额外的存储空间，并且在插入、更新和删除操作时都需要进行维护。因此，应该根据实际需求合理创建索引。
    通过以上案例，我们可以看到CREATE INDEX语句在优化数据库查询性能方面的重要作用。在实际应用中，应该根据具体的查询需求和数据特点来选择合适的索引类型和字段。
    以下是一些实际的SQL创建索引的案例，这些案例基于不同的数据库表和查询需求，展示了如何使用CREATE INDEX语句来优化查询性能。

案例一：在员工表上创建索引

假设我们有一个名为employees的表，该表存储了公司的员工信息。我们经常在last_name和department_id字段上进行查询，因此希望在这两个字段上创建索引。

sql">-- 在last_name字段上创建普通索引
CREATE INDEX idx_employees_last_name
ON employees (last_name);-- 在department_id字段上创建普通索引
CREATE INDEX idx_employees_department_id
ON employees (department_id);
-- 或者，我们可以创建一个复合索引，以同时优化基于last_name和department_id的查询
CREATE INDEX idx_employees_last_name_department_id
ON employees (last_name, department_id);

案例二：在订单表上创建索引

假设我们有一个名为orders的表，该表存储了公司的订单信息。我们经常在customer_id、order_date和status字段上进行查询和过滤，因此希望在这些字段上创建索引。

sql">-- 在customer_id字段上创建普通索引
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
-- 在order_date字段上创建普通索引
CREATE INDEX idx_orders_order_date
ON orders (order_date);-- 在status字段上创建普通索引（如果status字段的选择性较高）
CREATE INDEX idx_orders_status
ON orders (status);-- 或者，我们可以根据查询需求创建复合索引
-- 例如，优化基于customer_id和order_date的查询
CREATE INDEX idx_orders_customer_id_order_date
ON orders (customer_id, order_date);

案例三：在商品表上创建唯一索引

假设我们有一个名为products的表，该表存储了公司的商品信息。我们希望确保每个商品的product_code是唯一的，因此可以在该字段上创建唯一索引。

sql">-- 在product_code字段上创建唯一索引
CREATE UNIQUE INDEX idx_products_unique_product_code
ON products (product_code);

案例四：在文章表上创建全文索引

假设我们有一个名为articles的表，该表存储了公司的文章信息。我们经常需要在文章的content字段中进行全文搜索，因此可以在该字段上创建全文索引（注意，全文索引的创建方式可能因数据库系统而异，以下以MySQL为例）。

sql">-- 在MySQL中创建全文索引（需要确保数据库和表支持全文索引功能）
CREATE FULLTEXT INDEX idx_articles_fulltext_content
ON articles (content);

注意事项

  1. 索引的选择性：索引在选择性高的列上更有效。选择性是指列中不同值的数量与总行数之比。例如，在性别或布尔类型的列上创建索引可能并不是一个好的选择，因为这些列的选择性通常很低。
  2. 索引的维护成本：虽然索引可以显著提高查询性能，但它们也会增加插入、更新和删除操作的成本。因为数据库需要在这些操作发生时维护索引。因此，在创建索引时，需要权衡查询性能和数据维护成本。
  3. 索引的数量：过多的索引可能会导致性能问题。每个索引都需要占用额外的存储空间，并且在插入、更新和删除操作时都需要进行维护。因此，应该根据实际需求合理创建索引。
    以上案例展示了如何在不同的数据库表和字段上创建索引，以优化查询性能。在实际应用中，应该根据具体的查询需求和数据特点来选择合适的索引类型和字段。

http://www.ppmy.cn/embedded/125000.html

相关文章

【数据结构】【顺序表算法】 删除特定范围内的元素

【数据结构】【顺序表算法】 删除特定范围内的元素

题目&#xff1a;从顺序表中删除其值在给定值s和t之间&#xff08;s<t&#xff09;的所有元素&#xff0c;若s或t不合理或顺序表为空&#xff0c;则显示错误信息并退出运行 bool Del_s_t(SqList &L,ElemType s,ElemType t){int i,k0;if(L.length0||s>t){return fals…
阅读更多...
【Java】—— 数据结构与集合源码：数据结构概述与线性表、二叉树

【Java】—— 数据结构与集合源码：数据结构概述与线性表、二叉树

1. 数据结构剖析 我们举一个形象的例子来理解数据结构的作用&#xff1a; 战场&#xff1a;程序运行所需的软件、硬件环境 敌人&#xff1a;项目或模块的功能需求 指挥官&#xff1a;编写程序的程序员 士兵和装备&#xff1a;一行一行的代码 战术和策略&#xff1a;数据结构 上…
阅读更多...
C++——string类

C++——string类

目录 前言&#xff1a; 一、C语言中字符串的缺陷 二、string常见接口极其调用 1.构造及遍历 2.、、[]、<<、>>运算符重载 3.string自身属性接口 4.增删查改接口 三、模拟实现 前言&#xff1a; 严格来说&#xff0c;string类并不属于STL中的一部分&#xf…
阅读更多...
Linux 线程

Linux 线程

目录 一.线程的概念 1.什么是线程&#xff1f; 2.Linux 系统对线程的实现 线程比进程要更轻量化体现在什么方面&#xff1f;&#xff1f; 线程切换较进程切换效率高的原因&#xff1f;&#xff1f; ①cache缓存&#xff08;主要原因&#xff09; ②寄存器的刷新&#xff…
阅读更多...
Git管理远程仓库

Git管理远程仓库

添加远程仓库 要新增远程&#xff0c;请在终端上存储存储库的目录中使用 git remote add 命令。 git remote add 命令采用两个参数&#xff1a; 远程名称&#xff08;例如 origin&#xff09;远程 URL&#xff08;例如 https://github.com/OWNER/REPOSITORY.git&#xff09;…
阅读更多...
微知-一个不错的rpm大全网站，临时找rpm包的好地方(rpmfind.net)

微知-一个不错的rpm大全网站，临时找rpm包的好地方(rpmfind.net)

背景 经常要安装某个rpm包&#xff0c;在默认的镜像源找不到。这个网站可以直接下载安装&#xff0c;能够部分解决问题。 有些场景下载后还有依赖包&#xff0c;不影响大环境的情况&#xff0c;可以以 -nodeps安装&#xff0c;然后尝试使用。 另外rpmfind.net网站能够work的本…
阅读更多...
C++笔记之标准库和boost库中bind占位符_1的写法差异

C++笔记之标准库和boost库中bind占位符_1的写法差异

C++笔记之标准库和boost库中bind占位符_1的写法差异 code review! 参考博文： C++新特性探究(十五)：bind 在C++中，_1 和 std::placeholders::_1 都用于表示占位符，但它们有不同的上下文：
阅读更多...
顺序表和链表的区别

顺序表和链表的区别

顺序表和链表的区别 不同点顺序表链表&#xff08;带头双向循环&#xff09;存储空间物理上一定连续逻辑上连续物理上不一定连续随机访问&#xff08;用下标随机访问&#xff09;支持&#xff1a;O(1)不支持&#xff1a;O(N)任意位置插入或者删除元素可能需要搬移元素&#xf…
阅读更多...
最新文章

Copyright @ 2022~2023