MySQL Tens data table, creating table and field extensions for several suggestions

One: Overview

When we design a system, we need to consider the system after a period of time, the table data volume about how much, if in the early days, it can be estimated that a certain number of table data is very large (such as chat message table), it is necessary to create a table, this article from the creation of tables, add data, as well as field extension, which

Two: Create a table

If now we need to create IM project Chat message table, this table data volume, read operation far more than write operation, we all know, MySQL commonly used database engine mainly has INNODB,MYISAM, these two database engine main difference is, INNODB support transaction, support foreign key, Locks are row-level locks (row-level locks are only for primary keys, non-primary keys will also lock the full table), MyISAM does not support transactions, does not support foreign key constraints, locks are table-level locks, from a performance perspective, MyISAM is better than InnoDB, so on the database engine, I choose MyISAM, In addition, the message sends the user ID and the message receives the user ID on the index.

1: Selection of data types

Because the amount of data is very large, in the field data type selection, you can use the numbers do not use the string, of course, the time type should be replaced with bigint, in addition to the field as a condition query to index.

2: Table Partitioning

In the face of big data, in addition to the data type and performance has a great relationship, we can also use table partitioning, table and sub-Library is currently not used, table partitioning concept

2.1 Table Partitioning Concepts

Range Partition: Assigns multiple rows to a partition based on column values that belong to a given contiguous interval.

List partitioning: Similar to the range partition, the difference is that a list partition is selected based on a value in a set of discrete values that matches a column value.

Hash partition: A partition that is selected based on the return value of a user-defined expression that is evaluated using the column values of those rows that will be inserted into the table.

Key partitioning: Similar to partitioning by hash, the difference is that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns containing > integer values.

You can use show VARIABLES like '%partition% ' to determine the type of partition supported by MySQL.

Now I use the range partition, the partition field is PK, the complete SQL statement is as follows

CREATE TABLEchatmsg (CIDbigint Primary Key, Cmsgsenduseridbigint, Cmsgreceiveruseridbigint, CTimebigint, Ccontentvarchar( -) not NULL default "', Cextvarchar( the) ) ENGINE=MYISAMDEFAULTCHARSET=UTF8 COLLATE=utf8_binpartition byRANGE (CID) (PARTITION p0VALUESLess THAN (1000000), PARTITION p1VALUESLess THAN (5000000), PARTITION p2VALUESLess THAN (1000000), PARTITION P3VALUESLess THAN MAXVALUE);Create IndexSenduserid_index onchatmsg (Cmsgsenduserid);Create IndexReceiverid_index onchatmsg (Cmsgreceiveruserid);Create IndexCtime_index onChatmsg (CTime);

Three: Add chat history.

As seen from the build statement, we do not use foreign keys, so we need to check the integrity of FOREIGN key constraints manually.

Select Count (1fromuserwhere=  message Sender IDUnion All SelectCount(1fromuserwhere= message receiver ID

The Add statement can be executed when the above statement returns a result equal to 2 o'clock. Refine the query statement, you can refer to my article: million Data volume optimization scheme

Four: Extended fields

If the table has already produced 50 million data, the product manager too, Xiao Wang, chat records need to add a read or unread status, if at this time in the formal use of the environment to alter TABLEADD column, you can imagine this operation is more time-consuming, it is possible that the database directly crashes may be, The data volume is large, the Alter TABLEADD column operation database really crashed, not alarmist, remember when the table, we created a cext field, this field we record a JSON string, in fact, the correct way to add a version number, I'm not adding a version number here. The table contains the following data:

Select  from where =  -  and =  $ Union  All Select  from where =  $  and =  -

This method solves most of the extended fields, queries out the Cext, and then converts the value to the image. If the new field needs to appear in the where, it needs to be analyzed according to the actual situation.

Cext Extended Field Benefits:

(1) Ability to dynamically expand properties at any time

(2) Both old and new data can exist simultaneously

(3) Easy to migrate data, write a small program to change the old version of Ext to the new version of Ext, and modify version

Cext extension field is insufficient:

(1) fields in Cext cannot be indexed

(2) The key value in Cext has a lot of redundancy, it is recommended that the key is shorter

V: Other

For example, the initial project, product manager said, Xiao Wang, I choose any two users, query the two people chat record, need to return the nickname of the two users, the product manager chose two users, we got the ID of the two users, if the direct Chat table join user table, the performance is equally bad, In this case, we might consider using space-time, such as creating a nickname for the receiver and sender directly in the chat table. This method means that big data tables do not join as much as possible, performance is not good, and there are other ways to solve this problem. Of course, in the formal project, the specific situation also needs to be analyzed concretely.

I would also like to add some ideas, if the text has a description of the wrong place, I hope to point out, thank you, welcome to express their ideas, we progress together.

MySQL Tens data table, creating table and field extensions for several suggestions