MySQL索引

一、创建索引的语法

CREATE TABLE table_name[col_name data type] [UNIQUE| FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [(length)]) [ASC|DESC]  

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (column_name [(length)]) [ASC | DESC];

二、索引分类

2.1、按数据结构分类：

B+tree索引：
Hash索引：
Full-text索引：

2.2、按物理存储分类：

聚簇索引： 聚簇索引的每个叶子节点存储了一行完整的表数据，叶子节点间按id列递增连接，可以方便地进行顺序检索。InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非空的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增id列，并在此列上建立聚簇索引。 （如果没有显式的在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此为主键）
二级索引（辅助索引）:

2.3、按字段特性分类：

主键索引：建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。 主键索引也被称为聚簇索引（clustered index），也叫作聚集索引。其余都称呼为非主键索引也被称为二级索引（secondary index），也叫作辅助索引。
普通索引：单列索引
前缀索引：前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

2.4、按字段个数分类：

单列索引：
联合索引（复合索引、组合索引）：

三、全文索引和联合索引的区别

3.1 目的和使用场景

全文索引 (Full-Text Index)：用于全文搜索，特别适合自然语言查询，支持在文本数据中查找单词或短语。全文索引主要用于对大量文本数据进行快速、高效的全文搜索，例如在博客文章、产品描述、评论等场景下。使用场景：适合查找包含特定关键词的文本。例如，搜索包含某个词语的文章或产品描述。

联合索引 (Composite Index)：用于提高多个列组合条件的查询性能。联合索引通过存储多个列的值组合，支持快速的等值查询、范围查询和排序查询。使用场景：适合多个列组合的查询条件，如 WHERE column1 = 'value1' AND column2 = 'value2'，或者在排序时使用多个列。

3.2 工作方式

全文索引：基于文本解析：全文索引会分析文本内容，将其分解成词语或词干，存储词汇的位置信息，支持快速的关键词查找和匹配。查询方法：使用 MATCH(column1, column2) AGAINST('search terms') 语法，全文索引支持自然语言模式（natural language mode）和布尔模式（boolean mode）等多种查询模式。
联合索引：基于B-Tree或其他索引结构：联合索引按照多个列的组合值创建索引，并以B-Tree或类似的数据结构存储。查询方法：使用传统的SQL查询语句，如 SELECT * FROM table_name WHERE column1 = 'value' AND column2 = 'value';。联合索引主要用于加速这些条件下的查询操作。

3.3 查询语法和功能差异 全文索引：

 全文索引：
 查询语法：MATCH(column1, column2) AGAINST('search terms')
 支持复杂的文本查询，如自然语言搜索和布尔搜索。
 可以处理模糊搜索，返回相关性排序的结果。

 联合索引：
 查询语法：标准SQL条件查询，如 WHERE column1 = 'value' AND column2 = 'value'
 不支持全文搜索，不解析文本内容，查询条件必须精确匹配。
 适合等值查询、范围查询、排序和基于索引的连接（JOIN）。

3.4 性能和存储 全文索引：

全文索引：
	 更适合长文本字段（如TEXT和VARCHAR）。
	 存储和索引的代价可能较高，因为需要维护词汇表和反向索引表。
 联合索引：
	 更适合较短的列和结构化数据。
	 索引存储更紧凑，索引创建和维护成本较低。

3.5 总结

全文索引是专门为全文搜索设计的，使用自然语言处理技术来快速找到文本数据中的关键词或短语，适用于文本内容的模糊搜索和相关性排序。联合索引则更适合于多个列的组合条件查询，能够加速特定条件下的数据检索，如精确匹配、范围查询和排序。
根据不同的应用场景和查询需求，选择适当的索引类型可以显著提高数据库查询性能。

四、MYSQL中唯一约束和唯一索引的区别

1、唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有null。
2、创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。
3、创建一个唯一索引，这个索引就是独立，可以单独删除。
4、如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。
5、如果表的一个字段，要作为另外一个表的外键，这个字段必须有唯一约束（或是主键），如果只是有唯一索引，就会报错。

