MySQL InnoDB Engine insider Reading Note 1

1. MySQL is a single-process multi-thread Architecture

2. view the engines supported by the current database

Show engines \ G;

3 InnoDB has 7 Background threads, 4 Io threads, 1 master thread, 1 lock, and 1 error monitoring thread.

Show engine InnoDB status \ G;

4 Memory composition of InnoDB: Buffer Pool, redo log buffer pool, and additional memory pool

Show variables like 'innodb _ buffer_pool_size '\ G; (buffer pool)

Show variables like 'innodb _ log_buffer_size '\ G; Redo the log buffer pool,

Show variables like 'innodb _ additional_mem_pool_size '\ G;

For details, refer to the buffer pool method:

Show engine InnoDB status \ G;

Buffer Pool and memory

----------------------

Total memory allocated 385277496; in additional P

Buffer pool size 21632

Free buffers 21543

Database pages 89

Modified dB pages 0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page

Pages read 89, created 0, written 0

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer poaol page gets since the last printout

The allocated memory is 21632 (buffer pool size indicates the number of buffer frames) x 16 (each buffer frame is 16 k)/1024 = 338 m, free table

Shows the number of idle instances. database pages indicates how many instances are used. Modified dB pages indicates the number of dirty pages

5. the working principle of the master thread is not: inserts the buffer every 10 seconds, instead of occurring every second. Instead, InnoDB judges the current IO pressure and determines the delay.

The proportion of dirty pages in the dashboard (buf_get_modified_ratio_pct). If there are more than 70% dirty pages, click 100 dirty pages to the disk. If the proportion is smaller than 70%

Refresh 10% to disk

6 double write buffer: consists of two parts: the double write buffer in the memory, the size is 2 MB, and the disk

The 128 consecutive pages in the shared tablespace, that is, two partitions, with the same size of 2 MB. When the dirty pages of the buffer pool are updated, the memcpy function is used instead of the disk.

Copy the data to the double write buffer in the memory, and then write the data 1 MB to the physical disk of the shared tablespace twice, and then call the fsync function.

, Synchronize disk.

Show global status like 'innodb _ dblwr % '\ G;

If innodb_dblwr_pages_writen: innodb_dblwr_writes is much less than 64: 1, the disk write pressure is low.

7. The parameter type in MySQL can be set to the current session or the entire cycle.

Set read_buffer_size = 524288;

Check Global Select @ Global. Read. read_buffer_size \ G;

Query the Session select @ sessionread. read_buffer_size \ G;

8. Slow query logs:

Note: Starting from 5.1, slow queries are calculated in milliseconds,

Show variables like '% long % ';

Check whether the switch is enabled

Show variables like 'long _ slow_queries ';

Show variable like 'long _ query_time ';

You can set a switch. When you switch on, if the SQL statement does not use the index, the statement is also recorded.

Show variable like 'long _ queries_not_using_indexs ';

View slow query log records:

Mysqldumpslow XXXX. Log

At the beginning of 5.1, you can put slow query log records into a table:

9 query log: records all database request information, which is stored in the host name. log file.

10 binary files:

Binlog_cache_size: All uncommitted binary logs are recorded in the cache. when the transaction is committed, it is written from the buffer to the binary day.

Default Value: 32 KB

In 5.1, The binlog_format parameter can be set with statement (logs are recorded in the traditional way) and row (records the changes of rows in the table ).

), If you set row, you can set the transaction isolation level of read commited for better performance, but the disk space increases a lot.

1) view binary log files

Mysqlbinlog-start-position = 203 xxxx.00004

You can see the SQL statement

If row is set, you must use

Mysqlbinlog -- VV -- start-position = xxx 222.004

11)

The default file of InnoDB can be set in innodb_data_file_path.

[Mysqld]

Innodb_data_file_path =/DB/indabat1: 2000 m;/DB2/InnoDB: autoextend

Two files are specified to form the tablespace. It is recommended that these two files be located on different disks.

12) Redo the log file and redo the log file group. Similar to Oracle, you can set multiple groups. Each group has two files.

Write another one.

How to view the redo log file group: Show variables like '% InnoDB % log %' \ G;

13) InnoDB tables are also composed of tablespaces, segments, zones, pages, and rows. Segments are composed of data segments, index segments, and rollback segments.

The partition consists of 64 consecutive pages, each page is 16 KB, and each partition is 1 MB. Each row can contain a maximum of 16 KB/2-7992 rows, that is, rows.

14) Format of compact row record

Variable Length Field Length list null flag record header information column 1 data column 2 data...

The null value does not occupy the storage space.

15) Note that the varchar65536 length in MySQL indicates the total length of all varchar columns. If the length exceeds the limit, it cannot be created.

16) in MySQL, for example, for a not null field, if you want to insert an invalid field, no error is prompted by default. If you want to prompt an error,

Set SQL _mode, that is:

Set SQL _mode = 'strict _ trans_tables ';

17 six triggers can be created for each table in MySQL 5.1. Currently, only the for each row trigger mode is supported.

18 MySQL 5. Partitions In 1

Range partition, list, hase partition, key partition, no matter which partition, when the table has a primary key or unique index, the partition column must be a unique index

.

1) range partitioning

Create Table T (id int) partition by range (ID)

(Partition P0 values less than (10), partition P1 values less than (20 ));

View partition information:

Select * From information_schema.partitions where table_schema = database () and tablename = 'T ';

Note that the optimizer can only optimize year (), to_days (), to_second (), and unix_timestamp.

Year (date) * 100 + month (date) will not be optimized.



2) List partitions

Discrete value.

3) hash Partition

Partition by hash (Year (B ))

4) in MySQL 5.5, the column partition is newly added, which may not always be integer data as before,

For example

Partition P0 values less than ('2017-01-01 ');

Partitioning is better on OLAP. If it is on OLTP, it will be slower unless the primary key is queried,

Possible.

19) Binary Search Tree: The left subtree is smaller than the root, and the right subtree is larger than the root;

Balanced Binary Tree: the maximum height difference between left and right subtree of any node is 1.

In the B + tree, rotation is generally used instead of splitting to increase efficiency.

Both clustered index and secondary index are in the internal B +;

Clustered index: the leaf node stores the Row Records of the entire table. The leaf node of the clustered index is the final data node, not the leaf node of the clustered index.

It is still an index node, but it has a pointer to the final data.

20) at the beginning of InnoDB plugin, the index creation method is supported, but it only works for secondary indexes. You still need to re-create and delete primary keys.

If a table is used for secondary indexing, the S lock is applied. view the indexing method.

Show index from Table Name

Meaning of each column:

Non_unique: Non-unique index

Key_name: Index name

Seq_in_index: Position of the column in the Index

Column_name: Index Column

Collation: How columns are stored. B + tree indexes are always.

Cardinality: the estimated value of the unique number in the index. If it is very small, consider whether to create an index.

Sub_part: whether the part of the column is indexed. If the whole column is indexed, the field is null.

Packed: indicates how the keyword is compressed. If the keyword is not compressed, It is null.

Null: whether the index column contains a null value,

Index_type: Index type, which is B + in InnoDB

Cardinality analysis is not accurate. You need to use analyze tables to perform multiple analyses, and the values are accurate.