MySQL uses indexes to optimize query efficiency

The concept of an index

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. More generally, the database index is like a directory in front of a book, which can speed up the database query. In the absence of an index, the database will traverse all of the data and choose to match the criteria, and with the corresponding index, the database will look directly in the index for eligible options. If we replace the SQL statement with "SELECT * from table name WHERE id=2000000", do you want the database to give you the result after reading 2 million rows of data sequentially or to locate it directly in the index?

The index is divided into clustered index and non-clustered index, and the clustered index is in order according to the physical location of the data, and the non-clustered index is different; The clustering index can improve the speed of multi-row retrieval, but the non-clustered index is very fast for the single-line retrieval.

Type of index

1. General Index

This is the most basic index and it has no limitations.

--Create indexes directly
CREATEINDEXIndexName onTable(column(length))
--How to modify the table structure to add an index
ALTERTableaddINDEXIndexName on(column(length))
-Create the index while creating the table
CREATETABLE`Table` (
' ID ' int (11) notNULLAuto_increment,
' title ' char (255) CHARACTERSETUtf8COLLATEUtf8_general_ci notNULL,
' Content ' text CHARACTERSETUtf8COLLATEUtf8_general_ciNULL,
' Time ' int (10)NULLDEFAULTNULL,
PRIMARYKEY(' id '),
INDEXIndexName (Title (length))
)
--Delete index
DROPINDEXIndexName onTable

2. Unique index

Like a normal index, the difference is that the value of the indexed column must be unique, but it allows for a null value (note differs from the primary key). If it is a composite index, the combination of column values must be unique, similar to the creation method and the normal index.

--Create a unique index
CREATEUNIQUEINDEXIndexName onTable(column(length))
--Modify table structure
ALTERTableADD UNIQUEIndexName on(column(length))
--Specify directly when creating a table
CREATETABLE`Table` (
' ID ' int (11) notNULLAuto_increment,
' title ' char (255) CHARACTERSETUtf8COLLATEUtf8_general_ci notNULL,
' Content ' text CHARACTERSETUtf8COLLATEUtf8_general_ciNULL,
' Time ' int (10)NULLDEFAULTNULL,
PRIMARYKEY(' id '),
UNIQUEIndexName (Title (length))
);

3. Full-Text indexing (fulltext)

MySQL supports full-text and full-text indexing starting from version 3.23.23, fulltext indexes are available only for MyISAM tables; they can be created as part of a CREATE TABLE statement from char, varchar, or text columns, or subsequently using the ALTER TABLE or CREATE index is added. For larger datasets, enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into an existing Fulltext index. But remember, for a large data table, generating a full-text index is a very expensive way to consume hard disk space.

--Create tables that are suitable for adding full-text Indexes
CREATETABLE`Table` (
' ID ' int (11) notNULLAuto_increment,
' title ' char (255) CHARACTERSETUtf8COLLATEUtf8_general_ci notNULL,
' Content ' text CHARACTERSETUtf8COLLATEUtf8_general_ciNULL,
' Time ' int (10)NULLDEFAULTNULL,
PRIMARYKEY(' id '),
Fulltext (content)
);
--Modify table structure to add full-text index
ALTERTABLEArticleADDFulltext Index_content (content)
--Create indexes directly
CREATEFulltextINDEXIndex_content onArticle (content)

4. Single-column index, multicolumn index

Multiple single-column indexes differ from the query effect of a single multicolumn index because MySQL can use only one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes.

5. Combined index (leftmost prefix)

Usually use the SQL query statements generally have more restrictive conditions, so in order to further extract the efficiency of MySQL, we should consider the establishment of a composite index. For example, the previous table establishes a composite index for title and time: ALTER Table Article ADD index Index_titme_time (title (), Time (10)). Creating such a composite index is actually equivalent to establishing the following two sets of composite indexes:

--title,time

--title

Why is there no such combination index as time? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not all queries that contain these two columns will use the combined index, as shown in the following SQL:

--Use the index above
Select * from article whree title= "PHP programmer" and time=1234567890
Select * from article whree utitle= "PHP programmer"
--Do not use the index above
SELECT * from article Whree time=1234567890

Optimization of MySQL Index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. Therefore, the index also has its drawbacks: Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file. Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly. Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements. Here are some tips and optimizations for summarizing and collecting MySQL indexes.

1. When do I use a clustered or nonclustered index?

Action Description	using a clustered index	using nonclustered indexes
columns are often sorted by grouping	using	using
to return data in a range	use	do not use
One or very few different values	do not use	do not use
small number of different values	use	do not use
large number of different values	do not use	use
frequently updated columns	do not use	use
foreign key column	using	using
primary key column	using	using
frequently modifying indexed columns	do not use	use

In fact, we can understand the above table through examples of the previous clustered index and the definition of a nonclustered index. For example, to return data in a range. For example, if you have a table with a time column and you have the aggregate index in that column, you will be very fast when you query the entire data from January 1, 2004 to October 1, 2004, because the body of your dictionary is sorted by date, A clustered index only needs to find the beginning and end data in all the data to be retrieved, rather than a nonclustered index, you must first look up the page number for each item in the table of contents, and then find the specific content based on the page number. In fact, this specific usage I am not very understanding, can only wait for the later development of the project slowly learn.

2. The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

3. Using a short index

Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

4. Index column Sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

5. Like statement operations

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

6. Do not perform calculations on columns

For example: SELECT * from the users where year (adddate) <2007 will be performed on each row, which will cause the index to fail with a full table scan, so we can change to: SELECT * from Users where adddate < ' 2007-01-01 '.

Finally, MySQL uses the index only for the operator: <,<=,=,>,>=,between,in, and sometimes like (not in the case of a wildcard% or _). In theory each table can create up to 16 indexes, but unless the amount of data is really many, otherwise too much use of the index is not so fun, such as I just for the text type of the field to create an index, the system almost stuck.

MySQL uses indexes to optimize query efficiency