20 optimization summary of mysql
The content of this article is about the optimization summary of mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
Preface
Nowadays, database operations have increasingly become the performance bottleneck of the entire application, especially for Web applications. Therefore, I have compiled some suggestions for MySQL optimization. I hope these optimization techniques will be useful to you. If you can’t summarize them, you are welcome to add them.
Reasons for slow SQL execution
Slow network speed, insufficient memory, low I/O throughput, full disk space and other hardware problems
No index or index failure
There are too many data records in the data table
Server tuning and various parameter settings It may also affect the efficiency of SQL written by
Others
1. EXPLAIN Analyze your SELECT query
In many cases, using the EXPLAIN keyword can let you know how MySQL processes your SQL statement. This can help you analyze your query statement, so that you may be able to find optimizations as soon as possible. methods and potential performance issues. For the specific use of EXPLAIN and the meaning of each parameter, please refer to the relevant documents.
2. The SELECT query must specify the field name.
SELECT * query will add a lot of unnecessary consumption (such as CPU, I/O, etc.). At the same time, it may also increase the use of covering indexes. . Therefore, when performing a SELECT query, it is required to directly specify the corresponding field name to be queried at the end.
3. When querying a piece of data, use LIMIT 1
to reduce redundant queries, because after specifying limit 1, the query will no longer continue after querying a piece of data, making the type column in EXPLAIN To reach the const type, the query statement is better.
4. Create an index for the searched WHERE field
Generally, we will set a primary key for each table, and the index is not necessarily the primary key. If there is a field in your table that you always use for WHERE queries and searches, and it is more read than written, then please create an index for it. If you are interested in learning more about the principles of indexing, Relevant information can be found.
5. Never use ORDER BY RAND()
If you want to get data randomly, maybe the first one will tell you directly to use random numbers. Remember, at this time you must Control your brain to continue thinking in this direction and stop this terrible thought. Because this kind of query has no benefit to the performance of the database (consuming CPU). One of the better solutions is to first find the number N of data, and then use LIMIT N, 1 to query like this.
6. Ensure that each table has a primary key ID
We should develop a habit. Every time we create a new table, we should design an ID field for it, and Make it the primary key, preferably of type INT (some also use UUID), and set the ID field to the AUTO_INCREMENT flag.
8. Use NOT NULL as much as possible
Don’t think that NULL does not need space. The fact is that NULL also needs extra space. Maybe many people have not noticed but have encountered it. NULL fields are in When querying and comparing, it is more troublesome. Of course, if you really need NULL, then no problem, just use it. Otherwise, it is recommended to use NOT NULL.
8. Choose the appropriate storage engine
There are two storage engines, MyISAM and InnoDB, in MySQL. Both have their own pros and cons, so we need to understand the differences between the two and then make the best decision. Suitable choices, for example, InnoDB supports transactions but MyISAM does not, MyISAM queries are faster than InnoDB, etc.; in short, if you don't know what to choose, then use InnoDB.
9. Save the IP address as UNSIGNED INT
When encountering the need to store the IP address, many people’s first thought will be to store the VARCHAR (15) string type, and You wouldn't think of using INT integer type to store it; if you use integer type to store it, it only takes 4 bytes, and you can have fixed-length fields, and this will bring you advantages in querying.
10. Try not to judge the null value of a field during WHERE query
We all know that when we judge a field as null, it will be slower. This is because of this The judgment will cause the engine to abandon the use of all existing indexes and perform a full table scan search.
11. Try not to use LIKE fuzzy query with % prefix
Fuzzy query, we often encounter it in daily development, but I believe many people directly LIKE ' %key_word%' or LIKE '%key_word' are searched like this. These two search methods will cause the index to fail and require a full table scan search. If you want to solve the above fuzzy query, the answer is to use "use full-text index". If you are interested in the specific usage, you can check the information yourself.
12. Avoid performing expression operations on fields during WHERE queries.
For example, the query statement SELECT id FROM table WHERE num * 2 = 50;, such a query, Doing an arithmetic operation of multiplying by 2 on the field num will cause the index to fail.
14. Reduce unnecessary sorting
Sorting operations will consume more CPU resources, so reducing unnecessary sorting can reduce SQL performance when the cache hit rate is high and I/O is sufficient. Response time.
14. It is recommended to use JOIN instead of subquery
Some people will say that the performance of JOIN is not very good, but it still has great performance advantages compared with subquery. . Specifically, you can learn about issues related to the execution plan of a subquery.
15. Avoid implicit type conversion
Type conversion mainly refers to the type conversion that occurs when the type of the field in the WHERE clause is inconsistent with the type of the parameter passed in; this is Because if the data type we pass in is inconsistent with the field type, MySQL may perform a type conversion operation on the data we pass in, or it may not process it and directly hand it over to the storage engine for processing. In this way, the index may not be able to be processed. Execution plan problems caused by usage conditions.
16. Avoid inconsistent field types in multi-table queries
When we encounter the need for multi-table joint queries, when we design the table structure, try to keep the associated fields of the table consistent with the table, and Indexes must be set. At the same time, when performing multi-table connection queries, try to use the table with a small result set as the driving table.
17. It is recommended to turn on the query cache
Most MySQL servers have the query cache turned on. This is one of the most effective ways to improve performance, because the query cache is automatically processed by the MySQL database engine. When many of the same queries are executed multiple times, these query results will be placed in a cache, so that subsequent identical queries do not need to operate the table but directly access the cached results.
18. Use UNION instead of temporary table
UNION query can merge two or more SELECT query results into one query, so there is no need to create a temporary table to complete. It should be noted that the number of fields in all SELECT statements using UNION must be the same.
19. Use IN queries with caution
Be careful with IN and NOT IN queries, because they may lead to a full table scan. For continuous values, do not use IN if you can use BETWEEN.
20. Welcome to add
Conclusion
This is mainly to consider optimization from the perspective of query, as well as some sub-table, partition technology and read-write separation; the above optimization If the above mentioned points are not in place, please understand. There are many places where MySQL can be optimized. Other optimization suggestions are welcome, thank you.
The above is the detailed content of 20 optimization summary of mysql. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1376
52
The principles and usage scenarios of Synchronized in Java and the usage and difference analysis of the Callable interface
Apr 21, 2023 am 08:04 AM
1. Basic features 1. It starts with an optimistic lock, and if lock conflicts are frequent, it is converted to a pessimistic lock. 2. It starts with a lightweight lock implementation, and if the lock is held for a long time, it is converted to a heavyweight lock. 3. The spin lock strategy that is most likely used when implementing lightweight locks 4. It is an unfair lock 5. It is a reentrant lock 6. It is not a read-write lock 2. The JVM will synchronize the locking process Locks are divided into no lock, biased lock, lightweight lock, and heavyweight lock states. It will be upgraded sequentially according to the situation. Biased lock assumes that the male protagonist is a lock and the female protagonist is a thread. If only this thread uses this lock, then the male protagonist and the female protagonist can live happily forever even if they do not get a marriage certificate (avoiding high-cost operations). But the female supporting role appears
Java keyword synchronized principle and lock status example analysis
May 11, 2023 pm 03:25 PM
1. The concept of lock in Java Spin lock: When a thread acquires a lock, if the lock has been acquired by another thread, then the thread will wait in a loop, and then continue to judge whether the lock can be successfully acquired until it is acquired. The lock will exit the loop. Optimistic locking: Assuming there is no conflict, if the data is found to be inconsistent with the previously acquired data when modifying the data, the latest data will be read and the modification will be retried. Pessimistic lock: Assume that concurrency conflicts will occur, synchronize all data-related operations, and start locking from the time the data is read. Exclusive lock (write): Add a write lock to the resource. The thread can modify the resource, but other threads cannot lock it again (single write). Shared lock (read): After adding a read lock to a resource, it can only be read but not modified. Other threads can only add read locks and cannot add write locks (multiple). See as S
How to use synchronized to implement synchronization mechanism in Java?
Apr 22, 2023 pm 02:46 PM
Summary of how to use synchronized in Java 1. When synchronized is used as a function modifier, the sample code is as follows: Publicsynchronizedvoidmethod(){//….} This is the synchronization method. So which object is synchronized locked at this time? What he locks is calling this synchronized method object. In other words, when an object P1 executes this synchronization method in different threads, they will form mutual exclusion to achieve synchronization effect. However, another object P2 generated by the Class to which this object belongs can arbitrarily call this method with the synchronized keyword added. The sample code above, etc.
What are the three synchronization methods in Java and how to use them?
Apr 27, 2023 am 09:34 AM
1. Explain that synchronized is our most commonly used synchronization method, and there are three main ways to use it. 2. Example//General class method synchronization synchronizedpublidvoidinvoke(){}//Class static method synchronization synchronizedpublicstaticvoidinvoke(){}//Code block synchronization synchronized(object){}The difference between these three methods is that the synchronized objects are different. Ordinary classes synchronize the object itself, static methods synchronize the Class itself, and code blocks synchronize the objects we fill in the brackets. What collections are there in Java?
What is the principle and process of Java Synchronized lock upgrade?
Apr 19, 2023 pm 10:22 PM
Tool preparation Before we formally talk about the principle of synchronized, let's talk about spin locks first, because spin locks play a big role in the optimization of synchronized. To understand spin locks, we first need to understand what atomicity is. The so-called atomicity simply means that each operation is either not done or done. Doing all means that it cannot be interrupted during the operation. For example, to add one to the variable data, there are three steps: Load from memory into register. Add one to the value of data. Write the result back to memory. Atomicity means that when a thread is performing an increment operation, it cannot be interrupted by other threads. Only when this thread completes these three processes
Why does Java need to provide Lock instead of just using the synchronized keyword?
Apr 20, 2023 pm 05:01 PM
Summary: The synchronized keyword is provided in Java to ensure that only one thread can access the synchronized code block. Since the synchronized keyword has been provided, why is the Lock interface also provided in the Java SDK package? Is this unnecessary reinvention of the wheel? Today, we will discuss this issue together. The synchronized keyword is provided in Java to ensure that only one thread can access the synchronized code block. Since the synchronized keyword has been provided, why is the Lock interface also provided in the Java SDK package? Is this unnecessary reinvention of the wheel? Today, let’s discuss it together
What is Java Synchronized
May 14, 2023 am 08:28 AM
What is Synchronized? Java readers are no strangers to the synchronized keyword. It can be seen in various middleware source codes or JDK source codes. For readers who are not familiar with synchronized, they only know that the synchronized keyword needs to be used in multi-threading. synchronized can ensure thread safety. It is called: mutex lock (only one thread can execute at the same time, other threads will wait), also called: pessimistic lock (only one thread can execute at the same time, other threads will wait). The JVM virtual machine will help you implement it. , developers only need to use the synchronized keyword. When using it, you need to use an object as a mutex for the lock
How many non-access modifiers are there in Java?
Aug 30, 2023 pm 06:01 PM
Java provides some other modifiers to provide functionality beyond visibility. These modifiers are called non-access modifiers. Static Members declared as static are common to all instances of the class. Static members are class-level members that are stored in class memory. Final This modifier is used to restrict further modifications to a variable, method, or class. The value of a variable declared final cannot be modified once it obtains its value. The Final method cannot be overridden in a subclass, nor can a subclass of the Final class be created. Abstract This modifier can be used with a class or method. You cannot apply this modifier to variables and constructors. Methods declared abstract must be modified in subclasses. You cannot instantiate a class declared abstract. Synchronous This modifier is used to control multiple threads
