Mysql-我眼中的一个数据存储软件，我把他比作记事本、word我感觉不为过-CSDN博客

本文链接：https://blog.csdn.net/sutingStart/article/details/136039642

前言

在学习工作这些年中，Mysql一直离不开，但是我们在实际中使用到的也仅仅是他的十分之一都不到（数据不一定准确），但是作为我认为很强大的一个程序，我们很有必要去了解一下他的思想。致敬：迈克尔·维德纽斯（Michael Widenius）、詹姆斯·尼古拉·格雷
但是我们使用的时候也是遵从四步：
增、删、改、查

常规操作：

增：

Mysql的增加其实有很多种：咱们重点描述前五种：

1、数据的增加（Inserting Data）: 使用 INSERT 语句向表中添加新的数据行。

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

2、表的增加（Creating Tables）: 使用 CREATE TABLE 语句创建一个新表。
简写了，其实里边还有很多属性设置

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

3、索引的增加（Creating Indexes）: 使用 CREATE INDEX 语句在表的一个或多个列上创建索引，以提高查询效率。

CREATE INDEX index_name ON table_name (column1, column2);

4、新列的增加（Adding Columns）: 使用 ALTER TABLE 语句为现有表添加新列。

ALTER TABLE table_name ADD column_name datatype;

5、数据库的增加（Creating Databases）: 使用 CREATE DATABASE 语句创建一个新的数据库。

CREATE DATABASE database_name;

6、用户的增加（Creating Users）: 通过 CREATE USER 语句创建新的数据库用户，以管理不同用户的权限。

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

等等，太多了，常用的仅仅这些

改：

1、数据（Rows）的删除:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

2、表（Tables）的修改：
修改表通常指的是更改表的结构，如添加或删除列、修改列类型等。使用 ALTER TABLE 语句。

ALTER TABLE table_name ADD column_name datatype;    添加
ALTER TABLE table_name DROP COLUMN column_name;     删除
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;  
修改类型

3、索引（Indexes）的修改:
修改: MySQL不支持直接修改一个已经存在的索引，需要先删除旧索引然后创建新索引。
删除: 使用 DROP INDEX 语句删除索引。

DROP INDEX index_name ON table_name;

4、列（Columns）的修改:
修改: 使用 ALTER TABLE 语句修改列的定义。

ALTER TABLE table_name CHANGE old_column_name new_column_name new_datatype;

5、数据库（Databases）的修改:
修改: 不能直接修改数据库名，但可以对数据库的默认字符集进行修改。

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

6、用户（Users）的修改:
修改用户通常指的是更改用户的权限或密码。使用 GRANT 来更改权限和 ALTER USER 修改密码。

GRANT SELECT ON database_name.table_name TO 'username'@'host';
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

删：

1、数据（Rows）的修改:

DELETE FROM table_name WHERE condition;

2、表（Tables）的删除:
删除: 使用 DROP TABLE 语句删除整个表及其数据。

DROP TABLE table_name;

3、索引（Indexes）的删除:

DROP INDEX index_name ON table_name;

4、列（Columns）的删除:
删除: 使用 ALTER TABLE 语句从表中删除列。

ALTER TABLE table_name DROP COLUMN column_name;

5、数据库（Databases）的删除:
删除: 使用 DROP DATABASE 语句删除数据库。

DROP DATABASE database_name;

6、用户（Users）的删除:
删除: 使用 DROP USER 语句删除用户。

DROP USER 'username'@'host';

请注意，所有的DELETE和DROP操作都是不可逆的，
这意味着一旦执行这些操作，原数据将会被永久删除。
在执行这些操作之前，建议您备份相关数据以避免不必要的数据丢失。
而UPDATE和ALTER操作可逆，但也应当谨慎使用，并且确保在操作前有对数据进行备份，以便在必要时进行恢复。
结合Java开发时，这些SQL语句通常会被用在JDBC的Statement或PreparedStatement对象中执行。

查很重要-先聊索引

索引：

总结

主要的目的是为了：
1、提升查询效率：这是索引最直接和最主要的目的。
2、部分情况下减少回表：这是覆盖索引能够带来的利益之一。通过直接从索引中获取所需数据，避免了访问数据表
3、提升排序效率：拥有合适的索引会使得排序过程更加高效，尤其是在ORDER BY查询中
4、在部分情况下实现锁：通过使用索引，MySQL可以仅锁定必要的行

