Shulou(Shulou.com)06/01 Report--

Overall directional thinking

1. Data architecture is related to capacity planning

a. The estimate of the total amount of data, with a period of one or two years, has a direct impact on hard disk capacity planning.

b. Whether to associate with other databases and environments, the associated data will affect the amount of data growth.

c. Data importance, whether to need a cluster, backup level requirements, unimportant data can choose to archive or clear old data regularly, hard disk requirements can be reduced

Ps: the importance of data can be divided into three categories: completely unlost, some acceptable, some acceptable, and all lost will not be affected.

two。 Performance dependent

a. According to the estimate of the average number of active connections, the more active connections are, the fewer queries will be, and the pressure on the database will come up.

b. Estimate QPS data, add, delete, query and modify frequently. The higher the QPS, the greater the pressure on the database.

c. Whether the code has the phenomenon of round-robin and repeated query, operating the database too frequently will obviously increase the pressure.

d. Whether the code has the logic of checking before changing and querying without filtering conditions, the logic of artificially increasing the pressure on the database is not advisable.

e. Whether the code has a caching mechanism, especially for static information, can significantly reduce the pressure on the database, but it depends on the framework design ideas.

3. Amount of growth

a. Estimates of data growth and concurrent growth, monthly and annual forecasts, the initial pressure is not understandable, but don't forget to estimate the long-term pressure

b. The estimation of the growth of direct business and related business, and the pressure on the order bank, as above, depends on the medium and long term, while the pressure attached to the related business is often ignored.

Pay attention to when designing tables

4. Whether the table structure is scientific or not

a. The table field avoids null values, which are difficult to query and take up extra index space. It is recommended that the default number 0 replace null.

b. Use the appropriate INT type instead of brainless BIGINT, plus UNSIGNED if non-negative (which doubles the numerical capacity), and of course it's better to use TINYINT, SMALLINT, and MEDIUM_INT.

c. If it is only a classified field (such as gender) or a field with less data difference (such as month), it is recommended to use enumerations or integers instead of string types

d. Try to use TIMESTAMP instead of DATETIME, because DATETIME gradually exits history

e. Do not have too many fields in a single table. It is recommended that it be less than 20, or the total of all fields in a single record should be controlled within 8K bytes, because innodb defaults to 16K in a data page and stores two rows of data. Exceeding this will cause row overflow and affect performance.

f. Use integers to store IP, phone number, body F card and other information, and then code splicing to achieve

g. Be careful to use the blog/text large field, because it is easy to cause memory overflow, try to use only the query field, or actively hide the field when querying, and display it as needed.

h. Be careful to use stored procedures, triggers and functions, because it consumes internal resources in the database, and it is better to use programs to implement them.

i. The association unity of character set, comment and field, the unity of character set and field can avoid type conversion and master-slave error, and the unification of annotation makes it easy to query its meaning.

5. Specification for design of index structure

a. The more indexes, the faster the query in theory, but the more space on the hard disk is taken up, and the slower the data is inserted (you have to write the index after writing the data), so the necessity of index should be carefully considered.

b. Do not use foreign keys, deleting and modifying fields will cause associated locking, which is extremely troublesome. It is enough to use program constraints as far as possible.

c. When establishing an index, we should pay attention to the comparison of data differences. Too few differences are not suitable for establishing independent indexes. We should establish a joint index with other fields, but we should pay attention to the leftmost matching principle to avoid duplicate indexes.

d. Try to avoid judging the null value of the field in the WHERE clause, otherwise it will cause the engine to give up using the index and do a full table scan

e. Try to build only a prefix index for the character field, and it is best not to be the primary key. Because the performance of the range query is poor, it is recommended to establish a self-increasing integer field as the primary key and the character field as the unique index.

f. Fields with too many insertions should avoid using unique indexes, because each insert will judge uniqueness and consume unnecessary performance, although queries will also judge, but this performance loss is much less and can be ignored.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.