索引

索引是一种特殊的数据结构,用来提高数据库的查询效率,MySQL中,所有的数据类型都可以被索引

普通索引

所谓普通索引,就是在创建索引时,不添加任何限制条件(唯一,非空等限制),该类型可以创建在任何数据类型的字段上

可以如下定义

CREATE TABLE tablename(
propname1 type1,
propname2 type2,
……
propnamen type…n,
INDEX | KEY
[indexname] (propnamen [(length)] [ ASC | DESC ] ) );

其中，参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；

参数indexname是索引名字，可省略；

参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；

参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；

参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。

mysql> create database school;   #创建数据库school  
mysql> use school;   #选择数据库school  
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_no(id DESC));       #创建表class, 并建立为id 字段索引  
mysql> show create table class;  #查看表结构 
mysql> insert into class values(1, '一班', 'Martin');  # 插入记录1 
mysql> insert into class values(1, '二班', 'Rock');   # 插入记录2 
mysql> select * from class where id > 0 ;   #根据id查询记录，结果将降序排列  

可以在已经定义的表上添加索引
方法一 执行create语句

CREATE INDEX indexname
ON tablename (propname [(length)] [ASC|DESC]);

参数INDEX是用来指定字段为索引，此处不能为KEY；

参数indexname是新创建的索引的名字；

参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；

参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；

参数length是可选参数，表示索引的长度，必须是字符串类型才可以使用；

参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列,默认升序。

方法二 执行alter语句

ALTER TABLE tablename
ADD INDEX | KEY indexname
(propname [(length)] [ASC|DESC]);

查看索引的执行情况
EXPLAIN 查询语句(select…)
查询的时候必须要用到才能看到

输出结果

key： 实际使用的索引。如果为NULL，则没有使用索引

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引 将被列出，但不一定被查询实际使用

key_len： 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！(计算机默认给的越短越好)

唯一索引

所谓唯一索引,就是在创建索引时,限制索引的字段必须时唯一的.

简单的说,就是该索引比普通索引更快

创建表时创建索引:
CREATE TABLE tablename(
propname1 type1,
……
propnamen type…n,
UNIQUE INDEX | KEY[indexname] (propnamen [(length)] [ ASC | DESC ] ) );

大概同上,只是加了一个UNIQUE INDEX

全文索引

全文索引主要对字符串类型建立基于分词的索引,主要基于CHAR,VARCHAR和TEXT的字段上,以便能够快速查询数据量较大的字符串类型的字段

创建表时创建索引:

CREATE TABLE tablename(
propname1 type1,
propname2 type2,
……
propnamen type…n,
FULLTEXT INDEX | KEY
[indexname] (propnamen [(length)] ) );

使用全文检索数据
select * from [表名] where match(列名) against(‘关键字’)

配置中文分词全文检索

配置文件my.ini(有的是其他的)（Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0） 中增加如下配置项，同时重启MySQL80 服务:

76行下一行加上
ngram_token_size=2

让数据库使中文每俩个字作为一个词经行检索
配置好后重启

创建表时有稍微不同,在结尾加上 with parser ngram

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_des(comment) with parser   ngram);       #创建表class, 并建立为comment 字段为全文索引

多列索引

多列索引,是指在创建索引时所关联的字段不是一个字段,而是多个字段,虽然可以通过所关联的字段进行查询,但是只有查询条件中使用了所关联字段的第一个字段,多列索引才会被使用

创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
……
propnamen type…n,
INDEX | KEY [indexname] (propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
… …
Propnamen [(length)] [ ASC | DESC ])
);
注意：和普通索引定义基本相同，不同之处就是增加了多个索引列。

追加多列索引

create index 索引名 on 表名(列名1,列名2…);
alter table 表名 add index 索引名(列1,列2);

隐藏索引

隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。

隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #取消隐藏

删除索引

drop index indexname on tablename;