MySQL index experience

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) not null );

We randomly inserted 1000 records into it, one of which is
I _testid vc_name
555 erquan

Find the record of vc_name = "erquan"
Select * From testindex where vc_name = 'erquanc ';
If an index has been created on vc_name, MySQL can find this record accurately without any scanning! On the contrary, MySQL scans all records, that is, it needs to query 1000 times ~~ Indexes increase the query speed by 100 times.

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 on tablename (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 on tablename (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_name varchar (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. I will not discuss it here ~~

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_name varchar (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 where vc_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); -- note that 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, the query efficiency is the same as that of the preceding composite index? Hey, big difference, 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.

creating such a composite index is equivalent to creating
vc_name, vc_city, I _age
vc_name, three composite indexes such as vc_city
vc_name
! 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" and vc_city = "Zhengzhou"
select * From myindex whree vc_name = "erquan"
the following few do 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 on T. 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. Because MySQL does not use an index when it starts with the wildcard "%" and "_", as shown in
Select * From myindex where vc_name like 'erquan %'
Will use the index, and
Select * From myindex wheret vc_name like '% erquan'
The index 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.

At the end of the article:
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.