MySQL8 索引分类

一、MySQL8 索引介绍

提高性能的最佳方法就是，在一个或多个列上创建索引，索引允许查询条件与结果快速匹配。虽然添加索引很方便，但是过多的索引会浪费空间资源，索引还会增加插入、更新和删除数据的成本，这些操作会导致每一个索引都更新一遍。

1.1、索引用途

索引用于快速查找具有特定列值的行，如果没有索引，MySQL 必须从第一行开始，然后通读整个表以找到相关行。如果有相关列的索引，MySQL 可以快速确定位置，而无需检索全部数据，这比顺序读取要快得多。

1.2、索引操作

• 索引可以快速与 where 条件进行匹配；
• 存在多索引时，默认使用最少行数的索引；
• 多列索引，按照顺序优先命中左侧索引；
• 表关联的索引列，字段的类型大小一致会更高效。

二、MySQL8 索引分类

2.1、主键索引

主键代表最重要的一列或一组列，主键索引可以根据主键列进行超快查找和排序，如果您的表数据量非常多还很重要，但是又没有合适的列作为主键，那么您可以选择一个自动递增的列作主键。

创建主键索引，在线示例：

CREATE TABLE try8_tb (
  try8_col INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (try8_col)
);
ALTER TABLE try8_tb ADD PRIMARY KEY (try8_col); //修改主键
ALTER TABLE try8_tb DROP PRIMARY KEY; //删除主键
SHOW INDEX FROM try8_tb //查看索引

2.2、前缀索引

MySQL8 前缀索引就是基于原始的索引字段，截取前面指定的字符数或字节数用作索引。

创建前缀索引，在线示例：

CREATE TABLE try8_tb (try8_col BLOB, INDEX(try8_col(5)));

2.3、全文索引

全文索引是通过关键字来进行匹配，主要用于相似度而非精确度的数值检索。与常规 like 模糊匹配不同，like 一般用于文本较少的使用场景，全文索引主要用于比较大的文本数据检索。

创建全文索引，在线示例：

CREATE TABLE try8_tb ( 
  try8_id INT NOT NULL AUTO_INCREMENT,
  try8_col VARCHAR(200) NOT NULL,
  PRIMARY KEY (try8_id),
  FULLTEXT KEY try8_full (try8_col) 
); 

2.4、多列索引

多列索引也称为复合索引，一个复合索引最多可以包含 16 列，每一列都是有顺序的。

创建多列索引，在线示例：

CREATE TABLE try8_tb ( 
  try8_id INT NOT NULL AUTO_INCREMENT,
  try8_col1 VARCHAR(45) NOT NULL,
  try8_col2 VARCHAR(45) NOT NULL,
  PRIMARY KEY (try8_id),
  INDEX try8_index (try8_col1, try8_col2) 
); 

2.5、隐形索引

MySQL8 隐形索引也称为不可见索引，主要是指不让优化器使用的索引，该功能可用于除主键以外的其它索引。此功能可用于在无须删除索引的情况下快速测试查询性能，防止对表进行破坏性更改。

创建隐形索引，在线示例：

CREATE TABLE try8_tb (
  try8_i INT,
  try8_j INT,
  try8_k INT,
  INDEX try8_i_inv (try8_i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX try8_j_inv ON try8_tb (try8_j) INVISIBLE; //创建不可见索引
ALTER TABLE try8_tb ADD INDEX try8_k_inv (try8_k) VISIBLE; // 修改为可见索引

2.6、降序索引

MySQL8 支持降序索引：DESC 在索引定义中不再被忽略，而是导致按降序存储键值。以前，可以按相反顺序扫描索引，但会降低性能，降序索引可以正向扫描，效率更高。

创建降序索引，在线示例：

CREATE TABLE try8_tb (
  try8_a INT, try8_b INT,
  INDEX try8_idx1 (try8_a ASC, try8_b ASC),
  INDEX try8_idx2 (try8_a ASC, try8_b DESC),
  INDEX try8_idx3 (try8_a DESC, try8_b ASC),
  INDEX try8_idx4 (try8_a DESC, try8_b DESC)
);

2.7、唯一索引

唯一索引要求索引列的值必须唯一，允许有空值，因为 NULL 代表不同的值，如果是组合索引，则组合值必须唯一。

创建唯一索引，在线示例：

CREATE TABLE try8_tb (  
  try8_name VARCHAR(45) NOT NULL,  
  UNIQUE KEY try8_idx (try8_name)  
);  

三、MySQL8 索引分类 总结

MySQL8教程 - MySQL8 索引分类，主要用于介绍 MySQL 和 MySQL8 数据库，从 MySQL8 索引介绍开始，逐步到 MySQL8 索引分类和在线示例等，通篇实用易懂，让 MySQL8 入门更加快速和简单。

《MySQL8教程》主打原创、全部免费，欢迎学习和转载，如需交流请加微信号：try8_cn。