|
单实例mysql数据库中到底能建多少张表？ 业务两个同学今天就这个问题过来探讨，他们的诉求是： 1. 一个用户的表要全部放到一个数据库中。 2. 预计1000个用户每个用户初步规划1000张表。 这100W张表如何规划如何放的问题。 3. 后期扩容也要考虑。后期扩容要可扩、方便、且用再修改应用程序代码？ 面对这样一个场景，技术层面的很好解决，例如数据库到底能存多少张表？查官网资料即可确定。最重要的是要深入了解这么多表如何来的？来源是不是就有问题？有其他解决方案？这个需要深入的了解业务设计。这里不说业务，单说技术层面的。 mysql的配置项里是否有对一个数据库中，是否有最大数据表的限制，后面查询了相关资料发现，mysql数据库中，对单个数据库中的最大允许建立的数据表是没有配置项，也就是说没有限制的。 但是如果你执意要知道MYSQL能够建立多少个表的话，可以参考以下理论值的资料吧： 在mysql中，每个数据库最多可创建20亿个表，一个表允许定义1024列，每行的最大长度为8092字节（不包括文本和图像类型的长度）。 当表中定义有varchar、nvarchar或varbinary类型列时，如果向表中插入的数据行超过8092字节时将导致Transact-SQL语句失败，并产生错误信息。 ---- 这个可以用脚本模拟创建表进行测试。创建到20亿之后基本上就创建失败。脚本退出执行。原因待确定中。。。。。。 mysql单数据库对数据表限制数量的问题： 1.一个数据库是没有表现值的，或者说这个被限制的值很大 2.一个mysqld服务可以支持的表对象数量是，单一存储引擎InnoDB限制为40亿多点，所以这个限制基本上不需要考虑 从性能考虑的话，一个数据库建立多少张表适合的问题： 1.当一个数据库或实例中，表太多，意味着可能同时需要打开的表，从操作系统角度说就是：文件描述符很多，这个操作系统有限制的， 但是可以修改操作系统的内核参数达到。Linux最大打开文件数为65535个。 2.多少表性能就会下降，分2点阐述 2.1.分表多，意味着需要维护的表结构和统计信息多，一般情况下此不会成为任何瓶颈，但是太不合理，比如几万甚至更大，可能就有问题了 2.2.性能是否下降明显，最直接的就是：跟服务器的承载能力和数据量有关系，这个是最直接的 3.反问“为何要分很多表”呢？ 3.1.分表太多，容易造成程序逻辑复杂，降低性能，以及增加出错的风险点 3.2.若是表中的数据量（指容量，多少GB）很大，只是分表解决不了问题，因为在同一个服务器上，其物理IO能力最优先达到瓶颈 所以，一个数据库建立多少表，这个MYSQL是没有限制的。但是从性能考虑，肯定是有一定影响的，但是从IO和其他服务器性能考虑，这块的性能基本上还不足影响到很大成份。 所以建议大家一个数据库建立在二百个表以下（保守值，最多不要超过500个），这样也是比较合理的了。影响性能也不大。 第二个问题： 如果必须真有那么多表需要创建，需要做好多库，多数据盘规划： 例如表指定具体的其他盘的存储路径： mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'; #先检查一下配置变量 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec) 如果innodb_file_per_table值为OFF，可以动态修改： set global innodb_file_per_table=1; CREATE TABLE temp3 (empno smallint(4) not null, ename varchar(10), job varchar(9), mgr smallint(4), hiredate date, sal float(7, 2), comm float(7, 2), deptno tinyint(2) ) engine=innodb charset=utf8 data directory='/u01/mysql/temp/'; [root@test1 temp]# pwd /u01/mysql/temp [root@test1 temp]# ls temp2.MYD temp2.MYI temp.MYD temp.MYI test #由于表在test库下，所以在指定的目录里产生了个test目录 分区表： CREATE TABLE orders_list2 ( id INT AUTO_INCREMENT, customer_surname VARCHAR(30), store_id INT, salesperson_id INT, order_date DATE, note VARCHAR(500), INDEX idx (id) ) ENGINE = INNODB PARTITION BY LIST(store_id) ( PARTITION p1 VALUES IN (1, 3, 4, 17) INDEX DIRECTORY = '/var/orders/district1' DATA DIRECTORY = '/var/orders/district1', PARTITION p2 VALUES IN (2, 12, 14) INDEX DIRECTORY = '/var/orders/district2' DATA DIRECTORY = '/var/orders/district2', PARTITION p3 VALUES IN (6, 8, 20) INDEX DIRECTORY = '/var/orders/district3' DATA DIRECTORY = '/var/orders/district3', PARTITION p4 VALUES IN (5, 7, 9, 11, 16) INDEX DIRECTORY = '/var/orders/district4' DATA DIRECTORY = '/var/orders/district4', PARTITION p5 VALUES IN (10, 13, 15, 18) INDEX DIRECTORY = '/var/orders/district5' DATA DIRECTORY = '/var/orders/district5' ); 2. table_open_cache, innodb_open_files和open_files_limit 这3个mysql参数配置的尽可能大 不过mysqld启动之后，你看一下这3个变量，它们最终实际值是操作系统分配给mysqld进程的，并不一定能达到你配置的值。 因为 如果表文件(.frm, .ibd等）放到同一个目录，那么打开表都会很慢，因为每次打开一个表都要打开对应的2个表文件，文件系统效率会降低。 （如果这些表不是都用到，建议把不用的表归档然后从实例中删除。）如果这几十万张表都是有用的，那么你要考虑分库分表，以及考虑数据库设计是否合理，每个表是不是只有很少的数据，这些表能否合并，等等。否则，系统查询这三四十万张表的需要反复在table cache中淘汰打开的表才能打开更多的表，性能会降低很多。打开每个表需要打开2个文件，通常linux操作系统不允许一个进程打开的文件达到六十万到八十万个的，在这样的极限状态下操作系统的文件系统的运行效率也会大大降低。所以你需要把table_open_cache, innodb_open_files和open_files_limit 这3个mysql参数配置的尽可能大。 3. 规划Innodb表空间 从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。 原理性的东西； http://www./Linux/2015-01/111241.htm 操作步骤： http://blog.csdn.net/zm2714/article/details/8479974/ ———————————————— 版权声明：本文为CSDN博主「wulantian」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/wulantian/article/details/109598288 |
|
|
