The online SQL optimization article is really a lot of to be honest, I have also looked everywhere for such articles, what not to use in, what or, what and, many many, and many people come up with only a few s or even a few examples of the time difference of MS to prove what (a bit ridiculous), Let many people not know whether they are right or wrong. and SQL optimization is every programmer to deal with the database of the required course, so write this article, with friends.
When it comes to optimization, it necessarily involves indexing, just as it is necessary to talk about locks, so you need to know the index, just index, can talk for half a day, so the index I do not say (dozen words are very tired, and I also know very little), you can refer to the relevant articles, this online data is more.
Today to explore the implementation of the MSSQL plan, to let you know how to view the MSSQL optimization mechanism, in order to optimize the SQL query.
Copy Code code as follows:
--drop TABLE T_userinfo----------------------------------------------------
--Build Test table
CREATE TABLE T_userinfo
(
Userid varchar, UserName varchar (20),
Regtime datetime, Tel varchar (20),
)
--Inserting test data
DECLARE @i INT
DECLARE @ENDID INT
SELECT @i = 1
SELECT @ENDID = 100--Change the data you want to insert here, delete all data before inserting it again
While @i <= @ENDID
BEGIN
INSERT into T_userinfo
SELECT ' ABCDE ' +cast (@i as VARCHAR) + ' EF ', ' Lee ' +cast (@i as VARCHAR (20)),
GETDATE (), ' 876543 ' +cast (@i as VARCHAR (20))
SELECT @i = @i + 1
End
--related SQL statement explanation
---------------------------------------------------------------------------
--Building a clustered index
CREATE CLUSTERED INDEX Index_userid on T_userinfo (Userid)
--building a nonclustered index
CREATE nonclustered INDEX Index_userid on T_userinfo (Userid)
--Delete Index
DROP INDEX T_userinfo.index_userid
---------------------------------------------------------------------------
---------------------------------------------------------------------------
--Displays information about the amount of disk activity generated by Transact-SQL statements
SET STATISTICS IO on
--close information about the amount of disk activity generated by Transact-SQL statements
SET STATISTICS IO off
--Displays [returns detailed information about the execution of the statement and estimates the requirements for the statement for the resource]
SET Showplan_all on
--Close [return detailed information about statement execution and estimate statement requirements for resources]
SET Showplan_all off
---------------------------------------------------------------------------
Keep in mind that the set STATISTICS IO and set Showplan_all are mutually exclusive.
OK, now start:
First, we insert 100 data
Then I wrote a query statement:
SELECT * from T_userinfo WHERE userid= ' Abcde6ef '
Select the above statement, press CTRL+L, as shown below
That's the MSSQL Execution plan: Table scans: Scanning rows in a table
We then look at the read and write of the statement to IO:
Execution: SET STATISTICS IO on
The sql:select * from T_userinfo WHERE userid= ' abcde6ef ' will be executed at this time
The switch to the vanishing bar appears as follows:
Table ' T_userinfo '. Scan count 1, logical read 1 times, physics read 0 times, pre-read 0 times.
To explain the meaning of:
The four values are:
The number of scans performed;
Number of pages read from the data cache;
Number of pages read from disk;
Number of pages cached for query
Important: If you have multiple ways of writing a SQL query, the logical reads (logical reads) in these four values determine which is optimized.
Next we build a clustered index for it
Execute create CLUSTERED INDEX index_userid on T_userinfo (Userid)
And then execute the SELECT * from T_userinfo WHERE userid= ' Abcde6ef '
Switch to the message bar to display the following:
Table ' T_userinfo '. Scan count 1, logical read 2 times, physics read 0 times, pre-read 0 times.
At this point the logic reads from the original 1 to 2,
We added an index page, now we query, logical reading is to read two pages (1 index page +1 data pages), at this time less efficient than the index.
At this point, select the query and then ctrl+l the following figure:
Clustered Index Lookup: Scans a specific range of rows in a clustered index
Description, the index is used at this time.
OK, here you should already know the initial understanding of the MSSQL query plan and how to view the IO read consumption!
Next we continue:
Now I'm going to change the test data to 1000.
Then execute set STATISTICS IO on, and then execute
SELECT * from T_userinfo WHERE userid= ' Abcde6ef '
In the case of no clustered index:
Table ' T_userinfo '. Scan count 1, logical read 7 times, physics read 0 times, pre-read 0 times.
In the case of a clustered index: CREATE CLUSTERED index Index_userid on T_userinfo (Userid)
Table ' T_userinfo '. Scan count 1, logical read 2 times, physics read 0 times, pre-read 0 times.
(In fact, that is, read an index page at this time, a data page)
Thus, when the volume of data is slightly larger, the query advantage of the index is displayed.
The first small summary:
When you build the SQL statement, you can press Ctrl+l to see how the statement executes, is it an index scan or a table scan?
View logical reads through set STATISTICS IO on, complete different SQL statements of the same function, logical read
The smaller the query the faster (don't look for an example that has only hundreds of records to counter me).
Let's go further:
OK, now let's take another look, let's change the SQL statement to see how MSSQL executes this SQL.
Now get rid of the index: DROP index T_userinfo.index_userid
Now open the details of the display statement execution]:set Showplan_all on
Then execute: SELECT * from T_userinfo WHERE USERID like ' abcde8% '
See the results bar: the results of some specific parameters, such as IO consumption, CPU consumption.
Here we only look at the Stmttext:
SELECT * from T_userinfo WHERE USERID like ' abcde8% '
|--table Scan (OBJECT: ([student].[ DBO]. [T_userinfo]), Where: (Like ([t_userinfo].[ Userid], ' abcde8% ', NULL))
Ctrl+l look at the diagram line execution plan at this time:
I'll add the index:
Close first: SET showplan_all off
Re-executing: CREATE CLUSTERED INDEX Index_userid on T_userinfo (Userid)
Open again: SET showplan_all on
Re-execution: SELECT * from T_userinfo WHERE USERID like ' abcde8% '
View Stmttext:
SELECT * from T_userinfo WHERE USERID like ' abcde8% '
|--clustered Index Seek (OBJECT: ([student].[ DBO]. [T_userinfo]. [Index_userid]), Seek: ([T_userinfo]. [Userid] >= ' ABCDE8 ' and [T_userinfo]. [Userid] < ' ABCDE9 '), Where: (Like ([t_userinfo].[ Userid], ' abcde8% ', NULL) ORDERED FORWARD) ctrl+l See the diagram execution plan at this point:
Ctrl+l look at the diagram line execution plan at this time:
In the case of an index, we'll write another sql:
SET Showplan_all on
SELECT * from T_userinfo WHERE Left (userid,4) = ' abcde8% '
View Stmttext:
SELECT * from T_userinfo WHERE Left (userid,4) = ' abcde8% '
|--clustered Index Scan (OBJECT: ([student].[ DBO]. [T_userinfo]. [Index_userid]), Where: (SUBSTRING ([t_userinfo].[ Userid], 1, 4) = ' abcde8% ')
Ctrl+l look at the diagram line execution plan at this time:
Let's take a look at three different scenarios for IO.
were as follows:
In the first case: Table ' T_userinfo '. Scan count 1, logical read 7 times, physics read 0 times, pre-read 0 times.
Second: Table ' T_userinfo '. Scan count 1, logical read 3 times, physics read 0 times, pre-read 0 times.
Third: Table ' T_userinfo '. Scan count 1, logical read 8 times, physics read 0 times, pre-read 0 times.
This explains:
The first time is a table scan, sweep 7 pages, that is, full table scan
The second is an index scan, sweeping 1 pages of index, 2 page data page
The third is index Scan + table scan, sweep 1 page index, 7 page data page
[Graphical interface also has CPU and IO consumption, also can see which is the best!]
Through comparison, hey, it is easy to see: The second type of the third type in the case of indexing, like effective use of the index, and left is not, such a simple example of optimization came out, haha.
If you understand all of the above, then you may have a preliminary idea of the optimization of SQL, and the online heap of SQL optimization articles is really that? You have to try to know, and do not have to blindly remember those things, you try to see how the MSSQL is how to carry out on the understanding.
In my example, using a clustered index scan, the field is a number of letters, you can try the pure numbers, letters, Chinese characters, and so on, understand how the next mmsql will change the SQL statement to use the index. And then try again. What is a nonclustered index? What does it have to do with the index? Sub-query How is MSSQL performed? In with no index, like with no index? function with no index? Or, and, UNION? What about subqueries? Here I do not try to show everyone, as long as you know how to see the implementation of the MSSQL plan (graphics and text), a lot of things are very clear.
Big Summary:
There may be more than one SQL that implements the same query functionality, if you judge which optimization, if only from time, will be affected by a lot of external factors, and we understand how MSSQL to perform, through IO logic read, by looking at the diagram of the query plan, through its optimization and executed by the SQL statement, Is the real way to optimize SQL.
In addition to remind: the amount of data will sometimes affect the MSSQL to the same query writing statements of the implementation plan, which is particularly evident in the nonclustered index, there is in the multi-CPU and single CPU, in the context of multi-user concurrency, the same wording of the query statement execution plan will be different, This will require everyone to have the opportunity to experiment (I do not have too much experience to share with you).
First of all, because I know about MSSQL is very shallow, if there is a wrong place, please correct me.
