Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how to optimize MySQL database design, I believe most people still do not know how to, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

1, naming skills and norms

Whatever the design, naming should be treated as a very important thing. The 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

e.g. insertusers (us_id)value(us_id.nextval)

SQL Server, DB2 and other databases are no sequence meaning.

(2)The name of the associated table should be composed of the associated tables connected with "_"

For example: we have designed the table authors and the table books with many-to-many associations, so our association table can be named authors_books.

(3)The associated field names must be the same, and the names are subject to the field names of the underlying tables.

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

(4)The first two digits of the field definition are the abbreviation of the table name, and the third digit is underlined

Examples: us_id, us_name, bk_name, bk_time.

First, to ensure that the specification, the sequence name must be unique, and, in general, the sequence is the id field of this table. Without a prefix, calling fields id violates the uniqueness principle.

Second, for the convenience of writing associative query statements in the future.

(5)Common fields are defined fixedly

For example: Sequence: id Deletion: delornot

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

In order to improve the query speed of tables with large data volume, we can adopt the appropriate index method. If a table has only one index, it is recommended that the index name be the same as the table name, and if there are multiple indexes, underline the table name and add the index column name.

2. Tips for optimizing design

(1)Association field types are defined as numeric types whenever possible

For example: us_id,bk_id, etc. should be designed as numeric types.

(2)The sequence field of a table must be of numeric type

For the same reasons.

(3)If a field needs to be changed frequently, a space-for-time design approach is adopted

The most common example is the accumulation of user points login times. According to the normal design, a field us_scores is established in the users table. Later, it needs to be modified by the update statement when the user points change. However, knowing that the execution speed of the update statement is very slow, in order to avoid a large number of repeated uses of it, the optimized design solution is to establish a us_scores table, store each increase in the integral, and use the sum method of the SQL statement to calculate it.

(4)Do not use stored procedures and triggers if database migration is possible

(5)Creating appropriate indexes

Indexing is one of the basic techniques for speeding up database queries, and it is generally recommended that only tables with millions of records should be indexed.

How to ensure data security

The safest design scenario is to separate the Web database from the test database. Web database permissions are controlled by the administrator alone.

The above is "MySQL database design how to optimize" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.