MySQL技术内幕

MySQL技术内幕

1 后台线程

Master Thread

主要负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，包括脏页的刷新，合并插入缓冲区（insert bufffer）,undo页的回收等。

Purge Thread

回收已经使用并分配的undo页，innodb_purge_threads=4

Page Cleaner Thread

将脏页的刷新操作放到单独的线程中来完成，减轻Master thread的负担，提高性能

2 缓冲池(innodb_buffer_pool)

• 不包含重做日志Redo Log
• 页的修改操作，首先修改缓冲池中的页，再以一定机制/频率刷回磁盘。（checkpoint）
• 包含：数据页，索引页，锁信息，插入缓冲，自适应哈希索引等
• 页的大小默认为16KB，使用LRU算法对缓冲池进行管理
• 插入缓冲Insert Buffer
  对于非聚集索引的插入或更新，由于数据不在一个索引页，所以效率底
  先放入insert buffer，再以一定频率合并操作，通常在一个索引页

innodb_fast_shutdown=0,默认值是1.在升级之前，设置为0，完成所有的full purge和merge insert buffer,并且将所有脏页刷新到磁盘。可以理解为干净的关闭数据库。

sync_binlog=N 表示每写缓冲多少次就同步到磁盘。推荐为1.当主从延迟很大是可设置为2000,用于降低延迟。之后再次恢复为1

PID 文件，启动时，会将进程ID写入文件。参数为pid_file

重做日志与二进制日志的区别：

• 二进制日志会记录所有与mysql数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志，而InnoDB存储引擎的重做日志只记录有关其本身的事务日志，

• 记录的内容不同，不管你将二进制日志文件记录的格式设为哪一种，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志；而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况；

• 写入的时间也不同，二进制日志文件是在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大；而在事务进行的过程中，不断有重做日志条目被写入重做日志文件中。

3 表

在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：

• 首先判断表中是否存在非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键；
• 如果不符合上述条件，InnoDB存储引擎会自动创建一个6字节大小的指针；

表空间：undo信息，插入缓冲索引页，系统事务信息，二次写缓冲等还是在共享表空间中。

B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。然后把页读入内存，再在内存中查找需要的数据

show index from table_name\G

其中Cardinalitty值很关键，优化器会根据这个值来判断是否使用索引。但是值不是实时的。大概和主键值一样，可以手动更新。analyze table table_name\G（非高峰时期）

Online DDL 原理:在执行创建和删除操作的同时，将insert update delete DML操作日志写入一个缓存中。待完成索引创建再将重做应用到表上。缓存大小由innodb_online_alter_log_max_size控制，默认128M.

如果表很大，需要调高这个参数。或是使用share模式，禁用写操作。

执行期间，索引不生效。

执行计划，在Extra 出现Using index 表示为覆盖索引

Using index 是告诉优化器可以使用索引，最终是否使用也不一定。可以通过force index 对比

MRR 目的是为了减少磁盘的随机访问。

ICP 在读取索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层。

缓冲池命中率不应该小于99%

• show engine innodb status\G;

  Per second averages calculated from the last 6 seconds    #以下信息是最近6秒的平均值
  Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 314 / 1000      #缓冲池命中率
  结果：最近6秒缓冲池命中率是99.8%
  

show global status like ‘innodb%read%’\G;

+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 839015      |               #预读次数
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 1497697965  |           #从缓冲池中读取页的次数
| Innodb_buffer_pool_reads              | 1227641     |                  #从物理磁盘读取页的次数
| Innodb_data_pending_reads             | 0           |
| Innodb_data_read                      | 34861846528 |                  #总共读入的字节数
| Innodb_data_reads                     | 2128711     |                     #总共读取的次数
| Innodb_master_thread_active_loops     | 63667       |
| Innodb_master_thread_idle_loops       | 24896       |
| Innodb_pages_read                     | 2127692     |
| Innodb_rows_read                      | 803908452   |
| Innodb_read_views_memory              | 2176        |
+---------------------------------------+-------------+
13 rows in set (0.00 sec)

缓冲池命中率= Innodb_buffer_pool_read_requests/（Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_read_ahead）=1497697965/(1497697965+1227641+839015)=99.85%
平均读取的字节数=Innodb_data_read/ Innodb_data_reads=16376