Shulou(Shulou.com)06/01 Report--

Database optimization engineers must read the first part (index, view)

Security code: if you work hard, you can get a good night's sleep; if you work hard all your life, you can get a happy sleep.

Before the technical details of this chapter, I would like to share a summary of my communication with a younger brother who is an IT tonight:

We talked from 8: 00 to 10: 00. He was mainly a senior operator of Linux, and most of the time he still did some work on the database. He was supposed to be my younger brother. Ha ha. He said that when he just graduated from a foreign-funded high-tech enterprise to a state-owned enterprise, his salary increased by 2000, and now it is 8500 after tax. But he regretted it very much. I said that the former company should be so powerful that you would rather earn thousands less per month than go back to the previous company. He said that salary is not the only standard to measure a person's value. I'll give you an example to illustrate how powerful this company is. This company is called Qingniu (Beijing) Technology Co., Ltd., the most important thing to do is the majority of employees who work unpaid overtime on their own. Recall that I had just graduated and worked in that software company. In short, I was very efficient. For example, the manager issued an order to the technical department to finish it before leaving work at noon, and the staff of six or seven departments helped me finish it together. But now in the xx state-owned enterprise, I want to make a backup, do not know the key number, ask the manager, the test engineer, and ask everywhere, ah, when I know the key number. Something that could have been done at ten o'clock in the morning. As a result, it just started at two o'clock in the afternoon. If I were given a chance to choose again, I would certainly choose a company with strong combat effectiveness and cultural heritage. In Qingniu, I seemed to see the second Alibaba Group in the future. In fact, I would like to sum up: vision determines pattern choice and future. In the words of my teacher, the power of company culture largely determines how far he can go and how high he can fly!

It's 0: 54 in the morning, and we officially begin the indexing and detailed interpretation of the view. I hope, like the blog nickname "a candlelight", to help more people solve practical problems. Thank you for your support. I will continue to update more original technical documents.

Experiment case 1: create a database and use an index to query students' test scores (multiple tables are outlined in the second part of the T-SQL query sentence)

Select Student.StudentName,Subject.SubjectName,Result.ExamDate,Result.StudentResult

From Subject,Student,Result with (INDEX=aaa) Note: INDEX=aaa, that is, index = index name

Where Result.SubjectId=Subject.SubjectId and Result.StudentNo=Student.StudentNo and Result.StudentResult between 80 and 90

Note: INDEX=aaa, that is, index = index name. Although you can specify which index SQL Server will query for data, you generally do not need to specify it manually, and SQL Server will automatically optimize the query based on the index created. In fact, using an index can speed up data retrieval, but it is not necessary to establish a retrieval for each column. Because the retrieval itself also needs to be maintained and takes up some resources.

Case 2: verify the role of the index

1. First, create a table with a large amount of data, called "Student Table", with three columns, student number, name and class, as shown in the following figure, the student number is automatically numbered, and the class is the default value "Class one".

2. Insert a large amount of data into the table, the more data, the better the effect of verifying the index.

Complete with sentence: While 1 > 0 Insert into student table (name) values ('Yang Wen')

The above statement is an endless loop, unless forced to end, if 1 is greater than 0, the name will always be inserted into the table

As shown in the following figure:

3. Wait about 5 minutes, open the properties of the table, and view the number of rows of the table, which is 1030550, as shown below:

We can right-click and select the first 1000 lines, and the effect is as follows:

4. Use the statement to query 900000 rows of data, Select * from student table Where student number = 900000

5. Open the "sql server profiler" tool to track, as shown in the following figure:

Open the "sql server profiler" tool to view the tracking information and find that the query time is very long. Cpu worked for 359ms, reads: read 8630 times, writes: wrote 9 times, duration: took a total of 649ms to complete the query.

6. In order to make the following analysis file more accurate, execute Select * from student table Where student number = 900000 more times.

Then save the results of the trace on the desktop:

Note: select the first item here to track the file. Then save to the desktop, and the effect is as follows:

7. Open "Database engine tuning Advisor", add tracking files, analyze, find index recommendations, and need to establish an index.

Note: select the student table in the benet database and click "start Analysis"

The index type is clusterd (clustered index), and the index is listed as "student number".

8. Build a clustered index according to the index recommendations of Database engine tuning Advisor, and select unique

9. Execute Select * from student form again Where student number = 900000

10. Open sql server profiler to view the tracking time, and find that the query time is greatly improved, indicating that the index can improve the query speed.

It was found that the total time was 1 millisecond, which was so ignored that it took almost no time to query immediately.

Case 4: practice creating various indexes separately

First of all, let's take a look at the classification of indexes and the considerations for selecting index columns:

After we have mastered the academic theory, we will carry out detailed experimental operations to further consolidate:

1. Create a clustered index

There are currently no indexes or primary keys in the tstudent table

Create a clustered index for the tstudent table

Select studentID and click the key button on the upper left

Creating a primary key for the studentID of the Tstuden table also creates a clustered index

2. Create a composite index

Create a combined index for the score sheet, because a student cannot enter scores for a subject twice, so create a combined index of studentID and subjectID in the score table

3. Create a clustered index with the command

Create a table TS

Create TABLE TS (

StudentID varchar (10) NOT NULL

Sname varchar (10) DEFAULT NULL

Sex char (2) DEFAULT NULL

CardID varchar (20) DEFAULT NULL, Note: in practical work, it is recommended to keep it simple and fast, to ensure its quality. These grammars can be expanded.

Birthday datetime DEFAULT NULL

Email varchar (40) DEFAULT NULL

Class varchar (20) DEFAULT NULL

EnterTime datetime DEFAULTNULL

)

Go

Create a clustered index with the command

Create clustered index CL_studentID

On TS (studentID)

Creating a clustered index does not necessarily create a primary key, as shown in the following figure:

4. Create a unique index

When you create a uniqueness constraint, a uniqueness index is created, and there can be no duplicate values.

Create a unique nonclustered index for the Tstudent table

Create unique nonclustered index U_cardID on TStudent (cardID)

5. Create a nonclustered index-can have duplicate values

Create a nonclustered index for the name column of the Tstudent table

Use the command to view the index on the table

Select * from sys.sysindexes where id= (select object_id from sys.all_objects where

Name='Tstudent')

1 in Indid stands for clustered index

2 in Indid represents a unique nonclustered index

3 in Indidz stands for nonclustered index

II. View

Here, some examples will not be demonstrated one by one, because, in my opinion, as a database administrator, you must master the skill of database optimization.

It is best to master some basic general syntax, although the view is a variable, updated and changed at any time, it is very convenient and concise to use, and can be directly based on it.

Execute:

For example

Select * from View name

Where condition = xxx

It's convenient, but after all, the view has limitations and needs to be improved in terms of performance and modification limitations.

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.