Want to freestyle? First of all, we must obey the ten iron laws of MySQL!

We, as DBAs at work, may also need to face the various freestyle of developers, but do they know what to look for when working with databases?

Today with everyone simple talk about MySQL database development behavior norms, the ten iron discipline. It is a kind of help to the DBA, and it is also a kind of learning for the developers.

1. Create a table to use the InnoDB storage engine table

Now basically most of the business is InnoDB storage engine, MySQL 8.0 start, completely no longer use MyISAM. Each table contains a self-growing primary key ID.

2. The choice of data type, the more simple the better, the smaller the better the principle.

It is recommended to use int to store the type of IPv4, which can be converted by function. The Money field can also be stored as an int type, which can be done with a split unit.

The time type can be datetime, it is larger than the Tiemstamp available range, storage space from the original 8 bytes, dropped to 5 bytes, performance is also good.

Mysql> Select Inet_aton (' 192.168.56.132 '); +-----------------------------+|          Inet_aton (' 192.168.56.132 ') |+-----------------------------+| 3232249988 | +-----------------------------+mysql> Select Inet_ntoa (3232249988); +-----------------------+| Inet_ntoa (3232249988) |+-----------------------+| 192.168.56.132 | +-----------------------+1 row in Set (0.00 sec)

3. Library name, table name, field name must use lowercase letters, with "_" split

In the MySQL database, the case of the table name is differentiated by the parameter lower_case_table_names by default to 0, which represents case sensitivity. If it is 1, case is not sensitive and is stored in lowercase.

4. The ENUM type is not recommended and TINYINT is used instead.

It is also not recommended that fields such as text or blobs that use large data types appear in the business table.

5. The table character set uses UTF8 and can request the use of the UTF8MB4 character set if necessary.

its versatility is better than gbk,latin1. the UTF8 character set stores Chinese characters in 3 bytes and can be used if you encounter the requirements of the emoji store UTF8MB4

6. Select query table only need to get the necessary fields, avoid the use of select *.

This can reduce network bandwidth consumption and may also take advantage of the overwrite index

7. In all field definitions, a NOT NULL constraint is added by default to avoid null.

When you make a select count () statistic count on the field, you can make the statistics more accurate, because data that is null is not counted.

8. Do not index on low cardinality columns when creating indexes, such as sex, status fields.

Select COUNT (Distinct col1)/count (*) from table_name to be judged by the selectivity of the index; The closer to 1, the higher the selectivity, the better it is for creating the index. In general, the number of single-table indexes should not exceed 4-5 ranges.

9. In the SQL statement, avoid the OR clause as much as possible

This kind of judgment clause can let the program complete itself, do not give the database to judge. Also avoid using union, try to use union all, reduce the work of weight and sorting.

10. Always do the monitoring work, often on-line SQL statements, online capture analysis.

You can use the Percona-toolkit tool.

At present, so much to write, welcome to add in time, in fact, a lot of work needs DBAs and developers close cooperation. The new business is on-line, and the DBA is involved in the business, modeling with the development of the table. We have only one purpose, that is more comfortable to work, to earn more money, then why can not change our bad working habits. It's not what you want to do at work. Keep working on it!

More Great articles:

MySQL database "10 Deadly Sins" (Ten classic error cases)

Database optimization of the Dragon 18 palm

My online courses: the MySQL Architecture in-depth analysis and practical DBA video Course "

Course Objectives

Through a thorough analysis of the MySQL architecture, with the production environment backup recovery, master-slave replication, high-availability cluster architecture and optimization and other practical exercises, so that students can have a MySQL database from shallow to deep understanding. The final part of the course will also be explained in the opposite of the question summary, so that students can find the ideal MySQL DBA work.

Applicable people

Just contact MySQL database classmate, have a certain operation and maintenance experience of the operator, want to improve their MySQL database level DBA.

This article from "Zhang Mengsu Blog" blog, reproduced please contact the author!

Want to freestyle? First of all, we must obey the ten iron laws of MySQL!