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

This article is to share with you about the mysql optimization framework, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

MySQL optimization framework

1. SQL statement optimization

two。 Index optimization

3. Database structure optimization

4. InnoDB table optimization

5. MyISAM table optimization

6. Memory table optimization

7. Understand the query execution plan

8. Buffering and caching

9. Lock optimization

10. MySQL server optimization

11. Performance evaluation

12. MySQL optimized inside story

MySQL optimization needs to be coordinated at three different levels: MySQL level, OS level, and hardware level. MySQL-level optimization includes table optimization, query optimization and MySQL server configuration optimization, and various data structures of MySQL ultimately act on OS and hardware devices, so it is also necessary to understand the needs of each structure for OS-level resources and ultimately lead to CPU and Icano operations, etc., and on this basis, reduce the need for CPU and Icano operations as much as possible to improve their efficiency.

The optimization focus at the database level:

1. Whether the relevant properties of the table structure are set correctly, especially whether the field type of each field is the best. At the same time, whether or not appropriate tables and table fields are used for specific types of work organizations will also affect system performance. For example, scenarios where data is updated frequently should use more tables and each table should have a structure of fewer fields. Scenarios of complex data query or analysis should use fewer tables and the structure of more fields per table, and so on.

2. Whether an appropriate index is created for efficient query.

3. Whether the appropriate storage engine is selected for each table, and make effective use of the advantages and characteristics of the selected storage engine.

4. Whether the appropriate row format (row format) is selected for the table based on the storage engine. For example, compressed tables will reduce the need for I-stroke O operations and take up less disk space in read-write operations. InnoDB supports the use of compressed tables in read-write application scenarios, but MyISAM can only use compressed tables in a read environment.

5. Whether the appropriate locking strategy is used, such as using shared locks in concurrent operation scenarios and using exclusive locks for higher priority requirements. At the same time, you should also consider the types of locks supported by the storage engine.

6. Whether the appropriate memory space is set for InnoDB buffer pool, MyISAM key cache and MySQL query cache so that frequently accessed data can be stored without causing page swapping out.

Operating system and hardware level optimization focus:

1. Whether a suitable CPU is selected for the actual workload, such as using faster CPU or even more CPU for CPU-intensive application scenarios, and using more CPU for scenarios with more queries. Based on the multi-core and hyperthreading (hyperthreading) technology, the modern CPU architecture is becoming more and more complex and the performance is getting stronger and stronger, but the utilization of the parallel computing power of the multi-CPU architecture by MySQL is still not satisfactory, especially the older versions such as MySQL 5.1 can not even give play to the advantages of multi-CPU. However, there are usually two types of CPU performance improvement goals that need to be achieved: low latency and high throughput. Low latency requires faster CPU, because only one query can be used. In scenarios where many queries need to be run at the same time, multi-CPU can provide better throughput. However, its effectiveness depends on the actual work scenario, because MySQL can not run efficiently on multi-CPU, and its support for the number of CPU is limited. In general, newer versions can support 16 to 24 CPU or more.

2. Whether you have the right size of physical memory, and balance the memory and disk resources through reasonable configuration, so as to reduce or even avoid disk Ibank O. Caching techniques are usually used in modern programming to improve performance based on the principle of locality. this is especially true for database systems that frequently manipulate data-a well-designed database cache is usually more efficient than an operating system for general tasks. Caching can effectively delay and optimize writes, but it can also eliminate writes and comprehensively consider the scalability of storage space. It is also very important to select reasonable external storage devices for business.

3. Whether the appropriate network equipment is selected and the network is configured correctly also has a great impact on the overall system. Delay and bandwidth are the limiting factors of network connection, while common network problems, such as packet loss, even a small packet loss rate will agree to a significant decline in performance. What is more important is to adjust the settings of the off network in the system as needed to efficiently handle a large number of connections and small queries.

4. Whether the appropriate file system is selected based on the operating system. Actual tests show that the performance of most file systems is very close, so it is not cost-effective to choose file systems for performance. However, considering the repair ability of the file system, you should use journaling file systems such as ext3, ext4, XFS, and so on. At the same time, turning off certain features of the file system, such as access time and read-ahead behavior, and choosing a reasonable disk scheduler will often help improve performance.

5. MySQL uses a separate thread in response to each user connection, plus internally used threads, special purpose threads and any other threads created by the storage engine. MySQL needs to manage a large number of threads effectively. The NPTL thread library on Linux systems is lighter and more efficient. Thread pool plug-ins have been introduced in MySQL 5.5, but its utility is unclear.

