来自推荐一个程序员编程资料站http://cxyroad.com副业赚钱专栏https://xbt100.top2024年IDEA最新激活方法后台回复激活码CSDN免登录复制代码插件下载CSDN复制插件以下是正文。背景网上看了一篇文章《为什么说MySQL单表行数不要超过2000w》亲自实践了一下跟原作者有不同的结论。原文的结论是2000W左右性能会成指数级的下降而我的结论是随着数据量成倍地增加查询的时间也刚好是成倍增加是成正比的。我并不会直接搬运网上的文章和结论下边的实践过程是参考文章的实践方式进行优化的。原文的理论感觉是正确的但为啥我实践的结果不支持他的理论动手能力强的小伙伴可以照的我的实践过程试试。前置条件查看sql语句执行时间和效率show profiles; # 是mysql提供可以用来分析当前会话中语句执行的资源消耗情 况。可以用来SQL的调优测量。 select have_profiling # 查看是否支持profiling set profiling 1; # 设置MySQL支持profile select count(*) from tmp.person; #执行自己的sql语句 show profiles; 就可以查到sql语句的执行时间效果如下mysql set profiling 1; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql select count(*) from tmp.person;----------| count(*) |----------| 2 |----------1 row in set (0.00 sec)mysql show profiles;-------------------------------------------------------| Query_ID | Duration | Query |-------------------------------------------------------| 1 | 0.00017775 | select count(*) from tmp.person |------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)实验建一张表drop database if exists tmp; create database tmp; use tmp; CREATE TABLE person( id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment 主键, person_id tinyint not null comment 用户id, person_name VARCHAR(200) comment 用户名称, gmt_create datetime comment 创建时间, gmt_modified datetime comment 修改时间 ) comment 人员信息表;插入一条数据insert into person values(1,1,user_1, NOW(), now());利用 mysql 伪列 rownum 设置伪列起始点为 1select (i:i1) as rownum, person_name from person, (select i:100) as init; set i1;运行下面的 sql连续执行 20 次就是 2 的 20 次方约等于 100w 的数据执行 23 次就是 2 的 23 次方约等于 800w , 如此下去即可实现千万测试数据的插入如果不想翻倍翻倍的增加数据而是想少量少量的增加有个技巧就是在 SQL 的后面增加limit条件如limit 100控制将要新增的数据量。insert into person(id, person_id, person_name, gmt_create, gmt_modified) select i:i1, left(rand()*10,10) as person_id, concat(user_,i%2048), date_add(gmt_create,interval i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval i*cast(rand()*100 as signed) SECOND), interval cast(rand()*1000000 as signed) SECOND) from person;此处需要注意的是也许你在执行到近 800w 或者 1000w 数据的时候会报错The total number of locks exceeds the lock table size这是由于你的临时表内存设置的不够大只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size 512*1024*1024; #512M SET global innodb_buffer_pool_size 1*1024*1024*1024; # (1G);验证select count(1) from person; select count(1) from person where person_id 6; show profiles;优化测试MySQL函数ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)这是因为mysql 默认不允许创建自定义函数安全性的考虑此时我们需要将参数 log_bin_trust_function_creators 设置为开启状态show variables like log_bin_trust_function_creators;set global log_bin_trust_function_creators 1;但这样只是临时设置重启终端后该设置即会失效。如果要配置永久的需要在配置文件的 [mysqld] 上配置以下属性 log_bin_trust_function_creators1-- 随机产生字符串 drop function if exists rand_string; -- 先判断是否已存在同名函数如果已存在则先删除 DELIMITER $$ -- 两个 $$ 表示结束 create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ; declare return_str varchar(255) default ; declare i int default 0; while i n do set return_str concat(return_str, substring(chars_str, floor(1rand()*52), 1)); set ii1; end while; return return_str; end $$ DELIMITER ;-- 随机生成编号 drop function if exists rand_num; DELIMITER $$ create function rand_num() returns int(5) begin declare i int default 0; set ifloor(100rand()*10); return i; end $$ DELIMITER ;自定义函数的调用和其他普通函数的调用一样示例如下select rand_string(5); select rand_num();一键测试drop database if exists tmp; create database tmp;use tmp;CREATE TABLE person( id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment 主键, person_id tinyint not null comment 用户id, person_name VARCHAR(200) comment 用户名称, gmt_create datetime comment 创建时间, gmt_modified datetime comment 修改时间) comment user info;SET profiling 0;SET profiling_history_size 0;SET profiling_history_size 100; SET profiling 1;insert into person values(1,1,user_1, NOW(), now());show profiles;set i1;drop function if exists test_performance;DELIMITER $$ #设置结束符create function test_performance(num int) returns varchar(255) begin declare return_str varchar(255) default ; if(num 0) then insert into person(id, person_id, person_name, gmt_create, gmt_modified) select i:i1, left(rand()*10,10) as person_id, concat(user_,i%2048), date_add(gmt_create,interval i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval i*cast(rand()*100 as signed) SECOND), interval cast(rand()*1000000 as signed) SECOND) from person limit num; else insert into person(id, person_id, person_name, gmt_create, gmt_modified) select i:i1, left(rand()*10,10) as person_id, concat(user_,i%2048), date_add(gmt_create,interval i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval i*cast(rand()*100 as signed) SECOND), interval cast(rand()*1000000 as signed) SECOND) from person; end if; select count(1) into return_str from person where person_id 9; select count(1) into return_str from person; return return_str; end $$DELIMITER ;select test_performance(0); #2^1select test_performance(0); #2^2select test_performance(0); #2^3select test_performance(0); #2^4select test_performance(0); #2^5select test_performance(0); #2^6select test_performance(0); #2^7select test_performance(0); #2^8select test_performance(0); #2^9select test_performance(0); #2^10select test_performance(0); #2^11select test_performance(0); #2^12select test_performance(0); #2^13select test_performance(0); #2^14select test_performance(0); #2^15select test_performance(0); #2^16select test_performance(0); #2^17select test_performance(0); #2^18select test_performance(0); #2^19次方524288select test_performance(475712); #补上475712凑够100wselect test_performance(250000); #125wselect test_performance(0); #250wselect test_performance(0); #500wselect test_performance(0); #1kwselect test_performance(0); #2kwselect test_performance(0); #4kwselect test_performance(0); #8kwselect test_performance(0); #16kwselect test_performance(0); #32kw实验结果数据量有查询条件无查询条件125w0.13090750.08538975250w0.252130250.18290725500w0.48162550.358393751kw0.944938750.68090152kw1.8787881.446316754kw5.408157253.053568258kw11.0742426.651798516kw22.75385217.948613252kw46.3604122536.5971315Figure_1理论单表数量限制首先我们先想想数据库单表行数最大多大CREATE TABLE person( id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY comment 主键, person_id tinyint not null comment 用户id, person_name VARCHAR(200) comment 用户名称, gmt_create datetime comment 创建时间, gmt_modified datetime comment 修改时间 ) comment 人员信息表;看看上面的建表 sqlid 是主键本身就是唯一的也就是说主键的大小可以限制表的上限如果主键声明 int 大小也就是 32 位那么支持 2^32-1 ~~21 亿如果是 bigint那就是 2^62-1 36893488147419103232难以想象这个的多大了一般还没有到这个限制之前可能数据库已经爆满了有人统计过如果建表的时候自增字段选择无符号的 bigint , 那么自增长最大值是 18446744073709551615按照一秒新增一条记录的速度大约什么时候能用完图片表空间下面我们再来看看索引的结构对了我们下面讲内容都是基于 Innodb 引擎的大家都知道 Innodb 的索引内部用的是 B 树图片这张表数据在硬盘上存储也是类似如此的它实际是放在一个叫 person.ibd innodb data的文件中也叫做表空间虽然数据表中他们看起来是一条连着一条但是实际上在文件中它被分成很多小份的数据页而且每一份都是 16K。大概就像下面这样当然这只是我们抽象出来的在表空间中还有段、区、组等很多概念但是我们需要跳出来看。图片页的数据结构因为每个页只有 16K 的大小但是如果数据很多那一页肯定就放不下这些数据那数据肯定就会被分到其他的页中所以为了把这些页关联起来肯定就会有记录前后页地址方便找到对应页同时每页都是唯一的那就会需要有一个唯一标志来标记页就是页号页中会记录数据所以会存在读写操作读写操作会存在中断或者其他异常导致数据不全等那就会需要有校验机制所以里面还有会校验码而读操作最重要的就是效率问题如果按照记录一个个进行遍历那肯定是很费劲的所以这里面还会为数据生成对应的页目录Page Directory; 所以实际页的内部结构像是下面这样的。图片从图中可以看出一个 InnoDB 数据页的存储空间大致被划分成了 7 个部分有的部分占用的字节数是确定的有的部分占用的字节数是不确定的。在页的 7 个组成部分中我们自己存储的记录会按照我们指定的行格式存储到 User Records 部分。但是在一开始生成页的时候其实并没有 User Records 这个部分每当我们插入一条记录都会从 Free Space 部分也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分当 Free Space 部分的空间全部被 User Records 部分替代掉之后也就意味着这个页使用完了如果还有新的记录插入的话就需要去申请新的页了。这个过程的图示如下。图片刚刚上面说到了数据的新增的过程。那下面就来说说数据的查找过程假如我们需要查找一条记录我们可以把表空间中的每一页都加载到内存中然后对记录挨个判断是不是我们想要的在数据量小的时候没啥问题内存也可以撑但是现实就是这么残酷不会给你这个局面为了解决这问题mysql 中就有了索引的概念大家都知道索引能够加快数据的查询那到底是怎么个回事呢下面我就来看看。索引的数据结构在 mysql 中索引的数据结构和刚刚描述的页几乎是一模一样的而且大小也是 16K, 但是在索引页中记录的是页 (数据页索引页) 的最小主键 id 和页号以及在索引页中增加了层级的信息从 0 开始往上算所以页与页之间就有了上下层级的概念。图片看到这个图之后是不是有点似曾相似的感觉是不是像一棵二叉树啊对没错它就是一棵树只不过我们在这里只是简单画了三个节点2 层结构的而已如果数据多了可能就会扩展到 3 层的树这个就是我们常说的 B 树最下面那一层的 page level 0, 也就是叶子节点其余都是非叶子节点。图片看上图中我们是单拿一个节点来看首先它是一个非叶子节点索引页在它的内容区中有 id 和 页号地址两部分这个 id 是对应页中记录的最小记录 id 值页号地址是指向对应页的指针而数据页与此几乎大同小异区别在于数据页记录的是真实的行数据而不是页地址而且 id 的也是顺序的。单表建议值下面我们就以 3 层2 分叉实际中是 M 分叉的图例来说明一下查找一个行数据的过程。比如说我们需要查找一个 id6 的行数据因为在非叶子节点中存放的是页号和该页最小的 id所以我们从顶层开始对比首先看页号 10 中的目录有 [id1, 页号 20],[id5, 页号 30], 说明左侧节点最小 id 为 1右侧节点最小 id 是 565, 那按照二分法查找的规则肯定就往右侧节点继续查找找到页号 30 的节点后发现这个节点还有子节点非叶子节点那就继续比对同理6567, 所以找到了页号 60找到页号 60 之后发现此节点为叶子节点数据节点于是将此页数据加载至内存进行一一对比结果找到了 id6 的数据行。从上述的过程中发现我们为了查找 id6 的数据总共查询了三个页如果三个页都在磁盘中未提前加载至内存那么最多需要经历三次的磁盘 IO。需要注意的是图中的页号只是个示例实际情况下并不是连续的在磁盘中存储也不一定是顺序的。图片至此我们大概已经了解了表的数据是怎么个结构了也大概知道查询数据是个怎么的过程了这样我们也就能大概估算这样的结构能存放多少数据了。从上面的图解我们知道 B 数的叶子节点才是存在数据的而非叶子节点是用来存放索引数据的。所以同样一个 16K 的页非叶子节点里的每条数据都指向新的页而新的页有两种可能• 如果是叶子节点那么里面就是一行行的数据• 如果是非叶子节点的话那么就会继续指向新的页假设• 非叶子节点内指向其他页的数量为 x• 叶子节点内能容纳的数据行数为 y• B 数的层数为 z如下图中所示 Total x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。图片X 在文章的开头已经介绍了页的结构索引也也不例外都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum Supermum26 byte、File Trailer8byte, 再加上页目录大概 1k 左右我们就当做它就是 1K, 那整个页的大小是 16K, 剩下 15k 用于存数据在索引页中主要记录的是主键与页号主键我们假设是 Bigint (8 byte), 而页号也是固定的4Byte, 那么索引页中的一条数据也就是 12byte; 所以 x15*1024/12≈1280 行。Y叶子节点和非叶子节点的结构是一样的同理能放数据的空间也是 15k但是叶子节点中存放的是真正的行数据这个影响的因素就会多很多比如字段的类型字段的数量每行数据占用空间越大页中所放的行数量就会越少这边我们暂时按一条行数据 1k 来算那一页就能存下 15 条Y≈15。算到这边了是不是心里已经有谱了啊 根据上述的公式Total x^(z-1) y已知 x1280,y15 假设 B 树是两层那就是 Z 2 Total 1280 ^1 15 19200 假设 B 树是三层那就是 Z 3 Total 1280 ^2 *15 24576000 约 2.45kw哎呀妈呀这不是正好就是文章开头说的最大行数建议值 2000w 嘛对的一般 B 数的层级最多也就是 3 层你试想一下如果是 4 层除了查询的时候磁盘 IO 次数会增加而且这个 Total 值会是多少大概应该是 3 百多亿吧也不太合理所以3 层应该是比较合理的一个值。到这里难道就完了不我们刚刚在说 Y 的值时候假设的是 1K 那比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据 同样还是按照 Z3 的值来计算那 Total 1280 ^2 *3 4915200 近 500w所以在保持相同的层级相似查询性能的情况下在行数据大小不同的情况下其实这个最大建议值也是不同的而且影响查询性能的还有很多其他因素比如数据库版本服务器配置sql 的编写等等MySQL 为了提高性能会将表的索引装载到内存中。在 InnoDB buffer size 足够的情况下其能完成全加载进内存查询不会有问题。但是当单表数据库到达某个量级的上限时导致内存无法存储其索引使得之后的 SQL 查询会产生磁盘 IO从而导致性能下降所以增加硬件配置比如把内存当磁盘使可能会带来立竿见影的性能提升哈。总结1. Mysql 的表数据是以页的形式存放的页在磁盘中不一定是连续的。2. 页的空间是 16K, 并不是所有的空间都是用来存放数据的会有一些固定的信息如页头页尾页码校验码等等。3. 在 B 树中叶子节点和非叶子节点的数据结构是一样的区别在于叶子节点存放的是实际的行数据而非叶子节点存放的是主键和页号。4. 索引结构不会影响单表最大行数2kw 也只是推荐值超过了这个值可能会导致 B 树层级更高影响查询性能。上边理论是原文的我的实践结果是随着数据量成倍地增加查询的时间也刚好是成倍增加是成正比的。END推荐阅读副业赚钱推荐让你的时间开始变现免费体验AI图片生成就在 Image Generator Hub程序员在线工具站cxytools.com 推荐一个自己写的工具站https://cxytools.com专为程序员设计包括时间日期、 JSON处理、SQL格式化、随机字符串生成、UUID生成、文本Hash...等功能提升开发效 率。 ⬇戳阅读原文直达 朕已阅
谁说 MySQL 单表行数不要超过 2000W？
来自推荐一个程序员编程资料站http://cxyroad.com副业赚钱专栏https://xbt100.top2024年IDEA最新激活方法后台回复激活码CSDN免登录复制代码插件下载CSDN复制插件以下是正文。背景网上看了一篇文章《为什么说MySQL单表行数不要超过2000w》亲自实践了一下跟原作者有不同的结论。原文的结论是2000W左右性能会成指数级的下降而我的结论是随着数据量成倍地增加查询的时间也刚好是成倍增加是成正比的。我并不会直接搬运网上的文章和结论下边的实践过程是参考文章的实践方式进行优化的。原文的理论感觉是正确的但为啥我实践的结果不支持他的理论动手能力强的小伙伴可以照的我的实践过程试试。前置条件查看sql语句执行时间和效率show profiles; # 是mysql提供可以用来分析当前会话中语句执行的资源消耗情 况。可以用来SQL的调优测量。 select have_profiling # 查看是否支持profiling set profiling 1; # 设置MySQL支持profile select count(*) from tmp.person; #执行自己的sql语句 show profiles; 就可以查到sql语句的执行时间效果如下mysql set profiling 1; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql select count(*) from tmp.person;----------| count(*) |----------| 2 |----------1 row in set (0.00 sec)mysql show profiles;-------------------------------------------------------| Query_ID | Duration | Query |-------------------------------------------------------| 1 | 0.00017775 | select count(*) from tmp.person |------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)实验建一张表drop database if exists tmp; create database tmp; use tmp; CREATE TABLE person( id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment 主键, person_id tinyint not null comment 用户id, person_name VARCHAR(200) comment 用户名称, gmt_create datetime comment 创建时间, gmt_modified datetime comment 修改时间 ) comment 人员信息表;插入一条数据insert into person values(1,1,user_1, NOW(), now());利用 mysql 伪列 rownum 设置伪列起始点为 1select (i:i1) as rownum, person_name from person, (select i:100) as init; set i1;运行下面的 sql连续执行 20 次就是 2 的 20 次方约等于 100w 的数据执行 23 次就是 2 的 23 次方约等于 800w , 如此下去即可实现千万测试数据的插入如果不想翻倍翻倍的增加数据而是想少量少量的增加有个技巧就是在 SQL 的后面增加limit条件如limit 100控制将要新增的数据量。insert into person(id, person_id, person_name, gmt_create, gmt_modified) select i:i1, left(rand()*10,10) as person_id, concat(user_,i%2048), date_add(gmt_create,interval i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval i*cast(rand()*100 as signed) SECOND), interval cast(rand()*1000000 as signed) SECOND) from person;此处需要注意的是也许你在执行到近 800w 或者 1000w 数据的时候会报错The total number of locks exceeds the lock table size这是由于你的临时表内存设置的不够大只需要扩大一下设置参数即可。SET GLOBAL tmp_table_size 512*1024*1024; #512M SET global innodb_buffer_pool_size 1*1024*1024*1024; # (1G);验证select count(1) from person; select count(1) from person where person_id 6; show profiles;优化测试MySQL函数ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)这是因为mysql 默认不允许创建自定义函数安全性的考虑此时我们需要将参数 log_bin_trust_function_creators 设置为开启状态show variables like log_bin_trust_function_creators;set global log_bin_trust_function_creators 1;但这样只是临时设置重启终端后该设置即会失效。如果要配置永久的需要在配置文件的 [mysqld] 上配置以下属性 log_bin_trust_function_creators1-- 随机产生字符串 drop function if exists rand_string; -- 先判断是否已存在同名函数如果已存在则先删除 DELIMITER $$ -- 两个 $$ 表示结束 create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ; declare return_str varchar(255) default ; declare i int default 0; while i n do set return_str concat(return_str, substring(chars_str, floor(1rand()*52), 1)); set ii1; end while; return return_str; end $$ DELIMITER ;-- 随机生成编号 drop function if exists rand_num; DELIMITER $$ create function rand_num() returns int(5) begin declare i int default 0; set ifloor(100rand()*10); return i; end $$ DELIMITER ;自定义函数的调用和其他普通函数的调用一样示例如下select rand_string(5); select rand_num();一键测试drop database if exists tmp; create database tmp;use tmp;CREATE TABLE person( id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment 主键, person_id tinyint not null comment 用户id, person_name VARCHAR(200) comment 用户名称, gmt_create datetime comment 创建时间, gmt_modified datetime comment 修改时间) comment user info;SET profiling 0;SET profiling_history_size 0;SET profiling_history_size 100; SET profiling 1;insert into person values(1,1,user_1, NOW(), now());show profiles;set i1;drop function if exists test_performance;DELIMITER $$ #设置结束符create function test_performance(num int) returns varchar(255) begin declare return_str varchar(255) default ; if(num 0) then insert into person(id, person_id, person_name, gmt_create, gmt_modified) select i:i1, left(rand()*10,10) as person_id, concat(user_,i%2048), date_add(gmt_create,interval i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval i*cast(rand()*100 as signed) SECOND), interval cast(rand()*1000000 as signed) SECOND) from person limit num; else insert into person(id, person_id, person_name, gmt_create, gmt_modified) select i:i1, left(rand()*10,10) as person_id, concat(user_,i%2048), date_add(gmt_create,interval i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval i*cast(rand()*100 as signed) SECOND), interval cast(rand()*1000000 as signed) SECOND) from person; end if; select count(1) into return_str from person where person_id 9; select count(1) into return_str from person; return return_str; end $$DELIMITER ;select test_performance(0); #2^1select test_performance(0); #2^2select test_performance(0); #2^3select test_performance(0); #2^4select test_performance(0); #2^5select test_performance(0); #2^6select test_performance(0); #2^7select test_performance(0); #2^8select test_performance(0); #2^9select test_performance(0); #2^10select test_performance(0); #2^11select test_performance(0); #2^12select test_performance(0); #2^13select test_performance(0); #2^14select test_performance(0); #2^15select test_performance(0); #2^16select test_performance(0); #2^17select test_performance(0); #2^18select test_performance(0); #2^19次方524288select test_performance(475712); #补上475712凑够100wselect test_performance(250000); #125wselect test_performance(0); #250wselect test_performance(0); #500wselect test_performance(0); #1kwselect test_performance(0); #2kwselect test_performance(0); #4kwselect test_performance(0); #8kwselect test_performance(0); #16kwselect test_performance(0); #32kw实验结果数据量有查询条件无查询条件125w0.13090750.08538975250w0.252130250.18290725500w0.48162550.358393751kw0.944938750.68090152kw1.8787881.446316754kw5.408157253.053568258kw11.0742426.651798516kw22.75385217.948613252kw46.3604122536.5971315Figure_1理论单表数量限制首先我们先想想数据库单表行数最大多大CREATE TABLE person( id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY comment 主键, person_id tinyint not null comment 用户id, person_name VARCHAR(200) comment 用户名称, gmt_create datetime comment 创建时间, gmt_modified datetime comment 修改时间 ) comment 人员信息表;看看上面的建表 sqlid 是主键本身就是唯一的也就是说主键的大小可以限制表的上限如果主键声明 int 大小也就是 32 位那么支持 2^32-1 ~~21 亿如果是 bigint那就是 2^62-1 36893488147419103232难以想象这个的多大了一般还没有到这个限制之前可能数据库已经爆满了有人统计过如果建表的时候自增字段选择无符号的 bigint , 那么自增长最大值是 18446744073709551615按照一秒新增一条记录的速度大约什么时候能用完图片表空间下面我们再来看看索引的结构对了我们下面讲内容都是基于 Innodb 引擎的大家都知道 Innodb 的索引内部用的是 B 树图片这张表数据在硬盘上存储也是类似如此的它实际是放在一个叫 person.ibd innodb data的文件中也叫做表空间虽然数据表中他们看起来是一条连着一条但是实际上在文件中它被分成很多小份的数据页而且每一份都是 16K。大概就像下面这样当然这只是我们抽象出来的在表空间中还有段、区、组等很多概念但是我们需要跳出来看。图片页的数据结构因为每个页只有 16K 的大小但是如果数据很多那一页肯定就放不下这些数据那数据肯定就会被分到其他的页中所以为了把这些页关联起来肯定就会有记录前后页地址方便找到对应页同时每页都是唯一的那就会需要有一个唯一标志来标记页就是页号页中会记录数据所以会存在读写操作读写操作会存在中断或者其他异常导致数据不全等那就会需要有校验机制所以里面还有会校验码而读操作最重要的就是效率问题如果按照记录一个个进行遍历那肯定是很费劲的所以这里面还会为数据生成对应的页目录Page Directory; 所以实际页的内部结构像是下面这样的。图片从图中可以看出一个 InnoDB 数据页的存储空间大致被划分成了 7 个部分有的部分占用的字节数是确定的有的部分占用的字节数是不确定的。在页的 7 个组成部分中我们自己存储的记录会按照我们指定的行格式存储到 User Records 部分。但是在一开始生成页的时候其实并没有 User Records 这个部分每当我们插入一条记录都会从 Free Space 部分也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分当 Free Space 部分的空间全部被 User Records 部分替代掉之后也就意味着这个页使用完了如果还有新的记录插入的话就需要去申请新的页了。这个过程的图示如下。图片刚刚上面说到了数据的新增的过程。那下面就来说说数据的查找过程假如我们需要查找一条记录我们可以把表空间中的每一页都加载到内存中然后对记录挨个判断是不是我们想要的在数据量小的时候没啥问题内存也可以撑但是现实就是这么残酷不会给你这个局面为了解决这问题mysql 中就有了索引的概念大家都知道索引能够加快数据的查询那到底是怎么个回事呢下面我就来看看。索引的数据结构在 mysql 中索引的数据结构和刚刚描述的页几乎是一模一样的而且大小也是 16K, 但是在索引页中记录的是页 (数据页索引页) 的最小主键 id 和页号以及在索引页中增加了层级的信息从 0 开始往上算所以页与页之间就有了上下层级的概念。图片看到这个图之后是不是有点似曾相似的感觉是不是像一棵二叉树啊对没错它就是一棵树只不过我们在这里只是简单画了三个节点2 层结构的而已如果数据多了可能就会扩展到 3 层的树这个就是我们常说的 B 树最下面那一层的 page level 0, 也就是叶子节点其余都是非叶子节点。图片看上图中我们是单拿一个节点来看首先它是一个非叶子节点索引页在它的内容区中有 id 和 页号地址两部分这个 id 是对应页中记录的最小记录 id 值页号地址是指向对应页的指针而数据页与此几乎大同小异区别在于数据页记录的是真实的行数据而不是页地址而且 id 的也是顺序的。单表建议值下面我们就以 3 层2 分叉实际中是 M 分叉的图例来说明一下查找一个行数据的过程。比如说我们需要查找一个 id6 的行数据因为在非叶子节点中存放的是页号和该页最小的 id所以我们从顶层开始对比首先看页号 10 中的目录有 [id1, 页号 20],[id5, 页号 30], 说明左侧节点最小 id 为 1右侧节点最小 id 是 565, 那按照二分法查找的规则肯定就往右侧节点继续查找找到页号 30 的节点后发现这个节点还有子节点非叶子节点那就继续比对同理6567, 所以找到了页号 60找到页号 60 之后发现此节点为叶子节点数据节点于是将此页数据加载至内存进行一一对比结果找到了 id6 的数据行。从上述的过程中发现我们为了查找 id6 的数据总共查询了三个页如果三个页都在磁盘中未提前加载至内存那么最多需要经历三次的磁盘 IO。需要注意的是图中的页号只是个示例实际情况下并不是连续的在磁盘中存储也不一定是顺序的。图片至此我们大概已经了解了表的数据是怎么个结构了也大概知道查询数据是个怎么的过程了这样我们也就能大概估算这样的结构能存放多少数据了。从上面的图解我们知道 B 数的叶子节点才是存在数据的而非叶子节点是用来存放索引数据的。所以同样一个 16K 的页非叶子节点里的每条数据都指向新的页而新的页有两种可能• 如果是叶子节点那么里面就是一行行的数据• 如果是非叶子节点的话那么就会继续指向新的页假设• 非叶子节点内指向其他页的数量为 x• 叶子节点内能容纳的数据行数为 y• B 数的层数为 z如下图中所示 Total x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。图片X 在文章的开头已经介绍了页的结构索引也也不例外都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum Supermum26 byte、File Trailer8byte, 再加上页目录大概 1k 左右我们就当做它就是 1K, 那整个页的大小是 16K, 剩下 15k 用于存数据在索引页中主要记录的是主键与页号主键我们假设是 Bigint (8 byte), 而页号也是固定的4Byte, 那么索引页中的一条数据也就是 12byte; 所以 x15*1024/12≈1280 行。Y叶子节点和非叶子节点的结构是一样的同理能放数据的空间也是 15k但是叶子节点中存放的是真正的行数据这个影响的因素就会多很多比如字段的类型字段的数量每行数据占用空间越大页中所放的行数量就会越少这边我们暂时按一条行数据 1k 来算那一页就能存下 15 条Y≈15。算到这边了是不是心里已经有谱了啊 根据上述的公式Total x^(z-1) y已知 x1280,y15 假设 B 树是两层那就是 Z 2 Total 1280 ^1 15 19200 假设 B 树是三层那就是 Z 3 Total 1280 ^2 *15 24576000 约 2.45kw哎呀妈呀这不是正好就是文章开头说的最大行数建议值 2000w 嘛对的一般 B 数的层级最多也就是 3 层你试想一下如果是 4 层除了查询的时候磁盘 IO 次数会增加而且这个 Total 值会是多少大概应该是 3 百多亿吧也不太合理所以3 层应该是比较合理的一个值。到这里难道就完了不我们刚刚在说 Y 的值时候假设的是 1K 那比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据 同样还是按照 Z3 的值来计算那 Total 1280 ^2 *3 4915200 近 500w所以在保持相同的层级相似查询性能的情况下在行数据大小不同的情况下其实这个最大建议值也是不同的而且影响查询性能的还有很多其他因素比如数据库版本服务器配置sql 的编写等等MySQL 为了提高性能会将表的索引装载到内存中。在 InnoDB buffer size 足够的情况下其能完成全加载进内存查询不会有问题。但是当单表数据库到达某个量级的上限时导致内存无法存储其索引使得之后的 SQL 查询会产生磁盘 IO从而导致性能下降所以增加硬件配置比如把内存当磁盘使可能会带来立竿见影的性能提升哈。总结1. Mysql 的表数据是以页的形式存放的页在磁盘中不一定是连续的。2. 页的空间是 16K, 并不是所有的空间都是用来存放数据的会有一些固定的信息如页头页尾页码校验码等等。3. 在 B 树中叶子节点和非叶子节点的数据结构是一样的区别在于叶子节点存放的是实际的行数据而非叶子节点存放的是主键和页号。4. 索引结构不会影响单表最大行数2kw 也只是推荐值超过了这个值可能会导致 B 树层级更高影响查询性能。上边理论是原文的我的实践结果是随着数据量成倍地增加查询的时间也刚好是成倍增加是成正比的。END推荐阅读副业赚钱推荐让你的时间开始变现免费体验AI图片生成就在 Image Generator Hub程序员在线工具站cxytools.com 推荐一个自己写的工具站https://cxytools.com专为程序员设计包括时间日期、 JSON处理、SQL格式化、随机字符串生成、UUID生成、文本Hash...等功能提升开发效 率。 ⬇戳阅读原文直达 朕已阅
