Improve work efficiency: MySQL optimization tips

The content of this article is about improving work efficiency: MySQL optimization skills have certain reference value. Friends in need can refer to them. I hope it will be helpful to you.

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

1) In addition to ensuring that a record is uniquely marked, the index can also It is the MySQL server that gets 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.

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

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 will force the Mysql server to perform a full table scan to obtain the data being sought.

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

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

2. Use Union to optimize the Like statement

1) Sometimes, you may need to use the or operator for comparison in the query. 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 respectively, execute the following query statement:

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

Compared with the above query and the following query that uses union to merge two query statements, Much slower.

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 there are leading wildcards 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:

 | 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. Optimize database architecture

1) Normalization

First, normalize all database tables, even if there may 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.

5. Use the best data type

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

The above is the detailed content of Improve work efficiency: MySQL optimization tips. For more information, please follow other related articles on the PHP Chinese website!