In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
Editor to share with you the example analysis of the SQL Server index, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
Preface
This article is compiled from my previous notes, using the index as the entry to discuss the relevant database knowledge (which has been modified to make it easier for people to digest). Friends who have not been in contact with SQL Server can only read the following blue font words, which is simple and useful to save time; if you are friends with a good foundation in the database, you can read all of them, welcome to discuss.
The concept of index
The use of the index: our query and processing speed of data has become the standard to measure the success or failure of the application system, and the use of index to speed up data processing is usually the most commonly used optimization method.
What is the index: the index in the database is similar to the catalog of a book, using the catalog in a book can quickly find the information you want without having to read the whole book. In a database, database programs can use indexes to reweight the data in a table without having to scan the entire table. The catalogue in the book is a list of words and the page number on which they are located, and the index in the database is a list of values in the table and where they are stored.
The pros and cons of indexing: most of the overhead of query execution is Istroke O, and one of the main goals of using indexes to improve performance is to avoid full table scans, because full table scans need to read every data page of the table from disk. If an index points to the data value, the query only needs to read the disk a few times. Therefore, the reasonable use of index can speed up the query of data. However, the index does not always improve the performance of the system, the indexed table needs more storage space in the database, and the running time of the command used to add and delete data and the processing time required to maintain the index will be longer. Therefore, we should use the index reasonably and update and remove the suboptimal index in time.
1. Clustered and nonclustered indexes
Indexes are divided into clustered index and nonclustered index.
1.1 clustered Index
The data of the table is stored in the data page (the PageType of the data page is marked as 1), the SqlServer page is 8k, and the next page is stored when one page is full. If the table has a clustered index, the physical data is stored in the page sorted by the size of the clustered index field. When updating a clustered index field or inserting / deleting data in the middle, it causes the table data to move (with some performance impact) because it maintains ascending / descending sorting.
Note that the primary key is only a clustered index by default, it can also be set to a nonclustered index, or it can be set to a clustered index on a non-primary key field, and there can be only one clustered index for the whole table.
A good clustered index field generally contains the following four features:
(a). Self-growth
Always add records at the end to reduce paging and index fragmentation.
(B). Not to be changed
Reduce data movement.
(C). Uniqueness
Uniqueness is the most ideal feature of any index, and you can specify the position of the index key value in the sort.
More importantly, if the index key is unique, it can correctly point to the source data row RID in each record. If the clustered index key value is not unique, SqlServer needs to generate a combination of uniquifier columns internally as a clustered key to ensure the "key value" uniqueness; if the non-clustered index key value is not unique, it will increase the RID column (clustered index key or row pointer in the heap table) to ensure the "key value" uniqueness.
Think (but skip): the index "key value" is also guaranteed to be unique in non-leaf nodes in order to clarify the location of the index records in non-leaf nodes. For example, there is a nonclustered index field Name2, and there are many Name2='a' records in the table, causing Name2='a' to have multiple index records (nodes) on the non-leaf node. When you insert a Name2='a' record, you can quickly determine which index record (node) to insert to based on the RID of the non-leaf node and the RID of the new record. If there is no RID of the non-leaf node, You have to traverse all the leaf nodes of the Name2='a' to determine the location. In addition, when we select * from Table1 where Name2
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 257
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.