MySQL索引

索引

索引是帮助MySql高效获取数据的排好序的数据结构。

数据结构

二叉树:左边元素小于节点，右边元素大于节点。

红黑树

Hash表

B-tree

InnoDB存储引擎索引类型

数据结构：B+树索引，hash索引

唯一性：唯一索引，普通索引

B+树索引：聚簇索引，非聚簇索引

聚簇索引和非聚簇索引

聚簇索引的叶子节点中存储的是整行记录；

而非聚簇索引的叶子节点中存储的是主键ID；

所以，通过非聚簇索引的查询，需要进行一次回表，就是先查到ID，在通过ID查询所需字段。

索引下推(ICP)

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

select * from tuser where name like '张%' and age=10;

不使用ICP:在MySQL 5.6之前，存储引擎根据通过联合索引找到name like '张%' 的主键id，逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。

使用ICP:MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name like '张%'，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。

索引跳跃扫描

MySQL 8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况，优化器依然能组使用组合索引

索引失效的情况

1. like ‘%xxx’
2. 查询条件用了函数，例如：select * from test_table where YEAR(time)=‘2023’;
3. 使用了or
4. 使用了区间查询，例如> < ！= between 等
5. 隐式转换，例如数据库字段是varchar类型，查询条件使用int类型
6. is not null
7. order by
8. in
9. 联合索引（a,b,c） 查询条件bc。

索引失效

name varchar类型 ， age int类型

select * from tb_user where name=111  ---不走索引

select * from tb_user where age=111   --- mysql优化，会走索引

数据库用到了索引，但是查询速度慢？

• 数据量过大：当数据量非常大的时候，即使用到了索引，查询速度可能叶会慢。
• 硬件资源不足：硬件资源如CPU/内存/磁盘等不足时，可能导致查询性能下降。
• 查询语句优化不当：查询语句存在排序/子查询等操作，导致查询效率降低。
• 索引选择不当：选择了不适合作为索引的列，例如区分度不高的列；建立了过多的索引。