MySQL索引

mysql默认存储引擎是InnoDB，支持事务处理、外键约束和崩溃恢复。它具有高可靠性和高性能，是处理大型数据集和并发操作的首选引擎。

1.InnoDB引擎里面有两种索引类型，一种是主键索引、一种是普通索引

主键索引：

唯一性（主键索引保证索引列的值唯一，不允许重复）、自动创建（在定义主键时，数据库会自动为主键列创建唯一的索引）、不允许 NULL 值，默认是聚集索引。

普通索引：

允许重复值，显式创建，一般是非聚集索引

例如：

CREATE TABLE users (
    id INT PRIMARY KEY,      -- id 列是主键
    name VARCHAR(50)
);

主键索引为自动创建，即id列，而普通索引则需要以下操作：

CREATE INDEX idx_name ON users (name);

索引失效原因：

索引失效原因

1.在索引列上加函数运算，导致Mysql无法识别索引列，也就不会再走索引了（mysql8增加了函数索引，此问题已解决）

2.在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。

3.在组合索引中，索引的存储结构是按照索引列的顺序来存储的，因此在sql中也需要按照这个顺序才能进行逐一匹配，否则InnoDB无法识别，索引失效。

4.当索引列存在隐式转化的时候， 比如直接写成 WHERE column_name = some_value，而 column_name 是字符串类型，MySQL会将 some_value 转换为字符串进行比较。这种转换可能导致全表扫描而不是使用索引，因为MySQL可能无法有效利用该索引

5.当使用不等于号（!= 或 <>）或 NOT 查询时，MySQL的优化器会判断使用索引的效率低下，可能会选择不使用索引，而是直接扫描整个表

6.使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。例如：SELECT * FROM users WHERE username LIKE '%john'; 但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，会走索引。例如：SELECT * FROM users WHERE username LIKE 'john%';

7.使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效；只有or左右查询字段都是索引列的时候，才会生效。

总结：可以使用explain命令来查看sql的执行计划，比如，查看最终是否走索引，然后针对性的进行调优

不适合使用索引的场景：

不适合使用索引的场景

数据量少的不适合加索引	对于数据量很小的表，数据库引擎可以很快地进行全表扫描。创建索引的开销（包括索引的存储空间和维护成本）可能会超过从索引中获得的性能提升。因此，数据量少时，使用索引可能反而不划算
更新比较频繁的也不适合加索引	每次对表进行插入、更新或删除操作时，索引都需要被相应地更新。这会增加额外的写入开销。如果表的更新操作频繁，维护索引的成本可能会很高，这可能会导致整体性能下降，尤其是当这些操作频繁而且对性能要求较高时
区分度低的字段不适合加索引	索引的目的是提高查询速度，而高区分度的字段（即字段值种类多且分布均匀）能够显著提高查询效率。对于区分度低的字段（例如性别字段只有“男”和“女”两个值），索引可能无法带来明显的性能提升。因为这些字段的值分布不均，查询结果通常会有大量的重复记录，索引的选择性差（选择性是指索引的唯一性和查询优化的程度），从而使得索引的效果不显著

索引“潜规则”

覆盖索引	指的就是索引不仅包含列值本身，还包含列值所在的行的所有信息，这种索引就叫做覆盖索引。例如：当我们进行查询时，如果能直接根据索引中的信息获取查询的结果，而不需要返回表数据，就称为查询被索引覆盖。这种情况下，就不需要额外的 I/O 操作直接访问索引节点就能取得查询结果，可以极大提高查询效率
回表	回表是指在进行查询操作时，为了得到完整的记录信息，需要额外的操作回溯到底层表中获取。例如：一个索引不是覆盖索引，那么查询语句在通过索引定位到数据的位置后，还需要到数据表中获取完整的数据。这个过程称之为回表
索引数据结构	通常使用的是B+树，因为B+树适合整表扫描，且有较高的空间利用率，让插入操作更容易。B+树是一种自平衡的多叉树，用于存储排序的数据。它可以高效地进行范围查询和插入操作，广泛用于数据库和文件系统的索引结构
最左前缀原则	MySQL 在使用组合索引的时候遵循最左前缀原则，即查询使用的索引的字段必须包括最左边的字段或者最前边的那些字段。如果查询的字段序号在索引的列序号之后，那么这个查询不能使用这个索引。这个原则尤其适用于联合索引（组合索引），即一个索引由多个列组成
索引下推	它将一些过滤条件直接下推到存储引擎索引层进行过滤，大大减少数据访问量。例如：WHERE id=1 AND name='Alice' 这个条件会先在下层的索引结构中先过滤掉大部分不满足条件的行，然后再返回上层进行最后的精确匹配。这个机制提高了索引的效率，减少了不必要的回表操作（mysql8的新增内容）