MySQL-索引的增删改

最新推荐文章于 2024-06-05 17:05:41 发布

我姓谭却弹不死你

最新推荐文章于 2024-06-05 17:05:41 发布

阅读量453

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/badBoy_badMan/article/details/138874080

版权

MySQL 专栏收录该内容

40 篇文章 0 订阅

订阅专栏

1、索引的分类

从功能逻辑上划分：
- 普通索引 ：创建索引时不加任何限制条件，只是用来提高查询效率。可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。
- 唯一索引：使用UNIQUE参数可以设置索引为唯一索引。创建该索引时限制该索引的值必须是唯一的，但允许由空值，一张表里可以有多个唯一索引
- 主键索引：是一种特殊的唯一索引，在唯一索引的基础上添加了不为空的约束，一张表里至多只有一个主键索引
- 全文索引：是目前搜索引擎中使用的一种关键技术，能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能的筛选出我们想要的搜索结果。适合大型数据集
从物理实现方式：
- 聚簇索引
- 非聚簇索引
从作用字段个数：
- 单列索引：在表中单个字段创建索引。单列索引只根据该字段进行索引，可以是普通索引、唯一索引、全文索引。一张表可以有多个单列索引。
- 联合索引：是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这些字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。遵循最左前缀集合。

2、创建索引

2.1、创建表时创建索引

语法格式如下：create table table_name [ col_name data_type ] [ unique | fulltext | spatial ] [ index | key ] [ index_name ] (col_name [ length ]) [ asc | desc ] invisible
- unique | fulltext | spatial：可选参数，分别标识唯一索引，全文索引，空间索引
- index | key : 指定创建索引
- index_name : 指定创建索引名称，为可选参数，不指定则默认为 col_name为索引名
- col_name : 为需要创建索引的字段列，该列必须在数据表中定义的列中选择
- length ：可选参数，标识索引的长度，只有字符串类型的字段才能指定索引长度
- asc | desc : 指定升序或降序的索引值存储
- invisible ：设置索引的可见性
SQL语句如下：

mysql> create table test_index(id int,name varchar(10) ,age int ,info varchar(30), index(name));
Query OK, 0 rows affected (2.38 sec)

查看创建索引

#方式一
mysql> show create table test_index\G
*************************** 1. row ***************************
       Table: test_index
Create Table: CREATE TABLE `test_index` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `info` varchar(30) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (3.32 sec)

#方式二：
mysql> show index from test_index\G
*************************** 1. row ***************************
        Table: test_index
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (4.37 sec)

查看索引是否使用，关键字explain：

mysql> explain select * from test_index where name = 'rqtanc';
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_index | NULL       | ref  | name          | name | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.37 sec)

2.2、通过alter 语句修改索引

语法格式： alter table (table_name) add index [index_name] (col_name [length])

mysql> alter table test_index add index age;

mysql> show index from test_index\G
*************************** 1. row ***************************
        Table: test_index
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: test_index
   Non_unique: 1
     Key_name: age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.08 sec)

2.3、通过create index 语句创建索引

语法格式： create index （index_name） on table_name(col_name [length])

mysql> create index idx_info on test_index(info);
Query OK, 0 rows affected (0.18 sec)

Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from test_index\G
*************************** 1. row ***************************
        Table: test_index
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: test_index
   Non_unique: 1
     Key_name: age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: test_index
   Non_unique: 1
     Key_name: idx_info
 Seq_in_index: 1
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
3 rows in set (0.05 sec)

3、删除索引

3.1、使用alter table 删除索引

语法格式： alter table table_name drop index index_name

mysql> show index from test_index;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_index |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test_index |          1 | age      |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test_index |          1 | idx_info |            1 | info        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.10 sec)

mysql> alter table test_index drop index idx_info;
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test_index;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_index |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test_index |          1 | age      |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

3.2、使用 drop index 语句删除索引

语法格式：drop index index_name on table_name;

mysql> show index from test_index;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_index |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test_index |          1 | age      |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

mysql> drop index age on test_index;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test_index;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_index |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.03 sec)

4、隐藏索引

MySQL 8开始支持隐藏索引，只需要将待删除的索引设置隐藏索引，使查询优化器不在使用该索引（即使使用 force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后不受任何影响，则可以删除索引
先将索引设置为隐藏索引，在删除索引的方式就是软删除

注意点：
主键不能被设置为隐藏索引。
当表中没有显式主键时，表中第一个唯一非空索引会称为隐式主键，也不能设置为隐藏索引

4.1创建表时创建隐藏索引

基本格式：create table table_name [ col_name data_type ] [ unique | fulltext | spatial ] [ index | key ] [ index_name ] (col_name [ length ]) [ asc | desc ] invisible
SQL语句如下：

mysql> create table test1_index(id int,name varchar(10) ,age int ,info varchar(30), index idx_invisible (name) invisible);
Query OK, 0 rows affected (0.70 sec)

mysql> show index from test1_index;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1_index |          1 | idx_invisible |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.55 sec)

### 4.2、通过alter 语句修改索引

语法格式： alter table (table_name) add index [index_name] (col_name [length]) invisible

mysql> alter table test1_index add index idx_age(age) invisible;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1_index;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1_index |          1 | idx_invisible |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
| test1_index |          1 | idx_age       |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

4.3、通过create index 语句创建索引

语法格式： create index （index_name） on table_name(col_name [length]) invisible

mysql> create index idx_info on test1_index(info) invisible;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1_index;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1_index |          1 | idx_invisible |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
| test1_index |          1 | idx_age       |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
| test1_index |          1 | idx_info      |            1 | info        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.03 sec)

4.4、修改索引的可见性

语法格式： alter table table_name alter index index_name （visible | invisible )；

mysql> alter table test1_index alter index idx_info visible;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1_index;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1_index |          1 | idx_invisible |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
| test1_index |          1 | idx_age       |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
| test1_index |          1 | idx_info      |            1 | info        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)