优化是有风险的

优化的范围

1、硬件配置方面
2、配置参数方面
3、架构方面
4、mysql语句方面
5、安全方面：比如 数据库服务器 采用内网通信，指定内网通信服务器，如果要连接 可以采用4层反向代理形式连接

  1. 存储、主机和操作系统：
  2.     主机架构稳定性(raid)
  3.     I/O规划及配置
  4.     swap
  5.     OS内核参数
  6.     网络问题
  7. 应用: ( Index, lock, session )
  8.     应用程序稳定性和性能
  9.     SQL语句性能
  10.     串行访问资源
  11.     性能欠佳会话管理
  12. 数据库优化:(内存、数据库设计、参数)
  13.     内存
  14.     数据库结构(物理&逻辑)
  15.     实例配置

硬件优化

  1. --主机
  2. 真实的硬件(PC Server) : DELL  R系列，华为，浪潮，HP，曙光，联想
  3. 云产品: ECS、数据库RDS、DRDS、 PolarDB
  4. IBM小型机P6 570 595  P7 720  750 780    P8
  6. --CPU根据数据库类型
  7. OLTP 主要是对数据的增删改
  8. OLAP 主要是对数据的查询
  10. IO密集型:线上系统，OLTP主要 是IO密集型的业务，高并发
  11. CPU密集型:数据分析数据处理，OLAP, cpu密集型的，需要CPU高计算能力(i系列，IBM power系列)
  12. CPU密集型:I系列的，主频很高，核心少
  13. I0密集型:E系列(至强)，主频相对低，核心数量多
  16. --内存
  17. 建议2-3倍cpu核心数量( ECC )
  19. --磁盘选择
  20. SATA-III  SAS  Fc  SSD(sata)  pci-e  ssd  Flash
  21. 主机RAID卡的BBU (Battery Backup Unit) 关闭（备用电源，用来停电后继续工作，但不安全）
  23. --存储
  24. 根据存储数据种类的不同，选择不同的存储设备
  25. 配置合理的RAID级别(raid5、raid10、热备盘)
  27. r0:条带化,性能高
  28. r1 :镜像，安全
  29. r5 :校验+条带化，安全较高+性能较高(读)，写性能较低(适合 于读多写少)
  30. r10:安全+性能都很高，最少四块盘，浪费一半的空间(高I0要求)
  32. --网络
  33. 1、硬件买好的(单卡单口)
  34. 2、网卡绑定(bonding) ，交换机堆叠
  35. 以上问题，提前规避掉。

系统层优化

  1. CPU    计算（主）和调度（次）
  2. MEM    缓存和缓冲
  3. IO    输入和输出
  5. linux的监控命令：top（cpu指标）、vmstat（io指标）
  7. IO:
  8.     raid
  9.     no lvm
  10.     xfs或ext4(建议xfs，大数据业务有好)
  11.     ssd
  12.     IO调度策略
  14. --swap调整
  15. echo 0 > /proc/sys/vm/swappiness    (临时修改)
  17. echo "vm.swappiness=0" >> /etc/sysctl.conf        (永久修改)
  18. sysctl -p
  20. --IO调度策略
  21. centos 7 默认是deadline
  22. cat /sys/block/sda/queue/scheduler
  24. 7以下的需要手动改为deadline：
  25. echo deadline > /sys/block/sda/queue/scheduler     (临时)
  26. 永久：vi /boot/grub/grub.conf
  27. kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
  28. (主要是再kernel行加入：elevator=deadline)

参数优化后的参考

  1. [mysqld]
  2. basedir=/data/mysql
  3. datadir=/data/mysql/data
  4. socket=/tmp/mysql.sock
  5. log-error=/var/log/mysql.log
  6. log_bin=/data/binlog/mysql-bin
  7. binlog_format=row
  8. skip-name-resolve
  9. server-id=52
  10. gtid-mode=on
  11. enforce-gtid-consistency=true
  12. log-slave-updates=1
  13. relay_log_purge=0
  14. max_connections=1024
  15. back_log=128
  16. wait_timeout=60
  17. interactive_timeout=7200
  18. key_buffer_size=16M
  19. query_cache_size=64M
  20. query_cache_type=1
  21. query_cache_limit=50M
  22. max_connect_errors=20
  23. sort_buffer_size=2M
  24. max_allowed_packet=32M
  25. join_buffer_size=2M
  26. thread_cache_size=200
  27. innodb_buffer_pool_size=1024M
  28. innodb_flush_log_at_trx_commit=1
  29. innodb_log_buffer_size=32M
  30. innodb_log_file_size=128M
  31. innodb_log_files_in_group=3
  32. binlog_cache_size=2M
  33. max_binlog_cache_size=8M
  34. max_binlog_size=512M
  35. expire_logs_days=7
  36. read_buffer_size=2M
  37. read_rnd_buffer_size=2M
  38. bulk_insert_buffer_size=8M
  39. [client]
  40. socket=/tmp/mysql.sock  
  42. 压力测试：
  43.  mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

