Use of database indexes and database Indexes

Use of database indexes and database Indexes

I found a problem today. The problem is probably like this. I can query the interface information and use the local database locally for access. However, the access speed will be very busy after being published to the server, it may take about five minutes to return data. This is definitely unacceptable. At first, I thought it was a problem with server performance. To verify this, I backed up the database on the server to a local machine, the local speed is also slowed down immediately. What is the problem. After reading the SQL statement of the interface query, I was shocked:

               select            distinct              a.id            ,a.name            ,a.interfacecode            ,a.version            ,a.synasyn            ,a.frequence            ,a.solutionmodelid            ,a.owner            ,a.createtime            ,a.status            ,a.description            ,p.id  as "project.id"            ,p.name               as "project.name"            ,p.pcategory          as "project.pcategory"            ,r.name               as "release.name"            , r.id                as "release.id"            ,b.name               as "middlewarename"            ,l1.name              as "sourcesystem"            ,l2.name              as "targetsystem"            , f.name              as "messageformat1"            ,k.name               as "messageformat2"            ,g.name               as "messagename1"            ,l.name               as "messagename2"            ,m.id                 as "interfacemapid"            ,m.category           as category            , c.bos               as "bos1"            ,h.bos                as "bos2"            ,sm.name              as "solutionModelName"            ,a.lastmodifytime     as "lastModifiedAt"            ,df.name              as "scenario"            ,a.iscurrent          as "iscurrent"            ,m.reviewstatus       as "reviewStatus"            ,m.reviewedby         as "reviewedBy"            ,m.reviewedat         as "reviewedAt"            ,m.trackleader        FROM interfacemapping           m         LEFT JOIN project               p   ON m.projectid   =   p.id        LEFT JOIN realse                r   ON p.realseid    =   r.id        LEFT JOIN integrationinterface  a   ON m.interfaceid =   a.id        LEFT JOIN logicsystem           b   ON a.middleware  =   b.id        LEFT JOIN interfacedetail       c   ON c.interfaceid =   a.id   AND UCASE(c.flowflag) = 'START'        LEFT JOIN logicsystem           d   ON d.id          =   c.logicsystemid        LEFT JOIN messageformat         f   ON f.id          =   c.messageformatid        LEFT JOIN messagedic            g   ON g.id          =   c.messagename        LEFT JOIN interfacedetail       h   ON h.interfaceid =   a.id   AND UCASE(h.flowflag) = 'END'        LEFT JOIN logicsystem           j   ON j.id          =   h.middleware        LEFT JOIN messageformat         k   ON k.id          =   h.messageformatid        LEFT JOIN messagedic            l   ON l.id          =   h.messagename        LEFT JOIN interfacedetail       u   ON u.interfaceid =   a.id AND UCASE(u.flowflag) = 'MID'        LEFT JOIN messageformat         n   ON n.id          =   u.messageformatid        LEFT JOIN messagedic            z   on z.id          =   u.messagename        LEFT JOIN logicsystem           l1  ON l1.id         =   a.sourcesystemid        LEFT JOIN logicsystem           l2  ON l2.id         =   a.targetsystemid        lEFT JOIN solutionmodel         sm  ON sm.id         =   a.solutionmodelid        LEFT JOIN DATAFLOWINFO          df  ON df.ID         =   a.SCENARIOID 

I think you must be scared. But this is only part of it, and I have not posted dynamic SQL statements. Is the speed affected because of a bunch of table connections? I decided to start calling this SQL statement by connecting tables one by one. When I connect to LEFT JOINinterfacedetail c, the query speed is 20 seconds. Is the connection speed between tables caused by this table slow?

Then I began to analyze why data in the original local database was fast. I found that there was no data in interfacedetail in the original local database. In the current database, there are 8000 data records in the Table. Is this the cause of slow query? Is connection query not suitable for more than 8000 pieces of data?

But I suddenly thought of the same data in db2, why is the query quite fast. First, I verified it. The data in each table in mysql is exactly the same as that in db2. The results are exactly the same, but the execution speed in db2 is 0.4 seconds, which is completely acceptable. The problem arises again. What is the difference between enterprise-level databases and general databases?

However, this result still cannot be trusted. I checked other materials and found that some people said that indexes can speed up the connection between tables. As a result, I still found that db2 has an index of interfacedetail. interfaceid. I add the same index to mysql:

Create index T_PI_VLO_NAAE_IDX1 ONinterfacedetail (interfaceid );

Then, directly run the preceding statement, and the direct speed is less than 0.5 seconds .. The problem is solved. But why is the index so powerful? What is the index?

Why create an index? This is because creating an index can greatly improve the system performance.

First, you can create a unique index to ensure the uniqueness of each row of data in the database table.

Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.

Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.

Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.

 

Some may ask: why not create an index for each column in the table because increasing Indexes has so many advantages? Although such an idea has its own rationality, it also has its own one-sidedness. Although indexes have many advantages, it is unwise to add indexes to every column in the table. This is because adding indexes also has many disadvantages.

 

First, it takes time to create and maintain indexes. This time increases with the increase of data volume.

Second, in addition to the data space occupied by data tables, each index also occupies a certain amount of physical space. To create a clustered index, the required space will be larger.

Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.

 

Indexes are created on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index. In general, you should create an index on these columns. For example, you can speed up the search for columns that frequently need to be searched, force the uniqueness of the column and the data arrangement structure in the organization table. These columns are usually used in connected columns and are mainly foreign keys, which can speed up the connection; create an index on a column that often needs to be searched by range. Because the index has been sorted, the specified range is continuous. Create an index on a column that frequently needs to be sorted because the index has been sorted, in this way, the sorting of indexes can be used to speed up the sorting query time. indexes are often created on the columns in the WHERE clause to accelerate the condition judgment speed.

 

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics: first, indexes should not be created for those columns that are rarely used in queries or referenced. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements. Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed. Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small. Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index.

 

Okay. The problem has been solved and many things have been learned. This indexing course has a profound understanding.