MySQL-知识点详解

本文简要介绍了MySQL数据库的关键内容，包括数据类型、SQL语句、索引类型以及数据库优化等方面。详细讨论了各种数据类型，比较了DATETIME和TIMESTAMP类型，解释了SQL语句的执行顺序和数据库连接方式，介绍了MySQL索引的概念和优缺点，最后简要介绍了聚簇索引和非聚簇索引的区别。

什么是MySQL数据库

  MySQL是一个流行的开源关系型数据库管理系统，广泛用于Web应用程序开发和数据存储

MySQL - 数据类型

  MySQL支持多种数据类型，用于存储不同类型的数据。以下是MySQL中常见的数据类型及其用途：

1、整数类型

• TINYINT：1字节
• SMALLINT：2字节
• MEDIUMINT：3字节
• INT：4字节
• BIGINT：8字节

2、浮点数类型

• FLOAT(M, D)：单精度浮点数，占4字节，M表示总位数，D表示小数位数。
• DOUBLE(M, D)：双精度浮点数，占8字节，M表示总位数，D表示小数位数。
• DECIMAL(M, D)：精确小数，M表示总位数，D表示小数位数。

3、定点数类型

• DECIMAL(M, D)：同浮点数类型，但精确存储

4、字符串类型

• CHAR(N)：固定长度字符串，最大255个字符。
• VARCHAR(N)：可变长度字符串，最大65535个字符。
• TEXT：可变长度文本，最大65535个字符。
• BLOB：二进制数据，最大65535个字节。
• ENUM('value1', 'value2', ...)：枚举类型，存储特定值之一。
• SET('value1', 'value2', ...)：集合类型，存储多个特定值之一。

5、日期和时间类型（Date and Time Types）：

• DATE：日期，格式’YYYY-MM-DD’。
• TIME：时间，格式’HH:MM:SS’。
• DATETIME：日期时间，格式’YYYY-MM-DD HH:MM:SS’。
• TIMESTAMP：时间戳，与时区相关。
• YEAR：年份，格式’YYYY’。

6、其他类型：

• BOOL、BOOLEAN：布尔类型，存储true或false。
• JSON：存储JSON格式数据。

DATETIME 和 TIMESTAMP区别

DATETIME

  能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

  DATETIME以’YYYY-MM-DD HH:MM:SS’的格式存储日期时间，不受时区影响。

  DATETIME存储的时间不会受到时区的影响，存储和检索的时间都是直接的输入值。

TIMESTAMP

  和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年。

  TIMESTAMP以’YYYY-MM-DD HH:MM:SS’的格式存储日期时间，但在存储和检索时会自动转换为UTC时间，然后在显示时转换为当前时区的时间。

  TIMESTAMP在插入或更新记录时如果未提供值，会自动设置为当前时间。

  一般来说，如果需要存储历史事件、日志记录等不受时区影响的时间数据，可以选择DATETIME；而如果需要存储与时区相关的时间数据，并且自动管理时间更新，可以选择TIMESTAMP。

VARCHAR 和 CHAR对比分析

属性	VARCHAR	CHAR
存储方式	可变长度字符串，最大长度为指定的字符数	固定长度字符串，长度为指定的字符数
存储空间	实际占用的存储空间取决于存储的数据长度	固定占用指定长度的存储空间
适用场景	数据长度不固定或变化较大，节省存储空间	数据长度固定，对性能要求较高（如索引列）
存储方式	长度不受限制，但实际存储长度受存储数据长度影响	长度固定，不受存储数据长度影响
索引性能	对于长文本或变长字段，索引性能可能略逊于CHAR类型	由于固定长度，CHAR类型在索引性能上通常更优秀
内存使用	长度可变，可能导致内存碎片化	长度固定，内存使用更加稳定
速度	读取速度可能稍慢，但节省存储空间	读取速度较快，但占用存储空间可能较多

总结归纳：
  VARCHAR适用于存储长度不固定或变化较大的数据，可以节省存储空间并允许更大的存储长度。而CHAR适用于长度固定的数据，对索引性能要求较高或需要稳定的内存使用。

BLOB 和 TEXT 对比分析

属性	BLOB	TEXT
存储方式	二进制数据，用于存储大型二进制对象（如图像、音频）	文本数据，用于存储大型文本对象（如文章、日志）
存储空间	实际占用的存储空间取决于存储的数据长度	实际占用的存储空间取决于存储的数据长度
最大长度	最大长度受数据库配置或数据类型限制，通常较大	最大长度受数据库配置或数据类型限制，通常较大
适用场景	存储二进制数据或非结构化数据	存储文本数据或结构化文本数据
索引性能	不适合作为索引字段	不适合作为索引字段
读取速度	读取速度较慢，适合存储大型二进制数据	读取速度较快，适合存储大型文本数据
查询性能	查询性能相对较差，特别是在大数据量下	查询性能较好，特别是对于文本搜索或分析等操作
使用场景	适用于存储图片、音频、视频等二进制数据	适用于存储文章、日志、配置文件等文本数据

总结归纳：
  BLOB适用于存储二进制数据，如图片、音频、视频等，而TEXT适用于存储文本数据，如文章、日志等。

SQL语句执行顺序

1. FROM子句：查询指定的表，并获取需要查询的数据。
2. JOIN子句：如果使用了JOIN子句，会将数据连接起来。
3. WHERE子句：筛选满足条件的数据。
4. GROUP BY子句：将数据分组。
5. HAVING子句：筛选分组后满足条件的数据。
6. SELECT子句：选取需要查询的列。
7. DISTINCT关键字：去重，返回不重复的记录。
8. UNION关键字：将多个查询结果集合并成一个。
9. ORDER BY子句：按指定的列对结果进行排序。
10. LIMIT子句：限制返回的行数。

(8) SELECT (9) DISTINCT 
(1) FROM 
(3)  JOIN 
(2)    ON 
(4) WHERE 
(5) GROUP BY 
(6) WITH {CUBE|ROLLUP} 
(7) HAVING 
(10) ORDER BY 
(11) LIMIT 

先判断 表 ，然后看是否有 连表
然后 看where条件 ，看是否分组 、过滤
然后执行查询的 列 ，是否去重、是否限制

数据库查询的连接方式

数据库查询的连接方式主要包括三种：

1. 内连接（Inner Join）：只返回两个表中都有匹配数据的记录，即交集部分。
2. 左连接（Left Join）：返回左表中所有记录以及右表中匹配的记录，如果右表中没有匹配的记录，补充 null 值。
3. 右连接（Right Join）：返回右表中所有记录以及左表中匹配的记录，如果左表中没有匹配的记录，补充 null 值。

左连接

  Left join：即左连接，
  左连接是一种关联查询（Join）的方式，通常用于从两个表中获取数据，其中左表（Left Table）是主要的查询对象，而右表（Right Table）则根据指定的条件进行关联。在使用左连接时，左表中的所有记录都会被包含在结果中，而右表中符合条件的记录会根据关联条件与左表的数据合并。如果右表中没有符合条件的记录，相关列则显示为NULL。

左连接的特点

1、以左表为基础：左连接是以左表为主体进行查询的。即使右表中没有匹配的记录，左表中的记录仍然会显示在结果中。

2、根据ON后的条件进行连接：左连接使用ON后指定的条件来连接左表和右表。这个条件决定了如何将两个表中的数据合并。

3、左表所有查询信息列出：左连接会将左表中的所有记录列出，无论是否与右表中的记录匹配。

4、右表只列出ON条件满足的部分：右表中只会列出满足ON条件的记录，如果没有匹配的记录，则相关列显示为NULL。

示例

select A.device_id,question_id,result 
from A  
left join B on A.device_id = B.device_id
where university = "浙江大学"

如果在表B中有与表A中设备ID匹配的记录，并且这些记录中university列的值为"浙江大学"，那么这些记录会与表A中的对应行一起显示出来。如果某些设备ID在表B中没有匹配到对应的记录，那么对应的B表列（例如B中的device_id、university等）会显示为NULL。

右连接

  右连接（Right Join）和左连接（Left Join）相对应，右连接的特点和操作步骤如下：
1、以右表为基础：右连接是以右表为主要查询对象，即右表中的所有记录都会包含在结果中，而左表中只列出与右表满足连接条件的部分。
2、根据 ON 后的条件进行连接：右连接使用 ON 后给定的连接条件将右表和左表连接起来。
3、右表所有查询信息列出：右连接会将右表中的所有记录列出，无论是否与左表中的记录匹配。
4、左表只列出 ON 后条件满足的部分：左表中只会列出满足 ON 后条件的记录，如果没有匹配的记录，则相关列显示为 NULL。

代码示例

假设有两个表 users 和 orders，它们的结构如下：

表 users 包含以下列：user_id、username、email
表 orders 包含以下列：order_id、user_id、product_id、quantity
现在我们要对这两个表进行右连接，以获取所有订单信息以及对应的用户信息（如果有的话）：

SELECT *
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;

这个查询将会返回一个结果集，其中包括了所有订单信息以及对应的用户信息（如果有）。如果某个订单没有对应的用户信息（即user_id为 NULL），则对应的用户信息列（例如 username、email）将显示为 NULL。右表 orders 中的所有记录都会被包含在结果中，而左表 users 中与右表满足连接条件的部分记录也会一起显示。

内连接

  内连接（Inner Join）用于将两个表中满足连接条件的数据合并在一起。
内连接的特点和操作步骤如下：

1、根据 ON 后的条件进行连接：内连接使用 ON 后给定的连接条件将两个表（左表和右表）中满足条件的数据连接起来。
2、结果只包括满足连接条件的数据：内连接只会将左表和右表中满足连接条件的数据合并在一起，并将其他不满足条件的数据过滤掉。
3、结果集中只包含匹配的行：内连接的结果集中只包含满足连接条件的行，即左表和右表中连接字段的值相匹配的行。

SQL语法：

SELECT *
FROM 左表名称
INNER JOIN 右表名称 ON 左表条件 = 右表条件;

代码示例

假设有两个表 users 和 orders，它们的结构如下：

表 users 包含以下列：user_id、username、email
表 orders 包含以下列：order_id、user_id、product_id、quantity
现在我们要对这两个表进行内连接，以获取所有用户的订单信息

SELECT *
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

这个查询将会返回一个结果集，其中包括了所有用户的订单信息（如果有的话）。只有满足连接条件（users.user_id = orders.user_id）的用户和对应的订单数据才会被包含在结果中。如果某个用户没有订单信息或某个订单没有对应的用户信息，那么这些不匹配的数据将不会出现在结果中。

MySql 索引

什么是索引

  MySQL索引是一种数据结构，用于加快数据库表的数据检索速度。它们是数据库性能优化的关键工具之一，可以有效地提高查询效率。

索引的特点及作用

1、加速数据检索：索引可以快速定位到表中满足特定条件的行，从而加速数据检索的速度。
2、提高查询性能：对经常用于查询的列创建索引可以大幅提高查询性能，特别是在大型表中。
3、支持唯一性约束：索引可以用来确保某些列的唯一性，例如使用唯一索引来保证某个列不重复。
4、优化排序和分组操作：对于排序和分组等操作，索引可以加速数据的排序和分组过程。

索引的常见类型

1、普通索引：最基本的索引类型，没有特殊约束，可以加速数据检索。
2、唯一索引：保证索引列的值唯一，用于确保数据的唯一性。
3、主键索引：一种特殊的唯一索引，用于标识表中的每一行数据，通常与主键约束一起使用。
4、全文索引：用于全文搜索，支持对文本内容进行高效的搜索操作。
5、复合索引：针对多个列组合而成的索引，可以加速涉及到复合索引中所有列的查询。

索引的创建

CREATE INDEX index_name ON table_name (column_name);

index_name 是索引的名称，table_name 是要创建索引的表名，column_name 是要创建索引的列名。

索引的优缺点

优点：

1. 加速数据检索，提高查询性能。
2. 支持唯一性约束，确保数据的唯一性。
3. 优化排序和分组操作，加速数据处理。
4. 加速连接操作，减少查询执行时间。

缺点

1. 创建索引和维护索引需要耗费很多时间。
2. 对表中的数据进行增删改时，如果数据有索引，索引也会动态的修改，会降低SQL执行的效率。
3. 索引需要使用物理文件存储，占用空间。

使用索引一定能提高查询性能吗

大多数情况下，索引查询都是比全表扫描要快的。
但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

如何合理使用索引

1、选择合适的索引列：

• 对经常用于查询条件的列创建索引，例如经常出现在 WHERE、JOIN、ORDER BY、GROUP BY 子句中的列。
• 对于频繁用于过滤数据的列、用于排序或分组的列、外键列等都是合适的索引选择。

2、使用复合索引：

• 对于经常同时使用多个列进行查询的情况，可以考虑创建复合索引。
• 在创建复合索引时，将经常共同出现在查询条件中的列放在索引的前面，可以提高查询效率。

3、避免过多索引：

• 避免过度创建索引，过多的索引会增加写操作成本，降低写操作性能。
• 对于少访问的列或者很少用于查询条件的列，不需要创建索引。

4、定期维护和优化索引：

• 定期检查索引的使用情况，优化不必要或者不合适的索引。
• 对于长时间不使用或者变动频繁的索引，考虑删除或重新设计索引。

如何查看mysql中的索引

1、SHOW INDEXES FROM table_name;：这是一种常用的方法，可以查看指定表中的所有索引信息，包括索引名称、索引类型、索引字段等。

SHOW INDEXES FROM table_name;

2、DESCRIBE table_name;：使用DESCRIBE命令也可以查看表的结构信息，包括索引信息。

DESCRIBE table_name;

3、SHOW CREATE TABLE table_name;：这个命令可以显示创建表的SQL语句，其中包括了索引的创建语句，可以查看表的索引情况。

SHOW CREATE TABLE table_name;

怎么查看selcet语句是否用到索引？

1、使用EXPLAIN：

  在MySQL中，可以使用EXPLAIN关键字来分析SELECT语句的执行计划，其中包括了是否使用了索引以及使用的索引类型等信息。

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

  执行以上命令后，MySQL会返回一张执行计划表，其中的"key"列显示了使用的索引信息，如果出现"Using index"表示使用了索引，如果出现"Using where"表示使用了索引但仍需进一步过滤数据。

2、使用SHOW WARNINGS：

  在执行SELECT语句后，可以使用SHOW WARNINGS命令查看MySQL给出的警告信息，其中会显示是否使用了索引以及使用的索引类型。

SHOW WARNINGS;

3、查看慢查询日志：

  如果MySQL的慢查询日志已经启用，可以查看慢查询日志中关于该SELECT语句的执行信息，其中会显示是否使用了索引。

索引失效原因

  索引失效可能由多种原因引起，以下是一些常见的索引失效原因：

1、不符合索引最左前缀规则：

  MySQL使用最左前缀匹配索引的原则，如果查询条件不是索引的最左前缀，则索引可能会失效。
2、使用了不等于操作符（!=、<>）：

  在查询条件中使用不等于操作符会导致索引失效，因为MySQL无法使用范围查找。

3、数据量过小：

  对于数据量过小的表，MySQL可能选择全表扫描而不是使用索引。

4、表中有多个索引：

  如果有多个索引可以满足查询条件，MySQL可能会选择不同的索引导致索引失效。

5、查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;

6、以 % 开头的 LIKE 查询会导致索引失效，比如 like ‘%abc’;

最左 前缀/匹配 原则是什么

  最左前缀匹配原则是指在数据库查询中，如果有多个索引可以匹配查询条件，数据库系统会选择最左侧的索引开始匹配。这意味着数据库会尽可能使用最左侧的索引列来加速查询，而不是随意选择其他索引列。这个原则的好处在于可以最大程度地利用索引来提高查询效率，尤其是在复合索引的情况下，正确使用最左前缀匹配可以减少不必要的索引扫描，提高查询性能。

  例如，如果有一个复合索引包含了 (A, B, C) 三列，而查询条件只涉及到了 A 和 B 两列，那么最左前缀匹配原则会优先选择这个索引，而不会选择单独针对 A 或者 B 的索引，因为复合索引中的 (A, B) 子集是最左侧的前缀。

