MySQL数据库中的索引知识

MySQL数据库中索引的作用是用来加快数据的查询速度。

索引  index（表的层面）

在数据库中使用select来查询数据的时候会一条一条得去查询符合要求的数据，而索引就相当于在这张表中依据某一个字段的数值给这张表的数据创建了一个目录。目录帮助我们快速的去定位数据。

在表中创建索引的时候要挑选合适的字段来创建索引，如果为这张表创建的索引挑选的字段不合适的话，此时依据索引查询数据的速度不增反降。

如上图所示，在实际应用场景中我们需要依据一段时间之内用户的行为来将合适的字段创建索引。

如果说在一段时间以内，用户常常根据商品的价格来搜索商品，此时我们就需要将这张表的索引创建在价格从字段上。此时数据库会自动按照价格的数值来进行相应的排序，然后生成一个目录。

如果用户的搜索行为发生改变的话，此时我们就需要将原来表中的索引删除，重新创建一个索引。

在创建索引的时候不适合将索引创建在数据经常需要发生变动的字段上。

注意点：1.不适合在经常变动的数据上建立索引。

2.建立在合适的字段上{根据前端业务的用户行为}

MySQL数据库默认会在主键字段上自动创建索引，

查看表的索引

如上图所示，在MySQL数据库中使用SQL语句show index from 表的名称；

来查看这张表的索引，此时MySQL数据库软件会给出一个表，在这张表中有一行内容就代表有一个索引，一般在MySQL数据库中都会有这样一个默认行为即自动为拥有主键属性的字段去创建索引。

创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

如上图所示，是使用help create index;在MySQL数据库上去查询如何创建索引的SQL语句。

即如果想去在一张表的某一个字段上去创建索引，就需要使用语句

create index 自己起的索引名称

on 表的名称(表的字段名称，表的字段名称);

如上图所示，在使用show index from 表名称；的表格中的key_name是索引的名称。

如上图所示，使用SQL语句create index 创建的索引名称 on 表的名称 (字段名称);

可以在表中创建索引，使用show index from 表的名称；

在日常工作中，MySQL数据库工作人员常常使用字段_key的方式来为创建的索引命名。

删除索引

如上图所示，使用命令drop index 索引名称 on 表名称

验证索引对加快搜索数据的帮助

在MySQL内部有一个查询分析器的东西。这个查询分析器可以帮助我们分析select语句是如何帮助获取到数据的。

explain就是MySQL中的查询分析器。

如上图所示，key的结果可以说明在使用select语句查询表中信息的时候是否使用到了索引，当为null的时候表示没有使用到索引。而rows表示使用select语句调查表中的信息的时候调查了表的多少行数据。

如上图所示，当在表中使用语句create index 索引名称 on 表（字段名称）；

使用explain select * from 表名称 where 条件；

可以查看到select语句的执行过程。

什么情况下的select语句是不能触发字段的索引的？

当你写的select语句触发了数据类型转换的时候，就不能触发索引。

如上图所示，当使用select语句中使用了比较查询的时候，或者在等值查询的时候使用了“5000”这种将字符串=数字，的数据类型转换的时候同样不能触发索引。

在SQL语句中使用select语句去查询数据的时候什么情况是用不到索引的？

在使用SQL的SELECT语句查询数据时，以下情况可能不会使用索引，从而可能影响查询性能：

1. 全表扫描更快: 当表中的数据量非常小，全表扫描比使用索引进行查找更快时，数据库可能选择不使用索引。

2. 使用函数或表达式: 在查询条件中，如果对索引列使用了函数、表达式或计算（如DATE(create_time)、age - 1），这通常会导致索引失效。

3. 不等于(!=)和NOT IN操作符: 使用!=或NOT IN操作符可能导致索引不被使用，特别是当这些操作符与其他条件结合使用时。

4. 类型不匹配: 如果查询条件中的数据类型与索引列的数据类型不一致，可能导致索引失效。

5. LIKE操作符以通配符开头: 如果LIKE子句以通配符（如'%abc'）开头，索引通常不会被利用，因为这要求对列的每一项做全值比较。

6. OR条件: 当查询条件中包含多个通过OR连接的条件时，除非每个条件的列都有索引且优化器选择使用这些索引，否则索引可能不会被利用。

7. 多列索引未使用第一列: 对于复合索引（多列索引），如果查询条件没有首先使用索引的第一列，索引可能不会被使用。

8. 索引列参与计算: 在条件中，如果对索引列进行了数学运算（+，-，*，/，!）或其他计算，索引可能不会被使用。

9. 字段编码不一致: 在关联查询中，如果关联字段的字符编码不一致，可能导致索引失效。

10. 索引覆盖未满足: 如果查询需要从表中检索大量数据，而不仅仅是索引包含的列（即没有实现索引覆盖），数据库可能决定放弃使用索引而进行全表扫描。

了解这些情况有助于在设计查询和索引策略时做出更优化的选择，以提高查询效率。