MySQL进阶 (2)：Mysql 索引分类及使用策略

发布时间：2023-10-23 18:00

导读

提到Mysql的优化，索引优化是必不可少的。添加合适的索引能够让应用的并发能力和抗压能力得到明显的提升。应用性能的瓶颈主要是在"(select)"查询语句。

什么是索引？

在关系数据库中，索引是一种单独的，物理的对数据库表中一列或多列的值进行排序的一种存储结构。用大白话来讲，就是为了加速对表中的数据行的检索，而创建的一种分散的存储结构。

索引的作用？

索引就相当于图书的目录，让你查找书中的某篇文章，不用一页一页的去翻找，而是先从目录大纲中寻找关键字标题，快速定位其文章所在的页码

比如，可以把数据库中的表(table) 当作是一本书，table中一行一行的数据就是书里每篇文章的内容，即row，文章标题可看作是 table 中的 column字段，目录就是对 column 列建立的索引。

☛ 当表中有大量记录时，对表查询

第一种：没有目录，即全表扫描，将所有记录一一取出和查询条件对比，然后返回满足条件的记录，这种方式会消耗大量数据库系统时间，并造成大量磁盘I/O，因为数据是存放在磁盘中的，存取数据都要访问磁盘。

第二种：有目录，即有索引，就是在表中建立索引，也就是建立书的目录，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID 快速找到表中对应的记录。

索引分类：数据结构

MySQL索引按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。

	InnoDB	MyISAM	Memory
B+tree索引	√	√	√
Hash索引	×	×	√
Full-text索引	√（MySQL5.6+）	√	×

注意：InnoDB实际也支持Hash索引，但只能由存储引擎引擎自动优化创建，不能人为创建

1. FULLTEXT

即为全文索引，MyISAM引擎支持，InnoDB从MySQL5.6版本开始支持。可以在create table ，alter table ，create index 使用，不过目前只有 char、varchar ，text 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决 where name like “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。是MySQL里默认和最常用的索引类型。

索引分类：物理存储

MySQL索引按叶子节点存储的是否为完整表数据分为：聚簇索引、二级索引（辅助索引）。全表数据存储在聚簇索引中，聚簇索引以外的其他索引叫做二级索引，也叫辅助索引。

☛ 聚簇索引

也称为主键索引，其每个叶子节点存储了一行完整的表数据，叶子节点间按id列递增通过指针连接，提高区间访问的性能。

!! 注意：InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非null值的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增row_id列作为主键id。

MyISAM 引擎实现

MyISAM不存在聚簇索引。其主索引和辅助索引结构一致，只是主索引要求key是唯一的，而辅助索引的key可重复。因此MyISAM表可以没有主键。叶子节点不存储表数据，而是表数据的地址。数据和索引是分开存储的

☛ 二级索引

MyISAM其主索引和辅助索引结构一致，叶子节点不存储表数据，而是表数据的地址。

也称辅助索引（ps：InnoDB才有辅助索引的概念，MyISAM没有），叶子节点并不存储表数据，而是存储了聚簇索引所在列的值，即主键的值。如下

回表查询

辅助索引搜索需要检索两遍，由于辅助索引的叶子节点不存储完整的表数据，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录：

!! 注意：回表会增大查询耗时。但回表也不是必须的，当select的所有字段在单个二级索引中都能够找到，就无需回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖。

索引分类：索引种类

1. 主键索引

加速查询 + 列值唯一（不可以有null）+ 表中只有一个

建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，给字段设置主键时，会自动创建主键索引，通常在创建表时一起创建。如下

create table a (  
	id int primary key auto_increment,  -- id为主键索引
	name varchar(20)   
);

2. 唯一索引

加速查询 + 列值唯一（可以有null）

建立在unique字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。如下：

-- 1.建表时指定
create table d(
	id int primary key auto_increment , 
	name varchar(32) unique  -- name为唯一索引
) 

-- 2.对已存在表，创建
create unique index [索引名] ON table_name(col_name);

3. 普通索引

仅加速查询，主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找

建立在普通字段上的索引被称为普通索引。

-- 方式1.直接创建
create index 索引名 on table_name(column1,column2);

-- 方式2.修改表创建
alter table table_name add index [索引名](column1,column2);

4. 全文索引

搜索数据表中的字段是否包含关键字，就像搜索引擎中的模糊查询。

☆ Mysql全文索引版本限制

MySQL5.6版本以下，只有MYISAM引擎表支持全文索引；mySQL5.6以上，Innodb引擎表也支持全文索引。
因Mysql使用空格作为分词的分隔符，故只支持英文检索。从MySQL5.7版本开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

☆ 创建/使用全文索引

1、假设 article文章表结构如下：

2、创建全文索引

-- 使用 ngram 解释器（支持中英文）
create fulltext index full_body on article(body) with parser ngram;

-- 仅支持英文检索
create fulltext index full_body on article(body);

3、使用

match (字段名1,字段名2...) against('关键词1 关键词2  ... ');

5. 组合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并，称为联合索引或复合索引、组合索引。

create index [索引名] ON table_name(col_name_1,col_name_2);

6. 前缀索引

对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。

前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率，语法如下：

alter table 表名 add index [索引名](字段(长度));

-- 例如：
alter table article add index id_Prefix(title(4));

!! 注意：如果索引的列字符很长，索引则会很大且变慢，故可以创建列的部分长度的索引，节约索引空间从而提高索引效率

建立索引的几大原则

在where条件中经常使用

该字段的内容不是唯一的几个值（例如性别字段，唯一性太差，不推荐）

字段内容不是频繁变化。（每次更新还需额外维护索引文件）