MySQL的补充知识点

标签：COUNT 知识点补充数据库查询索引 MySQL 操作

SQL注入？

定义：

MySQL的sql注入是一种攻击技术，攻击者通过再应用程序中插入恶意的sql代码，从而控制应用程序和服务器之间的交互，当应用程序未对用户输入进行适当验证或转义时，这种攻击就可能发生。

发生的情况：

未对用户输入进行验证：当Web应用程序直接将用户输入拼接到SQL查询语句中，而没有对输入进行任何验证或转义时，就存在SQL注入的风险。
使用不安全的API：某些数据库API或函数可能允许不安全的SQL查询构造，如果没有正确使用或配置这些API，就可能导致SQL注入。
错误的错误处理：如果Web应用程序将数据库错误直接返回给用户，攻击者可以利用这些错误信息进行SQL注入攻击。

简单举例：

攻击者输入了1 OR '1'='1作为一个连接条件，就可以获取不应该获取到的信息。

如何预防：

使用参数化查询或者预编译语句的方式来确保用户的输入不会作为SQL代码；

对用户的输入进行适当的验证和转义；

不要将数据库的错误直接返回给用户；

幂等性？

定义：

数据库中的幂等性指的是多次执行相同的操作（如插入、更新、删除、查询）对数据库状态的影响与单次执行相同；

幂等的应用场景：

1.查询操作：查询操作天生是幂等的，因为无论执行多少次查询，结果都不会改变数据库的状态。

2.删除操作：删除操作在大多数情况下也是幂等的，因为删除同一个记录多次与删除一次的效果相同。

3.更新操作：直接更新某个值的更新操作可以是幂等的，但更新累加操作（如计数器）通常不是幂等的，因为每次执行都会改变数据库的状态。

4.插入操作：插入操作通常不是幂等的，因为每次插入都会向数据库添加新的记录。但是，通过使用唯一索引或其他机制，可以确保即使多次尝试插入相同的记录，也只有一条记录被添加。

数据库幂等性的实现方法：

1.通过悲观锁的形式实现幂等；

2.通过唯一索引实现幂等性；

3.通过乐观锁来实现幂等性；

（1）悲观锁的方式

实现 SQL 如下所示：

begin;  # 1.开始事务
select * from table_name where id='xxx' for update; # 2.查询状态
insert into table_name (id) values ('xxx'); # 3.添加操作
update table_name set status='xxx'; # 4.更改操作
commit; # 5.提交事务

在实现的过程中需要注意以下两个问题：

如果使用的是 MySQL 数据库，必须选用 innodb 存储引擎，因为 innodb 支持事务；

id 字段一定要是主键或者是唯一索引，不然会锁表，影响其他业务执行。

（2）唯一索引方式

我们可以创建一个唯一索引的表来实现幂等性，在每次执行业务之前，先执行插入操作，因为唯一字段就是业务的 ID，因此如果重复插入的话会触发唯一约束而导致插入失败。在这种情况下（插入失败）我们就可以判定它为重复提交的请求。

（3）乐观锁的方式

乐观锁是指在执行数据操作时（更改或添加）进行加锁操作，其他时间不加锁，因此相比于整个执行过程都加锁的悲观锁来说，它的执行效率要高很多。

乐观锁可以通过版本号来实现，例如以下 SQL：

update table_name set version=version+1 where version=0;

面试总结问题？

MySQL三大范式再开发中会完全遵循吗？

一般不会。

1.性能考虑：遵循所有范式可能会导致数据冗余减少，但这也可能增加查询的复杂性，从而降低性能。例如，为了满足第三范式（3NF），可能需要多次连接多个表来获取所需的数据，这可能会比在一个表中直接获取数据更复杂。

2.业务需求：业务需求可能直接影响数据库的设计。在某些情况下，业务需求可能要求数据以特定的方式存储，这可能与三大范式不符。

MySQL中的innodb存储引擎和mysiam存储引擎的区别？

innodb存储引擎支持事务外键行级锁；并且适用于高并发读写操作的场景，尤其是写操作频繁的时候；

而myisam支持表锁，不支持事务；主要以读操作为主的场景，尤其是大量读操作，因为myisam的表级锁可能导致写操作的并发性能较差；

为何innodb适用于并发量高的写操作频繁的场景，而myisam适用于读操作频繁的场景？

INNODB：

InnoDB存储引擎通过行级锁定、多版本并发控制、事务支持、缓冲池等机制，使得它适用于需要高并发读写操作，尤其是写操作频繁的场景。

MyISAM：

而MyISAM是表级锁定，这意味着当一个线程正在对表进行写操作时，其他线程必须等待该写操作完成才能访问该表，无论是读还是写。

