Brief Introduction to database indexes and simple database Indexes

Brief Introduction to database indexes and simple database Indexes

Recently, I have asked a lot of questions about database indexes during the written test interview. Because I didn't perform a system review separately, many of the index knowledge points are vague, today, I will sort out the relevant notes (I have not dug them deep, but you can still take a look at them for beginners) for your reference only.

What is an index?

Database indexes are like directories in front of a book, which can speed up database queries.

For example, select * from table1 where id = 44. If no index exists, you must traverse the entire table until the row with ID equal to 44 is found. If an index exists, it must be an index created on the column with ID ), find 44 in the index (that is, find the ID column) to find the location of this row, that is, find this row. It can be seen that the index is used for locating.

Indexes are divided into clustered indexes and non-clustered indexes. Clustered indexes are stored in the order of physical locations, rather than clustered indexes; clustering indexes can improve the speed of multi-row search, rather than clustering indexes can quickly retrieve individual rows.

The purpose of creating an index is to speed up the search or sorting of records in the table.

The cost for setting indexes for a table is: first, the storage space of the database is increased, second, it takes a lot of time to insert and modify data (because the index also needs to change ).

Why create an index?

Creating indexes can greatly improve the system performance.

First, you can create a unique index to ensure the uniqueness of each row of data in the database table.

Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.

Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.

Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.

Some may ask: why not create an index for each column in the table because increasing Indexes has so many advantages? This is because adding Indexes has many disadvantages.

First, it takes time to create and maintain indexes. This time increases with the increase of data volume.

Second, indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.

Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.

Where to create an index

Indexes are created on certain columns in the database table. When creating an index, you should consider which columns can create an index and which Columns cannot create an index. In general, you should create an index on these columns:

1. You can speed up the search on columns that frequently need to be searched;

2. Force the uniqueness of the column as the primary key and the data arrangement structure in the organization table;

3. these columns are usually used in connected columns. These columns are mainly foreign keys, which can speed up the connection. You can create indexes on columns that need to be searched by range frequently because the indexes have been sorted, the specified range is continuous;

4. Create an index on the columns that frequently need to be sorted. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;

5. Create indexes on columns in the WHERE clause frequently to speed up condition judgment.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following features:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.

Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.

Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small, which is not conducive to the use of indexes.

Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when modification operations are far more than search operations, you should not create an index.

Index data structure

B-tree, B is balance, which is generally usedDatabase index. Using the B-tree structure can significantly reduce the intermediate process experienced when locating records, thus accelerating access. B + tree is a variant of B-tree. The famous MySQL uses B + tree to implement its index structure.

Insert operation: When an element is inserted, check whether it exists in B-tree. If it does not exist, end at the leaf node and insert the new element into the leaf node. Note: if the leaf node has enough space, you need to move the leaf node to the right to find the New Keyword element. If the space is full, there is not enough space to add new elements, split the node, split half of the keyword elements into the new adjacent right node, and move the intermediate keyword elements to the parent node (of course, if the space of the parent node is full, the operation also needs to be split.) When the key elements in the node are moved to the right, the related pointer needs to be shifted to the right. If a new element is inserted at the root node and the space is full, the split operation is performed. In this way, the intermediate keyword element in the original root node is moved up to the new root node, which leads to an additional layer of tree height.

Delete)Operation:First, find the elements to be deleted in B-tree. If the element exists in B-tree, delete the element in its node. If the element is deleted, first, determine whether the element has left and right child nodes. If yes, move a similar element from the child node to the parent node, and then move it. If no, after direct deletion, it will be moved .. Delete the element. After moving the corresponding element, if the number of elements in a node is less than ceil (m/2)-1, you need to check whether a neighboring sibling node is full (the number of elements in the node is greater than ceil (m/2)-1). If it is full, you need to borrow an element from the parent node to meet the conditions; if the neighboring brothers are just getting rid of poverty, the number of nodes is smaller than ceil (m/2)-1, then the node is "merged" with a neighboring sibling node to form a node to meet the conditions.

The following examples illustrate the use of indexes in mysql.

Index is the key to quick search. The establishment of MySQL indexes is very important for the efficient operation of MySQL. The following describes several common MySQL index types.

Index fields in database tables can greatly improve the query speed. Suppose we have created a mytable table:

Create table mytable (id int not null, username VARCHAR (16) NOT NULL
); We randomly inserted 10000 records, one of which is 5555, admin.

Find the record "SELECT * FROM mytable WHERE" for username = "admin"
Username = 'admin'; if an index has been created on username, MySQL can find this record accurately without any scanning. On the contrary, MySQL scans all records to query 10000 records.

Indexes are divided into single-column indexes and composite indexes. A single-column index contains only one column. A table can have multiple single-column indexes, but this is not a combination index. A composite index contains multiple columns.

