Mysql不止CRUD，聊聊索引( 四 ) 我想很多人对mysql的认知可能就

更详细说明见官网：
2.3、创建索引创建索引的语法（如下都是默认的innodb存储引擎）：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name[index_type]ON tbl_name (key_part,...)[index_option][algorithm_option | lock_option] ...key_part: {col_name [(length)] | (expr)} [ASC | DESC]index_option: {KEY_BLOCK_SIZE [=] value| index_type| WITH PARSER parser_name| COMMENT 'string'| {VISIBLE | INVISIBLE}| ENGINE_ATTRIBUTE [=] 'string'| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'}index_type:USING {BTREE | HASH}algorithm_option:ALGORITHM [=] {DEFAULT | INPLACE | COPY}lock_option:LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}可以在创建的表的时候建立索引，也可以对创建好的表建立索引。
2.3.1、基于创建表时建立索引CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。
1、创建普通索引
CREATE TABLE test.`user2` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(50) DEFAULT NULL,`sex` varchar(5) DEFAULT NULL,`address` varchar(100) DEFAULT NULL,`birthday` datetime NOT NULL,PRIMARY KEY (`id`),index idx1(username));show index from test.`user2`;
文章插图
2、创建唯一索引
CREATE TABLE test.`user2` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(50) DEFAULT NULL,`sex` varchar(5) DEFAULT NULL,`address` varchar(100) DEFAULT NULL,`birthday` datetime NOT NULL,`score_num` int(11) NOT NULL UNIQUE,PRIMARY KEY (`id`),unique index idx1(username));show index from test.`user2`;
文章插图
前面两个索引都是通过主键和唯一约束自动创建的
3、创建组合索引
注意：最左前缀原则
CREATE TABLE test.`user2` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(50) DEFAULT NULL,`sex` varchar(5) DEFAULT NULL,`address` varchar(100) DEFAULT NULL,`birthday` datetime NOT NULL,`score_num` int(11) NOT NULL UNIQUE,PRIMARY KEY (`id`),index idx1(id,score_num,username));
文章插图
4、创建全文索引
FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引
CREATE TABLE test.`user2` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(50) DEFAULT NULL,`sex` varchar(5) DEFAULT NULL,`address` varchar(100) DEFAULT NULL,`birthday` datetime NOT NULL,`score_num` int(11) NOT NULL UNIQUE,PRIMARY KEY (`id`),fulltext index idx1(username));
文章插图
5、创建空间索引
create table test.test(position geometry not null,spatial index idx1(position));show index from test.test;
文章插图
6、创建前缀索引
CREATE TABLE test.t1 (col1 VARCHAR(10),col2 VARCHAR(20),INDEX (col1, col2(10)));
文章插图
前缀索引的目的主要是减少字符串作为索引占用的空间，提高查询速度
2.3.2、基于已创建好的表创建索引在已经存在的表中创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句创建索引。
1、使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]2、使用CREATE INDEX创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ONtable_name (col_name[length],...) [ASC|DESC]2.4、删除索引1、使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_nameCREATE TABLE test.t1 (id int AUTO_INCREMENT primary key,col1 VARCHAR(10),col2 VARCHAR(20),INDEX (col1, col2(10)));
文章插图
alter table test.t1 drop index col1;
alter table test.t1 drop index PRIMARY; #会报错：添加AUTO_INCREMENT约束字段的唯一索引不能被删除
文章插图
2、使用DROP INDEX语句删除索引
DROP INDEX index_name ON table_name;2.5、聚集索引和二级索引1、聚集索引
InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。