MyISAM 使用非聚集索引，索引和数据文件是分开的。这种索引结构使得读取数据非常直接和高效，因为 MyISAM 可以直接通过索引找到对应的数据文件位置并读取数据。

MyISAM不支持事务，因此它避免了与事务相关的开销，这种简化使得 MyISAM 能够更高效地处理读操作。

MySQL的一致性是什么？具体的sql实现？

定义：

数据库从一个一致性状态转变到另一个一致性状态的过程中，数据的完整性和准确性得以保持的特性。

实现：

使用事务：

使用START TRANSACTION、COMMIT和ROLLBACK来确保一组相关的SQL语句要么全部成功执行，要么全部不执行。这有助于维护数据的一致性。

START TRANSACTION; -- 假设我们有两个表：accounts 和 transfers -- 从账户A转账到账户B 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; 
-- 如果上述两个UPDATE都成功，则提交事务 COMMIT; 
-- 如果在事务执行过程中发生错误，则回滚事务 -- ROLLBACK;

使用外键约束：

外键约束可以确保两个表之间的数据关系的一致性。你可以定义一个表的外键列，该列的值必须在另一个表的主键列中存在。

CREATE TABLE orders (  
    order_id INT PRIMARY KEY,  
    user_id INT,  
    FOREIGN KEY (user_id) REFERENCES users(id)  
);

binlog有几种格式 RR、RC情况下都会生效吗？

三种格式：statement、row和mixed；

都会生效；

myisam统计数据时间复杂度？

全表扫描：

- 如果查询没有使用索引，或者索引不适用，MySQL可能会进行全表扫描。这意味着它会遍历表中的每一行以查找匹配的记录。这种操作的时间复杂度通常是O(n)，其中n是表中的行数。

索引扫描：

- 如果查询条件基于索引，MySQL可以使用索引来加速查询。索引扫描的时间复杂度取决于索引的类型和结构，但通常会比全表扫描快得多。基于B-Tree索引的查询时间复杂度可以近似为O(log n)，但需要注意的是，这里的“n”指的是索引树中的节点数，而不是表中的行数。

conut（*）、count(1)、count(字段)有什么区别(那个不统计null值)？

COUNT(*)

- COUNT(*) 计算表中的所有行数，包括那些包含NULL值的行。
- 它不关心表中的任何特定列或列中的值。
- 它通常比基于特定列的 COUNT 操作更快，因为数据库不需要检查列中的值（只需计算行数）。

COUNT(1)

- COUNT(1) 在功能上与 COUNT(*) 完全相同。
- 它也计算表中的所有行数，包括包含NULL值的行。
- 这里的“1”只是一个常量表达式，数据库在计数时不会真正考虑它的值。
- 在大多数数据库系统中，COUNT(1) 和 COUNT(*) 的性能是相似的。

COUNT(字段)

- COUNT(字段) 计算指定列中非NULL值的数量。
- 如果列中包含NULL值，那么这些行将不会被计入总数。
- 因此，与 COUNT(*) 和 COUNT(1) 相比，它可能会返回更小的值。
- 需要注意的是，如果查询中涉及到WHERE子句或其他条件，这些条件可能会影响 COUNT(字段) 的结果，但不会影响 COUNT(*) 或 COUNT(1) 的结果（除非这些条件导致某些行被完全排除在查询之外）。

总结：

COUNT(*) 和 COUNT(1) 都会统计表中的所有行数，包括包含NULL值的行。
COUNT(字段) 只统计指定列中非NULL值的数量。

因此，不统计NULL值的是 COUNT(字段)。

（宽表、数据冗余、代码关联）遇到过limit深度分页问题，有什么解决方案？

宽表：宽表是指字段比较多的数据库表，通常将业务主题相关的指标、维度、属性关联在一起。宽表的一个主要缺点是数据的大量冗余，因为它不符合数据库设计的三范式规范。

数据冗余:数据冗余是指在数据存储或传输过程中，为了保证数据的可靠性和安全性而增加的不必要的数据副本或冗余数据。

代码关联与Limit深度分页问题:在数据库查询中，当使用LIMIT和OFFSET进行分页时，如果OFFSET的值很大（即深度分页），查询性能会显著下降。这是因为数据库需要扫描大量的行来找到目标页的数据，这会导致大量的磁盘I/O和内存操作。

解决方案:

使用覆盖索引：确保查询的列都包含在索引中，这样数据库可以直接通过索引获取数据，而无需回表到数据行。

避免深度分页：如果可能的话，尽量避免深度分页。例如，可以提供搜索、筛选或排序功能来帮助用户更快地找到他们想要的数据。

使用上一页下一页的方式通过索引提升效率；

有没有遇到过死锁问题？什么场景？如何解决？update同一条记录会发送死锁吗？

