MYSQL

Chris-myx

已于 2023-05-10 11:16:35 修改

阅读量93

点赞数

文章标签： mysql 数据库 java

于 2023-05-07 20:54:33 首次发布

本文链接：https://blog.csdn.net/weixin_44973527/article/details/130395425

版权

1. 事务的四大特性？

事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么全部成功，要不全部失败。

原子性：事务中的所有操作要么全部成功，要么全部失败回滚，保证数据库状态的一致性。

一致性：事务执行前后，数据库都必须处于一致状态。

隔离性：每个事务在执行期间，对其他事务都是隔离的，即每个事务执行时，访问的数据都是从未被其他事务修改过的。

持久性：一旦事务提交，它对数据库的修改就应该永久保存到数据库中，即使系统崩溃也不应该丢失。

通过使用事务，可以确保多个并发用户在访问数据库时不会相互干扰，从而保证数据库操作的正确性和可靠性。

2.并发事务的问题

在MySQL中，由于多个事务可能同时访问和修改同一个数据，因此并发事务问题可能会导致数据不一致和丢失。主要的并发事务问题包括：

脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据，当该事务回滚时，读取到的数据实际上是无效的。
不可重复读（Non-repeatable Read）：一个事务多次读取同一行数据，但在两次读取之间，另一个事务修改了该行数据，导致读取到的数据不一致。
幻读（Phantom Read）：一个事务多次执行相同的查询，但在两次查询之间，另一个事务插入了新的数据或删除了已有的数据，导致查询结果不一致。
更新丢失（Lost Update）：两个或多个事务同时修改同一行数据，导致其中一个事务的修改被另一个事务覆盖。

为了避免并发事务问题，可以使用以下技术：

加锁：通过加锁来控制并发访问，防止不同事务同时访问同一数据。
事务隔离级别：设置不同的事务隔离级别，可以控制不同事务之间的可见性和并发控制。
MVCC：通过多版本并发控制技术，在不锁定数据的情况下实现事务隔离。
乐观锁：通过版本号等方式实现乐观锁，让并发事务可以同时读取数据，但在更新时进行版本号检查，避免更新丢失问题。

3. 事务隔离级别有哪些？

读未提交（Read Uncommitted）：允许一个事务读取另一个事务未提交的数据，最低的隔离级别，可能导致脏读、不可重复读、幻读等问题。
读已提交（Read Committed）：一个事务只能读取另一个事务已提交的数据，避免了脏读问题，但不可重复读和幻读问题仍然可能发生。
可重复读（Repeatable Read）：在同一个事务内，多次读取同一数据时，得到的结果都相同，即使其他事务修改了该数据，也不会对当前事务可见。避免了脏读和不可重复读问题，但幻读问题仍可能发生。
串行化（Serializable）：最高的隔离级别，确保事务串行执行，避免了所有并发问题，但可能会导致性能下降。

4.常见的存储引擎有哪些？

MySQL 中常用的四种存储引擎分别是： MyISAM 存储引擎、 innoDB 存储引擎、 MEMORY 存储引擎、 ARCHIVE存储引擎。 MySQL 5.5 版本后默认的存储引擎为 InnoDB 。

InnoDB存储引擎

InnoDB 是 MySQL 默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。 InnoDB 内部做了很多优化，如能够自动在内存中创建自适应hash 索引，以加速读操作。

优点：支持事务和崩溃修复能力。 InnoDB 引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。例如电子商务网站或者社交网站。

MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用 MyISAM 引擎。 MyISAM会将表存储在两个文件中，数据文件 .MYD 和索引文件 .MYI 。

MyISAM存储引擎非常适合读多写少的应用，例如Web站点或者数据仓库。

优点：访问速度快。

缺点： MyISAM 不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景 ：对事务完整性没有要求；只读的数据，或者表比较小，可以忍受修复 repair 操作。

MyISAM特性：

1. MyISAM对整张表加锁，而不是针对行。读取数据时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在读取表记录的同时，可以往表中插入新的记录（并发插入）。

2. 对于 MyISAM 表， MySQL 可以手动或者自动执行检查和修复操作。执行表的修复可能会导致数据丢失，而且修复操作非常慢。可以通过 CHECK TABLE tablename 检查表的错误，如果有错误执行 REPAIR TABLE tablename 进行修复。

MEMORY存储引擎

MEMORY 引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

Memory: 这是一种将数据存储在内存中的存储引擎，数据随着MySQL服务器的关闭而丢失。Memory存储引擎适合存储临时数据和缓存数据。

优点：访问速度较快。

缺点：

// email 列创建前缀索引

ALTER TABLE table_name ADD KEY ( column_name ( prefix_length )) ;

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。

2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。

3. 只支持等值比较，不支持范围查询。

4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

5. MyISAM和InnoDB的区别？

1. 是否支持行级锁 : MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁，默认为行级锁。

2. 是否支持事务和崩溃后的安全恢复 ： MyISAM 注重性能，每次查询具有原子性，其执行速度比

InnoDB 类型更快，但是不提供事务支持。而 InnoDB 提供事务支持，具有事务、回滚和崩溃修复

