⭐MySQL-索引_mysql索引的优缺点-优快云博客

本文链接：https://blog.youkuaiyun.com/Tomkruse11/article/details/144942993

⭐索引概念

⭐索引概念

索引本质上就是一种通过减少查询需要遍历行数，加快查询性能的数据结构，避免数据库进行全表扫描，好比书的目录，让你更快的找到内容。（一个表最多16个索引）

索引的优缺点：

（1）索引的优点：

1.减少查询需要检索的行数，加快查询速度，避免进行全表扫描，这也是创建索引的最主要的原因。

2.如果索引的数据结构是B+树，在使用分组和排序时，可以显著减少查询中分组和排序的时间。

3.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（2）索引的缺点：

4.当对表中的数据进行增加、删除和修改时，索引也要进行更新，维护的耗时随着数据量的增加而增加。

5.索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引使用的场景

（1）在哪些列上面创建索引：

1.WHERE子句中经常出现的列上面创建索引，加快条件的判断速度。

2.按范围存取的列或者在group by或order by中使用的列，因为索引已经排序，这样可以利用索引加快排序查询时间。

3.经常用于连接的列上，这些列主要是一些外键，可以加快连接的速度；

4.作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

（2）不在哪些列建索引？

1.区分度不高的列。由于这些列的取值很少，例如性别，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

2.在查询中很少的列不应该创建索引。由于这些列很少使用到，但增加了索引，反而降低了系统的维护速度和增大了空间需求。

3.当添加索引造成修改成本的提高远远大于检索性能的提高时，不应该创建索引。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。

4.定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。

索引的分类：

（1）普通索引、唯一索引、主键索引、全文索引、组合索引。

普通索引：最基本的索引，没有任何限制

唯一索引：但索引列的值必须唯一，允许有空值，可以有多个NULL值。如果是组合索引，则列值的组合必须唯一。

主键索引：一种特殊的唯一索引，不允许有空值。

全文索引：全文索引仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

组合索引：主要是为了提高mysql效率，创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

（2）聚簇索引与非聚簇索引：

如果按数据存储的物理顺序与索引值的顺序分类，可以将索引分为聚簇索引与非聚簇索引两类：

聚簇索引：表中数据存储的物理顺序与索引值的顺序一致，一个基本表最多只能有一个聚簇索引，更新聚簇索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引

非聚簇索引（二级索引）：表中数据的物理顺序与索引值的顺序不一致的索引组织，一个基本表可以有多个聚簇索引。

💡回表查询

以上图为例，当sql语句为 select id,name,gender from tb_user where name='TOM'时，因为此处创建了聚簇索引（主键索引）和二级索引（name字段），而这条语句where条件是name字段，所以先走二级索引找到对饮的name='tom'的值（id），但是sql语句还需要返回gender字段的值，而二级索引中没有存储该列的值，所以就需要根据二级索引找到的id值回到聚簇索引中进行查找。这种就叫回表。

💡覆盖索引

覆盖索引(covering index ，或称为索引覆盖)即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。例如上图如果sql语句是select id,name from user where name='tom', 那么id和name可以直接在二级索引中找到而不需要回表查询，那么这种查询就叫覆盖索引。

💡索引下推

要了解索引下推之前，先了解一下MySQL的结构：MySQL通常被分为两层架构，即Server层和存储引擎层。Server层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和提取

索引下推（Index Condition Pushdown，简称ICP） 是 MySQL 5.6 开始引入的一项优化技术，可以在执行查询时将过滤条件 下推到存储引擎层 。索引下推技术允许存储引擎使用非键列索引来筛选不符合条件的行，减少回表（访问主键索引）的次数，从而提高查询性能。

索引下推的适用条件如下：

ICP仅适用于 InnoDB 和 MyISAM 引擎，包括它们的分区表。

ICP适用于执行计划type是 range, ref, eq_ref和 ref_or_null 的查询语句

ICP 只适用于二级索引

存储函数不能使用索引下推，因为存储引擎无法调用存储函数

引用子查询条件不能使用索引下推

如果索引列的数据类型是 BLOB 、TEXT 等大数据类型，则索引下推无法使用。

索引下推只适用于联合索引

B+树以及和B树区别：

我们知道MySQL的索引就是使用了B+树的数据结构，那么到底什么是B+树呢，而B+树有又和普通的B树有什么区别？在此详细展开介绍。

B+树

首先从B+树开始说起，B+树就是文中上述图片中那种样子。根节点存储的是主键值，而叶子节点存储的才是主键以及主键对应的值，然后叶子节点构成一个有序链表，便于一些范围查询。而这点就与B树有所区别，B树的根节点也会存储主键值。

B树

B树和B+树的不同就是，B树在根节点上也会存储主键以及主键对应的值，而B+树只在叶子节点上保存有主键对应的值。

区别

从存储特点就可以看到两者之间的区别，B树的查询速度不稳定，如果需要的数据在根节点附近那么查询速度会比B+树快，如果节点在叶子节点那么查询速度就会比较慢了，而且最主要的是不适合范围查询。如果需要范围查询时，那么就需要在各个节点之间进进出出，效率比较低。而B+树的叶子节点是通过链表形式，范围查询效率更高。