【数据库】Mysql索引面试题

目录

 

1.索引的原理

2.常见面试题

为什么要给表加上主键?

为什么加索引过后查询会变快？

为什么加索引后会使写入、修改、删除变慢？

什么情况下要同时在两个字段上建索引？

索引碎片化的产生以及解决方案

---

1.索引的原理

没有索引，执行查询就是遍历整张表，生成索引后相当于把整个表梳理成了一个平衡树的数据格式

 

注意:非聚集索引和聚集索引（主键）的区别在于，通过聚集索引可以查到需要查找的数据，而非通过聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据

图来源 https://blog.csdn.net/weixin_41565013/article/details/94573960

2.常见面试题

为什么要给表加上主键?

（1）如果没有主键，数据就会无序的放在磁盘存储器上，一行一行的排列整齐

（2）主键的作用就是把表的数据格式转换成平衡树的格式放置，主键只能由一个也称为（聚集索引）

 

为什么加索引过后查询会变快？

假设现在有n行数据，最坏的情况也就是遍历所有数据，时间复杂度也就是O(n)，而树状结构我们不用再查询数据的时候都遍历整张表，而是可以通过数的分叉更好的找到我们需要的数据行，时间复杂度是O(logn)

 

为什么加索引后会使写入、修改、删除变慢？

任何事物都具有两面性，加上索引在带来快速查询的同时也会导致增删改变慢，这个道理也很好理解，因为这三个操作会改变索引数据的内容，从而导致原来通过数据内容的建立起来的平衡二叉树逻辑遭到了破坏，破坏后DMS会自动再花时间去梳理表的二叉树排列，这一步对于越大的表带来的开销也越大，从而影响增删改的熟读

 

什么情况下要同时在两个字段上建索引？

当这两个字段被频繁使用的时候，本质上是通过这两个字段的数据内容建立起来一个新的平衡二叉树，同时索引生效还有一个桥的概念需要在where时多个索引值同时存在才可以

但是需要注意的是，索引不是越多越好，每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此给表添加索引，会增加表的体积，占用磁盘存储空间

 

索引碎片化的产生以及解决方案

在数据增删改的过程中导致索引分页被破坏

1、控制碎片化速度
创建索引时使用FILLFACTOR，在页上预留一定的空间，可以降低页拆分发生的频率，从而减缓索引碎片化的速度
2、碎片整理
ALTER INDEX [ 索引名 | ALL ]
ON [表名/视图名]
REBUILD/REORGANIZE

REBUILD
该选项重建索引的所有级别(根级、中间级和叶级)，并根据FILLFACTOR设置填充所有页。如果重建的是聚集索引，默认只有聚集索引才会重建。如果使用ALL关键字重建聚集索引，则所有非聚集索引也会被重建。重建索引会获取一个共享表锁，禁止任何更改，直到重建结束。

REORGANIZE
该选项只消除叶级的碎片，是一个ONLINE操作，不会引发长时间的阻塞。