能力。

3. 是否支持外键： MyISAM 不支持，而 InnoDB 支持。

4. 是否支持 MVCC ： MyISAM 不支持， InnoDB 支持。应对高并发事务， MVCC 比单纯的加锁更高效。

6.mysql索引

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种 数据结构 （有序）。

索引的优缺点？

优点：

加快数据查找的速度

为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度

加速表与表之间的连接

缺点：

建立索引需要占用物理空间

会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时

间变长

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4 次磁盘，查询速度大大提升。

什么情况下需要建索引？

1. 经常用于查询的字段

2. 经常用于连接的字段（如外键）建立索引，可以加快连接的速度

3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

什么情况下不建索引？

1. where条件中用不到的字段不适合建立索引

2. 表记录较少

3. 需要经常增删改

4. 参与列计算的列不适合建索引

5. 区分度不高的字段不适合建立索引，性别等

索引的数据结构

索引的数据结构主要有 B+树和哈希表，对应的索引分别为 B+ 树索引和哈希索引。 InnoDB 引擎的索引类型有B+ 树索引和哈希索引，默认的索引类型为B+树索引。

12. 进阶-索引-结构-B+tree_哔哩哔哩_bilibili

13. 进阶-索引-结构-hash_哔哩哔哩_bilibili

Hash索引和B+树索引的区别？

哈希索引不支持排序，因为哈希表是无序的。

哈希索引不支持范围查找。

哈希索引不支持模糊查询及多列索引的最左前缀匹配。

因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定

的，每次查询都是从根节点到叶子节点。

为什么B+树比B树更适合实现数据库索引？

索引有什么分类？

什么是最左匹配原则？

什么是覆盖索引？

尽量使用覆盖索引，减少使用select * ；

覆盖索引（Covering Index）是指查询语句可以通过索引本身完成查询，而无需去查询表格数据，从而提高查询性能。

在 MySQL 中，如果一个查询涉及到了某个表格中的多个字段，可以通过创建覆盖索引来提高查询效率。当查询语句包含了被索引的字段时，MySQL 可以直接从索引中获取所需的数据，而无需从磁盘中读取表格数据，这样可以大大减少 I/O 操作和磁盘访问时间，从而提高查询速度。

尽量不要回表查询。

什么是前缀索引？

索引什么时候会失效？

对于组合索引，不是使用组合索引最左边的字段，则不会使用索引（最左前缀匹配原则）
以%开头的like查询如 %abc ，无法使用索引；非%开头的like查询如 abc% ，相当于范围查询，会使用索引（模糊查询）
查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
对索引列进行运算
查询条件使用or连接，也会导致索引失效，只要有列没有索引，那就都没有索引。
数据分布：当走索引不如全表扫描时，就不会使用索引。（判断索引列是否不等于某个值时）

索引的设计原则？

MYSQL锁

全局锁

表级锁

数据库的三范式是什么

数据库的三范式（Normalization）是指在关系数据库设计中，为了减少数据冗余和数据更新异常，将一个表分解成更小的、更规范化的表的过程。

第一范式（1NF）：所有属性都是不可分割的原子值。即每个属性都是最基本的数据单元，不可再分割。

第二范式（2NF）：非主键属性必须完全依赖于主键，不能只依赖于主键的一部分。即非主键属性只能取决于主键，而不能取决于主键的一部分。

第三范式（3NF）：非主键属性不能依赖于其他非主键属性，即不存在传递依赖关系。即任何非主键属性不能由其他非主键属性推导出来。

且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

三范式的实践可以有效地避免数据冗余和数据更新异常，提高数据库的数据完整性和一致性。但是范式化也可能会使查询变得复杂，需要权衡设计决策。

什么是视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

SQL优化

简单说一说drop、delete与truncate的区别

这三个术语都可以用于从数据库表中删除数据或删除整个表，但它们的用途略有不同。

DROP：DROP语句用于删除整个表，包括表定义和表中的所有数据。使用DROP语句后，表将不再存在于数据库中，而且无法恢复。
DELETE：DELETE语句用于从表中删除一些或所有数据，但不删除表本身。DELETE语句通常与WHERE子句一起使用，以指定要删除的特定行或符合某些条件的所有行。使用DELETE语句后，表结构保持不变，但表中被删除的行将无法恢复。
TRUNCATE：TRUNCATE语句用于删除表中的所有数据，但不删除表本身。TRUNCATE语句比DELETE语句更快，因为它不会记录已删除的行。使用TRUNCATE语句后，表结构保持不变，但表中的所有行都被删除，无法恢复。

速度,一般来说: drop> truncate >delete

什么是内联接、左外联接、右外联接？

内联接、左外联接和右外联接都是SQL中用于连接两个或多个表的类型。