索引底层原理 (B+树)

  MySQL 使用B+树作为主要的索引结构。B树适用于范围查询和精确查找，每个节点可以存储多个关键字和对应的指针；而B+树则在B树的基础上做了优化，将关键字只存储在叶子节点，非叶子节点只存储索引信息，这样可以提高范围查询的性能。

B+树

  B+树是一种多路平衡查找树，每个节点可以存储多个关键字和对应的指针，相比于B树，B+树的节点更加宽度，可以容纳更多的关键字，减少了树的高度，降低了磁盘I/O次数。

  在B+树中，所有关键字都存储在叶子节点上，非叶子节点只存储索引信息，例如指向下一层节点的指针。这样的设计使得范围查询更加高效，因为只需遍历叶子节点就可以获取到范围内的所有数据。

  由于B+树的叶子节点形成了一个有序链表，所以支持范围查询和顺序访问的性能较好。在数据库中，当执行范围查询时，B+树可以快速定位到起始位置，并且顺序地获取所需数据。

  B+树的宽度较大，每个节点存储的关键字数量较多，这意味着相同数量的数据可以用更少的节点来表示，减少了磁盘I/O次数，提高了查询效率。

B树和B+树的区别？为什么MySQL使用B+树？

特点	B树	B+树
节点结构	包含关键字和对应数据	非叶子节点只包含关键字和指针
节点连接	通过指针连接	叶子节点通过链表连接
关键字数量	较少	较多（等于子节点数量）
适用范围	随机访问	范围查询和顺序访问
有序性能	较差	较好
磁盘I/O次数	较多	较少
覆盖索引	不支持	支持

  B+树在数据库索引中的设计更加适合范围查询、顺序访问和覆盖索引等常见数据库操作，因此MySQL选择B+树作为主要的索引结构，以提高查询性能和减少磁盘I/O次数。

B+树索引和 哈希索引 的区别

特点	B+树索引	哈希索引
数据结构	多路平衡查找树	哈希表
范围查询	适用范围查询和顺序访问	不适用范围查询和顺序访问
排序性能	有序性能好	无序性能好
磁盘I/O次数	较少	较少（在内存中直接查找）
索引维护	插入、删除操作较复杂	简单，但可能导致碰撞和冲突
内存占用	较高	较低
唯一性	支持唯一性约束	支持唯一性约束
适用场景	数据库索引、范围查询	精确查找、哈希表实现缓存等

聚簇索引和非聚簇索引

  聚簇索引和非聚簇索引是数据库中常见的索引类型，它们在存储方式、索引结构和查询性能等方面有一些显著的区别。

  聚簇索引适合于范围查询、顺序访问和覆盖索引的情况，而非聚簇索引适合于单条数据的查找和少量数据的范围查询，但在大范围查询和覆盖索引方面性能可能不如聚簇索引。

聚簇索引

• 存储方式： 将索引和数据存储在一起，即索引的叶子节点存储的是实际的数据行。
• 索引结构： 通常使用B+树或B树结构，但叶子节点存储的是数据行而不是指向数据行的指针。
• 查询性能： 对于范围查询、顺序访问和覆盖索引的性能通常较好，因为相关的数据行在物理上是相邻存储的。
• 适用场景： 适合经常需要范围查询、顺序访问或者覆盖索引的情况，如主键索引和唯一索引。

非聚簇索引

• 存储方式： 将索引和数据分开存储，即索引的叶子节点存储的是指向数据行的指针（或行ID）。
• 索引结构： 通常使用B+树或B树结构，但叶子节点存储的是指向数据行的指针，而非实际数据。
• 查询性能： 对于单条数据的查询性能较好，因为直接指向了数据行。但对于范围查询、顺序访问或者覆盖索引，可能需要进行多次IO操作来获取数据。
• 适用场景： 适合单条数据的查找和少量数据的范围查询，但对于大范围的查询或者需要覆盖索引的情况，性能可能不如聚簇索引。