MySQL

每天记录典型回答 ✅binlog、redolog和undolog区别？ binlog是MySQL用于记录数据库中的所有DDL语句和DML语句的一种二进制日志。它记录了所有对数据库结构和数据的修改操作，如INSERT、UPDATE和DELETE等。binlog主要用来对数据库进行数据备份、灾难恢复和数据复制等操作。binlog的格式分为基于语句的格式和基于行的格式。 MySQL的bin log主要支持三种格式，分别是statement、row以及mixed。MySQL是在5.1.5版本开始支持row的、在5.1.8版本中开始支持mixed。 statement 当binlog的格式为statement时，binlog 里面记录的就是 SQL 语句的原文，也就是说在数据库中执行的SQL会原封不动的记录到binlog中。 如： 1 2 # at 12345 # statement: UPDATE employees SET name = "Hollis" WHERE id = 101; 这种格式现在用的都比较少了，因为他会导致主从同步的数据不一致问题。 比如说，当我们使用DELETE或者UPDATE的时候，指定了LIMIT，但是并没有使用order by，那么最终这条语句在主库和从库上的执行结果可能是不一样的（即使同一个库上面，你执行多次结果可能也不一样）。 ✅MySQL的limit+order by为什么会数据重复？ 还有就是下面这篇文章中提到的情况了： ✅为什么MySQL默认使用RR隔离级别？ row 在ROW格式中，bin log会记录每个数据更改的具体行的细节。这意味着二进制日志中的每个条目都会详细列出发生变更的行的内容和修改。 这种格式的好处就是不会导致主从不一致的问题。 但是他的缺点就是可能会要记录更多的内容，比如批量修改，就需要把每条记录的变更都记录下来。 所以，带来的问题就是基于这种格式的binlog，在数据恢复的时候，会需要更长的时间，也会导致磁盘IO和网络IO都比较高。 mixed 这种其实就是把statement和row结合了，MySQL会根据SQL的情况，自动在row和statement中互相切换选择一个他认为合适的格式进行记录。 但是，在RR下，row和statement都可以生效，但是在RC下，只有row格式才能生效。具体见上面我们贴的那个链接的内容。

典型回答 ✅MySQL主从复制的过程 在MySQL的主从复制中，我们介绍过MySQL的主从复制的原理，在复制过程中，主库的binlog会不断地同步到从库，然后从库有一个SQL线程不断地拉取并重放这些SQL语句，那么，一旦日志内容太多的话，一个线程执行就会有延迟，就会导致主从延迟。 为了降低因为这个原因导致的主从延迟，所以MySQL提供了并行复制的方案。在MySQL的多个版本中，先后推出过很多个并行复制的方案： MySQL 5.6推出基于库级别的并行复制。 MySQL 5.7推出基于组提交的的并行复制。 MySQL 8.0 推出基于WRITESET的并行复制。 库级别并行复制 在MySQL 5.6中，并行是基于Schema的，也就是基于库的，在MySQL 5.6中，可以配置多个库并行进行复制，这意味着每个库都可以有自己的复制线程，可以并行处理来自不同库的写入。这提高了并行复制的性能和效率。 但是，其实大多数业务都是单库的，所以这种方案，在推出之后很多开发者和DBA并不买账，因为实在是太鸡肋了。 组提交的的并行复制 因为5.6的并行复制被很多人诟病，于是在MySQL 5.7中推出了基于组提交的的并行复制，这才是真正意义上的并行复制。这就是注明的 MTS (Enhanced Multi-Threaded Slave) ：https://dev.mysql.com/blog-archive/multi-threaded-replication-performance-in-mysql-5-7/ ✅介绍下MySQL 5.6中的组提交 先了解下组提交，然后接着往下看。 在组提交的介绍中我们说过，一个组中多个事务，都处于Prepare阶段之后，才会被优化成组提交。那么就意味着**如果多个事务他们能在同一个组内提交，这个就说明了这个几个事务在锁上是一定是没有冲突的。** 1 2 binlog_transaction_dependency_tracking = WRITESET # COMMIT_ORDER transaction_write_set_extraction = XXHASH64 换句话说，就是这几个事务修改的一定不是同一行记录，所以他们之间才能互不影响，同时进入Prepare阶段，并且进行组提交。 那么，没有冲突的多条SQL，是不是就可以在主备同步过程中，在备库上并行执行回放呢？ 答案是可以的，因为一个组中的多条SQL之间互相不影响，谁先执行，谁后执行，结果都是一样的！ 所以，这样Slave就可以用多个SQL线程来并行的执行一个组提交中的多条SQL，从而提升效率，降低主从延迟。 基于WRITESET的并行复制 前面的组提交大大的提升了主从复制的效率，但是他有一个特点，就是他依赖于主库的并行度，假如主库的并发比较高，那么才可以进行组提交，那么才能用到组提交的并行复制优化。 如果主库的SQL执行并没有那么频繁，那么时间间隔可能就会超过组提交的那两个参数阈值，就不会进行组提交。那么复制的时候就不能用并行复制了。 MySQL 8.0为了解决这个问题，引入了基于WriteSet的并行复制，这种情况下即使主库在串行提交的事务，只要互相不冲突，在备库就可以并行回放。 开启WRITESET： 1 2 binlog_transaction_dependency_tracking = WRITESET # COMMIT_ORDER transaction_write_set_extraction = XXHASH64 ...

