mysql参数优化

mysql为了提高查询和写入效率，设计了非常多的内存缓存，mysql读写数据很多时候并不是直接去操作磁盘，而是利用了他的缓存，下面我们一一介绍mysql中的这些缓存；

内存

Innodb_buffer_pool

Innodb BufferPool  不仅缓存索引，同时还会缓存实际的数据，它的大小直接影响数据库的性能，随着缓冲池的增大，TPS会线性增长；直到缓冲池的大小大于数据文件本身的大小为止，最好的情况是将全部的数据和索引都放入到这个缓存里面，这样mysql的查询操作都从内存里取数据，大大提高了读取效率；

如何设置这个值？

网上都建议将这个值设置为整个系统物理内存的 80% 之间；

正确计算此值的姿势

这个值的设置并没有一个固定比例，2G内存、20G内存和200G内存都设置为80%，显然是不合理的，mysql内存分配除了innodb_buffer_pool  ，还有如下几大部分：

1.    操作系统自身占用；

2.    操作系统缓存，用于缓存mysql binlog日志，根据binlog日志大小和缓存的数量预留，如果有主从的情况下一定要预留大一些；

3.    query_cache_size查询缓存，MySQL启动的时候，一次性分配并且初始化这块内存，如果修改这个变量（即使设置为与当前值一样）,MySQL会立刻删除所有缓存的查询，重新分配这片缓存到指定大小，并且重新初始化内存,这个缓存对我们的近实时监控类的应用没有帮助，大部分查询都是当前时间，查询条件一直在变，返回数据也在不停的更新，除了配置类的信息，缓存基本没有用处，所以设置query_cache_type=0关闭它；

4.    Mysql线程独占的内存，网上给出的计算公式

（binlog_cache + sort_buffer+ join_buffer + read_buffer + read_rnd_buffer + thread_statck+temp_table）* 线程数；

注意：这个公式只是坏情况下内存占用，很多缓存如果不用就不分配，所以可以根据这个公式估算一个最大预留值；

5.   key_buffer_size,如果mysql当前还是用到了MyISAM引擎，还要减去Key Cache的小，即使我们应用里面没有直接创建MyISAM引擎的表，在查询过程中创建的磁盘临时表也会用到，并且系统表很多都是MYISAM格式的；

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度;

selectsum(INDEX_LENGTH) from INFORMATION_SCHEMA.TABLES WHEREENGINE="MYISAM";

使用上面命令查询当前存在的MYISAM表的索引大小；

检查设置是否合理key_reads /key_read_requests  <0.01;

通过SHOW STATUS LIKE ‘key_read%’可以获取上面两个值;

另外操作系统不会为该设置立马分配内存，而是使用的时候才分配。

6.    其它一些缓存，如table_cache_size ,当有线程打开表时，MySQL会检查这个值，如果大于缓存中表的数量，线程可以把最先打开的表放入缓存，如果该值比缓存中的表数小，MySQL将从缓存中删除不常用的表;

判断大小设置是否合适？

mysql> show global status like'Innodb_buffer_pool_pages%';

+----------------------------------+------------+

| Variable_name                    | Value      |

+----------------------------------+------------+

| Innodb_buffer_pool_pages_data    | 1936050   |   

| Innodb_buffer_pool_pages_dirty   | 0         |

| Innodb_buffer_pool_pages_flushed | 3570973767|

| Innodb_buffer_pool_pages_free    | 8192      |

| Innodb_buffer_pool_pages_misc    | 21834     |

| Innodb_buffer_pool_pages_total   | 1966076   |

+----------------------------------+------------+

6 rows in set (0.00 sec)

Innodb_buffer_pool_pages_data   使用的页

Innodb_buffer_pool_pages_free    空闲页

Innodb_buffer_pool_pages_total    总页数

如果空闲页很多，说明设置过大，相反如果空闲页很少，说明需要调大；

673988453447

mysql> show global status like'innodb%read%';

+---------------------------------------+--------------+

| Variable_name                         | Value        |

+---------------------------------------+--------------+

| Innodb_buffer_pool_read_ahead_rnd     | 0            |

| Innodb_buffer_pool_read_ahead         | 7693655      |

| Innodb_buffer_pool_read_ahead_evicted |3149         |

