What does SQL index mean? When is it used?

Indexes are mainly used to speed up data retrieval and are useful when there is a large amount of data. The index is equivalent to the library's book catalog, if you want to find a book, you can find the book on the book catalog, which is obviously much faster than going to the bookshelf to find the book, and the index is the reason. Indexes play a very important role in database optimization, like my **(

The tables in it are basically indexed, so that my ** data will not be very slow when there are millions of data (but now there is very little data, hehe), if there is no index, it will be miserable. Here's an introduction online, check it out:

Introduction to indexes Indexes are another way of representing data that provides a different order of data than the order in which it is physically stored on disk. The special role of indexes is to rearrange the physical location of records within a table. An index can be built on a single column of a data table, or on a combination of several columns of a table.

Recall that in the library, shelves and shelves of books are stored. Suppose a book is found, and the librarian searches one by one as necessary until he finds the book he is looking for. This kind of search must be time-consuming and laborious, so the administrator can establish index cards for all the book titles alphabetically, and put them in different office drawers in alphabetical order, so that the process of finding books one by one becomes a query for index cards in the desk drawer, and it is easy to get information about the book.

Further, imagine that the librarian is diligent and not only sorts the books by the alphabetical title, but also creates separate index cards by author name and publisher, a process that will give the borrower more flexibility in retrieving the book information. Therefore, it is possible to create multiple indexes in a single data table.

In a database, querying a table that does not have an index is generally referred to as a full table scan. A full table scan is the process used by the database server to search for every record in a table until all records that meet the given criteria are returned. This can be likened to looking up books in the library, starting with the first book on the first shelf and browsing each book until you find the one you want.

For efficient querying, you can index a field on a data table, and since the index includes a pointer to the data, the database server reads the index with only one column of data in the order in which the indexes are arranged (only one index) until the index pointer points to the corresponding record. Because indexes look up only one field and do not traverse the entire table, index lookups are generally faster than full table scans.

So, is it necessarily faster to use an index query than a full table scan? The answer is no. If you want to query a small table (with few records) or most of the data in a large table (with many records), a full table scan is more useful.

For example, querying the Gender field, whose value can only be Male or Female, doesn't make much sense to index on it, or even on Boolean, large binary (note, image, etc.). It's mine.

The same effect as the label.

In general, the index may be used in the first field corresponding to the index in the condition.

Microsoft's SQL Server provides two types of indexes: clustered indexes (also known as clustered indexes and clustered indexes) and nonclustered indexes (also known as nonclustered indexes and nonclustered indexes).

An index is an important data structure in a database, and its fundamental purpose is to improve query efficiency. Most of today's database products use the ISAM index structure that IBM first proposed.

Indexes are another type of file record that contains a variety of records that indicate the relevant data records. Each index has a corresponding search code, and any subset of character segments can form a search code. In this way, an index acts as a collection of all data catalog items, providing all the effective support needed to locate all data catalog items for a given search code value.

The format is:

select field name table.

from Table Name Table.

with (index).

where query criteria.

What sql-indexes do:

Microsoft's SQL Server provides two types of indexes: clustered indexes (also known as clustered indexes and clustered indexes) and nonclustered indexes (also known as nonclustered indexes and nonclustered indexes). Let's take an example to illustrate the difference between clustered and nonclustered indexes

In fact, the body of our Chinese dictionary itself is a clustered index. For example, if we want to look up the word "an", we will naturally turn the first few pages of the dictionary, because the pinyin of "an" is "an", and the dictionary of Chinese characters sorted by pinyin starts with the English letter "a" and ends with "z", so the word "an" is naturally ranked at the front of the dictionary. If you can't find the word after going through all the parts that start with "a", then you don't have it in your dictionary; Similarly, if you look up the character "zhang", you will also turn to the last part of your dictionary, because the pinyin of "zhang" is "zhang".

In other words, the body of the dictionary is a table of contents in itself, and you don't need to look through other directories to find what you're looking for. We call this kind of directory in which the body content itself is arranged according to certain rules as a "clustered index".

