Use of database indexes

Today, we find a problem, which is probably the problem. Query The interface information. Local access to the local database is not a problem. However, it is particularly busy to post to the server on a later date. it takes about 5 minutes for the ability to return data. This must not be acceptable to anyone. Just started to think of the server performance issue, in order to verify the database on the server back to local. found that the local speed also slowed down immediately. What the hell 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 it's just a part of it, and there's a dynamic sql I didn't post.

Is the speed affected by a bunch of table connections? 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 actually a few seconds.

Is this table causing a slow connection between tables?

then I started to analyze why the data in the original local database was fast. I found that there is no data in the Interfacedetail in the original local library. And now the tables in the library have 8000 data, which is the cause of the slow query? 8000 above data is not suitable for connection query?

but it occurred to me that the original DB2 The same data, why the query is very fast. First of all, I was verified. the data in each table in the current MySQL is exactly the same as the data in the DB2 .

It turns out that the same is true, but the speed of the DB2 is 0.4 seconds, which 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. It was found that indexes can increase the speed of connections between tables. As a result, I still really found interfacedetail inDB2. the index of the InterfaceID.

I added the same index in mysql :

CREATE INDEX t_pi_vlo_naae_idx1 oninterfacedetail (InterfaceID);

and then directly above the statement. The direct speed is immediately within 0.5 seconds. The problem was conquered.

But why is the index so big role. What is an index?

Why do you create an index? This is due to. Creating indexes can greatly improve the performance of your 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 speed of data. This is also the most basic reason to create an index.

Thirdly, it is particularly meaningful to speed up the connection between tables and tables, especially when implementing the data's textual integrity.

fourth. When you use grouping and sorting clauses for data retrieval. The same can significantly reduce the time to group and sort in a query.

Five, by using the index, can be in the process of querying. Improve the performance of your system with an optimized stealth device.

There may be people who ask: why does adding an index have so many advantages, why not create an index for each column in the correct table? Such an idea has its rationality. However, it has its one-sidedness. Although the index has many advantages, it is unwise to add an index to each column in the table. This is due to. Adding an index also has a number of disadvantages.

First, it takes time to create indexes and maintain indexes, and that time is added as the amount of data is added.

Second, each index takes up a certain amount of physical space in addition to the data tables that occupy the data space. Suppose you want to set up a clustered index, the space you need will be larger.

Thirdly, when the data in the table is added, deleted and changed, 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 the index is created. You should carefully consider which columns you can create indexes on and which columns you cannot create indexes on. Generally speaking. The indexes should be created on these columns. For example, on a column that often needs to be searched. The ability to speed up the search, forcing the column to be unique on the column that is the primary key, and arranging the structure of the data in the organization table; the columns that are often used on connected columns are mostly foreign keys. Ability to speed up connections, create indexes on columns that often have to be searched by scope, because the index is already sorted, its specified range is contiguous, and indexes are created on the columns that are often required to be sorted, because the index is already sorted. This allows the query to take advantage of the index's ordering. Speed up the sort query time. Speed up the inference of a condition by creating an index on the columns that are often used in the WHERE clause.

Same. For some columns, you should not create an index. In general, these columns that should not be indexed have the following characteristics: first, you should not create an index for columns that are very rarely used or included in the query.

This is due to the fact that these columns are very rarely used and therefore indexed or not indexed. does not improve query speed.

Instead. Because of the addition of indexes, the system maintenance speed and space requirements are reduced. Second. You should not add indexes to columns that have only very few data values.

This is because the values of these columns are very small, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a very large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are very large. Adding an 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 added. This is due to. The amount of data in these columns is either quite large. Or the value is very small.

Fourth. When the change performance is far greater than the retrieval performance. You should not create an index. This is due to. Changes in performance and retrieval performance are conflicting.

When an index is added, retrieval performance is improved. But it will reduce the performance of changes.

When the index is reduced. Improves the performance of the changes. Reduce retrieval performance. So. Indexes should not be created when the performance of the churn 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