MySQL

MySQL怎么做热点数据高效更新？

典型回答 MySQL的热点数据更新问题，一直都是行业内的一个难题，对于秒杀场景至关重要。一旦处理不好，就可能会导致数据库被打垮。那么，如果一定要在MySQL这个层面上，抗住高并发的热点数据并发更新，有什么方案呢？拿库存扣减举例 1、库存拆分，把一个大的库存拆分成多个小库存，拆分后，一次扣减动作就可以分散到不同的库、表中进行，降低锁粒度提升并发。优点：实现较简单缺点：存在碎片问题、库存调控不方便 2、请求合并（缓冲记账），把多个库存扣减请求，合并成一个，进行批量更新。优点：简单缺点：适用于异步场景，或者经过分析后认为可以合并的场景 3、把update转换成insert，直接插入一次占用记录，然后异步统计剩余库存，或者通过SQL统计流水方式计算剩余库存。优点：没有update，无锁冲突缺点：insert时控制不好容易超卖、insert后剩余库存不好统计除了上面这三个方案外，重点介绍一个我们公司内部在用的，扛了双十一的高并发的秒杀的方案。那就是改造MySQL 主要思路就是，针对于频繁更新或秒杀类业务场景，大幅度优化对于热点行数据的update操作的性能。当开启热点更新自动探测时，系统会自动探测是否有单行的热点更新，如果有，则会让大量的并发 update 排队执行，以减少大量行锁造成的并发性能下降。也就是说，他们改造了MySQL数据库，让同一个热点行的更新语句，在执行层进行排队。这样的排队相比update的排队，要轻量级很多，因为他不需要自旋，不需要抢锁。这个方案的好处就是开发不需要做额外的事情，只需要开启热点检测就行了。缺点就是改造MySQL数据库有成本。不过现在很多云上数据库都支持了。如：腾讯云数据库MySQL热点更新： https://cloud.tencent.com/document/product/236/63239 阿里云数据库Inventory Hint： https://www.alibabacloud.com/help/zh/apsaradb-for-rds/latest/inventory-hint 具体原理见： ✅阿里的数据库能抗秒杀的原理扩展知识批次更新的具体实现请求合并，把多个库存扣减请求，合并成一个，进行批量更新。这个方案的本质上是想将实时处理的并发操作转变为批量处理，以减少数据库的压力并提高效率。这种操作通常分为两个步骤：首先是收集和汇总积分，然后是定期更新用户积分表。以下是这两个步骤的示例 SQL 代码：收集和汇总积分在这一步中，你可以将需要增加的积分记录在一个临时表或一个专门用于积分变更的表中。假设这个表名为 pending_points，结构如下： 1 2 3 4 5 CREATE TABLE pending_points ( user_id INT, -- 用户ID points_to_add INT, -- 待增加的积分 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 记录创建时间 ); 每当有积分变更时，你只需要往这个表中插入一条记录： ...

MySQL用了函数一定会索引失效吗？

典型回答很多人认为，用了函数就不能走索引了。主要是因为索引是按照列值的原始顺序进行组织和存储的。当对列应用函数时（如进行数学运算、字符串操作或日期函数等），函数操作的结果会改变原始数据的值或格式，这使得数据库无法直接在索引树中定位到这些经过函数转换后的值。因此，数据库不得不回退到全表扫描，以确保能够评估所有行上的函数操作，从而导致查询性能下降。但是在MySQL 8.0之后就不一定了，因为有了函数索引，他就是用来优化函数的。 MySQL 8.0 引入“功能索引”（Functional Indexes）的新特性，也别叫做函数索引。功能索引允许在创建索引时包含列上的表达式，这意味着你可以对数据进行某种计算或转换，并对结果建立索引。这样，即使查询条件中使用了函数操作，仍然可以利用这些索引来优化查询性能。 **函数索引不是直接在表的列上创建的，而是基于列的某个表达式创建的。**这个表达式可以是简单的数学运算，也可以是字符串函数、日期函数等。创建了函数索引后，MySQL 可以在执行涉及该表达式的查询时使用这个索引，从而提高查询效率。使用方式假设我们有一个employees表，里面有first_name和last_name两个字段，我们希望能够快速查询基于这两个字段合并后的全名。在 MySQL 8.0 中，我们可以创建一个基于first_name和last_name合并后的表达式的函数索引，如下所示： 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) ); --创建函数索引 CREATE INDEX idx_full_name ON employees ((CONCAT(first_name, ' ', last_name))); --插入一条记录 insert into employees(first_name,last_name) values ('Hollis','Chuang'); 在上述示例中，idx_full_name就是一个函数索引，它基于first_name和last_name字段的组合（即全名）。 ...

MySQL的Hash Join是什么？