场景：

多个事务竞争同一资源：例如，两个事务都尝试锁定表中的同一行进行更新。如果它们都以相反的顺序锁定这些行，并且都在等待对方释放资源，就可能会产生死锁。
锁的顺序不一致：当多个事务以不同的顺序请求锁时，也可能导致死锁。例如，事务A先锁定资源1然后请求资源2，而事务B先锁定资源2然后请求资源1。

如何解决：

设置锁超时：数据库管理系统（DBMS）通常允许设置锁的超时时间。如果事务在等待锁超过这个时间，它将被终止并回滚，从而释放其持有的锁。
保持一致的锁顺序：确保所有事务都以相同的顺序请求锁。这可以通过编程逻辑或数据库设计来实现。

关于update同一条记录是否会发生死锁：

读未提交隔离级别下，update同一条记录有可能发生死锁；

读已提交和可重复读：在这两种隔离级别下，update同一条记录有可能发生死锁，特别是当多个事务按不同的顺序请求锁并且每个事务都持有其他资源并等待对方释放时。

串行化：由于事务按顺序执行，因此不会发生死锁。

innodb的表空间结构？innodb的三大特性？

系统表空间：包含InnoDB数据字典、回滚段、双写缓冲区等。在MySQL 5.6及之前，默认是ibdata1文件。
独立表空间（或称为文件表空间）：每个表都有一个独立的.ibd文件来存储数据和索引。从MySQL 5.6.6开始，可以配置InnoDB为每个表使用独立表空间。
通用表空间：这是一个可选的表空间类型，允许用户将多个表的数据和索引存储在一个共享的表空间文件中。
撤销表空间：用于存储撤销日志（Undo Logs），用于支持事务的MVCC（多版本并发控制）特性。

InnoDB存储引擎的三大特性包括：插入缓冲、两次写和自适应哈希

change buffer：change buffer 的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。
doublewrite（两次写）：就是在写数据页(块)之前，先把这个数据页写到一块独立的物理文件位置(ibdata),然后再写到数据页。这样在宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，这就是 double write.
自适应哈希：

SQL关键字的执行顺序

FROM子句：确定数据来源的表或视图。
ON子句：连接条件（如果有的话），例如JOIN操作。
JOIN子句：如果有的话，如INNER JOIN、LEFT JOIN等。
WHERE子句：过滤条件。
GROUP BY子句：分组条件，用于聚合函数。
HAVING子句：分组后的条件过滤。
SELECT子句：选取特定的列。
DISTINCT子句：去除重复数据。
ORDER BY子句：结果集的排序。

MySQL的去重操作

使用DISTINCT关键字
使用GROUP BY子句

索引合并？ Intersection合并、Union合并、Sort-Union合并

索引合并是MySQL查询优化器在处理复杂查询条件时使用的一种技术。当查询的WHERE子句中有多个独立的条件，且每个条件都可以使用不同的索引时，MySQL会尝试将这些索引合并起来，以提高查询效率。索引合并允许数据库在一个查询中同时使用多个索引，从而避免全表扫描或减少需要扫描的数据量。

索引合并主要有三种类型：

Intersection合并（交集合并）：

- 当查询的WHERE子句中有多个等值匹配条件，并且每个条件都可以使用不同的索引时，MySQL可能会使用Intersection合并。
- 它从每个相关索引中检索满足条件的记录，并计算这些记录的主键值的交集。
- 最后，它根据这个交集的主键值列表执行回表操作，以获取完整的记录。

Union合并（并集合并）：

- 当查询的WHERE子句中有多个条件，并且每个条件都可以使用不同的索引，且这些条件之间是“或”关系时，MySQL可能会使用Union合并。
- 它从每个相关索引中检索满足各自条件的记录，并将这些记录合并成一个结果集。
- 需要注意的是，Union合并并不保证结果的顺序，因为它只是简单地将来自不同索引的记录合并在一起。

Sort-Union合并（排序并集合并）：

- 当查询的WHERE子句中有多个条件，并且这些条件使用不同的索引时，但结果需要按照某个顺序排列时，MySQL可能会使用Sort-Union合并。
- 它首先从每个相关索引中检索满足条件的记录，并将这些记录合并成一个结果集。
- 然后，它根据指定的排序条件对这个结果集进行排序。

InnoDB行格式

InnoDB支持以下四种行格式：
COMPACT（紧凑格式）：

这是InnoDB的默认行存储格式，也是最常用的格式。

REDUNDANT（冗余格式）

DYNAMIC（动态格式）

COMPRESSED（压缩格式）

标签：COUNT,知识点,补充,数据库,查询,索引,MySQL,操作
From： https://blog.csdn.net/qq_58738794/article/details/144019641