table of Contents
- 1. mysql in-depth analysis
- 1.1. mysql architecture analysis
- 1.2. mysql storage engine
- 2. Index
- 2.1. MySQL Index System
- 2.1.1. Problems
- 2.1.2. InnoDB index
- 2.2. Index Classification
- 2.2.1. Classification
- 2.2.2. Index Analysis
- 2.2.3. Index failure
- 3. Log System
1. mysql in-depth analysis
- MySQL in-depth analysis: mysq|architecture system, mysql tuning, mysql index, index optimization, storage engine, B+ tree
1.1. mysql architecture analysis
- Row storage
- Columnar storage
-- 查看搜索引擎 show engines;
- 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
- Index-the type of query viewed in explain type: const system ref range all;
- Columns of interest: key key_len rows
- extra column: using where; using index etc.;
- Index implementation
- Classification etc.
2.1. MySQL Index System
- 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;
- What information does the index need to return when reading data?
- Reflect on the IO, the number and magnitude of reads;
- The key value file name offset --hive uses this format;
- Why doesn't mysql use this format?
- OLTP: online transaction processing: relational database: high timeliness;
- OLAP: online analytical processing: data warehouse: historical data analysis, have a decision-making impact;
- Because it's slow
- How does mysql design an index system?
- Data format: kv
- Data structure: hash tree
- What kind of data structure does mysql use, and why?
- hash b tree
- What data structure with the storage engine is relevant;
- Innodb, myisam uses a b-tree (essentially a B+ tree); at the same time, innodb supports adaptive hash; --cannot be controlled
- The memory storage engine uses a hash data structure;
- Why doesn't hash work?
- Need a good hash algorithm;
- The hash table requires a lot of memory;
- Not suitable for range query; Not suitable for business scenarios with a large number of queries;
- Can the tree work?
- bst avl red black tree B tree B+ tree
- Why use B+ tree?
- Binary trees need to be compared every time;
- 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);
- 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;
- 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;
- B tree: store multiple records in one row; - set the value of Max.degree;
- Factors to consider:
- Locality principle: space and time, data programs are gathered and stored;
- 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,
- If there is no primary key, then a unique key will be selected,
- 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 .
- Then find the corresponding record through the primary key index, which is called the back table ;
Clustered index and non-clustered index
- Clustered index: data and index are put together;
- Is the clustered index of a table a primary key index? Not necessarily
- Will there be many clustered indexes for a table? Only one
- 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?
- innodb is primary key clustering, myisam is non-clustering;
- The ordinary column of innodb is non-clustered;
- Adding a primary key will re-establish the clustered index; a large amount of data will take a certain amount of time;
- It can be seen by parsing the ibd file;
- The index is not as many as possible, you should choose the appropriate field;
2.2. Index Classification
- Clustered index and non-clustered index
- Primary key index
- Unique index
- Common secondary index index index auxiliary ==> back to the table and index covering
- Full-text index
- Combined index
2.2.2. Index Analysis
- Index classification
2.2.3. Index failure
- Function leads to
- Implicit conversion of data types:
111 和 '111'
- or and should be analyzed in detail, not necessarily leading to index failure; --explain analysis
- A combined index can be appropriately established to achieve index coverage and avoid returning to the table;