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

I do not know if you have any understanding of the previous articles on detailed methods of optimizing MYSQL queries, today I am here to give you a brief introduction. If you are interested, let's take a look at the body. I believe you will gain something after reading the detailed methods of optimizing MYSQL query.

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

In addition to ensuring that a record is uniquely marked, the index can also enable the MySQL CVM to obtain results from the database more quickly. Indexes also play a very important role in sorting.

Mysql indexes may take up extra space and reduce the performance of inserts, deletions, and updates to some extent. However, if your table has more than 10 rows of data, the index can greatly reduce the execution time of the lookup.

It is strongly recommended to use "worst-case data samples" to test MySql queries to gain a clearer understanding of how queries 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 forces the Mysql CVM to perform a full table scan to get the data it is looking for.

Model, Mysql provides a special Explain statement to analyze the performance of your query statement. When you add a query statement to the end of the keyword, MySql displays all the information the optimizer has about the statement.

If we analyze the above query with the declare statement, 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 | Using where | + -- +

As you can see, the optimizer shows very important information that can help us fine-tune the database tables. First, MySql performs a full table scan because key is listed as Null. Second, the MySql cloud server has made it clear that it will scan 500 rows of data to complete this query.

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

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 get the following result:

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 | Extra | +-+-- -- +-+ | 1 | SIMPLE | customers | NULL | ref | customer_id | customer_id | 13 | const | 1 | 100.00 | NULL | + -+

From the output above, it is obvious that the MySQL CVM will use the index customer_id to query the table. You can see that the number of rows that need to be scanned is 1. Although I only execute this query in a table with 500 rows, the index is more optimized when retrieving a larger dataset.

two。 Optimizing Like statements with Union

Sometimes, you may need to use the or operator in your 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 if one query has an optimized index and the other query has an optimized index.

For example, if an index exists on first_name and last_name respectively, execute the following query statement:

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

The above query is much slower than the following query that uses union to merge two query statements that make full use of the query.

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 wildcards

Mysql cannot use the index when a leading wildcard exists in the query. Taking the above student table as an example, the following query causes MySQL to perform a full table scan when the first_name field is indexed.

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

Using explain analysis, the following results are obtained:

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 | 50011.11 | Using where | +-+ -+

As shown above, Mysql scans all 500 rows of data, which makes the query extremely slow.

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

If you are faced with using wildcards to query data, but do not want to degrade the performance of the database, you should consider using MySQL's full-text search (FTS) because it is much faster than wildcard queries. In addition, FTS can return related results of better quality.

The statement to add a full-text 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 specify the columns (first_name, last_name) that we want to match for the search keyword Ade. If you query the optimizer for the execution of the statement above, you will get the following result:

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 database architecture and standardize

First, normalize all database tables, even if there may be some loss. For example, if you need to create two tables to record customers and orders data, you should refer to the customer with the customer id on the order table, not the other way around. The following figure shows a database architecture designed without any data redundancy.

In addition, use the same data type class storage for similar values.

Use the best data type

MySQL supports a variety of data types, including integer,float,double,date,datetime,varchar,text, etc. When designing database tables, you should use the shortest data type that satisfies the characteristics as much as possible.

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, with values ranging from-128 to 128. If a field needs to store date-type values, it is better to use the datetime type because there is no need for complex type conversions when querying.

Use Integer when the values are all numeric types. Values of type Integer are faster than values of type text when calculating.

Avoid NULL

NULL means that the column does not have any value. You should avoid these types of values as much as possible because they can damage database results. For example, you need to get the sum of all the order amounts 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 an exception in the calculation result. In some cases, you may need to define a default value for the column.

What do you think of this article after reading the detailed method of optimizing MYSQL query? If you want to know more about it, you can continue to follow our industry information section.

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.