mysql中优化必读

参考：https://www.phpmianshi.com/?id=24

通用规则

多读少写加缓存，少读多写加队列

带事务的不要整体commit，分段commit

一、要保证数据库的效率，要做好以下四个方面的工作：

① 数据库设计

② sql语句优化

③ 数据库参数配置

④ 恰当的硬件资源和操作系统

 

二、通俗地理解三个范式，对于数据库设计大有好处。

在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。 没有冗余的数据库设计可以做到。

但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

 

三、SQL优化的一般步骤

①通过show status命令了解各种SQL的执行频率。

②定位执行效率较低的SQL语句-（重点select）

③通过explain分析低效率的SQL语句的执行情况

④确定问题并采取相应的优化措施

 

下面的例子：

#其中Com_XXX表示XXX语句所执行的次数。

show global status like 'Com_%';

重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

#其中Com_XXX表示XXX语句所执行的次数。

show global status like 'com_%';

#试图连接MySQL服务器的次数

show status like 'Connections';

#服务器工作的时间（单位秒）

show status like 'Uptime';

#慢查询的次数 (默认是10)

show status like 'Slow_queries'

4.如何查询mysql的慢查询时间

Show variables like 'long_query_time';

5. 修改mysql 慢查询时间

set long_query_time=2

 

四、哪些列上适合添加索引 

1、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

  select * from emp where sex = '男'

2、更新非常频繁的字段不适合创建索引

  select * from emp where logincount = 1

 

#查看索引的使用情况

show status like 'Handler_read%';

大家可以注意：

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。含义：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。”

 

优化group by 语句

默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

 

optimizer_trace

从MySQL5.6版本开始，optimizer_trace 可支持把MySQL查询执行计划树打印出来，默认是关闭的，功能支持动态开关，因为对性能有20%左右影响，只建议分析问题时，临时开启。

1. 默认是关闭的

mysql> show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.05 sec)

2.演示 optimizer_trace 简单的使用流程：
2.1 会话级别临时开启

mysql> 
set session optimizer_trace="enabled=on",end_markers_in_json=on;

2.2 执行你的SQL

select host,user,plugin from user ;

2.3 查询information_schema.optimizer_trace表

mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE\G;

2.4 导入到一个命名为xx.trace的文件，然后用JSON阅读器来查看 

SELECT TRACE INTO DUMPFILE “xx.trace” FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

补充：永久开启 optimizer_trace    （重启失效）

mysql> set optimizer_trace="enabled=on";

 

五、最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

　　innodb_additional_mem_pool_size = 64M

　　innodb_buffer_pool_size =1G

 

对于myisam，需要调整key_buffer_size

 

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数

 

推荐的做法是 

innodb_flush_log_at_trx_commit=2

sync_binlog=N (N为500 或1000) 默认sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

 

一 参数意义

innodb_flush_log_at_trx_commit

InnoDB的innodb_flush_log_at_trx_commit属性可以控制每次事务提交时InnoDB的行为。当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入；当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。

innodb_flush_log_at_commit是InnoDB性能调优的一个基础参数，涉及InnoDB的写入效率和数据安全。

当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注:

   如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

   根据上述描述，我做了一张图，可以方便大家查看。

 

二 性能

    两个参数在不同值时对db的纯写入的影响表现如下：

    

 

 测试场景1 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000

 测试场景2 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1000

 测试场景3 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1

 测试场景4

  innodb_flush_log_at_trx_commit=1

  sync_binlog=1000

 测试场景5 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000 

 

场景	TPS
场景1	41000
场景2	33000
场景3	26000
场景4	33000

由此可见，当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。

 

三 安全

当innodb_flush_log_at_trx_commit和sync_binlog  都为 1 时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双11 会导致频繁的io操作，因此该模式也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

 

双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时 比如11.11 活动的压力

 

总结
一、为了保证事务的ACID特性，理论上每次事务提交都应该刷盘，但此时效率很低，有两种优化方向：
（1）随机写优化为顺序写；
（2）每次写优化为批量写；

二、redo log是一种顺序写，它有三层架构：
（1）MySQL应用层：Log Buffer
（2）OS内核层：OS cache
（3）OS文件：log file

三、为了满足不用业务对于吞吐量与一致性的需求，MySQL事务提交时刷redo log有三种策略：
（1）0：每秒write一次OS cache，同时fsync刷磁盘，性能好；
（2）1：每次都write入OS cache，同时fsync刷磁盘，一致性好；
（3）2：每次都write入OS cache，每秒fsync刷磁盘，折衷；

四、高并发业务，行业内的最佳实践，是：
innodb_flush_log_at_trx_commit=2