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

MySQL-索引（二）

2023-06-30 37

版权

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

本文涉及的产品

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

云数据库 RDS MySQL，集群版 2核4GB 100GB

云数据库 RDS MySQL，高可用版 2核4GB 50GB

简介： 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-索引（二）

添加唯一索引

添加单列索引

添加全文索引

添加组合索引

添加空间索引

创建索引

普通索引

单列索引

组合索引

全文索引

唯一索引

空间索引

删除索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像