| Innodb_buffer_pool_read_requests      |673950553617 |

| Innodb_buffer_pool_reads              | 30206175     |

| Innodb_data_pending_reads             | 0            |

| Innodb_data_read                      | 746666364928 |  读取的字节数

| Innodb_data_reads                     | 35134270     |     读入的请求次数|Innodb_pages_read                     | 45573342     |    

| Innodb_rows_read                      | 175996757160 |

+---------------------------------------+--------------+

10 rows in set (0.00 sec)

 

Innodb_buffer_pool_reads              从物理硬盘读取的页的次数，缓存不存在则会去硬盘上读；

Innodb_buffer_pool_read_ahead     顺序预读次数

Innodb_buffer_pool_read_ahead_evicted  预读的页，没有被读取就被替换的页，用来判断预读效率

 

Innodb_buffer_pool_read_requests  从缓冲池读取页的次数

Innodb_data_read                总共读入的字节数；

Innodb_data_reads                     读取的请求次数；

 

buffer_pool命中率=

Innodb_buffer_pool_read_request/(innodb_buffer_pool_read_request+Innodb_buffer_pool_read_ahead+innodb_buffer_pool_reads)

如果低于99%就要加大buffer pool；

平均读取字节数 = innodb_data_read/innodb_data_reads

innodb_buffer_pool_instances

当InnoDB_Buffer_Pool缓冲池达到好几十GB时，如果某个线程正在更新缓冲池，将会造成其他线程必须等待的瓶颈

 innodb_buffer_pool_instances可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写（取值范围为1-64）,每个缓存池的大小为

innodb_buffer_pool_size/innodb_buffer_pool_instances，通常单个innode_buffer_pool缓存池的大小不能小于1GB。

使用这个参数的前提是：高并发读写，根据并发情况设置，并发不高的情况下无需更改；

 

临时表

外部临时表

通过CREATE TEMPORARYTABLE 创建的临时表，这种临时表称为外部临时表。

这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。

内部临时表

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOWSTATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

 

内部临时表类型

l  一种是HEAP临时表，使用memory引擎，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。

l  一种是硬盘临时表，使用myisam引擎（5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎），MySQL最初会将临时表创建在内存中,当数据变的太大后,就会转储到磁盘上.

临时表的大小

tmp_table_size

max_heap_table_size 

两者最小值决定了内存临时表的大小；

每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数；

mysql> show global status like'Created_tmp%';

+-------------------------+--------+

| Variable_name           | Value  |

+-------------------------+--------+

| Created_tmp_disk_tables | 126714 |

| Created_tmp_files       | 37553 |

| Created_tmp_tables      | 238038 |

+-------------------------+--------+

3 rows in set (0.00 sec)

Created_tmp_disk_tables /Created_tmp_tables * 100%  值越小越好；

 

 

排序

排序方式

1.   通过索引直接获取数据，无需额外操作；

2.   通过内部排序算法,排序；

 

 

如何判断是否使用到了排序?

如果查看执行计划，在Extra列中显示Using filesort，说明mysql使用到了排序算法进行排序；

排序算法sort_model

l  < sort_key, rowid >双路排序（又叫回表排序模式）:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

l  < sort_key, additional_fields >单路排序:是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

l  < sort_key, packed_additional_fields >打包数据排序模式:MySQL 5.7.3以后引入的进一步优化

 

双路排序

1.    根据索引或者全表扫描，按照过滤条件获得需要查询的排序字段值和row ID；

2.    将要排序字段值和row ID组成键值对，存入sort buffer中；

3.    如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序算法)在内存中排好序，并写到临时文件中；

4.    重复上述步骤，直到所有的行数据都正常读取了完成；

5.    用到了临时文件的，需要利用磁盘外部排序，将row id写入到结果文件中；

6.    根据结果文件中的row ID按序读取用户需要返回的数据。由于row ID不是顺序的，导致回表时是随机IO，为了进一步优化性能（变成顺序IO），MySQL会读一批row ID，并将读到的数据按排序字段顺序插入缓存区中(缓存大小read_rnd_buffer_size，这个缓存用到的时候才分配，且并不是一次分配，而是用到多少分配多少)。

 

单路排序

1.    根据索引或者全表扫描，按照过滤条件获得需要查询的数据；