参数优化详解

针对查询，合理涉及索引是最好的优化

主要参数一个内存缓冲三个安全：

  1. innodb_buffer_pool_size（指定大小的内存来缓冲数据和索引）
  3. innodb_flush_log_at_trx_commit=1（每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID）
  5. sync_binlog=1（每次事务提交都立即刷写binlog到磁盘）
  7. innodb_flush_method=O_DIRECT（在数据页需要持久化时，直接写入磁盘）

Max_connections ★★★★★

  1. （1）简介
  2. Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。
  3. （2）判断依据
  4. show variables like 'max_connections';
  5.     +-----------------+-------+
  6.     | Variable_name   | Value |
  7.     +-----------------+-------+
  8.     | max_connections | 151   |
  9.     +-----------------+-------+
  10. show status like 'Max_used_connections';
  11.     +----------------------+-------+
  12.     | Variable_name        | Value |
  13.     +----------------------+-------+
  14.     | Max_used_connections | 101   |
  15.     +----------------------+-------+
  17. （3）修改方式举例
  18. vim /etc/my.cnf 
  19. Max_connections=1024
  21. 补充:
  22.     1.开启数据库时,我们可以临时设置一个比较大的测试值
  23.     2.观察show status like 'Max_used_connections';变化
  24.     3.如果max_used_connections跟max_connections相同,
  25.     那么就是max_connections设置过低或者超过服务器的负载上限了，
  26.     低于10%则设置过大.

back_log ★★★

  1. （1）简介
  2. mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
  3. back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
  4. （2）判断依据
  5. show full processlist
  6. 发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
  7. （3）修改方式举例
  8. vim /etc/my.cnf 
  9. back_log=1024

wait_timeout和interactive_timeout ★★★★

  1. （1）简介
  2. wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
  3. interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。
  4. wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
  5. （2）设置建议
  6. 如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低
  7. （3）修改方式举例
  8. wait_timeout=60
  9. interactive_timeout=1200
  10. 长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
  11. 一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
  12. 另外还可以使用类外的参数弥补。

