第25天：高级数据库学习笔记1

发布于：2025-07-02 ⋅ 阅读:(23) ⋅ 点赞:(0)

什么是存储过程？

实际开发过程中很少使用存储过程。（因此暂且跳过）

存储过程就是数据库里面内置的编程语言，类似于Java。

存储过程的优点和缺点？

优点：速度快。
解释：若是在Java程序不断处理数据库数据，再返回给数据库，这会造成很大的通信开销。若是在数据库中编写存储过程（处理数据库的函数），Java程序去调用该过程，则会降低通信开销。

缺点：移植性差。编写难度大。维护性差。
mysql数据库的存储过程，到了orcale数据库不可以用。

存储引擎概述

注意：存储引擎跟存储过程没有任何关系。

存储引擎的作用：决定了数据在磁盘上存取和访问的方式。为了适应不同的业务场景，开发出了不同的存储引擎。典型的存储引擎有：

  • InnoDB引擎支持事务和行级锁定（例如间隙锁与记录锁），适用于需要高并发读写的应用；
  • MyISAM引擎不支持事务，但适用于读操作较多的应用；
  • Memory引擎数据全部存储在内存中，不支持事务，适用于对读写速度要求很高的应用等等。

一般情况下存储引擎不用改默认innoDB,只有在要满足其他业务需求时才要改。

MySQL支持哪些存储引擎

使用show engines \G;命令可以查看所有的存储引擎：

Support是Yes的表示支持该存储引擎。当前MySQL的版本是8.0.33
MySQL默认的存储引擎是：InnoDB

指定和修改存储引擎

指定存储引擎

指定存储引擎的示例：

代码：CREATE TABLE my_table (column1 INT, column2 VARCHAR(50)) ENGINE = InnoDB;

解释：创建了一个名为my_table的表，并指定了使用InnoDB存储引擎。

修改存储引擎

一般用不着。因为改了，检索算法变了，可能导致数据的丢失。

修改存储引擎的示例：

代码：ALTER TABLE my_table ENGINE = MyISAM;

解释：使用ALTER TABLE语句将my_table表的存储引擎修改为MyISAM。

修改存储引擎需要考虑的点：

  1. 修改存储引擎可能需要执行复制表的操作，因此可能会造成数据的丢失或不可用。确保在执行修改之前备份你的数据。
  2. 不是所有的存储引擎都支持相同的功能。要确保你选择的新存储引擎支持你应用程序所需的功能。
  3. 修改表的存储引擎可能会影响到现有的应用程序和查询。确保在修改之前评估和测试所有的影响。
  4. ALTER TABLE语句可能需要适当的权限才能执行。确保你拥有足够的权限来执行修改存储引擎的操作。

索引（超级重要）

什么是索引

相当于书的目录。，对于MySQL数据库而言，索引对应的数据结构是B+树。

索引的创建和删除

主键和unique约束的字段会自动添加索引。

查看某张表上添加了哪些索引

show index from 表名;

给指定的字段添加索引

建表时添加索引：

CREATE TABLE emp (
    ...
    name varchar(255),
    ...
    INDEX idx_name (name)
);

如果表已经创建好了，后期给字段添加索引

ALTER TABLE emp ADD INDEX idx_name (name);

也可以这样添加索引：

create index idx_name on emp(name);

删除指定字段上的索引

ALTER TABLE emp DROP INDEX idx_name;

索引的分类

不同的存储引擎有不同的索引类型和实现：

  • 按照数据结构分类：
    • B+树 索引（mysql的InnoDB存储引擎采用的就是这种索引）采用 B+树 的数据结构
    • Hash 索引（仅 memory 存储引擎支持）：采用 哈希表 的数据结构
  • 按照物理存储分类：
    • 聚集索引：索引和表中数据在一起，数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引。
    • 非聚集索引：索引和表中数据是分开的，索引是独立于表空间的，一张表可以有多个非聚集索引。
  • 按照字段特性分类：
    • 主键索引（primary key）
    • 唯一索引（unique）
    • 普通索引（index）
    • 全文索引（fulltext：仅 InnoDB和MyISAM 存储引擎支持）
  • 按照字段个数分类：
    • 单列索引、联合索引（也叫复合索引、组合索引）

B+树经典面试题

**经典面试题：**mysql为什么选择B+树作为索引的数据结构，而不是B树？

  1. 非叶子节点上可以存储更多的键值，阶数可以更大，更矮更胖，磁盘IO次数少，数据查询效率高。
  2. 所有数据都是有序存储在叶子节点上，让范围查找，分组查找效率更高。
  3. 数据页之间、数据记录之间采用链表链接，让升序降序更加方便操作。