典型回答 深度分页问题是指在数据库查询中，当你尝试访问通过分页查询返回的结果集的后面部分（即深层页码）时遇到的性能问题。 假设你有一个包含数百万条记录的表，你想通过分页的方式来展示这些数据。当用户请求第10000页数据时，假设pageSize为10，那么最终就是LIMIT 99990,10 ，数据库必须先扫描过前99990条记录，才能返回第10000页的数据，这会导致显著的性能下降。 99991是起始ID = (页数 - 1) * 每页项目数 + 1 对于第1页，起始ID将是1，结束ID将是10。对于第2页，起始ID将是11，结束ID将是20，以此类推。 对于第10000页： 起始ID = (10000 - 1) * 10 + 1 = 99991 深度分页的问题想要优化，有以下几个手段。 使用子查询和JOIN优化 假如我们这样一条SQL： 1 SELECT c1, c2, cn... FROM table WHERE name = "Hollis" LIMIT 1000000,10 我们可以基于子查询进行优化，如以下SQL： 1 2 3 4 5 6 7 8 9 SELECT c1, c2, cn... FROM table INNER JOIN ( SELECT id FROM table WHERE name = "Hollis" ORDER BY id LIMIT 1000000, 10 ) AS subquery ON table.id = subquery.id 首先，使用一个子查询来获取限定条件下的一小部分主键id，这部分 id 对应于我们分页的目标区域。然后，使用这些 id 在主查询中获取完整的行数据。 ...

典型回答 当我们在做查询的时候，经常会使用order by进行排序，而当我们只想查询部分数量的时候，也会使用limit进行限制条数。 但是，有的时候，同样的一条SQL语句，多次执行得到的结果可能是不同的，在实际表现中可能就是在分页查询中，一条记录可能出现在多个页中。 很多人会以为是因为查询过程中有别的事务新增或者删除了数据。其实也不一定，即使没有数据变化，这种情况也有可能会发生。 因为在MySQL的Limit的实际实现中，有以下描述： If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns. https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html 也就是说，如果ORDER BY的列中，多行具有相同的值，服务器可以自由地以任何顺序返回这些行，并且根据整体执行计划的不同可能会以不同的方式返回它们。 ...

典型回答 数据库的主从延迟是指在主从数据库复制过程中，从服务器（Slave）上的数据与主服务器（Master）上的数据之间存在的时间差或延迟。 一般来说导致主从延迟可能由多种因素引起，以下是一些常见的原因： 网络延迟：主节点和从节点之间的网络延迟导致复制延迟这是比较常见的一种情况， 从节点性能问题：从服务器的性能不足也可能导致复制延迟。如果从服务器的硬件资源（CPU、内存、磁盘）不足以处理接收到的复制事件，延迟可能会增加。 复制线程不够：当从节点只有一个线程，或者线程数不够的时候，数据回放就会慢，就会导致主从节点的数据延迟。 大事务：如果出现了一个事务，特别长，执行过程特别慢，那么这个SQL在slave节点上重放的时候也会比较慢，也可能会出现延迟。、 解决主从延迟主要有几个事情可以做： 优化网络：确保主节点和从节点之间的网络连接稳定，尽量同城或者同单元部署，减小网络延迟。 提高从服务器性能：增加从服务器的硬件资源，如CPU、内存和磁盘，以提高其性能，从而更快地处理复制事件。 并行复制：借助MySQL提供的并行复制的能力，提升复制的效率，降低延迟。MySQL的并行复制原理 避免大事务：✅MySQL执行大事务会存在什么问题？