约束是业务检查，比方说非空约束,check约束，那是检查字段非空，是否符合check。而索引是一种数据结构, rowid与字段值的键值对。两个东西的效果是一样。但在建立唯一约束时，会自动创建一个唯一索引，并且失效该约束时，索引自动删除。而创建唯一索引则不会自动创建唯一约束，因此在建表时，如果要创建唯一索引，最好先建唯一索引再创建唯一约束。这样的话，在进行大批量数据插入时，可以先失效约束，插入完成后再进行索引重建。
除了引文中提到的，要被外键引用必须有唯一约束，还有这个区别：索引的键不一定要和唯一约束完全匹配，唯一约束可以只用索引的前导列。

在删除时这两者也有一定的区别：
删除唯一约束时可以只删除约束而不删除对应的索引，所以对应的列还是必须唯一的，
而删除了唯一索引的话就可以插入不唯一的值。
索引是使用 create/drop index 创建和删除的
而约束是使用 alter table tb add constraint 建立，使用 drop constraint 删除

五、最左前缀原则

最左匹配原则都是针对联合索引来说的，所以我们可以从联合索引的原理来了解最左匹配原则。 我们都知道索引的底层是一颗B+ 树，那么联合索引当然还是一颗B+ 树，只不过联合索引的键值数量不是一个，而是多个。 构建一颗B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+ 树。MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引            

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

六、字符串和int类型隐式转换

1.在字符串和数值作比较的时候，字符串和数值都会转换成浮点数进行比较。
2.字符串在转浮点数时，如果字符串开头是数字，那就一直截取，直到截取到的不是数字是其他字符为止。若开头不是数字，那么转换数值类型的结果就直接取0。（如’123abc’转成的值是123，'ab123’取到的值是0）
3.如果字符串存储的是18位varchar类型。由于在转换成浮点数的时候，数值太大会自动转换成科学计数法，而且浮点数的精度不准确，都是取近似值，所以会出现意外的内容。也就是说我的查询sql条件在用数值类型时，由于数值太大了，所以在转换成浮点数的时候变成了科学计数法，同理被查询的字段也被转换成了浮点数的科学计数法，又由于浮点数精度的不准确，导致查询结果出现不准确。
4.如果用了字符串字段，在查询的时候最好不要用数值（如整数之类的）来查询，因为如果用数值查询，也就是如上面说的，MySql会自动把表达式中的值都转换成浮点数。而这里索引字段varchar类型进行自动转换成了浮点数，导致了索引失效。

七、索引失效的几种场景

7.1、联合索引不满足最左匹配原则

联合索引场景下遵从最左匹配原则，顾名思义，在联合索引中，最左侧的字段优先匹配。因此，在创建联合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。
CREATE INDEX idx ON table(a, b, c);
对于一个多列索引，查询仅使用索引的一部分（如只查询b和c列），或者查询顺序与索引列顺序不一致（如先查询c再查询a），可能导致索引失效。

7.2、全表扫描，使用了select *

在《阿里巴巴开发手册》的ORM映射章节中有一条【强制】的规范：
在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。 说明:1)增加查询分析器解析成本。2)增减字段容易与 resultMap 配置不一致。3)无用字段增加网络 消耗，尤其是 text 类型的字段。
虽然在规范手册中没有提到索引方面的问题，但禁止使用select * 语句可能会带来的附带好处就是：某些情况下可以走覆盖索引。在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引
这一条不走索引的情况属于优化项，如果业务场景满足，则进来促使SQL语句走索引。至于阿里巴巴开发手册中的规范，只不过是两者撞到一起了，规范本身并不是为这条索引规则而定的。

7.3、索引列参与运算

针对这种情况，其实不单单是索引的问题，还会增加数据库的计算负担。就以上述SQL语句为例，数据库需要全表扫描出所有的id字段值，然后对其计算，计算之后再与参数值进行比较。如果每次执行都经历上述步骤，性能损耗可想而知。
建议的使用方式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。索引列参与了运算，会导致全表扫描，索引失效。

7.4、索引列参使用了函数

索引列参与了函数处理，会导致全表扫描，索引失效。对索引列进行数学运算（如除法、乘法等）或应用函数（如TO_CHAR(), UPPER(), LOWER()等），可能导致索引失效，因为索引存储的是原始数据值，而非经过计算或函数处理后的值。
此时，索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题。

