MySQL 索引

什么是索引，索引的分类

含义：索引是一个单独的，存储在磁盘上的数据库结构，他有数据库中所有记录的引用指针
好处：比如说可以提高数据库的查询速度
索引是在存储引擎中实现的，不同的存储引擎的索引不一定相同，MySQL中索引存储类型有两种，BTREE和HASH
myisam 和 innodb 支持BTREE， 而memory和 heap可以支持BTREE和HASH
索引的优点以及不利处
优点：
1. 建立索引可以保证数据的唯一性
2. 加快查询速度也是创建索引最主要的原因
3. 使用索引去建立表与表之间的联系会更快
4. 使用分组和排序的时候也可以显著的提高速度
不利处：6
1. 创建索引以及维护索引需要耗费时间。
2. 索引需要占用磁盘空间，如果索引量大的话，可能大小会超过表的本身
3. 对表进行增 删 改的时候，索引是需要动态维护的

索引分类

1. 普通索引，唯一索引
2. 单列索引，组合索引
3. 全文索引
4. 空间索引

创建索引

建表时创建索引

语法格式

create table 表名(
[col_name ,col_type]
[UNIQUE | FULLTEXT | SPATIAL ] [INDEX | KEY ] [ index_name ]  col_name
)

• 在book表中的year_publication字段上建立普通索引，

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)
);

explain 分析sql语句

select_type

select_type 为查询的类型，
simple：简单的查询
union：联合
primary：有子查询或union 位于最外层的select_type 为primary
sbuquery：子查询

type

指定了本数据表与其他表的关系

possible_key

MySQL在查询时可选用的索引

key

MySQL 实际选用的索引

key_len

索引按字节计算的长度，key_len 越小，表示越快

ref

关联关系中另一个数据表里的数据列名字

rows

预计要读取的数据行个数

extra

关联操作有关信息

唯一索引

创建唯一索引

CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

由结果可以看到，id字段上已经成功建立了一个名为UniqIdx的唯一索引。

单列索引

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

CREATE TABLE t2
(
id   INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);

组合索引(多列)

创建表t3，在表中的id、name和age字段上建立组合索引，

CREATE TABLE t3
(
id    INT NOT NULL,
name CHAR(30) 　NOT NULL,
age  INT NOT　 NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, age(100))
);

组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如这里由id、name和age 3个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：（id, name, age）、（id, name）或者id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用索引查询。

全文索引

FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部（前缀）索引。

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;

空间索引

创建表t5，在空间类型为GEOMETRY的字段上创建空间索引

CREATE TABLE t5
( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;

创建索引的其他方式

ALTER TABLE book ADD INDEX BkNameIdx( bookname(30) );
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );
ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );

CREATE INDEX BkNameIdx ON book(bookname);
CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );
CREATE INDEX BkcmtIdx ON book(comment(50) );
CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );

查看一个表建立的索引
show index from 表名

删除索引

alter table 表名 drop index 索引名

drop index 索引名 on 表名