MySQL - 创建索引
数据库索引可以提高数据库表的操作速度。它们可以在一个或多个列上创建，为快速随机查找和高效的记录访问排序奠定基础。
实际上，索引是一种特殊类型的查找表，它保存指向实际表中每条记录的指针。
我们可以在两种情况下在 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 索引，如下所示 -
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int | NO | MUL | NULL | |
| NAME | varchar(20) | NO | NULL | ||
| AGE | int | NO | NULL | ||
| ADDRESS | char(25) | YES | NULL | ||
| SALARY | decimal(18, 2) | YES | 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"列上创建了一个复合索引。
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int | NO | MUL | NULL | |
| NAME | varchar(20) | NO | MUL | NULL | |
| AGE | int | NO | NULL | ||
| ADDRESS | char(25) | YES | NULL | ||
| SALARY | decimal(18, 2) | YES | 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"列上创建了另一个复合索引。
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int | NO | MUL | NULL | |
| NAME | varchar(20) | NO | MUL | NULL | |
| AGE | int | NO | MUL | NULL | |
| ADDRESS | char(25) | YES | NULL | ||
| SALARY | decimal(18, 2) | YES | 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 数据库中创建索引的语法 -
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 type 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);
示例
以下是此操作在各种编程语言中的实现 -
$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"
});
//连接到 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://localhost: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
#建立连接
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.
