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

news/2024/10/10 7:09:09/

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/news/1536895.html

相关文章

ESP32利用WebServer进行设备配置

ESP32利用WebServer进行设备配置

目标需求 利用esp32的WebServer功能&#xff0c;展示一个网页&#xff0c;对里面的参数进行配置&#xff0c;并以json文本格式保存到flash里面。 1、定义HTML const char index_html[] PROGMEM R"rawliteral( <!DOCTYPE html> <html lang"en"> …
阅读更多...
JavaScript 网页设计案例与技巧

JavaScript 网页设计案例与技巧

简单的 JavaScript 网页设计案例 展示了动态改变页面内容和样式的效果： 一、HTML 结构 <!DOCTYPE html> <!-- 这是文档类型声明，告知浏览器以 HTML5 标准解析页面 --> <html lang="en"> <!-- 整个网页的根元素，lang="en" 表示页…
阅读更多...
爬虫学习实战

爬虫学习实战

1.大学排名爬取&#xff08;简单&#xff09; # -*- coding: utf-8 -*- from selenium import webdriver from selenium.webdriver.common.by import By import timeurl https://daxue.911cha.com/ driver webdriver.Chrome() # 设置隐式等待&#xff0c;等待页面加载完成 d…
阅读更多...
【hot100-java】二叉树中的最大路径和

【hot100-java】二叉树中的最大路径和

二叉树篇 easy. /*** Definition for a binary tree node.* public class TreeNode {* int val;* TreeNode left;* TreeNode right;* TreeNode() {}* TreeNode(int val) { this.val val; }* TreeNode(int val, TreeNode left, TreeNode right) {* …
阅读更多...
Run the FPGA VI 选项的作用

Run the FPGA VI 选项的作用

Run the FPGA VI 选项的作用是决定当主机 VI 运行时&#xff0c;FPGA VI 是否会自动运行。 具体作用&#xff1a; 勾选 “Run the FPGA VI”&#xff1a; 当主机 VI 执行时&#xff0c;如果 FPGA VI 没有正在运行&#xff0c;系统将自动启动并运行该 FPGA VI。 这可以确保 FPG…
阅读更多...
【unity游戏开发】彻底理解AnimatorStateInfo，获取真实动画长度

【unity游戏开发】彻底理解AnimatorStateInfo，获取真实动画长度

前言 前置知识&#xff1a;设置参数后&#xff0c;下一个循环才会切换对应动画&#xff0c;所以在下一个循环获取真实的动画长度 AnimatorStateInfo是结构体&#xff01;值类型&#xff0c;要不断重复获取才是最新的 主要是自动设置trigger切换的动画自动切回上一个动画&#x…
阅读更多...
面试--java基础

面试--java基础

Java基础 Java 中的几种基本数据类型了解么&#xff1f;基本类型和包装类型的区别&#xff1f;包装类型的缓存机制了解么&#xff1f;成员变量与局部变量的区别&#xff1f;静态变量有什么作用&#xff1f;静态方法为什么不能调用非静态成员?重载和重写有什么区别&#xff1f…
阅读更多...
MQ 架构设计原理与消息中间件详解（二）

MQ 架构设计原理与消息中间件详解（二）

### 一、RabbitMQ 如何保证消息不丢失&#xff1f; 消息不丢失可以分为三个方面进行保障&#xff1a;**生产者投递消息**、**消费者消费消息** 和 **MQ 服务器持久化**。 #### 1.1 生产者角色的消息确认机制 RabbitMQ 提供了两种方式来确保生产者投递的消息能够被成功接收&am…
阅读更多...
最新文章

Copyright @ 2022~2023