How to create and optimize the index organizational structure in MySQL (1)

Analyze how to design the storage structure, how to manipulate the stored data, and how to reduce the operation cost or cost and minimize the system overhead based on the data access requirements in a real production environment. At the same time, let more beginners understand how table indexes stored in data are organized and hope to serve as a reference template.

1. Test Case Description

The test case is in the B2C field. A product order information table is used to store the purchased items. However, some other fields are removed for testing and the data items in the table are not described in detail, the field meanings are shown in the table:

 
 

  
  
USE 'test ';
  
  
Drop table if exists 'test'. 'Goods _ order ';
  
  
Create table 'goods _ order '(
  
  
'Order _ id' int unsigned not null comment 'order No ',
  
  
'Goods _ id' int unsigned not null default '0' COMMENT 'item No ',
  
  
'Order _ type' tinyint unsigned not null default '0' COMMENT 'order type ',
  
  
'Order _ status' tinyint unsigned not null default '0' COMMENT 'order status ',
  
  
'Color _ id' smallint unsigned not null default '0' COMMENT 'color id ',
  
  
'Size _ id' smallint unsigned not null default '0' comment' size id ',
  
  
'Goods _ number' mediumint unsigned not null default '0' comment' qty ',
  
  
'Bucket _ id' int unsigned not null default '0' COMMENT 'repository id ',
  
  
'Packet _ id' int unsigned not null default '0' COMMENT 'bit Code ',
  
  
'Gmt _ create' timestamp not null default '2017-00-00 00:00:00 'comment' add time ',
  
  
'Gmt _ modify' timestamp not null default '2017-00-00 00:00:00 'comment' update time ',
  
  
Primary key (order_id, 'goods _ id ')
  
  
) ENGINE = InnoDB AUTO_INCREMENT = 1 character set 'utf8' COLLATE 'utf8 _ general_ci ';
 
 

Where, the primary key information: primary key (order_id, 'goods _ id'), why the primary key index field order is: order_id, 'goods _ id', instead: what about 'goods _ id' and order_id? The reason is very simple. The repetition rate of goods_id in the order information table is higher than that of order_id, that is, the filtering rate of order_id is higher, which can reduce the number of scanned index records to achieve higher efficiency, the SQL statements that will be listed below also tell us that some SQL statements only contain the order_id FIELD IN THE WHERE clause. Therefore, we must use the field order_id as the header of the joint primary key index, 'Goods _ id' is the end of the Union primary key index.

Summary of data storage table design:

To design a table structure for data storage, you must first know which data items are included, that is, the data streams that are often used in the row, and the attributes of each data item, for example, the storage data type, value range and length, data integrity and other requirements, so as to determine the attribute definition of the data item. After the stored data item information is determined, perform the following three steps:

● First, determine which data items or combinations can be used as the unique identifier of the record;

● Second, determine what operations are performed on data records, how often each operation is performed, and differentiate foreground and background operations for websites and other types of applications, that is, the operations performed by external users, or internal user operations;

● Finally, we will analyze the filtering rate of data items as a condition for data record operations, that is, the proportion of different values of data items to the total number of data records, the closer the ratio is to 1, the better the screening rate and the value distribution rate;

To sum up, let the data modification operation take precedence over the read-only operation, you can create an index structure that meets the requirements and has good performance.

The Design of Data Access involves a very important piece of knowledge: the basic knowledge of relational databases and the paradigm of relational data theory. It is recommended that the differences between 1NF, 2NF, 3NF, and BCNF be learned from the knowledge points of the paradigm so far. The problems and defects to be circumvented must be clear, however, in a real work environment, do not rely on the paradigm for any access design. In a Buddhist sentence, the expression is accurate: NULL is color, and color is empty.