MySQL索引

最新推荐文章于 2024-09-16 10:13:47 发布

BK俊河

最新推荐文章于 2024-09-16 10:13:47 发布

阅读量864

点赞数 17

分类专栏： MySQL知识点总结文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Junhe4512/article/details/142055013

版权

MySQL知识点总结专栏收录该内容

8 篇文章 0 订阅

订阅专栏

什么是索引？

索引（Index）是一种以加快查询速度为目的的数据结构，减少了系统的IO。

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

为什么要有索引？

提高查询速度，避免全表扫描，减少IO消耗，提高系统性能

索引有哪些类型？

数据结构划分

B+树索引

Hash索引

索引字段划分

主键索引（聚簇索引）

二级索引、普通索引（非聚簇索引）

索引唯一性约束划分

唯一索引

索引字段数量划分

联合索引

InnoDB引擎中聚簇索引和非聚簇索引的区别

1.聚簇索引
叶子节点记录的是完整的行数据。
非叶子节点记录的是主键和子节点指针。
2非聚簇索引
叶子节点记录的是索引列、主键。
非叶子节点记录的是索引列和子节点指针。

什么是回表？

"回表”就是当非聚簇索引的查询结果不能满足查询的目标字段要求,那么就会通过主索引的值回聚簇索引中进行查找;这个过程就回表。

回表不仅仅只是多查了一次，还会带来随机I/O，导致效率变低。

所以不要因为方便都用 select * from，避免不必要的回表操作，或者增加覆盖索引。

什么是覆盖索引？

覆盖索引指的是二级索引中包含了查询所需的所有字段，从而是查询仅通过访问二级索引就可以获得想要的信息，避免了回表。是一种现象

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    department_id INT,
    INDEX idx_name_hiredate (last_name, first_name, hire_date)
);

当想要获得员工姓名、名和雇佣日期时就可以通过idx_name_hiredate这个索引覆盖获取结果，而无需访问实际的主键索引的数据。

SELECT last_name, first_name, hire_date 
FROM employees 
WHERE last_name = '鸭' AND first_name = '你好';

如果需求变化，需要多查找一个idx_name_hiredate中不包含其他字段，就无法利用索引覆盖了。

优点：减少IO操作，提高查询速度，减少内存占用。

最左前缀匹配原则是什么

在使用索引进行查询时，查询条件从左边开始逐个字段匹配，主要应用与组合索引中。

比如说创建了一个table_name的表有两个字段name和age，并且在这两个字段上创建了一个联合索引（name，age）

（1）.SELECT * FROM table_name WHERE name = 'abc' AND age = 25;满足最左

（2）SELECT * FROM table_name WHERE name = 'abc';满足

（3）SELECT * FROM table_name WHERE age = 25;不满足，无法利用这个联合索引导致全盘扫描

（4）SELECT * FROM table_name WHERE age > 25;不满足，当条件语句中包含范围查询时，例如>、<、BETMEEN或LIKE ，MySQL会停止使用索引的左匹配原则。

MySQL索引一定遵循最左前缀匹配吗，索引跳跃是什么

不一定

索引跳跃（Index Skipping）是指在 MySQL 中查询时，某些情况下索引不能被充分利用或部分索引被跳过的现象。具体而言，索引跳跃通常发生在使用组合索引时，查询条件并没有按索引顺序完全使用，导致 MySQL 不能利用所有索引列来优化查询。

例子：假设有一个组合索引 idx_name_age_city，它是由 name、age 和 city 三个字段组成的组合索引，按照 name -> age -> city 的顺序。

SELECT * FROM users WHERE name = 'John' AND city = 'New York';

在这里，age 列被跳过，MySQL 无法利用 age 之后的 city 列的索引，这就是索引跳跃的典型例子。在这种情况下，MySQL 只会使用 name 列的索引，而 city 列的索引不会被用到。

如何避免索引跳跃

避免索引跳跃的关键在于遵循最左前缀原则，并确保在查询条件中使用索引定义的前几个列。例如：

在组合索引中，尽量按顺序使用索引列。
如果需要查询部分列，可以根据查询的频率和模式，调整索引的定义顺序，确保常用列在索引的最左端。

索引下推是什么

通过将部分带有索引的条件交由存储引擎层进行过滤操作，从而减少了IO（本应该由Server层做的操作，交由给存储引擎层，因此叫下推）

1）索引下推在MySQL 5.6及以后的版本支持，InnoDB和MyISAA这两个存储引擎都生效。

2)索引下推是应用在联合索引上的。

3)使用了聚簇索引(主键）查询，索引下推也不会生效，因为其是对于非聚簇索引来进行减少回表次数。

索引创建时需要注意哪些事项

索引不是越多越好
对于字段的值大量重复的不要建立索引
长字段不应该建立索引
当字段的修改频率远远大于查询频率
如果有多个条件经常一起查询，可以建立联合索引
经常在order by、group up、distinct后的字段可以建立索引

如何判断索引是否生效，失效的场景都有哪些？

可以通过MySQL的执行计划explain{SQL语句}来看是否生效：

type（访问类型）：这个属性显示了查询使用的访问方法，当使用索引时，一般显示为index或range，表示使用了索引访问。如果值为ALL，则执行了全表扫描，没用索引。
key（使用的索引）：显示查询所用的索引，如果使用了索引就会显示索引的名称，没有使用就会显示null。
row（扫描的行数）：显示查询扫描的行数，需要评估下扫描量判断

失效的场景都用：