典型回答 在MySQL中，使用like进行模糊查询，在一定情况下是无法使用索引的。如下所示： 当like值前后都有匹配符时%abc%，无法使用索引 1 2 3 4 5 6 7 EXPLAIN SELECT * FROM `test` WHERE `name` LIKE '%abc%' ; +----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+ | 1 | SIMPLE | test | <null> | ALL | <null> | <null> | <null> | <null> | 19820 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+ 当like值前有匹配符时%abc，无法使用索引 1 2 3 4 5 6 7 EXPLAIN SELECT * FROM `test` WHERE `name` LIKE '%abc' ; +----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+ | 1 | SIMPLE | test | <null> | ALL | <null> | <null> | <null> | <null> | 19820 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+ 当like值后有匹配符时’abc%’，可以使用索引 1 2 3 4 5 6 7 EXPLAIN SELECT * FROM `test` WHERE `name` LIKE 'abc%' ; +----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+ | 1 | SIMPLE | test | <null> | range | idx_name | idx_name | 153 | <null> | 200 | 100.0 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+ 那么，like %abc真的无法优化了吗？ ...

典型回答 MySQL的查询缓存是一种数据库性能优化技术，它允许MySQL在执行SELECT时将查询结果缓存起来，以便在以后相同的查询被再次执行时，可以直接返回缓存的结果，而不必再次重新解析和执行该语句。 查询缓存在session之间共享，因此由一个客户端生成的结果集可以用来响应另一个客户端发出的相同查询。 查询缓存可以提高查询性能，尤其是具有不经常更改的表且服务器接收到许多相同查询的环境中非常有用。 但是查询缓存是存在一定的限制的，如： 查询缓存是根据查询文本来匹配查询的。**查询必须以字节级别完全匹配，包括大小写敏感性。**如果查询文本不完全相同，查询将不会被缓存。 包含非确定性函数的查询结果不会缓存，例如RAND()和NOW()。这是因为这些函数的结果在不同的执行之间可能会不同，因此无法缓存结果。 如果查询涉及使用临时表，查询缓存将不会工作。这是因为临时表的存在会导致查询无法被缓存。 查询缓存被设计成不提供陈旧的结果。对底层表的任何修改都会导致这些表的所有缓存失效。 某些存储引擎可能不支持查询缓存，或者在特定情况下限制了其使用。例如，InnoDB存储引擎在某些情况下不使用查询缓存，以便支持多版本并发控制（MVCC）。 在MySQL 5.6之前，查询缓存默认开启的，但是从5.6开始，查询缓存已经默认禁用了！ 但是，在MySQL 8.0中取消了查询缓存：https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/ 主要原因是因为虽然他能带来一定的性能提升，但它也有一些明显的缺点，这些缺点导致了MySQL 8.0取消查询缓存的决定： 频繁失效：查询缓存是以表级别为单位进行管理的，这意味着如果任何表中的数据发生变化，与该表相关的所有查询缓存都将被清除。这导致了缓存的频繁失效，减少了其效用。 内存开销：查询缓存需要占用大量内存来存储查询文本和结果集，这对于具有大量查询和数据的数据库来说，会导致内存开销问题。 不一致性：有时查询结果可能会因为数据库中的数据更改而不再与缓存的结果匹配，这可能导致不一致性的问题。 查询分布不均匀：在某些情况下，查询缓存可能会导致性能下降，因为它不能很好地应对不均匀的查询分布。（部分频繁查询需要频繁更新缓存，不频繁的查询又无法命中缓存） 所以，MySQL 8.0决定取消查询缓存，以便提高整体性能和可维护性。

