- One, database command specification
- Two, database basic design specifications
- Three, database field design specifications
- Iv. Index design specification
- Five, common index column recommendations
- How to select the order of index columns
- Avoid creating redundant and duplicate indexes
- Prioritize overwriting indexes
- Index SET specification
- X. Database SQL development specifications
- 11. Code of conduct for database operation
One, database command specification
All database object names must be in lowercase letters and separated by underscores
2, all mysql database object names banned reserved keywords (if the name of the table contains the keyword query, needs to be enclosed in single quotation marks)
3, the database object to be able to do the name of the meaning, and the final no more than 32 characters
4. The temporary database table must be prefixed with tmp_ and suffixes with date. The backup table must be prefixed with bak_ and suffixes with date (timestamp)
5. All columns that store the same data must have the same name and type. (Generally, they are associated columns.
Two, database basic design specifications
1. All tables must use Innodb storage engine. There are no special requirements (i.e. Innodb supports transaction, supports row-level locking, better recovery, and better performance at high concurrency
2. The database and table character sets use UTF8 for better compatibility. The unified character set can avoid garbled characters caused by character set conversion
Add comments to tables and columns using comment clauses. Maintain the data dictionary from the start
4, try to control the size of a single table data, suggest that control in 5 million 5 million is not within the limitation of the MySQL database, will cause to modify the table structure, backup, recovery will have a big problem Can use historical data archive (used in log data), depots table (applied to business data), and other means to control the amount of data size
5. Be careful about using MySQL partitioned table A partitioned table is physically represented as multiple files and logically represented as one table. If you select a partition key carefully, cross-partition query efficiency may be lower
MySQL limits each table to store a maximum of 4096 columns, and the size of each row cannot exceed 65535 bytes to reduce disk I/O and ensure the memory cache hit ratio of hot data. More memory is used to load the table into the memory buffer pool, which also consumes more IO.) Use the cache more efficiently and avoid reading useless cold data.
7. Do not create a reserved field in a table. It is difficult to name a reserved field based on its name
8, banned images stored in the database, the file such as binary data File is very big, often can cause rapid growth of data quantity in a short period of time, the database to read database, usually a large number of random IO operations, file is large, IO operations is very time-consuming Usually stored in a file server, database storing files only address information
9. Do not do database stress tests online
10. Do not directly connect to the generated environment database from the development environment or test environment
Three, database field design specifications
1, the choice that met the need of the storage the smallest field data type column, the greater the space needed for indexing the greater, in such a page can store the number of inodes are less and less, in the IO number traversed need the more, the index of performance will be poor method 1) to convert a string to numeric type storage, such as: Translates an IP address into an integer. Mysql provides two methods for processing IP addresses: inet_aton converts an IP address to an unsigned integer (4-8 bits). Inet_ntoa Converts an IP address to an integer (4-8 bits). When displaying data, use inet_ntoa to convert an integer IP address into an address. 2) For non-negative data (such as the increment ID, integer IP), it is preferred to use unsigned integer because: UNSIGNED INT -2147483648 2147483647 UNSIGNED INT 0 4294967295 VARCHAR(N) Instead of the number of bytes use UTF8 to store 255 Characters Varchar(255)=765 bytes. Excessive length consumes more memory
2. Avoid using data types like TEXT and BLOB. The most common type is TEXT, which can store 64K of data. Memory temporary tables cannot be used, and disk temporary tables must be used. For this type of data, Mysql still has to perform a second query, which will make SQL performance very poor, but this does not mean that you must not use this type of data. If you must, it is recommended that you separate BLOB or TEXT columns into separate extended tables. You must not use SELECT * when querying, but only need to fetch the necessary columns. Do not query for the TEXT column if you do not need the column data. TEXT or BLOB types can only use prefix indexes because MySQL has restrictions on the length of index fields, so the TEXT type can only use prefix indexes, and the TEXT column cannot have default values.
3. Avoid using the ENUM type to modify the ENUM value, you need to use the ALTER statement. The ORDER BY operation of the ENUM type is inefficient
NOT NULL (NOT NULL, NOT NULL, NOT NULL); Comparisons and calculations require special treatment for NULL values
5. Use TIMESTAMP (4 bytes) or DATETIME (8 bytes). The storage time of TIMESTAMP ranges from 1970-01-01 00:00:01 to 2038-01-19-03:14:07. TIMESTAMP takes up 4 bytes, which is the same as INT, but is more readable than INT. It is common for people to use strings to store date-type data (not correct) : Disadvantages 1: You cannot use date functions to calculate and compare disadvantages 2: It takes more space to store dates using strings
6. The decimal type must be used for money data related to finance. Inexact floating point: float,double precise floating point: decimal Type is an exact floating point number that does not lose accuracy when calculating. The footprint is determined by the width of the definition. Every 4 bytes can store 9 digits, and the decimal point takes up one byte. Can be used to store integer data larger than Bigint.
Iv. Index design specification
1. Limit the number of indexes on each table. It is recommended that no more than 5 indexes on a single table is not the more the better! Indexes can increase or decrease efficiency. Indexes can increase query efficiency, but they can also reduce insert and update efficiency, and in some cases even reduce query efficiency. Because mysql when choosing how to optimize the query optimizer, according to a unified information, for each index can be used to evaluate, to generate a best execution plan, if there are a lot of index can be used to query at the same time, it will increase the time of mysql optimizer to generate the execution plan, also can reduce the query performance.
Before the 5.6 release, SQL could only use one index in a table. After 5.6, although there is an optimized method of merging indexes, it is far from as good as using one index in a table
Innodb is a table organized by indexes: data is stored in the same logical order as the indexes. Each table can have multiple indexes, but there is only one order in which tables can be stored. Innodb organizes tables according to the order of primary key indexes. Do not use frequently updated columns as primary keys. Multi-column primary keys (equivalent to a federated index) do not use UUID, MD5, HASH, or string columns as primary keys (the sequential growth of data cannot be guaranteed). You are advised to use the increment ID value for the primary key.
Five, common index column recommendations
SELECT * from ‘SELECT’, ‘UPDATE’, ‘DELETE’, ‘ORDER BY’, ‘GROUP BY’, ‘DISTINCT’, ‘SELECT’ from ‘SELECT’, ‘UPDATE’ from ‘SELECT’, ‘DELETE’ from ‘SELECT’, ‘UPDATE’ from ‘SELECT’, ‘DELETE’ from ‘SELECT’, ‘UPDATE’ from ‘SELECT’, ‘DELETE’ from ‘SELECT’ Generally, the columns in 1 and 2 are set up to have a better effect of joint index
How to select the order of index columns
The purpose of creating indexes is to reduce random I/O and improve query performance. The less data can be filtered out by indexes, the less data can be read from disks. The most distinguishable is placed on the leftmost side of the joint index (distinguishable = number of different values in a column/total number of rows in a column); Try to place columns with small field lengths at the far left of the joint index (because smaller columns can store more data per page, and better I/O performance). The most frequently used columns are placed to the left of the union index (so that fewer indexes can be created).
Avoid creating redundant and duplicate indexes
This increases the time it takes the query optimizer to generate an execution plan. Example Of duplicate indexes: Primary key(ID), index(ID), and Unique Index (ID) Example of redundant indexes: index(A, B,c), index(a,b), index(a)
Prioritize overwriting indexes
Override indexes are preferred for frequent queries. Overwrite index: an index that contains all query fields (select,ordery by,group by) Innodb stores tables in the order of clustered indexes. For Innodb, a secondary index stores the primary key of a row in a leaf node. If a secondary index is used to query data, after finding the corresponding key value, We need to do a secondary query through the primary key to get the actual data we need. In the overwrite index, all data can be obtained from the key value of the secondary index, avoiding the secondary query of the primary key, reducing I/O operations and improving query efficiency. Can make random IO order IO to speed up the query efficiency Due to cover index is the key values stored in order, for the scope of the IO intensive search, random contrast each line of data read from the disk IO is much less, so when using the index visit can also keep the random read disk IO into a IO index search order.
Index SET specification
Avoid using foreign key constraints Foreign key constraints are not recommended, but indexes must be created on the associated keys between tables. Foreign keys can be used to ensure the referential integrity of data, but it is recommended to implement it on the business side. Foreign keys can affect write operations on both parent and child tables and degrade performance.
X. Database SQL development specifications
1, it is recommended to use prepared statements for database operation prepared statements can be repeated use these plans, the time required to reduce the SQL compiler, can also solve the problem of dynamic SQL brought about by the SQL injection Only pass parameters, than the more efficient the same statement can pass SQL statement parsing, used multiple times and improve the processing efficiency.
2. Avoid implicit conversions of data types Implicit conversions will invalidate the index. Select name,phone from customer where id = ‘111’;
3. Make full use of existing indexes on the table to avoid using double % search conditions. Select * from index where index = ‘%123%’ where index = ‘%123%’; select * from index where index = ‘%123%’; Have a, b, c column joint index, the scope of a column in the query conditions query, in b, c on the column index will not be used, in defining joint index, if find a column to use range, will put a column in the right side of the joint index. Use left Join or Not EXISTS to optimize not in operations because Not in also often uses index invalidation.
4, database design, should be to consider the future expansion
5, the program connects to different databases using different accounts, base cross-library query for database migration and database partition table leave room to reduce the business coupling degree to avoid security risks caused by excessive permission
6. Do not use SELECT *. You must use SELECT < Field List > to query data
INSERT into values (‘a’,’b’,’c’); Insert into t(c1,c2,c3) values (‘a’,’b’,’c’);
8. Avoid using subqueries and optimize subqueries to join operations. Usually, when the subqueries are in the in clause and the subqueries are simple SQL(excluding union, group by, Order BY, and LIMIT clauses), the subqueries can be transformed into associated queries for optimization. The cause of poor subquery performance is as follows: The result set of a subquery cannot use an index. Generally, the result set of a subquery is stored in a temporary table. There is no index in either a temporary table in memory or a temporary table on disk, so the query performance will be affected to some extent. Especially for the subquery which returns a larger result set, its impact on query performance will be greater. Because subqueries generate a large number of temporary tables with no indexes, they consume excessive CPU and IO resources, resulting in a large number of slow queries.
Mysql has an associated cache. The size of the cache can be set using the Join_buffer_size parameter. In Mysql, if the same SQL join a table, an additional association cache will be allocated. The more tables associated in a SQL, the more memory will be used. If multiple table association operations are used in the program and the join_buffer_size is incorrectly set, the server memory may overflow, affecting the stability of the server database. A maximum of 61 tables can be associated with Mysql. You are advised to limit the number of associated tables to five.
10, reduce the number of interactions with the database database is more suitable for processing batch operations merge multiple identical operations together, can improve the processing efficiency
11. When making an OR judgment for the same column, use in instead of or in. The value of “IN” should not exceed 500.
Order by RAND () will load all the data in the table into memory, and then order all the data in memory according to the randomly generated value, and may generate a random value for each row, if the data set that satisfies the condition is very large, It consumes a lot of CPU and IO and memory resources. It is recommended to get a random value in the program and then get the data from the database
13, the WHERE clause prohibits functional transformations and computations on columns. Functional transformations or computations on columns make indexes unavailable. Where date(create_time)=’20190101′ and date(create_time)=’20190101′
14. Use UNION ALL instead of UNION when it is clear that there are no duplicate values. The UNION will remove ALL data from the two result sets after placing them in a temporary table
15. Split a large complex SQL into multiple smaller SQL. Large SQL: A logically complex SQL that requires a large number of cpus for computation MySQL: One SQL can only be performed on one CPU
11. Code of conduct for database operation
1. Batch write (UPDATE, DELETE, INSERT) operations of more than 1 million rows should be performed several times in batches. Large volume operations may cause serious master-slave delay. And only when the main library on execution is completed, only can be carried on from other libraries, so will cause the main library and from the library for a long period of time delays binlog log for the row will produce large amounts of log format Large quantities of write operation will produce large amounts of log, especially for the binary data row format, because each line will record data in the row format changes, The more data we modify at a time, the more logs we generate, and the longer it takes to transfer and recover logs, which is one reason for master-slave delays. To avoid large transaction operations and modify data in large quantities, it must be done in a single transaction, which will cause a large amount of data in the table to be locked, resulting in a large number of blocks, blocking will have a very large impact on the performance of MySQL. In particular, long blocks can fill all available connections to the database, preventing other applications in the production environment from connecting to the database, so it is important to be careful that large writes are batched.
2. Use pt-online-schema-change to modify the structure of a large table to avoid master-slave delay caused by large table modification. Avoid table locking when modifying table fields. Pt-online-schema-change first creates a new table with the same structure as the original table and changes the structure of the new table, then copies the data from the original table to the new table and adds triggers to the original table. Copy the new data from the original table to the new table. After all data is copied, the new table is named as the original table and the original table is deleted. Break down a SINGLE DDL operation into smaller batches
3. Do not grant the super permission to the account used by the program. When the number of connections reaches the maximum limit, one user with the super permission can be run.
4. For an account connecting to a database, follow the minimum permission rule. An account using a database can only be used in one DB
More exciting content, please pay attention to my public number “programmer Cow”, my personal blog site: www.kuya123.com
Source: www.jianshu.com/p/b7aa5d745… Author: Deam unlimited if you need to reprint, please contact the original author