Mysql索引索引有什么用( 二 ) _小知识

如果不好理解，请看下面这个表：

文章插图
表中id和物理地址是保持一致顺序的，id较大的行，其物理地址也比较靠后。因为聚集索引的特性，它的建立有一定的特殊要求：

在Innodb中，聚簇索引默认就是主键索引。
如果表中没有定义主键，那么该表的第一个唯一非空索引被作为聚集索引。
如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

大家还记得，自增主键和uuid作为主键的区别么？由于主键使用了聚集索引，如果主键是自增id，那么对应的数据一定也是相邻地存放在磁盘上的，写入性能比较高。如果是uuid的形式，频繁的插入会使innodb频繁地移动磁盘块，写入性能就比较低了。

1.3 索引原理图示下面用一个通过主键索引查找数据的案例演示一下索引的原理。假如有student表如下，id上建立了聚集索引，name上建立非聚集索引：

文章插图
1.3.1 聚簇索引当我们执行下面的语句时，
SELECT name FROM student WHERE id=2
查询过程如下图所示：

文章插图
用语言描述一下，是这样的：

先找到根节点所在磁盘块，读入内存。（第1次磁盘I/O操作）
在内存中判断id=3所在区间(0,8)，找到该区间对应的指针1（第1次内存查找）
根据指针1记录的磁盘地址，找到磁盘块2并读入内存（第2次磁盘I/O操作）
在内存中判断id=3所在区间(0,4)，找到该区间对应的指针2（第2次内存查找）
根据指针2记录的磁盘地址，找到磁盘块4并读入内存（第3次磁盘I/O操作）
在内存中查找到id=2对应的数据行记录（第3次内存查找）

我们知道，磁盘I/O相对于内存运算（尤其内存中的主键是有序排列的，利用二分查找等算法效率非常高）耗时高得多，因此在数据库查询中，减少磁盘访问时数据库的性能优化的主要手段。
而分析上面过程，发现整个查询只需要3次磁盘I/O操作（其实InnoDB引擎是将根节点常驻内存的，第1次磁盘I/O操作并不存在）和3次内存查找操作。相对于不使用索引的遍历式查找，大大减少了对磁盘的访问，因此查找效率大幅提高。但是，因为索引树要与表中数据保持一致，因此当表发生数据增删改时，索引树也要相应修改，导致写数据比没有索引时开销大一些。
1.3.2 非聚簇索引好，聚集索引看完后，再看非聚集索引。

文章插图

如上图，多加一个索引，就会多生成一颗非聚簇索引树。因此，索引不能随意增加。在做写库操作的时候，需要同时维护这几颗树的变化，导致效率降低！
另外，仔细观察的人一定会发现，不同于聚集索引，非聚集索引叶子节点上不再是真实数据，而是存储了索引字段自身值和主键索引。因此，当我们执行以下SQL语句时：
SELECT id,name FROM student WHERE name='叶良辰';
整个查询过程与聚集索引的过程一样，只需要扫描一次索引树（n次磁盘I/O和内存查询），即可拿到想要的数据。
但是，如果查询name索引树没有的数据时，情况就不一样了：
SELECT score FROM student WHERE name='叶良辰';

文章插图

注意看上图中的红色箭头，因为扫描完name索引后，Mysql只能获取到对应的id和name，然后用id的值再去聚集索引中去查询score的值。这个过程相对于聚集索引查询的效率下降，可以理解了吧。

这就是通常所说的回表或者二次查询：使用聚集索引查询可以直接定位到记录，而普通索引通常需要扫描两遍索引树，即先通过普通索引定位到主键值，在通过聚集索引定位到行记录，这就是所谓的回表查询，它的性能比扫描一遍索引树低。

既然普通索引会导致回表二次查询，那么有什么办法可以应对呢？建立联合索引！
1.3.3 联合索引所谓联合索引，也称多列所谓，就是建立在多个字段上的索引，这个概念是跟单列索引相对的。联合索引依然是B+树，但联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

Mysql索引 索引有什么用( 二 )

Mysql索引索引有什么用( 二 )