典型回答 存储过程是一种在数据库中存储的预编译的代码块，它可以执行一系列的数据库操作。存储过程被设计用来完成特定的功能或业务逻辑，通常是一组SQL语句的集合。 假设我们有一个名为 Employees 的数据库表，其中包含员工的基本信息，如员工ID、姓名和薪水。我们想创建一个存储过程来更新特定员工的薪水。以下是一个在MySQL中实现这个功能的存储过程示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 DELIMITER // CREATE PROCEDURE UpdateEmployeeSalary(IN empID INT, IN newSalary DECIMAL(10,2)) BEGIN -- 检查新薪水是否大于0 IF newSalary > 0 THEN -- 更新员工的薪水 UPDATE Employees SET salary = newSalary WHERE employee_id = empID; ELSE -- 如果薪水无效，则抛出错误 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid salary amount'; END IF; END // DELIMITER ; 这个存储过程名为 UpdateEmployeeSalary，接受两个参数：empID（员工ID）和 newSalary（新薪水）。它首先检查新薪水是否为正数，如果是，则更新对应员工的薪水。如果薪水不合理（例如，小于或等于0），则抛出一个错误。 ...

典型回答 ✅数据库怎么做加密和解密？ 数据库加密可以保障数据的安全，但是也会带来很多的问题，其中有一个比较关键的就是数据的模糊查询的问题。 当我们通过加密后把密文存到数据库中的时候，在通过明文进行模糊查询是不生效的。 比如Hollis加密后的内容是363164846D8200899E314897E64A7420，那么当我想用Ho来做模糊查询时候，那么他的密文是71AAFD38484F3160708C6A6D2D5F736B，这两个密文可以说是没有任何关系的，所以，是无法直接做模糊查询的。那么如何解决这个问题呢？ 先解密再查询 一种比较常见的方法，就是把要查询的表中的所有符合条件的数据，都加载到应用内存中，在内存中逐个解密，然后再做模糊匹配。 这个方案的优点就是实现简单，缺点也很明显，需要把所有数据都加载到内存中，容易导致OOM。不推荐 明文映射表 还有人提出过说单独建一张表，其中保存明文和目标表之间的映射，需要模糊查询的时候先去明文映射表中查到主键，然后再去目标表查询数据。 但是这个方案基本上是属于自欺欺人，因为一旦数据被拖库，还是会丢。不推荐 数据库解密函数 加密的时候如果用了函数的话，解密的时候我们也可以借助函数来做解密，同时做模糊查询，比如加密时使用了AES_ENCRYPT算法： 1 2 -- 加密数据 INSERT INTO user_data (username, credit_card) VALUES ('Hollis', AES_ENCRYPT('1234-5678-9012-3456', 'hollis_secret_key')); 那么在做模糊查询的时候就可以这样做： 1 SELECT * FROM user_data WHERE AES_DECRYPT(credit_card, 'hollis_secret_key') like 'Hol%'; 这样也就能实现一个模糊查询的效果了，但是这个方案有个缺点，就是无法用到索引，不是因为用like，而是因为我们在字段上用了函数，索引就会失效。 这个方案适合于表中数据量不大，或者查询条件中还有其他查询字段可以走索引的情况。 明文分词 首先有一个比较简单的做法，那就是对明文进行分词，然后分别加密后存储到数据库中，比如Hollis这个需要加密的字符串，我们就可以把他拆成Ho 、Holl、llis等这几个字符串，然后分别对他们进行加密，并保存到数据库中。 这样当我们使用Ho 、Holl、llis 进行查询的时候，就可以对明文加密后去数据库中匹配了。 这个方案的缺点也比较明显，第一个就是需要冗余很多字段，第二个就是不够灵活，如果我按照Holli来查询的话就不支持了。 当然，也有一些改进的方式，比如并不需要增加多个字段，可以把这些需要用于模糊查询的信息都放到同一个字段中，如DECRYPT_NAME，拼接成一个字符串就行了。如71AAFD38484F3160708C6A6D2D5F736B,83B01A578395CE81AEAAC6A4FE70AA94,E90048FB068AA98B7EC751CBD6DC78B7 这样就只需要通过这个字段做模糊查询就行了（索引也会失效）。SQL如下： 1 2 3 SELECT * FROM hollis_test_table WHERE DECRYPT_NAME REGEXP '71AAFD38484F3160708C6A6D2D5F736B|83B01A578395CE81AEAAC6A4FE70AA94|E90048FB068AA98B7EC751CBD6DC78B7'; 很多公司其实都是用的这种方案，比如： 淘宝：https://open.taobao.com/docV3.htm?docId=106213&docType=1 拼多多：https://open.pinduoduo.com/application/document/browse?idStr=7553D76C8D1713EF

