第9章索引

最新推荐文章于 2022-09-25 23:21:51 发布

weixin_43037507

最新推荐文章于 2022-09-25 23:21:51 发布

阅读量479

点赞数

本文链接：https://blog.csdn.net/weixin_43037507/article/details/85040378

版权

索引用于快速找出在某个列中有一特定值的行。
9.2 创建索引
1. 创建普通索引
例：在vook表中的year_publication字段上建立普通索引，SQL语句如下：

mysql> CREATE TABLE vook(
    -> 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 (2.67 sec)

语句执行完毕后，使用SHOW CREATE TABLE查看表结构：

mysql> SHOW CREATE TABLE vook\G;
*************************** 1. row ***************************
       Table: vook
Create Table: CREATE TABLE `vook` (
  `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=latin1
1 row in set (0.11 sec)

ERROR:
No query specified

使用EXPLAIN语句查看索引是否正确使用：

mysql> EXPLAIN SELECT * FROM vook WHERE year_publication=1900\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
1 row in set, 1 warning (0.24 sec)

ERROR:
No query specified

创建唯一索引
例：创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引。

mysql> CREATE TABLE t1(
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> UNIQUE INDEX UniqIdx(id)
    -> );
Query OK, 0 rows affected (0.40 sec)

使用SHOW CREATE TABLE查看表结构：

mysql> SHOW CREATE TABLE t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

创建单列索引
例：创建表t2，在表中的name字段上创建单列索引。

mysql> CREATE TABLE t2(
    -> id INT NOT NULL,
    -> name CHAR(50) NULL,
    -> INDEX SingleIdx(name(20))
    -> );
Query OK, 0 rows affected (0.35 sec)

查看表：

mysql> SHOW CREATE TABLE t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

创建组合索引
例：创建t3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

mysql> CREATE TABLE t3(
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> INDEX MutilIdx(id,name,age)
    -> );
Query OK, 0 rows affected (0.56 sec)

查看表：

mysql> SHOW CREATE table t3
    -> \G;
*************************** 1. row ***************************
       Table: t3
Create 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 `MutilIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.13 sec)

ERROR:
No query specified

创建全文索引

mysql> CREATE TABLE t4(
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> info VARCHAR(255),
    -> FULLTEXT INDEX FullTxtIdx(info)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.36 sec)

创建空间索引
例：创建表t5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

mysql> CREATE TABLE t5(
    -> g GEOMETRY NOT NULL,SPATIAL INDEX spatIdx(g))ENGINE=MyISAM;
Query OK, 0 rows affected (0.15 sec)

9.2.2 在已经存在的表上创建索引

使用ALTER TABLE 语句创建索引
例：在vook表中的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：

mysql> ALTER TABLE vook ADD INDEX BkNameIdx(bookname(30));
Query OK, 0 rows affected (1.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

ysql> SHOW INDEX FROM vook\G;
*************************** 1. row ***************************
        Table: vook
   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:
*************************** 2. row ***************************
        Table: vook
   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:
2 rows in set (0.11 sec)

ERROR:
No query specified

例：创建表t7，在t7的空间字段g上创建名为spatIdx的空间索引，SQL语句如下:

mysql> CREATE TABLE t7(
    -> g GEOMETRY NOT NULL
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.14 sec)

使用ALTER TABLE在表t7的g字段建立空间索引：

mysql> ALTER TABLE t7 ADD SPATIAL INDEX saptIdx(g);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用SHOW INDEX语句查看索引：

mysql> SHOW INDEX FROM t7\G;
*************************** 1. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: saptIdx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

使用CREATE INDEX创建索引
使用CREATE INDEX创建索引的基本句法如下:

CREATE [UNIQUE| FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

例：在vook表中的bookname字段上建立名为BkNameIdx的普通索引，语句如下：

mysql> CREATE INDEX BkNameIdx ON vook(bookname);

9.3 删除索引

使用ALTER TABLE删除索引
基本句法如下：

ALTER TABLE table_name DROP INDEX index_name;

使用DROP INDEX 语句删除索引
基本句法：

DROP INDEX index_name ON table_name;

9.4 综合案例

登录MySQL数据库

C:\Users\lenovo>mysql -u root -p
Enter password: ******

创建数据库index_test

mysql> CREATE database index_test;
Query OK, 1 row affected (0.30 sec)

mysql> USE index_test;
Database changed

创建表test_table1

mysql> CREATE TABLE test_table1(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> name CHAR(100) NOT NULL,
    -> address CHAR(100) NOT NULL,
    -> description CHAR(100) NOT NULL,
    -> UNIQUE INDEX UniqIdx(id),
    -> INDEX MultiColIdx(name(20),address(30)),
    -> INDEX ComIdx(description(30))
    -> );
Query OK, 0 rows affected (1.81 sec)

使用SHOW语句查看索引信息
mysql> SHOW CREATE TABLE test_table1\G;
*************************** 1. row ***************************
       Table: test_table1
Create Table: CREATE TABLE `test_table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(100) NOT NULL,
  `address` char(100) NOT NULL,
  `description` char(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UniqIdx` (`id`),
  KEY `MultiColIdx` (`name`(20),`address`(30)),
  KEY `ComIdx` (`description`(30))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.10 sec)

ERROR:
No query specified

创建表test_table2，存储引擎为MyISAM

mysql> CREATE TABLE test_table2(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> firstname CHAR(100) NOT NULL,
    -> middlename CHAR(100) NOT NULL,
    -> lastname CHAR(100) NOT NULL,
    -> birth DATE NOT NULL,
    -> title CHAR(100) NULL
    -> )ENGINE=MyISAM;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: index_test

Query OK, 0 rows affected (1.34 sec)

使用ALTER TABLE 语句在表test_table2的birth字段上，建立名称为ComDateIdx的普通索引

mysql> ALTER TABLE test_table2 ADD INDEX ComDateIdx(birth);
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用ALTER TABLE语句在表test_table2的id字段上，添加名称为UniqIdx2的唯一索引，并以降序排列。

mysql> ALTER TABLE test_table2 ADD  UNIQUE INDEX UniqIdx2(id DESC);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用CREATE INDEX 在firstname、middlename和lastname 3个字段上建立名称为MultiColIdx2的组合索引。

mysql> CREATE INDEX MultiColIdx2 ON test_table2(firstname,middlename,lastname);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用CREATE INDEX在title字段上建立名称为FTIdx的全文索引。

mysql> CREATE FULLTEXT INDEX FTIdx ON test_table2(title);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用ALTER TABLE语句删除表test_table1中名称为UniqIdx的唯一索引。

mysql> ALTER TABLE test_table1 DROP INDEX UniqIdx;
Query OK, 0 rows affected (1.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用DROP INDEX语句删除表test_table2中名称为MultiColIdx2的组合索引。


mysql> DROP INDEX MultiColIdx2 ON test_table2;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

weixin_43037507

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
第9章索引

9.2 创建索引
复制链接

扫一扫

第9章 索引

“相关推荐”对你有帮助么？

第9章索引