Optimization ideas
The detailed MySQL optimization steps are as follows:
- Check the data table structure and improve the imperfect design
- Run the main business and collect commonly used database query SQL
- Analyze the query SQL, split it appropriately, add indexes, etc. to optimize the query
- While optimizing SQL, optimize the code logic
- Add local cache and redis cache
Try not to use NULL values
Because when creating a table, if you do not set a default value for the created value, MySQL Will be set to NULL
by default. So why is it not good to use NULL
?
-
NULL
makes index maintenance more complicated. It is strongly recommended to setNOT NULL
-
NOT IN
on the index column. ,!=
and other negative conditional queries will always return empty results when there is aNULL
value, and the query is prone to errors in the -
NULL
column An extra byte is needed as a flag to determine whether it isNULL
- When using
NULL
, it may not be of the same type as other values in the column, causing problems. (It behaves differently in different languages) - MySQL has difficulty optimizing queries for columns that can be
NULL
So for those fields that were lazy before, Manually set a default value, empty string, and add 0.
Although this method does not improve the performance of MySQL much, it is a good habit, and seeing the big from the small, don't ignore these details.
Add index
For frequently queried fields, please add indexes. The query speed with and without indexes differs by ten times or more.
- Generally speaking, each table needs to have a primary key
id
Field - Fields commonly used for queries should be indexed
- # For fields of type ##varchar
, when creating an index, it is best to specify the length
When the query has multiple conditions, the conditions with indexes are preferred - Like
- LIKE
Conditional fuzzy search is invalid for field indexes, and additional keyword indexes need to be established to solve the problem
- Please try not to constrain the relationship between tables at the database level, The dependencies between these tables should be resolved at the code level
Optimize the table field structure
Do not use the string type for data that can be represented by integers in the database. Should you usevarchar or
charDepends on the possible values of the field.
- For those columns with very limited possible values, use
- tinyint
instead of
VARCHAR,
- For example, to record the mobile device platform, there are only two Value: android, ios, then you can use 0 to represent android and 1 to represent ios. This column must be commented
- Why not use
- ENUM
?
ENUMDifficulty in expansion. For example, the mobile platform later added an
ipad, which would be confusing, but
tinyintjust adds a 2, and
ENUMis very strange to handle in the code. Whether it is treated as an integer or a string varies from language to language.
In this method, the meaning of each value must be stated in the database comment or code
For those fixed-length strings, you can use - char
, such as zip code, is always 5 digits
For those strings with unknown length, use - varchar
- bigint
, such as records For the
idfield of the table with the number of articles, just use
int. The upper limit of 2.1 billion articles is enough.
Properly break the database paradigm and add redundant fields to avoid queries. When querying table connection
int type must be faster than
varchar, because the comparison of integers can be achieved by directly calling the underlying operator, while the string type The comparison is done character by character.
Large table splitting
For those tables whose data volume may exceed 5 million in the near future or grow rapidly, be sure to split tables vertically or horizontally in advance. When the data After the volume exceeds one million, the query speed will drop significantly.
Try to finalize the plan for sub-database and sub-table in the early stage of database design, otherwise it will greatly increase the code complexity and be difficult to change later.
Vertical table partitioning is based on external variables such as dates, and horizontal table partitioning is based on certain field relationships in the table, using hash mapping to divide the table into equal parts.
The prerequisite for sub-database and table sub-database is that before executing the query statement, you already know which sub-database and which sub-table the data to be queried may fall into.
Optimizing query statements
This is the initiator of many system database bottlenecks.
- Please try to use simple queries and avoid using table links
- Please try to avoid full table scans. Statements that will cause full table scans include but are not limited to:
- where clause condition is always true or empty
- Use
LIKE
- Use inequality operator (, !=)
- Query the column containing
is null
- When using
or
- on non-index columns to query with multiple conditions, please put Put simple query conditions or index column queries in front
- Please try to specify the columns you need to query, don't be lazy to use select *
- If you don't specify, on the one hand, redundant data will be returned, occupying bandwidth, etc.
- On the other hand, when MySQL executes a query, if there are no fields, it will first query the fields in the table structure.
- Uppercase query keywords are a little faster than lowercase ones
- Using subqueries will create temporary tables, which will be slightly slower than links (JOIN) and unions (UNION)
- Try not to use database functions when querying on index fields, which is not convenient for caching query results
- When there is only one row of data, please use LIMIT 1. If there is too much data, please set LIMIT appropriately and perform paging query
- Never use ORDER BY RAND(), the performance is extremely low
Add cache
Using caches such as redis and local file cache can greatly reduce the number of database queries. When it comes to caching, you must analyze the data characteristics of your own system and make appropriate choices.
- For some commonly used data, such as configuration information, etc., it can be placed in the cache.
- The table structure of the database can be cached locally.
- The cached data must be Pay attention to timely updates, and set the validity period
- Increasing cache will definitely increase system complexity, so be sure to pay attention to the trade-off
Check the data table structure
Recommended learning: "mysql video tutorial"
The above is the detailed content of Summarize the most basic operations of MySQL optimization. For more information, please follow other related articles on the PHP Chinese website!

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.


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

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SublimeText3 English version
Recommended: Win version, supports code prompts!

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

Atom editor mac version download
The most popular open source editor
