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

This article will tell you what you need to pay attention to when building a table in mysql. There is a certain reference value, friends in need can refer to, hope to help you.

1. Each name of mysql

The library name, table name and field name are all in lowercase letters, separated by an underscore of'_', and the length of the name is no more than 12.

Second, use Inoodb storage engine

1. If you want to support transactions, please select innodb. If you don't need to, you can consider MyISAM.

two。 If the vast majority of the tables are just read queries, consider MyISAM, and if you both read and write frequently, use InnoDB.

3. After the collapse of the system, it is more difficult for MyISAM to recover. Can it be accepted?

4. Since the MySQL5.5 version, Innodb has become the default engine of Mysql (previously MyISAM), which shows that its advantages are obvious to all. If you don't know what to use, then use InnoDB, at least not bad.

Third, field type selection

1. It is recommended that all tables have a self-increasing id, which can often be used as a primary key.

two。 Unsigned is used to store nonnegative numbers. Because of the same number of bytes, the range of stored values is larger, such as tinyint, signed is-128127and unsigned is 0255.

3. No length is added in the shaping definition, and int is used instead of int (n). In fact, the n here does not mean the storage length, such as int (3) shows 3 bits by default, blanks are filled, and when exceeded, it is displayed normally.

4. When storing values in the range of 0-80, use tinyint unsigned.

5. Understand that the n in varchar (n) refers to the number of characters, not bytes.

6. Character set selection utf8

7. Both timestamp and datetime are accurate to seconds, so timestamp is preferred because timestamp has only 4 bytes and datetime8 has 4 bytes. At the same time, timestamp has the characteristics of automatic assignment and automatic update.

8. If possible, it is best to use not null for all fields. Because: the null field is indexed, it requires an extra 1 byte; it makes the comparison of indexes, index statistics, and values more complex. Therefore, if it is an index field, be sure to define it as not null, and the null value can be replaced by '0precinct'.

Fourth, attention should be paid to the establishment of the index

1. Index names must be lowercase

two。 The general index is named according to "idx_ field name", and the unique index is named after "uniq_ field name".

3. It is recommended that there are no more than 5 fields in the index. The number of indexes in a single table is limited to 5. InnoDB uses b+tree for storage, so you need to adjust the b+tree in update, delete, and insert. Too many indexes will slow down the update.

4. The unique index does not duplicate the primary key.

5. Fields that are often used as where conditions are indexed. It is recommended that you add an index to the field of order by,group by.

6. Use explain to determine whether the sql statement uses the index properly, and try to avoid the occurrence of Using File Sort,UsingTemporary columns: index.

7. Reasonable creation of joint indexes to avoid redundancy, (a) (b) is equivalent to (a) (b) (b), mysql has the principle of leftmost matching. Make rational use of overlay indexes.

5. Sql statement

1. With prepared statement, you can provide performance and avoid SQL injection.

2. The select statement only gets the required fields, instead of using select *.

3. Non-equivalent conditions in where conditions (in, between,

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.