MySQL-索引（二）-阿里云开发者社区

MySQL-索引（二）

2023-06-30 47

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： MySQL-索引

添加唯一索引

添加索引

1. mysql> alter table book add unique index Uniqldx(bookid);
2. Query OK, 0 rows affected (0.05 sec)
3. Records: 0  Duplicates: 0  Warnings: 0

查看索引

1. mysql> show index from book\G
2. *************************** 1. row ***************************
3.         Table: book
4.    Non_unique: 0
5.      Key_name: Uniqldx
6.  Seq_in_index: 1
7.   Column_name: bookid
8.     Collation: A
9.   Cardinality: 0
10.      Sub_part: NULL
11.        Packed: NULL
12.          Null: 
13.    Index_type: BTREE
14.       Comment: 
15. Index_comment: 
16. *************************** 2. row ***************************
17.         Table: book
18.    Non_unique: 1
19.      Key_name: year_publication
20.  Seq_in_index: 1
21.   Column_name: year_publication
22.     Collation: A
23.   Cardinality: 0
24.      Sub_part: NULL
25.        Packed: NULL
26.          Null: 
27.    Index_type: BTREE
28.       Comment: 
29. Index_comment: 
30. *************************** 3. row ***************************
31.         Table: book
32.    Non_unique: 1
33.      Key_name: BKNameIdx
34.  Seq_in_index: 1
35.   Column_name: bookname
36.     Collation: A
37.   Cardinality: 0
38.      Sub_part: 30
39.        Packed: NULL
40.          Null: 
41.    Index_type: BTREE
42.       Comment: 
43. Index_comment: 
44. 3 rows in set (0.00 sec)

添加单列索引

添加索引

1. mysql> alter table book add index BKidex(comment(50));
2. Query OK, 0 rows affected (0.01 sec)
3. Records: 0  Duplicates: 0  Warnings: 0

查看索引

1. mysql> show index from book\G
2. *************************** 1. row ***************************
3.         Table: book
4.    Non_unique: 0
5.      Key_name: Uniqldx
6.  Seq_in_index: 1
7.   Column_name: bookid
8.     Collation: A
9.   Cardinality: 0
10.      Sub_part: NULL
11.        Packed: NULL
12.          Null: 
13.    Index_type: BTREE
14.       Comment: 
15. Index_comment: 
16. *************************** 2. row ***************************
17.         Table: book
18.    Non_unique: 1
19.      Key_name: year_publication
20.  Seq_in_index: 1
21.   Column_name: year_publication
22.     Collation: A
23.   Cardinality: 0
24.      Sub_part: NULL
25.        Packed: NULL
26.          Null: 
27.    Index_type: BTREE
28.       Comment: 
29. Index_comment: 
30. *************************** 3. row ***************************
31.         Table: book
32.    Non_unique: 1
33.      Key_name: BKNameIdx
34.  Seq_in_index: 1
35.   Column_name: bookname
36.     Collation: A
37.   Cardinality: 0
38.      Sub_part: 30
39.        Packed: NULL
40.          Null: 
41.    Index_type: BTREE
42.       Comment: 
43. Index_comment: 
44. *************************** 4. row ***************************
45.         Table: book
46.    Non_unique: 1
47.      Key_name: BKidex
48.  Seq_in_index: 1
49.   Column_name: comment
50.     Collation: A
51.   Cardinality: 0
52.      Sub_part: 50
53.        Packed: NULL
54.          Null: YES
55.    Index_type: BTREE
56.       Comment: 
57. Index_comment: 
58. 4 rows in set (0.00 sec)

添加全文索引

添加索引

1. mysql> CREATE TABLE t6
2.     ->  (
3.     ->  id INT NOT NULL,
4.     ->  info CHAR(255)
5.     ->  )ENGINE=MyISAM;
6. Query OK, 0 rows affected (0.00 sec)
7. 
8. mysql> ALTER TABLE t6 ADD FULLTEXT INDEX InfoFULIdx(info);
9. Query OK, 0 rows affected (0.00 sec)
10. Records: 0  Duplicates: 0  Warnings: 0

查看全文索引

