For clustered index scanning, we first know that the data is a tree data structure arranged by the index key as the leaf node. The data in each row in the table is attached to the index key, the fastest way to perform data search for such a table is, of course, "clustered index search ". Under what circumstances is "clustered index scanning? When the condition field of the data you want to search for has no index, the query executor will read the data in the entire table one by one to confirm that the data meets the query conditions, however, when fields in the table have clustered indexes, the scan process is called "clustered index scan". On the contrary, when no field in the table has a clustered index, this scan process is called "Table scan". In fact, they are essentially the same process, that is, they obtain the data of each row in the table one by one and confirm that the data meets the conditions. The performance is almost the same. Table scan: The data in the table is stored in the form of a "Heap", that is, when the table inserts a new data, you do not have to consider where the new data should be stored logically, you only need to know the location of the previous data and put it behind it. Pay attention to this point, that is, you must never think that the data stored by clustered indexes is stored in the physical disk in order! They store random locations on disks as blocks, but use a linked list to indicate the locations of their front-end and subsequent nodes through pointers. In this case, you may be enlightened! The reason is very simple. If the clustered index table stores data on the disk sequentially, when the data of an intermediate node is inserted to a previous position, isn't it necessary to move tens of thousands or hundreds of millions of data to the back position of the disk. That's terrible. We are easy to understand clustering index scanning and table scanning, but it is not easy to understand non-clustered index scanning. This is often confusing for beginners, the reason is that there is no need for non-clustered index scanning, because if the query results are not highly selective, clustering index scanning can be used in clustered index tables, why does a non-clustered index scan exist when a table is scanned? The reason for this problem is that we did not consider a situation, that is, if the query result is overwritten or contained by a field with a non-clustered index, at this time, the non-clustered index on the where condition field is not highly selective for the query results. So what kind of query plan will the query optimizer choose? 1. Non-clustered index search? This is definitely not acceptable, because the result set is not highly selective. 2. Clustered index scan (or table scan )? The answer is no, because the overhead of the first two is still relatively large. Why does our optimizer choose this non-clustered index? As long as the index is scanned once, the query does not need to access the data page at all, because the query result fields are overwritten or included by the index, which is much lower than the previous two. Haha!
