MySQL高级篇第一天

小咪咪 2024-03-17 09:24 87阅读 0赞

**目录**

一、索引

二、索引结构

三、索引分类

四、索引语法

五、索引设计原则

六、视图

七、存储过程与概述

八、触发器

九、总结

--------------------

#### 一、索引 ####

**（一）索引概述**

索引是一种能够帮组Mysql高效的从磁盘上**查询数据**的一种数据结构，这些数据结构以某种方式引用（**指向**）**数据**， 这样就可以在这些数据结构上实现**高级查找算法**，这种数据结构就是索引，目的是为了**提高查询效率**。在MySQL5.5之后默认采取InnoDB作为存储引擎，InnoDB底层是采取**B+树**的结构来实现索引和数据的存储。如下图所示：

![58af1fad76c14a1598a34b4655f1aeb5.png][]

![3182cf8aef2d41669408908f89fde5d2.png][]

**（二）索引优点**

1）查询效率高：

通过**B+树**的结构来存储数据，因为B+树除了叶子节点之外，其他的节点只存储索引，不存储数据，所以可以大大减少数据检索时的**磁盘IO次数**，从而提升数据查询的性能；B+树在进行范围查找的时候，只要找到起始节点，然后基于叶子节点的**双向链表**结构往下读取即可，查询效率较高。

2）保证了数据唯一性：

在某些情况下，我们需要保证某个**字段的值是唯一的**，比如用户的ID或者邮箱等。可以通过**唯一索引**来约束，保证数据表中的每一行数据的唯一。

3）降低CPU消耗：

通过索引列对数据进行排序，降低数据排序成本，**降低CPU的消耗**。

**（三）索引缺点**

1）占用物理空间：

索引实际上也是一张表，该表中保存了主键和索引字段，指向实体类的记录，所以索引列也需要占用物理空间，数据库的增加、修改、删除操作，需要涉及到**索引的维护**，当数据量较大的情况下，索引的维护会带来**较大的性能开销**。

2）性能降低：

创建索引的时候，需要考虑到索引字段值的分散性，并按照索引设计原则**合理创建索引**，如果字段的重复数据过多，创建索引反而会造成性能降低。

#### 二、索引结构 ####

索引是在MySQL的**存储引擎层**中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。我们平常所说的索引，如果没有特别指明，都是指**B+树**（多路搜索树，并不一定是二叉的）结构组织的索引。其中  
聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

（一）、BTree数据结构

例如一颗**m叉**的BTree树：

每个节点最多包含m个孩子。  
除根节点与叶子节点外，每个节点至少有\[ceil(m/2)\]个孩子。  
若根节点不是叶子节点，则至少有两个孩子。  
所有的叶子节点都在同一层。  
每个非叶子节点由n个key与n+1个指针组成，其中\[ceil(m/2)-1\] <= n <= m-1

![7be31c492dad4b56a6d62a43b4db631a.png][]

（二）、B+Tree数据结构

B+Tree为BTree的变种，如果n叉的B+Tree树，B+Tree与BTree的区别为：  
1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。  
2). **B+Tree的叶子节点保存所有的key信息**，依key大小顺序排列。  
3). 所有的**非叶子节点都可以看作是key的索引部分**。

![532c4346e561497b9c6da89abee75b6a.png][]

（三）、MySQL中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针（**双向链表**），就形成了带有顺序指针的B+Tree，提高区间访问的性能和**效率**。

![f562f2966673448d921cc81507b94112.png][]

#### 三、索引分类 ####

**1）主键索引：**数据列不允许重复，不允许为null，一个表只能有一个主键。

**2）唯一索引：**数据列不允许重复，允许为null值，一个表允许多个列创建唯一索引。

**3）普通索引：**基本的索引类型，没有唯一性的限制，允许为Null值。

**4）全文索引：**是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。

**5）覆盖索引：**查询列要被建立的索引覆盖，不必读取数据行。

**6）组合索引：**多列值组成一个索引，用于组合搜索，效率大于索引合并

#### 四、索引语法 ####

（一）创建索引

示例 ： 为city表中的city\_name字段创建索引 ；

create index idex_city_name on city(city_name);

（二）查看索引

示例：查看city表中的索引

show index from city;

（三）删除索引

示例 ： 想要删除city表上的索引idx\_city\_name，可以操作如下：

drop index idx_city_name on city;

（四）、修改索引

1). alter table tb_name add primary key(column_list);
    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
    2). alter table tb_name add unique index_name(column_list);
    这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
    3). alter table tb_name add index index_name(column_list);
    添加普通索引， 索引值可以出现多次。
    4). alter table tb_name add fulltext index_name(column_list);
    该语句指定了索引为FULLTEXT， 用于全文索引

#### 五、索引设计原则 ####

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

（一）、对于查询频**率高的字段、数据量大**的表建立索引

（二）、索引字段的选择，最佳候选列应当从**where子句的条件中**提取，如果where子句中的组合比较多，那么应当挑选**最常用、过滤效果最好的列**的组合。

（三）、使用唯一索引，区分度越高，使用索引的效率越高。

（四）、遵循**最左匹配原则。**

创建复合索引:
    CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
    就相当于
    对name 创建索引 ;
    对name , email 创建了索引 ;
    对name , email, status 创建了索引 ;

#### 六、视图 ####

视图（View）是一种**虚拟存在的表**。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

#### 七、存储过程与概述 ####

存储过程和函数是事先经过编译并存储在数据库中的一段 **SQL 语句的集合**，调用存储过程和函数可以**简化**应用开发人员的很多工作，**减少**数据在数据库和应用服务器之间的传输，对于**提高数据处理的效率**是有好处的。存储过程和函数的区别在于函数必须有返回值，而存储过程没有。  
函数 ： 是一个有返回值的过程 ；  
过程 ： 是一个没有返回值的函数 ；

#### 八、触发器 ####

触发器是与表有关的**数据库对象**，指在insert/update/delete之前或之后**，触发并执行触发器定义的SQL语句集合**。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

#### 九、总结 ####

索引就是一种提高查询效率的数据结构，MySQL5.5以后默认都是使用InnoDB作为存储引擎，采用的是**聚簇索引。**由于它的底层数据结构是采用**B+树**。而B+树叶子节点存储所有数据，**非叶子节点只存储索引**，因此它的**磁盘IO次数比较少**，树也比较矮，**查询效率非常高**。并且MySQL中的B+树对原始B+树进行了优化，在原B+树的基础上添加了**双向链表指针**，当要查询数据的时候，只需要**遍历两个节点的链表指针**即可获取所有的数据，提高了区间访问的**性能和速度**。

[58af1fad76c14a1598a34b4655f1aeb5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/b9dbd6aa2f0341daa8aa61c11840c83f.png
[3182cf8aef2d41669408908f89fde5d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/a5e58830c9d94acebcc1acca6bb5b700.png
[7be31c492dad4b56a6d62a43b4db631a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/d31675bc0eb748099a8a47849bbd4580.png
[532c4346e561497b9c6da89abee75b6a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/7115db1325ac4acdab4c914d626c9a99.png
[f562f2966673448d921cc81507b94112.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/b7ab1e151184457aaac5fc2c505fcd02.png