Familiarity with MySQL indexes

1. Introduction to index
(1)The meaning and specificity of index
(2)The classification of index
(3)The design principles of index

2. Create an index
(1) Create an index when creating a table
(2) Create an index on an existing table
(3) Delete the index

(free learning recommendation ：mysql video tutorial)

---

1. Index introduction

Index is used to quickly find rows with a specific value in a column. Without using an index, MySQL must read the entire table starting from the first record until it finds the relevant rows. The larger the table, the more time it takes to query the data. If the queried column in the table has an index, MySQL can quickly get to a location to search the data file without having to look at all the data.

(1)The meaning and specific

meaning of index: The index is a separate database structure stored on disk, which contains reference pointers to all records in the data table. Used to quickly find rows with a specific value in one or more columns.

Indexes are implemented in storage engines, so the indexes of each storage engine are not necessarily exactly the same, and each storage engine does not necessarily support all index types. Define the maximum number of indexes and maximum index length for each table according to the storage engine. All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes. Most storage engines have higher limits.

There are two storage types of indexes in MySQL: BTREE and HASH, which are specifically related to the storage engine of the table; MyISAM and InnoDB storage engines only support BTREE indexes; The MEMORY/HEAL storage engine can support HASH and BTREE indexes.

Advantages of indexes:
1. By creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.
2. Can greatly speed up data query speed. (The main reason for creating an index)
3. In terms of achieving referential integrity of data, it can speed up the connection between tables and tables.
4. When using grouping and sorting clauses for data query, the time of grouping and sorting in the query can also be significantly reduced.

Disadvantages of adding indexes:
1. Creating and maintaining indexes takes time, and as the amount of data increases, the time spent will also increase.
2. Indexes occupy disk space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file.
3. When adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the data maintenance speed.

(2) Classification of indexes

1. Ordinary index and unique index (unique)

• Ordinary index is the basic index type in MySQL, which allows definition Insert duplicate values ​​and null values ​​into indexed columns.
• Unique index, the value of the index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique.
• The primary key index is a special unique index that does not allow null values.

2. Single column index and combined index

• Single column means that an index only contains a single column, and a table can have multiple single column indexes.
• Combined index refers to an index created on a combination of multiple fields in the table. The index will be used only when the left fields of these fields are used in the query conditions.
• Follow the leftmost prefix set when using combined indexes.

3. Full-text index (fulltext)

• The full-text index type is FULLTEXT, which supports full-text search of values ​​on the columns where the index is defined, and allows insertion into these index columns. Duplicate values ​​and null values, full-text indexes can be created on columns of type char, varchar or text. Only the MyISAM storage engine in MySQL supports full-text indexing.

4. Spatial index (spatial)

• The spatial index is an index established on fields of spatial data types. There are 4 types of spatial data types in MySQL, namely geometry, point, linestring and polygon. MySQL has been extended with the spatial keyword, enabling spatial indexes to be created with syntax similar to that used to create regular indexes. Columns used to create spatial indexes must be declared not null. Spatial indexes can only be created in tables whose storage engine is MySQL.

(3) Index design principles

Unreasonable index design or lack of indexes will cause obstacles to the performance of the database and applications. Efficient indexes are very important to obtain good performance. When designing indexes, you should consider the following guidelines:

• 1. More indexes are not better.
• 2. Avoid too many indexes on frequently updated tables, and index as few columns as possible.
• 3. It is best not to use indexes for tables with small data volumes.
• 4. Create indexes on columns with many different values ​​that are often used in conditional expressions. Do not create indexes on columns with few different values.
• 5. When uniqueness is a characteristic of some data itself, specify a unique index.
• 6. Create indexes on columns that are frequently sorted or grouped (group by or order by operations). If there are multiple columns to be sorted, you can create a combined index on these columns.

2. Create index

Syntax format:

