【MySQL】MySQL索引基础知识

小鱼儿 2023-10-08 17:34 38阅读 0赞

## 1.索引是什么 ##

索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。  
索引一般存储在磁盘的文件中，它是占用物理空间的。  
适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。

## 2.MySQL索引有哪些类型 ##

数据结构维度

*  B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。  
    \-哈希索引: 适合等值查询，检索效率高，一次到位。  
    \-全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。  
    \-R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

物理存储维度

*  聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
 *  非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

逻辑维度

*  主键索引：一种特殊的唯一索引，不允许有空值。
 *  普通索引：MySQL中基本索引类型，允许空值和重复值。
 *  联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
 *  唯一索引：索引列中的值必须是唯一的，但是允许为空值。
 *  空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

## 3.索引什么时候会失效？ ##

*  查询条件包含or，可能导致索引失效
 *  如果字段类型是字符串，where时一定用引号括起来，否则索引失效
 *  like通配符可能导致索引失效。
 *  联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
 *  在索引列上使用 mysql 的内置函数，索引失效。
 *  对索引列运算（如，+、-、\*、/），索引失效。
 *  索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
 *  索引字段上使用is null， is not null，可能导致索引失效。
 *  左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
 *  mysql 估计使用全表扫描要比使用索引快,则不使用索引。

## 4.哪些场景不适合建立索引？ ##

*  数据量少的表，不适合加索引
 *  更新比较频繁的也不适合加索引
 *  区分度低的字段不适合加索引（如性别）
 *  where、group by、order by等后面没有使用到的字段，不需要建立索引
 *  已经有冗余的索引的情况（比如已经有a,b的联合索引，不需要再单独建立a索引）

## 5.B+ 树有什么好处，为什么不用二叉树？ ##

都知道树会变成单链，这样效率直接变成很低了。

## 6.B 树和 B+树区别？ ##

*  B+树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储 键值，也会存储数据。innodb 中页的默认大小是 16KB，如果不存储数据，那 么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就 会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数有会再次减少，数据查 询的效率也会更快。
 *  B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链 表连着的。那么 B+树使得范围查找，排序查找，分组查找以及去重查找变得 异常简单。

## 7.什么是回表？如何减少回表？ ##

当查询的数据在索引树中，找不到的时候，需要回到主键索引树 中去获取，这个过程叫做回表 。

比如：

select * from user where age=32;
    
    有一个索引 idx_age;

需要查询所有列的数据，idx\_age普通索引不能满足，需要拿到主键id的值后，再回到id主键索引查找获取，这个过程就是回表。

## 8.什么是覆盖索引？ ##

如果我们查询SQL的select \* 修改为 select id, age的话，其实是不需要回表 的。因为id和age的值，都在idx\_age索引树的叶子节点上，这就涉及到覆盖索引的知识点了。

覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。

## 9.左前缀原则(最左匹配原则) ##

索引的最左前缀原则，可以是联合索引的最左N个字段 。

比如你建立一个组合索引（a,b,c），其实可以相当于建了（a），（a,b）,(a,b,c)三个索引，大大提高了索引复用能力。

这个SQL: select \* from user where name like ‘小%’ order by age desc; 也是命中索引的。

## 10.什么是索引下推 ##

比如：

select * from user where name like '小%' and age=28 and sex='0';

name和age为联合索引（idx\_name\_age）。

如果是Mysql5.6之前 ，在idx\_name\_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。

MySQL 5.6就引入了索引下推优化 ，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

因此，MySQL5.6版本之后，选出包含“小”字后，顺表过滤age=28

## 11.大表如何添加索引 ##

如果一张表数据量级是千万级别以上的，那么，如何给这张表添加索引？

我们需要知道一点，给表添加索引的时候 ，是会对表加锁的 。如果不谨慎操作，有可能出现生产事故的。可以参考以下方法：

*  先创建一张跟原表A数据结构相同的新表B。
 *  在新表B添加需要加上的新索引。
 *  把原表A数据导到新表B
 *  rename新表B为原表的表名A，原表A换别的表名；

## 12. 如何知道语句是否走索引查询？ ##

可以看这一片优化篇：[优化篇][Link 1]

## 13.Hash 索引和 B+ 树区别是什么？ ##

*  B+树可以进行范围查询，Hash 索引不能。
 *  B+树支持联合索引的最左侧原则，Hash 索引不支持。
 *  B+树支持 order by 排序，Hash 索引不支持。
 *  Hash 索引在等值查询上比 B+树效率更高。（但是索引列的重复值很多的话，Hash冲突，效率降低）。
 *  B+树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

## 14.索引有哪些优缺点？ ##

优点：

*  索引可以加快数据查询速度，减少查询时间
 *  唯一索引可以保证数据库表中每一行的数据的唯一性  
    缺点：
 *  创建索引和维护索引要耗费时间
 *  索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
 *  以表中的数据进行增、删、改的时候，索引也要动态的维护。
 *

## 15.聚簇索引与非聚簇索引的区别? ##

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式 。  
它表示索引结构和数据一起存放的索引。

非聚集索引是索引结构和数据分开存放的索引 。

在MySQL的InnoDB存储引擎中， 聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。

*  一个表中只能拥有一个聚集索引（因为**一般聚簇索引就是主键索引** ），而非聚集索引一个表则可以存在多个。
 *  一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。

而在MyISM存储引擎中，它的主键索引，普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据 。

[Link 1]: https://blog.csdn.net/daohangtaiqian/article/details/128880897