典型回答 hash join 是 MySQL 8.0.18版本中新推出的一种多表join的算法。在这之前，MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，而嵌套循环的算法其实性能是比较差的，而Hash Join的出现就是要优化Nested-Loop Join的。 ✅为什么大厂不建议使用多表join？所谓Hash Join，其实是因为他底层用到了Hash表。 Hash Join 是一种针对 equal-join 场景的优化，他的基本思想是将驱动表数据加载到内存，并建立 hash 表，这样只要遍历一遍非驱动表，然后再去通过哈希查找在哈希表中寻找匹配的行，就可以完成 join 操作了。举个栗子。 1 2 3 4 SELECT student_name,school_name FROM students LEFT JOIN schools ON students.school_id=schools.id; 以上，是一个left join的SQL，在Hash Join过程中，主要分为两个步骤，分别是构建和探测。构建阶段，假如优化器优化后使用students作为驱动表，那么就会把这个驱动表的数据构建到hash表中：探测阶段，在这个过程中，从school表中取出记录之后，去hash表中查询，找到匹配的数据，在做聚合就行了。需要注意的时候，上面的Hash表是在内存中的，但是，内存是有限的（通过join_buffer_size限制），如果内存中存不下驱动表的数据怎么办呢？扩展知识基于磁盘的hash join 如果驱动表中的数据量比较大，没办法一次性的加载到内存中，就需要考虑把这些数据存储在磁盘上。通过将哈希表的一部分存储在磁盘上，分批次地加载和处理数据，从而减少对内存的需求。在这样的算法中，为了避免一个大的hash表内存中无法完全存储，那么就采用分表的方式来实现，即首先利用 hash 算法将驱动表进行分表，并产生临时分片写到磁盘上。这样就相当于把一张驱动表，拆分成多个hash表，并且分别存储在磁盘上。接下来就是做join了，在这个过程中，会对被驱动表使用同样的 hash 算法进行分区，确定好在哪个分区之后，先确认下这个分区是否已经加载到内存，如果已经加载，则直接在内存中的哈希表中进行查找匹配的行。 ...

MySQL的优化器的索引成本是怎么算出来的？

典型回答 ✅为什么MySQL会选错索引，如何解决？在上面的文章中，我们介绍过，MySQL 是基于成本来选择索引的，并且也列举了一些可能会影响成本的因素，那么具体到细节上，这个成本是如何计算出来的呢？包括哪些内容呢？其实在 MySQL中，一条 SQL 的成本主要就是包含了 CPU 的成本和 IO的成本两部分 1 Cost = CPU Cost + IO Cost CPU Cost 表示计算的开销，通过select * from mysql.server_cost查看（MySQL 8.0）主要包含了： disk_temptable_create_cost：创建磁盘临时表的成本 disk_temptable_row_cost：磁盘临时表中每条记录的成本 key_compare_cost：索引键值比较的成本 memory_temptable_create_cost：创建内存临时表的成本 memory_temptable_row_cost：内存临时表中每条记录的成本 row_evaluate_cost：记录间的比较成本可以看到，创建临时表的成本是最高的，索引键值比较的成本比较低。 IO Cost 表示引擎层 IO 的开销，通过select * from mysql.engine_cost查看（MySQL 8.0）主要包含了： io_block_read_cost：从磁盘读取一个页的成本 memory_block_read_cost：从内存读取一个页的成本可以看到，从磁盘中读取一个页的成本（1）是从内存中读取一个页的成本（0.25）的4倍。当我们想看一个 SQL 的执行成本时，可以通过 explain FORMAT=json 的方式来查看，得到的结果如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 { "query_block": { "select_id": 1, "cost_info": { "query_cost": "4.55" }, "table": { "table_name": "collection_inventory_stream", "access_type": "ALL", "rows_examined_per_scan": 43, "rows_produced_per_join": 43, "filtered": "100.00", "cost_info": { "read_cost": "0.25", "eval_cost": "4.30", "prefix_cost": "4.55", "data_read_per_join": "68K" }, "used_columns": [ "id", "gmt_create", "gmt_modified", "collection_id", "changed_quantity", "price", "quantity", "state", "saleable_inventory", "occupied_inventory", "stream_type", "identifier", "deleted", "lock_version" ] } } } 主要关注 cost_info 即可， ...

MySQL的数据存储一定是基于硬盘的吗？

