4. Summary of index usage experience that is not found in other books
1. Using a aggregated index is faster than using a primary key that is not a aggregated index
The following is the example statement: (all extract 250,000 pieces of data)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
Use time: 3326 milliseconds
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
Use time: 4470 milliseconds
Here, using aggregation index is nearly 1/4 faster than using a primary key that is not aggregation index.
2. Using aggregated indexes is faster than using general primary keys as order by, especially in small data volumes
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
Time to use: 12936
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
Time: 18843
Here, using aggregated indexes is 3/10 faster than using a general primary key as order by. In fact, if the data volume is very small, using clustered index as the sorting sequence is significantly faster than using non-clustered indexes; if the data volume is large, such as more than 100,000, the speed difference between the two is not obvious.
3. When using the time period within the aggregated index, the search time will be reduced proportionally to the percentage of the data in the entire data table, regardless of how many of the aggregated indexes are used:
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1''
Time: 6343 milliseconds (extract 1 million pieces)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-6-6''
Time taken: 3170 milliseconds (500,000 pieces are extracted)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
Time taken: 3326 milliseconds (exactly the same as the result in the previous sentence. If the number of collections is the same, then the number greater than and equal to the number is the same)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen
where fariqi>''2004-1-1'' and fariqi<''2004-6-6''
where fariqi>''2004-1-1'' and fariqi<''2004-6-6''
Time taken: 3280 milliseconds
4. The date column will not slow down the query speed because of the input of minutes and seconds.
In the following example, there are 1 million pieces of data in total, and there are 500,000 pieces of data after January 1, 2004, but there are only two different dates, with dates accurate to date; there were 500,000 pieces of data before, with 5,000 different dates, with dates accurate to seconds.
select gid,fariqi,neibuyonghu,reader,title from Tgongwen
where fariqi>''2004-1-1'' order by fariqi
where fariqi>''2004-1-1'' order by fariqi
Time taken: 6390 milliseconds
select gid,fariqi,neibuyonghu,reader,title from Tgongwen
where fariqi<''2004-1-1'' order by fariqi
where fariqi<''2004-1-1'' order by fariqi
Time taken: 6453 milliseconds
V. Other precautions
"Water can carry a boat, or it can cover it", the same is true for the index. Indexing can help improve retrieval performance, but too many or inappropriate indexing can also lead to system inefficiency. Because every time a user adds an index to the table, the database needs to do more work. Too many indexes can even lead to index fragmentation.
Therefore, we need to establish an "appropriate" index system, especially the creation of aggregated indexes, and we should strive for excellence so that your database can be performed with high performance.
Of course, in practice, as a due diligent database administrator, you also need to test more solutions to find out which one is most efficient and most effective.
Previous page1234Read the full text