Shulou(Shulou.com)06/01 Report--

Indexing is to improve the efficiency of the query.

Common index models are hash index, ordered array, binary tree index.

Hash index: hash table is a data structure stored in key-value pairs, which is suitable for equal-value query scenarios. Because the data is not stored sequentially, the efficiency of range query is relatively low.

Ordered array: suitable for static storage engine, where the data will not change, the efficiency of equivalence query and range query is very good.

N-ary index: in order to make the query read the disk as little as possible, it is not suitable for binary tree, use N-binary tree.

INNODB uses the B+ index tree model, where tables are stored in the form of indexes in the order of primary keys, that is, indexes organize tables. The index is divided into primary key index and non-primary key index (secondary index).

The non-primary key index query data needs to return to the table, and the overlay index does not need to return to the table because the value to be queried already exists in the index, which reduces the search times of the tree and improves the performance.

B+ tree index structure, you can use the leftmost prefix principle to locate data.

When establishing federated indexes, pay attention to the order of fields so as to minimize the number of indexes to be maintained.

MySQL 5.6 introduces index push-down optimization. In the process of index traversal, the fields contained in the index are judged first, the records that do not meet the conditions are filtered, and the times of returning to the table are reduced.

Type of MySQL lock:

Global lock, table lock, row lock.

Global locks lock the entire instance of the database, flush table with read lock, used in logical backups.

Table-level locks: table and metadata locks

Table lock, lock tables * * read/write

Metadata lock

MySQL online table DDL operation, if there are uncommitted transactions, will also cause database congestion.

Add an index online:

Alter table test add index index_id (column1) algrithm=inplace

The following online ddl summary comes from the blog:

Https://www.cnblogs.com/beef/p/7376035.html

Https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

Online ddl mainly includes three stages: prepare stage, ddl implementation stage and commit stage. Rebuild has essentially one more ddl implementation stage than no-rebuild, and the prepare stage is similar to the commit stage. The following will focus on the process of the three stages of ddl execution.

3.1Phase of Prepare:

①: create a new temporary frm file (independent of InnoDB) ②: hold an EXCLUSIVE-MDL lock, disable reading and writing ③: determine the execution mode (copy,online-rebuild,online-norebuild) according to the alter type

If it is Add Index, then choose online-norebuild or INPLACE mode.

④: memory object for updating data dictionaries

⑤: assign row_log object record increments (required only by rebuild type) ⑥: generate a new temporary ibd file (required only by rebuild type)

3.2.The execution phase of ddl:

①: degrades the EXCLUSIVE-MDL lock to allow reading and writing

②: scan every record of old_table 's clustered index rec ③: traverse the new table's clustered index and secondary index, processing them one by one

④: construct the corresponding index entry according to rec

⑤: insert the construction index entry into the sort_buffer block sort ⑥: update the sort_buffer block to the new index ⑦: record the increments generated during the execution of the ddl (required by the rebuild type only) ⑧: replay the operations in the row_log to the new index (the no-rebuild data is updated on the original table) ⑨: generate the dml operation append between the playback row_log to the last Block of row_log

3. Commit stage:

①: disable reading and writing when the current Block is the last one in row_log, upgrade to EXCLUSIVE-MDL lock ②: redo the last part of row_log incremental ③: update innodb's data dictionary table ④: commit transaction (brush transaction redo log) ⑤: modify statistics ⑥: rename temporary idb file, frm file ⑦: change completed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.