High performance mysql--Create high-performance indexes

An index is a data structure that the storage engine uses to quickly find records. Index optimization is the most effective means of optimizing query performance.

1. Type of index

In MySQL, indexes are implemented at the storage engine layer rather than at the server level. So without a unified indexing standard, the indexing of different storage engines does not work the same way.

B-tree Index

The B-tree index uses the B-TREE data structure to store the data. B-tree usually means that all values are stored sequentially and that each leaf page is the same distance from the root. The storage engine has different ways to use the B-tree index, and the performance varies.

You can use the query type of the B-tree index--full key value, key value range, and key prefix lookup. Where the key prefix lookup applies only to lookups based on the leftmost prefix.

B-tree Index valid query type:

• Full value matching--full value matching refers to matching all columns in the index. For example, for example, there is an index of key (Name,age), which can be used to find the person name=xxx and age=20;
• Match the leftmost front-the leftmost prefix uses only the first column of the index. For example: The index mentioned above, only use name=xxx;
• Match column prefixes--or you can match only the beginning of the value of a column. For example, the above index can be used to find all people with a surname beginning with J;
• Match range Values-for example: The above index can be used to find people whose surname is between Allen and Bootstrap;
• Match exactly one column and range to another--for example: first column full value match, second column range match;
• A query that accesses only the index--for example, an overlay index.

Because the index tree is ordered, it can also be used for sort lookups.

B-tree usage Limits for indexes:

• If you do not start the search by the leftmost column of the index, the index is invalidated. For example: Index key (name,age), the person who cannot find where age=20 and Name= ' Tom ';
• Columns in the index cannot be skipped. For example index key (name,age,address), if find where name= "xxx" and address= "xxx", then only the first column index can be used;
• If the query has a range query for a column, none of its right columns can use the index. For example: Index key (name,age,address), if the query condition where name like ' s% ' and age=20, this query can only use the first column of the index.

Hash index

Hash indexes are based on hash table implementations, and only queries that exactly match all columns of the index are valid. In MySQL, only the memory engine shows support for a hash index, which is also the default index type for the memory engine.

For each row of data, the storage engine computes a hash code for all indexed columns, and if multiple columns have the same hash code, the index holds multiple record pointers to the same hash entry in the form of a linked list. The hash index stores all the hash codes in the index, and a pointer to each row of data is saved in the hash table.

Hash Index usage Restrictions:

• Hash index values store hash values and row pointers without storing field values, so values in the index cannot be used to avoid reading row data;
• Hash index data is not stored in the order of index values, so it cannot be used for sorting;
• A hash index does not support partial indexed column matching queries because the hash index always computes the hash value using the entire contents of the indexed column;
• The hash index only supports equivalent comparison queries, including =, in (), <=>. does not support any scope query;
• The data accessing the hash index is very fast, unless a hash conflict occurs, and the storage engine needs to traverse all the row pointers in the linked list and compare rows by row;
• Some index maintenance operations can be costly if there are a lot of hash collisions.

The InnoDB engine has a special feature called "Adaptive Hash Index". When InnoDB notes that some index values are used very frequently, it creates a hash index on top of the B-tree index in memory, so that the B-tree index also has some features that are hash indexed. This is a spontaneous internal behavior that the user cannot control, but the user can turn off the secondary function.

Create a custom hash index

If the storage engine does not support a hash index to create a pseudo-hash index, you can do it yourself. Idea: Create a pseudo-hash index based on the B-tree index.

Spatial Data Index (slightly) Full-text index (slightly) other index categories (slightly) 2, the advantages of the index index of the three major advantages:

1. The index greatly reduces the amount of data that the server needs to scan;
2. Indexes can help the server avoid sorting and staging tables;
3. Indexes can turn random I/O into sequential I/O.

Samsung system--How to evaluate whether an index conforms to a query

The index gets a single star when the related records are put together, and two stars if the data order in the index and the sort order in the lookup are the same; if the columns in the index contain all the columns in the query, get Samsung.

3, High-performance indexing strategy