1. mysql> show index from t6\G
2. *************************** 1. row ***************************
3.         Table: t6
4.    Non_unique: 1
5.      Key_name: InfoFULIdx
6.  Seq_in_index: 1
7.   Column_name: info
8.     Collation: NULL
9.   Cardinality: NULL
10.      Sub_part: NULL
11.        Packed: NULL
12.          Null: YES
13.    Index_type: FULLTEXT
14.       Comment: 
15. Index_comment: 
16. 1 row in set (0.00 sec)

添加组合索引

添加索引

1. mysql>  ALTER TABLE book ADD INDEX BKAUthAndInfoIdx(authors(20),info(50));
2. Query OK, 0 rows affected (0.01 sec)
3. Records: 0  Duplicates: 0  Warnings: 0

查看索引

1. mysql> show index from book\G
2. *************************** 1. row ***************************
3.         Table: book
4.    Non_unique: 0
5.      Key_name: Uniqldx
6.  Seq_in_index: 1
7.   Column_name: bookid
8.     Collation: A
9.   Cardinality: 0
10.      Sub_part: NULL
11.        Packed: NULL
12.          Null: 
13.    Index_type: BTREE
14.       Comment: 
15. Index_comment: 
16. *************************** 2. row ***************************
17.         Table: book
18.    Non_unique: 1
19.      Key_name: year_publication
20.  Seq_in_index: 1
21.   Column_name: year_publication
22.     Collation: A
23.   Cardinality: 0
24.      Sub_part: NULL
25.        Packed: NULL
26.          Null: 
27.    Index_type: BTREE
28.       Comment: 
29. Index_comment: 
30. *************************** 3. row ***************************
31.         Table: book
32.    Non_unique: 1
33.      Key_name: BKNameIdx
34.  Seq_in_index: 1
35.   Column_name: bookname
36.     Collation: A
37.   Cardinality: 0
38.      Sub_part: 30
39.        Packed: NULL
40.          Null: 
41.    Index_type: BTREE
42.       Comment: 
43. Index_comment: 
44. *************************** 4. row ***************************
45.         Table: book
46.    Non_unique: 1
47.      Key_name: BKidex
48.  Seq_in_index: 1
49.   Column_name: comment
50.     Collation: A
51.   Cardinality: 0
52.      Sub_part: 50
53.        Packed: NULL
54.          Null: YES
55.    Index_type: BTREE
56.       Comment: 
57. Index_comment: 
58. *************************** 5. row ***************************
59.         Table: book
60.    Non_unique: 1
61.      Key_name: BKAUthAndInfoIdx
62.  Seq_in_index: 1
63.   Column_name: authors
64.     Collation: A
65.   Cardinality: 0
66.      Sub_part: 20
67.        Packed: NULL
68.          Null: 
69.    Index_type: BTREE
70.       Comment: 
71. Index_comment: 
72. *************************** 6. row ***************************
73.         Table: book
74.    Non_unique: 1
75.      Key_name: BKAUthAndInfoIdx
76.  Seq_in_index: 2
77.   Column_name: info
78.     Collation: A
79.   Cardinality: 0
80.      Sub_part: 50
81.        Packed: NULL
82.          Null: YES
83.    Index_type: BTREE
84.       Comment: 
85. Index_comment: 
86. 6 rows in set (0.00 sec)

添加空间索引

添加索引

1. mysql> CREATE TABLE t7
2.     ->  (
3.     ->  g GEOMETRY NOT NULL
4.     ->  )ENGINE=MyISAM;
5. Query OK, 0 rows affected (0.01 sec)
6. 
7. mysql> ALTER TABLE t7 ADD SPATIAL INDEX SpatIdx(g);
8. Query OK, 0 rows affected (0.01 sec)
9. Records: 0  Duplicates: 0  Warnings: 0

查看索引

1. mysql> show index from t7\G
2. *************************** 1. row ***************************
3.         Table: t7
4.    Non_unique: 1
5.      Key_name: SpatIdx
6.  Seq_in_index: 1
7.   Column_name: g
8.     Collation: A
9.   Cardinality: NULL
10.      Sub_part: 32
11.        Packed: NULL
12.          Null: 
13.    Index_type: SPATIAL
14.       Comment: 
15. Index_comment: 
16. 1 row in set (0.00 sec)

创建索引

创建一个book1表

1. mysql> create table book1 (bookid int not null, bookname varchar(255) not null,
2. -> authors varchar(255) not null, info varchar(255) null, comment varchar(255) null,
3. -> year_publication year not null );
4. Query OK, 0 rows affected (0.01 sec)

普通索引

