Preface
As a programmer who writes SQL, it is one thing to write good code, but SQL is bad and has poor performance. The whole company has to pay for your slow query, especially on big data scales, whether SQL can write it or not. Good thing is a "insider" level error.
Today, those who are not silly, just come with some hard products: 20 SQL optimization tips, each one can save your life. The key is, I will also provide you with code examples and use them directly. I hope you will have them. Helped.
I recently opened the source of a mall project based on SpringBoot+Vue+uniapp. There are many technical highlights in it. Welcome to visit and star. [/dvsusan/susan_mall]
1. Avoid SELECT *
Don't just come upSELECT *, you think writing this way is easy, but the database has to move every column of the entire table to you, which is extremely expensive. Write down what you need clearly, don't be lazy.
-- Error writing
SELECT * FROM users;
-- Correct writing
SELECT id, name, age FROM users;Optimization reasons: Reduce the amount of data transmitted on the network and avoid useless fields occupying memory.
Finally, I would like to say (please pay attention, don't mess with me for free)
If this article is helpful or inspiring to you, please help me follow my official account of the same name: Su San Talks about Technology. Your support is my greatest motivation for persisting in writing.
Please ask for three consecutive one click: like, forward, and watch.
Follow the official account: [Su San Talks about Technology], reply in the official account: When you enter a large factory, you can get the 100,000-word interview book I have compiled for free. Many friends have obtained offers from many large factories through this book.
2. The WHERE field must have an index
When you write a WHERE condition, the fields involved must have an index. Without index, MySQL has to scan the table row by row. The scan of the entire table is so slow that you doubt your life.
-- Create an index
CREATE INDEX idx_users_age ON users(age);
-- Query with index
SELECT * FROM users WHERE age > 25;Optimization reasons: Indexes are like directories, which can jump directly to the page you want, rather than turning a book from beginning to end.
3. Use EXPLAIN to analyze SQL performance
Before writing SQL, don't run directly, add it firstEXPLAINCheck out the execution plan. What to see?typeYes or noALL, is it gone to the full table scan? If so, change it quickly.
EXPLAIN SELECT * FROM users WHERE age > 25;
Optimization reasons: Be clear about the execution plan and know whether SQL runs efficiently.
4. Avoid function operations in WHERE
Do not use functions for fields in WHERE conditions, the index will be invalid directly.
-- Error writing
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- Correct writing
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';Optimization reasons: Function operations invalidate the index, and return to the table scan is wasted.
5. Avoid OR conditions and use UNION instead
WHERE wrote an OR, which may cause the indexes of both fields to be invalidated. Don't use OR, use UNION instead.
-- Error writing
SELECT * FROM users WHERE age = 25 OR city = 'shenzhen';
-- Correct writing
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE city = 'shenzhen';Optimization reasons:OR will invalidate the index, while UNION can utilize indexes separately.
6. Optimize LIMIT paging
Whether LIMIT is used well directly affects performance, especially when the page reaches 10,000 pages, it will directly jam you. An improvement is to filter with ID or time range.
-- Error writing
SELECT * FROM users ORDER BY create_time LIMIT 10000, 10;
-- Correct writing
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;Optimization reasons: Use range filtering to reduce unnecessary scans.
7. Override index
Overriding the index means that all the fields in the query are in the index, and MySQL does not need to return to the table.
-- Create an overlay index
CREATE INDEX idx_users_age_name ON users(age, name);
-- Query
SELECT name FROM users WHERE age > 25;Optimization reasons: Only obtain data from the index, no need to return to the table, and improve query speed.
8. Reduce the number of JOIN tables
JOINToo many tables will complicate the execution plan. If tables with large data volumes use JOIN, the performance will be reduced.
-- Complex multi-table JOIN
SELECT * FROM orders
JOIN users ON orders.user_id =
JOIN products ON orders.product_id = ;
-- Optimization: Split Query
SELECT * FROM orders WHERE user_id IN (
SELECT id FROM users WHERE age > 25
);Optimization reasons: Reduce the data processing volume of intermediate tables and reduce the complexity of JOIN.
9. Using batch insertion
If there are too many writes for a single insert, the performance will be dragged down by the write lock and replaced with batch insertion.
-- Error writing
INSERT INTO users (id, name) VALUES (1, 'zhangsan');
INSERT INTO users (id, name) VALUES (2, 'lisi');
-- Correct writing
INSERT INTO users (id, name) VALUES (1, 'zhangsan'), (2, 'lisi');Optimization reasons: Reduce the number of database connections and commits.
10. Filter data before GROUP BY
GROUP BYIn essence, it is to sort and group the results, and the efficiency is touching when the data is large. Use firstWHEREReduce the amount of data.
-- Error writing
SELECT age, COUNT(*) FROM users GROUP BY age;
-- Correct writing
SELECT age, COUNT(*) FROM users WHERE age > 25 GROUP BY age;Optimization reasons: Reduce the amount of data processed by GROUP BY.
11. LIKE query optimization
For fuzzy query%xxx%When the index is gone, the entire table is scanned directly. Change to prefix matching, or use full text index.
-- Prefix matching
SELECT * FROM users WHERE name LIKE 'zhang%';
-- Full text index
CREATE FULLTEXT INDEX idx_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('zhang');Optimization reasons: Use indexes to improve query efficiency.
12. Avoid frequent query of large fields
Large fields such as TEXT and BLOB will drag down query performance and split them into a table separately.
-- Original table
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
profile TEXT
);
-- Disassemble table
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY,
profile TEXT
);Optimization reasons: Reduce the burden of large fields during primary table query.
13. Clean useless data regularly
There is too much garbage data in the table, and the index also expands, and the query performance plummeted.
-- Clean up historical data regularly
DELETE FROM logs WHERE create_time < '2022-01-01';Optimization reasons: Keep the table lightweight and avoid data bloating.
14. Optimize big data tables with partition tables
Partition tables are a powerful tool for optimizing large data volumes, dividing data into multiple partitions by scope.
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE NOT NULL
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024)
);
Optimization reasons: Query scans only one partition, not the entire table.
15. Use low cardinality fields for index fields
It doesn't make sense to use indexes for fields with low cardinality (such as gender). If you can use combination indexes, use them.
-- Error: Gender field plus index
CREATE INDEX idx_gender ON users(gender);
-- Correct: Gender + Age combination index
CREATE INDEX idx_gender_age ON users(gender, age);Optimization reasons: Avoid wasting storage in low cardinality indexes.
16. Control the number of fields in the table
Too many table fields will make the data table bloated and query performance will also become worse. Split the table reasonably.
17. Optimize batch updates with transactions
Large batch updates can reduce lock competition.
START TRANSACTION;
UPDATE users SET age = age + 1 WHERE age > 25;
COMMIT;
18. Use query cache
MySQL has a query cache mechanism, which is suitable for high-frequency queries of static data.
-- Turn on query cache
SET GLOBAL query_cache_size = 1048576;
SELECT SQL_CACHE * FROM users WHERE age > 25;19. Use PreparedStatement
Precompilation can improve performance and prevent SQL injection.
20. Regularly optimize tables
The table will be fragmented after using it for a long time, and regular optimization of the table can improve performance.
OPTIMIZE TABLE users;
Summarize
SQL optimization is a craft. If you write SQL well, you can lose countless pots, especially when team development, a bad SQL can drag the entire project to death.
Don’t just read these 20 tips once, practice them more with your own inquiries, and don’t wait until the links are lost online before regretting it!