mysql 的一些基础知识点

友儿

发布于 2024-02-22 10:57:44

1250

发布于 2024-02-22 10:57:44

文章被收录于专栏：友儿

mysql索引的本质是什么 1、其实就相当于目录，是帮助mysql高效获取数据的数据结构。 2、我们都知道，在mysql中数据最终存储在硬盘中的，访问磁盘相当于是IO操作。 3、在mysql中有一个page的概念，一个表都被分为若干个页面(page),每个页面(page)就是树中的一个节点，每次mysql就会取出一个页面(page)也就是一个节点的数据，而mysql默认一个页面(page)保存16k的数据。 4、页面(page)的大小会直接影响到数据的存储和检索效率，因此我们也可以实际业务需求和硬件条件进行评估和调整，合理设置mysql的页面(page)大小，以达到最佳的性能表现。

mysql的索引常见分类以及操作索引的语法

主键索引

主键索引是一种特殊的索引类型，它是用于唯一标识每一行数据的索引，每个表只能有一个主键索引，索引列中的值必须是唯一的，不允许有空值。

复合索引(又称为联合索引)

复合索引也叫多列索引或联合索引，它是包含多个列的索引类型，能够加速多列查询和排序操作。需要遵循最左前缀匹配原则（最左匹配原则）

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引

唯一索引是用来保证列的唯一性的索引，一个表可以有多个唯一索引。索引列中的值必须是唯一的，但是允许为空值。

全文索引

全文索引是一种用于全文搜索的索引类型，能够对文本数据进行快速的模糊搜索和关键字搜索。只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。

哈希索引

哈希索引是基于哈希表实现的索引类型，能够对等值查询进行高效的处理，但不支持范围查询和排序，MySQL 中 Memory 引擎中支持哈希索引。

索引操作

`新建表中添加索引`

CREATE TABLE 表名 (
       主键字段名 数据类型 [完整性约束条件]  PRIMARY KEY,
       字段名 数据类型 [完整性约束条件],
       [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY  [索引名](字段名1 [(长度)] [ASC | DESC])
);

注解：
    UNIQUE：可选。表示索引为唯一性索引。
    FULLTEXT；可选。表示索引为全文索引。
    SPATIAL：可选。表示索引为空间索引。
    INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
    索引名：可选。给创建的索引取一个新名称。
    字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
    长度：可选。指索引的长度，必须是字符串类型才可以使用。
    ASC：可选。表示升序排列。
    DESC：可选。表示降序排列。

`在已建表中添加索引`

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON  表名(字段名)

`以修改表的方式添加索引`

ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL | INDEX | KEY | PRIMARY KEY] 索引名(字段名)

索引的数据类型

二叉树
平衡二叉树
b-tree
b+tree

二叉树 (简称：BST树,全称：Binary Search Trees)

一、特点 1、左子树的所有值都小于根节点 2、右子树的所有值都大于根节点 3、每个根节点最多分列出两个子节点 二、模型 1、如图1所示，如果我们要查询9，首先从根节点10查询，9小于10，走左节点8，9大于8，走右节点9，因为每个节点就是一次IO查询，相当于进行了3次IO查询。

2、如图2所示，二叉树有一种极端情况，假如从小到大依次向树中插入的情况就会形成一个链表的形式，我们这时候要查询9时，至少需要9次IO操作，相当于遍历了整个表，IO次数可想而知。

为了避免这个问题呢？就有了平衡二叉树。

平衡二叉树 (简称：AVL树,全称：Balanced binary search trees)

一、特点 1、相对平衡，左右两个子树的深度差绝对值不能超过1 2、左右两个子树也必须是平衡二叉树 3、避免了二叉树的极端情况 二、模型

三、缺点 1、一个节点只有2个分支，一个节点只能保存一条数据，获取16k的页面(page)，只有一条数据，造成资源浪费。单个节点保存数据少，就会造成节点增多，树的深度就会增多，查询次数就会增多，IO次数多。 2、每次增删之后，都要进行平衡，会降低效率。

为了避免这个问题呢？就有b-tree

b-tree (一种多路自平衡搜索树，它类似普通的二叉树，但是b-tree允许每个节点有更多的子节点)

一、特点 1、所有键值分布在整个树中 2、任何关键字出现且只出现在一个节点中 3、搜索有可能在非叶子结点结束 4、在关键字全集内做一次查找，性能逼近二分查找算法 5、自动层次控制 6、一个节点可以存储超过2个元素，可以拥有超过2个子节点；拥有二叉树的一些性质；平衡，每个节点的所有子树高度一致；比较矮。

二、一棵m阶的B-Tree有如下特性 1、每个节点最多有m个孩子。 2、除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子(Ceil返回大于或者等于指定表达式的最小整数)。 3、若根节点不是叶子节点，则至少有2个孩子 4、所有叶子节点都在同一层，且不包含其它关键字信息 5、每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn） 6、关键字的个数n满足：ceil(m/2)-1 <= n <= m-1 7、ki(i=1,…n)为关键字，且关键字升序排序。 8、Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

三、模型(以m=3的b-tree举例如图4 所示) 1、每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。 2、两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。 3、以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

4、模拟查找关键字29的过程： ① 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】 ② 比较关键字29在区间（17,35），找到磁盘块1的指针P2。 ③ 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】 ④ 比较关键字29在区间（26,30），找到磁盘块3的指针P2。 ⑤ 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】 ⑥ 在磁盘块8中的关键字列表中找到关键字29。