Shulou(Shulou.com)05/31 Report--

This article introduces what are the MySQL access standards, the content is very detailed, interested friends can refer to, hope to be helpful to you.

I. Database design

1. Table structure design

-the bigint type is recommended for the self-incrementing column (auto_increment attribute) in the table-the first choice is to use a unique key that is not empty, then choose the self-increment column or the generator does not use frequently updated columns, and try not to select string columns. Do not use UUID MD5 HASH-the status status, type type and other fields with few choices in the business recommend using tinytint or smallint type-the int type is recommended for the IP address field in the business-the creation time field create_time and the last update time field update_time of the row data must be in the active large table-all fields in the table must be NOT NULL attributes Business can define default values as needed-use decimal to store exact floating-point numbers (do not use floating-point types). Types such as enum,set,blob,text are not recommended. For large tables, fields such as text and blob must be split or tables must be created independently.

2. Index design

-avoid redundant indexes: avoid indexing the same field, which needs to be evaluated according to the SQL statements accessed-only one index can be used for a table, do not index separately on each query condition field-the number of indexes in a single table does not exceed 7, the number of fields in a single index does not exceed 5-do not index on null columns-do not index on low cardinality columns For example, "gender"-compound index field sort, the most differentiated field comes first-core SQL gives priority to override index-use prefix index on string-prefix length does not exceed 8 characters and must be leftmost prefix

3. Character set and check set

-the character sets of databases and tables must be consistent, and the character sets of all tables must be the same. Only the character sets of all tables in utf8; database must be consistent-the character sets of master and slave databases must be the same-the character sets of front-end programs or environment variables must be consistent with the character sets of databases and tables.

4. Other requirements

-Foreign keys, temporary tables, views, custom functions, stored procedures and triggers are not recommended. On SSD hard disk, the number of rows of data in a single table cannot exceed 50 million or the storage space must not exceed 20 million on 30GB-SAS hard disk, or the storage space must not exceed 20 million or storage space must be greater than 15GB. Before launching, DBA must give an expansion plan for the database and table based on the business access volume and data growth within one year.

II. SQL compilation

1 、 select

-SELECT statement must specify a specific field name, forbid writing as "select *"-SELECT statement forbids the use of UNION, UNION ALL is recommended, and the number of UNION clauses is limited to 5

2 、 DML

The-INSERT statement must specify a specific field name, not INSERT VALUES (...) Form-the parameter value type passed in the program by the SQL statement must be the same as the type of the field in the database

3. Multi-table federated query

Aliases are recommended for multi-table join queries, and aliases are used to refer to fields and databases in the SELECT list. Table format, such as "select a.cid from iknow_qb. Tblreply a where..." -in production systems, it is not recommended to join more than 3 tables (including 3 tables) in a single query. In production systems, external associations, including left outer association, right outer association and full outer association are strongly not recommended. In queries with multi-table joins, tables with smaller result sets must be selected in the driver table-SQL statements nested in multi-level subqueries are prohibited. It is recommended to rewrite the format of table sequential join-try not to perform multi-table join operations in INSERT | UPDATE | DELETE | REPLACE statements

4. Transaction

-the number of rows operated by the INSERT | UPDATE | DELETE | REPLACE statement in the transaction is controlled at 2000, and the number of parameters passed by the IN list in the WHERE clause is controlled at 2000. When operating data in batches, it is necessary to control the transaction interval and perform the necessary sleep. The specific values are given by DBA, and the program must have interrupt processing capability-insert operations for tables with auto_increment attribute fields. Concurrency needs to be controlled within 200 seconds-SQL level / transaction level / table storage engine type in master-slave database should be consistent, mixed use of storage engine will lead to master-slave data inconsistency or master-slave synchronization interruption-read-only queries insensitive to synchronization delay must be executed on slave database Read-only queries that are sensitive to synchronization latency can be executed on the main library-try not to use set statements in front-end programs, including set names, set sql_mode, set isolation_level, etc.

5. Table scanning method:

-SELECT | UPDATE | DELETE | REPLACE must have WHERE clause, and the condition of WHERE clause must be searched by index-full table scan on large tables is strongly not recommended in production databases, but full table scans can be used for static tables with less than 5000 rows. Full table scanning and full table export (dump) are recommended in backup or offline reading libraries-search using only fully fuzzy LIKE conditions (such as like'% aj%') is prohibited in the WHERE clause. There must be other query conditions-functions cannot be used on index columns or composite index leading columns in the WHERE clause

6. Sorting and grouping

