This article is about improving work efficiency: MySQL optimization skills, there is a certain reference value, there is a need for friends can refer to it, I hope to help you.
Add index to all columns where, ORDER by, and group by
1) In addition to ensuring that a record is uniquely marked, the index also enables the MySQL server to retrieve results from the database more quickly. Indexes also play a big role in sorting.
Mysql indexes can take up extra space and reduce insert, delete, and update performance somewhat. However, if your table has more than 10 rows of data, indexes can greatly reduce the execution time of lookups.
2) It is highly recommended to test MySql queries with a “worst-case data sample” to get a clearer picture of how queries behave in production.
3) Suppose you are executing the following query in a database table with more than 500 rows:
|
1
|
mysql>select customer_id, customer_name from customers where customer_id=’345546′
|
The above query forces the Mysql server 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 queries. When you add a query statement to the keyword, MySql displays all of the optimizer’s information about the statement.
If we analyze the above query with the Explain statement, we get the following analysis results:
|
1
2
3
|
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 | | NULL ALL | NULL | NULL | NULL | 500 | | 10.00 Using the where |
|
As you can see, the optimizer displays very important information that helps us fine-tune our database tables. First, MySql performs a full table scan because the key column is Null. Second, the MySql server has explicitly stated that it will scan 500 rows of data to complete this query.
5) To optimize the above query, add index m to customer_id (customer_id) :
|
1
2
3
|
mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02sec)
Records: 0 Duplicates: 0 Warnings: 0
|
If we execute the explain statement again, we get the following result:
|
1
2
3
4
|
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 | | NULL | 100.00
+—-+————-+———–+————+——+—————+————-+———+——-+——+———-+– —–+
|
2. Optimize the Like statement with Union
1) Sometimes, you may need to use the OR operator in your query for comparison. When the OR keyword is used too often in the WHERE clause, it may cause the MySQL optimizer to incorrectly select 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 first_name and last_name have indexes respectively, execute the following query:
|
1
|
mysql> select * from students where first_name like ‘Ade%’ or last_name like ‘Ade%’
|
The above query is much slower than the following query, which uses union to combine two fully utilized query statements.
|
1
|
mysql> select * from students where first_name like ‘Ade%’ union all select * from students wherelast_name like ‘Ade%’
|
3. Avoid expressions with leading wildcards
Mysql cannot use indexes when leading wildcards exist in queries. MySQL > select * from student; MySQL > select * from student;
|
1
|
mysql> select * from students where first_name like ‘%Ade’
|
The explain analysis results are as follows:
|
1
2
3
|
| possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———-+————+——+—————+——+———+——+——+———-+———– — — + | | 1 SIMPLE | students | NULL | | NULL ALL | NULL | NULL | NULL | 500 | | 11.11 Using the where |
+—-+————-+———-+————+——+—————+——+———+——+——+———-+———– –+
|
As shown above, Mysql scans all 500 rows of data, which makes the query extremely slow.
4. Optimize the database architecture
1) Normalization
First, normalize all database tables, even though there may be some loss. For example, if you need to create two tables to record the Customers and Orders data, you should refer to customers with customer ids on the ORDER table, not vice versa. The following figure shows a database architecture designed without any data redundancy.
Use the best data types
1) MySQL supports various data types, including INTEGER, float, double, date, datetime, vARCHar, text, etc. When designing database tables, use the shortest data type possible to satisfy the characteristics.
For example, if you are designing a table of system users and the number of users does not exceed 100, you should use ‘TINYINT’ for user_ud, which ranges from -128 to 128. If a field needs to store a date value, it is better to use datetime because you do not need to perform complex type conversions at query time.
Integer is used when values are all numeric types. A value of type Integer is faster than a value of type text when performing a calculation.
That’s how you improve your productivity: MySQL optimization tips