千万级数据库多表查询解决方案

1 建立合理的索引,避免扫描多余数据，避免表扫描！

2使用子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。在这种情况下可以考虑用联接查询来取代。

3用EXISTS替代IN、用NOT EXISTS替代NOT IN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。无论在哪种情况下,NOT IN都是最低效的。因为它对子查询中的表执行了一个全表遍历。

deop快

rename table old_table to new_table

相当于create了一个new_table，将原表old_table的数据insert到new_table，然后drop掉原表old_table

首先这个日志不适合放在数据库，其次mysql就是慢，这么大的数据量，弄个从库专门用来统计，慢慢统计吧

1 sql优化

2 分表（垂直、水平）

3 内存存放

4 提高磁盘IO（如使用SSD）

5 负载均衡

6 CDN

7 +CPU/内存

数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。

可以做表拆分，减少单表字段数量，优化表结构。

在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

主要两种拆分 垂直拆分，水平拆分。

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对 那种 几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Product一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库 *** 作还是有IO瓶颈。不建议采用。

水平分库分表

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

水平分库分表切分规则

1 RANGE

从0到10000一个表，10001到20000一个表；

2 HASH取模

一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。

3 地理区域

比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

4 时间

按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

分库分表后面临的问题

事务支持

分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库join

只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题

这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

数据迁移，容量规划，扩容等问题

来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

ID问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由

一些常见的主键生成策略

UUID

使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。

Twitter的分布式自增ID算法Snowflake

在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

跨分片的排序分页

一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。

系统内有一只游戏日志表，每日以百万条数据增长，过段时间需要按照日期清理数据。同事使用delete循环删除过一次，时间久不说，表中的数据是删除了，但是查看服务器发现，idb文件大小居高不下，使用optimize table 表名 , 优化表以后，内存大小恢复正常。前前后后花费将近4个小时的时间。效率比较低，偶然想起TRUNCATE TABLE，决定使用以下方案，结果10分钟内，清除3千多万条废弃数据。记录以下，已备下次使用。

按以上步骤，可以解决锁表问题。

通过字符流把一次性把所有数据读出来

把数据split("|")后 放到一个数组里 下标为n%3==0的为第一种类型的数据 为1的是第二种类型

在连接数据库把数组里的数据insert到数据库里 就用原始的jdbc插入就很快了

数据库吧，你用什么数据库

mysql可以配合lucene做搜索引擎，还不够大可以用cluster一般你用like没问题，索引要做得好。

如果大家有异议，可以在后面补充。我会随时更新的。

现在大概列出如下:(望各位补充)

1数据库的设计

尽量把数据库设计的更小的占磁盘空间

1)尽可能使用更小的整数类型(mediumint就比int更合适)

2)尽可能的定义字段为not null,除非这个字段需要null（这个规则只适合字段为KEY的情形）

3)如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char（CHAR 总是比VARCHR快）

4)表的主索引应该尽可能的短这样的话每条纪录都有名字标志且更高效

5)只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。

（这条只适合MYISAM引擎的表，对于INNODB则在保存记录的时候关系不大，因为INNODB是以事务为基础的，如果想快速保存记录的话，特别是大批量的导入记录的时候）

6)所有数据都得在保存到数据库前进行处理。

7)所有字段都得有默认值。

8)在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。

(具体的表现为：MYISAM表的MERGE类型，以及MYISAM和INNODB通用的分区，详情见手册)

9)不会用到外键约束的地方尽量不要使用外键。

2系统的用途

1)及时的关闭对MYSQL的连接。

2)explain 复杂的SQL语句。（这样能确定你的SELECT 语句怎么优化最佳）

3)如果两个关联表要做比较话，做比较的字段必须类型和长度都一致（在数据庞大的时候建立INDEX）

4)LIMIT语句尽量要跟order by或者 distinct这样可以避免做一次full table scan

5)如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename

不过有一个问题，truncate 不会在事务处理中回滚。因为她要调用create table 语句。

（Truncate Table 语句先删除表然后再重建，这个是属于文件界别的，所以自然快N多）

实测例子：

song2为INNODB表。

mysql> select count(1) from song2;

+----------+

| count(1) |

+----------+

