Is it true that Select * does not go through the Index ?, Select Index

Is it true that Select * does not go through the Index ?, Select Index

The index indicates that the execution plan of the SQL statement uses 1. Clustered index search 2. index search, and the where clause must be included in the query statement.

Searches for records in clustered or non-clustered indexes based on the filter conditions of the where clause.

When a table has only one column:

Clustered Index

 

 USE [tempdb] GO CREATE TABLE t1 ( id INT ) GO CREATE CLUSTERED INDEX CIX_T1 ON [dbo].[t1](ID ASC) GO DECLARE @I INT SET @I = 1 WHILE @I < 1000      BEGIN         INSERT  INTO [dbo].[t1] ( [id] )                 SELECT  @I         SET @I = @I + 1     END SELECT * FROM [dbo].[t1] WHERE [id]=20

View Code

 

Non-clustered Index

 

 USE [tempdb] GO  CREATE TABLE t2 ( id INT ) GO CREATE NONCLUSTERED INDEX IX_T2 ON [dbo].[t2](ID ASC) GO DECLARE @I INT SET @I = 1 WHILE @I < 1000      BEGIN         INSERT  INTO [dbo].[t2] ( [id] )                 SELECT  @I         SET @I = @I + 1     END SELECT * FROM [dbo].[t2] WHERE [id]=20

View Code

Only one column will be indexed

When a table has multiple columns

There are three situations:

1. Only clustered Indexes

2. Only non-clustered indexes are supported.

3. clustered and non-clustered Indexes

Only clustered Indexes

-- Only the clustered index USE [tempdb] go create table Department (clustered mentid int identity (1, 1) not null primary key, Name NVARCHAR (200) not null, groupnvname archar (200) not null, Company NVARCHAR (300), ModifiedDate datetime not null default (GETDATE ())) DECLARE @ I int set @ I = 1 WHILE @ I <100000 BEGIN INSERT INTO Department (name, [Company], groupname) VALUES ('sales Department '+ CAST (@ I AS VARCHAR (200), 'Hello China Co., Ltd. XX branch', 'sales group ') SET @ I = @ I + 1 END SELECT * FROM [dbo]. [Department] WHERE [dimension mentid] = 2

View Code

Summary:

Only tables with clustered indexes: If the where clause does not include the first field when the clustered index is created, clustered index scanning is used.

The following SQL statement uses clustered index search because it includes the first field when the clustered index is created.

SELECT * FROM [dbo]. [Department] WHERE [Company] = 'sales Department 12' AND [partition mentid] = 12

Only non-clustered Indexes

 

-- Only USE [tempdb] go create table Department (clustered mentid int identity (1, 1) not null, Name NVARCHAR (200) not null, GroupName NVARCHAR (200) not null, Company NVARCHAR (300), ModifiedDate datetime not null default (GETDATE () create nonclustered index IX_Department ON Department (specified mentid ASC) DECLARE @ I int set @ I = 1 WHILE @ I <100000 BEGIN INSERT INTO Department (name, [Company], groupname) VALUES ('sales Department '+ CAST (@ I AS VARCHAR (200), 'Hello China Co., Ltd. XX branch', 'sales group ') SET @ I = @ I + 1 END SELECT * FROM [dbo]. [Department] WHERE [Company] = 'sales Department 12' AND [sales mentid] = 12

View Code

 

Summary:

Only tables with non-clustered indexes: If the where clause does not include the first field when a non-clustered index is created, the table or index scan is used.

The following SQL statement uses a non-clustered index for search, because it includes the first field when creating a non-clustered index.

SELECT * FROM [dbo]. [Department] WHERE [Company] = 'sales Department 12' AND [partition mentid] = 12

 

Clustered indexes and non-clustered Indexes

-- USE [tempdb] go create table Department (clustered mentid int identity (1, 1) not null primary key, Name NVARCHAR (200) not null, groupName NVARCHAR (200) not null, Company NVARCHAR (300), ModifiedDate datetime not null default (GETDATE () create nonclustered index IX_Department ON Department (Company ASC) DECLARE @ I int set @ I = 1 WHILE @ I <100000 BEGIN INSERT INTO Department (name, [Company], groupname) VALUES ('sales Department '+ CAST (@ I as varchar (200), 'Hello China Co., Ltd. XX branch', 'sales group') SET @ I = @ I + 1 END

View Code

Summary:

Tables with clustered indexes and non-clustered indexes: If the where clause includes the first field when the clustered index is created, the clustered index will be used for search.

If the where clause includes the first field when a non-clustered index is created, but does not include the first field when a clustered index is created, the index query is used.

If the where clause does not include the first field when a non-clustered index is created or the first field when a clustered index is created, the clustered index scan is used.

1 SELECT * FROM [dbo]. [Department] WHERE [GroupName] = 'sales group'

Summary

In fact, the key to not going through the index depends on whether the where clause is included or not.

When you create a clustered indexFirst field

When creating a non-clustered IndexFirst field

It has nothing to do with select *. The biggest impact of select * isAdditional IO overhead

Such operators as "Key lookup" and "RID lookup" are additional overhead

Key search: Find the values of other fields in the clustered index.

RID lookup: Find the values of other fields in the heap table.

 

My SQL statements are not indexed (Oracle help)

Use the following method to use indexes:
Select/* + index (a03 a03_a306_index )*/
* From a03 where a306> 1.5;

The HINTS in the middle seem to be annotated with ORACLE, forcing the use of Indexes
There are a lot of questions about not going to index, such as not doing Index analysis or indexing is invalid. It's too general and I don't know what's going on.

Whether the SQL index is for select or where

We recommend that you check the usage of the index. When you use the where clause, it is useful. When it is an update deleted clause, it also involves fields, but it is mainly used for query.