(4) Summary of indexing experience in other books
1. Use the aggregated index to be used for the main key speed of the aggregated index.
The following is an example statement: (all 250,000 data)
Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi = '2004-9-16'
Time: 3326 ms
Select Gid, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where gid <= 250000
Time: 4470 ms
Here, the main key speed of the aggregation index is faster than the main key speed of the aggregated index.
2. Use aggregated indexes to be used as a general primary key as the order by the order BY, especially in the case of small data
Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Order by Fariqi
Time: 12936
Select Gid, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Order by GID
Time: 18843
Here, when using the aggregated index than the general primary key as the Order By, the speed is 3/10. In fact, if the amount of data is small, use the aggregation index as a silend, which is more significant than using the non-aggregated index speed; and if the amount of data is large, if more than 100,000, the speed difference is not obvious. .
3. Use the time period within the aggregated index, the search time will decrease according to the percentage of the entire data table, regardless of the aggregate index.
Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi> 2004-1-1 '
Time: 6343 ms (extraction of 1 million)
Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi> 2004-6-6 '
Time: 3170 ms (500,000)
Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi = '2004-9-16'
Time: 3326 milliseconds (the result of the sentence is exactly the same. If the number of collected is the same, then the same is equal to the number and equal to the number)
Select GID, Fariqi, Neibuyonghu, Reader, Title from TGongwen Where Fariqi> 2004-1-1 'and Fariqi <2004-6-6'
Time: 3280 ms
4, the date column will not slow down the query speed due to each-second input
In the following example, a total of 1 million data, 500,000 data after January 1, 2004, but only two different dates, the date is accurate to the day; there are 500,000 data, 5000 different Date, the date is accurate to second.
Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi> '2004-1-1' Order by Fariqi
Time: 6390 ms
Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi <'2004-1-1' Order by Fariqi Time: 6453 ms
(5) Other considerations
"Water can be used as a boat, can also come to the boat", the index is the same. Index helps to improve retrieval performance, but too much or improper index will also cause system to inefficient. Because the user adds an index in the table, the database will do more work. Excessive indexing will even result in index fragmentation.
So, we have to build a "appropriate" index system, especially for the creation of aggregated indexes, more refined to make your database can get high performance.
Of course, in practice, as a due diligent database administrator, you have to test some programs, find which program is the highest, most effective.
Second, improve SQL statement
Many people don't know how the SQL statement is executed in SQL Server, and they are worried that the SQL statements you have written will be misunderstood by SQL Server. such as:
Select * from table1 where name = 'zhangsan' and Tid> 10000
And execute:
Select * from table1 where tid> 10000 and name = 'zhangsan'
Some people don't know if the execution efficiency of the above two statements is the same, because if the simple from the statement, these two statements are indeed different, if the Tid is a aggregated index, then the last sentence is only from 10,000 from the table. In the record, you will find it; and the previous sentence, you must first find a few name = 'zhangsan' from the full table, and then subsequent query results according to the restriction condition TID> 10000.
In fact, such concerns are unnecessary. There is a "query analysis optimizer" in SQL Server, which can calculate the search criteria in the WHERE clause and determine which index can reduce the search space of the table scan, that is, it can be automatically optimized.
Although the query optimizer can be optimized according to the WHERE clause, you still need to know how to "Query Optimizer" work. If it is not like this, sometimes Query the optimizer will not follow your original intention.
In the query analysis phase, query the optimizer view each stage of the query and determine whether the amount of data that needs to be scanned is useful. If a phase can be used as a scan parameter (SARG), it is called optimized, and the required data can be quickly obtained using indexes.
Definition of SARG: Used to restrict a search for a search because it is usually referring to a specific match, a matching match or two or more-over-connection. The form is as follows:
Column Operator
or
The column name can appear on one side of the operator, and constant or variable appears on the other side of the operator. Such as:
Name = 'Zhang San'
Price> 5000
5000 Name = 'Zhang San' and the price> 5000 If an expression cannot meet the form of SARG, it will not limit the range of search, that is, SQL Server must determine whether it meets all the conditions in the WHERE clause. So a index is useless for expressions that do not satisfy the form of SARG. After the introduction SARG, we summarize the experience of using SARG and the conclusions of some information in practice: 1. Whether the LIKE statement belongs to SARG depends on the type of wildcard used Such as: name like ', this is SARG and: Name Like'% Zhang 'is not SARG. The reason is that wildcard% is open in the string so that the index cannot be used. 2, OR will cause full table scanning Name = 'Zhang San' AND price> 5000 Symbol SARG, and: Name = 'Zhang San' OR Price> 5000 does not comply with SARG. Using OR will cause full meter scans. 3, non-operator, function caused by the unmet SARG form The most typical case of not satisfying SARG form is to include non-operative statements, such as: not,! =, <>,! <,!>, Not exists, not in, not like, etc., there are also functions. Below is a few examples that don't meet the form of SARG: ABS (price) <5000 Name Like '% three' Some expressions, such as: WHERE price * 2> 5000 SQL Server will also think that SARG, SQL Server will convert this type to: Where price> 2500/2 But we do not recommend this, because sometimes SQL Server does not guarantee that this conversion is completely equivalent to the original expression. 4, in the role of IN is quite Statement: Select * from table1 where tid in (2, 3) with Select * from table1 where tid = 2 or TID = 3 Yes, it will cause full mete scanning if there is an index on the TID, and its index will fail. 5, try to use NOT 6, the execution efficiency of EXISTS and IN is the same Many data show that EXISTS is high than in the execution efficiency, while using NOT EXISTS as much as possible. But in fact, I tried it, found that both the front belt without NOT, the execution efficiency between the two is the same. Because of the subquery, we tried this time the PUBS database comes with SQL Server. We can open the SQL Server's Statistics I / O status before running. (1) SELECT TIM, Price from Titles Where title_id in (SELECT TITLE_ID from Sales Where Qty> 30) The result of this sentence is: Table 'Sales'. Scan count 18, logically read 56 times, physically read 0 times, read 0 times. Table 'Titles'. Scan count 1, logic read 2 times, physically read 0 times, read reading 0 times. (2) SELECT TITLE, Price from Titles Where Exists (Select * from sales where sales.title_id = titles.title_id and qty> 30) The execution result of the second sentence is: Table 'Sales'. Scan count 18, logically read 56 times, physically read 0 times, read 0 times. Table 'Titles'. Scan count 1, logic read 2 times, physically read 0 times, read reading 0 times. We can see this with exists and use in in the use of EXISTS. 7, using the function charIndex () and the previous addition of the grouper LIKE execution efficiency In front, let's talk about if you add a wildcard in front of the Like, it will cause full menu scanning, so its execution efficiency is low. But some information said that using the function charIndex () instead of the LIKE speed will have a big improvement, after I trial, I found this description is also wrong: SELECT GID, TITLE, FARIQI, Reader from TGongwen Where CHARINDEX ('Criminal Investigation Detachment ', reader> 0 and fariqi>' 2004-5-5 ' Time: 7 seconds, in addition: scan count 4, logic read 7155 times, physically read 0 times, read reading 0 times. SELECT GID, TITLE, FARIQI, Reader from Tgongwen Where Reader Like '%' 'Criminal Investigation Detachs' '%' and Fariqi> '2004-5-5' Time: 7 seconds, in addition: scan count 4, logic read 7155 times, physically read 0 times, read reading 0 times. 8, Union does not have a high performance efficiency of the OR We have already talked about the use of OR in the WHERE clause that will cause full menu scanning. Generally, the information I have ever seen is recommended here with union instead of OR. It turns out that this statement is suitable for most. Select Gid, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi = '2004-9-16' or gid> 9990000 Time: 68 seconds. Scan count 1, logic read 404008 times, physically read 283 times, read reading 392,163 times. Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi = '2004-9-16' union Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Gid> 9990000 Time: 9 seconds. Scan count 8, logic read 67489 times, physically read 216 times, read 7499 times. It seems that using Union is usually more efficient than using OR. But after the test, the author found that if the query column on both sides is the same, then union is inverted and the execution speed of the OR is a lot, although the Union scan is an index, and the OR scan is a full table. Select Gid, Fariqi, Neibuyonghu, Reader, Title from TGongwen Where Fariqi = '2004-9-16' or Fariqi = '2004-2-5' Time: 6423 ms. Scan count 2, logic read 14726 times, physically read once, read 7176 times. Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi = '2004-9-16' union Select GID, Fariqi, Neibuyonghu, Reader, Title from Tgongwen Where Fariqi = '2004-2-5'