**经典面试题：**如果一张表没有主键索引，那还会创建B+树吗？

当一张表没有主键索引时，默认会使用一个隐藏的内置的聚集索引（clustered index）。这个聚集索引是基于表的物理存储顺序构建的，通常是使用B+树来实现的。

其他索引及相关调优

Hash索引

支持Hash索引的存储引擎有：

  • InnoDB（不支持手动创建Hash索引，系统会自动维护一个自适应的Hash索引）
    • 对于InnoDB来说，即使手动指定了某字段采用Hash索引，最终show index from 表名的时候，还是BTREE。
  • Memory（支持Hash索引）

Hash索引底层的数据结构就是哈希表。一个数组，数组中每个元素是链表。和java中HashMap一样。哈希表中每个元素都是key value结构。key存储索引值，value存储行指针。

Hash索引优缺点：

  • 优点：只能用在等值比较中，效率很高。例如：name=‘孙悟空’
  • 缺点：不支持排序，不支持范围查找。

聚集索引和非聚集索引

聚集索引：索引值与数据存放在一起。
非聚集索引：索引值与数据不存放在一起。

存储引擎是InnoDB的，主键上的索引属于聚集索引。
存储引擎是MyISAM的，任意字段上的索引都是非聚集索引。

InnoDB的物理存储方式：当创建一张表t_user，并使用InnoDB存储引擎时，会在硬盘上生成这样一个文件：

  • t_user.ibd （InnoDB data表索引 + 数据）
  • t_user.frm （存储表结构信息）

MyISAM的物理存储方式：当创建一张表t_user，并使用MyISAM存储引擎时，会在硬盘上生成这样一个文件：

  • t_user.MYD （表数据）
  • t_user.MYI （表索引）
  • t_user.frm （表结构）

注意：从MySQL8.0开始，不再生成frm文件了，引入了数据字典，用数据字典来统一存储表结构信息，例如：

  • information_schema.TABLES （表包含了数据库中所有表的信息，例如表名、数据库名、引擎类型等）
  • information_schema.COLUMNS（表包含了数据库中所有表的列信息，例如列名、数据类型、默认值等）

聚集索引的原理图：（B+树，叶子节点上存储了索引值 + 数据）
未命名文件 (1).png

非聚集索引的原理图：（B+树，叶子节点上存储了索引值 + 行指针）
未命名文件 (1).png

聚集索引的优点和缺点：

  1. 优点：聚集索引将数据存储在索引树的叶子节点上。可以减少一次查询，因为查询索引树的同时可以获取数据。
  2. 缺点：对数据进行修改或删除时需要更新索引树，会增加系统的开销。

二级索引（重点）

二级索引属于非聚集索引。
一张表上的主键是聚集索引，他的非主键是二级索引，非主键二级索引的叶子节点并不存放数据，而是存放该条数据对应的主键值对应的索引，然后再查B+树，找到该数据。

有表t_user，id是主键。age是非主键。在age字段上添加的索引称为二级索引。（所有非主键索引都是二级索引）

image.png

二级索引的数据结构：
无标题.png

二级索引的查询原理：
假设查询语句为：

select * from t_user where age = 30;

无标题.png

为什么会“回表”？因为使用了select *
避免“回表【回到原数据表】”是提高SQL执行效率的手段。例如：select id from t_user where age = 30; 这样的SQL语句是不需要回表的。

覆盖索引

是基于多列索引的
覆盖索引（Covering Index），查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。

假设有一个用户表（user）包含以下列：id, username, email, age。

常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能，可以创建一个覆盖索引，包含（username, email）这两列。

创建覆盖索引的SQL语句可以如下：

CREATE INDEX idx_user_username_email ON user (username, email);

当执行以下查询时：

SELECT email FROM user WHERE username = 'lucy';

值得注意的是，覆盖索引的创建需要考虑查询的字段选择。如果查询需要的字段较多，可能需要创建包含更多列的覆盖索引，以满足完全覆盖查询的需要。

