On the experience of MySQL database indexing in PHP development

If we create a testindex table:
CREATE TABLE Testindex (i_testid INT not null,vc_name VARCHAR () not NULL);

We randomly inserted 1000 records into it, one of which
I_testid Vc_name
555 Erquan

Looking for records for vc_name= "Erquan"
SELECT * from Testindex WHERE vc_name= ' Erquan ';
, if the index has been established on the Vc_name, MySQL does not need any scanning, that is, accurate to find the record! Instead, MySQL will scan all records, that is, to query 1000 times AH ~ ~ can be indexed to improve the query speed 100 times times.

Index and combined index

Single-column Index: An index contains only a single column, and a table can have multiple single-column indexes, but this is not a combined index.
Combined index: A cord contains multiple columns.

Second, introduce the type of index

    1. Normal index.
      This is the most basic index, and it has no limitations. It is created in the following ways:
      (1) Creating an index: CREATE INDEX IndexName on tablename (tablecolumns (length)); If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and TEXT type, length must be specified.
      (2) Modifying table structure: ALTER tablename ADD INDEX [IndexName] On (tablecolumns (length))
       (3) specify directly when creating a table: Create TABLE TableName ([...], INDEX [IndexName] (tablecolumns (length));
   
    2. Unique index.
       It is similar to the previous "normal index", except that the value of an indexed column must be unique, but a null value is allowed. If it is a combined index, the combination of the column values must be unique. It is created in the following ways:
       (1) Creating an index: Create UNIQUE index indexname on tablename (Tablecolumns ( Length)
      (2) Modifying the table structure: ALTER tablename ADD UNIQUE [IndexName] On (tablecolumns (length))
      (3) specify directly when creating a table: Create TABLE TableName ([...], UNIQUE [IndexName] (Tablecolumns ( length));

3. Primary KEY index
It is a special unique index and does not allow null values. Typically, you create a primary key index at the same time as the table is being built: CREATE TABLE Testindex (I_testid INT not NULL auto_increment,vc_name VARCHAR () not null,primary key (i _testid)); Of course, you can also use alter order.
Remember that a table can have only one primary key.

4. Full-Text Indexing
MySQL supports full-text indexing and Full-text search starting with version 3.23.23. No discussion here, hehe ~ ~

Syntax for deleting an index: Drop index index_name on tablename

Three-column index and combined index

To visually contrast the two, build a table:
CREATE TABLE Myindex (I_testid INT NOT null auto_increment, Vc_name VARCHAR (m) not NULL, vc_city VARCHAR () not NULL, I _age int NOT NULL, i_schoolid int is not NULL, PRIMARY KEY (I_testid));

In the 10,000 records, 7 of the 8 fields distribute 5 vc_name= "Erquan" records, but City,age,school combinations are different.
Look at this T-sql:
SELECT I_testid from Myindex WHERE vc_name= ' Erquan ' and vc_city= ' Zhengzhou ' and i_age=25;

First, consider a single-column index:
An index was established on the Vc_name column. When you execute T-SQL, MySQL quickly locks the target on the 5 records of Vc_name=erquan, taking it out and putting it in an intermediate result set. In this result set, the first rule out vc_city not equal to "Zhengzhou" record, and then exclude i_age not equal to 25 records, finally filtered out the only eligible records.

Although the index is established on the Vc_name, the query MySQL does not need to scan the entire table, the efficiency is improved, but there is a certain distance from our request. Similarly, the efficiency of a SINGLE-COLUMN index established separately in vc_city and I_age is similar.

To further squeeze MySQL's efficiency, consider establishing a composite index. is to build the Vc_name,vc_city,i_age into an index:
ALTER table Myindex ADD INDEX name_city_age (Vc_name, vc_city,i_age);--notice, when the table is built, the vc_name length is 50, why use 10 here? Because the name does not normally exceed 10 in length, this will speed up the indexing query, reduce the size of the index file, and increase the update speed of the insert.

When you execute T-SQL, MySQL does not need to scan any records to find a unique record!!

There must be someone to ask, if you set up a Single-column index on the vc_name,vc_city,i_age, so that the table has 3 single-column indexes, the query and the same combination index efficiency? Hey, big different, far less than our combined index ~ ~ Although there are three indexes at this time, but MySQL can only use the one that it seems to be the most efficient Single-column index.

The establishment of such a composite index, in fact, is equivalent to the establishment of a separate
Vc_name,vc_city,i_age
Vc_name,vc_city
Vc_name
Such a combination of three index! Why not have such a combination index such as vc_city,i_age? This is because the MySQL composite index "leftmost prefix" results. The simple understanding is to just start from the left. Instead of using the combined index for queries that contain all three columns, the following T-SQL uses:
SELECT * from Myindex whree vc_name= "Erquan" and vc_city= "Zhengzhou"
SELECT * from Myindex whree vc_name= "Erquan"
The following few are not used:
SELECT * from Myindex whree i_age=20 and vc_city= "Zhengzhou"
SELECT * from Myindex whree vc_city= "Zhengzhou"

Iv. Use of indexes

Should you be able to build and use the index? But under what circumstances should an index be built? In general, the columns that appear in the where and join need to be indexed, but not exactly, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like (followed by a description).
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.vc_city= ' Zhengzhou ' when there is a pair of Myin The need to index the vc_city and i_age of the Dex table is necessary because the vc_name of the Testindex table is out of the join clause.

Just now, there are times when the like needs to be indexed? Yes. Because MySQL does not use indexes when querying with wildcard% and _, such as
SELECT * from Myindex WHERE vc_name like ' erquan% '
will use the index, and
SELECT * from Myindex wheret vc_name like '%erquan '
The index is not used.

　　V. Deficiencies of the Index

Is it true that there are so many good words in the index? Of course, there will be shortcomings.

1. Although indexing greatly improves query speed, it reduces the speed of updating tables, such as INSERT, UPDATE, and delete tables. Because MySQL will not only save the data, but also save the index file when updating the table

2. index files that create an index that consumes disk space. Generally this is not a serious problem, but if you create multiple combinations of indexes on a large table, the index files will swell up very quickly.

end of article:

So much to say is to use the index to improve the efficiency of database execution. But indexing is only one factor in improving efficiency. If your MySQL has large data tables, you'll need to spend time researching the best indexes or optimizing the query statements.