MySQL indexes include:

(1) Common Index

This is the most basic index with no restrictions. It can be created in the following ways:

◆ Create an index

Create index indexName ON mytable (username (length ));
For CHAR and VARCHAR types, the length can be smaller than the actual length of the field; For BLOB and TEXT types, the length must be specified, the same below.

◆ Modify Table Structure

ALTER mytable add index [indexName] ON (username (length ))

◆ When creating a table, specify

Create table mytable (id int not null, username VARCHAR (16) not null,
INDEX [indexName] (username (length); syntax for deleting an INDEX:

Drop index [indexName] ON mytable;

(2) unique index

It is similar to the previous normal index. The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:

◆ Create an index

Create unique index indexName ON mytable (username (length ))

◆ Modify Table Structure

ALTER mytable add unique [indexName] ON (username (length ))

◆ When creating a table, specify

Create table mytable (id int not null, username VARCHAR (16) not null,
UNIQUE [indexName] (username (length )));

(3) Primary Key Index

It is a special unique index and does not allow null values. Generally, when creating a table, you can create a primary key index at the same time:

Create table mytable (id int not null, username VARCHAR (16) not null,
Primary key (ID); of course, you can also use the ALTER command. Remember: A table can only have one primary key.

(4) Composite Index

To visually compare Single-Column indexes and composite indexes, add multiple fields to the table:

Create table mytable (id int not null, username VARCHAR (16) not null,
City VARCHAR (50) not null, age int not null );
To further extract MySQL efficiency, you must consider establishing a composite index. Create name, city, and age in an index:

Alter table mytable add index name_city_age (name (10), city, age );
Usernname is 16 characters long when a table is created.
10. This is because the name length generally does not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.

If
Create a single-column index on usernname, city, and age, so that the table has three single-column indexes. The query efficiency will be significantly different from the preceding combination index, which is far lower than our combination index. Although there are three indexes at this time, MySQL can only use one of them, which seems to be the most efficient single-column index.

The establishment of such a composite index is actually equivalent to the establishment of the following three composite indexes:

Usernname, city, age usernname, city usernname
What about combined indexes like city and age? This is because MySQL Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. This composite index is not used for queries that contain these three columns. The following SQL statements use this composite index:

SELECT * FROM mytable WHREE username = "admin" AND city = "Zhengzhou" SELECT * FROM
Mytable WHREE username = "admin" and the following are not used:

SELECT * FROM mytable WHREE age = 20 AND city = "Zhengzhou" SELECT * FROM mytable WHREE
City = "Zhengzhou"

(5) Time to create an index

Now we have learned how to create an index. Under what circumstances do we need to create an index? IN general, you need to create an index for the columns that appear IN the WHERE and JOIN operations, but this is not the case because MySQL only applies to <, <=, =,>,> =, BETWEEN, IN, and sometimes LIKE will use the index. For example:

SELECT t. Name FROM mytable t left join mytable m ON t. Name = m. username
WHERE m. age = 20 AND m. city = 'zhengzhou'
In this case, you need to create an index for the city and age. Because the userame of the mytable table also appears in the JOIN clause, it is also necessary to create an index for it.

As mentioned earlier, only LIKE needs to be indexed in some cases. MySQL does not use an index when it starts with the wildcard "%" and. For example, the following sentence uses an index:

SELECT * FROM mytable WHERE username like 'admin % 'and the following sentence will not be used:

SELECT * FROM mytable WHEREt Name like '% admin'. Therefore, pay attention to the above differences when using LIKE.

(6) index Deficiency

The advantages of using indexes are described above, but excessive use of indexes will cause abuse. Therefore, the index also has its disadvantages:

◆ Although the index greatly improves the query speed, it also reduces the speed of updating tables, such as performing INSERT, UPDATE, and DELETE operations on tables. When updating a table, MySQL not only needs to save data, but also stores the index file.

◆ Index files that occupy disk space when an index is created. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will expand very quickly.

Indexes are only a factor to improve efficiency. If your MySQL database has a large data volume of tables, you need to spend time researching and creating the best indexes or optimizing query statements.

(7) Notes on using Indexes

Tips and notes for using indexes:

◆ The index does not contain columns with NULL values.

As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.

◆ Use short Indexes

Index a string or column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

◆ Sorting of index Columns

MySQL queries only use one index. Therefore, if an index is already used in the where clause, order
By columns do not use indexes. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.

◆ Like statement operation

Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use the index and like
"Aaa %" can use indexes.

◆ Do not perform operations on columns

Select * from users where YEAR (adddate) <2007;
The operation will be performed on each row, which will cause index failure and scan the entire table, so we can change it

Select * from users where adddate <'2014-01-01 ';

◆ Do NOT use not in and <> operations

The above section describes the MySQL index type.