MySQL数据库索引

MySQL数据库索引

一、两种索引类型（MyISAM和InnoDB）

1、两者区别

1. MyISAM支持全文索引，InnoDB不支持全文索引。
2. MyISAM锁的粒度是表级，InnoDB支持行级锁定。
3. MyISAM是非事务安全的，InnoDB是事务安全的。
4. MyISAM相对简单，所以在效率上优于InnoDB，小型应用可以可以考虑使用MyISAM。
5. MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
6. InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，将非事务表切换到事务表（alter table tablename type=InnoDB）。

2、应用场景

1. MyISAM管理非事务表，它能提供高速存储、检索及全文搜索的能力。如果应用中需要执行大量的SELECT查询，那么MyISAM无疑是更好的选择。
2. InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

二、数据库索引分类

1、主键索引（PRIMARY KEY）

最常见的索引类型，能确保数据记录的唯一性及特定数据记录在数据库中的位置。

2、唯一索引（UNIQUE）

避免同一个表中某数据列中的值重复，与主键索引的区别是：主键索引只能有一个，唯一索引可有多个；主键索引唯一、非空，唯一索引可以为空。

3、常规索引（INDEX）

可快速定位特定数据。

4、全文索引（FULLTEXT）

注意：全文索引只能用于MyISAM类型的数据表，只能用于CHAR、VARCHAR、TEXT数据列类型，适合大型数据集。

三、索引的基本操作

1. 添加索引：创建表，声明列属性时添加索引

CREATE TABLE student1(
	id INT(4) PRIMARY KEY,
	name VARCHAR(10) UNIQUE,
	idcard VARCHAR(50),
	job VARCHAR(20),
	hobby TEXT,
	INDEX `idx_job`(job),
	FULLTEXT(hobby)
)ENGINE=MYISAM;

2. 添加索引：创建表，将所有列声明完毕后再添加索引

CREATE TABLE student2(
	id INT(4),
	name VARCHAR(10),
	sex VARCHAR(50),
	job VARCHAR(20),
	hobby TEXT,
	PRIMARY KEY(id),
	UNIQUE KEY(name),
	INDEX `idx_job`(job),
	FULLTEXT(hobby)
)ENGINE=MYISAM;

3. 添加索引：创建表之后，修改表时添加索引

CREATE TABLE student3(
	id INT(4),
	name VARCHAR(10),
	sex VARCHAR(50),
	job VARCHAR(20),
	hobby TEXT
)ENGINE=MYISAM;

ALTER TABLE student3 ADD PRIMARY KEY(id);
ALTER TABLE student3 ADD UNIQUE KEY(name);
ALTER TABLE student3 ADD INDEX(job);
ALTER TABLE student3 ADD FULLTEXT(hobby);

#后期添加全文索引
ALTER TABLE student3 ENGINE=MYISAM;
ALTER TABLE student3 ADD FULLTEXT(`name`);

#全文索引查询
SELECT * FROM student3 WHERE MATCH(`name`) AGAINST("刘备");
#用explain的结果，extra列会出现：using index
EXPLAIN SELECT * FROM student3 WHERE MATCH(`name`) AGAINST("刘备");

4. 显示索引信息

SHOW INDEX FROM student1;

5. 删除索引

#方法一
DROP INDEX name ON student3;

#方法二
ALTER TABLE student3 DROP INDEX job;

#删除主键索引（特殊）
ALTER TABLE student3 DROP PRIMARY KEY;