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 索引，如下所示 −
| 字段 | 类型 | Null | 键 | 默认值 | 额外 |
|---|---|---|---|---|---|
| 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' 列上创建了一个复合索引。
| 字段 | 类型 | Null | 键 | 默认值 | 额外 |
|---|---|---|---|---|---|
| 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' 列上创建了另一个复合索引。
| 字段 | 类型 | Null | 键 | 默认值 | 额外 |
|---|---|---|---|---|---|
| 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.
');
// 创建索引
$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);
//创建索引
sql = "CREATE INDEX sample_index ON temp (name);"
con.query(sql);
//描述表
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...!");
//在 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.