Use of database indexes

Source: Internet
Author: User
Tags db2

Find a problem today, the problem is probably this, query interface information, local use of local database access is not a problem, but published to the server later access speed is particularly busy, need 5 minutes or so to return data, which is certainly unacceptable, just started to think that the server performance problem, in order to verify the database on the server back to the local, found that the local speed is immediately slow down, what is the problem. Look at it. Query interface 's SQL statement can not help but startled:

               Select distinct a.ID, A.name, A.interfacecode, A.V Ersion, A.synasyn, A.frequence, A.solutionmodelid, A.owner, a.creat  ETime, 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 "me            Ssageformat1 ", 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 Interfacemap                Ping M left join project p on M.projectid = P.id LEFT Join Realse R on P.realseid = R.id left Join Integrationinterface A in M.interfaceid = a.ID left join Lo Gicsystem b on a.middleware = b.ID left joins Interfacedetail c on c.interfaceid = a.ID A  ND UCASE (c.flowflag) = ' START ' left JOIN logicsystem d in d.id = C.logicsystemid Left     JOIN Messageformat    F on f.id = C.messageformatid left JOIN messagedic g on g.id = C.messagena Me left Join Interfacedetail h in H.interfaceid = a.ID and UCASE (h.flowflag) = ' END ' LEFT join L   Ogicsystem J on j.id = H.middleware left joins Messageformat k on k.id = H.messageformatid left join messagedic l in l.id = H.messagename LEFT Join INTERFAC Edetail u on u.interfaceid = a.ID and UCASE (u.flowflag) = ' MID ' left joins Messageformat N on N. id = U.messageformatid Left JOIN messagedic z on z.id = U.messagename LEF          T join Logicsystem l1 on l1.id = A.sourcesystemid left joins Logicsystem L2 on L2.id = A.targetsystemid left JOIN solutionmodel sm on sm.id = A.solutionmodelid LEF     T JOIN Dataflowinfo     DF on df.id = a.scenarioid  

I think you must be scared, too. But that's just a part of it, and there's a dynamic SQL I didn't post. Is it because a bunch of tables are connected so speed has an impact? Decided to start by adjusting the SQL statement, the method used is to join the table on a per-connection basis. When I connect to the left joininterfacedetail C , the query speed is a few seconds. Is this table causing a slow connection between tables?

Then I started to analyze why the original local database was fast, I found that there is no data in the Interfacedetail in the original local library. Now the table in the library has 8000 8000 The data above is not suitable for connection query?

But it occurred to me that the original was in db2 mysql Data in each table in and DB2 The data in DB2 0.4 seconds, is entirely acceptable. Here comes the question again. Is this the difference between an enterprise-level database and a normal database?

But the result is still not going to make me believe it. I checked the other information, found that the index can improve the speed of connection between tables, and I still really found that there are interfacedetail in DB2 . the index of the InterfaceID. I'm Adding the same index to MySQL:

CREATE INDEX t_pi_vlo_naae_idx1 oninterfacedetail (InterfaceID);

and then directly above the statement, the direct speed immediately to 0.5 seconds or less. The problem is solved. But why is the index such a big role, what is the index?

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

First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.

Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.

Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

by using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Perhaps someone will ask: there are so many advantages to adding indexes, why not create an index for each column in the table? Although this kind of thought has its rationality, but also has its one-sidedness. Although indexes have many advantages, it is very unwise to add indexes to each column in a table. This is because there is a lot of downside to increasing the index.

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.

Second, in addition to the data table for the data space, each index will also occupy a certain amount of physical space, if you want to establish a clustered index, then the space will be larger .

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

indexes are built on top of some columns in a database table. Therefore, when you create an index, you should carefully consider which columns you can create an index on, and on which columns you cannot create an index. In general, you should create indexes on these columns, such as: on columns that are often searched, you can speed up the search, enforce the uniqueness of the column on the column that is the primary key, and arrange the structure of the data in the organization table; These columns are mostly foreign keys, which can speed up the connection. Create an index on a column that often needs to be searched by scope, because the index is sorted, its specified range is contiguous, and the index is created on columns that are often ordered, because the index is sorted so that the query can take advantage of the sorting of the index to speed up the sort query time To speed up the judgment of a condition by creating an index on a column that is often used in the WHERE clause.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics: first, you should not create an index for columns that are seldom used or referenced in a query. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement. Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval. Third, for those columns that are defined as text,image and bit data types, the index should not be incremented. This is because the amount of data in these columns is either quite large or has very little value. The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.

All right. The problem has been solved, but also learned a lot of things, this time the index class to understand deeply.



Use of database indexes

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.