Shulou(Shulou.com)06/01 Report--

This article mainly introduces "what is the knowledge of MySQL specification". In daily operation, I believe that many people have doubts about the knowledge of MySQL specification. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the question of "what is the knowledge of MySQL specification?" Next, please follow the editor to study!

6. How to choose the order of index columns

The purpose of establishing an index is to search for data through the index, reduce random IO, and increase query performance. The less data the index can filter out, the less data will be read from the disk.

The one with the highest differentiation is placed on the far left of the federated index (differentiation = the number of different values in the column / the total number of rows in the column).

Try to place columns with small field length on the far left side of the federated index (because the smaller the field length, the larger the amount of data a page can store, and the better IO performance will be).

The most frequently used columns are placed to the left of the federated index (so that fewer indexes can be built).

Avoid the establishment of redundant and duplicate indexes

Because this increases the time it takes the query optimizer to generate an execution plan.

Duplicate index example: primary key (id), index (id), unique index (id)

Examples of redundant indexes: index (aformab), index (aforme b), index (a)

Priority should be given to overriding the index.

Override indexes are preferred for frequent queries.

Override index: an index that contains all query fields (fields contained in where,select,ordery by,group by)

Benefits of overriding an index:

Avoid the secondary query of InnoDB table indexing

InnoDB is stored in the order of clustered index. For InnoDB, the secondary index stores the primary key information of the row in the leaf node. If the secondary index is used to query the data, after finding the corresponding key value, we have to do a second query through the primary key to get the data we really need. In the overlay index, all the data can be obtained from the key value of the secondary index, which avoids the secondary query of the primary key, reduces the IO operation, and improves the query efficiency.

Random IO can be changed into sequential IO to speed up query efficiency.

Because the overlay index is stored in the order of key values, for IO-intensive range lookups, there is much less IO than randomly reading each row from the disk, so the overlay index can also be used to transform the randomly read IO of the disk into the order IO of the index lookup.

IX. Index SET specification

Try to avoid using foreign key constraints.

Foreign key constraints (foreign key) are not recommended, but be sure to build indexes on the associated keys between tables.

Foreign keys can be used to ensure the referential integrity of data, but it is recommended to be implemented on the business side.

Foreign keys affect the write operations of parent and child tables, thus reducing performance.

Database SQL development specification

1. It is recommended to use precompiled statements for database operation.

Precompiled sentences can reuse these plans, reduce the time needed for SQL compilation, and solve the problem of SQL injection caused by dynamic SQL. It is more efficient than passing SQL statements that the same sentence can be parsed at once and used many times, thus improving the processing efficiency.

two。 Avoid implicit conversion of data types

Implicit conversion can cause the index to fail. Such as:

Select name,phone from customer where id = '111 million. Make full use of the indexes that already exist on the table

Avoid using query conditions with double% numbers.

For example, a like'3% indexes, (if there is no pre -%, only post%, you can use the index on the column)

A SQL can only use one column in the composite index to query the range.

For example, if there is a federated index with column an in the query condition, then the index on column a will not be used. When defining the federated index, column a should be placed on the right side of the federated index if column a uses range lookup.

Use left join or not exists to optimize not in operations

Because not in also usually uses indexes to fail.

4. When designing a database, future extensions should be considered.

5. The program connects to different databases and uses different accounts to query across databases.

Leave room for database migration and subdatabase and table

Reduce business coupling

Avoid the security risk caused by excessive permissions

6. Prohibit the use of SELECT * must use SELECT query

Reason:

Consume more CPU and IO for network bandwidth resources

Cannot use override index

Can reduce the impact of table structure changes

7. Prohibit the use of INSERT statements without field lists

Such as:

Insert into values ('axiomagy, baccalaureate, cinema,')

You should use:

Insert into t (C1, c2, c3) values ('axiomagrical, baccalaureate, c'); 8. Avoid using subqueries, which can be optimized to JOIN operations

The general subquery is in the in clause, and when the subquery is simple SQL (does not include union, group by, order by, limit clauses), the subquery can be transformed into an associated query for optimization.

Reasons for poor performance of subqueries:

The result set of subquery can not use index, the result set of general subquery is stored in temporary table, and there is no index in memory temporary table or disk temporary table, so the query performance will be affected to a certain extent.

