MySQL数据库索引-索引概述、创建、删除

数据库索引

索引概述

1、实例问题

• 在数据库操作中，经常需要查找特定的数据

SELECT * FROM student WHERE id=100000;

2、出现问题

• MySQL数据库必须从第1条记录开始遍历直到找到id为100000的数据
• 这样的效率非常低下

3、解决问题方案

• 建立索引，索引加快数据表的查询和排序

4、理解

• 数据库的索引好比新华字典的音序表
• 索引是对数据库表中一列或多列的值进行排序后的一种结构

5、索引的作用

• 提高查询的速度

6、索引的缺点

• 索引会占用一定的磁盘空间
• 在创建和维护索引时其消耗的时间是随着数据量的增加而同步增加的。

索引的分类

1、普通索引

• 普通索引由KEY或INDEX定义，它可以创建在任何数据类型的字段上。

2、唯一性索引

• 唯一性索引由UNIQUE定义，该索引所在字段的值必须是唯一的。

3、全文索引

• 全文索引由FULLTEXT定义，它只能创建在CHAR、VARCHAR或 TEXT类型的字段上。

4、单列索引

• 单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一性索引或者全文索引，只要保证该索引只对应表中一个字段即可。

5、多列索引

• 多列索引指的是在表中多个字段上创建索引，只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。
• 例如，在student表的id、name和score字段上创建一个多列索引；那么，只有查询条件中使用了 id字段时该索引才会被使用。

6、空间索引

• 空间索引由SPATIAL定义，它只能创建在空间数据类型的字段上。
• MySQL中空间数据类型
  • geometry（GEOMETRY）
  • point （POINT）
  • linestring （LINESTRING）
  • polygon （POLYGON）
• 注意
  • 必须将创建空间索引的字段声明为NOT NULL
  • 空间索引只能在存储引擎为MylSAM的表中创建

创建索引

1、创建表时创建索引

（1）语法如下

CREATE TABLE 表名（字段名1 数据类型[完整性约束条件],
                  字段名2 数据类型[完整性约束条件],
                  ...........
                  [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
                  [别名](索引字段名 [（长度）])[ASC|DESC])

（2）参数标识

• UNIQUE：该参数为可选项，标识唯一索引

• FULLTEXT：该参数为可选项，表示全文索引

• SPATIAL：可选参数该参数，表示空间索引

• INDEX|KEY：该参数表示字段的索弓|，二者选一即可

• 别名：该参数为可选项，表示创建的索引的名称

• 索引字段名：指定索引对应的字段的名称

• 长度：该参数为可选项，表示索引的长度

• ASC|DESC：该参数为可选项，ASC表示升序排列，DESC表示降序排列。

（3）示例编写

• 创建普通索引，在sid字段上建立了一个普通索引

-- 创建表并建立普通索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
	sid INT,
    sname VARCHAR(50),
	age INT,
	gender VARCHAR(50) DEFAULT 'male',
    INDEX(sid)
);
-- 查看表结构
SHOW CREATE TABLE student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sid=1004;

• 创建唯一性索引，在sid字段上创建了名为uniqueIndex的sid按照升序排列的唯一索引。

-- 创建表并建立唯一性索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
	sid INT,
    sname VARCHAR(50),
	age INT,
	gender VARCHAR(50) DEFAULT 'male',
    UNIQUE INDEX uniqueIndex (sid ASC)
);

-- 查看表结构
SHOW CREATE TABLE student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sid=1004;

• 创建全文索引，在sname字段上建立了一个名为fullTextIndex的全文索引。
• 注意：目前只有MylSAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引；

-- 创建表并建立全文索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
	sid INT,
    sname VARCHAR(50),
	age INT,
	gender VARCHAR(50) DEFAULT 'male',
    FULLTEXT INDEX fullTextIndex (sname)
)ENGINE=MyISAM;

-- 查看表结构
SHOW CREATE TABLE student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sname='xixi';

• 创建单列索引，在sname字段上建立了一个名为singleIndex长度为20的单列索引。

-- 创建表并建立单列索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
	sid INT,
    sname VARCHAR(50),
	age INT,
	gender VARCHAR(50) DEFAULT 'male',
    UNIQUE singleIndex (sname (20))
);

-- 查看表结构
SHOW CREATE TABLE student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sname='dodo';

• 创建多列索引，在sid和sname字段上建立了一个名为mulIndex 长度为20的多列索引。

-- 创建表并建立多列索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
	sid INT,
    sname VARCHAR(50),
	age INT,
	gender VARCHAR(50) DEFAULT 'male',
    INDEX mulIndex (sid,sname (20))
);

-- 查看表结构
SHOW CREATE TABLE student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sid=1003;

2、已有表创建索引（CREATE INDEX）

（1）语法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (字段名 [(长度)] [ASC|DESC]);

（2）示例编写

-- 创建表
DROP TABLE IF EXISTS book;
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,
  publicyear YEAR NOT NULL
)ENGINE=MyISAM;

-- 创建普通索引
CREATE INDEX bookIndex ON book(bookid);
-- 创建唯一性索引
CREATE UNIQUE INDEX uniqueIndex ON book (bookid);
-- 创建单列索引
CREATE INDEX singleIndex ON book(comment);
-- 创建多列索引
CREATE INDEX mulIndex ON book (authors(20),info(20));
-- 创建全文索引
CREATE FULLTEXT INDEX fullTextIndex ON book(info);
-- 查询表结构
SHOW CREATE TABLE book;

3、已有表创建索引（ALTER TABLE）

（1）语法

ALTER TABLE	表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX
ON 表名 (字段名 [(长度)] [ASC|DESC]);

（2）编写示例

- 创建表
DROP TABLE IF EXISTS book;
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,
  publicyear YEAR NOT NULL
)ENGINE=MyISAM;

-- 创建普通索引
ALTER TABLE book ADD INDEX bookIndex(bookid);
-- 创建唯一性索引
ALTER TABLE book ADD UNIQUE uniqueIndex(bookid);
-- 创建单列索引
ALTER TABLE book ADD INDEX singleIndex(comment);
-- 创建多列索引
ALTER TABLE book ADD INDEX mulIndex(authors(20),info(20));
-- 创建全文索引
ALTER TABLE book ADD FULLTEXT INDEX fullTextIndex(info);
-- 查询表结构
SHOW CREATE TABLE book;

删除索引

1、删除索引（ALTER TABLE）

（1）语法

ALTER TABLE 表名 DROP INDEX 索引名;

（2）示例编写

-- 创建表
DROP TABLE IF EXISTS book;
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,
  publicyear YEAR NOT NULL
)ENGINE=MyISAM;

-- 创建普通索引
ALTER TABLE book ADD INDEX bookIndex(bookid);
-- 查询表结构
SHOW CREATE TABLE book;
-- 删除索引
ALTER TABLE book DROP INDEX bookIndex;
-- 查询表结构
SHOW CREATE TABLE book;

2、删除索引（DROP INDEX）

（1）语法

DROP INDEX 索引名 ON 表名;

（2）示例编写

-- 创建表
DROP TABLE IF EXISTS book;
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,
  publicyear YEAR NOT NULL
)ENGINE=MyISAM;

-- 创建普通索引
ALTER TABLE book ADD INDEX bookIndex(bookid);
-- 查询表结构
SHOW CREATE TABLE book;
-- 删除索引
DROP INDEX bookIndex ON book;
-- 查询表结构
SHOW CREATE TABLE book;