hello，大家好，欢迎来到橙子老哥的分享时刻，希望大家一起学习，一起进步。
欢迎加入.net意社区，第一时间了解我们的动态，文章第一时间分享至社区
社区官方地址：https://ccnetcore.com (上千.neter聚集地)
官方微信公众号：搜索 意.Net
添加橙子老哥微信加入官方微信群：chengzilaoge520
今天，我们来聊一聊当代Crud Boy必备的东西--Mysql数据库
1、数据结构
数据结构可视化网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
聊这个之前，我们要先来掌握一些基础的数据结构，否则肯定一团雾水 当然，这个也不是本期文章的重点，笔者还是会简单带过一下
首先，我们要清楚，数据库说简单点，就是一个存数据的地方，方便数据的操作，crud等 它既要保证数据的写入操作要快，又要保证查询操作要快
例如，一本书，想要找到一个内容，从第一面开始翻，耗时耗力，但是我们给它加一个目录，这查起来，快了不是一个级别
查询数据，也是这个道理，我们的数据保存在硬盘中，通过什么操作，能让更快的查询到想要的数据呢？这里就要用到数据结构
下面，介绍一下常用的几个数据结构去充当数据库会怎么样，简单过一遍
1.1、链表
我们通过链表存储，遍历查询确实是快，插入也快，但是不适合搜索查询，比如我想查询年龄等于10的人，还是需要从链表的一个开始遍历，我们数据库肯定不能每次都要全表扫描
1.2、二叉树(二叉搜索树)
我们通过二叉树存储，通过二叉查询，查询速度确实快上去了
但是有个问题，当我们顺序插入数据的时候，二叉树就会变得很不平衡，导致和链表一个样，又回到了链表的结构上
1.3、AVL树(平衡二叉搜索树)
既然二叉树会有不平衡的情况，那我们就让它平衡
每次插入的时候，发现不平衡了，就将树进行旋转，变成平衡的，保证了这个查询起来不就快了
但是想要保证每次都绝对的平衡，也没那么简单，旋转的情况都能拆分很多种，而且每次旋转还要考虑父子节点，对于写操作，很不友好
1.4、红黑树
红黑树和AVL很像，但是通过每个节点引入颜色，进行限制，它并没有要求AVL树的那种绝对平衡，但是基本能保证平衡，也解决了写入操作过于复杂的问题
2、B树和B+树（平衡多路查找树）
下面要重点介绍，B树和B+树，中所皆知，mysql的引擎InnoDB就是采用基于B+树的一种结构，想要研究Mysql，我们就要掌握好B+树
2.1、B树
红黑树这么完美，使用起来没有问题吗？
也不是，由于每个节点都只有一个，这个树的层级会非常深，查询起来也会非常耗时。如果这个查询操作时在内存中，倒问题不大，如果时访问的硬盘IO，当树的高度太大，一样会导致查询慢的问题。
b树就是用来解决这个问题的，B树可以有多个子节点，而红黑树则是二叉，极大程度降低了树的高度。如果有写入操作，每个节点超出了它的最大范围，只需要进行分裂和合并操作即可。
2.2、B+树
B+树从名字上来就可以知道，是对B树的“加强版”。
B树的每个节点，都存放了对应的关键字和数据，当我们想要进行遍历操作的时候，B树就不舒服了，只能通过树的中序遍历去一个一个找
为了解决这点，这里就有B+树。它将每个数的节点不再存储数据，而只存储对应的关键字，在最后一层的叶子节点才真实存放数据，这就导致想要查找到数据，就必须走到最后一层，最后一层天然就是有序的结构
这里刚好可以再加上一个链表，这下，我们像遍历数据，又可以直接走链表的查询，我们想插入数据，又可以走B树的分裂和合并操作。
3、代码实现
上面的b+树，我们使用c#代码去简单实现一下，这里主要是实现插入和查询操作，查询很简单，插入操作就需要考虑B树的分裂
class BPlusTreeNode
{
public bool IsLeaf { get; set; }
public List<int> Keys { get; private set; }
public List<BPlusTreeNode> ChildNodes { get; private set; }
public BPlusTreeNode Parent { get; set; }
public int MaxDegree { get; private set; }
public BPlusTreeNode(int maxDegree, bool isLeaf)
{
MaxDegree = maxDegree;
IsLeaf = isLeaf;
Keys = new List<int>();
ChildNodes = new List<BPlusTreeNode>();
}
}
class BPlusTree
{
private BPlusTreeNode root;
private int maxDegree;
public BPlusTree(int degree)
{
maxDegree = degree;
root = new BPlusTreeNode(degree, true);
}
public void Insert(int key)
{
BPlusTreeNode node = root;
// 找到要插入的叶子节点
while (!node.IsLeaf)
{
int i = 0;
while (i < node.Keys.Count && key > node.Keys[i])
{
i++;
}
node = node.ChildNodes[i];
}
// 在叶子节点中插入
int position = node.Keys.BinarySearch(key);
if (position < 0) position = ~position; // 获取插入的位置
node.Keys.Insert(position, key);
// 检查是否需要分裂
if (node.Keys.Count == maxDegree)
{
Split(node);
}
}
private void Split(BPlusTreeNode node)
{
int midIndex = node.Keys.Count / 2;
int midKey = node.Keys[midIndex];
BPlusTreeNode newSibling = new BPlusTreeNode(maxDegree, node.IsLeaf)
{
Parent = node.Parent
};
// 分配 keys 和 child nodes
for (int i = midIndex + 1; i < node.Keys.Count; i++)
{
newSibling.Keys.Add(node.Keys[i]);
}
if (!node.IsLeaf)
{
for (int i = midIndex + 1; i < node.ChildNodes.Count; i++)
{
newSibling.ChildNodes.Add(node.ChildNodes[i]);
node.ChildNodes[i].Parent = newSibling;
}
}
// 更新原始节点
node.Keys.RemoveRange(midIndex, node.Keys.Count - midIndex);
node.ChildNodes.Add(newSibling);
// 如果是根节点，则需要新建根节点
if (node.Parent == null)
{
root = new BPlusTreeNode(maxDegree, false);
root.Keys.Add(midKey);
root.ChildNodes.Add(node);
root.ChildNodes.Add(newSibling);
node.Parent = root;
newSibling.Parent = root;
}
else
{
node.Parent.Keys.Add(midKey);
node.Parent.ChildNodes.Add(newSibling);
node.Parent.ChildNodes.Sort((x, y) => x.Keys[0].CompareTo(y.Keys[0]));
// 检查父节点是否需要分裂
if (node.Parent.Keys.Count == maxDegree)
{
Split(node.Parent);
}
}
}
public bool Search(int key)
{
BPlusTreeNode node = root;
while (node != null)
{
int i = 0;
while (i < node.Keys.Count && key > node.Keys[i])
{
i++;
}
if (i < node.Keys.Count && key == node.Keys[i])
{
return true; // 找到关键字
}
if (node.IsLeaf)
{
return false; // 在叶子节点中未找到
}
node = node.ChildNodes[i]; // 继续向下搜索
}
return false; // 未找到
}
}
class Program
{
static void Main(string[] args)
{
BPlusTree bPlusTree = new BPlusTree(3); //
设定每个节点最大三个子节点
// 插入
bPlusTree.Insert(12);
bPlusTree.Insert(32);
bPlusTree.Insert(65);
bPlusTree.Insert(10);
bPlusTree.Insert(23);
bPlusTree.Insert(86);
Console.WriteLine(bPlusTree.Search(10)); // 输出 True
Console.WriteLine(bPlusTree.Search(15)); // 输出 False
}
}
4、聚簇索引与回表查询
我们要讲mysql的回表查询，就要先讲什么是它的聚簇索引
简单说，数据和索引是否是在一起，如果放在一起就是聚簇索引，否则就是非聚簇索引 mysql默认有且只有一个聚簇索引，因为数据都挂在这个唯一的聚簇索引上。当我们的表中，有主键的时候，主键索引就是聚簇索引
当建了一个普通索引，它最终指向的是索引列的数据和聚簇索引的关键字，如果查询的语句中还有非索引的字段，在b+树查找到的索引值，还需要再去聚簇索引上通过主键去查找其他字段，这个过程，就叫做回表查询
例如，给name和age建立索引 查询语句：
select name,age,sex from student where name='张三' and age>10 (回表)
select name,age from student where name='张三' and age>10 (不回表)
select * from student where name='张三' and age>10 (回表)
所以在业务允许的情况下，最好select 只有索引的字段，这样的效率是非常高的
5、聚合索引最左匹配原则
假设有一个聚合索引 (A, B, C)
SELECT * FROM table WHERE B ='value' AND C ='value';(失效)
SELECT * FROM table WHERE A ='value' AND C ='value';(失效)
SELECT * FROM table WHERE A ='value';(成功)
SELECT * FROM table WHERE A ='value' AND B >'value' AND C >'value';(成功)
SELECT * FROM table WHERE A ='value' AND B >'value';(成功)
SELECT * FROM table WHERE C ='value'
AND B >'value' AND A >'value';(成功)
聚合索引，是将多个索引共同组合在一起，它的结构和普通索引几乎没区别，只是存储的数据由一列，变成了多个值
当我们查询聚合索引的时候，为什么会有最左匹配原则？
因为我们我们在b+树存储的节点，是按照从左到右进行排序的，每次比较的时候也是从左到右进行比较，如果跳过了一个，就匹配不上了，自然索引也就失效了
6、索引失效
在 MySQL 中，索引的失效可能会导致查询性能的下降。以下是一些常见的导致 MySQL 索引失效的情况
分享知乎一篇文章，索引失效情况比较明了：https://zhuanlan.zhihu.com/p/703872510
1. 字段类型隐式转换
2. 查询条件中包含or
3. like 通配符% 错误使用
4. 联合索引最左匹配原则
5. 索引列使用MySQL函数，索引失效
6. 索引列存在计算，使用（+、-、*、/），索引失效
7. 使用（！= 或者 < >，not in），导致索引失效
8. 使用is null， is not null，导致索引失效
9. 左连接、右连接关联字段编码不一致，索引失效
10. 使用了select *，导致索引失效
11. 两字段列做比较，导致索引失效
12. order by使用,导致索引失效
13. 参数不同，导致索引失效
14. group by 使用违反最左匹配原则，导致索引失效
最后，不得不佩服先辈，一步一个脚印，弄出B+树这么神仙的玩意儿，最后用链表把叶子节点串起来，简直是神之一手
.Net意社区，高频发布原创有深度的.Net相关知识内容
与你一起学习，一起进步
