MySQL详解

目录

索引数据结构（B+还是B-？）

为什么InnoDB使用聚集索引

非聚集索引

聚集索引

数据库引擎MyISAM与InnoDB的区别

mysql索引失效的几种情况

explain执行计划

数据库分库分表

日志文件redo log、undo log、binlog

order by与索引

事务

锁机制

MVCC

---

• 索引数据结构（B+还是B-？）

目前大多数数据库系统都使用B-树和B+树作为索引结构。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。机械磁盘的存储（I/O）需要寻道（移动磁头对准磁道）以及旋转（将磁盘的目标扇区旋转到磁头下），因此为了提高效率，要尽量减少磁盘I/O。

为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

• B-树 所有节点都存储key和data，所以按key查询数据的算法很简单：首先从根节点进行二分查找，如果找到就返回节点的data，否则对相应区间的指针指向的节点进行递归查找，直到找到节点或找到null，前者查找成功，后者查找失败。伪代码：

BTree_Search(node, key) {
    if(node == null) return null;
    foreach(node.key)
    {
        if(node.key[i] == key) return node.data[i];
            if(node.key[i] > key) return BTree_Search(leftChild, key);
    }
    return BTree_Search(rightChild, key);
}

data = BTree_Search(root, my_key);

• B+树 非叶子节点不存储data。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能。
• B+还是B-？ （1）B+树的中间结点不保存数据，所以磁盘页能容纳更多结点元素，更“矮胖”。（2）对于范围查找来说，B+只需遍历叶子节点链表即可，B树却需要重复地中序遍历。
• 其他结构：（1）为什么不用hash索引：hash索引性能不稳定，理想情况下是O(1)，但存在hash碰撞时，查找次数明显变多，参考hashmap用拉链法解决hash碰撞，在链表过长时使用红黑树，链表长度不小于8时将链表转化为红黑树，可见链表长度有可能过长。而B+树查询稳定在3-5次。hash索引功能不足，不支持模糊查询、不支持联合索引、不支持范围查询。（2）为什么MySQL不用红黑树而使用b+树？（镜像问题：为什么HashMap不使用b+树而使用红黑树）：B树层数少，可以确保读取磁盘的次数尽可能的少（上面说到每个节点是一页），所以b树更适合磁盘存储。红黑树查找的时间复杂度是O(logn)，而b树的查找时间复杂度在O(logn)~O(n)之间，比如若所有数据都在一个节点且要查找的数字是最大值，则变为O(n)，所以数据在内存时红黑树更有优势。

• 为什么InnoDB使用聚集索引

非聚集索引

非聚集索引在叶子节点的data域存放的是数据的地址，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如下图所示，左图为以col1为主键建立主索引，右图为在col2上建立辅助索引。