内联接：内联接（INNER JOIN）是连接两个或多个表中共有记录的方法。INNER JOIN只返回两个表中共有的行，也就是在一个表中有匹配项的另一个表中的行。内联接使用JOIN或INNER JOIN关键字来实现。
左外联接：左外联接（LEFT OUTER JOIN）返回左表中的所有记录以及右表中匹配的记录。如果右表中没有匹配的记录，则返回NULL值。左外联接使用LEFT JOIN或LEFT OUTER JOIN关键字来实现。
右外联接：右外联接（RIGHT OUTER JOIN）与左外联接相反，返回右表中的所有记录以及左表中匹配的记录。如果左表中没有匹配的记录，则返回NULL值。右外联接使用RIGHT JOIN或RIGHT OUTER JOIN关键字来实现。

需要注意的是，INNER JOIN返回的结果集只包含两个表中共有的行，而外联接则可能返回一个表中的所有行，即使在另一个表中没有匹配的行。

大表如何优化？

索引优化：索引是优化查询性能的关键因素。为大表的主键、外键和经常用于查询的列添加索引可以加快查询速度。但是，过多的索引可能会影响插入和更新的性能，因此需要平衡索引数量和性能。
分区：将大表拆分为较小的逻辑单元可以减少查询和操作的数据量。可以使用水平分区或垂直分区，具体取决于应用程序的查询模式和数据结构。

数据库缓存：大表中的查询结果可以被缓存在数据库中，以提高查询速度。可以使用内存数据库缓存或查询结果缓存等技术。
优化查询语句：使用正确的查询语句可以大大提高查询速度。应该避免使用SELECT *，限制返回的列数，避免使用子查询和不必要的连接操作等。
数据库分片：将大表数据分散到多个数据库或服务器上，以缓解单个服务器的负载。这种方法需要应用程序支持多数据库连接和数据分片的技术。
垃圾回收：删除不再需要的数据可以释放磁盘空间并减少查询和操作的数据量。
读 / 写分离 ：经典的数据库拆分方案，主库负责写，从库负责读；

分库分表之后,id 主键如何处理？

在分库分表后，对于每个分片（shard）中的表，其主键需要保证唯一性，并且在整个分片集群（sharding cluster）中不能有重复。一种常见的处理方式是使用分布式主键（distributed key）。

分布式主键可以采用不同的生成方式，例如：

基于UUID的主键：使用UUID（通用唯一标识符）作为主键，UUID是根据时间戳、随机数等生成的全局唯一标识符。
基于snowflake算法的主键：Snowflake算法是Twitter开源的分布式ID生成算法，通过机器ID、数据中心ID、时间戳等信息生成64位的唯一ID。
基于数据库自增ID的主键：在每个分片中，可以使用数据库的自增ID作为主键，但是需要在不同分片中使用不同的自增ID起点。
基于一致性哈希（consistent hash）算法的主键：使用一致性哈希算法将不同主键映射到不同分片中。

以上是一些常见的分布式主键生成方式，选择合适的方式取决于应用程序的需求和分片集群的规模。同时，还需要考虑到主键的生成效率、冲突率和可读性等方面的因素。

mysql有关权限的表都有哪几个

MySQL中有多个系统表（system table）和系统视图（system view）用于管理用户和权限。其中，与权限相关的系统表包括：

mysql.user：用于存储MySQL的用户账号信息，包括用户名、密码、所属主机等信息。
mysql.db：用于存储数据库级别的权限信息，包括哪些用户可以访问哪些数据库以及执行哪些操作。
mysql.tables_priv：用于存储表级别的权限信息，包括哪些用户可以访问哪些表以及执行哪些操作。
mysql.columns_priv：用于存储列级别的权限信息，包括哪些用户可以访问哪些表的哪些列以及执行哪些操作。
mysql.roles：用于存储MySQL角色的信息，可以将权限授予角色并将角色分配给用户，简化权限管理。
mysql.role_edges：用于存储MySQL角色之间的关系，例如继承关系等。
mysql.proxies_priv：用于存储代理用户的权限信息，代理用户是指具有代理权限的用户，可以代替其他用户执行某些操作。

以上是MySQL中与权限相关的常用系统表。在使用这些表管理用户和权限时，需要谨慎操作，避免误删或误操作导致系统安全问题。

mysql有哪些数据类型

数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）、浮点型（FLOAT、DOUBLE、DECIMAL）、布尔型（BOOLEAN）等。
日期和时间类型：DATE、TIME、YEAR、DATETIME、TIMESTAMP等。
字符串类型：CHAR、VARCHAR、TEXT、BLOB、ENUM、SET等。
二进制类型：BINARY、VARBINARY、BLOB等。

除了上述常用的数据类型，MySQL还支持一些高级的数据类型和扩展类型，例如JSON类型、空间类型（SPATIAL）、时间戳（TIMESTAMP）、自动更新时间戳（TIMESTAMP ON UPDATE）等。

创建索引的三种方式，删除索引

1、CREATE INDEX语句：使用CREATE INDEX语句可以在已存在的表上创建新的索引，语法如下：

CREATE [UNIQUE] INDEX index_name ON table_name(column_name);

其中，index_name为索引的名称，table_name为表名，column_name为要创建索引的列名。UNIQUE关键字表示该索引是否唯一，如果不指定则默认为非唯一索引。