key_buffer_size ★★★★★

  1. （1）简介
  2. key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
  3. 《1》此参数与myisam表的索引有关
  4. 《2》临时表的创建有关（多表链接、子查询中、union）
  5.      在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃
  6.      临时表有两种创建方式：
  7.                         内存中------->key_buffer_size
  8.                         磁盘上------->ibdata1(5.6)
  9.                                       ibtmp1 (5.7）
  10. （2）设置依据
  11. 通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。
  12. mysql> show variables like "key_buffer_size%";
  13. +-----------------+---------+
  14. | Variable_name   | Value   |
  15. +-----------------+---------+
  16. | key_buffer_size | 8388608 |
  17. +-----------------+---------+
  18. 1 row in set (0.00 sec)
  20. mysql> 
  21. mysql> show status like "key_read%";
  22. +-------------------+-------+
  23. | Variable_name     | Value |
  24. +-------------------+-------+
  25. | Key_read_requests | 10    |
  26. | Key_reads         | 2     |
  27. +-------------------+-------+
  28. 2 rows in set (0.00 sec)
  30. mysql> 
  31. 一共有10个索引读取请求，有2个请求在内存中没有找到直接从硬盘中读取索引
  32. 控制在 5%以内 。
  33. 注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
  34. 可以使用检查状态值created_tmp_disk_tables得知：
  36. mysql> show status like "created_tmp%";
  37. +-------------------------+-------+
  38. | Variable_name           | Value |
  39. +-------------------------+-------+
  40. | Created_tmp_disk_tables | 0     |
  41. | Created_tmp_files       | 6     |
  42. | Created_tmp_tables      | 1     |
  43. +-------------------------+-------+
  44. 3 rows in set (0.00 sec)
  45. mysql> 
  46. 通常地，我们习惯以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
  47. Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
  49. 或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。
  50. Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
  51. 控制在5%-10%以内
  52. 看以下例子：
  53. 在调用mysqldump备份数据时，大概执行步骤如下：
  54. 180322 17:39:33       7 Connect     root@localhost on
  55. 7 Query       /*!40100 SET @@SQL_MODE='' */
  56. 7 Init DB     guo
  57. 7 Query       SHOW TABLES LIKE 'guo'
  58. 7 Query       LOCK TABLES `guo` READ /*!32311 LOCAL */
  59. 7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
  60. 7 Query       show create table `guo`
  61. 7 Query       show fields from `guo`
  62. 7 Query       show table status like 'guo'
  63. 7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
  64. 7 Query       UNLOCK TABLES
  65. 7 Quit
  67. 其中，有一步是：show fields from `guo`。从slow query记录的执行计划中，可以知道它也产生了 Tmp_table_on_disk。
  69. 所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。 
  70. （3）配置方法
  71. key_buffer_size=64M

query_cache_size ★★★

  1. （1）简介：
  2. 查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。
  4. SQL层：
  5. select * from t1 where name=:NAME;
  6. select * from t1 where name=:NAME;
  8. 1、查询完结果之后，会对SQL语句进行hash运算，得出hash值,我们把他称之为SQL_ID
  9. 2、会将存储引擎返回的结果+SQL_ID存储到缓存中。
  11. 存储方式：
  12. 例子：select * from t1  where id=10;      100次
  14. 1、将select * from t1  where id=10; 进行hash运算计算出一串hash值，我们把它称之为“SQL_ID"
  15. 2、将存储引擎返回上来的表的内容+SQLID存储到查询缓存中
  17. 使用方式：
  18. 1、一条SQL执行时，进行hash运算，得出SQLID，去找query cache
  19. 2、如果cache中有，则直接返回数据行，如果没有，就走原有的SQL执行流程
  21. 一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。
  22. 注：两个sql语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。
  24. （2）判断依据
  25. mysql> show status like "%Qcache%";
  26. +-------------------------+---------+
  27. | Variable_name           | Value   |
  28. +-------------------------+---------+
  29. | Qcache_free_blocks      | 1       |
  30. | Qcache_free_memory      | 1031360 |
  31. | Qcache_hits             | 0       |
  32. | Qcache_inserts          | 0       |
  33. | Qcache_lowmem_prunes    | 0       |
  34. | Qcache_not_cached       | 2002    |
  35. | Qcache_queries_in_cache | 0       |
  36. | Qcache_total_blocks     | 1       |
  37. +-------------------------+---------+
  38. 8 rows in set (0.00 sec)
  40. ---------------------状态说明--------------------
  41. Qcache_free_blocks：缓存中相邻内存块的个数。
  42. 如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
  43. 注：当一个表被更新之后，和它相关的cache 
  44. blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
  46. Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。
  48. Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
  50. Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
  52. Qcache_lowmem_prunes：
  53. 多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）
  55. Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
  57. Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；
  58. Qcache_total_blocks：当前Query Cache 中的block 数量；。
  59. Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits) 
  60.     90/         10000             0             90
  62. 如果出现hits比例过低，其实就可以关闭查询缓存了。使用redis专门缓存数据库
  64. Qcache_free_blocks    来判断碎片
  65. Qcache_free_memory   +   Qcache_lowmem_prunes  来判断内存够不够
  66. Qcache_hits 多少次命中  Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)  
  68. （3）配置示例
  69. mysql> show variables like '%query_cache%' ;
  70. +------------------------------+---------+
  71. | Variable_name                | Value   |
  72. +------------------------------+---------+
  73. | have_query_cache             | YES     |
  74. | query_cache_limit            | 1048576 |
  75. | query_cache_min_res_unit     | 4096    |
  76. | query_cache_size             | 1048576 |
  77. | query_cache_type             | OFF     |
  78. | query_cache_wlock_invalidate | OFF     |
  79. +------------------------------+---------+
  80. 6 rows in set (0.00 sec)
  82. mysql> 
  83. -------------------配置说明-------------------------------
  84. 以上信息可以看出query_cache_type为off表示不缓存任何查询
  86. 各字段的解释：
  87. query_cache_limit：超过此大小的查询将不缓存
  88. query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
  89. query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
  91. query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：
  92. 如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
  93. 如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
  94. 如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
  96. 修改/etc/my.cnf,配置完后的部分文件如下：
  97. query_cache_size=128M
  98. query_cache_type=1

max_connect_errors ★★★

  1. max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
  2. 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
  3. max_connect_errors=2000

sort_buffer_size ★★★

  1. （1）简介：
  2. 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速
  3. ORDER BY 
  4. GROUP BY
  5. distinct
  6. union 
  8. （2）配置依据
  9. Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
  10. 列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存
  11. （3）配置方法
  12.  修改/etc/my.cnf文件，在[mysqld]下面添加如下：
  13. sort_buffer_size=1M

max_allowed_packet ★★★★★

  1. （1）简介：
  2. mysql根据配置文件会限制，server接受的数据包大小。
  3. （2）配置依据：
  4. 有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数
  5. （3）配置方法：
  6. max_allowed_packet=32M

join_buffer_size ★★★

  1. select a.name,b.name from a join b on a.id=b.id where xxxx
  2. 用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
  3. 尽量在SQL与方面进行优化，效果较为明显。
  4. 优化的方法：在on条件列加索引，至少应当是有MUL索引

