This is the 11th day of my participation in the August Wenwen Challenge.More challenges in August

preface

An index is a data structure stored in a file system to help MySQL query efficiently.

First, index type

  1. The primary key index: a primary key is a unique index usedprimary keySpecifies that a table can have only one primary key, even ifNot manually specified, MySQL will alsoThe default generated.
  2. The only index: All values of the index column can occur only once, but must be unique. It can be null.
  3. Composite index: Index of multiple columns for combined search.
  4. Normal index: Basic index type, which can be empty or not unique.
  5. The full text indexing: The fulltext index is of type fulltext and can be created on vARCHAR, CHAR, or text columns.

MyIasm and InnoDB choose B+ tree

For InnoDB engine, MySQL uses the data structure form of B+ tree, which is a variation of B tree. Compared with B-tree, the biggest difference is that all data nodes are stored in leaf nodes. In this case, non-leaf nodes can store more addresses pointing to the next page. By default, each page is 16Kb. If data is stored in a directory page, fewer addresses point to the next page, increasing disk I/o times, and deepening the depth. Therefore, the root reason for selecting a B+ tree is to reduce the tree depth and disk I/O times. Of course, another reason to choose a B+ tree is that the leaves are arranged in order from left to right.

Best left prefix rule, back table, overwrite index, index push down

1. The best left prefix rule

Suppose we add the combined index name,age to the table. SQL queries the data based on the name field and then the age field.

select * from user where name='zhang' and age = 10;  // Use the index
select * from user where name='zhang'; // Use the index
select * from user where age = 10 and name='zhang'; // Use the index. Because of the optimizer, the SQL is optimized to use the index.
select * from user where age = 10; // No index is used
select * from user where name like '%ang';// The index is not used
select * from user where name like 'zha%'; // Use the index to search according to the leftmost zha
Copy the code

2. Back to the table

Suppose we add an index to the name field of the table, executeselect * form where name = 'zhang';, we will search the leaf node in the name index tree, find the primary key ID of the corresponding data, and then search the corresponding data in the primary key index tree according to the primary key ID, and then return the data to the client. Six disk I/OS are performed.

3. Overwrite indexes

Select id form where name = ‘zhang’; According to the figure above, the corresponding ID can be directly found and returned to the client.

Select * from (username, age); select * from (username, age); Run select username, age from user where username = ‘Java’ and age = 22 to directly return the result to the client.

4. Index push down

For performingselect * from where name='zhang' and age=10, if there is no index push-down, we will query 100 pieces of data according to name to the server layer, and then the server layer filters 10 pieces of data according to age and returns them to the client. For index push-down, the final 10 records will be queried to the server layer based on name and age, and finally returned to the client.

Common cases where indexes are not used

  1. If there is an OR, all fields in or must be indexed
  2. Match Index does not match leftmost match
  3. Like starts with %
  4. Type conversion is required. For example, varchar does not have quotes
  5. Where index columns have operations
  6. Index columns in WHERE use functions
  7. If mysql feels that a full table scan is faster, do not use the index (less data).

Index optimization details

  1. Avoid expressions when querying with indexed columns, and put calculations in the business layer rather than the database layer.
  2. Try to use primary key queries rather than other indexes, so no back-table queries occur
  3. How to control index length when using prefix index
  4. Use index scan sort
  5. Union all, in, or can all use indexes, but in is recommended
  6. Range columns can use indexes
  7. A cast will scan the entire table
  8. Updates are frequent, and indexes should not be established on fields with low data differentiation
  9. Columns that create indexes are not allowed to be null, which may result in unexpected results
  10. When you need to join a table, it is best to have no more than three tables because the data types of the fields that need to be joined must be consistent
  11. Use limit whenever you can
  12. You are advised to limit the number of indexes in a single table to five
  13. The number of combined index fields cannot exceed five