Best practices for using the InnoDB storage engine:

1. Create a primary key based on the most commonly used fields or field combinations in MySQL query statements. If there is no suitable primary key, it is best to use a field of AUTO_INCRMENT type as the primary key.

2. Consider using multi-table queries as needed, and establish constraint relationships between these tables through foreign keys.

3. Close autocommit.

4. Use transactions (START TRANSACTION and COMMIT statements) to combine related modification operations or an overall unit of work, and of course you should not create an overly large unit of execution.

5. By stopping using LOCK TABLES statements, InnoDB can efficiently handle concurrent read and write requests from multiple sessions. If you need exclusive access on a series of lines, you can use SELECT... FOR UPDATE locks only rows that need to be updated.

6. Enable the innodb_file_per_table option to store the data and indexes of each table separately.

7. Evaluate whether data and access modes can benefit from InnoDB's table compression (use the ROW_FORMAT=COMPRESSED option when creating tables), and if so, enable compression.

EXPLAIN statement parsing:

The identifier of an id:SELECT statement, usually a number, indicating the position of the corresponding SELECT statement in the original statement. An entire query without a subquery or union has only one SELECT statement, so its id is usually 1. In federated or subquery statements, inner SELECT statements are usually numbered in the order they are in the original statement. However, UNION operations usually end up with a row with an id of NULL, because the results of UNION are usually saved in a temporary table, and MySQL needs to get the results in this temporary table.

Select_type:

That is, the SELECT type, as shown in the following value list:

SIMPLE: simple query, that is, no union or subquery is used

The outermost query of PRIMARY:UNION or the first query

UNION: relative to PRIMARY, is the second and subsequent query of the federated query

DEPENDENT UNION: same as UNION, but in a federated subquery (that is, the UNION query itself is a subquery)

Implementation result of UNION RESULT:UNION

SUBQUERY: non-subordinate subquery, which the optimizer usually thinks needs to be run only once

DEPENDENT SUBQUERY: dependent subqueries, which the optimizer believes needs to be run once for each row of the peripheral query, such as for subqueries in the IN operator

DERIVED: a subquery for the FROM clause, that is, a derived table query

Table:

The table name of the table to which the output information relates may also be displayed in the following format:

Id is the result of executing the federated query for M and N queries

The result set of the query execution for which id is N

Type:

The official manual of MySQL explains that the role of type is "type of join", but it more precisely means "record access type", because its main purpose is to show how MySQL finds the required rows in the table. There are typically record access types like the following:

System: there is only one row in the table, which is a special case of type const

Const: there is at most one matching row in the table, which is read only once at the beginning of the query, so the value in this field can be regarded by the optimizer as a constant (constant). When a query is based on a PRIMARY KEY or UNIQUE NOT NULL field and compared with a constant, its type is const, and its execution speed is very fast.

Eq_ref: similar to const, there is at most one matching row in the table, but the value compared is not a constant, but from other tables; ed_ref appears when indexes of type PRIMARY KEY or UNIQUE NOT NULL are used entirely for join operations to compare equivalents (=); this is the best access type except system and const

Ref: the index type when querying is not PRIMARY KEY or UNIQUE NOT NULL, so that the matching rows may not be unique, or only the left prefix of the index can be used instead of all access types; ref can be used for index-based fields for = or operation

Fulltext: used in FULLTEXT indexes to retrieve records using plain text matching.

Ref_or_null: similar to ref, but you can search for null values in addition

Index_merge: using the record access type of "index merge optimization", accordingly, multiple indexes used will appear in the key field (the output result of EXPLAIN), and the longest length list of the index used will appear in the key_len field; the operation of merging the rows obtained by multiple "range scan (range scan)" into a result set is called index merging (index merge).

Unique_subquery: used in "key value unique" access type scenarios in subqueries in the IN comparison operator, such as value IN (SELECT primary_key FROM single_table WHERE some_expr)

Index_subquery: similar to unique_subquery, but the key values in subqueries are not unique

Range: an index scan with range restrictions, rather than a full index scan, which starts at a point in the index and returns rows that match that range of values; accordingly, the index used is output in its key field (the output of EXPLAIN), and the key_len field contains the length of the longest part of the index used; range is usually used to compare indexes with constants

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.