mysql数据库中到底能建多少张表？（单实例下单个库）

单实例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.linuxidc.com/Linux/2015-01/111241.htm

       操作步骤：

       http://blog.csdn.net/zm2714/article/details/8479974/