-queries with distinct, order by and group by clauses, with intermediate result sets limited to 10000 rows.-sorting and grouping of large result sets (intermediate result sets exceeding 10000 rows) are implemented on the program side.

7. Other requirements

-the size of a single SQL statement is limited to 5MB-the intermediate and final result sets of SQL statements in the production database must be limited to 5MB-hints are prohibited for SQL statements in the production database, such as force index,ignore index,straight_join,sql_no_cache, etc.-full-text search function is prohibited-event (EVENT) function is prohibited-do not use or operate the mysql library and test library in the program Prohibit the creation of test or libraries starting with test-prohibit the use of user-defined variables in mysql-do not perform real-time statistics or summarization of business in the online database, and can be exported using other tools or offline backup libraries-reduce the number of interactions with the database INSERT. ON DUPLICATE KEY UPDATE REPLACE INTO, INSERT IGNORE, INSERT INTO VALUES (), (), UPDATE... WHERE ID IN (An and B, C, …) -do not use negative queries, such as not inline queries =, not like-do not perform mathematical and functional operations on index columns-queries that do not use% leading, such as like "% abc"-avoid implicit conversions between large table data types (which often have performance problems) can lead to index failures, such as converting numbers to strings

III. Introduction of related features of MySQL

1. The processing characteristics of SQL by MySQL.

-SQL request processing can only use one core-there is no SQL compilation cache, SQL stored procedures are hard parsing-operational comparison is not supported on indexes-in most cases a Query can only use one index-does not support Hash jion (currently supported by MariaDB)-thread-based external service model (high number of connections, serious performance degradation)-poor support for subqueries, outer queries generally can not walk the index

2. Storage size supported by MySQL

-single table space 64T, each table has only one table space, that is, the maximum 64T-Innodb Logfile per single table cannot exceed 512g-the row size limit is 65535 byte-each table has a maximum of 1027 fields-a maximum of 64 common indexes per table

3. Reference index of MySQL production.

-A single instance should not exceed 1T, except for the surrounding LOG. The maximum is not recommended to exceed 5T-the maximum recommended for general OLTP single tables is no more than 10G-usually in the case of buffer hits: Select can reach 3-6W/S Insert in the case of continuous clustered index to 2w-3W/S in the case of discontinuous clustered index, that is, 200-300 S UPDATE data can reach 3K in memory. / S DELETE data can reach 1k/s in memory There may be less-database bottleneck: IO capabilities, find ways to use sequential IO, reduce random IO

IV. Form establishment and verification

To build a database or form, you need to find the DBA evaluation table statement in advance, and fill in the form and SQL audit template:

SQL audit template. Doc

5. Capacity assessment

1. Overview of capacity assessment

All databases go online: new clusters, new databases and new tables need to be evaluated in advance to prevent subsequent operations such as adjustment, expansion and migration of online businesses due to capacity problems, thus affecting the online business. Capacity includes: traffic (read and write), data and growth, disk space capacity.

2. Table capacity

The capacity of the table is mainly evaluated from the number of records, average length, growth, reading and writing, and total size of the table. Generally speaking, for OLTP tables, it is recommended that a single table should not exceed 2000W rows of data, with a total size of less than 15G. Number of visits: the number of reads and writes per table is less than 1600 paces.

For tables with millions of data in a single table, the length of each row should not be too long and should not be placed in the same table as text, blob and other field types. (the MySQL data page size is 16K, and the longer each row of records, the fewer records per data page will store, so more IO will be generated when retrieving the data.)

3. Instance capacity

MySQL is a thread-based service model, so in some scenarios with high concurrency, a single instance can not make full use of the server's CPU resources, and the throughput will be stuck in the mysql layer, especially for the mysql5.5 version. A great deal of optimization has been made in mysql version 5.6, and the percona version has thread pool, which can fully deal with the high consumption of CPU context switching in high concurrency scenarios.

The throughput of a single instance QPS is generally controlled within 20000 seconds, and the slave delay should also be taken into account. For the mysql5.6 version, you can consider dividing the database and then dividing the table, making full use of the multi-thread replication based on the library level in version 5.6, so as to improve the write throughput.

4. Disk space

The server generally hosts multiple database instances, so before each instance goes online, it is necessary to evaluate the amount of data of each instance, as well as the growth of several major large tables in 1-2 years, and evaluate the amount of data as accurately as possible to each field. For businesses whose growth is not particularly fast (if it doubles in half a year), it is recommended that the amount of data in 1-2 years will eventually account for less than 70% of the disk utilization. At the same time, for some fast-growing data, we can consider using large slow disks for data archiving.

What about the MySQL access criteria to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.