Optimization Measures for MySQL Database Design

We know that a goodDatabase DesignThe solution often achieves twice the result with half the effort for database performance. Therefore, as a Database Designer, we often need to consider some measures to optimize the database when designing the database. This article briefly introducesMySQL databaseWe hope to help you with some optimization measures during the design.

1. Naming skills and specifications

Regardless of the design, naming should be taken into consideration as a very important thing. Naming techniques for tables, sequences, fields, and indexes can be summarized as follows:

(1) The sequence name is the same as the table field name

Example: insert into users (us_id) value (us_id.nextval)

SQL Server, DB2, and other databases have no sequence meanings.

(2) The name of the joined table should be composed of the joined table connected "_".

For example, if we have designed many-to-many tables for association, authors and books, then our association table can be named authors_books.

(3) The names of associated fields must be the same. The names are accurate based on the field names of the base table.

For example, the authors table contains as_id and as_name fields.

(4) The first two digits of a field definition are abbreviated table names and the third digit is an underscore.

Example: us_id, us_name, bk_name, bk_time.

First, ensure that the sequence name is unique, and the general sequence is the id field of the table. If the prefix is not added, all fields are called ids, which violates the uniqueness principle.

Second, to facilitate the writing of association query statements in the future.

(5) use fixed definitions for common fields

For example, whether the sequence: id is deleted: delornot

(6) The index name is the same as the table name.

To improve the query speed of tables with large data volumes, you can create an appropriate index. If a table has only one index, it is recommended that the index name be the same as that of the table. If Multiple indexes exist, the table name is underlined and the index column name is added.

2. optimization design skills

(1) The associated field type should be defined as a number as much as possible.

For example, us_id, bk_id, and Other types should be designed as numbers.

(2) The sequence field of the table must be numeric.

The reason is the same as above.

(3) If a field needs to be changed frequently, use the space-for-time design method.

The most common example is the accumulation of the number of Logon points for users. According to the paradigm design, a us_scores field is created in the users table. In the future, you need to use the update statement when the user points change. However, we know that the execution speed of update statements is very slow. To avoid repeated use of the update statements, the optimal design is to create a us_scores table and store the points added each time, the query is calculated using the sum method of the SQL statement.

(4) If the database is portable, no stored procedures and triggers are used.

(5) create an appropriate index

Index creation is one of the basic tips for accelerating database queries. It is recommended that indexes be created only for tables with millions of records.

3. How to ensure data security

The safest design scheme is to separate Web databases from Test Databases. The Web Database permission is only available to the Administrator.

There are so many optimization measures for MySQL database design. I hope this introduction will help you.