MySql的索引分类、数据结构以及优化细节

文章目录

索引的分类mysql数据结构面试常问名词Mysql优化小细节

实操案例： 选错「索引」导致的线上慢查询 explain指令：MySQL Explain详解

B树和B+树各自的优势：为什么 Mongodb 索引用 B 树，而 Mysql 用 B+ 树?

索引的分类

主键索引（不允许空值）唯一索引（允许空值）普通索引（其他普通列）全文索引（关键字查询，mysql不推荐用）组合索引（一个索引包含多个列）

mysql数据结构

树形结构的演化 二叉树->BST（banary search tree，实现了二分查找，但是插入递增数据的话，会退化成近似链表的结构）->AVL Tree（自动旋转，最短子树和最长子树高度之差不超过1，查找性能提升是以插入性能降低为前提的）-> BRT（black red tree，最长子树和最短子树高度之差不超过1倍，在插入性能以及查询性能之间做了平衡，适用于插入频率和查询频率近似相等的场景）

mysql为什么不使用以上树形结构

IO读取数据的最小单位是页（8K/16K等），每个节点上的数据不能保证对齐；数据量大的时候，树型结构变深，增加IO次数，降低效率。

B-Tree

b树有个概念叫degree，可以定义每个节点存储的数据量（多个数据可以存放进一个节点，可以作为一个数据页）。

b树模型（InnoDB默认加载16K）

B+树模型（非叶子节点全部存放指针和索引，数据全部放进叶子结点，减小了树的深度，提高查询效率），三层B+树可以支撑千万级别的数据量

B+树所有的叶子结点之间存在双向链表。

InnoDB–B+Tree，叶子结点直接存放数据

每建一个索引，就会有一颗B+树，换言之，一张表里可以有多棵树，但是带有原始数据的tree只有一颗（如下例子，name列也创建了索引，name树叶子节点存放的就是主键id的值）。

面试常问名词

回表：先查name树查到id值，再查id树找到数据，这样查多棵树的行为叫做回表，应该尽量避免。索引覆盖：查询的索引信息包含查询数据，不需要回表即可返回数据，称为索引覆盖。

select id,name from tbl where name=‘zhang’; select * from tbl where name=‘zhang’; 此两种方式查询过程不一样。

最左匹配：针对于组合索引，优先过滤不符合最左定义的索引的数据。

name age 组合索引 where name = ？ and age = ？ where name = ？ where age = ？ where age = ？ and name = ？ 依然会走最左匹配，优化器优化

索引下推：针对于组合索引，5.6之后根据name，age两个列的值去获取数据，直接返回（减少了整体io量）。

Mysql优化小细节

当使用索引列进行查询的时候，尽量不要使用表达式，把计算放在业务层而不是数据库层；

尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询（主键最好自增，否则不好维护，大小乱序会造成叶子结点分裂）；

使用索引的选择性。如果主键是varchar且较长（uuid），可以使用left（uid，7）来创建索引。

强制类型转化，会使索引失效，进行全盘扫描。

更新十分频繁，数据区分度不高的字段不宜建立索引。

索引列不允许为null（mybatis会把0当做null）；

表连接查询最好不要超过三张表；

能使用limit尽量使用limit，减少IO量；

创建索引应避免以下认知：

索引越多越好过早优化，在不了解系统的情况下进行优化