Mysql多表查询问题

Question

场景：
系统每天会生成一个mysql数据表，里面存储的当天的模块访问量，假设字段为pv。如何统计一个月的模块访问量呢？每个表的结构都是一样的。

非常感谢！

Answer 1

1. If the amount of data per day is not particularly large, you can divide the tables by month, so that the problem only needs to be queried and counted in one table;
2. Use views to create multi-table views, but if the amount of data is large, it will take a long time.
3. In the case of large amounts of data, relational databases are not a good choice just for recording statistics. You can consider NoSQL, such as Hbase.

Answer 2

= =
MySQL data tables cannot be built infinitely. What is your design? . .
It is recommended to go home and read a book on database design first. . .
If we put them all in one table from the beginning, it wouldn't be so painful at all, right? ? ?
Have you considered performance and maintainability issues? ? ?

Answer 3

Try creating a view with multiple tables

Answer 4

select count(1) from pv_150506
union all
select count(1) from pv_150507
union all
........

foreach result set, summed up.

Answer 5

Create a data table every day? Are you crazy

Answer 6

Let’s assume that he automatically creates tables on a monthly basis, and each table has a lot of data

Search like this

When querying, join the tables and merge the query
Dynamically generate query statements

Answer 7

This expression is also earth-shattering

Answer 8

The log record table is designed in such a way that frequent insertions will lock the query operation. In Linux, you can write a shell script to count pv visits.

Answer 9

Brother, was this built under the guidance of sub-table thinking?

Answer 10

Actually, I think depending on the user scenario, if the data in a day is really millions of rows, creating tables in different days is not necessarily a bad decision.