2.    将要排序的列值和 用户需要返回的字段 组成键值对，存入sort buffer中；

3.    如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序算法)在内存中排好序，并写到临时文件中；

4.    重复上述步骤，直到所有的行数据都正常读取了完成；

5.    用到了临时文件的，需要利用磁盘外部排序，将排序后的数据写入到结果文件中；

6.    直接从结果文件中返回用户需要的字段数据，而不是根据row ID再次回表查询。

 

打包排序

此序模式的改进仅仅在于将char和varchar字段存到sort buffer中时，更加紧缩。

前两种模式中，存储了”yes”3个字符的定义为VARCHAR(255)的列会在内存中申请255个字符内存空间，5.7.3改进后，只需要存储2个字节的字段长度和3个字符内存空间就够了，内存空间整整压缩了50多倍,可以让更多的键值对保存在sort buffer中。

 

使用哪种排序算法？

max_length_for_sort_data

 

第二种模式是第一种模式的改进，避免了二次回表，采用的是用空间换时间的方法。

但是由于sort buffer就那么大，如果用户要查询的数据非常大的话，很多时间浪费在多次磁盘外部排序，导致更多的IO操作，效率可能还不如第一种方式。

所以，MySQL给用户提供了一个max_length_for_sort_data的参数。当“排序的键值对大小” > max_length_for_sort_data时，MySQL认为磁盘外部排序的IO效率不如回表的效率，会选择第一种排序模式；反之，会选择第二种不回表的模式。

Sort buffer

排序缓存，sort_buffer_size ,一旦需要排序，MySQL就会立刻分配给改参数指定大小的全部内存，而不管排序是否需要这么大的内存，如果大小够用，则只会使用sortbuffer执行排序操作，如果sortbuffer不够，则会使用临时表；

 

查看参数是否配置是否合理

mysql> show global status like'%Sort_merge_passes%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Sort_merge_passes | 12255 |

+-------------------+-------+

1 row in set (0.00 sec)

 

mysql> show session status like '%Sort_merge_passes%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Sort_merge_passes | 0     |

+-------------------+-------+

1 row in set (0.00 sec)

sort_merge_passes  由于sort buffer不够大，不得不将需要排序的数据进行分段，然后再通过sort merge的算法完成整个过程的merge总次数，一般整个参数用来参考sortbuffer size 是否足够。

 

 

 

 

排序优化

能用索引的，建索引；

针对无法使用索引的：

1.   查询结果用到多少字段就返回多少字段，对于返回的结果集非常大的查询，可以减少磁盘的读写与cpu的消耗；

2.   使用索引排序，消除排序操作；

3.   增大sort_buffer_size，max_length_for_sort_data,消除磁盘临时表；

4.   增大max_length_for_sort_data，避免双路排序

5.   无法避免双路排序，则增大read_rnd_buffer_size

 

Order by，group by，distinct；

 

thread_cache_size

当有连接关闭时，MySQL检查缓存中是否还有空间来缓存线程。

如果有:则缓存改线程已被下次连接重用

如果没有：他讲销毁改线程而不再缓存，

缓存中使用的线程数，不会立即减少，只有在新的连接删除缓存中的一个线程并使用后才会减少

MySQL只在关闭连接时候才在缓冲中增减线程，在创建新的连接的时候才从缓存中删除线程

 

硬盘

IO密集型，最好使用固态硬盘，机械硬盘

 

Io调度策略

Noop

把邻近bio进行了合并处理，对bio进行了后向合并，从而最大限度的保证相邻的bio得到合并处理，该算法适合使用固态硬盘；

Deadline

Deadline这种调度器对读写request进行了分类管理，并且在调度处理的过程中读请求具有较高优先级。这主要是因为读请求往往是同步操作，对延迟时间比较敏感，而写操作往往是异步操作，可以尽可能的将相邻访问地址的请求进行合并，但是，合并的效率越高，延迟时间会越长。因此，为了区别对待读写请求类型，deadline采用两条链表对读写请求进行分类管理。但是，引入分类管理之后，在读优先的情况下，写请求如果长时间得到不到调度，会出现饿死的情况，因此，deadline算法考虑了写饿死的情况，从而保证在读优先调度的情况下，写请求不会被饿死。

该算法适合机械硬盘；