How to optimize MYSQL query? Introduction to mysql query optimization methods

The content of this article is about the simple implementation code of process pool in python. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. Add indexes on all columns used for where, order by and group by

except index It can ensure that a record is uniquely marked, and it can also enable the MySQL server to obtain results from the database faster. Indexes also play a very important role in sorting.

Mysql's index may occupy additional space and reduce the performance of insertion, deletion and update to a certain extent. However, if your table has more than 10 rows of data, indexing can greatly reduce the search execution time.

It is highly recommended to use "worst case data samples" to test MySql queries to get a clearer understanding of how the query will behave in production.

Suppose you are executing the following query statement in a database table with more than 500 rows:

mysql>select customer_id, customer_name from customers where customer_id='345546'

The above query will force the Mysql server to perform a full table scan to obtain the data being sought.

model, Mysql provides a special Explain statement to analyze the performance of your query statement. When you add a query statement after the keyword, MySql will display all the information the optimizer has about the statement.

If we use the explain statement to analyze the above query, we will get the following analysis results:

mysql> explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

As you can see, the optimizer displays very important information, which can help us Fine-tune database tables. First, MySql will perform a full table scan because the key column is Null. Secondly, the MySql server has made it clear that it will scan 500 rows of data to complete this query.

In order to optimize the above query, we only need to add an index m on the customer_id column:

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

If we execute the explain statement again, we will get the following results :

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

From the above output results, it is obvious that the MySQL server will use the index customer_id to query the table. You can see that the number of rows to be scanned is 1. Although I am only executing this query on a table with 500 rows, the index is even more optimized when retrieving a larger data set.

2. Use Union to optimize the Like statement

Sometimes, you may need to use the or operator in the query for comparison. When the or keyword is used too frequently in the where clause, it may cause the MySQL optimizer to mistakenly choose a full table scan to retrieve records. The union clause can make queries execute faster, especially when one of the queries has an optimized index and the other query also has an optimized index.

For example, when there are indexes on first_name and last_name, execute the following query statement:

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

The above query and the following use union Compared with merging two queries that fully utilize the query statement, the speed is much slower.

mysql> select * from students where first_name like 'Ade%' union all select * from students where last_name like 'Ade%'

3. Avoid using expressions with leading wildcard characters

Mysql cannot use the index when there is a leading wildcard character in the query. Taking the student table above as an example, the following query will cause MySQL to perform a full table scan and add an index to the first_name field in time.

mysql> select * from students where first_name like '%Ade'

Use explain analysis to get the following results:

mysql> explain select * from students where first_name like  '%Ade'  ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

As shown above, Mysql will scan all 500 rows of data, which will make the query extremely slow.

4. Make full use of MySQL’s full-text search

If you are faced with using wildcard characters to query data, but do not want to reduce the performance of the database, you should consider using MySQL’s full-text search (FTS). Because it is much faster than wildcard query. In addition to this, FTS is able to return better quality relevant results.

The statement to add a full-text search index to the student sample table is as follows:

mysql> alter table students add fulltext(first_name, last_name)';
mysql> select * from students where match(first_name, last_name) against ('Ade');

In the above example, we specified the search keyword Ade that we want to match Columns (first_name, last_name). If the query optimizer executes the above statement, you will get the following results:

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

5. Optimize the database schema

Normalization

First, normalize all database tables, even if possible There will be some losses. For example, if you need to create two tables to record customers and orders data, you should reference the customer by customer ID in the orders table, not the other way around. The diagram below shows the database architecture designed without any data redundancy.

In addition, use the same data type class to store similar values.

Use the best data type

MySQL supports various data types, including integer, float, double, date, datetime, varchar, text, etc. When designing database tables, you should try to use the shortest data type that can satisfy the characteristics.

For example, if you are designing a system user table and the number of users will not exceed 100, you should use the 'TINYINT' type for user_ud. The value range of this type is -128 to 128. If a field needs to store date type values, it is better to use datetime type, because there is no need to perform complex type conversion when querying.

When the values ​​are all numeric types, use Integer. Values ​​of type Integer are faster than values ​​of type Text when performing calculations.

Avoid NULL

NULL means that the column has no value. You should avoid these types of values ​​if possible because they can harm database results. For example, you need to get the sum of the amounts of all orders in the database, but the amount in an order record is null. If you don't pay attention to the null pointer, it is likely to cause exceptions in the calculation results. In some cases, you may need to define a default value for a column.

The above is the detailed content of How to optimize MYSQL query? Introduction to mysql query optimization methods. For more information, please follow other related articles on the PHP Chinese website!