典型回答不是的，MySQL也可以基于内存的，即MySQL的内存表技术。它允许将数据和索引存储在内存中，从而提高了检索速度和修改数据的效率。优点包括具有快速响应的查询性能和节约硬盘存储空间。此外，使用内存表还可以实现更高的复杂性，从而提高了MySQL的整体性能。创建内存表与创建普通表一样，使用CREATE TABLE语句，但需要将存储引擎设置为：ENGINE = MEMORY 扩展知识什么是数据库存储引擎？数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。查看mysql当前使用什么存储引擎:show engines; 查看mysql当前默认的存储引擎:show variables like ‘%storage_engine%’; 查看看某个表用了什么引擎:show create table 表名;，在显示结果里参数engine后面的就表示该表当前用的存储引擎 MySQL的存储引擎是基于表的还是基于数据库的？表 MySQL中如何指定引擎 1、创建表时，可以通过ENGINE来指定存储引擎，在create语句最后加上“engine=存储引擎;”即可； create table table1(id int(11) primary key auto_increment)engine=MyISAM; 2、修改表时，可以使用“alter table 表名 engine=存储引擎;”来指定存储引擎。 alter table table1 engine=InnoDB; MySQL支持哪几种执行引擎，有什么区别 MySQL是开源的，我们可以基于其源码编写我们自己的存储引擎，有以下几种存储引擎 MyISAM、InnoDB、NDB、MEMORY，Archieve、Fedarated以及Maria等。对比如下： Innodb和MyISAM有什么区别？ ✅InnoDB和MyISAM有什么区别？

MySQL的行级锁锁的到底是什么？

典型回答数据库的行级锁根据锁的粒度不同，可以叫做不同的名字。 Record Lock表示记录锁，锁的是索引记录。 Gap Lock是间隙锁，锁的是索引记录之间的间隙。 Next-Key Lock是Record Lock和Gap Lock的组合，同时锁索引记录和间隙。他的范围是左开右闭的。扩展知识 Record Lock **Record Lock，翻译成记录锁，是加在索引记录上的锁。**例如，SELECT c1 FROM t WHERE c1 = 10 For UPDATE;会对c1=10这条记录加锁，为了防止任何其他事务插入、更新或删除c1值为10的行。 Gap Lock Gap Lock，翻译成间隙锁，他指的是在索引记录之间的间隙上的锁，或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。那么，这里所谓的Gap（间隙）又怎么理解呢？ Gap指的是InnoDB的索引数据结构中可以插入新值的位置。当你用语句SELECT…FOR UPDATE锁定一组行时。InnoDB可以创建锁，应用于索引中的实际值以及他们之间的间隙。例如，如果选择所有大于10的值进行更新，间隙锁将阻止另一个事务插入大于10的新值。（实际会锁到+∞，这里为了演示什么是gap简化了一下）既然是锁，那么就可能会影响到数据库的并发性，所以，间隙锁只有在Repeatable Reads这种隔离级别中才会起作用。在Repeatable Reads这种隔离下，对于锁定的读操作（select … for update 、 lock in share mode)、update操作、delete操作时，会进行如下的加锁：对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不会锁定间隙。对于其他搜索条件，InnoDB锁定扫描的索引范围，使用gap lock或next-key lock来阻塞其他事务插入范围覆盖的间隙。也就是说，对于SELECT FOR UPDATE、LOCK IN SHARE MODE、UPDATE和DELETE等语句处理时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁，即锁住其扫描的范围。 Next-Key Lock Next-Key锁是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。假设一个索引包含值10、11、13和20。此索引可能的next-key锁包括以下区间: 1 2 3 4 5 (-∞, 10] (10, 11] (11, 13] (13, 20] (20, ∞ ] 对于最后一个间隙，∞不是一个真正的索引记录，因此，实际上，这个next-key锁只锁定最大索引值之后的间隙。 ...

MySQL索引一定遵循最左前缀匹配吗？

典型回答 ✅什么是最左前缀匹配？为什么要遵守？因为索引底层是一个B+树，如果是联合索引的话，在构造B+树的时候，会先按照左边的key进行排序，左边的key相同时再依次按照右边的key排序。所以，在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。这也就是最左前缀匹配。 MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了。因为8.0.13中引入了索引跳跃扫描。扩展知识索引跳跃扫描 MySQL 8.0.13 版本中，对于range查询（什么是range后面会提到)，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。通过一个例子给大家解释一下，首先有下面这样一张表（参考了MySQL官网的例子，但是我做了些改动和优化）： 1 2 3 4 5 6 7 8 9 CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL); CREATE INDEX idx_t on t1(f1,f2); INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (2,2), (2,3), (2,4), (2,5); INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; 通过上面的SQL，先创建一张t1表，并把f1,f2两个字段设置为联合索引。之后再向其中插入一些记录。 ...

MySQL能保证数据100%不丢吗？

