Create a table and field suggestion

1. All data tables and fields should have clear annotations and field descriptions

Note: The definition of data tables and fields is clear to both the creator and other developers and subsequent maintainers

2. Use lowercase letters or digits in the table name and field name. Do not start with digits

Note: MySQL is case-insensitive on Windows, but it is case-sensitive by default on Linux. To avoid unnecessary trouble, use lowercase

3. Set each column to not NULL (not null if column is BLOB/TEXT) and define default values

3.1: NOT IN,! = Equal-negative conditional query returns a result set with a NULL value that is not a NULL row

3.2: When concat function concatenation is used, non-NULL judgment must be performed on each field first. Otherwise, NULL will be generated as long as any field is empty

3.3: NULL columns are not counted when the count function is used for statistics

: 3.4 because NULL column makes the index, index statistics and value comparison are more complex and can be NULL columns will use more storage space, also need special handling in mysql, when can be indexed for NULL columns, each index record need an additional byte, if the plan is built on the column index, should avoid the column design is NULL.

4, each table has a self-increment column ID and primary key, using the unsigned type unsigned, not used for business logic

Description:

4.1: Avoid storing negative values, and expand the range of representation

4.2: If the use of on the primary key (if the id number or student id, etc.), since each into the approximation of random key values, so every time a new record to be inserted into the existing index page to a certain position in the middle, the MySQL had to in order to insert a new record to the appropriate location and mobile data, or even the target page may have been back from the cache to disk and clear it, The OPTIMIZE TABLE had to be rebuilt and the fill page had to be optimized as it had to be read back from disk, which added a lot of overhead and fragmentation due to frequent movement and paging

Is_xxx; unsigned tinyint (1 indicates yes, 0 indicates no)

For example, is_valid, 1. Indicates that the value is valid. 0. Indicates that the value is invalid

Disallow mysql reserved words such as desc, range, match, action, add, alter, all.. , etc.

More reserved word can refer to the mysql official documentation: dev.mysql.com/doc/refman/…

7. Varchar is a variable length string. Storage space is not allocated in advance, and the length should not exceed 5000

8. It is recommended to divide database and table only when the number of rows in a single table exceeds 5 million or the form capacity exceeds 2GB

9, command specification: primary key index name pk_ field name; Unique index name uk_ field name; Common index name IDX_ field name; Temporary tables are prefixed with TMP

10. Decimal type is decimal; float and double are prohibited

Error: Error: error: error: error: error: error: error: error: error: error: error: error: error If you store data beyond the range of Decimal, it is recommended to store the data separately as integers and decimals

11. Allow some redundancy of fields to improve query performance. Redundant fields should follow: 1) Fields that are not frequently modified. 2) VARCHAR is not a long field, let alone a text field.

InnoDB and MyISAM store engine table, index type select BTREE; MEMORY tables can select HASH or BTREE indexes as needed.

13. When building an index, consider setting up a joint index, and put the most differentiated fields first, so that data can be retrieved more efficiently

14, the appropriate character storage length, not only save database table space, save index storage, more importantly, improve the speed of retrieval

Add, delete, change and check good habits

SELECT * from MySQL; SELECT * from MySQL; SELECT * from MySQL; SELECT * from MySQL

2, paging query, when the limit starting point is high, you can use filter conditions for filtering. Select f1, F2,f3 from table1 limit 2000020; Select f1, F2,f3 from table1 where id>20000 LIMIT 20

Select * from ‘where’ where ‘equal’

4. In multi-table join, try to select the table with smaller result set as the driver table to join other tables

5. The number of insert columns must be the same as that of the value list. If no column list is specified, the length of the value list must be the same as that of the value list.

6. The number of rows affected by update and deletion should not be too large. If so, fine-grained split

Remember to write where condition (do you want to delete the library? Ha ha)

You are welcome to add and build more elegant data specifications together.

Original link:

www.cnblogs.com/peyshine/p/…

Wenyuan network, only for the use of learning, such as infringement, contact deletion.

I’ve compiled the interview questions and answers in PDF files, as well as a set of learning materials covering, but not limited to, the Java Virtual Machine, the Spring framework, Java threads, data structures, design patterns and more.

Follow the public account “Java Circle” for information, as well as quality articles delivered daily.