MYSQL-innodb性能优化几个点

窝窝插件

　　MYSQL-innodb性能优化几个点
　　数据库常用参数
　　MYSQL数据库的参数配置一般在my.ini配置（部分参数也可以用set  global 参数名=值 做临时调整，重启后失效），配置完后需要重启数据库才生效。
　　
　　参数1：slow_query_log= 0|1
　　说明:开关慢查询日志。slow_query_log_file=为存放路径；long_query_time =记录超过的时间，默认为10s。
　　
　　参数2：join_buffer_size= MB
　　说明：join buffer存放基于每thread的连接表信息，连接时，只需访问join buffer,不需要再去有并发机制保护的cache.
　　参数3：Sort_Buffer_Size = MB
　　说明:Sort_Buffer_Size 是一个connection级参数，每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官网文档说“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation”
　　参数4: binlog_format= STATEMENT|ROW|MIXED
　　说明：日志格式
　　1)STATEMENT模式（SBR）
　　每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)。
　　2)ROW模式（RBR）
　　不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
　　3)MIXED模式（MBR）
　　以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
　　参数5：binlog_cache_size = MB
　　说明：默认大小是37268即32K.根据事务需要调整大小。该参数表示在事务中容纳二进制日志sql语句的缓存大小。二进制日志缓存，是服务器支持事务存储引擎并且服务器启用了二进制日志(-log-bin选项)的前提下为每个客户端分配的内存，是每个client都可以分配设置大小的binlog cache空间。
　　参数6:Max_binlog_cache_size = MB
　　说明：默认值是18446744073709547520，这个值很大，够我们使用的了。此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果系统中事务过多，而此参数值设置有小，则会报错。
　　参数7:Max_binlog_size = GB/MB
　　说明：Max_binlog_size: 1073741824=1G ，binlog的最大值，一般设置为512M或1G,一般不能超过1G。此参数不能非常严格控制binlog的大小，特别是在遇到大事务时，而binlog日志又到达了尾部，为了保证事务完整性，不切换日志，把所有sql都写到当前日志。
　　参数8:expire_logs_days= N
　　说明:设置binlog老化日期；有大致三种情况引发日志切换：binlog大小超过max_binlog_size；手动执行flush logs；重新启动时(MySQL将会new一个新文件用于记录binlog)
　　参数9:innodb_file_per_table = 0|1
　　说明：参数值为1，表示对每张表使用单独的 innoDB 文件
　　参数10:innodb_log_file_size= GB/MB
　　说明:对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要;值太大了会让恢复过程变慢.
　　参数11：innodb_log_files_in_group = N
　　说明:该变量控制日志文件数。默认值为2。日志是以顺序的方式写入。
　　参数12:innodb_flush_method=
　　说明:设置InnoDB同步IO的方式：Default (fsync);O_SYNC (以sync模式打开文件，通常比较慢);O_DIRECT(在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering（mysql buffering 和OS buffering）)
　　参数13:transaction_isolation = READ-UNCOMMITTED | READ-COMMITTED |REPEATABLE-READ | SERIALIZABLE
　　说明:设定事务隔离级别
　　1)未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
　　2)提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
　　3)可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
　　4)串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
　　参数14:character-set-server= utf8|utf8mb4
　　说明:设定字符集，utf8存3个字节，utf8mb4存4个字节。
　　参数15：innodb_buffer_pool_size = Gb/MB
　　说明：此参数类似于oracle的SGA配置，当主机做为mysql数据库服务器时，一般配置为整机内存的60%~80%。
　　参数16：innodb_buffer_pool_instances=N
　　说明：内存缓冲池实例数，将innodb_buffer_pool_size配置的内存分割成N份，此参数当配置内存大小于1G时才生效，当数据库有多个会话进行数据库操作时，用于并行在多个内存块中处理任务，一般配置值《=服务器CPU的个数。
　　参数17：max_connections = xxxx
　　说明：最大连接数，当数据库面对高并发时，这个值需要调节为一个合理的值，才满足业务的并发要求，避免数据库拒绝连接。
　　参数18：max_user_connections=xxxx
　　说明：设置单个用户的连接数。
　　
　　参数19：innodb_log_buffer_size =xxxxx
　　说明：日志缓冲区大小,一般不用设置太大，能存下1秒钟操作的数据日志就行了，mysql默认1秒写一轮询写一次日志到磁盘。
　　
　　参数20：innodb_flush_log_at_trx_commit=
　　说明：（这个配置很关键）一般的实时业务交易配置为2，取值0,1,2
　　0:数据操作时，直接写内存，并不同时写入磁盘；
　　2:数据操作时，直接写内存，并不同时写入磁盘；
　　1:就每个事务提交就会要刷新到磁盘后才算提交完成,这种情况是保证了事务的一致性,但性能会有很大的影响。
　　0与2的区别：
　　0：当mysql挂了之后，可能会损失前一秒的事务信息
　　2：当mysql挂了之后，如果系统文件系统没挂，不会有事务丢失。
　　参数21：innodb_read_io_threads = xxxx
　　说明：数据库读操作时的线程数，用于并发。
　　参数22：innodb_write_io_threads = xxx
　　说明：数据库写操作时的线程数，用于并发。
　　参数23：innodb file per table=1
　　说明：每一个表是否使用独立的数据表空间，默认为OFF（使用共享表空间），一般建议配置为1，InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：（ibdata1），这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，
　　单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
　　共享表空间在Insert操作上少有优势。其它都没独立表空间表现好，如果数据库基本上都插入操作则配置为0。
　　
　　参数24：innodb_stats_on_metadata={OFF|on}
　　说明：是否动态收集统计信息，开启时会影响数据库的性能(一般关闭，找个时间手动刷新，或定时刷新）如果为关闭时，需要配置数据库调度任务，定时刷新数据库的统计信息。
　　参数25：innodb_spin_wait_delay=xxxxx
　　说明：控制CPU的轮询时间间隔，默认是6,配置过低时，任务调度比较频繁，会消耗CPU资源。
　　参数26：innodb_lock_wait_timeout=xxxx
　　说明：控制锁的超时时间，默认为50，这个值要注意，如果有特殊业务确实要耗时较长时，不能配置太短。
　　具体的调优参数内容较多，可参考官方文档，一些比较重要的参数：
　　back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500
　　
　　wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时
　　
　　max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限
　　
　　thread_concurrency：并发线程数，设为CPU核数的两倍
　　
　　skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
　　
　　key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在0.1%以下最好
　　
　　innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好
　　
　　innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小
　　
　　innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
　　
　　query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大.
　　可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
　　
　　read_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
　　
　　sort_buffer_size：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
　　
　　read_rnd_buffer_size：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
　　
　　record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
　　
　　thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
　　
　　table_cache：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM
执行计划分析
　　mysql> EXPLAIN SELECT * FROM smssendbak;
　　+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
　　|>
　　+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
　　|  1 | SIMPLE      | smssendbak | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
　　+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
　　说明：
　　Table:表名称
　　Type:重要的一列，显示使用了何种连接，从好到差依次为const、eq_ref、ref、range、index、all，下面详细说明：
　　type的描述：
　　System：表只有一行，这是const连接类型的特例；
　　Const：表中一个记录的最大值能够匹配这个查询(索引可以是主键或唯一索引)。因为只有一行，这个值实际就是常数，因为mysql先读这个值，再把它当作常数对待
　　eq_ref：从前面的表中，对每一个记录的联合都从表中读取一个记录。在查询使用索引为主键或唯一索引的全部时使用；
　　Ref：只有使用了不是主键或唯一索引的部分时发生。对于前面表的每一行联合，全部记录都将从表中读出，这个连接类型严重依赖索引匹配记录的多少－越少越好；
　　Range：使用索引返回一个范围中的行，比如使用>或