[MySQL Advanced] In-depth analysis of MySQL architecture index

table of Contents

1. mysql in-depth analysis


  1. MySQL in-depth analysis: mysq|architecture system, mysql tuning, mysql index, index optimization, storage engine, B+ tree

1.1. mysql architecture analysis

MySQL architecture


Storage type

  1. Row storage
  2. Columnar storage


-- 查看搜索引擎
show engines;


  1. The query cache was cancelled after version 8.0; - Data that is not updated can be placed in the cache;

1.2. mysql storage engine

  • Optimization steps
  1. Connector
  2. Optimizer
  3. Index-the type of query viewed in explain type: const system ref range all;
  4. Columns of interest: key key_len rows
  5. extra column: using where; using index etc.;

2. Index

  • Index implementation
  • Classification etc.

2.1. MySQL Index System

2.1.1. Problems

  • problem:
  1. Is the index file in the file or in the memory?
    Indexes are not allowed to be stored persistently, except for storage engines such as memory;
  2. What information does the index need to return when reading data?
  3. Reflect on the IO, the number and magnitude of reads;
  4. The key value file name offset --hive uses this format;
  5. Why doesn't mysql use this format?
  6. expand:
  7. OLTP: online transaction processing: relational database: high timeliness;
  8. OLAP: online analytical processing: data warehouse: historical data analysis, have a decision-making impact;
  9. Because it's slow
  10. How does mysql design an index system?
  11. Data format: kv
  12. Data structure: hash tree
  13. What kind of data structure does mysql use, and why?
  14. hash b tree
  15. What data structure with the storage engine is relevant;
  16. Innodb, myisam uses a b-tree (essentially a B+ tree); at the same time, innodb supports adaptive hash; --cannot be controlled
  17. The memory storage engine uses a hash data structure;
  18. Why doesn't hash work?
  19. Need a good hash algorithm;
  20. The hash table requires a lot of memory;
  21. Not suitable for range query; Not suitable for business scenarios with a large number of queries;
  22. Can the tree work?
  23. bst avl red black tree B tree B+ tree
  24. Why use B+ tree?
  25. Binary trees need to be compared every time;
  26. BST tree: Binary search tree, the left and right sizes are ordered: missing : when increasing and decreasing, a linked list is formed, and the traversal query (n);
  27. avl tree: is a binary balanced tree: recalculate the root node to maintain the balance of the tree: lack : every time it is balanced, it leads to performance consumption;
  28. Red-black tree: It is also a binary balanced tree: its longest path/shortest path does not exceed twice, and the avl tree does not exceed 1. Lack : too much data, resulting in the tree height being too deep, increasing the number of I/O;
  29. B tree: store multiple records in one row; - set the value of Max.degree;
  30. Factors to consider:
  31. Locality principle: space and time, data programs are gathered and stored;
  32. Disk Read Ahead: interacting with the memory disk when there is a minimum logical unit, i.e. the page ; 4K or 8k; innodbDB default read 16K;
    show variables like %innodb_datapage_size%; --innodb_page_size 16384
-- 查看索引类型
    show index from table;

2.1.2. InnoDB index

InnoDB is a B + tree structure by the primary key index is created and stored in the leaf node records,

  1. If there is no primary key, then a unique key will be selected,
  2. If there is no unique key, a 6-byte row_id will be generated as the primary key;

If the key to create the index is another field, then the primary key of the record is stored in the leaf node .

  1. Then find the corresponding record through the primary key index, which is called the back table ;

Clustered index and non-clustered index

  1. Clustered index: data and index are put together;
  2. Is the clustered index of a table a primary key index? Not necessarily
  3. Will there be many clustered indexes for a table? Only one
  4. Is the primary key index necessarily a clustered index? If there is no primary key or unique key when the table is created, and the primary key is set after adding data, what will happen?
  5. innodb is primary key clustering, myisam is non-clustering;
  6. The ordinary column of innodb is non-clustered;
  7. Adding a primary key will re-establish the clustered index; a large amount of data will take a certain amount of time;
  8. It can be seen by parsing the ibd file;


  1. The index is not as many as possible, you should choose the appropriate field;

2.2. Index Classification

2.2.1. Classification


  1. Clustered index and non-clustered index


  1. Primary key index
  2. Unique index
  3. Common secondary index index index auxiliary ==> back to the table and index covering
  4. Full-text index
  5. Combined index

2.2.2. Index Analysis

  • Index classification

2.2.3. Index failure

  1. expression: id+1=7
  2. Function leads to
  3. Implicit conversion of data types: 111 和 '111'
  4. or and should be analyzed in detail, not necessarily leading to index failure; --explain analysis


  1. A combined index can be appropriately established to achieve index coverage and avoid returning to the table;

3. Log System