This article originates from the public number [architect's road], in order to help the memory, now copied with this, if there is infringement, inform immediately delete, thank you!
Military use scenario: Internet business with large amount of concurrency and large data volume
Basic specifications
- You must use the InnoDB storage Engine: Supports transactions, row-level locks, better concurrency, and CPU and memory cache page optimizations for higher resource utilization
- Must use UTF8 Character set: Universal code, no transcoding, five garbled risk, space saving
- Data tables, data fields must be added in Chinese notes: good memory is better than bad writing
- Prohibit the use of stored procedures, views, triggers, Event: High concurrency Big data of the Internet business, the architecture design idea is "to liberate the database CPU, transfer the computation to the service layer", the concurrency is large, these functions may drag the database, the business logic to the service layer with better scalability, can easily achieve " Increase the performance of the machine. " The database is good at storing and indexing, CPU computing or moving up.
- Prohibit the storage of large files or large pictures: Do not let the database do not do good things, large files and pictures stored in the file system, the database to save the URI.
Naming conventions
- Connect to the database using only intranet domain names instead of IP
- Online environment, development environment, test environment database intranet domain name follows the naming specification:
- Library name, table name, field name: lowercase, underline style, no more than 32 characters, must see the name of the meaning, prohibit pinyin mixed in English
- Table name Txxx, non-unique index name idxxxx, unique index name uniq_xxx
Table Design Specifications
- The number of single-instance tables must be less than 500
- The number of columns in a single table must be less than 30
- The table must have a primary key, such as a self-increment primary key: Improve data insertion performance, avoid page splitting, reduce table fragmentation to improve space and memory usage; primary key select shorter data type, reduce index disk space, improve index cache efficiency; table deletion without primary key, master-slave architecture in row mode, will cause the repository to be stretched
- The use of foreign keys is forbidden, and if there are foreign key integrity constraints, application control is required: foreign keys cause tables to be coupled to tables, and update and delete operations involve associated tables, which can affect SQL performance or even deadlock. Big Data high concurrency Business scenario data usage takes precedence over performance
Field Design Specification
- You must design the field as not NULL and provide a default value: The null column makes index/index statistics/value comparisons more complex, more difficult for MySQL to optimize, and the null type requires special handling in MySQL, consumes database performance, and requires more storage space for null values. Null values in the table or index require additional space to identify, and null processing can only be done with IS NULL or is not NULL, and cannot take the action symbol =, in.
- Prohibit the use of text, BLOB type: Waste more disk and memory space, resulting in low cache hit ratio, affecting performance
- Prohibit the use of fractional storage currencies: Decimals easily lead to accuracy errors
- The phone number must be stored using varchar (20): May involve area code or country codes, varchar supports fuzzy query like
- You can use tinyint instead of enum: Add new enum value to do DDL operation, internal is actually integer
Index Design Specification
- Single-table index recommended control is within 5: too many affect update and delete performance
- The number of single-indexed fields does not allow more than 5: too many filter effects
- Prohibit indexing on highly-updated, low-sensitivity properties: Updates change the underlying B + tree, and updating frequent field indexes can significantly degrade database performance, differentiate between low-level properties, and create a cable that does not have the effect of filtering data, similar in performance to full-table scans
- To set up a composite index, you must put a high-sensitivity field in front: more efficient filtering of data, related to the query optimizer algorithm
SQL Usage Specification
- Disable SELECT *, get only necessary fields, need to display description column properties: Increase consumption, not effective use of overwrite index, easy to add or remove fields after the program bug
- Disable INSERT INTO t_xxx VALUES (XXX), you must display the specified column properties for insertion: Easy to add or remove fields after a program bug
- Disallow attribute implicit conversions: results in a non-hit index, with full table scan; The Select UIDfrom the user WHERE phone=13812345678 causes a full table scan and cannot hit the phone index because the phone field is varchar (20) and the correct notation is: SELECT uid from t user WHERE phone= ' 13812345678′;
- Disable the use of functions or expressions on the properties of a Where condition: results in a non-hit index and full table scan; SELECT uid from tuser WHERE Fromunixtime (day) >= ' 2017-02-15 ' will cause full table scan, correct notation: SELECT uid from tuser WHERE day>=unixtimestamp (' 2017-02-15 00:00:00 ')
- Suppress negative queries, and a fuzzy query with a% start: Negative query condition: not,! Start or <> causes full table scan; percent start fuzzy query, resulting in full table scan
- Prevent large tables from using a join query, prevent large tables from using subqueries: temporary tables, consuming more memory and CPUs, and greatly impacting database performance
- Prohibit use or condition, must change in query: MySQL old version or query cannot hit index, new version consumes CPU to query optimization hit index
- The application must catch the SQL exception and have the appropriate processing
Summary
Large data volume and high concurrency of the Internet business, greatly affect the performance of the database is not allowed.
58 Home Database 30 rule interpretation
