Detailed explanation of the use of various MySQL indexes

1. Slow query log

1.1 MySQL log types

Logs are used to record the operation of the database and various operations performed by users on the database. When a database failure occurs, the problem can be analyzed and solved based on the logs , thereby restoring the database.

1.2 Understanding Slow Query Logs

The slow query log is used to record statements in the MySQL database whose response time exceeds the specified threshold . The slow query log is also often referred to as a slow log because it targets not only SELECT statements, but also statements such as INSERT、UPDATE、DELETE . As long as the response time exceeds the set threshold, it will be recorded in the slow query log.

1.3 How to enable slow query log command

The slow query log can be set temporarily through commands or permanently by modifying the configuration file.

Check whether the slow query log is enabled

show variables like 'slow%';

Temporarily enable slow query log

set slow_query_log='ON';
set long_query_time=1;

Slow query log file location

show variables like '%datadir%';

2. Query Analyzer - EXPLAIN

2.1 Introduction to explain

The explain command can view the execution plan of the SQL statement. When explain is used with a SQL statement, MySQL displays information from the optimizer about the statement's execution plan. That is, MySQL explains how it will process the statement , including information about how to join the tables and in what order.

What can explain do?

• Analyze the reading order of the table
• Operation type of data read operation
• Which indexes can be used
• Which indexes are actually used?
• References between tables
• How many rows of each table are queried by the optimizer

2.2 Use of explain

The use of explain is very simple. You only need to add the explain command before the SQL statement. In addition to select statements, explain can also analyze insert、update和delete statements.

Command Explanation:

3. Basic use of index

3.1 What is an index?

An index is a special data structure , similar to a book catalog, which can greatly improve the query efficiency of the database. If there is no index, when querying data, all records in the table must be scanned to find records that meet the conditions. This full table scan query efficiency is very low .

Summary : Improving query efficiency is like sorting garbage. Put things with the same effect together so that they are easier to find.

3.2 Common Index Types

An index is a structure that sorts the values ​​of one or more columns in a database table. An index can be used to quickly access specific records in a database table.

The index of a database is like the table of contents of a book, which can speed up the query of the database. The index is the key to fast search. If there is no index, a full table scan will be performed to find any specific data.

3.3 Use of Index

Create Index

Creating a normal index

CREATE INDEX indexName ON tableName(columnName(length));

Creating a unique index

CREATE UNIQUE INDEX indexName ON tableName(columnName(length));

Creating a composite index

CREATE INDEX indexName ON tableName(columnName1, columnName2, …);

Deleting an Index

DROP INDEX [indexName] ON tableName;

View Index

SHOW INDEX FROM tableName;

3.4 Practical experience with indexing

IV. Composite index leading column characteristics

Composite index leading column feature : In MySQL, if you create a composite index (name, salary, dept) , it is equivalent to creating three indexes: (name, salary, dept), (name, salary), and (name). Therefore, when creating a composite index, you should put the column that is most often used as a query condition on the left , in descending order .

List:

Index not used

select * from employee where salary=8800;
select * from employee where dept='Department A';
select * from employee where salary=8800 and dept='Department A';

Use index : preceded by name

select * from employee where name='liufeng';
select * from employee where name='liufeng' and salary=8800;
select * from employee where name='liufeng' and salary=8800 and dept='Department A';

5. Covering Index

5.1 What is a covering index?

A covering index is also called an index coverage , which means that the select data columns can be obtained only from the index without reading the data rows, that is, the query results can be obtained by scanning the index.

A few notes about covering indexes :

1. With a covering index, you can retrieve the required data from the index without scanning the data table;
2. The size of an index is often much smaller than that of a data table, so reading only the index will be very fast and will greatly reduce the amount of data access.
3. MySQL's query optimizer will determine whether there is an index that can cover all query columns before executing the query;
4. Not all types of indexes can be used as covering indexes. Covering indexes must store the values ​​of the indexed columns. Like hash index, spatial index, full
5. Text indexes do not actually store the values ​​of the indexed columns.

5.2 How to determine whether a covering index is used

When a query uses a covering index, you can see “Using index” in the Extra column of the query analyzer EXPLAIN .

This concludes this article on the detailed usage of various MySQL indexes. For more information on the usage of MySQL indexes, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

My blog: https://blog.csdn.net/weixin_46654114