当前位置：网站首页>Mysql索引

Mysql索引

2022-08-05 12:35:00 【51CTO】

创建表的时候创建索引
1.创建普通索引

       
       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.11 sec)
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

2.创建唯一索引

       
       mysql> CREATE TABLE t1
       
    -> (
       
    -> id INT NOT NULL,
       
    -> name CHAR(30) NOT NULL,
       
    -> UNIQUE INDEX UniqIdx(id)
       
    -> );
       
Query OK, 0 rows affected (0.08 sec)
       
mysql> SHOW CREATE TABLE t1 /G
       
*************************** 1. row ***************************
       
       Table: t1
       
Create Table: CREATE TABLE `t1` (
       
  `id` int(11) NOT NULL,
       
  `name` char(30) COLLATE utf8mb4_general_ci NOT NULL,
       
  UNIQUE KEY `UniqIdx` (`id`)
       
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
       
1 row in set (0.01 sec)
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

可以看到创建了一个名叫UniqIdx的唯一索引

3.创建单列索引

       
       mysql> CREATE TABLE t2
       
    -> (
       
    -> id INT NOT NULL,
       
    -> name CHAR(50) NULL,
       
    -> INDEX SingleIdx(name(20))
       
    -> );
       
Query OK, 0 rows affected (0.11 sec)
      
1.
2.
3.
4.
5.
6.
7.

       
       mysql> SHOW CREATE TABLE t2 /G
       
*************************** 1. row ***************************
       
       Table: t2
       
Create Table: CREATE TABLE `t2` (
       
  `id` int(11) NOT NULL,
       
  `name` char(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
       
  KEY `SingleIdx` (`name`(20))
       
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
       
1 row in set (0.00 sec)
      
1.
2.
3.
4.
5.
6.
7.
8.
9.

创建了一个名叫SingleIdx的单列索引

4.创建组合索引

       
       CREATE TABLE t3
       
(
       
    id INT NOT NULL,
       
    name CHAR(30) NOT NULL,
       
    age INT NOT NULL,
       
    info VARCHAR(255),
       
    INDEX MultiIdx(id, name, age)
       
);
       
mysql> SHOW CREATE TABLE t3 /G
       
*************************** 1. row ***************************
       
       Table: t3
       
Create Table: CREATE TABLE `t3` (
       
  `id` int(11) NOT NULL,
       
  `name` char(30) COLLATE utf8mb4_general_ci NOT NULL,
       
  `age` int(11) NOT NULL,
       
  `info` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
       
  KEY `MultiIdx` (`id`,`name`,`age`)
       
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
       
1 row in set (0.01 sec)
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

创建了MultiIdx的组合索引

5.创建全文索引

       
       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;
      
1.
2.
3.
4.
5.
6.
7.
8.

只有MyISAM才支持全文索引，CHAR，VARCHAR，TEXT列才可以创建索引。

6.创建空间索引

       
       mysql> CREATE TABLE t5(g GEOMETRY NOT NULL, SPATIAL INDEX apatId(g)) ENGINE=MyISAM;
       
Query OK, 0 rows affected, 1 warning (0.03 sec)
       
mysql> SHOW CREATE TABLE t5 /G
       
*************************** 1. row ***************************
       
       Table: t5
       
Create Table: CREATE TABLE `t5` (
       
  `g` geometry NOT NULL,
       
  SPATIAL KEY `apatId` (`g`)
       
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
       
1 row in set (0.00 sec)
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

只有在MyISAM上可以创建空间索引

9.2.2在已经创建的表上增加索引

9.3删除索引

       
       ALTER TABLE table_name DROP INDEX index_name;
      
1.

MySQL

原网站

版权声明
本文为[51CTO]所创，转载请带上原文链接，感谢
https://blog.51cto.com/u_15735145/5547370

当前位置：网站首页>Mysql索引

Mysql索引

边栏推荐

猜你喜欢

随机推荐