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://: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.
列印頁面
上一頁
下一頁
廣告