MYSQL学习笔记九（索引）

一、索引概念

1.1 概念

在MySQL中，索引的关键字为index也叫做“键（key），是存储引擎用于快速找到记录的一种数据结构。
当表中的数据量越来越大时，索引对于良好的性能非常关键。索引优化应该是对查询性能优化最有效的手段，创建一个真正最优的索引经常需要重写SQL查询语句。
注意：索引一般创建在经常的查询的字段上。

1.2 索引的工作原理

要理解MySQL中索引的工作原理，最简单的方法就是去看一看一本书的索引部分：比如你想在一本书中寻找某个主题，一般会先看书的索引目录，找到对应的章节、对应的页码后就可以快速找到你想看的内容。

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中查找对应的值，然后根据匹配的索引记录找到对应的数据行，最后将数据结果集返回给客户端。

1.3 索引的类型

mysql索引的基础类型有四种，分别是主键索引、唯一索引、普通索引和全文索引。
主键索引:
主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。
唯一索引:
索引列的所有值都只能出现一次，即必须唯一，值可以为空。
普通索引 :
基本的索引类型，值可以为空，没有唯一性的限制。
全文索引:
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。
全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技术处理中文。

二、索引创建

#创建测试表，创建表的脚本如下：
CREATE TABLE data1
(
ID INT,  
studentNumber NVARCHAR(30),
subjectName NVARCHAR(30),
score INT
)

2.1 创建主键索引

创建主键索引的语法结构如下：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

eg：为data1创建列为ID的主键索引，脚本语句如下：

ALTER TABLE data1 ADD PRIMARY KEY (ID) 

2.2 创建唯一索引

创建唯一索引的语法结构如下：

ALTER TABLE `table_name` ADD UNIQUE ( 
column` 
) 

eg：为data1创建列为studentNumber的唯一索引，脚本语句如下：

ALTER TABLE data1 ADD UNIQUE ( 
studentNumber
) 

2.3 创建普通索引

创建普通索引的语法结构如下：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

eg：为data1创建列为subjectName 的普通索引，脚本语句如下：

ALTER TABLE data1 ADD INDEX index_name (subjectName) 

2.4 创建全文索引

创建全文索引的语法结构如下：

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

eg：为data1创建列为subjectName 的全文索引，脚本语句如下：

ALTER TABLE data1 ADD FULLTEXT INDEX fulltext_123(studentNumber,subjectName);

注意，执行上述脚本时候，有时候会出现如下错误信息：

报此错误是因为不支持全文索引，解决办法：

1、查看创建表时用的哪种引擎，如果是InnoDB，改为MyISAM，InnoDB不支持FULLTEXT类型的索引
2、查看配置文件mysql.ini，搜索default-storage-engine= ，如果是InnoDB，改为MyISAM。重启MySQL服务。

三、索引查看

索引查询有两种方式，分别如下：

方法一：

show index from tblname;

方法二：

show keys from tblname;

四、索引删除

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY