MySQL基础知识

sql语句类型及用途：

  1. Ddl：建表 改表 删表 删数据【保留表结构】
  2. Dql：查询 where, group by, order by, join
  3. Dml：新增 修改 删除数据
  4. Dcl：新增 删除用户对数据库权限

事务

ACID特性简介

  1. 原子性（Atomicity）
    事务被视为一个不可分割的最小单位，它要么完全执行，要么完全不执行。
  2. 一致性（Consistency）
    一致性保证了事务的执行将数据库从一个一致的状态转变到另一个一致的状态。
  3. 隔离性（Isolation）
    隔离性是指当多个事务同时对数据库进行操作时，每个事务都是独立的，一个事务的操作不会影响到其他事务。
  4. 持久性（Durability）
    持久性意味着一旦事务被提交，它对数据库的修改就是永久性的，即使系统发生故障也不会丢失。

事务隔离级别

隔离级别 脏读 不可重复读 幻读
读未提交(Read uncommitted) × × ×
读已提交(Read committed) √ × ×
读未提交(Read uncommitted)（默认隔离级别） √ √ ×
读未提交(Read uncommitted) √ √ √

什么是脏读、幻读和不可重复读？

脏读： 一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

不可重复读： 一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

幻读： 一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

存储引擎MyISAM 与 InnoDB 如何选择？

  1. InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
  2. MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
  3. InnoDB支持外键，MyISAM不支持
  4. MySQL 在 5.1 之前版本默认存储引擎是 MyISAM，5.1 之后版本默认存储引擎是 InnoDB
  5. InnoDB不支持FULLTEXT类型的索引
  6. InnoDB中不保存表的行数，如select count() from
    table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(    )语句包含where条件时MyISAM也需要扫描整个表
  7. 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
  8. 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
  9. InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like
    '%lee%'

索引

索引类型

  1. Normal（普通索引）：
    • 普通索引是最常见的索引类型。
    • 它用于加速查询操作，提高查询性能。
    • 允许重复值，可以在一个字段上创建多个普通索引。
  2. Unique（唯一索引）：
    • 唯一索引要求索引列的值是唯一的，不允许重复值。
    • 它用于保证数据的唯一性约束。
    • 唯一索引可以加速唯一性检查和查找操作。
  3. Spatial（空间索引）：
    • 空间索引用于处理空间数据，如地理位置信息、二维图形等。
    • 它支持空间范围查询和最近邻查询等操作。
    • 空间索引使用特定的空间算法来加速空间数据的检索。
  4. Fulltext（全文索引）：
    • 全文索引用于支持全文搜索功能。
    • 它可以在文本数据中快速搜索关键词，支持模糊搜索、分词和关键词匹配等操作。
    • 全文索引适用于文本处理和搜索引擎等应用场景。

索引方法

B树索引（B-Tree Index）：

  • B树索引是一种常见的平衡树结构索引，适用于范围查询和精确查找。
  • B树索引适用于传统的关系型数据库系统，如MySQL、Oracle等。
  • B树索引的特点是支持高效的插入和删除操作，能够自动平衡和调整树结构。

哈希索引（Hash Index）：

  • 哈希索引使用哈希表的结构来快速查找数据，适用于等值查找。
  • 哈希索引通常用于内存数据库或具有特定查询模式的场景。
  • 哈希索引的特点是查找速度快，但不支持范围查询和排序操作。

索引失效情况

  • 查询类型错误：当查询条件中的数据类型与索引列的数据类型不一致时，可能会发生隐式转换，导致索引失效 。

  • 索引列参与运算：如果索引列参与了计算或函数操作，如height + 1 > 189，索引将无法被使用。

  • 错误使用通配符：在使用LIKE语句时，如果通配符%位于字符串的开头，如%keyword，索引将不会被使用 。

  • OR连接无索引字段：当使用OR连接多个条件，且其中一个条件的列没有索引时，索引可能会失效 。

  • 未用到覆盖索引：如果查询中没有使用覆盖索引，即查询的列不完全包含在索引中，索引将不会被使用 。

  • 联合索引失效：在使用联合索引时，如果查询条件没有包含索引的最左列，索引将不会生效 。

  • 索引列参数使用函数：当索引列作为函数的参数时，可能会使索引失效 。

  • 类型隐式转换：如果参数类型与索引列的类型不匹配，可能会导致类型隐式转换，从而使得索引失效 。

  • 比较两个列：在查询条件中比较两个列的值，如id > age，可能会导致索引失效 。

  • 使用IS NOT NULL：使用IS NOT NULL作为查询条件可能会导致索引失效 。

  • 使用NOT IN和NOT EXISTS：这些条件可能会导致索引失效，尤其是当子查询返回大量行时。

  • ORDER BY导致索引失效：在某些情况下，使用ORDER BY和LIMIT可能会导致索引失效，尤其是当排序列不是索引的一部分时 。

  • 索引失效的其他原因：包括MySQL优化器的决策，如在某些情况下，优化器可能认为全表扫描比使用索引更快，因此放弃使用索引 。

索引优化策略

  • 识别高频查询：首先识别频繁执行且消耗大量资源的查询。这些查询通常涉及大量数据的过滤、排序或连接。通过针对这些查询进行优化，可以实现显著的性能提升 。

  • 选择合适的索引列：根据查询中的WHERE、ORDER BY和JOIN子句使用情况选择索引列。经常用于搜索条件或排序的列应优先考虑索引，以加速数据检索 。

  • 避免过度索引：虽然索引可以提高查询性能，但过度索引会消耗存储空间，并增加数据修改操作的开销。因此，避免在很少用于查询或选择性低的列上创建索引 。

  • 利用复合索引：复合索引（多列索引）在涉及多个过滤条件或排序标准的查询中特别有用。通过在查询中经常一起使用的列上创建复合索引，可以优化查询性能并减少索引开销 。

  • 监控索引使用和性能：定期使用MySQL内置的监控工具（如性能模式和慢查询日志）监控索引的使用和性能。识别利用率低的索引、未使用的索引以及导致性能瓶颈的索引，并相应调整索引配置以优化查询性能 。

  • 考虑索引数据类型：为索引列选择合适的数据类型，以最小化索引大小并优化查询性能。对于数值列使用整数数据类型，对于可变长度字符串使用VARCHAR数据类型，对于日期和时间列使用DATE或DATETIME数据类型。除非必要，否则避免对大型文本或二进制数据类型的列进行索引 。

  • 定期分析和优化索引：使用MySQL的ANALYZE TABLE和OPTIMIZE TABLE命令定期分析索引的使用情况、碎片化和效率。这些命令有助于识别和解决索引碎片化、过时统计信息和低效索引结构的问题，从而优化查询性能并维护数据库健康 。

  • 使用覆盖索引：覆盖索引是一种特殊类型的索引，它包含了查询所需的所有列，从而避免了访问实际表的需要。这可以显著减少磁盘I/O并提高查询执行时间 。

  • 考虑使用部分索引：部分索引用于索引表中的特定行子集，从而减少索引大小并提高特定数据子集的查询性能 。

  • 监控和维护索引：定期检查索引碎片并重新组织或重建索引，以帮助减少磁盘I/O并提高查询性能 。

索引设计原则

视图

定义：

视图是在数据库中定义的虚拟表。它是一个基于一个或多个实际表的查询结果集，可以像实际表一样被查询和操作。视图可以看作是一个动态生成的数据表，其内容是从其他表中选择、过滤和计算得到的。

用途:

  • B树索引是一种常见的平衡树结构索引，适用于范围查询和精确查找。
  • B树索引适用于传统的关系型数据库系统，如MySQL、Oracle等。
  • B树索引的特点是支持高效的插入和删除操作，能够自动平衡和调整树结构。

优点：

  • 数据的抽象和简化：视图是一个虚拟表，它可以根据特定的查询语句从一个或多个表中选择、过滤和计算数据。通过使用视图，可以将复杂的查询逻辑和多表连接操作封装为一个简化的视图查询，提供了更简洁、更易于理解的数据模型。
  • 数据安全性：视图可以限制用户对底层表的访问权限。通过给用户授予对视图的访问权限，可以隐藏底层表的结构和敏感数据，只允许用户在特定条件下查看和操作数据。这为数据库提供了更高的安全性和数据保护。
  • 逻辑数据分离和模块化：通过视图，可以将数据逻辑分离为不同的模块。这使得数据库的维护和管理更加灵活，可以根据需要对各个模块进行独立的修改和优化，而无需影响其他模块。
  • 提高查询性能：视图可以预先计算和存储查询结果，从而提高查询性能。当使用视图进行查询时，MySQL 可以利用预先计算的结果，而不需要重新执行复杂的查询操作。这对于频繁执行相同查询的场景非常有用。
  • 简化应用开发：通过将复杂的查询逻辑封装为视图，应用程序开发人员可以更快速、更轻松地构建应用程序。他们只需要简单地查询视图，而无需关心视图背后的复杂查询逻辑和表结构。

