mysql索引

索引分类

1.普通索引：不附加任何限制条件，可创建在任何数据类型中

2.唯一性索引：使用unique参数可以设置索引为唯一性索引，在创建索引时，限制该索引的值必须唯一，主键就是一种唯一性索引

3.全文索引：使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar或text类型的字段上。查询数据量较大的字符串类型字段时，效果明显。但只有MyISAM存储引擎支持全文检索

4.单列索引：在表中单个字段上创建的索引，单列索引可以是任何类型，只要保证索引只对应一个一个字段

5.多列索引：在表中多个字段上创建的索引，该索引指向创建时对应的多个字段

6.空间索引：使用spatial参数可以设置索引为空间索引，空间索引只能建立在空间数据类型上比如geometry，并且不能为空，目前只有MyISAM存储引擎支持

示例表：

CREATE TABLE `DICTIONARY_ITEM` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `dic_id` bigint(20) NOT NULL COMMENT '数据字典表中字典Id',
  `dic_item_key` varchar(255) DEFAULT NULL COMMENT '字典编码',
  `dic_item_value` varchar(512) DEFAULT NULL COMMENT '值',
  `dic_item_alias` varchar(512) DEFAULT NULL COMMENT '别名',
  `status` int(2) DEFAULT '1' COMMENT '状态',
  `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '父项ID',
  `product_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '产品ID',
  `tenant_id` varchar(64) NOT NULL DEFAULT '0' COMMENT '租户ID',
  `create_by` varchar(64) NOT NULL DEFAULT '0' COMMENT '创建人账号',
  `update_by` varchar(64) DEFAULT NULL COMMENT '修改人账号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_value` (`dic_id`,`dic_item_value`,`product_id`,`parent_id`) USING BTREE,
  KEY `dic_idx` (`dic_item_value`,`product_id`),
  KEY `dic_translate_idx` (`dic_id`,`status`,`product_id`,`parent_id`),
  KEY `dic_item_idx` (`dic_id`,`dic_item_value`,`product_id`,`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=240638329 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

--主键索引
PRIMARY KEY (`id`)
--唯一联合索引
UNIQUE KEY `unique_value` (`dic_id`,`dic_item_value`,`product_id`,`parent_id`) USING BTREE
--普通联合索引
KEY `dic_idx` (`dic_item_value`,`product_id`)
KEY `dic_translate_idx` (`dic_id`,`status`,`product_id`,`parent_id`)
KEY `dic_item_idx` (`dic_id`,`dic_item_value`,`product_id`,`parent_id`)

创建索引

在创建表时创建索引

--创建普通索引
create table index1(
id int,
name varchar(20),
sex boolean,
index(id)
);

此处在id字段上创建索引，show create table可查看

--创建唯一性索引
create table index2(
id int unique,
name varchar(20),
unique index index2_id(id ASC)
);

此处使用id字段创建了一个名为index2_id的索引

这里的id字段可以不设置唯一性约束，但这样一来索引就没有作用

--创建全文索引
create table index3(
id int,
info varchar(20),
fulltext index index3_info(info)
)engine=MyISAM;

要注意创建全文索引时只能使用MyISAM存储引擎

--创建单列索引
create table index4(
id int,
subject varchar(30),
index index4_st(subject(10))
);

此处subject字段长度是30,而索引长度则是10

这么做的目的在于提高查询速度，对于字符型的数据不用查询全部信息

--创建多列索引
create table index5(
id int,
name varchar(20),
sex char(4),
index index5_ns(name,sex)
);

可以看出，这里使用了name字段和sex字段创建索引列

--创建空间索引
create table index6(
id int,
space geometry not null,
spatial index index6_sp(space)
)engine=MyISAM;

这里需要注意空间space字段不能为空，还有存储引擎

在已经存在的表上创建索引

--创建普通索引
create index index7_id on example0(id);

这里在现有表的id字段上创建了一条名为index7_id的索引

--创建唯一性索引
create unique index index8_id on example1(course_id);

此处只需要在index关键字前面加上unique即可

至于表中的course_id字段，最要也设置唯一性约束条件

--创建全文索引
create fulltext index index9_info on example2(info);

fulltext关键字用来设置全文引擎，此处的表必须是MyISAM存储引擎

--创建单列索引
create index index10_addr on example3(address(4));

此表中address字段的长度是20,这里只查询4字节，不需要全部查询

--创建多列索引
create index index11_na on example4(name,address);

索引创建好之后，查询中必须有name字段才能使用

--创建空间索引
create spatial index index12_line on example5(space);

这里需要注意存储引擎是MyISAM，还有空间数据类型

用alter table语句来创建索引

--创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

--创建普通索引
mysql> alter table example6 add index `index13_n`(name(20));

--创建唯一性索引
alter table example7 add unique `index` index14_id(id);

--创建全文索引
alter table example8 add fulltext `index` index15_info(info);

--创建单列索引
alter table example9 add index `index16_addr`(address(4));

--创建多列索引
alter table example10 add index `index17_in`(id,name);

--创建空间索引
alter table example11 add spatial index `index18_space`(space);

到此，三种操作方式，每种索引类别的建立就都列举了

对于索引，重要的是理解索引的概念，明白索引的种类

更多的是自己的使用经验

删除索引

--删除索引
-- table_name表名，column_list列名，index_name索引名
-- 删除index索引
ALTER TABLE table_name DROP INDEX index_name
DROP INDEX index_name ON talbe_name
-- 删除主键索引（只能用ALTER）
ALTER TABLE table_name DROP PRIMARY KEY
-- 删除唯一索引
ALTER TABLE table_name DROP INDEX column_list;
ALTER TABLE table_name DROP INDEX index_name
DROP INDEX index_name ON talbe_name

查看索引

SHOW INDEX FROM table_name;
SHOW KEYS FROM table_name;