MySQL is the most popular relational database management system. The database is a warehouse that organizes, stores and manages data according to the data structure. Such warehouses also need to be managed, otherwise they will be messy. When you use it, it will be more complicated. Therefore, effective care can help you to operate and use it better and more effectively. Today, I will briefly introduce eight ways to optimize MySQL database.
1. Create Index
Indexes are a very important presence in applications where queries dominate. Many performance problems are very simple. Often there is no added retrieval or no more effective index, which makes these problems particularly complicated and difficult. If there is no index, and you want to know data, you have to perform a full table scan. When your data volume is relatively small, maybe you don't need indexes. However, when your data volume reaches a large level, without indexes, your workload will increase indefinitely. In serious cases, the computer crashed.
2. Composite Index
The composite index is an upgraded version of the index. When we work with two keywords, we build an index for each one. MySQL queries can only use one index at a time. But it has improved a lot of efficiency compared with full table scanning. However, if a composite index is created on two keyword columns, it will bring higher efficiency. If there are more keywords, we can create three composite indexes. It is arranged according to the urgency of your needs, decreasing from left to right. This composite index has greatly improved our work efficiency.
3. The index will not contain columns with NULL values.
Any NULL value contained in the column will not be included in the index. As long as a column in a composite index contains NULL values, this column is invalid. Therefore, we should not let the default value of the field be NULL when designing the database.
4. Use a short index
Indexing the tandem. If possible, you should specify a prefix length. Short indexes can not only improve query speed, but also save disk space and enhance I/O operability.
5. Index Problem of Sorting
The MySQL query uses only one index. Therefore, if the index is already used in the where clause, the columns in order by will not use the index. So do not use sort operations when the default sort of the database meets the requirements. Try not to contain sorting of multiple columns, and it is best to create a composite index if necessary.
6. Like statement operation
In general, like operations are not encouraged.
7. Do not operate on columns
Select * from users where YEAR (add date) < 2007;
This statement will operate on each row, which will cause the index to fail and scan the whole table. So we can change it to
Select * from users where add date < '2007-01-01';
8. Do not use NOT IN and <> operation
Neither NOT IN nor <> operations will use indexes to scan the entire table. NOT IN can be replaced by NOT EXISTS. If id <> 3, id > 3 or id < 3 can be used instead.
The above is a brief introduction to eight ways to optimize MySQL database. Although it is not very detailed, it is basically acceptable to beginners. Through the above methods, you can greatly improve your work efficiency and ability to process data.
