Indexing and query optimization

Type of index:

Ø Normal Index This is the most basic index type, no uniqueness, and so on.

Ø the uniqueness index and the normal index are basically the same, but all index column values remain unique.

Ø primary key Index primary key is a unique index, but must be specified as "PRIMARY key".

Ø full-text indexing MySQL supports full-text indexing and full-text retrieval from 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column.

Most MySQL indexes (PRIMARY KEY, UNIQUE, index, and fulltext) are stored using the B-tree.

Index of Spatial column type use R-Tree, memory table supports hash index.

Single-column indexes and multicolumn indexes (composite indexes):

The index can be a single-column index or a multicolumn 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 consist of 15 columns. For some column types, the left prefix of the column can be indexed, and the order of the columns is important.

A multicolumn index can be treated as an array of sorts that contains values created by connecting the values of indexed columns. In general, even the most restrictive single-column indexes can have a far less restrictive ability than multi-column indexes.

A multi-column index has one feature, the leftmost prefix (leftmost prefixing). If there is a multicolumn index of key (FirstName LastName age), MySQL will use that multicolumn index when the search condition is the combination and order of the following columns:

Firstname,lastname,age

Firstname,lastname

FirstName

In other words, the equivalent of the key (FirstName LastName) and Key (FirstName) is established.

Indexes are primarily used for the following operations:

Ø quickly find the row that matches a WHERE clause.

Ø Delete Row. When a join is performed, rows are retrieved from other tables.

Ø find the Max () or min () value for the column key_col that are indexed. is optimized by the preprocessor to check whether all keyword elements that occur before key_col in the index are used where key_part_# = constant. In this case, MySQL performs a keyword lookup for each min () or max () expression, replacing 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 leftmost prefix of a usable keyword is sorted or grouped (for example, order by key_part_1,key_part_2), sort or group a table. If all keyword elements are followed by DESC, the keyword is read in reverse order.

Ø in some cases, a query can be optimized so that values can be retrieved without querying the data rows. If the query uses only numeric types from a table and is the leftmost column that makes up some of the keywords, you can retrieve the values from the index tree for faster.

SELECT key_part3 from TB WHERE key_part1=1;

Sometimes MySQL does not use indexes, even if there is an index available. One scenario is when the optimizer estimates that using an index will require MySQL to access most of the rows in the table. (In this case, the table scan might be faster). However, if such a query uses limit to search only a subset of rows, MySQL uses the index because it can find several rows more quickly and return them in the results. For example:

Recommendations for proper index establishment:

(1) Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caches, and are processed faster.

(2) Simple data types are better: integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.

(3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

This section is a few trivial suggestions and points for attention when it comes to indexing and writing SQL statements:

1. Use limit 1 When the result set has only one row of data

2. Avoid select *, always specify the columns you need

The more data is read from the table, the more slowly the query becomes. He increases the time it takes for the disk to operate, or if the database server is separate from the Web server. You will experience a very long network delay, simply because the data is not required to be transferred between servers.

3. Use connection (join) instead of subquery (sub-queries)

Connect (Join): It is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query effort.

4. Using enum, CHAR instead of varchar, use reasonable field property length

5. Use not NULL where possible

6. Fixed-length tables are faster

7. Splitting a large delete or INSERT statement

8. The smaller the query column, the faster

Where Condition:

In the query, the Where condition is also a relatively important factor, as little as possible and is reasonable where condition is very important, as far as possible in multiple conditions, will extract as little data as possible in front of the conditions, reduce the next where the query time of the condition.

Some where conditions cause the index to be invalid:

Øwhere clause in the query conditions have! =,mysql will not be able to use the index.

Øwhere clause when using the MySQL function, the index will be invalid, for example: SELECT * from TB where left (name, 4) = ' xxx '

Ø when searching for matches using like, the index is valid: SELECT * from TBL1 where name as ' xxx% ', and the index is invalid if '%xxx% '

Indexing and query optimization