thread_cache_size ★★★★★

  1. (1)简介
  2. 服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.
  3. （2）配置依据
  4. 通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
  5. 设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
  6. 服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
  8. 试图连接到MySQL(不管是否连接成功)的连接数
  9. mysql>  show status like 'threads_%';
  10. +-------------------+-------+
  11. | Variable_name     | Value |
  12. +-------------------+-------+
  13. | Threads_cached    | 8     |
  14. | Threads_connected | 2     |
  15. | Threads_created   | 4783  |
  16. | Threads_running   | 1     |
  17. +-------------------+-------+
  18. 4 rows in set (0.00 sec)
  20. Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
  21. Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
  22. Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。
  23. Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
  24. (3)配置方法：
  25. thread_cache_size=32
  27. 整理：
  28. Threads_created  ：一般在架构设计阶段，会设置一个测试值，做压力测试。
  29. 结合zabbix监控，看一段时间内此状态的变化。
  30. 如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。
  31. 如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）

innodb_buffer_pool_size ★★★★★★

  1. （1）简介
  2. 对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
  3. （2）配置依据：
  4. InnoDB使用该参数指定大小的内存来缓冲数据和索引。
  5. 对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。
  6. （3）配置方法
  7. innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit ★★★★★★

  1. （1）简介
  2. 主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
  3. 0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
  4. 1，每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID；
  5. 2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。
  7. （2）配置依据
  8. 实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
  9. 根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
  10. （3）配置方法
  11. innodb_flush_log_at_trx_commit=1
  12. 双1标准中的一个1

innodb_thread_concurrency ★★★

  1. （1）简介
  2. 此参数用来设置innodb线程的并发数量，默认值为0表示不限制。
  3. （2）配置依据
  4. 在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
  5. 如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
  6. 如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
  7. 并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
  8. 例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，
  9. 性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，
  10. 建议设置innodb_thread_concurrency参数为80，以避免影响性能。
  11. 如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），
  12. 建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），
  13. 如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。
  14. 但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，
  15. 你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
  16. 在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
  17. 定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
  19. 128   -----> top  cpu  
  20. 设置标准：
  21. 1、当前系统cpu使用情况，均不均匀
  22. top
  24. 2、当前的连接数，有没有达到顶峰
  25. show status like 'threads_%';
  26. show processlist;
  27. （3）配置方法：
  28. innodb_thread_concurrency=8
  29. 方法:
  30.     1. 看top ,观察每个cpu的各自的负载情况
  31.     2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值
  32.     3. 一直观察top状态,直到达到比较均匀时,说明已经到位了.

innodb_log_buffer_size

  1. 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
  2. innodb_log_buffer_size=128M
  4. 设定依据：
  5. 1、大事务： 存储过程调用 CALL
  6. 2、多事务

innodb_log_file_size = 100M ★★★★★

  1. 设置 ib_logfile0  ib_logfile1 
  2. 此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
  3. innodb_log_file_size = 100M

innodb_log_files_in_group = 3 ★★★★★

  1. 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

read_buffer_size = 1M ★★★

  1. MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

read_rnd_buffer_size = 1M ★★★

  1. MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
  2. 注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

bulk_insert_buffer_size = 8M ★★★

  1. 批量插入数据缓存大小，可以有效提高插入效率，默认为8M
  2. tokuDB    percona
  3. myrocks   
  4. RocksDB
  5. TiDB
  6. MongoDB

binary log ★★★★★

  1. log-bin=/data/mysql-bin
  2. binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M
  3. max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小
  4. max_binlog_size= 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
  5. expire_logs_days = 7 //定义了mysql清除过期日志的时间。
  6. 二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。
  7. log-bin=/data/mysql-bin
  8. binlog_format=row 
  9. sync_binlog=1
  10. 双1标准(基于安全的控制)：
  11. sync_binlog=1   什么时候刷新binlog到磁盘，每次事务commit
  12. innodb_flush_log_at_trx_commit=1
  13. set sql_log_bin=0;
  14. show status like 'com_%';

安全参数 ★★★★★

  1. Innodb_flush_method=(O_DIRECT, fsync) 
  2. 1、fsync    ：
  3. （1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
  4. （2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
  5. 但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
  6. 2、 Innodb_flush_method=O_DIRECT
  7. （1）在数据页需要持久化时，直接写入磁盘
  8. （2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
  9. 但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
  11. 最安全模式：
  12. innodb_flush_log_at_trx_commit=1
  13. innodb_flush_method=O_DIRECT
  14. 最高性能模式：
  15. innodb_flush_log_at_trx_commit=0
  16. innodb_flush_method=fsync
  18. 一般情况下，我们更偏向于安全。 
  19. “双一标准”
  20. innodb_flush_log_at_trx_commit=1        ★★★★★★★★★★
  21. sync_binlog=1                           ★★★★★★★★★★
  22. innodb_flush_method=O_DIRECT