MySQL Database design Summary

Copyright notice: This article by the lacquer Honkela original article, reprint please indicate the source:
Article original link: https://www.qcloud.com/community/article/119

Source: Tengyun https://www.qcloud.com/community

Rule 1: The MyISAM storage engine can be selected in general, and the InnoDB storage engine must be used if transaction support is required.

Note: The MyISAM storage Engine B-tree index has a big limit: the sum of the lengths of all fields participating in an index cannot exceed 1000 bytes. In addition MyISAM data and indexes are separate, while INNODB data storage is sorted by cluster (cluster) index, the primary key is the default clustered (cluster) index, so MyISAM although in general, the query performance is higher than InnoDB, However, InnoDB's query performance with the primary key condition is very high.

Rule 2: Naming rules.

1. The database and table names should be the same as the business module names they serve

2. A class of tables that serve the same submodule should prefix or suffix the submodule name (or part of the word) as far as possible

3. The table name should contain the words corresponding to the stored data as far as possible

4. Field names should also be kept as much as possible relative to the actual data

5. The federated index name should contain as much as possible all the index key field names or abbreviations, and the order of each field name in the index name should match the index key in the index, and try to include an idx-like prefix or suffix to indicate that the period object type is an index.

6. Other objects, such as constraints, should include, as far as possible, the names of the owning table or other objects to indicate their relationship

Rule 3: Database field type definition

1. Often need to calculate and sort CPU-consuming fields, should try to select more rapid fields, such as with TIMESTAMP (4 bytes, the minimum 1970-01-01 00:00:00) instead Datetime (8 bytes, the minimum value of 1001-01-01 00:00:00), Replacing floating-point and character types with integral types

2. Variable Length field varchar , do not usechar

3. For binary multimedia data, pipelining queue data (such as logs), large text data is not placed in database fields

Rule 4: The business logic execution process must have an initial value in the table to be read. Avoid the value of the business read out as negative or infinite causes the program to fail

Rule 5: Do not need to obey the paradigm theory, moderate redundancy, let query minimize join

Rule 6: A large large segment with low frequency access splits the data table. Some large characters occupy a lot of space, access frequency is significantly less than other fields, this situation is split, frequent queries do not need to read big pieces, resulting in the waste of IO resources.

Rule 7: Large tables can consider horizontal splitting. Large tables affect query efficiency, there are many split methods according to business characteristics, such as data based on time increment, can be divided according to time. Data divided by ID can be split according to the number of id% databases.

I. Database indexing

Rule 8: The relevant index for business needs is determined by the where condition of the SQL statement constructed by the actual design, the business does not need to build an index, and does not allow more than one field in the federated index (or primary key). In particular, the field does not appear in a conditional statement at all.

Rule 9: One field or multiple fields that uniquely determine a record to establish a primary key or a unique index, you cannot uniquely identify a record and create a generic index for improved query efficiency

Rule 10: The table used by the business, some records are few, or even only one record, in order to constrain the need to establish an index or set the primary key.

Rule 11: For values that cannot be duplicated, a field that is frequently used as a query condition should have a unique index (the primary key default unique index), and the criteria for that field in the query criteria be placed in the first position. There is no need to establish a federated index associated with the field.

Rule 12: For fields that are queried frequently, the values are not unique, and you should also consider establishing a normal index in which the field condition is placed in the first position and the same method for the Federated index Processing.

Rule 13: When the business accesses data through a non-unique index, it is important to consider the density of records returned through that index value, which in principle may not be more dense than 0.2, and if the density is too high, it is not appropriate to index it.

When the amount of data found through this index accounts for more than 20% of all the data in the table, the cost of establishing the index needs to be considered, and because the index scan produces random I/O, the efficiency is much lower than the sequential I/O of the full table sequential scan. This index may not be used when the database system optimizes query.

Rule 14: Databases that require federated indexes (or federated primary keys) should be aware of the order of the indexes. The matching criteria in the SQL statement are also consistent with the order of the indexes.

Note: The incorrect flow of the index can also lead to serious consequences.

Rule 15: Multiple field queries in a table are query criteria, do not contain other indexes, and field union values are not duplicated, and a unique federated index can be built on these fields, assuming the index fields (a1,a2,... an), the query criteria (a1 op val1,a2 op val2,...am op valm)m<=n can be used for the index, The position of the field in the query condition is consistent with the position of the field in the index.

Rule 16: Establishment of a federated index (the following is assumed to establish a federated index on the field a,b,c of the database table (A,B,C))

1. The fields in the federated index should be as much as possible in order to filter the data from many to fewer, i.e. the field with the largest variance should be the first field in the House

2. Indexing as much as possible is consistent with the condition order of the SQL statement, so that the SQL statement is as conditional as the entire index as possible, avoiding the query as part of the index (especially if the first condition is inconsistent with the first field of the index)

3. Where a=1,where a>=12 and a<15,where a=1 and b<5 ,where a=1 and b=7 and c>=40为条件可以用到此联合索引；而这些语句where b=10,where c=221,where b>=12 and c=2则无法用到这个联合索引。

4. When the database fields that need to be queried are all reflected in the index, the database can directly query the index to get the query information without scanning the whole table (this is called key-only), can greatly improve the query efficiency.
   Index can be used when A,AB,ABC is associated with a query in another table field

5. Indexes can be used when A,AB,ABC order by or group is not B,C,BC,AC for sequential execution

6. Table scans and sorting may be more efficient than using federated indexes when:
   A. The table has been organized by index
   B. A large proportion of all data in the data station being queried.

Rule 17: When a key business accesses a data table. However, when data cannot be accessed through an index, it should be ensured that the number of sequential access records is limited and in principle no more than 10.

Two. Query statement and Application system optimization

Rule 18: Construct query statements reasonably

1. Insert statement, according to the test, bulk insertion of 1000 at the most efficient, more than 1000, to split, multiple times the same insert, should be combined batch. Note the length of the query statement is less than the mysqld parameter Max_allowed_packet

2. The performance order of various logical operators in a query condition is and,or,in, so you should avoid using in large collections in the query condition as much as possible.

3. Always drive large recordsets with small result sets, because in MySQL, only the nested join is a join method, which means that the MySQL join is implemented through nested loops. Reduce the number of loops in a nested loop by using the principle of small result sets to drive large recordsets to reduce the total number of IO and CPU operations

4. Optimize the inner loop of the nested join as much as possible.

5. Take only the columns you need and try not to use SELECT *

6. Use only the most efficient filter fields, where the filter condition in the WHERE clause is less good

7. Avoid complicated joins and subqueries as much as possible

   MySQL in concurrency this is not too good, when the concurrency is too high, the overall performance will drop sharply, which is mainly related to MySQL internal resource contention lock control, myisam with table lock, InnoDB better use row lock.

Rule 19: Optimization of the application system

1. Using the cache wisely, the performance increase is an order of magnitude for the less-changed portions of active data that are cached in memory through the application layer's cache.

2. Reduce the number of IO times by merging the same query repeatedly.

3. Transaction Correlation Minimum principle

MySQL Database design Summary