MySQL index and Query Optimization

MySQL index and Query Optimization

Index type:

Common indexes are the most basic index types and have no restrictions such as uniqueness.

The unique index is basically the same as the normal index, but all index column values are unique.

The primary key index primary key is a unique index, but must be specified as a "primary key ".

Ø full-text indexing MYSQL supports full-text indexing and full-text retrieval from 3.23.23. In MYSQL, the full-text index type is FULLTEXT. Full-TEXT indexes can be created on VARCHAR or TEXT columns.

Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored in the B tree.

The R-tree is used for spatial column indexes, and the MEMORY table supports hash indexes.

Single-Column index and multi-column index (Composite Index ):

An index can be a single-column index or multiple-column index. Using indexes on related columns is one of the best ways to improve the performance of SELECT operations.

MySQL can create indexes for multiple columns. An index can contain 15 columns. For some column types, You Can index the column's left prefix. The column order is very important.

Multi-column indexes can be considered as an array that contains the sorting of values created by connecting the values of the index column. In general, even if it is the most restrictive single-column index, its capacity is far lower than that of multiple-column indexes.

Multiple-column indexes have a feature, that is, the Leftmost prefix (Leftmost Prefixing ). If one index of multiple columns is key (firstname lastname age), MySQL uses this index when the search condition is the combination and sequence of the following columns:

Firstname, lastname, age

Firstname, lastname

Firstname

That is, key (firstname lastname) and key (firstname) are also created ).

Indexes are mainly used for the following operations:

Quickly find the rows matching a WHERE clause.

Delete rows. When a join is executed, the row is retrieved from other tables.

Locate the MAX () or MIN () value for the specific indexed column key_col. Optimized by the pre-processor to check whether the WHERE key_part _ # = constant is used for all keyword elements before key_col in the index. In this case, MySQL performs a keyword search for each MIN () or MAX () expression and replaces it with a constant. If all expressions are replaced with constants, the query returns immediately. For example:

Select min (key2), MAX (key2) FROM tb WHERE key1 = 10;

If the prefixes at the leftmost of an available keyword are sorted or grouped (for example, order by key_part_1, key_part_2), sort or group a table. If DESC is followed by all keyword elements, the keywords are read in reverse order.

In some cases, you can optimize a query so that you can retrieve values without querying data rows. If a query uses only the numeric type of a table and the leftmost prefix of some keywords, you can retrieve the value from the index tree for faster speed.

SELECT key_part3 FROM tb WHERE key_part1 = 1;

Sometimes MySQL does not use indexes, even if there are available indexes. One scenario is when the optimizer estimates that using indexes will require MySQL to access most rows in the table. (In this case, table scanning may be faster ). However, if this type of query uses LIMIT to search for only some rows, MySQL uses an index because it can locate several rows faster and return results. For example:

Reasonable suggestions for index creation:

(1) smaller data types are generally better: smaller data types usually require less space in the disk, memory, and CPU cache for faster processing.

(2) A simple data type is better: the processing overhead of integer data is smaller than that of characters, because the strings are more complex. In MySQL, the built-in Date and Time data types should be used instead of strings to store the time, and the IP addresses of integer data types should be used to store the time.

(3) Avoid NULL as much as possible: the column should be specified as not null unless you want to store NULL. In MySQL, it is difficult to query and optimize columns with null values, because they make the index and index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string.

These are some trivial suggestions and notes for indexing and writing SQL statements:

1. Use LIMIT 1 when the result set has only one row of data

2. Avoid SELECT * and always specify the columns you need

The more data you read from a table, the slower the query. It increases the disk operation time, or when the database server and the WEB server are independent. You will experience a very long network latency, just because the data is not required to be transmitted between servers.

3. Use JOIN instead of Sub-Queries)

JOIN... it is more efficient because MySQL does not need to create a temporary table in the memory to perform the query in two steps.

4. Use ENUM, CHAR instead of VARCHAR, and use reasonable field attribute length

5. Try to use NOT NULL

6. tables with a fixed length will be faster

7. Split large DELETE or INSERT statements

8. The smaller the queried column, the faster it is.

Where condition:

In a query, the WHERE condition is also an important factor. It is important to have as few and as few as possible where conditions as possible. When multiple conditions are met, put the conditions that extract as little data as possible before, reducing the query time of the next where condition.

Some where conditions will invalidate the index:

The query conditions of the where clause include! =, MySQL will not be able to use indexes.

Ø when the where clause uses the Mysql function, the index will be invalid. For example, select * from tb where left (name, 4) = 'xxx'

The index is valid when LIKE is used for search matching: select * from tbl1 where name like 'xxx % ', and the index is invalid when like' % xxx % '.

# The ideal of life is for ideal life! #

This article permanently updates the link address: