Mysql 简明教程

MySQL - Non-Clustered Index

MySQL 中的索引用于从数据库表或视图中更快地检索数据。用户无法在应用程序级别看到索引，但它们在幕后工作以加快搜索和查询。

Indexes in MySQL are used to retrieve the data much faster from the database tables or views. Users cannot see the indexes on the application level, but they work behind to speed up searches and queries.

MySQL 中有两种类型的索引 -

There are two types of Indexes in MySQL −

Clustered Index
Non-Clustered Index

MySQL 中的聚集索引可以按照创建所需的键列对表格中的所有行进行排序，从而手动对表格中的数据进行排序。另一方面，非聚集索引将数据存储在一个位置，索引及该数据的指针存储在另一个位置。

A clustered index in MySQL can sort the data in a table manually by ordering all the rows in the table based on the key columns used to create it. On the other hand, a non-clustered index stores data in one location and indexes containing pointers to this data in another location.

MySQL Non-Clustered Indexes

非聚集索引将数据存储在一个位置，将索引存储在另一个位置。这些索引包含对实际数据的指针。

Non-Clustered indexes store data in one location and its indexes in another location. These indexes contain pointers to the actual data.

然而，MySQL 并未提供明确创建聚集和非聚集索引的方法。主键被视为聚集索引。未定义主键时，第一个 UNIQUE NOT NULL 键即为聚集索引。表上的其他索引均为非聚集索引。

However, MySQL does not provide ways to explicitly create clustered and non-clustered indexes. A PRIMARY KEY is treated as a clustered index. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is a clustered index. All the other indexes on a table are non-clustered indexes.

Syntax

以下是创建 MySQL 表上非聚集索引的基本语法——

Following is the basic syntax to create a non-clustered index on a MySQL table −

CREATE INDEX index_name ON table_name(column_name(s));

Example

我们通过一个示例来看看如何在名为“Students”的表上创建非聚集索引。此表包含学生的详细信息，如学号、姓名、年龄和系别。我们尝试对学号和系别列应用非聚集索引，查询如下——

Let us see an example to create a non-clustered index on a table named 'Students'. This table contains details of students like their Roll Number, Name, Age, and Department. Here, we are trying to apply the non-clustered index on columns Roll Number and Department, using the following query −

首先使用下面所示的 CREATE TABLE 语句创建学生表——

Let us first create the table Students using CREATE TABLE statement shown below −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE  INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2),
   PRIMARY KEY(ID)
);

使用以下查询在 NAME 列上创建非聚集索引——

Using the following query, create a non-clustered index on the NAME column −

CREATE INDEX nc_index ON CUSTOMERS(NAME);

Note - 由于 MySQL 没有非聚集索引的具体规定，我们使用了常规的 CREATE INDEX 语句。

Note − As MySQL does not have specific provision for Non-Clustered Index, we are using the usual CREATE INDEX statement.

Verification

要验证表 CUSTOMERS 上是否已创建索引，可以使用 DESC 命令显示表定义——

To verify whether the INDEX is created on the table CUSTOMERS or not, display the table definition using DESC command −

DESC CUSTOMERS;

正如我们在下面看到的那样，CUSTOMERS 表上创建了两个索引。主键索引为聚集索引，多重索引为非聚集索引——

As we can see below, there are two indexes created on the CUSTOMERS table. The PRIMARY KEY index is a clustered index and the multi-index is a non-clustered index −

Creating a Non-Clustered Index Using NodeJS

除了使用 SQL 查询之外，我们还可以使用客户端程序创建非聚集索引。

In addition to using SQL queries to create non-clustered indexes, we can also create them on a MySQL database using a client program.

MySQL NodJS 连接器 mysql2 提供了一个名为 query() 的函数，用于执行 MySQL 数据库中的 CREATE INDEX 查询。

The MySQL NodeJS connector mysql2 provides a function named query() to execute the CREATE INDEX query in the MySQL database.

Syntax

以下是使用 NodeJS 在 MySQL 数据库中创建非聚集索引的语法——

Following is the syntax to create a non-clustered index in MySQL database using NodeJS −

sql = "CREATE INDEX index_name ON table_name(column_name(s))";
con.query(sql);

Example

以下是使用 NodeJS 实现此操作——

Following are the implementation of this operation using NodeJS −

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE STUDENTS(RNO INT NOT NULL,NAME VARCHAR(50),AGE INT,DEPT VARCHAR(50));"
  con.query(sql);

  //Creating Index
  sql = "CREATE INDEX nc_index ON STUDENTS(RNO, DEPT);"
  con.query(sql);

  //Describing the Table
  sql = "DESC STUDENTS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

输出结果如下：

The output produced is as follows −

Connected!
--------------------------
[
  {Field: 'RNO',Type: 'int',Null: 'NO',Key: 'MUL',Default: null,Extra: ''},
  {Field: 'NAME',Type: 'varchar(50)',Null: 'YES',Key: '',Default: null,Extra: ''},
  {Field: 'AGE',Type: 'int',Null: 'YES',Key: '',Default: null,Extra: ''},
  {Field: 'DEPT',Type: 'varchar(50)',Null: 'YES',Key: '',Default: null,Extra: ''}
]