7.5、错误的Like使用

以通配符开头的查询（如’%abc’）通常无法利用索引。但以固定字符串开头的模糊查询（如’abc%'）有时可以利用索引前缀。针对like的使用非常频繁，但使用不当往往会导致不走索引。常见的like使用方式有：
● 方式一：like ‘%abc’；
● 方式二：like ‘abc%’；
● 方式三：like ‘%abc%’；
其中方式一和方式三，由于占位符出现在首部，导致无法走索引。这种情况不做索引的原因很容易理解，索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

7.6、索引列被隐式转换

参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。如果查询中对索引列进行了隐式类型转换（如将数值型索引列与字符串比较），可能导致索引失效。这种情况还有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，虽然使用了单引号或双引号。

7.7、索引列使用OR连接多个条件

OR是日常使用最多的操作关键字了，但使用不当，也会导致索引失效。所以，在使用or关键字时，切记两个条件都要添加索引，否则会导致索引失效。查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效。

7.8、索引两列做比较

两列数据做比较，即便两列都创建了索引，索引也会失效。

7.9 不等于比较

当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是。执行之后会发现，当查询结果集占比比较小时，会走索引，占比比较大时不会走索引。此处与结果集与总体的占比有关。查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。

7.10、is not null

查询条件使用is null时正常走索引，使用is not null时，不走索引。

7.11、not in和not exists

在日常中使用比较多的范围查询有in、exists、not in、not exists、between and等。
上述四种语句执行时都会正常走索引，具体的explain结果就不再展示。主要看不走索引的情况：
当使用not in时，不走索引？把条件列换成主键试试：
查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
当查询条件使用not exists时，不走索引。
查询条件使用not exists时，索引失效。

7.12 索引列参与排序但无谓值筛选：

SELECT * FROM table ORDER BY indexed_column;
当仅对索引列进行排序而无其他筛选条件时，可能无法利用索引进行排序优化。其实这种情况的索引失效很容易理解，毕竟需要对全表数据进行排序处理。
结果依旧不走索引。在网络上看到有说如果order by条件满足最左匹配则会正常走索引， 在当前8.0.18版本中并未出现。所以，在基于order by和limit进行使用时，要特别留意。是否走索引不仅涉及到数据库版本，还要看Mysql优化器是如何处理的。这里还有一个特例，就是主键使用order by时，可以正常走索引。可以看出针对主键，还是order by可以正常走索引。
当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证

7.13、参数不同导致索引失效

为什么同样的查询语句，只是查询的参数值不同，却会出现一个走索引，一个不走索引的情况呢？
答案很简单：上述索引失效是因为DBMS发现全表扫描比走索引效率更高，因此就放弃了走索引。
也就是说，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。某些场景下即便强制SQL语句走索引，也同样会失效。
类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。
第十四种索引失效情况：当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。

7.14、索引列用于JOIN条件但数据分布不均：

SELECT * FROM table1 JOIN table2 ON indexed_column = foreign_key;
如果关联表的数据分布极不均匀，即使使用了索引，也可能因数据倾斜导致索引效果不佳。

7.15、临时表或派生表操作：

CREATE TEMPORARY TABLE AS SELECT …;
创建临时表或派生表的操作通常不保留原表的索引，后续查询可能无法利用索引。

7.16、索引未被维护或统计信息过期：

ANALYZE TABLE table;
如果表数据发生显著变化后未重新收集统计信息，或索引长期未进行维护，可能导致查询优化器对索引效用的评估不准确，从而不使用索引。

7.17、索引列用于NOT操作：

SELECT * FROM table WHERE NOT indexed_column = value;
对索引列应用逻辑非操作（如NOT、!=、<>）可能导致索引失效，除非索引支持倒序扫描。

7.18、其他

当然，还有其他一些是否走索引的规则，这与索引的类型是B-tree索引还是位图索引也有关系，就不再详细展开。这里要说的其他，可以总结为第十五种索引失效的情况：Mysql优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。特殊情况特殊处理