一、索引

1、索引分类

1.1、作用

• 提高查询速度。
• 确保数据的唯一性。
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性。
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间。
• 全文检索字段进行搜索优化。

1.2、分类

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)
• 空间索引（很少用到，暂时不学习）

1.3、优势和劣势

1、优势：

• 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

2、劣势：

• 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
• 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2、主键索引

主键 : 某一个属性组能唯一标识一条记录

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY关键字。不能使用CREATE INDEX 语句创建主键索引。

特点：

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

3、唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

与主键索引的区别：

• 主键索引只能有一个
• 唯一索引可能有多个

创建唯一索引通常使用 UNIQUE关键字。

CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

4、常规索引

作用 : 快速定位特定数据

• 普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

• 普通索引允许在定义索引的列中插入重复值和空值。

• 创建普通索引时，通常使用的关键字是 INDEX 或KEY。

注意：

• index 和 key 关键字都可以设置常规索引。
• 应加在查询找条件的字段 。
• 不宜添加太多常规索引，影响数据的插入，删除和修改操作。

CREATE TABLE `result`(
    -- 省略一些代码
    INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)

-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

5、全文索引

作用 : 快速定位特定数据

• 全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

• 全文索引允许在索引列中插入重复值和空值。

• 不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

• 创建全文索引使用 FULLTEXT 关键字。

• 只能用于MyISAM类型的数据表

/*
#方法一：创建表时
	CREATE TABLE 表名 (
		字段名1 数据类型 [完整性约束条件…],
		字段名2 数据类型 [完整性约束条件…],
		[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
		[索引名] (字段名[(长度)] [ASC |DESC])
		);
		
#方法二：CREATE在已存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
	ON 表名 (字段名[(长度)] [ASC |DESC]) ;
	
#方法三：ALTER TABLE在已存在的表上创建索引
		ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
							索引名 (字段名[(长度)] [ASC |DESC]) ;
							
							
#删除索引：DROP INDEX 索引名 ON 表名字;


#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;

*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`
(`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串做为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。

EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

6、索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

7、索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；