典型回答 很多时候，我们的数据库表中会存储很多敏感信息，如用户的手机号、身份证号、密码之类的，这些数据如果不做好加密的话，一旦数据泄漏就会导致重要信息泄露。 一般来说都需要对敏感字段进行加密，然后再在数据库中保存加密后的数据，这样即使被拖库也没关系，比如攻击者拿到的只是加密后的密码，并不知道真实密码是什么。 ✅什么是撞库、拖库和洗库？ 那么，我们应该如何做数据库的加密呢。通常来说有以下几种办法： 服务端加解密 服务端加解密指的就是数据库在存入数据库之前就加密好，然后再从数据库取出之后进行解密。这样可以保证数据库的数据绝对安全，因为数据库也不知道明文到底是什么。 一般来说我们可以选择各种各样的加密算法，如对称加密和非对称加密都可以，一般来说用对称加密就行了。 ✅什么是对称加密和非对称加密？ 当然，有些场景下也可以用MD5（MD5严格来说并不是加密算法，只是一种hash算法），但是需要注意的是MD5不支持解密，所以只能用于那种存储后只做匹配而不作查询展示的场景，如用户的密码。而需要展示的场景，如手机号等就需要支持解密。 数据库加密函数 MySQL提供了一些内置的加密函数，我们可以直接使用这些加密函数进行数据加密： AES_ENCRYPT 和 AES_DECRYPT，这两个函数是对称加密算法，使用对称密钥加密，这意味着加密和解密都使用相同的密钥。因此，必须确保密钥的安全性。 AES_ENCRYPT(str, key) 用于使用AES算法对字符串 str 进行加密，key表示使用的密钥。 AES_DECRYPT(crypt_str, key) 用于解密已加密的字符串 crypt_str，使用相同的密钥 key。 1 2 3 4 5 -- 加密数据 INSERT INTO user_data (username, credit_card) VALUES ('Hollis', AES_ENCRYPT('1234-5678-9012-3456', 'hollis_secret_key')); -- 解密数据 SELECT username, AES_DECRYPT(credit_card, 'hollis_secret_key') FROM user_data WHERE username = 'John'; ENCRYPT： ENCRYPT(str, salt) 函数使用UNIX crypt()函数对字符串 str 进行加密，其中 salt 是一个2字符的随机盐。这种加密方法通常用于密码存储，但不是最安全的加密方式。 1 2 3 4 5 -- 使用随机盐加密密码 INSERT INTO user_credentials (username, password_hash) VALUES ('Hollis', ENCRYPT('my_password', 'ab')); -- 验证密码 SELECT * FROM user_credentials WHERE username = 'Hollis' AND password_hash = ENCRYPT('entered_password', password_hash); MD5 和 SHA1： MD5(str) 和 SHA1(str) 函数分别用于计算字符串 str 的MD5和SHA-1哈希值。这不是真正的加密，而是散列函数，无法逆向解密。 1 2 3 4 5 -- 存储MD5哈希值 INSERT INTO user_data (username, password_hash) VALUES ('Hollis', MD5('hollis666')); -- 验证密码 SELECT * FROM user_data WHERE username = 'Hollis' AND password_hash = MD5('hollis666'); PASSWORD： PASSWORD(str) 函数将字符串 str 加密为MySQL原生密码散列。这通常用于MySQL用户密码的存储。 1 2 -- 存储MySQL原生密码散列 INSERT INTO mysql_users (username, password_hash) VALUES ('Hollis', PASSWORD('hollis666')); DES_ENCRYPT 和 DES_DECRYPT，需要注意：DES算法不够安全，通常不建议使用： DES_ENCRYPT(str, key_str) 用于使用DES算法对字符串 str 进行加密，使用提供的密钥字符串 key_str。 DES_DECRYPT(crypt_str, key_str) 用于解密已加密的字符串 crypt_str，使用相同的密钥字符串 key_str。 InnoDB静态加密 MySQL 5.7开始，InnoDB支持对静态数据加密。适用于按表的文件表空间、常规表空间、mysql系统表空间、redolog和undolog进行加密。 ...