| 500000 |

+----------+

1 row in set (091 sec)

mysql> delete from song2;

Query OK, 500000 rows affected (1570 sec)

mysql> truncate table song2;

Query OK, 502238 rows affected (017 sec)

mysql>

{

这一点手册上有详细解释：

1329 TRUNCATE语法

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE用于完全清空一个表。从逻辑上说，该语句与用于删除所有行的DELETE语句等同，但是在有些情况下，两者在使用上有所不同。

对于InnoDB表，如果有需要引用表的外键限制，则TRUNCATE TABLE被映射到DELETE上；否则使用快速删减（取消和重新创建表）。使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器，设置时不考虑是否有外键限制。

对于其它存储引擎，在MySQL 51中，TRUNCATE TABLE与DELETE FROM有以下几处不同：

· 删减 *** 作会取消并重新创建表，这比一行一行的删除行要快很多。

· 删减 *** 作不能保证对事务是安全的；在进行事务处理和表锁定的过程中尝试进行删减，会发生错误。

· 被删除的行的数目没有被返回。

· 只要表定义文件tbl_namefrm是合法的，则可以使用TRUNCATE TABLE把表重新创建为一个空表，即使数据或索引文件已经被破坏。

· 表管理程序不记得最后被使用的AUTO_INCREMENT值，但是会从头开始计数。即使对于MyISAM和InnoDB也是如此。MyISAM和InnoDB通常不再次使用序列值。

· 当被用于带分区的表时，TRUNCATE TABLE会保留分区；即，数据和索引文件被取消并重新创建，同时分区定义（par）文件不受影响。

TRUNCATE TABLE是在MySQL中采用的一个Oracle SQL扩展。

}

6)能使用STORE PROCEDURE 或者 USER FUNCTION的时候（ROUTINE总是减少了服务器端的开销）

7)在一条insert语句中采用多重纪录插入格式而且使用load data infile来导入大量数据，这比单纯的indert快好多（在MYSQL中具体表现为：INSERT INTO TABLEQ VALUES (),(),();）

（还有就是在MYISAM表中插入大量记录的时候先禁用到KEYS后面再建立KEYS,具体表现语句：

ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;

而对于INNNODB 表在插入前先 set autocommit=0;完了后：set autocommit=1;这样效率比较高。）

8)经常OPTIMIZE TABLE 来整理碎片

9)还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。

3系统的瓶颈

1)磁盘搜索

并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间

2)磁盘读写(IO)

可以从多个媒介中并行的读取数据。

3)CPU周期

数据存放在主内存中这样就得增加CPU的个数来处理这些数据。

4)内存带宽

当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈

====

Another article more about tuning details:

>

在企业级大数据平台的建设中，从传统关系型数据库（如Oracle）向Hadoop平台汇聚数据是一个重要的课题。目前主流的工具有Sqoop、DataX、Oracle GoldenGate for Big Data等几种。Sqoop使用sql语句获取关系型数据库中的数据后，通过hadoop的MapReduce把数据从关系型数据库中导入数据到HDFS，其通过指定递增列或者根据时间戳达到增量导入的目的，从原理上来说是一种离线批量导入技术；DataX 直接在运行DataX的机器上进行数据的抽取及加载，其主要原理为：通过Reader插件读取源数据，Writer插件写入数据到目标 ，使用Job来控制同步作业，也是一种离线批量导入技术；Oracle Goldengate for Big Data抽取在线日志中的数据变化，转换为GGS自定义的数据格式存放在本地队列或远端队列中，并利用TCP/IP传输数据变化，集成数据压缩，提供理论可达到9：1压缩比的数据压缩特性，它简化了向常用大数据解决方案的实时数据交付，可以在不影响源系统性能的情况下将交易数据实时传入大数据系统。对比以上工具及方法，结合数据处理的准确性及实时性要求，我们评估Oracle Goldengate for Big Data基本可以满足当前大数据平台数据抽取的需求。

以上就是关于千万级数据库多表查询解决方案全部的内容，包括:千万级数据库多表查询解决方案、mysql数据库中的表数据量在千万级别。drop和rename哪个快一些、千万级的数据，统计分析，非常慢，怎么优化等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！