2. stand-alone columns --indexed columns cannot be part of an expression, nor can they be parameters of a function. For example: Select actor_id from actor where actor_id + 1 = 5, or select actor_id from actor where f (actor_id) = 5;
4. prefix index and index selectivity --Sometimes you need to index a long character column, which makes the index very large and slow. At this point there can be two policies, one is a custom hash index, the other is the prefix index;
   • The prefix index can greatly save the index space and improve the efficiency of the index, but this will also reduce the selectivity of the index (index selectivity-the ratio of non-duplicated index values to the total number of data table records);
   • The selection of index prefix length--the method of calculation. For example: Lelect count DISTINCT City/count (*) as Sel1, Count (DISTINCT left (city, 3))/count (*) as Sel2, ...; If the prefix is selectively close to sel1, it can be used. Sometimes it is not reliable to look at the average selection type, but also to make further judgments.
   • Cons: MySQL cannot use the prefix index for both order by and group by, nor can it use the prefix index for overwrite scanning;
   • You can also use a prefix index sometimes--you can store the string of the corresponding column in reverse order and create a prefix index.
6. Multi-column index --Create an appropriate index for multiple columns
   • Multi-column index. For example: Key (col1, col2, col3);
   • The version after MySQL5.0 introduces the strategy of "index merging", which can be used to locate the rows in the table by using multiple single-column indexes on the table to some extent;
   • The index merge policy is sometimes an optimized result, but it actually means that the indexes on the table are poorly built.
     • When a server intersects multiple indexes (multiple and), it usually means that a multi-column index with related columns is required, rather than multiple independent single-column indexes;
     • When a server needs to do a federated operation on multiple indexes (multiple or), it usually takes a lot of CPU and internal algorithms to cache, sort, and merge.
8. Select the appropriate index order
   • The correct index order depends on the query using the index, and at the same time need to consider how to better meet the needs of sorting and grouping;
   • Indexes can be scanned in ascending or descending order to meet the query requirements of order BY, GROUP by, and distinct clauses that accurately conform to the order of the columns;
   • Selection of index column order-the highest-selectivity column is placed at the top of the index (rule of thumb) without regard to grouping and sorting;
   • Avoid random I/O and sequencing;
   • For some special users and groupings, avoid using normal index queries.
10. Clustered Index -clustered index is not a separate index type, but a way of storing data
12. Overwrite Index
14. using index scanning to sort --mysql There are two ways to generate ordered results: By sorting, by index order.
    • MySQL can use the index to sort the results only if the column order of the index is exactly the same as the ORDER BY clause, and if all columns have the same sort direction (ascending/descending).
    • When a query needs to be associated with more than one table, the index sort can be used only if the fields referenced by the ORDER BY clause are all from the first table;
    • The fields in the ORDER BY clause need to satisfy the leftmost prefix of the index in order to use the index ordering;
    • When a leading column of an index is constant, the ORDER BY clause can also use index ordering if it does not satisfy the leftmost prefix requirement of the index. For example: Key (Rental_date, inventory_id, customer_id);.. where Rental_data= ' 2018-01-08 ' ORDER by inventory_id DESC;
16. Compression (prefix compression) index (abbreviated)

4. Maintain indexes and tables

Maintenance tables have three purposes: to locate and repair damaged tables, to maintain accurate index statistics, and to reduce fragmentation.

• Update index statistics--mysql's query optimizer uses two APIs to understand the distribution of the index values of the storage engine and determines how the index information is used. (1), Records_in_range (), (2), info (). If the index statistics provided by the storage engine to the optimizer are inaccurate, it will cause the optimizer to make the wrong optimization decision, which can seriously affect query performance. You can resolve this problem by executing analyze TABLE to regenerate the statistics.
• Reduce fragmentation of indexes and data
  • B-tree indexes can be fragmented, and fragmented indexes may be stored on disk in a poorly or unordered manner, which can reduce query efficiency;
  • The table data store may also be fragmented. There are three kinds of debris, line fragments and residual space fragments . For MyISAM tables, these three types of fragments can occur, but InnoDB does not appear to have short line fragments, InnoDB moves short lines and rewrites them into a fragment.
  • Maintenance methods can reorganize the data by executing potimize table or exporting and re-importing, and for engines that do not support the Potimize Table command, you can rebuild the table by performing an ALTER TABLE operation. You only need to change the storage engine of the table to the current engine. Example: ALTER TABLE <table> engine=<engine>;

High performance mysql--Create high-performance indexes