If you know a word, you can quickly look it up automatically. But you may also encounter a word you don't know, don't know its pronunciation, at this time, you can't find the word you want to check according to the method just now, but need to find the word you are looking for according to the "radical", and then turn directly to a page to find the word you are looking for according to the page number after the word. For example, if you look up the word "Zhang", we can see that the page number of "Zhang" in the checklist after the radical is 672 pages, and the top of "Zhang" in the checklist is the word "Chi", but the page number is 63 pages, and the bottom of "Zhang" is the word "crossbow", and the page is 390 pages.

Obviously, these words are not really located above and below the word "zhang", and now the three words "chi, zhang, and crossbow" that you see are actually their ordering in the nonclustered index, which is the mapping of the words in the dictionary body in the nonclustered index. This is how we can find the word you need, but it takes two processes, first finding the result in the table of contents, and then turning to the page number you need. We refer to this sort of directory as a pure directory and the body of the text as a "nonclustered index".

: Index is a Chinese word, pronounced suǒ yǐn. It means a tool for searching for library materials.

Extension: Interpretation.

According to certain needs, excerpts of the main content or various titles in books and periodicals, indicate the source and page number, and arrange them in a certain order for people to consult. It is also called "lead".

An index is a single, physical storage structure that sorts the values of one or more columns of a database table, and is a collection of values in one or more columns of a table and a corresponding list of logical pointers to the data pages in the table that physically identify those values. An index acts like a table of contents for a book, and you can quickly find what you need based on the page numbers in the table of contents.

An index provides pointers to data values stored in a specified column of a table, and then sorts those pointers according to the sort order you specify. The database uses an index to find a specific value, and then points along the pointer to the row that contains that value. This allows SQL statements corresponding to tables to be executed faster, allowing for quick access to specific information in database tables.

When there are a large number of records in a table, if you want to query the table, the first way to search for information is to search the whole table, which is to take out all the records one by one, compare them with the query conditions one by one, and then return the records that meet the conditions, which will consume a lot of database system time and cause a large number of disk operations. The second is to create an index in the table, then find the index value that matches the query conditions in the index, and finally quickly find the corresponding record in the table by saving the rowid (equivalent to the page number) in the index.

Advantages: 1. Greatly speed up the retrieval of data;

2. Create a unique index to ensure the uniqueness of each row of data in the database table;

3. Accelerate the connection between tables and tables;

4. When using grouping and sorting clauses for data retrieval, the time of grouping and sorting in the query can be significantly reduced.

Disadvantages: 1. The index needs to occupy physical space.

2. When adding, deleting, and modifying the data in the table, the index should also be dynamically maintained, which reduces the maintenance speed of the data.

History: Indexes first appeared in the West, mainly for medieval European religious works. After the 18th century, there began to be thematic indexes in the West, and by the end of the 19th century, content analysis indexes were widely used.

Indexes in China appeared relatively late. It is generally believed that the "Rhyme of the Names of the Two Han Books" compiled by Fu Shan at the end of the Ming Dynasty is the earliest surviving index of personal names. During the Qianjia period of the Qing Dynasty, Zhang Xuecheng advocated the compilation of a comprehensive index of group books.

In the 20s of the 20th century, with the introduction of Western indexing theory and compilation technology, indexing and research in China in the modern sense flourished. In 1930, Qian Yaxin published "Index and Index Law", and in 1932, Hong Ye published "Introduction to Theory", which marked the rapid development of modern indexing theory and technology with Chinese characteristics.

In the 50s of the 20th century, computer technology was used for indexing. Since then, a large number of machine-compiled indexes have made profound changes in indexing theory, technology, and the form of indexing carriers.

Indexes are not covered in the SQL standard, but commercial relational database management systems generally support indexing mechanisms, but different relational database management systems support different types of indexes.

Indexes have become a very important part of relational databases. They are used as table pointers that contain the data they care about. With an index, you can find a specific record directly from a table, rather than having to scan the table sequentially and look it up one at a time.

For large tables, indexes are necessary. Without indexing, you have to wait hours, days, not seconds to get a result.