缺点：
不宜过多，否则造成数据量过大：
总结：
其实这里也体现了程序的一个固有特点：权衡利弊
创建的目的是为了查询快，但是那是因为创建的时候已经把这个时间花费掉了，这里就需要去判断：这个表中的数据是查询频率高还是增加评率高了，再去决定构建索引的数量和索引的类型以及大小
其实和生活一样，
哪有什么岁月静好，只不过是有人在替你负重前行
所看到的美好，背后都是经历很多辛苦才获得的，前提是咱们就是没有背景的普通人，送一句话：人生一世，如白驹过隙、忽然而已。

系统学习：B+Tree

B+树是一种平衡树数据结构，它主要用于数据库和操作系统中实现索引，特别是在磁盘驱动的存储中。
MySQL的InnoDB存储引擎就大量使用了B+树索引。让我们一步步地了解B+树。

B+树的基本特征有：

1、多层结构：
B+树通常有多个层级，包括一个根节点，多个内部节点和许多叶子节点。像一棵倒挂的树，根节点在顶部，叶子节点在底部。

2、平衡： B+树总是保持平衡，即根节点到每个叶子节点的路径长度都一样，这样就可以保证无论数据量有多大，查找操作的时间复杂度始终是对数级的。

3、分支因子：每个节点（除了叶子和根节点）都有许多子节点。每个内部节点的子节点数量通常是预定义的范围内，这个范围由最小分支因子和最大分支因子决定。

4、排序：所有的叶子节点按照键值排序，并且在叶子节点层实际存储数据（或者数据的指针）。此外，叶子节点之间是按照键值的顺序相互链接的，这样可以使得范围查找非常效率。

5、键与值的分离：对于B+树来说，只有叶子节点存储了真正的数据（键与对应的记录或指向记录的指针）。内部节点仅存储键（作为索引的标识以及用于导航的分界点）。这也是和B树的一个很大的区别

B+树与B树的区别主要在于：

B+树的所有叶子节点包含所有索引键及指针，而且它们以链表的方式连接，这一点使得B+树特别适合于做范围查询。

B树节点存储键和数据，而B+树的内部节点不存储数据，仅存储键，这样B+树的内部节点可以有更高的分支因子，使得树的高度更低，盘访问次数更少。

B+树索引的操作包括：

1、查找：通过比较键值和内部节点的键，循序渐进地从根节点到叶子节点的过程中，逐渐缩小查找范围。

2、插入：当要插入新的键值时，先找到合适的叶子节点，并将其插入。如果叶子节点满了，它会分裂并可能递归地导致内部节点分裂，如有必要，树会增加新的层级。

3、删除：删除操作首先定位到叶子节点上的键，并将其移除。如果节点的填充度低于最小要求，可能会进行节点的合并或者重分布键的操作。

对于数据库来说，B+树索引的优点很多：
1、它可以加速数据的读操作（特别是范围查询），而且因为结构是平衡的，所以每次搜索的性能都是可预知的。
2、同时，存储了实际数据或指向数据的指针的叶子节点是他们彼此物理上连接的，从一个节点到另一个节点的操作很快，这使得B+树特别适合数据库系统。

结构示例
在这里插入图片描述
在这个示例中：
P、C-F、G-K 和 L-P 是索引键，在内部节点中。
[A-B-C]、[D-E-F]、[G-H-I]、[J-K-L]、[M-N-O] 和 [P-Q-R] 代表叶子节点中实际存储的数据范围。
叶子节点中的键是实际的索引条目，并且存储或指向了实际的行数据。
在一个B+树中：
所有数据条目都存储在叶子节点中。
内部节点（非叶子节点）仅存储键作为“指示器”来引导搜索查询向正确的叶子节点方向移动。
叶子节点是双向链表，代表键值是全局有序的，这使得范围查询相当高效，因为可以从范围的最低值开始，并遵循链表来快速访问所有连续的条目，直到范围的最高值。
“页”或“节点”的大小通常与磁盘的物理块大小相匹配，以最小化磁盘I/O操作。
这样的文本表示能够给出一个B+树的基本概念，尽管不能展示出树的所有属性，例如节点的确切子节点数量、数据指针和叶子节点间的链表连接等。在实际的数据库系统中，这些结构会更加复杂，并且通常会有图形化的工具来帮助可视化这些数据结构。

分析工具：

使用：

explain select * from 表面 where 条件

字段含义

id:

查询的唯一标识符，序号越小的执行阶段越早。如果两个执行计划的 id 相同，则它们属于同一个查询的不同部分（比如联合查询）。

select_type:

查询的类型，比如 SIMPLE（简单SELECT，不使用UNION或子查询等）、PRIMARY（主查询，即外层查询）、SUBQUERY（子查询中的第一个SELECT）、UNION（UNION中的第二个或后面的SELECT语句）等。

table:

显示这一行的数据是关于哪张表的。它显示的是表的真实名称或表的别名。

partitions:

显示查询将会涉及表的哪些分区。如果表不是分区表，该列为 NULL。

type:

访问类型，表示 MySQL 如何查找表中的行，通常的值有 ALL（全表扫描）、index（全索引扫描）、range（范围）、ref（非唯一索引扫描或唯一性索引的前缀扫描）、eq_ref（唯一索引扫描）、const（通过索引一次找到）、system（表中只有一行）等。

possible_keys:

显示 MySQL 能使用哪些索引来优化查询。

key:

实际使用的索引。如果没有使用索引，则为 NULL。

key_len:

索引中使用的字节数。可以给出部分索引使用情况。

ref:

显示索引的哪一列被使用了，它显示的是哪个字段或常数被用于和 key 一起从表中选择行。

rows:

MySQL 认为它执行查询时必须检查的行数。这是一个估计值。

filtered:

表示返回结果的行占需要读到的行(rows的值)的百分比，这个值是一个估算值。

Extra: 重点

包含不适合在其他列中显示但非常重要的额外信息，比如“Using where”表示MySQL服务层将在存储引擎返回结果后再进行过滤，“Using index”表示相应操作中使用了覆盖索引，避免了访问表的数据行。

Extra内部提示：

Using filesort:
表示 MySQL 会对结果使用额外的排序步骤，这通常在没有合适索引来支持 ORDER BY 时发生。
优化: 考虑添加或调整索引来满足排序需求。

Using temporary:
表示 MySQL 创建一个临时表来处理查询，这通常是 GROUP BY 和 ORDER BY 子句的结果。
优化: 优化索引来满足分组和排序条件，以减少使用临时表的需求。

Using where:
通常表示 MySQL 在存储引擎层对索引检索到的行进行了额外的过滤。
优化: 索引当前的质量可能不够好，可以考虑优化索引。

Range checked for each record (index map: N):
表示对于每条记录，MySQL 都需要检查是否有索引可以用于优化查询。
优化: 可能需要添加或重新定义索引以避免这种情况。
通常不需要（或不一定需要）优化索引的提示：

Using index:
查询已经有效地使用了索引，一般不需要进行优化。

Using join buffer:
这与连接操作有关，可能需要调优连接操作或者增加 join_buffer_size 值，但不一定涉及索引优化。

Impossible WHERE noticed after reading const tables:
这表示查询会因为 WHERE 子句的不可能条件而提前结束，并不涉及索引优化。

Using index condition:
这表明已经使用了索引条件下推优化。

Using MRR:
这表明 MySQL 已使用多范围读取优化查询。

Using index for group-by:
表明已有效利用索引进行了 GROUP BY 操作。

Using where with pushed condition:
这表明服务层的部分条件已被推送至存储引擎层，不一定涉及索引优化。

Using sort_union(…), Using union(…), Using intersect(…):、
这些值通常与索引合并策略有关。它们显示了 MySQL 如何合并多个索引来执行查询，但是否需要优化索引取决于特定情况。
请注意，虽然某些 Extra 值可能暗示着查询已经很好地使用了索引，但总体查询性能还是需要综合考虑多方面的因素，比如表的大小、数据的分布、查询条件的复杂度等。即使在“不一定需要优化索引”的情况下，也可能存在其他的优化机会。

这些信息可以帮助开发者了解 SQL 执行过程的细节，从而优化查询语句和表结构设计。

归类

列的多少

单列索引：

使用场景：当你的查询条件只包含一个列时，适合使用单列索引。它可以加速那些匹配单个字段的查询，或者排序和分组操作。
构建方式：
sql

-- 创建单列索引
CREATE INDEX index_name ON table_name (column_name);
-- 例如，为用户表的 email 字段创建索引
CREATE INDEX idx_email ON users (email);

多列索引（组合索引）：

使用场景：多列索引适用于查询条件包含多个列的情况。需要注意的是，组合索引按列的先后顺序存储数据，因此当查询条件涉及索引的前缀列（也就是在组合索引中排在前面的列）时，这个索引最为有效。
构建方式：

-- 创建多列索引
CREATE INDEX index_name ON table_name (column1, column2, ..., columnN);
-- 例如，为订单表的客户ID和创建时间创建组合索引
CREATE INDEX idx_customerid_created ON orders (customer_id, created_at);