覆盖索引具有以下优点：

  1. 提高查询性能：覆盖索引能够满足查询的所有需求，同时不需要访问表中的实际数据行，从而可以提高查询性能。这是因为DBMS可以直接使用索引来执行查询，而不需要从磁盘读取实际的数据行。
  2. 减少磁盘和内存访问次数：当使用覆盖索引时，DBMS不需要访问实际的数据行。这样可以减少磁盘和内存访问次数，从而提高查询性能。
  3. 减少网络传输：由于在覆盖索引中可以存储所有查询所需的列，因此可以减少数据的网络传输次数，从而提高查询的性能。
  4. 可以降低系统开销：在高压力的数据库系统中，使用覆盖索引可以减少系统开销，从而提高系统的可靠性和可维护性。

覆盖索引的缺点包括：

  1. 需要更多的内存：覆盖索引需要存储查询所需的所有列，因此需要更多的内存来存储索引。在大型数据库系统中，这可能会成为一项挑战。
  2. 会使索引变得庞大：当索引中包含了许多列时，它们可能会使索引变得非常庞大，从而影响查询性能，并且可能会占用大量的磁盘空间。
  3. 只有在查询中包含了索引列时才能使用：只有当查询中包含了所有的索引列时才能使用覆盖索引。如果查询中包含了其他列，DBMS仍然需要访问实际的数据行，并且无法使用覆盖索引提高查询性能。

索引下推

核心思想：减少回表次数。

一般情况下索引下推是MYSQL优化自动处理，不需要程序员干预。

索引下推，是基于多列索引的，根据查询条件在索引层进行筛选，这样就减少了回表次数。

假设有以下表结构：

表名：users

id name age city
1 John 25 New York
2 Alice 30 London
3 Bob 40 Paris
4 Olivia 35 Berlin
5 Michael 28 Sydney

现在我们创建了一个多列索引：（索引下推通常是基于多列索引的。）

ALTER TABLE users ADD INDEX idx_name_city_age (name, city, age);

假设我们要查询年龄大于30岁，并且所在城市是"London"的用户，假设只给age字段添加了索引，它就不会使用索引下推。传统的查询优化器会将所有满足年龄大于30岁的记录读入内存，然后再根据城市进行筛选。

使用索引下推优化后，在索引范围扫描的过程中，优化器会判断只有在城市列为"London"的情况下，才会将满足年龄大于30岁的记录加载到内存中。这样就可以避免不必要的IO和数据传输，提高查询性能。

具体的查询语句可以是：

SELECT * FROM users WHERE age > 30 AND city = 'London';

在执行这个查询时，优化器会使用索引下推技术，先根据索引范围扫描找到所有满足条件的记录，然后再回到原数据表中获取完整的行数据，最终返回结果。

单列索引（单一索引）

单一索引是指将表的某一字段作为索引。对表数据进行查询时，用作为索引的表字段作为筛选条件，查询最快。

复合索引（组合索引）

对数据库表中多个列进行索引创建。

索引的优缺点（面试可能会问）

优点：

  1. 提高查询性能：通过创建索引，可以大大减少数据库查询的数据量，从而提升查询的速度。
  2. 加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率。
  3. 减少磁盘IO：索引可以减少磁盘IO的次数，这对于磁盘读写速度较低的场景，尤其重要。

缺点：

  1. 占据额外的存储空间：索引需要占据额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。
  2. 增删改操作的性能损耗：每次对数据表进行插入、更新、删除等操作时，需要更新索引，会导致操作的性能降低。
  3. 资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。

何时用索引

在以下情况下建议使用索引：

  1. 频繁执行查询操作的字段：如果这些字段经常被查询，使用索引可以提高查询的性能，减少查询的时间。
  2. 大表：当表的数据量较大时，使用索引可以快速定位到所需的数据，提高查询效率。
  3. 需要排序或者分组的字段：在对字段进行排序或者分组操作时，索引可以减少排序或者分组的时间。
  4. 外键关联的字段：在进行表之间的关联查询时，使用索引可以加快关联查询的速度。

在以下情况下不建议使用索引：

  1. 频繁执行更新操作的表：如果表经常被更新数据，使用索引可能会降低更新操作的性能，因为每次更新都需要维护索引。
  2. 小表：对于数据量较小的表，使用索引可能并不会带来明显的性能提升，反而会占用额外的存储空间。
  3. 对于唯一性很差的字段，一般不建议添加索引。当一个字段的唯一性很差时，查询操作基本上需要扫描整个表的大部分数据。如果为这样的字段创建索引，索引的大小可能会比数据本身还大，导致索引的存储空间占用过高，同时也会导致查询操作的性能下降。

总之，索引需要根据具体情况进行使用和权衡，需要考虑到表的大小、查询频率、更新频率以及业务需求等因素。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布