MySQL三板斧 - MySQL概述

1、MySQL概述

1.1、MySQL体系结构

---

• Connectors：连接MySQL服务的常用工具集
• Management Serveices & Utilities：系统管理和控制工具， 如：备份恢复、MySQL复制、集群等
• Connection Pool：连接池，管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
• SQL Interface：接收用户的SQL命令，并且返回用户需要查询的结果。
• Parser（解析器）：SQL命令验证和解析，将SQL语句分解成数据结构，并将这个接口传递到后续的步骤
• Optimizer（查询优化器）：SQL语句在查询之前会经过查询优化器对查询进行优化。

// 使用“选取-投影-联接” 的策略
例如：select a, b from table where id = 1;
1. 根据where条件进行选取， 而不是先将表中数据全部查询出来以后再过滤
2. 根据a, b进行属性投影， 而不是将属性（列）全部取出来以后再过滤
3. 将1和2两个联接起来生成最终查询结果

• Cache & Buffer （高速缓存区）：查询缓存， 如果查询缓存有命中的查询结果， 查询语句就直接从缓存中取数据。 使用LRU算法淘汰。 由一系列的小缓存组成，如：表缓存、记录缓存、KEY缓存、权限缓存等
• Engine（存储引擎）：

1.2、 MySQL内存结构

---

下图中，左边是MySQL缓存， 中间是操作系统缓存， 右边是磁盘

1.2.1 全局内存 Global Buffer

innodb_buffer_pool_size

• InnoDB 高速缓冲data和索引，简称IBP，这个是Innodb引擎中影响性能最大的参数。建议将IBP设置的大一些，单实例下，建议设置为可用RAM的50%~80%。

• InnoDB 不依赖OS，而是自己缓存了所有数据，包括索引数据、行数据等等，这个和MyISAM有差别。

• IBP有一块buffer用于插入缓冲，在插入时，先写入内存之后再合并后顺序写入磁盘；在合并到磁盘的时候会引发较大的IO操作，对实际操作造成影响。（看上去的表现是抖动，TPS变低）

• InnoDB会定时（约每10秒）将脏页刷新到磁盘，默认每次刷新10页；要是脏页超过了指定数量（innodb_max_dirty_pages_pct），InnoDB则会每秒刷100页脏页

• innodb_buffer_pool_instances可以设置pool的数量

• show engine innodb status\G 可以查看innodb引擎状态

// 查看IBP状态，单位是page(16kb),其中，Innodb_buffer_pool_wait_free 如果较大，需要加大IBP设置
show global status like 'innodb_buffer_pool_%' ;

---

innodb_additional_mem_pool_size

指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是8M（8388608）。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息

innodb_log_buffer_size

innodb redo日志缓冲，提高redo写入效率。如果表操作中包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请尽量调高此项值，以提高日志效率。

默认8M，一般设置为16 ~ 64M足够了

show global status 
- 查看 Innodb_log_waits 是否大于0，是的话，就需要提高 innodb_log_buffer_size，否则维持原样。
- 查看30~60秒钟 Innodb_os_log_written 的间隔差异值，即可计算出 innodb_log_buffer_size 设置多大合适。

---

key_buffer_size

• MyISAM 引擎中表的索引 的缓存大小，默认值=16M；
• 单个key_buffer_size最大只有4G（32-bit系统下最大4G，64-bit下可以超过）
• 若主要使用 MyISAM 存储引擎，则设置最高不超过物理内存的20%~50%，
• 即便全是innodb表,没用MyISAM，也有必要设置key_buffer_size用于缓存临时表的索引，推荐设置32MB
• 关于临时表，如果内存tmp_table_size（Created_tmp_tables）不够的话，内部的临时磁盘表是MyISAM表（Created_tmp_disk_tables）。

show global status like 'Create%'; 
show variables like 'tmp%';

query_cache_size

查询高速缓冲，缓存结果，减少硬解析（建议关闭，如果真需要查询缓存可以借助redis等缓存）。

MySQL8中已经被移除了

table_definition_cache

表定义文件描述缓存，提高表打开效率。是frm文件在内存中的映射。MySQL需要打开frm文件，并将其内容初始化为Table Share 对象。这里存放与存储引擎无关的，独立的表定义相关信息。

table_open_cache

• 表空间文件描述缓冲，提高表打开效率。
• 增加table_open_cache，会增加文件描述符（ulimit -a查看系统的文件描述符），当把table_open_cache设置的过大时，如果系统处理不了这么多文件描述符，那么就会出现客户端失效、连接不上。
• 也就是平时说的table cache。存放当前已经打开的表句柄，与表创建时指定的存储引擎相关。请注意和table_define_cache参数的区别。

max_heap_table_size和tmp_table_size

• max_heap_table_size 参数：定义了MEMORY、HEAP表的最大容量，如果内存不够，则不允许写入数据
• tmp_table_size参数：规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下。
• 优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的，否则临时表超过内存临时表的限制，会自动转化为基于磁盘的MyIsam表。

1.2.2 线程内存 Thread Buffer

每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。
事务开始之后，则需要增加更多的空间。

运行较小的查询可能仅给指定的线程增加少量的内存消耗。

如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约read_buffer_size、sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的内存空间
不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。

read_buffer_size

是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。
read_buffer_size变量控制这一缓冲区的大小。
如果对表的顺序扫描请求非常频繁， 并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

read_rnd_buffer_size

是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。
进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。
但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

sort_buffer_size

是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。
如果不能，可以尝试增加sort_buffer_size变量的大小

join_buffer_size

应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的"被驱动表"的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。

当Join Buffer太小，MySQL不会将该Buffer存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join的表进行 Join操作，
然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，
如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。

binlog_cache_size

在事务过程中容纳二进制日志SQL 语句的缓存大小。

二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存，注意，是每个Client 都可以分配设置大小的binlog cache 空间。

如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。

当然，我们可以通过MySQL 的以下两个状态变量来判断当前的binlog_cache_size 的状况：Binlog_cache_use 和Binlog_cache_disk_use。

"max_binlog_cache_size"：和"binlog_cache_size"相对应，但是所代表的是binlog 能够使用的最大cache 内存大小。
当我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出
" Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage"的错误。

其中需要注意的是：table_cache表示的是所有线程打开的表的数目，和内存无关。

tmp_table_size

MySQL的临时表缓冲大小

thread_stack

主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，
我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存

thread_cache_size

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。

net_buffer_length

客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。

bulk_insert_buffer_size

这只能对myisam表使用

如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度