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

I don't know if you have any knowledge of similar MySQL optimization techniques before, but today I'm going to tell you a little bit about it. If you are interested, let's take a look at the text. I believe you will gain something after reading what skills you have in optimizing MySQL.

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

1) the index not only ensures that a record is uniquely marked, but also enables the MySQL CVM to obtain the 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.

2) it is strongly recommended that "worst-case data samples" be used to test MySql queries to gain a clearer understanding of how queries behave in production.

3) 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.

4) Mysql provides a special Explain statement to analyze the performance of your query. 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 | 500 | 10.00 | 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.

5) 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 | + -+

two。 Optimizing Like statements with Union

1) 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 wherelast_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:

| | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | students | NULL | ALL | NULL | Using where | + -+

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

4. Optimize database architecture

1) Standardization

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.

5. Use the best data type

1) MySQL supports various data types, including integer,float,double,date,datetime,varchar,text. 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.

What are the skills of optimizing MySQL after reading this article, what do you think? 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.