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

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, so in the field data type selection, you can use the number of numbers do not use the string, of course, the time type should be replaced with bigint, do not recommend the use of the text type, in the varchar field is recommended to create default values, such as: "Default", because where to use Are null is a full table scan, numeric types also need to add default values, such as num int default 0, if you do not add the default value, and the execution of the INSERT statement, do not assign a value to the field, which do update xxx set num = num + 1 o'clock, you will find that SQL does not error , then the value of NUM is not updated, and the field that is queried as a condition is indexed.

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 TABLE chatmsg (CID bigint primary Key,cmsgsenduserid Bigint,cmsgreceiveruserid bigint,ctime bigint,ccontent varchar (+) NOT null default "', Cext varchar" Engine=myisam default Charset=utf8 collate=utf8_binpartition by RAN GE (CID) (PARTITION p0 values less THAN (1000000), PARTITION p1 values less THAN (5000000), PARTITION p2 values less THAN (1 000000), PARTITION P3 VALUES less THAN MAXVALUE); create index senduserid_index on chatmsg (Cmsgsenduserid); CREATE INDEX Rec Eiverid_index on Chatmsg (cmsgreceiveruserid), CREATE index ctime_index on chatmsg (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 (1) from user where UID = message sender idunion All select COUNT (1) from user where UID = 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 now the table has produced 50 million data, 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 Cid,ctime,ccontent,cext from chatmsg where Cmsgsenduserid = + Cmsgreceiveruserid = 200union Allselect cid,cti Me,ccontent,cext from chatmsg where Cmsgsenduserid = $ and Cmsgreceiveruserid = 100

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.

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