create table table_name [col_name date_type][unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [asc | desc]

• unique, fulltext and spatial are optional parameters, representing unique index, full-text index and spatial index respectively.
• Index and key are synonyms. They have the same function and are used to specify the creation of an index.
• col_name is the field column that needs to be indexed. This column must be selected from multiple columns defined in the data table.
• index_name specifies the name of the index, which is an optional parameter. If not specified, MySQL defaults to col_name as the index value.
• length is an optional parameter, indicating the length of the index. Only string type fields can specify the index length.
• asc or desc specifies index value storage in ascending or descending order.

(1) Create an index when creating a table

①Create an ordinary index

Ordinary index is the most basic index type, without restrictions such as uniqueness , its function is only to speed up access to data.

[Example 1] Create a common index on the year_publication field in the book table. The SQL statement is as follows:

mysql> create table book    -> (
    -> bookid int not null,
    -> bookname varchar(255) not null,
    -> authors varchar(255) not null,
    -> info varchar(255) null,
    -> comment varchar(255) null,
    -> year_publication year not null,
    -> index(year_publication)
    -> );Query OK, 0 rows affected (0.21 sec)mysql> show create table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from book where year_publication=1990 \G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication          key: year_publication
      key_len: 1
          ref: const         rows: 1
     filtered: 100.00
        Extra: NULL1 row in set, 1 warning (0.00 sec)

The explanation of each row of the output result of the explan statement is as follows:

## The #select_type line specifies the select query type used. The value here is simple, which means a simple select without using union or subquery. Other possible values ​​include primary, union, subquery, etc.
• The table row specifies the name of the data table read by the database, and they are arranged in the order in which they are read. The
• type line specifies the relationship between this database table and other database tables. Possible values ​​include system, const, eq_ref, ref, range, index and all.
• possible_keys row. Gives various indexes that MySQL can use when searching for data records.
• The key row is the index actually selected by MySQL.
• The key_len line gives the length of the index in bytes. The smaller the key_len value, the faster it is. The
• ref line gives the name of the data column in another data table in the relationship.
• rows rows is the number of data rows that MySQL expects to read from this data table when executing this query. The
• extra line provides information about the association operation.

As you can see, the values ​​of possible_key and key are year_publication, and the index is used in the query.

②Create a unique index

The main reason for creating a unique index is to reduce the execution time of querying index column operations, especially for relatively large data tables. It is similar to the previous ordinary index, except that the value of the index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique.

[Example 2] Create a table t1 and use the unique keyword to create a unique index on the id field in the table.

mysql> create table t1    -> (
    -> id int not null
    -> ,name char(30) not null,
    -> unique index uniqidx(id)
    -> );Query OK, 0 rows affected (0.27 sec)mysql> show create table t1 \G*************************** 1. row ***************************
       Table: t1Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)

③Create a single-column index

A single-column index is an index created on a certain field in the data table. Multiple single-column indexes can be created in a table.

[Example 3] Create a table t2 and create a single-column index on the name field in the table.

mysql> create table t2    -> (
    -> id int not null,
    -> name char(50) null,
    -> index singleidx(name(20))
    -> );Query OK, 0 rows affected (0.06 sec)mysql> show create table t2 \G*************************** 1. row ***************************
       Table: t2Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `singleidx` (`name`(20))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)

As you can see from the results, a single-column index named SingleIdx has been successfully established on the id field, with an index length of 20.

④Create a composite index

A composite index is to create an index on multiple fields.

[Example 4] Create table t3 and create a composite index on the id, name and age fields in the table. The SQL statement is as follows:

