Mysql学习笔记之【索引】

索引的分类

1. 按功能逻辑上说，索引有四种：普通索引，唯一索引，主键索引，全文索引
2. 按物理实现方式说：索引分为聚簇索引和非聚簇索引（二级索引）
3. 按字段个数说：索引分单列索引和联合索引

普通索引
在创建普通索引时，不添加任何限制条件，只是用于提高查询效率，可以添加到任何类型的字段上。

唯一索引
创建索引时，用UNIQUE参数限制，限制该字段值是唯一的，但是允许有NULL值，一张数据表中可以有多个唯一索引列。

主键索引
主键索引是一种特殊的唯一索引，在唯一索引的基础上加上了非空的约束。就是UNIQUE + NOT NULL，一张表里只能有一个主键索引。

全文索引
使用 FULLTEXT 可以设置全文检索，全文索引列中可以有重复值和空值，全文索引只能建在CHAR,VARCHAR,TEXT类型及系列类型字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

索引的创建

方式一：创建表时创建索引

1.隐式的方式创建，在声明有主键约束，唯一约束，外键约束的字段上，会自动添加相关的索引

CREATE TABLE employee(
	e_id INT PRIMARY KEY AUTO_INCREMENT,
	e_email VARCHAR(50) UNIQUE,
	e_name VARCHAR(20)
); 

2.显示的方式创建
基本语法格式如下

CRATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY ] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE 、FULLTEXT和SPICIAL 为可选参数，分别表示，唯一索引、全文索引和空间索引；
• INDEX 和KEY 为同义词，两者作用相同，用来知道创建索引；
• index_name 声明索引名称，为可选参数，如果不指定，MYSQL 默认用col_name 做索引名称；
• col_name 为需要添加索引的字段列；
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC或者DESC 指定升序或者降序的索引值存储。
  例：

创建普通索引：

CREATE TABLE student(
	stu_id INT,
	stu_name VARCHAR(50),
	stu_number CHAR(10),
	INDEX idx_stuname(stu_name)
);

创建唯一索引：

CREATE TABLE student(
	stu_id INT,
	stu_name VARCHAR(50),
	stu_number CHAR(10),
	UNIQUE INDEX idx_stuname(stu_name)
);

创建联合索引：

CREATE TABLE student(
	stu_id INT,
	stu_name VARCHAR(50),
	stu_number CHAR(10),
	INDEX mul_idx_sname_snumber(stu_name,stu_number)
);

注意：联合索引命中要遵循最左前缀原则。

方式二：在表创建之后创建索引
1.

ALTER TABLE table_name ADD [UNIQUE] INDEX index_name(column_name);

CREATE INDEX index_name ON table_name(column_name);

索引查看

方式一：

SHOW CREATE TABLE student;

方式二：

SHOW INDEX FROM student;

索引删除

方式一：

ALTER TABLE table_name DROP INDEX index_name;

注意：添加AUTO_INCREATMENT约束的字段的唯一索引不能被删除

方式二：

DROP INDEX index_name ON table_name;

注意：删除表中的列时，如果删掉的列是索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列被删除，则整个索引被删除。

MySQL 8.0新特性

1. 支持降序索引（仅限于InnoDB存储引擎）。
2. 隐藏索引（删除前可以先隐藏索引查看一下是否出错，或者性能的影响）
   注意：主键不能被设置为隐藏索引。当表中没有显示主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引
   语法：

1.创建表时

CREATE TABLE student(
	stu_id INT,
	stu_name VARCHAR(50),
	stu_number CHAR(10),
	INDEX idx_stuname(stu_name) invisible
);

2.创建表后

ALTER TABLE student ADD INDEX idx_stuname(stu_name) invisble;

CREATE INDEX idx_stunumber ON  student(stu_number) invisible;

修改索引的可见性

ALTER TABLE student ALTER INDEX visible | invisible;

注意：当索引被隐藏时，它的内容仍然和正常索引一样实时更新的。如果一个索引需要长时间被隐藏，那么可以将其删除，因为索引的存在会影响插入，更新和删除的性能。

适合添加索引的情况

1. 字段的数值有唯一性的限制
   如果某个字段是唯一的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地查找某条记录。
   注意：业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
   说明：不要以为唯一索引影响了insert速度，这个速度的损耗可以忽略，但是提高查询速度是明显的。

2. 频繁的作为WHERE查询条件的字段

3. 经常GROUP BY 和ORDER BY 列
   索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROUP BY 对数据进行分组查询，或使用ORDER BY 对数据进行排序的时候，就需要对分组或排序的字段进行索引。如果排序的列有多个，可以在这些列上建立组合索引。

4. UPDATE、DELETE的WHERE条件列
   原理是因为我们需要先根据WHERE条件列检索出来这条记录，然后对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5. DISTINCT字段需要创建索引

6. 多表JOIN连接操作时，创建索引的注意事项
   首先，连接表的数据尽量不要超过3张，因为每增加一张表就相当于增加一次嵌套循环，数量级增长非常快，严重影响查询的效率。
   其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。
   最后，对于连接的字段创建索引，并且该字段在多张表中类型必须一致。

7. 使用列的类型小的创建索引
   这里说的类型大小指的是该类型表示数据范围的大小。
   以整数类型来说，有TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围也是依次递增。我们想要对某个整数列建立索引的话，在表示整数范围允许的情况下，尽量让索引使用较小的类型，比如能用INT就不要用BIGINT,能使用MEDIUMINT就不要使用INT。因为：

• 数据类型越小，在查询时进行的比较操作越快
• 数据类型越小，索引占用的空间就越少，在一个数据页内就可以放下更多的记录 ，从而减少磁盘的I/O带来的性能消耗，也就意味着可以把更多的数据页缓存到内存中，从而加快读写效率。

8. 使用字符串前缀创建索引
   创建一个商户表，由于地址字段比较长，在地址字段上建立前缀索引

CREATE TABLE shop(address VARCHAR(150)) NOT NULL);
CREATE INDEX idx_address(12) ON shop;

如何计算不同长度的选择性呢？
先看一下字段在全部数据中的选择读：

SELECT COUNT(DISTINCT address) / COUNT(*) FROM shop;

通过不同长度去计算，与全表选择性对比：
公式：

COUNT(DISTINCT LEFT(col_name,index_length) / COUNT(*)

值越接近于1越好，如果几个长度值差别不大，选最短的。

9. 区分度高（散裂性高）的列适合作为索引

可使用SELECT COUNT(DISTINCT a) / COUNT(*) FROM t1; 计算区分度，越接近于1越好，一般超过33%就算是比较高效是索引了。

10. 使用最频繁的列放在联合索引的左侧
    ‘最左前缀原则’
    补充：在多个字段都要创建索引的情况下，联合索引优于单列索引

限制索引的数量

单表索引数量最好不要超过6个。