这是我参与「第五届青训营 」伴学笔记创作活动的第 9 天
MySQL索引
-
机制
-
创建 方式
-
create [给一张已存在的表结构添加索引]
- CREATE INDEX/KEY indexName ON tableName (columnName(length) [ASC|DESC]);
- indexName：当前创建的索引，创建成功后叫啥名字。
- tableName：要在哪张表上创建一个索引，这里指定表名。
- columnName：要为表中的哪个字段创建索引，这里指定字段名。
- length：如果字段存储的值过长，选用值的前多少个字符创建索引。
- ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。
- ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
- 建表时DDL语句中创建 [适合在库表设计时，已经确定了索引项的情况下建立]
-
-
指定索引的查询、删除
-
SHOW INDEX FROM tableName;
- ①Table：当前索引属于那张表。
- ②Non_unique：目前索引是否属于唯一索引，0代表是的，1代表不是。
- ③Key_name：当前索引的名字。
- ④Seq_in_index：如果当前是联合索引，目前字段在联合索引中排第几个。
- ⑤Column_name：当前索引是位于哪个字段上建立的。
- ⑥Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
- ⑦Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。
- ⑧Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
- ⑨Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
- ⑩Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
- ⑪Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。
- ⑫Comment：创建索引时，是否对索引有备注信息。
- SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....; [FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询]
- DROP INDEX indexName ON tableName;
-
-
-
分类
-
数据结构层次
- CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]) USING HASH;//指定数据结构
- B+Tree类型：MySQL中最常用的索引结构，大部分引擎支持，有序。
- Hash类型：大部分存储引擎都支持，字段值不重复的情况下查询最快，无序。
- R-Tree类型：MyISAM引擎支持，也就是空间索引的默认结构类型。
- T-Tree类型：NDB-Cluster引擎支持，主要用于MySQL-Cluster服务中。
-
字段数量层次
-
前缀索引
- length：如果字段存储的值过长，选用值的前多少个字符创建索引。
-
单列
- 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束使用。
-
主键索引：主键索引是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。
- 在主键字段上建立的索引被称为主键索引
- 非主键字段上建立的索引一般被称为辅助索引或、二级索引或次级索引
- 普通索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限制，单纯的可以让查询快一点。
- 多列/组合/联合/复合/多值索引
-
-
功能逻辑层次
-
全文索引
- 类似于ES、Solr搜索中间件中的分词器，和之前常用的like+%模糊查询很类似
- 只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上
- 条件字符数量必须大于3才生效
- 空间索引——GIS空间数据(GEOMETRY、POINT、LINESTRING、POLYGON等类型)
-
-
存储方式层次
- 聚簇索引：逻辑上连续且物理空间上的连续(非空唯一索引)(一般适合采用带有自增性的顺序值)
- 非聚簇索引：逻辑上的连续，物理空间上不连续。
-
-
其他索引的创建使用
- 唯一索引UNIQUE
- 主键索引PRIMARY
-
全文索引FULLTEXT
-
note
- 5.6版本的MySQL中，存储引擎必须为MyISAM才能创建。
- 创建全文索引的字段，其类型必须要为CHAR、VARCHAR、TEXT等文本类型。
- 如果想要创建出的全文索引支持中文，需要在最后指定解析器：with parser ngram。
-
参数 查询命令show variables like '%ft%';
- ft_min_word_len：使用MyISAM引擎的表中，全文索引最小搜索长度。
- ft_max_word_len：使用MyISAM引擎的表中，全文索引最大搜索长度。
- ft_query_expansion_limit：MyISAM中使用with query expansion搜索的最大匹配数。
- innodb_ft_min_token_size：InnoDB引擎的表中，全文索引最小搜索长度。
- innodb_ft_max_token_size：InnoDB引擎的表中，全文索引最大搜索长度。
- ft_query_expansion_limit, 是控制拓展搜索时的拓展行数的
-
关键字
- MATCH()主要是负责指定要搜索的列，这里要指定创建全文索引的字段
- AGAINST()则指定要搜索的关键字，也就是要搜索的词语
-
搜索模式
- 自然语言模式
-
布尔搜索模式 IN BOOLEAN MODE
-
语法
- +：表示必须匹配的行数据必须要包含相应关键字。
- -：和上面的+相反，表示匹配的数据不能包含相应的关键字。
-
：提升指定关键字的相关性，在查询结果中靠前显示。
- <：降低指定关键字的相关性，在查询结果中靠后显示。
- ~：表示允许出现指定关键字，但出现时相关性为负。
- ：表示以该关键字开头的词语，如A，可以匹配A、AB、ABC....
- ""：双引号中的关键字作为整体，检索时不允许再分词。
- "X Y"@n：""包含的多个词语之间的距离必须要在n之间，单位-字节
-
-
查询拓展搜索WITH QUERY EXPANSION
- 首先会根据指定的关键字MySQL进行一次全文检索。
- 然后第二阶段还会对指定的关键进行分词，然后再进行一次全文检索。
-
- 空间索引 SPATIAL
-
联合索引
- SELECT语句的查询条件中，必须包含组成联合索引的第一个字段，此时才会触发联合索引