mysql> create table t3    -> (
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> index mulitiidx(id,name,age)
    -> );Query OK, 0 rows affected (0.07 sec)mysql> show create table t3 \G*************************** 1. row ***************************
       Table: t3Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `mulitiidx` (`id`,`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from t3 where id = 1 and name = 'joe' \G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: mulitiidx          key: mulitiidx
      key_len: 124
          ref: const,const         rows: 1
     filtered: 100.00
        Extra: Using index condition1 row in set, 1 warning (0.06 sec)

The composite index plays the role of several indexes. However, when using it, it is not possible to use the index to query any field, but to follow the "leftmost prefix": using the leftmost column set in the index to match rows, such a column set is called the leftmost prefix.

For example, here is an index composed of three fields: id, name, and age. The index rows are sorted in the order of id/nam/age. The index can search for field combinations: (id, name, age), ( id, name) or id. If the column does not constitute the leftmost prefix of the index, MySQL cannot use the local index. For example, the (age) or (name, age) combination cannot use the index query. When querying the id and name fields, the multiidx index is used. If the combination of (name, age) is queried or the name and age fields are queried separately, the index is null.

⑤Create a full-text index

Fulltext full-text index can be used for full-text search. Only the MyISAM storage engine supports fulltext indexes, and only creates indexes for char, varchar, and text columns. Indexing is always performed on the entire column, local (prefix) indexes are not supported.

[Example 5] Create table t4 and create a full-text index on the info field in the table. The SQL statement is as follows:

mysql> create table t4    -> (
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> fulltext index fulltxtidx(info)
    -> )engine=MyISAM;Query OK, 0 rows affected (0.08 sec)mysql> show create table t4 \G*************************** 1. row ***************************
       Table: t4Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `fulltxtidx` (`info`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)

Full-text index is very suitable for large data sets. For small data sets , its usefulness is relatively small.

6. Create a spatial index

The spatial index must be created in a MyISAM type table, and the spatial type field must be non-null.

[Example 6] Create table t5 and create a spatial index on the field with spatial type geometry. The SQL statement is as follows:

mysql> create table t5    -> ( g geometry not null,spatial index spatidx(g) ) ENGINE=MyISAM;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> show create table t5 \G*************************** 1. row ***************************
       Table: t5Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.05 sec)

You can see that the name is created on the g field of table t5 is the spatial index of spatIdx. Specify the non-null constraint of the spatial type field value when creating, and the storage engine of the table is MyISAM.

(2) Create an index on an existing table

To create an index on an existing table, you can use the alter table statement or create index statement.

1. Use the alter table statement to create an index

Basic syntax:

alter table table_name add [unique|fulltext|spatial] [index|key][index_name] (col_name[length],...) [asc |dec]

[Example 7] Create a name on the bookname field in the book table Is the ordinary index of BkNameIdx.

Before adding an index, use the show index statement to view the index created in the specified table:

mysql> show index from book \G*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL1 row in set (0.10 sec)

其中，各个主要参数的含义为;

• table表示创建索引的表。
• Non_unique表示索引非唯一，1表示非唯一，0表示唯一。
• Key_name表示索引的名称。
• Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。
• Column_name表示定义索引的列字段。
• Sub_part表示索引的长度。
• Null表示该字段是否能为空值。
• Index_type表示索引类型。

可以看到book表中已经存在一个索引，即year_publication索引，该索引为非唯一索引，下面使用alter table 在bookname字段上添加索引，SQL语句如下：

mysql> alter table book add index bknameidx( bookname(30) );Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bknameidx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL2 rows in set (0.05 sec)

可以看到表中有了两个索引，另一个为通过alter table语句添加的名称为bknameidx的索引，该索引为非唯一索引，长度为30。

【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引，SQL语句如下：

mysql> alter table book add unique index uniqididx(bookid);Query OK, 0 rows affected (0.17 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G1...2...*************************** 3. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bknameidx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL3 rows in set (0.01 sec)

可以看到，Non_unique的属性值为0，表示名称为Uniqididx的索引为唯一索引，创建唯一索引成功。

【例9】在book表的comment字段上建立单列索引，SQL语句如下：

mysql> alter table book add index bkcmtidx ( comment(50) );Query OK, 0 rows affected (0.11 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G1...2...3...*************************** 4. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkcmtidx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL4 rows in set (0.01 sec)

可以看到，语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引，长度为50，在查询时，只需要检索前50个字符。

【例10】在book表的authors和info字段上建立组合索引，SQL语句如下：

mysql> alter table book add index bkauandinfoidx (authors(30),info(50) );Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G1...2...3...4...*************************** 5. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkauandinfoidx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL*************************** 6. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkauandinfoidx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL6 rows in set (0.06 sec)

可以看到名称为bkauandinfoidx的索引由两个字段组成，authors字段长度为30，在组合索引中的序号为1，该字段不允许空值null；info字段长度为50，在组合索引中的序号为2，该字段可以为空值null。

【例11】创建表t6，在t6表上使用alter table创建全文索引，SQL语句如下：

mysql> create table t6    -> (
    -> id int not null,
    -> info char(255)
    -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t6 add fulltext index infofiidx( info );Query OK, 0 rows affected (0.13 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from t6 \G*************************** 1. row ***************************
        Table: t6
   Non_unique: 1
     Key_name: infofiidx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT      Comment:
Index_comment:
      Visible: YES
   Expression: NULL1 row in set (0.05 sec)

可以看到，t6表中已经创建了名称为infoftidx的索引，该索引在info字段上创建，类型为fulltext，允许空值。

【例12】创建表t7，t7的空间类型字段g上创建名称为spatidx的空间索引，SQL语句如下：

mysql> create table t7(g geometry not null)ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t7 add spatial index spatidx(g);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0  Duplicates: 0  Warnings: 1mysql> show index from t7 \G*************************** 1. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: spatidx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL      Comment:
Index_comment:
      Visible: YES
   Expression: NULL1 row in set (0.01 sec)

可以看到，t7表的g字段上创建了名为spatidx的空间索引。

2.使用create index 创建索引

create index 语句可以在已经存在的表上添加索引，MySQL中create index被映射到一个alter table语句上，基本语法为：

create [unique|fulltext|spatial] index index_nameon table_name (col_name[length],...) [asc|desc]

可以看到create index语句和alter index语句的语法基本一样，只是关键字不同，使用相同的表book，假设该表中没有任何索引值，创建book表语句如下：

create table book(bookid int not null,bookname varchar(255) not null,authors varchar(255) not null,info varchar(255) null,comment varchar(255) null,year_publication year not null);

【例13】在book表的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：

mysql> create index BkNameOdx on book(bookname);Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0

【例14】在book表的bookid字段上建立名为UniqidIdx的唯一索引，SQL语句如下：

mysql> create unique index UniqiiIdx on book(bookid);Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0

【例15】在book表的comment字段上建立单列索引，SQL语句如下：

mysql> create index BkcmtIdx on book(bookid);Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0

【例16】在book表的authors和info字段上建立组合索引，SQL语句如下：

mysql> create index BkAuAndInfoIdx on book (authors(20),info(50));Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0

【例17】删除表t6，重新建立表t6，在t6表中使用create index 语句，在char类型的info字段上创建全文索引。

mysql> drop table t6;Query OK, 0 rows affected (0.02 sec)mysql> create table t6    -> (
    -> id int not null,
    -> info char(255)
    -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create fulltext index infoftidx on t6(info);Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0

【例18】删除表t7，重新创建表t7，在t7表中使用create index语句，在空间数据类型字段g上创建名称为spatIdx的空间索引。

mysql> drop table t7;Query OK, 0 rows affected (0.06 sec)mysql> create table t7 (g geometry not null )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create spatial index spatIdx on t7 (g);Query OK, 0 rows affected, 1 warning (0.07 sec)Records: 0  Duplicates: 0  Warnings: 1

(3)删除索引

MySQL中删除索引使用alter table或者drop index 语句，两者可实现相同的功能，drop index 语句在内部被映射到一个alter table语句中。

1.使用alter table删除索引

alter table 删除索引的基本语法格式:

alter table table_name drop index index_name

【例1】删除book表中的名称为UniqidIdx的唯一索引。

mysql> show create table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  UNIQUE KEY `UniqiIdx` (`bookid`),
  KEY `BkNameOdx` (`bookname`),
  KEY `BkcmtIdx` (`bookid`),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> alter table book drop index UniqiIdx;Query OK, 0 rows affected (0.19 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameOdx` (`bookname`),
  KEY `BkcmtIdx` (`bookid`),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

可以看到，book表中已经没有名称为UniqidIdx的唯一索引，删除索引成功。

注意：添加auto_increment约束字段的唯一索引不能被删除。

2.使用drop index 语句删除索引

drop index语句删除索引的基本语法格式：

drop index inde _name on table_name

【例2】删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：

mysql> drop index BkAuAndInfoIdx on book;Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create  table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameOdx` (`bookname`),
  KEY `BkcmtIdx` (`bookid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

可以看到，book表中已经没有名称为BkAuAndInfoIdx的组合索引，删除索引成功。

注意：删除表中的列时，如果要删除的列为索引的组成部分，则该部分也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。

相关免费学习推荐：mysql数据库(视频)

The above is the detailed content of Familiarity with MySQL indexes. For more information, please follow other related articles on the PHP Chinese website!