典型回答这种绝对性的问题，答案肯定是不能的。首先，MySQL有很多引擎，其中一部分是基于磁盘的，比如Innodb，Myisam等，但是也有基于内存的，比如Memory，而基于内存的这种如果断电了，可能就丢数据了。那么，基于磁盘的就万无一失了么？其实也不是。日志要写入磁盘要经过几个过程：如果你看过下面这篇文章，你会知道，MySQL为了保证数据不丢，在事务写入的时候做了2阶段提交。 ✅什么是事务的2阶段提交？这里面画图的时候其实是考虑的默认情况的，啥意思呢，先来看2个MySQL的参数。 innodb_flush_log_at_trx_commit是MySQL InnoDB存储引擎独有的参数，用于控制InnoDB的Redo log日志记录方式。取值范围为0、1、2： 0：只写入LogBuffer，不会把Redo日志写入磁盘，而是靠InnoDB的后台线程每秒写入磁盘。 1（默认值）：写入LogBuffer，并立即将LogBuffer数据写入磁盘缓冲区并刷盘。 2：写入LogBuffer，并立即将Redo日志写入操作系统的磁盘缓冲区，每秒由操作系统调度刷盘一次。 sync_binlog是MySQL Binlog日志的重要参数，用于控制Binlog的更新策略。取值范围 0、1 或 N（正整数）： 0：事务提交后仅将Binlog写入文件系统缓存，依赖操作系统调度刷盘。 1（默认值）：每次事务提交后立即将Binlog写入磁盘。 >1：每N个事务提交后，立即将Binlog写入磁盘。那么也就是说，如果你了解操作系统的write和fsync指令的话，翻译一下就是这样的： ✅write和fsync的区别是什么？ **innodb_flush_log_at_trx_commit = 1** **innodb_flush_log_at_trx_commit = 0** **innodb_flush_log_at_trx_commit = 2** 动作写LogBuffer**write()** 到 OS Cache**fsync()** 到磁盘写LogBuffer 写 Log Buffer** **write()** 到 OS Cache** **sync_binlog**** = 1** **sync_binlog**** = 0** **sync_binlog**** > 1** 动作写LogBuffer**write()** 到 OS Cache**fsync()** 到磁盘写LogBuffer 写LogBuffer每 **N** 个事务提交后，才将 binlog **write()** 到 Page Cache 并执行 **fsync()** 到刷盘。那么也就意味着，如果你想让数据不丢，至少要把innodb_flush_log_at_trx_commit和sync_binlog都设置为1，让他们立刻写入磁盘并刷盘。 ...

order by 是怎么实现的？

典型回答 order by 是做排序的，具体怎么排取决于优化器的选择，如果优化器认为走索引更快，那么就会用索引排序，否则，就会使用filesort (执行计划中extra中提示：using filesort），但是能走索引排序的情况并不多，并且确定性也没有那么强，很多时候，还是走的filesort。 filesort这种排序方式中，如果需要排序的内容比较少，就会基于内存中的sort_buffer，否则就需要使用临时文件进行排序了。并且在实际排序过程中，如果字段长度并不是特别长，那么就会使用全字段排序的方式直接在sort_buffer中排序后返回结果集。如果字段长度特别长，那么就可能基于空间考虑，采用row_id排序，这样就会在排序后进行二次回表后返回结果集。索引排序我们都知道，索引是天然有序的，所以当我们在使用order by的时候，如果能借助索引，那么效率一定是最高的。并且MySQL确实也可以基于索引进行order by的查询，但是这个过程是否一定用索引，完全取决于优化器的选择。 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE `t2` ( `id` INT(11), `a` varchar(64) NOT NULL, `b` varchar(64) NOT NULL, `c` varchar(64) NOT NULL, `d` varchar(64) NOT NULL, `f` varchar(64) DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY `f` (`f`), KEY `idx_abc` (`a`,`b`,`c`) KEY `idx_a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 假设有以上这样一张表，在排序时，可能出现的情况如下（因为有优化器的干预，以下结果并不一定可以100%复现。我自己实验的时候是可以的，我的环境是MySQL 5.7，）： ...

SQL语句如何实现insertOrUpdate的功能？

典型回答在 MySQL 中，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE语句实现 insertOrUpdate 功能。需要注意：在on duplicate key时，会在前一个索引值到当前值加临键锁（RR下），极容易造成死锁。要使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 语句，需要满足以下条件：表必须有主键或唯一索引；插入的数据必须包含主键或唯一索引列；主键或唯一索引列的值不能为 NULL。举个栗子：假设有一个 student 表，包含 id、name 和 age 三列，其中 id 是主键。现在要插入一条数据，如果该数据的主键已经存在，则更新该数据的姓名和年龄，否则插入该数据。 1 2 INSERT INTO student (id, name, age) VALUES (1, 'Alice', 20) ON DUPLICATE KEY UPDATE name='Alice', age=20; 扩展知识实现原理 INSERT INTO ... ON DUPLICATE KEY UPDATE ，如果数据库中已存在具有相同唯一索引或主键的记录，则更新该记录。其底层原理和执行流程如下： ...

留言给博主