Use military rules for mysql_db organized by the Department and sort out military rules for mysql_db

Use military rules for mysql_db organized by the Department and sort out military rules for mysql_db
Mysql_db military rules:

1. Developers and testers are prohibited from manually deleting and modifying data in the IDC environment.

 

2. Submit all requirements through the DB Tool System

 

3. Test in IDC environment DB is prohibited

 

4. the SQL statements submitted in the IDC environment must be verified in an informal environment and "explain SQL;" The Execution Plan has been indexed.

 

5. Create a database and table in the IDC environment in lower case. The database and table are abbreviated for short.

 

6. prohibit direct connection to DB from web machines

 

7. The creation and modification time of data to be saved for each record. A table usually has a primary key and innodb engine is used.

 

8. In the IDC environment, db only authorizes addition, query, and modification, and deletes the permission DBA evaluation.

 

9. Estimate and control the data volume of a single table within one million. If the data volume is too large, you need to clear or split the table.

 

10. mysql views, stored procedures, triggers, and user-defined functions are not allowed in the IDC environment.

 

 

I. One-sentence table Optimization

 

1. int type cannot exceed 1000 w, and char type cannot exceed 500 w

When numbers and characters cannot be loaded, consider multiple fields.

 

2. Table sharding by time, table sharding by primary key modulo/hash, and table sharding by volume

Red Packets are pay-as-you-go.

 

3. Limit the number of tables in a single database to within 10 million

 

4. Reject text and blob types

We can't avoid using the text and blob types. Split the table. Or save it locally and store multiple machines in multiple parts.

 

5. The partition algorithm can be time-based.

Such as day and month, which facilitates targeted query with a hit rate of 100%

 

 

2. One-sentence Field Optimization

 

1 length can be redundant, with an appropriate amount of about 10%

Tinyint (1 Byte) smallint (2 Byte) mediumint (3 Byte) int (4 Byte) bigint (8 Byte) recognize the length and select a proper type.

 

2. Do you know null?

Avoid using NULL fields because it is difficult to query and optimize NULL fields. The index of NULL fields requires extra space. The compound index of NULL fields is invalid.

Example of an error: 'fpacket _ name' char (32) default null.

Correct example: 'fpacket _ name' varchar (60) not null default ''.

'Fface _ value' int (10) unsigned not nulldefault '0 '. "

 

3. Related fields must be defined for the same type.

Statement operations of the same type can improve operation efficiency and reduce conversion costs.

 

4. Use numbers and enumeration to select a type.

Number to represent the meaning, to replace the string.

Correct example: Gender, 0 male, 1 female; time in the form of timestamp; IP in the number type and so on.

 

III. Statement Optimization

 

1. advantages and disadvantages of using the explain artifact to optimize statements

Type result set: displays the Type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, index, and all.

 

2 Truncate is faster than delete

Delete counters are not cleared, Delete by row, slow. Truncate is equivalent to deleting and recreating the file. It is best to export useful data for batch deletion, delete old tables, and rename new tables.

 

3. Use join instead of subquery

Join principle: nested loop. Left Join has a small amount of data before Straight_JOIN.

 

4 built-in function operations

Don't let MYSQL use its own function. He is very tired and tries to implement it in the program, such as now (). Put it in the program and pass it to mysql for processing. Do not use mysql to deal with big logical operations.

 

5. A statement depends on a CPU kernel.

One statement is a kernel. Large statements can be split and used by multiple cores to reduce the mysql lock time.

 

6. Do not select *

Unless you need to use all the fields you are querying .... Otherwise, you will occupy so much memory, bandwidth, CPU time, And I/O.

 

7. If you can use in, do not use or.

Or, the time complexity is n, and the time complexity of in is log (n ).

Example of an error: select Fpacket_name from t_account_packet where Fpacket_id = 68698080 or Fpacket_id = 68711068;

Example: select Fpacket_name from t_account_packet where Fpacket_id in (68698080,68711068 );"

 

8 if union can be used, do not use or

Same as above

 

9 reasonable use of limit

The Pat data is generally auto-incrementing, so the positioning usually needs to look at the last time in reverse order. However, if limit is the slowest reverse order and reasonably combined with limit, it can reflect higher efficiency.

 

The last two batches. Correct example: select Fpacket_id from t_account_packet order by Fpacket_id desclimit 2;

Incorrect idea: select count (*) from t_account_packet; => 879446;

SelectFpacket_id from t_account_packet limit 879444,879446;

The last 2nd batches. Correct example: selectFpacket_id from t_account_packet order by Fpacket_id desc limit ;"

Incorrect idea: select count (*) from t_account_packet; => 879446;

SelectFpacket_id from t_account_packet limit 879444,879445;

 

10 if you can use load data, do not use insert

By default, many data related to blood type and personality are loaded during the lucky divination activity. At that time, the source + row-by-row insert method was used to export data or load data. The principle is that the index will be disabled before the load is executed. After all the load operations are completed, then re-create the index. The principle of insert is that the database is updated and the index is updated every time a row is inserted. Therefore, it takes many times to slow down, depending on the order of magnitude to be processed.

 

 

Iv. One-sentence INDEX OPTIMIZATION

 

1. Try to select an index with a high degree of discrimination for search.

Incorrect example: name. The correct example is id.

 

2. Not too many

The table data-to-index capacity ratio is kept at. At least one index exists in one statement.

 

3. The index principle is from left to right.

 

4. Use the explain artifact to perform and analyze index Overwriting

 

5. Do not use index fields for Calculation

Which emergency channel have you ever seen?

 

6. Know them about MyISAM (re-search) and Innodb (default, transactional, and re-service)

Auto-increment columns are recommended for innodb primary keys.