MySQL Performance Tuning: 10 Tips (English Version)

配置、最佳实践和追踪丑小鸭 迈克·弗兰克产品管理总监 MySQL 乌尔瓦希·奥斯瓦尔主要MySQL技术人员 议程 十大技巧 议程 配置6.适用于工作负载的正确配置 所有关于查询9、工作负载10以及丑小鸭的内容 导入数据 1. 使用MySQL Shell工具2. 加快导入 存储器 利用机器学习解决第4个问题——自动索引 架构设计 7. 消费 8. Linux内存分配器 3.4.5.主键指数并行索引创建 导入数据以光速！ 导入数据 对于逻辑转储，我的SQL壳牌应该优先选择 Dump & Load Utility 而非过时的单线程 mysqldump！ 我的SQL壳牌Dump & Load 可以导出一个完整的实例、一个或多个模式（schemas）或表（tables）。您还可以添加一个 WHERE 子句。 此工具并行地导出和加载数据！ 数据可以存储在文件系统、OCI对象存储、S3和Azure Blob存储上。 JS > util.dumpInstance(\"/opt/dump/\", {threads: 32}) 导入数据（2） 该倾倒场可以被导入到我的SQL使用 util.loadDump()。loadDump()是用于加载由以下创建的转储的方法： •util.dumpInstance()•util.dumpSchemas()•util.dumpTables() (, {:32})JS> util.加载转储\"/opt/dump/\线程 导入数据 – 高速 我们可以加快这个过程！在初始加载期间，&耐用性是没问题如果发生崩溃，进程可以重新启动。因此，如果耐用性不是很重要，我们可以进一步降低它以加速加载。 我们可以禁用二进制日志，禁用重做日志并调整Inno数据库通过更改一些设置。请注意，禁用和启用二进制日志需要重启。我的SQL. 以 --disable-log-bin 选项启动 mysqld MySQL>ALTER实例禁用INNODB重做日志;MySQL>设置全球InnoDB 扩展和初始化=关闭;MySQL>设置全球InnoDB最大脏页百分比=10;MySQL>设置全球innodb_max_dirty_pages_pct_lwm=10; 架构设计 主键索引，不过少，也不过多。 主键是必需的，而且一个好的主键更是锦上添花！主键ForInno数据库 InnoDB将数据存储在表空间中。一些理论 记录使用聚集索引（PK）进行存储和排序。 所有二级索引也包含主键作为索引的最右列（即使这没有公开）。这意味着当使用二级索引检索一条记录时，会使用两个索引：首先使用二级索引指向用于最终检索记录的主键。 InnoDB 主键 – 非顺序 = 访问许多页面 主要键的影响在于如何插入值以及二级索引的大小。非顺序的PK可能导致许多随机的IOPS。 此外，使用能够生成完全随机主键的应用程序变得越来越普遍。 这意味着如果主键不是顺序的， Inno数据库将不得不在插入页面上大量重新平衡所有页面。 粉红积木 - 某页面已触达。这里有很多IOPs。 InnoDB 主键 – 顺序键 – 少数页面访问 如果我们比较使用自增整数作为主键时的相同负载（插入），我们可以看到只有最新的页面最近被接触过： InnoDB 主键 ? 无键 ! 使用InnoDB时另一个常见的错误是没有定义任何主键。 当未定义主键时，使用第一个唯一的非空键。 如果没有任何可用的，InnoDB 将创建一个隐藏的主键（6字节）。 问题的关键在于您无法对此键有任何控制权，并且更糟的是，此值对所有表格都是全局的，而没有主键的情况下，如果同时对这样的表进行多次写操作，可能会导致竞争问题（）。dict_sys->mutex). 如果您计划实现高可用性，没有主键的表将不被支持！ InnoDB 主键 ? 无键 !为了识别这些表格，运行以下SQL语句以查找GEN_CLUST_INDEX： SELECT i.TABLE_ID, t.NAME FROM INFORMATION_SCHEMA.INNODB_INDEXES i JOIN INFORMATION_SCHEMA.INNODB_TABLES t ON (i.TABLE_ID = t.TABLE_ID) WHERE i.NAME='GEN_CLUST_INDEX'; InnoDB 主键 ? 无键 ! (2) |1198|松弛/some_table|1472|test/默认测试|1492|test/t1|2018|世界/订单|2019|世界/销售|2459|dbt3/时间统计+ InnoDB GIPK模式 自MySQL 8.0.30以来，在GIPK模式下运行时，MySQL支持生成的不可见主键！ GIPK模式由sql_generate_invisible_primary_key服务器系统变量控制。 当我的SQL正在运行中GIPK模式在表中，服务器添加了一个主键，列和键的名称始终是我的行ID. 指数，不多也不少——未使用的指数 需要维持指数（指数）未使用可能费用高昂并增加不必要的IOPS。使用sys Schema和innodb_index_stats可以识别出那些未使用的索引： 选择数据库名称,表名,t1.指数名称,字节格式化(统计值*@@innodb_page_size)大小从MySQL.innodb_index_stats t1加入系统.schema_unused_indexes t2在对象模式=数据库名称并且对象名称=表名并且t2.指数名称=t1.指数名称此处无具体内容需翻译，直接保留原文：where变量名：stat_name=大小按顺序排列统计值desc; 指数，不多也不少——未使用的指数 指数，不多也不少——未使用的指数并且这种行为同样适用于重复指数。没有必要继续维持它们： 选择t2.*,字节格式化(统计值*@@innodb_page_size)大小从MySQL.innodb_index_stats t1加入系统.schema_redundant_indexes t2在表架构=数据库名称并且t2.表名=t1.表名并且t2.冗余索引名称=t1.指数名称此处无具体内容需翻译，直接保留原文：where变量名：stat_name=大小按顺序排列统计值desc\G 重复索引 别忘了！ 不要轻信推荐，删除指数前请先进行检查。 不要立即删除索引，但首先设置为不可见一段时间内。偶尔这个指数可能会被使用，比如用于月度报告。 监控ALTER语句的进度 lpad(格式化的皮科时间（format_pico_time）(语句.定时器等待),10," ")作为开始于过去,lpad(格式化的皮科时间（format_pico_time）(语句.定时器等待/圆形(100*阶段.已完成工作/阶段.工作估算0),10," ")作为估计的全职员工数, lpad(格式化的皮科时间（format_pico_time）((语句.定时器等待/圆形(100*阶段.已完成工作/阶段.工作估算00) -语句.定时器等待),10," ")作为估计剩余时间,当前分配的内存 从性能模式.事件声明当前语句内连接系统.内存使用量按线程和当前字节统计 mt在mt.线程ID=语句.线程ID内连接性能模式.事件_阶段 当前阶段在阶段.线程ID=语句.thread_id\\G 缺失索引我们还需要找出哪些指数可能被 看起来——机器学习可以自动化此过程 自动驾驶索引 - 演示 索引创建速度缓慢。 并行索引创建 - 示例 MySQL>修改表预订添加索引idx_2(航班号,座位,乘客ID);查询成功,0行受影响(9最小值0.6838sec) 默认设置如下： innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 Theinnodb_ddl_buffer_size是共享于所有innodb_ddl_threads定义良好。如果您增加线程数量，我们建议您同时增加缓冲区大小。 并行索引创建 - 示例（2）为了找到这些变量的最佳值，让我们来看看CPU核心的数量： MySQL>选择计数从信息架构.INNODB_METRICS此处无具体内容需翻译，直接保留原文： +-------+where姓名='cpu_n';|计数| +-------+|16| +-------+ 所以我们有16个核心可以共享。 由于这台机器内存充足，我们可以为InnoDB DDL缓冲区分配1GB。 并行索引创建 - 示例（3） MySQL>SETinnodb_ddl_threads=8;MySQL>SETInnoDB并行读取线程=8;MySQL>SETinnodb_ddl_buffer_size=1048576000; 我们现在可以重试之前相同的索引创建： MySQL>修改表预订添加索引idx_2(航班号,座位,乘客ID);查询成功,0行受影响(2最小值43.1862sec) 并行索引创建 - 示例（4） 最好运行测试以确定您的数据库、硬件和数据的最优设置。 例如，这里我们将缓冲区大小设置为2GB，并且将DDL线程和并行读取线程都设置为4。 耗时2分43秒，比最初的9分钟好得多！ 关于更多信息，请访问：For more information, go tohttps://lefred.be/content/mysql-8-0-innodb-parallel-threads-for-online-ddl-operations/ 秘密 #1 是 InnoDB 缓冲池的大小内存中保持工作集是很重要的。InnoDB缓冲池的大小很重 要： MySQL>SELECT字节格式化(@@innodb_buffer_pool_size)缓冲池大小,格式(A.数字（num）*100.0/B.数字（num）,2)缓冲池满百分比,格式(C.数字（num）*100.0/D.数字（num）,2)缓冲池脏百分比FROM (SELECT变量值数值FROM性能模式.全球状态WHERE变量名=Innodb_buffer_pool_pages_data)A, (SELECT变量值数值FROM性能模式.全球状态WHERE变量名=Innodb_buffer_pool_pages_total)B, (SELECT变量值数值FROM性能模式.全球状态WHERE变量名=Innodb_buffer_pool_pages_dirty)C, (SELECT变量值数值FROM性能模式.全球状态WHERE变量名=Innodb_buffer_pool_pages_total)D; 秘密 #1 是 InnoDB 缓冲池的大小内存中保持工作集是很重要的。InnoDB缓冲池的大小很重 要： MySQL>SELECT字节格式化(@@innodb_buffer_pool_size)缓冲池大小,格式(A.数字（num）*100.0/B.数字（num）,2)缓冲池满百分比,格式(C.数字（num）*100.0/D.数字（num）,2)缓冲池脏百分比 (.=),(.=),(.=),(.=);+----------------+-------------------+--------------------++----------------+-------------------+--------------------+FROMSELECT可变值数字FROM性能模式全球状态WHERE变量名| 128.00 MiB | 87.12 | 0.36 |Innodb_buffer_pool_pages_dataASELECT可变值1行在集（0.0012秒）数字FROM性能模式全球状态WHERE变量名Innodb_buffer_pool