MySQL索引

最新推荐文章于 2024-08-12 15:41:43 发布

weixin_42565018

最新推荐文章于 2024-08-12 15:41:43 发布

阅读量280

点赞数 2

分类专栏： mysql 文章标签：面试 sql

本文链接：https://blog.csdn.net/weixin_42565018/article/details/123318755

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

索引概述

索引是帮助MySQL高效获取数据的一种有序的数据结构。索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构。主要有：
在这里插入图片描述
不同的存储引擎对索引结构的支持也是不一样的：

常说的没有特别指明就是B⁺Tree索引

索引结构

B⁺Tree索引

结构

在这里插入图片描述

特点

所有数据都会出现在叶子节点
叶子节点形成了一个双向链表
非叶子节点仅仅起到索引数据的作用，叶子节点存放数据。

Hash索引

结构

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到哈希表对应的槽位上，存储到哈希表中。如果多个键值映射到同一个槽位上，则产生了哈希冲突，可以通过链表来解决。

特点

适用于查询单条记录（=， in），不支持范围查询（between, > , < , … ）
无法利用索引完成排序操作

索引分类

在这里插入图片描述
根据索引的存储形式，又可以分成：

聚集索引的选取原则：

如果存在主键索引，主键索引就是聚集索引。
如果不存在主键索引，将使用第一个唯一索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，InnoDB会自动生成一个行id（rowid）作为隐藏的聚集索引。

select * from user where name = "Arm"的执行过程：

先根据name字段在二级索引中查找，找到Arm对应的主键值为10
再根据主键值10到聚集索引中查找对应的行记录。

回表查询：先到二级索引中查找数据，找到主键值，再根据主键值去聚集索引中查找数据。
直接走聚集索引的查询性能比回表查询高！

索引使用

最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则。最左前缀法则指：查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。联合索引最左边的字段必须存在，这与编写SQL语句时查询条件的先后顺序无关。

索引失效情况

以tb_user表为例，在 tb_user 表中，有一个联合索引，这个联合索引idx_user_pro_age_sta涉及到三个字段，顺序分别为：profession，age，status。
在这里插入图片描述

不符合最左前缀法则

索引最左边的字段在查询条件中不存在，索引失效。

#索引最左边的字段profession不存在，索引失效
select * from tb_user where age = 31 and status = '0';

范围查询

出现范围查询（>, <），范围查询右侧的列索引失效，例如：

#age用了范围查询（>, <），右侧的status字段索引会失效。
select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
#age用了范围查询(>=, <=)，右侧的status字段索引不会失效。
select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 < 。

索引列运算

在tb_user表中加一个phone字段的单列索引。
在这里插入图片描述

#根据phone字段进行函数运算操作之后，索引失效
select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

#status字段是字符串类型，但没有加引号，索引失效
select * from tb_user where profession = '软件工程' and age = 31 and status = 0;

模糊查询

尾部模糊匹配，索引不会失效；头部模糊匹配，索引失效。

#like 模糊查询中，关键字前面加了%
select * from tb_user where profession like '%工程';

or连接条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。
为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
例如：在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：
create index idx_xxxx on table_name(column(n)) ;

单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引，减少回表查询的概率。

索引设计原则（什么时候需要添加索引）

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。