In today's data-driven era, database performance optimization has become one of the skills that every developer and database administrator must master. Among the many optimization tools, the use of indexes is undoubtedly the most important and effective.
However, instead of improving performance, index abuse or misuse may bring additional overhead. So, how can indexes be used correctly to really improve database performance?
Why is it that sometimes our carefully created indexes don't deliver the expected performance gains? How do you actually use indexes correctly to ensure that your database is fast and efficient when queried?
Mysql is a relational database management system widely used in applications of various sizes and types. In actual database applications, we often face a variety of performance bottlenecks and problems, according to incomplete statistics.
More than 70% of the performance bottlenecks found during performance testing come from the database; and when the performance of the database encounters a bottleneck, the response time of the application will be lengthened, the TPS will be lowered, and even serious
times can lead to system crashes and downtime.
Therefore, performance tuning of databases is very important and a key part of ensuring that applications run efficiently.
Mysql is a relational database management system widely used in applications of various sizes and types. In actual database applications, we often face a variety of performance bottlenecks and problems, according to incomplete statistics.
More than 70% of the performance bottlenecks found during performance testing come from the database; and when the performance of the database encounters a bottleneck, the response time of the application will be lengthened, the TPS will be lowered, and even severe
of the time can lead to system crashes and downtime.
Therefore, performance tuning of databases is very important and a key part of ensuring that applications run efficiently.
So, what are the possible performance bottlenecks of a database? The main summary is as follows:
1, low database query performance: the slow execution of certain query statements, resulting in slower response time of the application, the user experience degradation;
2, concurrent access problems: when multiple users access the database at the same time, there may be lock competition, deadlock and other problems, which leads to system performance degradation;
3, the database is not properly configured: Mysql's default configuration may not be able to meet the needs of a particular application, the need to release the parameters of the adjustment, such as the maximum number of database connections and other configurations, you can get better performance;
4, database storage engine selection: different engines have different performance characteristics, it is also critical to choose the right engine.
5, the design of the database is unreasonable: for example, the business table data is too large, not divided into tables and libraries, resulting in data query updates are very slow.
In the face of these problems, we need to take a series of database tuning measures to improve the performance of the Mysql database and user experience. We mainly give you today to talk about the usual use of the most index tuning.
Indexing concepts
Index is a data structure used to help us quickly locate the data we want to find in a large amount of data, the main role is to speed up our search for data, similar to the Chinese dictionary and book catalog.
You have the environment can take their own project to try, indexed and not indexed sql statement execution speed.
This statement executes in 0.02s because id is the primary key index;
This SQL executes at 0.118s, the same library and table, an order of magnitude slower because the mobile field is not an index.
SELECT * FROM cb_account where id = 182037; SELECT * FROM cb_account where mobile= '13525329369';`
Add an index to the mobile field of this table and query it:
The same sql statement immediately speeds up to 0.020s
SELECT * FROM cb_account where mobile= '13525329369';
Through the above simple case demonstration, we found that the index can indeed greatly improve the speed of the query, especially when the amount of data is relatively large, the speed of this effect is particularly obvious.
indexing
The main types of indexes are as follows:
A table, there can be no primary key; if there is a primary key, the value of the column of the primary key, must be unique, not repeated, there is no 'empty'
To create a primary key index: create index index name on table name (field).
Syntax for creating a primary key index: create unique index index name on table name (field).
Syntax for creating a primary key index: create index index name on table name(field1,field2,......) ;
Primary key: also called single-value index, if a table is created with a primary key, a primary key index will be generated by default without additional creation.
Unique index: non-repeatable, but can store NULL.
Compound index, also called the combination of indexes: by the table of multiple columns in order to become a combination of indexes; use, in accordance with the combination of the order of the use of indexes, you can also use the combination of indexes in some of the index fields.
Principles of Index Creation
Although the index can greatly improve the performance of the database query, but you can not blindly add the index, because the index has a certain size, will take up disk \ memory space.
Creating indexes, in fact, is essentially using space for time, trading space on disk and in memory for less query time; if indexes are created a lot and are very complex, then they take up a lot of memory space, the
The performance loss will be huge; so we need to index correctly to get the best balance of space and time.
We have to follow the following principles while creating an index:
Columns for which indexes can be created:
1、Primary key columns can be used to create indexes, foreign key columns are used for table associations and linked queries, and can also be used to create indexes.
2, frequent query data columns can be used to create indexes
3, frequently used in the where statement in the columns can create an index
Columns that should not be indexed:
Table modification operations are much more frequent than query operations: Because modifying the table requires modifying the index, the cost of maintaining the index is higher than the cost of using the index;
Columns with very little data, such as the type field with only two values, 0 and 1, do not improve significantly;
Query for rarely used columns or columns that change frequently.
How to use indexes correctly
Leftmost prefix rule for indexes
When the index type for the composite index, we have to follow the combination of indexes, "the leftmost prefix" rule, otherwise the use will not work, can not achieve the purpose of improving query efficiency.
For example, to create a combined index (c1,c2,c3),which actually contains three indexes (c1), (c1,c2), (c1,c2,c3), there must be a leftmost field in the index. The index must be used according to this
Only then will the index be used correctly, otherwise it will not be used.
When we build the index, we use the columns, as long as the name of the column, has nothing to do with the order of the table columns, because the columns in the table are not in order, the
Let's do a little exercise: if we have the following fields in a table, we create a compound index:
Combining indexes as above is equivalent to adding 3 indexes:
mobile
mobile，email
mobile，email，gqid
Our SQL statement will need to follow the three indexes as above to query before we will use the indexes to increase speed, so it is important to use the indexes correctly. Let's do a judgment exercise, which of the following SQL statements will use indexes and which will not?
1 SELECT * FROM `cb_account` where gqid= '4004707'; -- This one won't use an index 2 SELECT * FROM `cb_account` where email= '13537007192@test. com'; -- This one won't use an index 3 SELECT * FROM `cb_account` where email= '13537007192@test. com' and gqid= '4004707';-- This one won't use an index 4 5 SELECT * FROM `cb_account` where mobile= '13525329369'; -- This will use the index 6 SELECT * FROM `cb_account` where mobile= '13525329369' and gqid= '4004707'; -- This will use the index 7 SELECT * FROM `cb_account` where mobile= '13525329369' and gqid= '4004707' and email= '13537007192@test. com' ; -- This will use the index
Other Scenarios for Index Failure
1, sql match to the right to encounter the query range will stop matching, after the index will be invalid: such as between like, etc. ;
2, like %value% This % appears at the beginning, and will not use the index [index failure]:
SELECT * FROM **cb_account where mobile like '%135%' ;
3, the columns do function or expression operations, can also lead to index failure:
select * from user where YEAR(date) < 1990; # Functional use can cause indexing to fail select * from student where id -1 =1; # Operations can cause indexes to fail SELECT * FROM **cb_account where id = 182037; # It is valid to write the index directly like this
4. When there is an or in the query condition, the index fails unless all the query conditions are indexed:
SELECT * FROM student where id =1 or birthday = "2021-12-23' # Unless id and birthday are both indexed, indexing fails
5, if the column type is a string, then the query conditions need to quote the data in quotes, otherwise do not go to the index:
select * from strudent where name = 222；#nameis the index of the string type，222The value is not enclosed in quotation marks，Indexing Failure
Summary of Pros and Cons of Indexing
Disadvantages of Indexing
a, the index itself is very large, usually exists in the disk (can also exist in memory), so do not just see the index, taking up space;
b, not all cases can use the index: data volume is very small, as well as frequent changes in the value of columns, as well as columns are rarely used in the case are not recommended to use the index;
c, the index will enhance the efficiency of the check but will reduce the efficiency of additions and deletions, because additions and deletions need to be modified to update the index itself, so add the index will reduce the speed of additions and deletions.
Advantages of Indexing
a, reduce IO, CPU utilization: query time, line by line than the need for a large number of CPU operations, each line is read IO will also be high; indexing will reduce the consumption of these small
b, indexed columns, you can guarantee the uniqueness of the line: you want to make a field unique You can set this field as a unique index, then it is functionally guaranteed that it is unique.
c. Data retrieval time can be effectively reduced
d, accelerate the connection between the table: multi-table correlation query, generally associated fields [foreign key] to create an index, greatly improving the efficiency of the query.
Therefore, the index should be set up, but can not be abused, the reasonable setting of the index is very important. In general, the database table data volume level within the 100,000 level, with or without indexes, the speed of finding data
The difference is not significant enough to warrant an index.
In the era of big data, the explosive growth of data volume has brought great challenges to database performance. With the increasing demand for real-time data analysis and fast response from enterprises, database performance optimization of the
The importance of indexing is becoming more and more prominent. The correct use of indexes is one of the keys to solving this problem, but it is also necessary to combine the actual business scenarios and data characteristics to avoid blindly creating indexes.
The index is like a sharp sword, if it is used properly, it will be invincible; if it is used improperly, it will be exhausted.
Through detailed case studies and analysis of social phenomena, this article demonstrates the importance of the proper use of indexes in database performance optimization, and wraps up with an engaging beginning and a powerful golden...
Gives the reader a strong interest and practical motivation for indexing optimization.
