Table of Contents
1. Introduction
2. Interception of SQL statements
3. Basic analysis commands for query optimization
4. Several directions for query optimization
5. Index optimization
 5.1. Index advantages:
 3. Multiple indexes, if the optimizer takes time, the best choice
will not be used 3. If the query retrieves more than 20% of the data, full-text scanning will be used without indexing
B-Tree includes: PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT
6. Subquery optimization
7. Rewrite the equivalent predicate:
8. Condition simplification and optimization
9, external connection optimization
10. Other query optimization
Home Database Mysql Tutorial Detailed introduction to MySQL query optimization

Detailed introduction to MySQL query optimization

Mar 26, 2017 am 11:46 AM

1. Introduction

The most important thing about a good web application is excellent access performance. The database MySQL is an integral part of the web application and an important part that determines its performance. Therefore, it is crucial to improve the performance of MySQL.

The improvement of MySQL performance can be divided into three parts, including hardware, network, and software. Among them, hardware and network depend on the company's financial resources and require a lot of money, so I won't go into them here. The software is subdivided into many types. Here we achieve performance improvement through MySQL query optimization.

Recently I read some books about query optimization, and also read some articles written by seniors online.

The following is some summary of query optimization that I compiled and learned from:

2. Interception of SQL statements

1. Comprehensive query log

2. Slow query Log

3. Binary log

4. Process list

SHOW FULL PROCESSLIST;

. .

3. Basic analysis commands for query optimization

1. EXPLAIN {PARTITIONS|EXTENDED}

2. SHOW CREATE TABLE tab;

3. SHOW INDEXS FROM tab;

 4.SHOW TABLE STATUS LIKE 'tab';

 5.SHOW [GLOBAL|SESSION] STATUS LIKE '';

 6.SHOW VARIABLES

 . . . .

PS: I personally feel that all of them are nutritionally devoid of any nutrients. Here’s the real stuff.

4. Several directions for query optimization

1. Try to avoid full-text scanning, add indexes to corresponding fields, and use indexes to query

2. Delete unused or duplicate indexes

 3. Query rewriting, equivalent conversion (predicate, subquery, join query)

 4. Delete content and repeat unnecessary statements, and streamline statements

 5. Integrate repeatedly executed statements

 6. Cache query results

5. Index optimization

 5.1. Index advantages:

 1. Maintain data integrity

  2. Improve data query performance

  3. Improve table connection operations (jion)

  4. Sort query results. If there is no index, the internal file sorting algorithm will be used for sorting, which is slower. 5. Simplify aggregated data operations. 5.2. Disadvantages of indexing. 1. The index needs to occupy a certain amount of space. Storage space

  2. Data insertion, update, and deletion will be affected by the index, and performance will be reduced. Because the data changes, the index also needs to be updated

 3. Multiple indexes, if the optimizer takes time, the best choice

 5.3. Index selection

 1. When the amount of data is large Use

  2. When the data is highly repetitive,

will not be used 3. If the query retrieves more than 20% of the data, full-text scanning will be used without indexing

5.4. Detailed study of index

Data query:

InnoDB and MyISAM in MySQL are B-Tree type indexes

B-Tree includes: PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT

 B-Tree type index is not supported (that is, when the field uses the following symbols, the index will not be used):

  >, <, >=, <=, BETWEEN, !=, < >,like '%**'

  【Here I will introduce the covering index first】

                            Around  I will introduce it in a way that I understand. Covering indexes do not really exist like primary key indexes and unique indexes. It is just a definition of certain specific scenarios for index application [another understanding: the queried column is an index column, so the column is covered by the index]. It can break through traditional limitations, use the above operators, and still use indexes for queries.

Because the queried column is an index column, there is no need to read the row, only the column field data needs to be read. [For example, if you are reading a book and need to find a certain content, and that content happens to appear in the table of contents, you don’t need to turn page by page, just locate the page in the table of contents and search]

  How to activate What about covering indexes? What is a specific scenario?

The index field just appears in the select.

Compound indexes may also have other special scenarios. For example, for a three-column composite index, you only need to have the leftmost column of the composite index appear once in select, where, group by, and order by to activate the use of the covering index.

View:

Extra in EXPLAIN displays Using index, indicating that this statement uses a covering index.

Conclusion:

It is not recommended to use select*from when querying. You should write the fields you need and add corresponding indexes to improve querying. performance.

Actual test results for the above operators: 1. In the form of select*from, where is the primary key and can be used to kill [except like] (use the primary key for query); index cannot be used at all Can.

2. Test in the form of select field a from tab where field a "above operator", the result can still be queried using the index. [Using covering index]

Other index optimization methods:

1. Use index keywords as connection conditions

2. Use compound indexes

3. Index merging or and, will involve The fields to be merged into a composite index

4. Add index to the fields involved in where, and group by

6. Subquery optimization

In from, it is a non-correlated subquery. Subqueries can be pulled up to the parent layer. In multi-table join queries, consider the join cost before selecting.

The query optimizer generally uses nested execution for subqueries, that is, executing the subquery once for each row in the parent query, so that the subquery will be executed many times. This execution method is very inefficient.

Advantages of converting subqueries into join queries:

1. The subquery does not need to be executed many times

