MySQL optimization quad (optimized table structure)

MySQL optimization quad (optimized table)

I had too much to eat and stayed up until two o'clock in the morning last night. Today, the hair is messy, the face haggard, like drugs. In the afternoon to buy clothes, belly a look a lot. But the girlfriend has not yet one, already has the fat. I can't keep my mouth open.

I. Optimization of table structure

1. As far as possible to define a table field as a NOT NULL constraint, because the column containing null values in MySQL is difficult to query optimization, the null value makes the index and the index of the statistics is very complex, you can use 0 or an empty string instead.

2. You can use Enum, set, and other conforming data types. For fields that contain only a specific type. However, in the process of work is generally used to express the use of tinyint.

3. The comparison of numeric fields is much more efficient than strings, and the field types use the smallest and simplest data types possible. The IP address can use the INT type.

Second, table split 1, Vertical split

A vertical split is split by field, in fact, to separate the columns that make up a row into different tables that have different structures, with fewer columns in the split table, such as some fields in the user table that might be accessed frequently, and you can put these fields in a table. Other information that is infrequently used can be put into another table. The use of transactions when inserting, can also guarantee that the data of the two tables are consistent. The disadvantage is also obvious, because the split out of the two tables exist 1:1 of the relationship, need to use redundant fields, and the need to join operations, we can use the time to take two times, so that both can avoid join operation, but also improve efficiency.

2. Horizontal Split

Horizontal splitting is split by row, and the most common is the sub-database table. Take the user table as an example, you can take the user ID, and then use PHP decimal conversion 16 binary method dechex , intercept the first character, the user evenly allocated into the 0-9, a-f these 16 tables. It is also fast and convenient to find the same rules. Of course, there are many similar rules, and you can use the redundancy method to distribute the data into different tables according to the remainder. Some tables have strong business associations, so you can use time-based partitioning. Take one of my company's business as an example, every day to create a new table. This type of business requires high-speed insertion, but is less concerned about the efficiency of the query. The larger the table, the longer the index maintenance is required to insert the data.

Third, partition

Partitioning this concept, the first time I saw it in practice was in the last year, the Data center table was divided into a district by time one months. I read the data from these tables and then continue to do the subsequent business processing. In general, this zoning concept is rarely used in the main business, and partitioning is the product of large data processing. For example, the System user registration promotion and so on, will produce a large number of logs, of course, can also be set up in time to build more than one table, but in practice, there has been an operation and maintenance personnel forgot to switch the table, resulting in an emergency data error. The visible partition is suitable for the log record, the query is small, generally uses in the Background data report analysis. For these data aggregation requirements, a lot of log tables are needed to do the aggregating of data, we can tolerate 1s to 2s delay, as long as the data is accurate enough to meet the demand.

MySQL mainly supports 4 modes of partition: Range partition, list predefined lists partition, hash partition, key key value partition. After several did not see the data center colleagues used, may be the operation is not convenient, the application is not extensive, so for the moment do not speak.

-- 创建表create table testpar(    f_userid int unsigned not null default 0,    f_date datetime)engine=innodb,charset=utf8;-- 分区alter table testpar  partition by range columns(f_date) (    partition p0 values less than (‘2017-01-31‘),    partition p2 values less than (‘2017-02-20‘));-- 查看表结构mysql> show create table testpar\G*************************** 1. row ***************************       Table: testparCreate Table: CREATE TABLE `testpar` (  `f_userid` int(10) unsigned NOT NULL DEFAULT ‘0‘,  `f_date` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50500 PARTITION BY RANGE  COLUMNS(f_date)(PARTITION p0 VALUES LESS THAN (‘2017-01-31‘) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (‘2017-02-20‘) ENGINE = InnoDB) */1 row in set (0.00 sec)

MySQL optimization quad (optimized table structure)