MySQL8.0.19索引

一，创建index

语法：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type]
    ON tbl_name (key_part,...)

key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type: USING {BTREE | HASH}

1.1创建唯一索引

create unique index index_book_id on book(id desc)
//book表的id创建唯一索引，降序；（默认升序）

1.2创建普通索引

create index index_book_name on book(book_name)
//book_name：创建普通索引，可以指定长度；book_name(10)
create index index_book_name on book(book_name(10))

1.3多列索引

当查询条件要使用多个列时，可以建立多列索引；

create index num_name_com on book(name,number,commits);

1.3.1最左原则

先看几个测试

create index num_name_com on book(name,number,commits);//engine=InnoDB,index_type=BTree
//查询字段：name,number,commits;使用了索引num_name_com 
explain select * from book where name like 'sdf%' and number >2 and commits like 'sdss%';
//查询字段：name,number;使用了索引num_name_com 
explain select * from book where name like 'sdf%' and number >2;
//查询字段：name,commits;使用了num_name_com 
explain select * from book where name like 'sdf%' and commits like 'sdfs%';

//查询字段：number;没有使用索引
explain select * from book where number >2;
//查询字段：commits ;没有使用索引
explain select * from book where commits like 'sdss%';
//查询字段：number ,commits;没有使用索引
explain select * from book where number >1 and commits like 'sdfsdfss%';

为什么有 name作为查询条件的使用了索引，没有使用name作为查询条件的没有使用索引？
       当b+树的数据项是复合的数据结构，比如(name,number,commits)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(‘QWEQ’,1,‘QWE’)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较number和commits，最后得到检索的数据；但当(number,commits)这样的没有name的数据的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(‘QWEQ’,‘QWE’)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段number的缺失，所以只能把名字等于QWEQ的数据都找到，然后再匹配commits是QWE的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

1.4全文索引

//字段长度很长时适合创建该索引；
create fulltext index index_name on table_name( field_name);

1.5在创建索引时指定索引的数据结构

//HASH索引
create index index_name using HASH ON TABLE_NAME(FILED);
//BTREE索引
create index index_name using BTREE ON TABLE_NAME(FILED);

MySQL各类存储引擎，索引的数据结构类型

Storage Engine	Permissible Index Types
InnoDB	BTree
MyISAM	BTree
MEMORY	BTree,HASH
NDB	BTree,HASH

在InnoDB中只能创建BTree类型的索引，所在以创建时指定类型为HASH时不会生效，最终的类型仍然会是BTree；

MEMORY中支持2种索引；还可以看到主键索引默认使用:HASH

二，查询索引

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

使用：

show index in book from ssm;//查找ssm数据库中book表的索引
等同下面：
show index in ssm.book 

查询结果字段说明

字段	说明
Table	表的名称
Non_unique	如果索引不能包含重复项，则为0；如果可以，则为1
Key_name	索引名称；【如果建立多列索引，会有多个相同的key_name】
Seq_in_index	索引中的列序号，从1开始；【如果建立多列索引，序列号与创建索引时字段的顺序一致】
Column_name	列名
Collation	列在索引中的排序方式。它可以具有值 A（升序），D （降序）或NULL（未排序）
Cardinality	索引中唯一值数量的估计（不是准确数字）
Null	可以为null，YES;不允许值为 ‘’
Index_type	索引的数据结构：BTREE, FULLTEXT, HASH, RTREE
Visible	索引是否对优化器可见

多列索引：

3.删除索引

DROP INDEX index_name ON tbl_name

4.MySQL存储引擎特性

InnoDB存储引擎索引特性

Index Class	Index Type	Stores NULL VALUES（存储空值）	Permits Multiple NULL Values(允许多个空值)	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key(主键)	BTREE	NO	NO	N/A	N/A
Unique(唯一索引)	BTREE	Yes	Yes	Index	Index
Key（普通索引）	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	table	table
SPATIAL	N/A	NO	NO	N/A	N/A

MyISAM 存储引擎索引特性

Index Class	Index Type	Stores NULL VALUES（存储空值）	Permits Multiple NULL Values(允许多个空值)	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key(主键)	BTREE	NO	NO	N/A	N/A
Unique(唯一索引)	BTREE	Yes	Yes	Index	Index
Key（普通索引）	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	table	table
SPATIAL	N/A	NO	NO	N/A	N/A

MEMORY 存储引擎索引特性

Index Class	Index Type	Stores NULL VALUES（存储空值）	Permits Multiple NULL Values(允许多个空值)	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key(主键)	BTREE	NO	NO	N/A	N/A
Unique(唯一索引)	BTREE	Yes	Yes	Index	Index
Key（普通索引）	BTREE	Yes	Yes	Index	Index
Primary key(主键)	HASH	NO	NO	N/A	N/A
Unique(唯一索引)	HASH	Yes	Yes	Index	Index
Key（普通索引）	HASH	Yes	Yes	Index	Index

查看MySQL支持的引擎

show  engines;

engine	support	comment	transaction	XA	savepoint
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tablestables	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
InnoDB	YES	Supports transactions, row-level locking, and foreign keys	YES	YES	YES

参考：博客一
官网连接