MySql records a large number of table shards

Background description: MySql used by the database. There is a device status record table in the project, which records the status values of each property of the device at a certain time. All device status records in the system are inserted into this table. Problem description: There are many devices, and many records are inserted every day. Now... background description: MySql used by the database. The project has a device status record table, which records the status values of each property of the device at a certain time. This table is inserted for all device status records in the system..

Problem description: There are many devices, and many records are inserted every day.Query records by device and by Time Interval. Currently, the table has a large amount of data and the query speed is slow.
1. How to optimize the change?
2. Table sharding is available on the Internet. If you plan to split tables by timeHow to query data by device and time after table shardingWhat about it?

Reply content:

Background description: MySql used by the database. There is a device status record table in the project, which records the status values of each property of the device at a certain time.This table is inserted for all device status records in the system..

Problem description: There are many devices, and many records are inserted every day.Query records by device and by Time Interval. Currently, the table has a large amount of data and the query speed is slow.
1. How to optimize the change?
2. Table sharding is available on the Internet. If you plan to split tables by timeHow to query data by device and time after table shardingWhat about it?

Be sure to meet your business needs! Be sure to meet your business needs! Be sure to meet your business needs!

1. If you have not performed an index, we recommend that you create an index.

2. For example, if most of your business logic only queries the latest device status records, then the old data you have a separate table, or a table like every year, depends on your data size.

If you want to split tables by time, for example, by monthly, you can set the table name to: 201601, 201602 ,...
Then, you can index the fields of the device type in each table (assuming the field name is platform). The general idea of the query is as follows:

$ Date = $ _ POST ['date']; // query time, which must be consistent with the table name rule $ table = $ date; $ platform =$ _ POST ['platform']; // The queried device type $ where = "'platfrom' = '$ platform '"; $ query = "select * from $ table $ where ";

You can also remove the need for table sharding. After mysql5.5, you can try partitioning by month or by year.

If you query this situation, you can split the table. Because the query condition has a date, it is certainly not possible to query the table by date (the query may be performed in multiple tables ), you can also query by device. You can directly split the table by device (number), such as the device id, put the data of 10 thousand devices into the same table, or put the data of one thousand devices into one table, that is to say, if the integer part of the result of device No./10000 is the same as that of the table, the table name is obtained first, and then the result that matches the date range is queried in the table.