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条件中
经常使用- 该字段的内容
不是唯一的几个值（例如性别字段，唯一性太差，不推荐）- 字段内容
不是频繁变化。（每次更新还需额外维护索引文件）