Using index, explain, and profile to optimize mysql database query Summary

I'm sure you have made a lot of use of index, explain, and profile. This is some of the content I have optimized mysql statement query data over the last two days. I hope you can add it together.

Index usage:

1. It is best to compare fields of the same type. Before MySQL 3.23, this is even a required condition. For example, you cannot compare an indexed INT field with a BIGINT field. However, in special cases, when a CHAR field is of the same size as a VARCHAR field, you can compare them.

2. Do not use functions to operate indexed fields or perform operations in the database.

3. Avoid negative query and % prefix fuzzy query. You can use indexes for suffix like 'xxx %.

4. Do not query data in the form of select * from in the production environment program. Only query the columns to be used.

5. Use limit to reduce the number of returned rows and reduce data transmission time and bandwidth waste.

6. No index is available for the query column using functions.

7. Avoid implicit type conversion. For example, the numeric type must use '', And the numeric type must not use ''.

8. All SQL keywords should be capitalized to form good habits and avoid wasting system resources due to repeated compilation of SQL statements.

9. When querying a table, remember to put the small result set in front and follow the small structure and the principle of driving the big result set. This article is very important!

10. Enable slow query and regularly use explain to optimize SQL statements in slow query.

11. Any operation on the column will cause the table to scan, including database functions and calculation expressions. During the query, try to move the operation to the right of the equal sign.

12. IN And OR clauses often use worksheets to invalidate indexes. If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.

13. an index does not contain a column with a NULL value. As long as the column contains a NULL value, it is not included in the index. If a column in a composite index contains a NULL value, this column is invalid for this compound index. Therefore, do not set the default value of a field to NULL during database design.

14. Use a short index to index string columns. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

15. For sorting indexes, mysql queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.

 

Under what circumstances should I create no or less indexes?

1. Too few table records

If a table has only five records and uses indexes to access the records, you must first access the index table and then access the data table through the index table. Generally, the index table and the data table are not in the same data block, in this case, ORACLE must read data blocks at least twice. ORACLE reads all the data once without an index, and the processing speed is obviously faster than that with an index. For example, the table zl_sybm (Department used) generally has only a few records, and no performance optimization will be produced for indexing any field except the primary keyword, in fact, if you perform statistical analysis on this table, ORACLE will not use the index you created, but will automatically perform full table access. For example, select * from zl_sybm where sydw_bh = '000000' (index creation on sydw_bh does not produce performance optimization) frequently inserted, deleted, and modified tables

For business tables that are frequently processed, minimize the number of indexes allowed by queries, such as zl_yhbm, gc_dfss, gc_dfys, and gc_fpdy.

 

2. Duplicate and evenly distributed table fields

Assume that A table has 0.1 million rows of records and one field A has only T and F values, and the distribution probability of each value is about 50%, creating an index for this table field A generally does not increase the database query speed. Table fields that are frequently queried with the primary field but have a large index value (such as gc_dfss) the table often queries a collection based on the billing serial number, user ID number, meter reading date, electricity generation date, and Operation mark, if all fields are created in one index, the data modification, insertion, and deletion time will be increased, from the actual analysis of a collection, if the index by the billing serial number has been reduced to only a few records, and then by the following field index query will not have a big impact on performance. Index for tens of millions of MySQL Databases and methods to improve performance.

 

MySQL indexes include:

 

1. Common Index

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

◆ Create an index

Create index indexName ON mytable (username (length); for CHAR and VARCHAR types, length can be smaller than the actual length of the field; For BLOB and TEXT types, length must be specified, the same below.

◆ Modify Table Structure

ALTER mytable add index [indexName] ON (username (length) ◆ specify

Create table mytable (id int not null, username VARCHAR (16) not null, INDEX [indexName] (username (length); syntax for deleting indexes:

Drop index [indexName] ON mytable;

 

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:

◆ Create an index

Create unique index indexName ON mytable (username (length) ◆ modify the table structure

ALTER mytable add unique [indexName] ON (username (length) ◆ specify

Create table mytable (id int not null, username VARCHAR (16) not null, UNIQUE [indexName] (username (length )));

 

3. Primary Key Index

It is a special unique index and does not allow null values. Generally, when creating a table, you can create a primary key index at the same time:

Create table mytable (id int not null, username VARCHAR (16) not null, primary key (ID); of course, you can also use the ALTER command. Remember: A table can only have one primary key.

4. combined index

To visually compare Single-Column indexes and composite indexes, add multiple fields to the table:

Create table mytable (id int not null, username VARCHAR (16) not null, city VARCHAR (50) not null, age int not null); to further extract MySQL efficiency, you must consider creating a composite index. Create name, city, and age in an index:

Alter table mytable add index name_city_age (username (10), city, age); usernname is 16 in length during TABLE creation, and 10 is used here. This is because the name length generally does not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.

If a single column index is created on usernname, city, and age respectively, the table has three single column indexes, and the query efficiency will be significantly different from that of the preceding composite indexes, 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 of the following three composite indexes:

Why does usernname, city, age usernname, and city usernname have no combined indexes such as city and age? This is because MySQL Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. This composite index is not used for queries that contain these three columns. The following SQL statements use this composite index:

SELECT * FROM mytable WHREE username = "admin" AND city = "Zhengzhou" SELECT * FROM mytable WHREE username = "admin" AND the following are not used:

SELECT * FROM mytable WHREE age = 20 AND city = "Zhengzhou" SELECT * FROM mytable WHREE city = "Zhengzhou"

 

Index creation time

Now we have learned how to create an index. Under what circumstances do we need to create an index?

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 will use the index. For example:

SELECT t. name FROM mytable t left join mytable m ON t. name = m. username WHERE m. age = 20 AND m. city = 'zhengzhou 'at this time, you need to create an index for the city and age, because the userame of the mytable table also appears in the JOIN clause, it is also necessary to create an index for it.

 

Index Deficiency

The advantages of using indexes are described above, but excessive use of indexes will cause abuse. Therefore, the index also has its 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.

Indexes are only a factor to improve efficiency. If your MySQL database has a large data volume of tables, you need to spend time researching and creating the best indexes or optimizing query statements.

 

Common simple optimization operations:

Set profiling = 1;

Expain (query statement );

Show profiles;

Show profile for query (query statement id );

Create index aIndex on tablename (columnname );

According to the explain command, the mysql internal query steps and specific parameters are detected to optimize the select statement.

 

(There are not many studies on mysql, and there are many places that need to be supplemented and corrected. I hope that my brothers and sisters can have a lot of discussions and learn together ...)

 

Reprinted please indicate the source: Edward_jie, http://www.cnblogs.com/promise-7/archive/2012/05/25/2517356.html