Summary of Experience and Common Sense in php+mysql Development

This paper summarizes the experience and common sense in the development of php+mysql. Share it for your reference, as follows:

1. Basic specifications

(1) Use the InnoDB storage engine as much as possible

Support transactions, row-level locking, better concurrency performance, CPU and memory cache pages are optimized properly, and resource utilization is higher

(2) The UTF8 Character Set must be used

No transcoding, no risk of garbled code

(3) Chinese annotations must be added to data tables and data fields

After N, who knows what this r1, r2, r3 field is for

(4) Try not to use stored procedures, views, triggers, Event

For Internet services with high concurrency and big data, the architecture design idea is to "liberate the database CPU and transfer the calculation to the service layer". In the case of large concurrency, these functions are likely to drag the database to death, and the business logic in the service layer has better scalability, which can easily realize "adding machines and adding performance". The database is good at storage and indexing, and CPU calculation is moved to the upper level as much as possible

(5) It is forbidden to store large files or photos

Don't let the database do what it is not good at. Large files and photos are stored in the file system (using upload service), and URI is stored in the database

2. Naming conventions

(6) Library name, table name and field name: lowercase, underlined style, no more than 32 characters, must see the name and know the meaning, and it is forbidden to mix Pinyin with English

(7) The table name is concise and clear, not only 1 index name idx_xxx, but only 1 index name uniq_xxx

3. Table design specification

(8) The number of single instance tables must be less than 500

(9) The number of columns in a single table is preferably less than 30

(10) Tables must have primary keys, such as self-increasing primary keys

a) Primary key increment, data row writing improves insert performance, avoids page splitting, reduces table fragmentation, and increases space and memory usage

b) Primary key to select a shorter data type, Innodb engine common index will save the value of the primary key, shorter data type can effectively reduce the index disk space, improve the efficiency of the index cache c) No primary key table deletion, in row mode of master-slave architecture, will lead to standby library stuck

(11) Prohibit the use of foreign keys. If there are foreign key integrity constraints, application control is required

Foreign keys will cause coupling between tables, and update and delete operations will involve associated tables, which will affect the performance of sql and even cause deadlock. In the case of high concurrency, it is easy to cause database performance. In the case of big data and high concurrency, the database is used with performance first

4. Field design specification

(12) The field must be defined as NOT NULL and provide a default value

a) The columns of null make index/index statistics/value comparisons more complex and more difficult to optimize for MySQL
b) null This type of MySQL needs special processing, which increases the complexity of processing records in the database. Under the same conditions, when there are more empty fields in the table, the processing performance of the database will be reduced a lot
c) null values require more storage space, and the columns of null in each row in both tables and indexes require additional space to identify them
d) When dealing with null, only is null or is not null can be used, but =, in, < , < > ,! =, not in. For example: where name! = 'cuiyang', if there are records with name as null value, the query result will not contain records with name as null value

(13) Prohibit the use of TEXT and BLOB types

More disk and memory space will be wasted, and a large number of unnecessary large field queries will eliminate hot data, which will lead to a sharp decrease in memory hit ratio and affect database performance

(14) Avoid using decimal storage currency

Using integer storage, decimal can easily lead to mismatch of money

(15) Mobile phone number must be stored using varchar (20)

a) refers to an area code or country code and may appear +-() b) Does the phone number do math? c) varchar can support fuzzy queries, for example: like "138%"

(16) ENUM is prohibited and TINYINT can be used instead

a) Adding a new ENUM value requires an DDL operation b) The actual internal storage of ENUM is an integer. Do you think you are defining a string?

5. Index design specifications

(17) It is recommended to control the single table index within 5

(18) The number of single index fields is not allowed to exceed 5

When there are more than 5 fields, it can no longer effectively filter data

(19) It is forbidden to establish indexes on attributes with frequent updates of 10 points and low discrimination

a) Updates change the B + tree, and indexing frequently updated fields can significantly degrade database performance b) "gender" is not a very discriminating attribute, so it is meaningless to build an index, it can not filter data effectively, and its performance is similar to that of full table scanning

(20) To establish a combined index, the fields with high discrimination must be put in front

Can filter data more effectively

6. SQL usage specification

(21) Prohibit SELECT *, only get the necessary fields, and need to display the description column attribute

a) Reading unwanted columns increases CPU, IO, NET consumption b) Inefficient use of overlay indexes c) Using SELECT * It is easy for the program BUG to appear after adding or deleting fields

(22) Disable the use of INSERT INTO t_xxx VALUES (xxx) and must display the column properties specified for insertion

It is easy to appear the program BUG after adding or deleting fields

(23) Prohibit implicit conversion of attributes

SELECT uid FROM t_user WHERE phone=13812345678

Causes a full table scan without hitting the phone index

(24) Avoid using functions or expressions on properties of WHERE conditions

SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 

Will result in a full table scan. The correct way to write it is:

SELECT uid FROM t_user WHERE day>= unix_timestamp( ' 2017-02-15 00:00:00')

(25) Avoid negative queries and prohibit fuzzy queries beginning with%

a) Negative query criteria: NOT,! =, < > ,! < ,! > , NOT IN, NOT LIKE, and so on, will cause a full table scan
b) A fuzzy query beginning with% causes a full table scan

(26) Avoid using JOIN queries and subqueries for large tables

It will produce temporary tables, consume more memory and CPU, and greatly affect database performance

(27) Avoid using OR conditions and try to change to IN queries

The OR query of the old version of Mysql can't hit the index. Even if it can hit the index, the database needs to spend more CPU to help implement query optimization

(28) The application must catch SQL exceptions and handle them accordingly

For more readers interested in PHP related content, please check the topics on this site: "Introduction to php+mysql Database Operation", "Summary of php+mysqli Database Programming Skills", "Introduction to php Object-Oriented Programming", "Encyclopedia of PHP Array (Array) Operation Skills", "Summary of php String (string) Usage" and "Summary of php Common Database Operation Skills"

I hope this article is helpful to everyone's PHP programming.