缺点：

  • 性能影响：视图查询可能在执行时产生额外的性能开销。因为视图是根据查询语句动态生成的，每次查询时都需要重新计算视图的结果。对于复杂的视图和大型数据集，这可能导致查询较慢，影响数据库性能。
  • 更新限制：默认情况下，MySQL 不允许对包含特定条件的视图进行更新操作。这些条件包括使用聚合函数、DISTINCT、GROUP BY 和 HAVING 等的视图。因此，如果你使用的视图有这些限制条件，你将无法对其进行直接的插入、更新或删除操作。
    数据一致性：视图查询的结果是根据底层表的数据动态生成的，而不是存储实际的数据副本。这意味着如果底层表的数据发生了变化，但视图查询结果没有及时更新，可能导致数据一致性的问题。
  • 限制和复杂性：视图的使用是受到一些限制的，特别是在涉及复杂的查询和多表连接时。一些复杂的查询逻辑和操作可能无法在视图中实现，这可能需要使用其他技术或重新设计查询。
  • 管理复杂性：随着数据库中视图的数量增加，管理和维护视图变得更加困难。复杂的视图层次结构和依赖关系可能会导致维护和调试问题的增加

约束

定义

约束是表级的强制规定。可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束

用途

防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制

  • 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
  • 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
  • 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
  • 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

存储过程/函数

定义

存储过程：一组预编译的 SQL 语句，用于执行复杂的操作。它可以接收输入参数，并返回输出参数或结果集。
函数：一种特殊类型的存储过程，它只返回一个标量值（单个数据值）。

主要区别

  • 返回值：
    存储过程可以返回多个值（通过输出参数）或结果集。
    函数只能返回单个标量值。
  • 事务控制：
    存储过程通常在事务中执行，这意味要么所有语句都成功执行，要么整个事务回滚。
    函数可以在不使用事务的情况下执行，称为不确定性函数。
  • 副作用：
    存储过程通常具有副作用，例如更新或插入数据。
    函数通常没有副作用，并且只返回一个值。
  • 可重用性：
    存储过程和函数都可以在多次查询中重用。
    不过，函数通常比存储过程更易于重用，因为它们只返回一个值。
  • 性能：
    存储过程通常比函数性能更好，因为它们是预编译的。
    但是，如果函数只执行简单的计算，则它可能比存储过程更快。
    选择标准：
    如果需要执行复杂的操作，具有副作用并且需要返回多个值，则使用存储过程。
    如果只需要返回单个标量值并且没有副作用，则使用函数。

水平分区和垂直分区

水平分区是把数据分散到多个表中，表的字段都一致，数据不同
例如：可以根据时间范围（如每年的数据）或地理区域（如每个城市的数据）来分区。水平分区的常见模式包括：

  • Range（范围）：根据某个列的值的范围来分区，如按照年份划分数据。
  • Hash（哈希）：通过对表的一个或多个列的哈希值进行计算，将数据均匀分布到不同的分区中。
  • Key（键值）：类似于哈希分区，但使用的是MySQL系统产生的哈希键。
  • List（预定义列表）：通过DBA定义的列表的值所对应的行数据进行分割，适用于离散值的列。
  • Composite（复合模式）：结合以上模式，例如在范围分区的基础上再进行哈希分区。

垂直分区是把一张表拆分成多个表，表的字段不一致，数据一致
垂直分区是根据列的属性来分割表。这种分区模式下，每个分区包含的是表中的不同列，而行数保持不变。通常，垂直分区将频繁访问的列和不常访问的列分开，以优化查询性能和减少读取磁盘的数据量。
例如：可以将一个包含大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就可以把这些不经常使用的text和BLOB列划分到另一个分区。

分区的优势和限制
优势：提高查询性能，减少需要搜索的数据量；数据管理更加灵活，可以独立地备份、恢复或优化某个分区；提高数据备份/恢复等操作的效率。
限制：如果不当使用，可能会对性能产生负面影响；需要更多的内存和磁盘空间来维护索引、统计信息和其他元数据；对于跨分区的查询，可能需要额外的连接操作，这可能会导致性能问题。

mysql 8.0以上版本没找到data文件的问题

如果不在C:\Program Files目录下，那么在C:\ProgramData\MySQL\MySQL Server 8.0目录下可以找到data目录和ini配置文件

mysql 8.0版本因为密码插件问题无法登录


如果因为密码插件问题无法登录，可以尝试在命令行输入以下语句
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
将mysql的密码插件从caching_sha2_password修改为mysql_native_password

数据库系统和数据库管理系统的关系、区别？

数据库系统是一个包含硬件、软件和数据的完整体系，而数据库管理系统是这个体系中负责数据操作和管理的软件部分。
navicat是数据库管理系统人机交互界面，不是数据库管理系统
mysql是数据库管理系统

posted @ 2024-11-13 15:12  恒辉信达  阅读(35)  评论(1)  编辑  收藏  举报