1. mysql> create index bknameidex on book1(bookname);
2. Query OK, 0 rows affected (0.34 sec)
3. Records: 0 Duplicates: 0 Warnings: 0

单列索引

1. mysql> create index bkcmtidex on book1 (comment(50));
2. Query OK, 0 rows affected (0.00 sec)
3. Records: 0 Duplicates: 0 Warnings: 0

组合索引

1. mysql> create index bkauthandinfoidex on book1(authors(30),info(50));
2. Query OK, 0 rows affected (0.00 sec)
3. Records: 0 Duplicates: 0 Warnings: 0

全文索引

1. mysql> drop table t6;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> create table t6 ( id int not null, info char(255))engine=myisam;
4. Query OK, 0 rows affected (0.00 sec)
5. mysql> CREATE FULLTEXT INDEX FullIdex ON t6(info);
6. Query OK, 0 rows affected (0.00 sec)
7. Records: 0 Duplicates: 0 Warnings: 0

唯一索引

1. mysql> create unique index uniqidx on book1(bookid);
2. Query OK, 0 rows affected (0.00 sec)
3. Records: 0 Duplicates: 0 Warnings: 0

空间索引

1. mysql> drop table t7;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> create table t7 ( g geometry not null )engine=myisam;
4. Query OK, 0 rows affected (0.00 sec)
5. mysql> create spatial index spaidx on t7(g);
6. Query OK, 0 rows affected (0.00 sec)
7. 
8. Records: 0 Duplicates: 0 Warnings: 0

删除索引

先查看book表的索引

1. mysql> show create table book\G
2. *************************** 1. row ***************************
3.        Table: book
4. Create Table: CREATE TABLE `book` (
5.   `bookid` int(11) NOT NULL,
6.   `bookname` varchar(255) COLLATE utf8_bin NOT NULL,
7.   `authors` varchar(255) COLLATE utf8_bin NOT NULL,
8.   `info` varchar(255) COLLATE utf8_bin DEFAULT NULL,
9.   `comment` varchar(255) COLLATE utf8_bin DEFAULT NULL,
10.   `year_publication` year(4) NOT NULL,
11.   KEY `year_publication` (`year_publication`),
12.   KEY `bknameidx` (`bookname`(30))
13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
14. 1 row in set (0.00 sec)

使用alter命令删除索引后，再次查看book表已经删除bknameidx索引。

1. mysql> alter table book drop index bknameidx;
2. Query OK, 0 rows affected (0.03 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
4. 
5. mysql> show create table book\G
6. *************************** 1. row ***************************
7.        Table: book
8. Create Table: CREATE TABLE `book` (
9.   `bookid` int(11) NOT NULL,
10.   `bookname` varchar(255) COLLATE utf8_bin NOT NULL,
11.   `authors` varchar(255) COLLATE utf8_bin NOT NULL,
12.   `info` varchar(255) COLLATE utf8_bin DEFAULT NULL,
13.   `comment` varchar(255) COLLATE utf8_bin DEFAULT NULL,
14.   `year_publication` year(4) NOT NULL,
15.   KEY `year_publication` (`year_publication`)
16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
17. 1 row in set (0.00 sec)

使用drop index删除

1. mysql> show create table t7\G
2. *************************** 1. row ***************************
3.        Table: t7
4. Create Table: CREATE TABLE `t7` (
5.   `g` geometry NOT NULL,
6.   SPATIAL KEY `spatidx` (`g`)
7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
8. 1 row in set (0.00 sec)
9. 
10. mysql> drop index spatidx on t7;
11. Query OK, 0 rows affected (0.00 sec)
12. Records: 0  Duplicates: 0  Warnings: 0
13. 
14. mysql> show create table t7\G
15. *************************** 1. row ***************************
16.        Table: t7
17. Create Table: CREATE TABLE `t7` (
18.   `g` geometry NOT NULL
19. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
20. 1 row in set (0.00 sec)

MySQL-索引（二）

添加唯一索引

添加单列索引

添加全文索引

添加组合索引

添加空间索引

创建索引

普通索引

单列索引

组合索引

全文索引

唯一索引

空间索引

删除索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL-索引（二）

添加唯一索引

添加单列索引

添加全文索引

添加组合索引

添加空间索引

创建索引

普通索引

单列索引

组合索引

全文索引

唯一索引

空间索引

删除索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像