mysql 几种索引

隐藏索引 invisible index 8版本以上
1、软删除 将索引隐藏 但是索引依然会被维护。可以观察删除索引后是否有影响
2、灰度发布 设置一个影藏索引 观察是否有效 有效再将索引可见

  create index index_field on table_test(field) invisible;
  show index from table_test;

降序索引 8版本以上
8版本 innodb btree 支持真正降序索。 如果一个查询，需要对多个列进行排序，且顺序要求不一致。在这种场景下，要想避免数据库额外的排序-“filesort”，只能使用降序索引。

create table test(field1 int ,field2 int, index idx1(field1 asc,field2 desc));
-- 8以下版本 需要进行额外filesort  8是backward index scan;
explain select * from test order by field1,field2 desc;

函数索引
基于虚拟列实现的函数索引。

  1、创建虚拟列
  alter table t_log add column create_time_index datetime GENERATED               ALWAYS AS (date_format(create_time,'%Y-%m-%d'));
  2、对虚拟列建立索引
  alter table t_log add index_create_time_idx(create_time_index);
  3、可以对json格式数据添加虚拟列、索引 注意 json格式如果是字符串 查询时需要加上字符串转义。
 select * from test where `name` = ’\"khaskdjlasjldjlsajldj\"’；

全文索引
MySQL 5.7.6 开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。
普通索引只能支持前缀匹配，不能进行 like '%test%' 这种查询

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

ALTER TABLE articles ADD FULLTEXT INDEX title_body_index   (title,body) WITH PARSER ngram;
insert into `med`.`articles` ( `title`, `body`) values ( 'aaaa神经aaaa', 'aaaaa精神aaaa');

explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神');

hash索引
hash索引单个查找速度快 缺点不支持范围查找

btree索引
btree索引内部是一颗b+树 支持范围查找