MySQL - 创建索引

Table of content
上一页
下一页

数据库索引提高了数据库表中操作的速度。它们可以在一个或多个列上创建，为快速随机查找和有效排序记录访问提供基础。

实际上，索引是一种特殊的查找表，它保存指向实际表中每个记录的指针。

我们可以在两种情况下在 MySQL 表上创建索引：创建新表时和在现有表上。

在新的表上创建索引

如果我们想在新的表上定义索引，我们使用 CREATE TABLE 语句。

语法

以下是创建新表索引的语法：

CREATE TABLE(
 column1 datatype PRIMARY KEY,
 column2 datatype,
 column3 datatype,
 ...
 INDEX(column_name)
);

示例

在本例中，我们创建一个名为 CUSTOMERS 的新表，并使用以下 CREATE TABLE 查询向其中一列添加索引：

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

要验证索引是否已定义，我们使用以下 DESC 语句检查表定义。

DESC CUSTOMERS;

输出

显示的表结构将在 ID 列上包含一个 MUL 索引，如下所示：

字段 类型 空 键 默认值 额外
ID int 否 MUL NULL
NAME varchar(20) 否 NULL
AGE int 否 NULL
ADDRESS char(25) 是 NULL
SALARY decimal(18, 2) 是 NULL

在现有表上创建索引

要创建现有表的索引，我们使用以下 SQL 语句：

  • 使用 CREATE INDEX 语句
  • 使用 ALTER 命令

CREATE INDEX 语句

CREATE INDEX 语句的基本语法如下：

CREATE INDEX index_name ON table_name;

在以下示例中，让我们在 CUSTOMERS 表上创建索引。我们在这里使用 CREATE INDEX 语句：

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

要检查索引是否已创建在表上，让我们使用 DESC 语句显示表结构，如下所示：

DESC CUSTOMERS;

输出

如下表所示，在 CUSTOMERS 表的 'NAME' 列上创建了一个复合索引。

字段 类型 空 键 默认值 额外
ID int 否 MUL NULL
NAME varchar(20) 否 MUL NULL
AGE int 否 NULL
ADDRESS char(25) 是 NULL
SALARY decimal(18, 2) 是 NULL

ALTER... ADD 命令

以下是 ALTER 语句的基本语法：

ALTER TABLE tbl_name ADD INDEX index_name (column_list);

让我们在以下示例中使用 ALTER TABLE... ADD INDEX 语句向 CUSTOMERS 表添加索引：

ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE);

输出

如下表所示，在 CUSTOMERS 表的 'AGE' 列上创建了另一个复合索引。

字段 类型 空 键 默认值 额外
ID int 否 MUL NULL
NAME varchar(20) 否 MUL NULL
AGE int 否 MUL NULL
ADDRESS char(25) 是 NULL
SALARY decimal(18, 2) 是 NULL

简单和唯一索引

唯一索引是指不能在两行上同时创建的索引。以下是创建唯一索引的语法：

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

示例

以下示例在 temp 表上创建唯一索引：

CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name);

复合索引

我们还可以在一列以上创建索引，这称为复合索引，创建复合索引的基本语法如下：

CREATE INDEX index_name
on table_name (column1, column2);

示例

以下查询在上面创建的表的 ID 和 Name 列上创建复合索引：

CREATE INDEX composite_index on CUSTOMERS (ID, Name);

使用客户端程序创建索引

除了使用 SQL 查询外，我们还可以使用客户端程序在 MySQL 数据库的表上创建索引。

语法

以下是使用各种编程语言在 MySQL 数据库中创建索引的语法：

PHP NodeJS Java Python

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

$sql=" CREATE INDEX index_name
   ON table_name (column_name)";
$mysqli->query($sql);

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

sql = "CREATE INDEX index_name
   ON table_name (column1, column2, ...)";
con.query(sql);

我们可以使用 **JDBC 4 类型** 驱动程序通过 Java 与 MySQL 通信。它提供了一个名为 **executeUpdate()** 的函数，用于在 MySQL 数据库中执行 CREATE INDEX 查询。

String sql = " CREATE INDEX index_name
   ON table_name (column_name)";
statement.executeUpdate(sql);

**MySQL Connector/Python** 提供了一个名为 **execute()** 的函数，用于在 MySQL 数据库中执行 CREATE INDEX 查询。

create_index_query = CREATE INDEX index_name
   ON table_name (column_name [ASC|DESC], ...);
cursorObj.execute(create_index_query);

示例

以下是此操作在各种编程语言中的实现：

PHP NodeJS Java Python
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

// CREATE INDEX
$sql = "CREATE INDEX tid
        ON tutorials_table (tutorial_id)";
if ($mysqli->query($sql)) {
    printf("Index created successfully!.
");
}
if ($mysqli->errno) {
    printf("Index could not be created!.
", $mysqli->error);
}
$mysqli->close();

输出

获得的输出如下：

Index created successfully!.

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("--------------------------");

  sql = "create database TUTORIALS"
  con.query(sql);

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE temp(Name VARCHAR(255), age INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "INSERT INTO temp values('Radha', 29, 'Vishakhapatnam'), ('Dev', 30, 'Hyderabad');"
  con.query(sql);

  //Creating an Index
  sql = "CREATE INDEX sample_index ON temp (name);"
  con.query(sql);

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

输出

产生的输出如下：

Connected!
--------------------------
[
  {Field: 'Name',Type: 'varchar(255)',Null: 'YES',Key: 'MUL',Default: null,Extra: ''},
  {Field: 'age',Type: 'int',Null: 'YES',Key: '',Default: null,Extra: ''},
  {Field: 'Location',Type: 'varchar(255)',Null: 'YES',Key: '',Default: null,Extra: ''}
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CreateIndex {
   public static void main(String[] args) {
      String url = "jdbc:mysql://127.0.0.1:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Create an index on the tutorials_tbl...!;
         String sql = "CREATE INDEX tid ON tutorials_tbl (tutorial_id)";
         statement.executeUpdate(sql);
         System.out.println("Index created Successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

输出

获得的输出如下所示：

Connected successfully...!
Index created Successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
create_index_query = "CREATE INDEX idx_submission_date ON tutorials_tbl (submission_date)"
cursorObj.execute(create_index_query)
connection.commit()
print("Index created successfully.")
cursorObj.close()
connection.close()

输出

以下是上述代码的输出：

Index created successfully.
打印页面
上一页
下一页
广告