如果我截断一个表，是否还应该添加索引？

2024-04-23 05:07:03 314

如果截断表，则不需要添加索引，因为截断表后会重新创建表，并且会自动添加索引。

让我们首先创建一个表-

mysql> create table DemoTable
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> FirstName varchar(20),
   -> LastName varchar(20)
   -> );

以下是创建索引的查询-

mysql> create index Index_firstName_LastName on DemoTable(FirstName,LastName);
Records: 0 Duplicates: 0 Warnings: 0

使用插入命令在表中插入一些记录-

mysql> insert into DemoTable(FirstName,LastName) values('John','Smith');

截断表格之前，您需要检查表格状态-

mysql> show create table DemoTable;

输出结果

这将产生以下输出-

+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create
Table|
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DemoTable    | CREATE TABLE `DemoTable` (`Id` int(11) NOT NULL AUTO_INCREMENT,`FirstName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,`LastName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,PRIMARY KEY (`Id`),KEY `Index_firstName_LastName` (`FirstName`,`LastName`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

现在截断表-

mysql> truncate table DemoTable;

现在再次检查表状态。您不需要添加索引-

mysql> show create table DemoTable;

输出结果

这将产生以下输出-

+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create
Table
|
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DemoTable | CREATE TABLE `DemoTable` (`Id` int(11) NOT NULL AUTO_INCREMENT,`FirstName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,`LastName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`Id`),KEY `Index_firstName_LastName` (`FirstName`,`LastName`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果我截断一个表，是否还应该添加索引？

热门推荐

随机推荐