Mysql千万级数据性能调优配置

背景：

　　笔者的源数据一张表大概7000多万条，数据大小36G，索引6G，加起来表空间有40G+，类似的表有4张，总计2亿多条

数据库mysql，引擎为innodb,版本5.7，服务器内存256G，物理内存几个T，硬件参数杠杠的，然而处理这些数据踩了不少坑，因

为之前没做过这方面的工作，现在记录下清洗的过程，详细的业务清洗过程和规则均记录在https://gitee.com/yanb618/zhirong/wikis

感受：

　　清洗从表名，字段名，字段类型，字段值，索引创建与删除做起，每每看到那秒数慢慢的涨到几千秒，心中一首凉凉唱起来

假设这个速度，一天下来甚至只能处理一张表，之前的工作经历从来没有做过性能调优，正好这次尝到了苦头，记录之

在描述之前先插一句：一定要先做实验，做实验!看执行时间，这么大的一张表，处理不好执行起来得以小时记，连取消都得等好久

预备：

　　首先读者要分清楚Innodb和Myisam两种引擎的区别：Innodb有完整的事物支持，行锁，B-Tree/Hash索引；Myisam支持表锁，

不支持事物，有FullText索引，适用于快速读取；

　　其次，临时数据能在内存中读写操作的就不要往磁盘上读写

具体：

配置temp_table_size和max_heap_table_size，下面是官网说明

tmp_table_size

Command-Line Format --tmp-table-size=#
System Variable Name    tmp_table_size
Scope   Global, Session
Dynamic Yes
Permitted Values    Type    integer
Default 16777216
Minimum 1024
Maximum 18446744073709551615

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.
The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. 

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. 
Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing 
the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

See also Section 8.4.4, “Internal Temporary Table Use in MySQL”.

max_heap_table_size

Command-Line Format --max-heap-table-size=#
System Variable Name    max_heap_table_size
Scope   Global, Session
Dynamic Yes
Permitted Values (32-bit platforms) Type    integer
Default 16777216
Minimum 16384
Maximum 4294967295
Permitted Values (64-bit platforms) Type    integer
Default 16777216
Minimum 16384
Maximum 1844674407370954752

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. 
The value of the variable is used to calculate MEMORY table MAX_ROWS values. 
Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE 
or altered with ALTER TABLE or TRUNCATE TABLE. 

A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. 
See Section 8.4.4, “Internal Temporary Table Use in MySQL”.

max_heap_table_size is not replicated. 

See Section 17.4.1.20, “Replication and MEMORY Tables”, and Section 17.4.1.38, “Replication and Variables”, for more information.

通常在执行一个耗时很长的更新表操作时查看show processlist命令可以看到如下信息

Copying to tmp table   Copying to tmp table on disk

后者表示内存临时表空间不够，需要往硬盘写，这个很要命，临时查询数据部分在内存部分在硬盘，读写速度骤降

调整这两个参数的大小为40G或者更大都行，你的服务器内存足够大的话可以继续往上调，笔者因为有select into 重构表的需求，

一次读出来的数据很大，因此需要调高默认值

 

innodb_buffer_pool_size

innodb缓冲池，以下是他的职责场景

* 数据缓存 – 最重要的目的
* 索引缓存 – 使用的是同一个缓冲池
* 缓冲 – 更改的数据(通常称为脏数据)在被刷新到硬盘之前先存放到缓冲
* 存储内部结构 – 一些结构如自适应哈希索引或者行锁也都存储在InnoDB缓冲池

很幸运的是我们的服务器是独立的划分给mysql了，根据坊间经验设置为总可用内存的80%，在这里总内存256G，完成这个清洗

设置100G已经足矣，我们需要这个空间来存临时查询的数据，大数据量下尽量避免临时数据在内存和磁盘间交换带来的IO性能影响

 

bulk_insert_buffer_size

笔者的清洗方式其中一种是多线程读取+插入，插入采用批量插入的方式，insert into tbl_name values(),(),()，这个参数可以设置插入

的语句的长度大小，java中String一般是没有长度限制的(除非jvm装不下)，如果需要大批量的拼接insert语句，需要加大这个参数值

当然这个值其实是专门给Myisam存储引擎设计的，设置这个参数后还要配合设置如下参数一起用才能飞起，否则会直接报错

Max_allowed_packet=100M，最大值1G

 

其他的参数优化暂时未使用到，如果是正常的小型的项目数据量不是很大的情况下，一般建议是：使用默认值，不要更改，不要更改

道理很简单：很多企业开发时都不会去调这些参数，这也意味着默认设置经历过最多次的压力测试，除非你已经到了不得不去优化的地步