Mysql中索引的分类、增删改查与存储引擎对应关系

场景

Mysql中存储引擎简介、修改、查询、选择：

Mysql中存储引擎简介、修改、查询、选择-CSDN博客

上面介绍Mysql的存储引擎。

下面介绍索引的分类和使用。

注：

博客：
霸道流氓气质-CSDN博客

实现

Mysql中索引的分类

1、普通索引和唯一索引

普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引：：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一 。

主键索引是一种特殊的唯一索引，不允许有空值。

2、单列索引和组合索引

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。

组合索引：在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时索引才会被使用。

使用组合索引遵循最左前缀集合。

3、全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

全文索引可以在 CHAR VARCHAR 或者 TEXT 类型的列上创建。在 MySQL中只有MyISAM存储引擎支持全文索引。

4、空间索引

空间索是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有4种，分别是GEOMETRY POINT LINSTRNG POLYGON。

MySQL 使用 SPATIAL关键字进行扩展，使得能够用与创建正规索引类似的语法创建空间索引。

创建空间索引的列必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

Mysql中创建和查看索引

Mysql在创建表时创建索引

在使用 CREATE TABLE 创建表时 除了可以定义列的数据类型以外，还可以定义主键约束、外键约束或者唯一性约束，

而不论创建哪种约束 在定义约束时相当于在指定列上创建了一个索引。

创建和查看普通索引

CREATE TABLE fruit (
 id INT NOT NULL,
 NAME VARCHAR ( 50 ) NOT NULL,
 price DECIMAL ( 8, 2 ) NOT NULL,
 city VARCHAR ( 100 ) NOT NULL,
INDEX ( city )
);

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构。

SHOW CREATE TABLE fruit;

插入两条测试数据

INSERT INTO fruit VALUES(1,'苹果',5.20,'北京');
INSERT INTO fruit VALUES(2,'香蕉',2.20,'上海');

使用EXPLAIN 语句查看索引是否正在使用

EXPLAIN SELECT * FROM fruit WHERE city = '北京';

输出结果

1 SIMPLE fruit  ref city city 302 const 1 100.00 

可以看到，possible_keys key的值都为city，在查询时使用了索引。

Mysql如何在已经建立的表上建立索引

方法1：

使用alter table语句 后面是索引名(字段名)

ALTER TABLE fruit add index city(city);

方法2：

使用create index 语句 前面是索引名，后面是字段名

CREATE INDEX city on fruit(city)

Mysql中创建和查看唯一索引

上面新建的是普通索引，唯一索引与前面的普通索引类似，不同的是索引列的值必须唯一，但允许有空值。

如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX id_index ON fruit(id);

Mysql中创建和查看多列索引

组合索引是在多个字段上创建一个索引。

CREATE INDEX group_index on fruit(id,name,price);

可以验证如果查询时包含了id则使用了索引，否则不使用索引

EXPLAIN SELECT * FROM fruit WHERE id='1' AND city = '北京';

EXPLAIN SELECT * FROM fruit WHERE name='苹果' AND city = '北京';

Mysql中创建和查看全文索引

全文索引可以用于全文搜索。只有 MyISAM 存储引擎支持全文索引 ，从MySQL 5.6开始InnoDB也支持全文索引。

 并且只为 CHAR VARCHAR TEXT列。全文索引只能添加到整个字段上，不支持局部（前缀）索引。

CREATE FULLTEXT INDEX ft_index ON fruit(name);

全文索引非常适合大型数据集，对于小的数据集， 它的用处可能比较小。

关于存储引擎，常用sql

-- 查看系统所支持的引擎类型
SHOW ENGINES;
-- 查看当前默认的存储引擎
SELECT @@default_storage_engine;
-- 查看特定库中所有表的存储引擎
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ry-vue-studydemo';
-- 查看单个表的存储引擎
SHOW TABLE STATUS LIKE 'fruit';
-- 更改表的存储引擎
ALTER TABLE fruit ENGINE=MyISAM;
ALTER TABLE fruit ENGINE=InnoDB;

Mysql中删除索引

1、使用DROP INDEX 删除索引

DROP INDEX city ON fruit;

2、使用ALTER TABLE 删除索引

ALTER TABLE fruit DROP INDEX ft_index;