IT评测·应用市场-qidao123.com技术社区

标题: mysql底子知识 [打印本页]

---

作者: 千千梦丶琪    时间: 2024-11-13 18:16
标题: mysql底子知识
sql语句类型及用途：

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

事务

ACID特性简介

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

事务隔离级别

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

脏读： 一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操纵，那么 A 读取到的数据是脏数据。
不可重复读： 一个事务中两次读取的数据的内容不同等。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，效果 不同等。
幻读： 一个事务中两次读取的数据量不同等。 体系管理员 A 将数据库中全部门生的成绩从具体分数改为 ABCDE 等级，但是体系管理员 B 就在这个时候插入了一条具体分数的记录，当体系管理员 A 改结束后发现另有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
不可重复读的和幻读很轻易肴杂，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的题目只需锁住满意条件的行，解决幻读需要锁表。
存储引擎MyISAM 与 InnoDB 如何选择？

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

索引

索引类型

• Normal（平凡索引）：
  
  
  
  • 平凡索引是最常见的索引类型。
    
  • 它用于加快查询操纵，提高查询性能。
    
  • 允许重复值，可以在一个字段上创建多个平凡索引。
    
  
  
• Unique（唯一索引）：
  
  
  
  • 唯一索引要求索引列的值是唯一的，不允许重复值。
    
  • 它用于包管数据的唯一性束缚。
    
  • 唯一索引可以加快唯一性检查和查找操纵。
    
  
  
• Spatial（空间索引）：
  
  
  
  • 空间索引用于处理空间数据，如地理位置信息、二维图形等。
    
  • 它支持空间范围查询和最近邻查询等操纵。
    
  • 空间索引利用特定的空间算法来加快空间数据的检索。
    
  
  
• 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是数据库管理体系

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)

Powered by Discuz! X3.4