2. The optimizer can choose different methods and connection sequences based on the information

 3. The connection conditions and filtering conditions of the subquery become the filtering conditions of the parent query to improve efficiency.

Optimization:

Subquery merging. If there are multiple subqueries, try to merge them as much as possible.

Subquery expansion, that is, pull-up becomes a multi-table query (equivalent changes are guaranteed at all times)

Note:

Subquery expansion can only expand simple queries. If the subquery If the query contains aggregate functions, GROUP BY, and DISTINCT, it cannot be pulled up.

Select * from t1 (select*from tab where id>10) as t2 where t1.age>10 and t2.age<25;

select*from t1,tab as t2 where t1.age>10 and t2.age<25 and t2.id>10;

Specific steps:

1. Merge from and from and modify the corresponding parameters

2 , merge where with where, use and to connect

3. Modify the corresponding predicate (change = in in)

7. Rewrite the equivalent predicate:

1. BETWEEEN AND Rewrite it as >=, <= and so on. Actual measurement: 100,000 pieces of data, time before and after rewriting, 1.45s, 0.06s

 2. In converts multiple or. When the field is an index, both can use the index, or is more efficient than in

3. Name like 'abc%' is rewritten as name>='abc' and name<'abd';

Note: In the million-level data test, the like query before name is not indexed is faster than the latter query; after adding an index to the field, the latter query is a little faster, but there is not much difference, because both methods are used when querying To the index.

 . . . .

8. Condition simplification and optimization

1. Combine where, having (when there are no groupby and aggregate functions), and join-on conditions as much as possible

2. Delete unnecessary parentheses, reduce the or and and tree layers of syntax, and reduce CPU consumption

 3. Constant transfer. a=b and b=2 is converted to a=2 and b=2. Try not to use variables a=b or a=@var

4. Eliminate useless SQL conditions

5. Try not to calculate expressions on the right side of the where equal sign; do not use fields in where Calculate expressions and use functions

 6. Identity transformation and inequality transformation. Example: Testing millions of data a>b and b>10 becomes a>b and a>10 and b>10 with significant optimization

9, external connection optimization

About to convert external connections For inner joins

Advantages:

1. The optimization processor handles outer joins in more steps than inner joins and is time-consuming

2. After the outer joins are eliminated, the optimizer selects multiple tables There are more choices for the connection sequence, you can choose the best

 3. You can use the table with the strictest filtering conditions as the outer surface (the front of the connection sequence is the outer loop layer of the multi-layer loop body),

It can reduce unnecessary I/O overhead and speed up algorithm execution.

The difference between on a.id=b.id and where a.id=b.id, on means the table is connected, and where means data comparison

Note: The premise must be that the result is NULL avoidance (that is, the condition is restricted to no NULL data rows, semantically speaking, it is an inner connection)

Optimization principles:

Streamline queries, eliminate connections, equivalent conversions, and remove redundant table object connections

For example: the primary key/unique key is used as the connection condition, and the intermediate table column is only used as the equivalent condition, the intermediate table connection can be removed

10. Other query optimization

1. The following will be Causes the index query to be abandoned and full-text scanning is used

 1.1. Use the != or <> operator in the where clause. Note: Primary key support. Non-primary keys do not support

1.2. Avoid using or used, so the specific situation should be analyzed on a case-by-case basis.

Similar optimization:

select * from tab name='aa' or name='bb';

Select * from tab name='aa'

   union all

   select * from tab name='bb';

    Actual measurement:

   1. One hundred thousand data test, Without any index, the above query is twice as fast as the query below.

2. In the 300,000 data test, when aa and bb are indexed separately, the following query speed is a little faster than or.

 1.3. Avoid using not in

  Not in generally cannot use indexes; primary key fields can

  1.4. Try to avoid using the judgment of null in where 

 1.5. like cannot be preceded by a percent sign like '%.com'

Solution:

1. If you must use % prefix and the data length is not large, such as URL, you can flip the data and save it Enter the database and check again. LIKE REVERSE'%.com';

                                             

  1.6. When using an index field as a condition, if it is a compound index, the field name with the leftmost prefix of the index should be used

2. Replace exists with in

Select num from a where num in(select num from b)

Select num from a where exists(select 1 from b where num =a.num)

With one million pieces of data, it takes 6.65s and 4.18s to filter 59417 pieces of data. No other optimizations were done, just replacing exists with in.

 3. The field definition is a string. There are no quotation marks when querying, and no index will be used. Full-text scanning will be performed.

[The following is an excerpt from Luantanqin’s blog post http://www.cnblogs.com/lingiu/p/3414134.html. I have not conducted the corresponding test]

4. Try to use it as much as possible table variables instead of temporary tables

5. Avoid frequently creating and deleting temporary tables to reduce the consumption of system table resources

6. If a temporary table is used, be sure to add it at the end of the stored procedure To delete all temporary tables explicitly, first truncate table, and then drop table, this can avoid long-term locking of system tables

 7. Try to avoid using cursors, because cursors are less efficient. If the cursor operation is If the data exceeds 10,000 rows, then you should consider rewriting

8. Large data volume. If the data volume is too large, you should consider whether the corresponding requirements are reasonable.

 9. Try to avoid large transaction operations and improve system concurrency.

The above is the detailed content of Detailed introduction to MySQL query optimization. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.

See all articles