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

As DBA at work, we may also have to face the various Freestyle requirements of developers, but do they know what they need to pay attention to when operating the database?

Today, we will briefly talk about the code of conduct for the development of MySQL database and the top ten iron disciplines. It is a kind of help to the DBA brothers and a kind of learning for developers.

1. Create tables to use innodb to store engine tables

Now basically most of the business is innodb storage engine, MySQL 8.0, no longer use myisam. Each table should contain a self-incrementing primary key id.

two。 In the choice of data types, we should adhere to the principle of the simpler the better, and the smaller the better.

It is recommended to use int to store the type of ipv4, which can be converted by function. Fields of the money category can also be stored as int types, which can be divided into units.

The time type can be datetime, which is more available than tiemstamp, the storage space has been reduced from 8 bytes to 5 bytes, and the performance is 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 names, table names, and field names must be in lowercase letters and separated by "_"

In the MySQL database, the case-sensitive parameter lower_case_table_names defaults to 0, which means that the table name is case-sensitive. If it is 1, it is case insensitive and is stored in lowercase.

4. It is not recommended to use the ENUM type, use TINYINT instead.

It is also not recommended to use fields of big data type such as text or blob to appear in the business table.

5. The table character set uses utf8, and if necessary, you can apply for the utf8mb4 character set.

Its versatility is better than gbk,latin1. The utf8 character set takes up 3 bytes to store Chinese characters. If you meet the requirements of facial expression storage, you can use utf8mb4.

6. When select queries the table, you only need to get the necessary fields and avoid using select *.

This reduces network bandwidth consumption and may also take advantage of overlay indexes.

7. In all field definitions, not null constraints are added by default to avoid null.

When counting select count () statistics on this field, you can make the statistical results more accurate, because data with a value of null will not be counted.

8. When creating an index, do not build an index on a low cardinality column, such as sex or status.

Judging by the selectivity of the index, the closer the select count (distinct col1) / count (*) from table_name; is to 1, the higher the selectivity, the more suitable it is to create an index. In general, the number of indexes in a single table should not exceed 4-5 ranges.

9. In SQL statements, try to avoid or clauses.

This kind of judgment clause can let the program complete on its own, do not give it to the database to judge. You should also avoid using union and try to use union all, which reduces the work of deduplication and sorting.

10. Always do a good job of monitoring, often on-line sql statements, online capture for analysis.

You can use the percona-toolkit tool.

At present, write so much first, welcome to add in time, in fact, a lot of work requires close cooperation between DBA and developers. If a new business is to be launched, DBA should participate in the business and build table modeling together with the development. We have only one goal, which is to work more comfortably and earn more money, so why not change our bad work habits? You can't do whatever you want at work! Keep trying!

More wonderful articles:

MySQL Database "Ten deadly sins" (Ten Classical error cases)

Eighteen palms of database optimization

My online course: "in-depth Analysis of MySQL Architecture and practical DBA Video course"

Curriculum objectives

Through in-depth analysis and explanation of MySQL architecture, together with production environment backup and recovery, master-slave replication, highly available cluster architecture and optimization, students can have a shallow-to-deep understanding of MySQL database. The last part of the course will also explain the summary of the examination questions, which is helpful for students to find an ideal job in MySQL DBA.

intended for

Students who have just come into contact with MySQL database, operation and maintenance personnel who have some experience in operation and maintenance want to improve their MySQL database level of DBA.

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.