MySQL优化-MySQL体系结构

MySQL体系结构 三层体系结构： 连接层 SQL层 存储层 关于timeout 通过jdbc等程序连接的是非交互会话。 通过mysql cli客户端连接的是交互会话。 wait_timeout，关闭非交互连接（程序端）之前等待的秒数。默认8h。 interactive_timeout，关闭交互式连接（客户端）前等待的秒数。默认8h。 本节小结： 5.7开始，支持密码过期机制。 8.0开始，支持更多密码安全机制，更安全的MySQL 8.0之全新密码策略。 一些LB应用会疯狂探测数据库，可能会造成被blocked。 调整合理的timeout阈值，减少连接数浪费。 配置文件my.cnf [client]是所有客户端工具全局选项（mysql/mysqladmin/mysqldump/mysqlshow等） [mysql] 是mysql客户端的相关选项、账号、密码、socket、字符集、auth-rehash等。 .frm 文件可以用mysqlfrm工具解析。 .ibt user session innodb temp tablespace. .ibu 用户自定义的undo表空间文件。 支持DDL原子性，都成功或都失败。 I_S查询性能提升（以往总是需要先访问.frm文件），并增加cache机制。 information_schema_stats_expiry控制元数据缓存失效时长，设置为0不缓存，或者analyze table也会更新缓存。 设置innodb_read_only后，就无法创建任何新表。 手动mkdir newDB，无法被自动识别。 DDL过程涉及InnoDB引擎事务系统，会相对略慢些。 本节小结： 8.0之后，元数据全部存储在InnoDB里。 不要再任性手动创建一个目录或者删除文件，可能造成元数据损坏。 使用独立undo表空间，以及独立用户表空间。 并行复制writeset机制 5.7的并行复制效率，取决于事务在主库上的并发度。如果主库上并发度不高，或者有大事务，则从库延迟依然比较严重。 8.0的writeset模式完美解决了这个难题：即便在主库是串行提交，但只要事务间不冲突，在从库依然可以并行回放。 新增选项binlog_transaction_dependency_tracking。 commit_ordere（默认），基于锁的并发策略，使用5.7的group commit机制决定事务依赖。 writeset（建议）基于主键的并发策略，可以并发的执行同一个session内的事务（只要不冲突），具有最好的性能。 writeset_session，基于主键的并发策略，不可以并发执行同一个session内的事务。 MySQL 8.0新特性介绍 1.消除了buffer pool mutex（Percona的贡献）。 2.数据字典全部采用InnoDB引擎存储，支持DDL原子性、crash safe，metadata管理更完善（可以利用ibd2sdi工具提取metadata）。 # ibd2sdi t1.ibd -d t1.sdi 3.快速在线加新列（腾讯互娱DBA团队贡献） 4.并行redo log，并提升redo log的I/O性能，可在线调整redo log buffer。 5.新增倒叙索引，不可见索引。 6.优化器增强，增强CBO特性。 7.支持会话级别SET_VAR动态调整部分参数。 8.重构SQL分析器（SQLParser）。 9.行锁增加skip locked和nowait特性选项。 10.新增事务CATS特性，大大提升事务性能（Michigan大学贡献）。 11.在线修改undo log数量。 12.直方图。 13.认证插件由sha256_password改成caching_sha2_password。 14.新增角色role功能，将权限打包分组再二次授权。 15.自增列持久化。 16.增强json性能、功能。 17.新增智能选项innodb_dedicated_server。 18.InnoDB memcached插件支持mget操作。 19.增加redo/undo log加密。 20.增加resource group功能。 21.新增更多数据字典。 22.默认字符集改成utf8mb4。 23.通用表达式CTE。 24.窗口函数。 25.set persist。 26.restart功能。 27.取消QC。 8.0.14后新特性： 双密码机制 binlog加密 log_slow_extra admin_port innodb_buffer_pool_in_core_file MySQL InnoDB Cluster支持IPv6 虚拟列 5.7开始支持json、虚拟列，看起来是为8.0的函数索引，表达式索引做准备。 此外，8.0开始支持json的多值索引模式。 临时表/临时文件 用户创建的显式临时表，create temporary table。 内部临时表，SQL执行时优化器创建的隐式临时表。 临时表超过内存限制后，就会转成磁盘临时表。 相关选项 tmp_table_size / max_heap_table_size temptable_max_ram internal_tmp_disk_storage_engine innodb_temp_tablespace_dir innodb_temp_data_file_path 临时文件 可见临时文件，用完后自行删除。 不可见文件，创建后立即unlink，需要用lsof才能看到，默认存储在tmpdir下。 运行状态下，vim打开log文件后保存，也会生成deleted状态的不可见文件。 binlog中未提交事务太大时，需要binlog disk cache，ML****。 filesort排序时，内存放不下，需要磁盘文件，MY****。 主库执行load data时，从库也会有相应的临时磁盘文件（slave_load_tmpdir）。 执行show create table查看分区表时，也会生成临时文件。 innodb引擎也会创建临时文件，用于存储最新外键&唯一约束错误、死锁信息、innodb状态信息。 DDL过程中生成的临时文件。 online DDL的过程中，有时需要重建原表，同时需要对二级索引排序，会创建ibXXXXXX这样的临时文件。 DDL过程中，还允许DML进行，就需要把这些DML操作记录到online log中。log较少时，就放在内存（innodb_sort_buffer_size）中，较大时就会转成磁盘临时文件，存储在innodb_tmpdir下。 当online log超限（innodb_online_alter_log_max_size）后，DDL失败。 在DDL的最后阶段，要把上面排序完的临时文件和online log全部都应用到一个中间文件中，文件名叫sql-ibX1-X2.ibd中（X1是table id，X2是随机数）。此外，在8.0前还会生成.frm文件，8.0后则不会。 几个优化建议 创建索引，减少额外排序、分组。 适当调大sort_buffer_size、tmp_table_size和max_heap_table_size，temptable_max_ram，减少磁盘表。 减少大事务以避免binlog_cache过大，适当调大binlog_cache_size。 多用短连接，因为连接断开后，一些session级内存（例如binlog cache）会及时释放，减少binlog disk cache机会。 当确定知道SQL会生成大表时，可告知优化器直接生成磁盘表，减少内存=>磁盘的转化开销，select ... sql_big_result ... (sql_small_result)。 本节小结： 建议使用8.0后默认的temptable引擎，性能更好。 尽量不使用临时表。 有用到临时表的会话，记得关闭/重置连接，释放临时表。 尽量不online DDL，若innodb_online_alter_log_max_size不够大会失败。 不要用vi打开正在运行的数据文件、日志文件。 怎么调整内存相关选项，如何尽可能避免发生内存溢出？ 内存泄露，memory leak，内存跑到黑洞里，不可用了。 内存溢出，memory overflow, out of memory，申请的内存超过物理内存大小。 MySQL内存结构： 全局： 只分配一次。 全局共享。 连接/会话： 针对每个会话/线程分配。 按需动态分配，查询结束后释放。 用于处理（缓冲、中转）查询结果。 每个会话的缓冲区大小都不一样。 多用短连接，以为连接断开后，一些session级内存会及时释放。 连接/会话层：net/read/join/sort/bulk insert buffer、tmp/heap table,binlog cache sql层/server层：query cahce、table (def) cache、thread cache、mdl cache 引擎层：innodb buffer、innodb log buffer、key buffer、myisam sort buffer size mysqld进程消耗内存估算 = global buffers + all thread buffers 全局分配内存global buffer（类似SGA） = innodb buffer pool + innodb log buffer + key buffer + query cache + table cache + thread cache 会话/线程级分配内存all thread buffers（类似PGA） = max_threads * ( read buffer + read rnd buffer + sort buffer + join buffer + tmp table + binlog cache + histogram ram) 两个容易被设置很大的内存选项 都是session级 max_heap_table_size限制memory表最大容量，不管其他执行SQL产生的临时表，若内存不够用，则不允许写入新的数据，memory表也不会转成磁盘表，只会告警超限后拒绝写入。 tmp_table_size不限制memory表最大容量，如果执行SQL产生临时表超过tmp_table_size或max_heap_table_size，则会产生基于磁盘的临时表。 这2个选项特别容易分配较大，若有需要，可临时调大，不要修改全局值。 查看buffer pool的组成 [dba@localhost:mysql.sock] [(none)]> select page_type as page_type, -> sum(data_size)/1024/1024 as Size_in_MB -> from information_schema.innodb_buffer_page -> group by page_type -> order by Size_in_MB desc; +-------------------+-------------+ | page_type | Size_in_MB | +-------------------+-------------+ | INDEX | 17.88112068 | | INODE | 0.00000000 | | SYSTEM | 0.00000000 | | UNKNOWN | 0.00000000 | | FILE_SPACE_HEADER | 0.00000000 | | UNDO_LOG | 0.00000000 | | TRX_SYSTEM | 0.00000000 | | IBUF_BITMAP | 0.00000000 | | IBUF_INDEX | 0.00000000 | +-------------------+-------------+ 9 rows in set (0.12 sec) 查看表的数据和索引使用情况 select engine, count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),‘M‘) rs, concat(round(sum(data_length)/(1024*1024*1024),2),‘G‘) DATA, concat(round(sum(index_length)/(1024*1024*1024),2),‘G‘) idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),‘G‘) total_size, round(sum(index_length)/sum(data_length),2) idxfrac from information_schema.TABLES where table_schema not in (‘mysql‘, ‘performance_schema‘, ‘information_schema‘, ‘test‘) group by engine order by sum(data_length+index_length) desc limit 10; [dba@localhost:mysql.sock] [(none)]> select engine, count(*) as TABLES, -> concat(round(sum(table_rows)/1000000,2),‘M‘) rs, -> concat(round(sum(data_length)/(1024*1024*1024),2),‘G‘) DATA, -> concat(round(sum(index_length)/(1024*1024*1024),2),‘G‘) idx, -> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),‘G‘) total_size, -> round(sum(index_length)/sum(data_length),2) idxfrac -> from information_schema.TABLES -> where table_schema not in (‘mysql‘, ‘performance_schema‘, ‘information_schema‘, ‘test‘) -> group by engine -> order by sum(data_length+index_length) -> desc limit 10; +--------+--------+-------+-------+-------+------------+---------+ | engine | TABLES | rs | DATA | idx | total_size | idxfrac | +--------+--------+-------+-------+-------+------------+---------+ | InnoDB | 15 | 6.66M | 0.40G | 0.01G | 0.41G | 0.04 | | NULL | 100 | NULL | NULL | NULL | NULL | NULL | +--------+--------+-------+-------+-------+------------+---------+ 2 rows in set (0.11 sec) 关于huge page 使用大页是为了提高内存管理效率。 默认内存页4KB，当有大内存时管理效率较低。采用大页（2MB）时，可以减少内存页数量，提高管理效率。 经过测试，启用大页对MySQL OLTP场景的性能提升很小。 此外，启用透明大页可能反而导致MySQL（TokuDB）更容易发生内存泄露、OOM等问题。 查看是否关闭。 [root@db01 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled [always] madvise never [root@db01 ~]# cat /sys/kernel/mm/transparent_hugepage/defrag [always] madvise never 本节小结： 从5.7起，innodb buffer pool可以在线动态调整，因此不要设置太大，可以在运行过程中按需加大（只要没有ibp wait free就不用太担心）。 几个可能容易被误以为全局分配的内存选项，不要任性设置太高，主要有tmp_table_size/max_heap_table_size、temptable_max_ram。 从8.0起，临时表默认采用temptable方式存储，整体效率更高。 默认的sql_mode可能比较难用，但建议遵循默认规则。 物理表选择默认的InnoDB引擎，临时表选择默认的temptable引擎。 PT工具使用 information_schema performance_schema sys MySQL客户端工具 mysql cli GUI mysql workbench navicat 执行mysql_upgrade记得加-f 8.0.16之后就没有mysql_upgrade了。 数据安全： MySQL服务器没有公网IP。 也不能通过映射/NAT等方案让外部可访问。 也不能绑定全部网卡，明确指定IP。 授权时，务必限制IP段、域名、主机名，而不是全局。 权限务必严格控制，不过度放权。 绝对不能使用弱密码，像123456，abc123，abcd.1234这些。 有条件的话，要求定期更换密码（5.7起可以设置密码定期失效） 应用程序端，数据库连接配置信息尽可能加密。 所有用户输入值都要做过滤。 利用prepare做预处理。 利用sql_mode做限制。 定期扫描，发现异常SQL请求。 或者直接利用审计功能发现异常SQL请求。 sleep超过5秒。 访问I_S、P_S、mysql库读取元数据。 有union、union all特征。 有updatexml、extractvalue、concat_ws、concat、hex、load_file、outfile、sys_exec、UDF等函数。 mysqld进程必须用普通用户运行，且该用户没有登入shell。 datadir目录仅mysqld进程属主可读写，设置为0700模式。 禁用load_file，必要的话设置可信的source_file_priv。 设置socket文件仅允许mysqld属主访问，其余用户均不可使用（甚至关闭）。 必要时开启审计功能。 禁用old_password选项，如果有可能则采用最新的caching_sha2_password机制。

 

MySQL优化-MySQL体系结构

标签：插件   read   打开   link   memory   内存溢出   reads   串行   部分