How to use indexes in MySQL: Detailed analysis and examples, mysql Indexes

How to use indexes in MySQL: Detailed analysis and examples, mysql Indexes

Using indexes in database tables can greatly improve the query speed. Suppose we have created a testIndex table:

Create table testIndex (I _testID INT NOT NULL, vc_Name VARCHAR (16) NOTNULL );

We randomly inserted 1000 records into the table, one of which is I _testID vc_Name 555 erquan.

When querying the record SELECT * FROM testIndex WHERE vc_Name = "erquan"; for vc_Name = "erquan", MySql does not need to perform any scans if an index has been created on vc_Name, this record can be found accurately! On the contrary, MySql scans all records to query 1000. The query speed is increased by 100 times by indexing.

I. index is divided into single-column indexes and composite indexes

Single-Column index: an index only contains a single column. A table can have multiple single-column indexes, but this is not a combination index. Composite Index: A cable contains multiple columns.

Ii. Introduce the index type

1. Common indexes.

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

(1) CREATE an INDEX: create index indexName ONtableName (tableColumns (length); for CHAR and VARCHAR types, the length can be smaller than the actual length of the field; For BLOB and TEXT types, length must be specified, the same below.

(2) modify the table structure: ALTER tableName add index [indexName] ON (tableColumns (length ))

(3) when creating a TABLE, specify: create table tableName ([...], INDEX [indexName] (tableColumns (length ));

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:

(1) CREATE an INDEX: create unique index indexName ONtableName (tableColumns (length ))

(2) modify the table structure: ALTER tableName add unique [indexName] ON (tableColumns (length ))

(3) when creating a TABLE, specify: create table tableName ([...], UNIQUE [indexName] (tableColumns (length ));

3. Primary Key Index

It is a special unique index and does not allow null values. Create table testIndex (I _testID int not null AUTO_INCREMENT, vc_NameVARCHAR (16) not null, primary key (I _testID )); of course, you can also use the ALTER command. Remember: A table can only have one primary key.

4. Full-text index

MySQL supports full-text indexing and full-text retrieval from version 3.23.23.

Syntax for deleting an INDEX: drop index index_name ON tableName

Iii. Single-Column index and Composite Index

To visually compare the two, create another table:

Create table myIndex (I _testID int not null AUTO_INCREMENT, vc_NameVARCHAR (50) not null, vc_City VARCHAR (50) not null, I _Age int not null, I _SchoolID INT NOT NULL, primary key (I _testID ));

Five vc_Name = "erquan" records are distributed in the Top 7 and bottom 8 of the 10000 records, except that the combinations of city, age, and school are different.

Look at this T-SQL: SELECT I _testID FROM myIndex WHEREvc_Name = 'erquanc' AND vc_City = 'zhengzhou 'AND I _Age = 25;

First, create a single column index:

An index is created on the vc_Name column. When the T-SQL was executed, MYSQL quickly locked the target to five records in vc_Name = erquan and pulled them out to the intermediate result set. In this result set, records with vc_City not equal to "Zhengzhou" are excluded, records with I _Age not equal to 25 are excluded, and a unique matching record is filtered out.

Although an index is created on vc_Name, MYSQL does not need to scan the entire table during query, which improves the efficiency, but there is still some distance from our requirements. Similarly, the efficiency of single-column indexes created in vc_City and I _Age is similar.

To further extract MySQL efficiency, you must consider establishing a composite index. Vc_Name, vc_City, and I _Age are built into an index:

Alter table myIndex add index name_city_age (vc_Name (10), vc_City, I _Age );

When creating a table, the length of vc_Name is 50. Why is 10 used here? In general, the length of the name will not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.

When you run the T-SQL, MySQL finds a unique record without scanning any record !!

Someone must ask, if you create a single column index on vc_Name, vc_City, and I _Age respectively, so that the table has three single column indexes, will the query efficiency be the same as that of the preceding composite indexes? The difference is far lower than our combined 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

Vc_Name, vc_City, I _Age
Vc_Name, vc_City
Vc_Name

These three composite indexes! Why are there no composite indexes such as vc_City and I _Age? This is because mysql Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. Not as long as the query contains these three columns will use the composite index, the following T-SQL will use:

SELECT * FROM myIndex WHREE vc_Name = "erquan" ANDvc_City = "Zhengzhou"
SELECT * FROM myIndex WHREEvc_Name = "erquan"

The following are not used:

SELECT * FROM myIndex WHREE I _Age = 20 AND vc_City = "Zhengzhou"
SELECT * FROM myIndex WHREE vc_City = "Zhengzhou"

4. Using Indexes

So far, should you create and use indexes? But under what circumstances do indexes need to be created? 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 (as described later) will use the index.

SELECT t. vc_Name FROM testIndex t left join myIndex m ONt. vc_Name = m. vc_Name WHERE m. I _Age = 20 AND m. when vc_City = 'zhengzhou ', you need to create an index for the vc_City and I _Age OF THE myIndex table. Because the vc_Name OF THE testIndex table is opened in the JOIN clause, it is also necessary to create an index for it.

As I mentioned earlier, only LIKE needs to be indexed in some cases? Yes. MySQL does not use indexes when querying with wildcards % and _, for example, SELECT * FROM myIndex WHERE vc_Name like 'erquan %'
The index will be used, and SELECT * FROM myIndex WHEREt vc_Namelike '% erquan' will not be used.

V. Index Deficiency

So many good words about indexes are mentioned above. Is it really as good as the legend? Of course, there will be disadvantages.

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

2. 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.

After talking about this, I just want to use indexes to improve the efficiency of database execution. However, indexing is only a factor to improve efficiency. If your MySQL has big data tables, you need to spend time researching and creating the best indexes or optimizing query statements.

MYSQL index question: how to use indexes in queries?

Suppose you have a table,
SQL> CREATE TABLE test_tab (2 id INT,
3 name VARCHAR (10 ),
4 age INT,
5 val VARCHAR (10) 6); your business has a query, which is
SELECT * FROM test_tab WHERE name = data of an external input
At the beginning, when there was not much data, the execution results were good.
As the amount of data increases, the query becomes slower and slower.
Then the index is created on the name.
Create index idx_test4_name ON test_tab (name );
This will speed up the previous query.
However, one day, you executed the following SQL statement and found that the speed was slow.
SELECT * FROM test_tab WHERE age = 25
Why? Because there is no index on the age Field
The index is only on the name.
In other words, the condition in the WHERE clause will automatically determine whether there are available indexes. If there is any, this should not be used.
A multi-column index is an index that contains two fields.
For example: create index idx_test_name_age ON test_tab (name, age); then SELECT * FROM test_tabWHEREname LIKE 'zhang %'
AND age = 25
The above indexes can be used for such queries.
Multiple-column indexes are also available. In some cases, it is sufficient to only access the index for queries, and no more table access is required. For example, SELECTAVG (avg) AS average age FROMtest_tabWHEREname LIKE 'zhang %'
At this time, name and age are included in the index. You do not need to retrieve the data in the table for query.

SQL, index example

Let's use the mysql database as an example.

1. What is an index?

Indexes are used to quickly search for records with specific values. All MySQL indexes are saved as B-trees. If no index exists, MySQL must scan all the records of the entire table from the first record until the required records are found. The more records in the table, the higher the operation cost. If an index has been created on the column used as a search condition, MySQL can quickly obtain the location of the target record without scanning any records. If the table has 1000 records, the index search records should be at least 100 times faster than the Sequential Scan records.

Suppose we have created a table named "people:

Create table people (peopleid smallint not null, name CHAR (50) not null );

Then, we randomly insert 1000 different name values into the people table. Displays a small part of the data file of the people table:

We can see that there is no clear order for the name column in the data file. If we create an index for the name column, MySQL will sort the name column in the index:

For each item in the index, MySQL internally stores the "Pointer" of the actual record location in a data file for it ". Therefore, if we want to find the peopleid of the record whose name is equal to "Mike" (the SQL command is "SELECT peopleid FROM people WHERE name = \ 'Mike \';"), mySQL can search for the "Mike" value in the name index, and then directly go to the corresponding row in the data file to return the peopleid (999) of the row accurately ). In this process, MySQL only needs to process one row to return results. If there is no index for the "name" column, MySQL will scan all records in the data file, that is, 1000 records! Obviously, the less records that need to be processed by MySQL, the faster it can complete the task.

Ii. Index types

MySQL provides multiple index types:

Common Index

This is the most basic index type, and it has no limitations such as uniqueness. Common indexes can be created in the following ways:

CREATE an INDEX, for example, create index <INDEX Name> ON tablename (column list );
Modify a TABLE, such as alter table tablename add index [INDEX name] (column list );
Specify an INDEX when creating a TABLE, for example, create table tablename ([...], INDEX [INDEX name] (column list ));

Unique Index

This index is basically the same as the previous "normal index", but there is a difference: all values of the index column can only appear once, that is, they must be unique. You can create a unique index in the following ways:

CREATE an INDEX, for example, create unique index <INDEX Name> ON tablename (column list );
Modify a TABLE, such as alter table tablename add unique [index name] (column list );
Specify an index when creating a TABLE, for example, create table tablename ([...], UNIQUE [index name] (column list ));

Primary Key

A primary key is a unique index, but it must be specified as a "primary key ". If you have used columns of the AUTO_INCREMENT type, you may already be familiar with primary keys and other concepts. The primary key is generally specified during TABLE creation, for example, "create table tablename ([...], primary key (column list ));". However, we can also add the primary key by modifying the TABLE, for example, "alter table ta... the remaining full text>