Premise, you are development, not DBA, not operations.

Secondly, the following are all suggestions, specific or subject to the company’s regulations.

General specification

  • The engine is mostly Innodb, the characters are usually UTF8, and the expressions are usually UTF8MB4.
  • Data tables, fields must be commented, and always remember to update the comments.
  • Do not use stored procedures, views, triggers, events in general. Is the SAO operation, SAO SAO with the hair, as far as possible when the database warehouse, do not write logic.
  • Do not save pictures and large files, CDN, object storage to understand.
  • If the business design knows that the data volume is very large, divide the table in advance.
  • Balancing Paradigms and Redundancy, see: Table design, the final mountain of learning mysql
  • Intranet domain name, not Internet, not IP.
  • According to the business account, convenient isolation and circuit breaker.

named

It all depends on the company. It’s mostly lowercase and underlined, not too long, abbreviations.

Table design

  • Control table size, number of fields, and separation of hot and cold data.
  • Don’t use foreign keys. Don’t use null.
  • You have to have a primary key. The self-incrementing ID is usually used as the primary key.
  • Try not to use enum, add the table structure that needs to be changed, and use numeric type directly.
  • Char and varchar, datetime and timestamp.

Index design

  • Control index number
  • Create a more appropriate index. Reference: Learn MySQL’s first mountain – index

The use of SQL

  • Don’t useselect *“, similarly, try to bring a limit, pay extra attention, do not make a good bucket.
  • Be aware of some conditions that can invalidate an index.
    • null
    • We add a function after where
    • Where or JOIN are of different types.
  • Not in is changed to subquery
  • Limit 1000 = >1000 limit 10
  • In not too much (generally about 50), more than a subquery.
  • How to put the index of where, also refer to my index section.
  • Inset into values(1, 2, 3)toInset into table values(1, 2, 3) Inset into table values(1, 2, 3)

conclusion

I believe that with these foundations, the basic will not be because of the database bucket, but try to look at the implementation plan after writing, there is no pit.

conclusion

  • If something is wrong, you are welcome to correct it.
  • If you don’t understand anything, you are welcome to point out and I will add chestnuts.
  • If you feel OK, you can like it and let more people see it.

Related reading:

Table design is the final mountain to learn mysql

Learn MySQL’s first mountain – index

Learn about MySQL’s second mountain — locks and transactions

Why does MySQL use B+ trees?