Especially for subqueries with large result sets, the greater the impact on query performance.

Because the subquery will produce a large number of temporary tables and no indexes, it will consume too much CPU and IO resources, resulting in a large number of slow queries.

9. Avoid using JOIN to associate too many tables

For MySQL, there is an associated cache, and the size of the cache can be set by the join_buffer_size parameter.

In MySQL, one more associative cache is allocated for one join of the same SQL, and the more tables associated in a SQL, the more memory is consumed.

If the operation of multi-table association is widely used in the program, and the setting of join_buffer_size is unreasonable, it is easy to cause the server memory overflow, which will affect the stability of the server database performance.

At the same time, for association operations, temporary table operations will occur, which will affect query efficiency. MySQL allows a maximum of 61 tables to be associated, and no more than 5 tables are recommended.

10. Reduce the number of interactions with the database

The database is more suitable for handling batch operations and merging multiple identical operations together, which can improve the processing efficiency.

11. When making or judgment for the same column, use in instead of or

The value of In should not exceed 500. in operations can make more efficient use of indexes, and or rarely uses indexes in most cases.

twelve。 Do not use order by rand () for random sorting

All eligible data in the table is loaded into memory, and all data is sorted in memory according to randomly generated values, and a random value may be generated for each row, which consumes a lot of CPU and IO and memory resources if the dataset that meets the criteria is very large.

It is recommended to get a random value in the program and then get the data from the database.

13. Function conversion and computation of columns are prohibited in the WHERE clause

When a column is functionally converted or evaluated, the index cannot be used.

Not recommended:

Where date (create_time) = '20190101'

Recommended:

Where create_time > = '20190101' and create_time < '20190102' 14. Use UNION ALL instead of UNION when there is clearly no duplicate value

UNION puts all the data from the two result sets into a temporary table before deduplicating.

UNION ALL no longer deduplicates the result set.

15. Split a complex large SQL into multiple small SQL

Large SQL: a SQL that is logically complex and takes up a lot of CPU for calculation.

MySQL: a SQL can only be calculated using one CPU.

After the SQL is split, the processing efficiency can be improved by parallel execution.

Code of conduct for database operation

1. Batch write operations (UPDATE, DELETE, INSERT) of more than 1 million lines need to be performed in batches for multiple times

Large quantities of operations may cause serious master-slave delays

In the master-slave environment, a large number of operations may cause serious master-slave delay. Large-batch write operations usually take a long time to execute, but only when the execution on the master database is completed, it will be executed on other slave libraries, so it will cause a long delay between the master library and the slave library.

When Binlog logs are in row format, a large number of logs will be generated.

Mass write operations will generate a large number of logs, especially for binary data in row format. Since modifications to each row of data are recorded in row format, the more data we modify at a time, the more logs will be generated, and the longer it takes for log transmission and recovery, which is one of the reasons for the master-slave delay.

Avoid large transaction operations

Mass modification of data must be carried out in a transaction, which will cause a large number of data in the table to be locked, resulting in a large number of blocking, which will have a great impact on the performance of MySQL.

In particular, long-term blocking will fill the available connections to all databases, which will prevent other applications in the production environment from connecting to the database, so it is important to pay attention to batch writes.

two。 Use pt-online-schema-change to modify the table structure for large tables

Avoid master-slave delay caused by large table modification

Avoid locking tables when modifying table fields

The modification of the data structure of large tables must be careful, which will cause serious table locking operations, especially in the production environment, which cannot be tolerated.

Pt-online-schema-change will first create a new table with the same structure as the original table, and modify the table structure on the new table, then copy the data from the original table to the new table, and add some triggers to the original table.

The new data in the original table is also copied to the new table. After all the data in the row is copied, the new table is named the original table, and the original table is deleted. The original DDL operation is divided into several small batches.

3. It is forbidden to grant super permissions to accounts used by the program.

When the maximum number of connections is reached, one user with super permission to connect to super can only be used by the DBA account dealing with the problem.

4. For the program to connect to the database account, following the principle of minimum permissions, the database account can only be used under one DB, and the account used by cross-database programs is not allowed to have drop permission in principle. At this point, the study of "what is the knowledge of MySQL specification" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.