“This is the sixth day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.”

1. The role of indexes

  • Similar to the table of contents in a book, to optimize the role of queries

2. Index classification (algorithm)

  • BTree

    • Default index type

  • R tree

  • Hash

  • FullText

  • GIS

    • Geographic index

3. BTree Index algorithm evolution (Understanding)

B tree -> B+ tree -> B* tree

4. Classification on the BTree index function

4.1 Auxiliary Indexes

How to generate:

  1. Extract all values of the index column and sort them;
  2. The sorted values are evenly stored in leaf nodes to further generate branch nodes and root nodes.
  3. The value in the leaf node corresponds to the primary key ID stored;

4.2 Clustered Indexes

How to generate:

  1. MySQL will automatically select the primary key as the clustered index column. If no primary key is selected, the unique key will be selected.
  2. When MySQL stores data, it stores data rows in the order of clustered index column values.
  3. The clustered index directly takes the original table data page as the leaf node, and then extracts the clustered index column upward to generate branches and roots;

4.3 Differences between clustered indexes and secondary indexes

  1. Secondary indexes can be created for any column in the table, as long as you have a different name.
  2. In a table, there can be only one clustered index, usually a primary key.
  3. The secondary primary index leaves only store the ordered index column + the clustered index column value;
  4. Clustered indexes, leaf nodes store ordered whole rows of data;
  5. MySQL’s table data store is organized by clustered indexes;

5. Assist index subdivision

5.1 Single-column secondary index

5.2 Joint Index (Overwrite Index)

5.3 Unique Index

6. Height of the index tree

Index tree height should be as low as possible, generally maintained at 3-4 optimal.

6.1 Too many Data Rows

  • Part list: Part list (less used at present)
  • Sharded and distributed architecture

6.2 Field Length

  1. If services allow, select columns with short character length as index columns
  2. Services are not allowed. The prefix index is used

6.3 Data Types

  • Char or varchar
  • enum

7. Perform index operations

7.1 Querying an Index

Use the desc table command to view the index:

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+
| id     | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname  | varchar(64)         | NO   |     | NULL              |                |
| age    | tinyint(3) unsigned | NO   |     | 0                 |                |
| sex    | char(4)             | NO   |     | NULL              |                |
| intime | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.01 sec)
Copy the code
  • PRI: primary key index
  • MUL: secondary index
  • UNI: Unique index

You can also use the show index from table command to view the index:

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
1 row in set (0.00 sec)
Copy the code

7.2 Creating an Index

-- Single column auxiliary index
mysql> alter table student add index idx_name(sname);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- Joint index
mysql> alter table student add indexidx_name_sex(sname,sex); Query OK, 0 rows affected (0.05sec) Records: 0 Duplicates: 0 Warnings: 0Create unique index
mysql> alter table student add unique index idx_name_unique(sname);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Create prefix index
mysql> alter table student add index idx_name_pre(sname(5));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
Copy the code

7.3 Deleting an Index

mysql> alter table student drop indexidx_name_pre; Query OK, 0 rows affected (0.02sec) Records: 0 Duplicates: 0 Warnings: 0Copy the code

8. Execute the plan

8.1 role

The execution plan selected by the optimizer is truncated for the convenience of managing and judging the execution efficiency of statements.

8.2 Obtaining Execution

  • Desc SQL statements
  • The explain SQL statements
mysql> explain select * from student where id > 3;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------- ----+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------- ----+| | SIMPLE | 1 student | NULL | range | PRIMARY | PRIMARY 4 | | NULL | 1 | | 100.00 Using the where | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------- ----+
1 row in set.1 warning (0.00 sec)
Copy the code

8.3 Analyzing the Execution Plan

8.3.1 table

The name of the table

8.3.2 type

Query type:

  1. Full table scan: ALL
  2. Index, range, ref, eq_ref, const(system), NULL
  • Index: full index scan
  • <, >, <=, >=, between and, or, in, like)
  • Ref: Auxiliary index equivalent query
  • Eq_ref: When multiple tables are joined, the word table uses the primary key or unique column as the join condition
  • Const (system) : equivalent queries for primary key indexes or unique indexes

8.3.3 Possible_keys: possible index

8.3.4 Keys: the actual selected index

8.3.5 KEY_len: index override length

Utf8mb4 varchar(10) => Can store 10 Chinese, or 10 English, or 10 digits, up to 40 bytes.

varchar(20) utf8mb4

  1. Can store 20 arbitrary characters
  2. Regardless of the stored characters, digits, or Chinese characters, the reserved character is 1. The maximum reserved length is 4 bytes
  3. For Chinese, one is 4 bytes
  4. For numbers and letters, 1 actual occupancy size is 1 byte.
  5. select length() from table_name;

Key_len Length calculation

Character set UTF8MB4:

  • Varchar (10) : not null(1) + 4 * 10 + 2 (before and after start, end) = 43
  • Not null(1) + 4 * 10 = 41
  • Int: not null(1) + 4 = 4

Purpose: To determine the coverage length of a federated index. Generally, the longer the better

8.3.6 Extra

Using filesort

Using filesort indicates that there is no proper index applied to the sorted condition column in the query. For example:

  • order by
  • group by
  • distinct
  • union

Focus on the key_len length

9. Explain (desc) Usage Scenarios (Interview Questions)

  • What optimizations have you made?
  • What optimization tools have you used?
  • How did you optimize the index section?
  1. MySQL has a performance problem. There are two cases:

    1. Slow response: suddenly tamping;

      1. Processing process:
        1. Show the processlist. Gets the statement that causes database hang
        2. Explain analyzes the SQL execution plan, whether there is an index, and the type of the index
        3. Create an index, change a statement

    2. Slow (persistent) for a period of time

      1. Record the slowlog and analyze the slowlog
      2. Explain analyzes the SQL execution plan, whether there is an index, and the type of the index
      3. Create an index, change a statement

10. Index application specification

10.1 Principles for Creating indexes

  1. Create table must have primary key, usually independent column, self – growing
  2. Often used as a WHERE condition column, order by, group BY, join on, or distinct condition
  3. It is best to use columns with more unique values as the leading column of the joint index, and do the rest according to the joint index optimization details
  4. For indexed columns with long column values, we recommend using prefix indexes
  5. Reduce index entries, on the one hand, do not create useless indexes, rarely used index cleanup
  6. Index maintenance should avoid busy periods
  7. Small tables do not have indexes

10.2 No Index Is used

  1. No query condition or no index is created for the query condition
  2. The query result set is the majority of the data in the original table, should be more than 25%.
  3. The index itself is invalid and the statistics are not true
  4. Query criteria use functions on or against indexed columns. Operators include (+, -, *, /), and so on
  5. An implicit conversion invalidates the index.
  6. <>, not in
  7. %aa% % % % % % %
  8. Joint index

T1 (id,k1,k2,k3) IDx (K1,k2,k3)

  1. It’s equivalent in the WHERE condition where k1=xx, k2=xx, k3=xx

    1. The order of where conditions is irrelevant, as long as the control index, you need to put more unique values in the left

  2. When there is no left-most column condition in the conditional query, the query without k1 column is not indexed

  3. If the query appears (> < >= <= like)

    1. a= and b<xxx and c=xxx
    2. When creating an index, put the equivalent query conditions forward and the non-equivalent query conditions last

  4. Where c XXX order by a, B

    1. idx(c,a,b)