Mysql--- sql优化 + innodb引擎优化

一. 优化方案选择

1.SQL本身的优化

1.1 从索引开始优化—提升SQL执行效率

1.1.1 B数索引

B数：小的放左边，大的放右边

索引建立原则：

A．频繁更新的字段不适合

B．很少使用的效率

C．索引使用会降低写操作的效

2. 选择需要优化的sql ( 阶段 )

选择方法：优先选择高并发低消耗的SQL：

A．1小时请求1W次，1次10个IO

B．1小时请求10次，1次1W个IO

即：A和B从单位时间产生的IO总数是相同的

  针对A 把10个io 减少到7个，则1小时减少3W个IO

  针对B 把1W个io减少到7K个，则1小时减少3W个IO

从系统性能上考虑，第一个SQL的优化能极大改善性能，第二个SQL慢一点无非也就是10个连接慢

3. 在索引中完成排序：

4.优化方法

1. 使用最小的colum

4.2 Where 语句中的查询条件不一定越多越好

  因为where语句中只能使用一个索引列

4.3 避免使用复杂的join和子查询

4.4 把复杂SQL拆分成简单的小SQL ，在内存中处理数据

5.引擎参数合理配置优化

5.1.缓冲池innodb_buffer_pool_size :

官网：

https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

LRU算法。用于缓存索引的内存和实际的数据的空间，一般是系统内存的50% --70%左右

   该参数是在my.ini / my.cnf配置文件中设置，

查询其大小：show global variables like 'innodb_buffer_pool_size';

如果是加载到缓存中是没有IO操作的，索引对性能提升很大

5.1.1. 设置原则 调整InnoDB缓冲池大小

缓冲池innodb_buffer_pool_size大小必须是

缓冲块innodb_buffer_pool_chunk_size  *

缓冲实例 innodb_buffer_pool_instances 结果的倍数。

Buffer = N * （ block * instance * num ）

如果配置不满足倍数则缓冲池大小会自动调整为等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。

命令： SET GLOBAL innodb_buffer_pool_size = 3221225472

单位是bit

5.1.2  如何权衡配置innodb_buffer_pool_size大小

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。

可以使用以下公式计算InnoDB缓冲池性能：

Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。

例如，在我的服务器上，检查当前InnoDB缓冲池的性能：

5.1.2.1.缓存使用查询

A．show status like 'innodb_buffer_pool_read%';

| Variable_name                         | Value       |

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

| Innodb_buffer_pool_read_ahead_rnd     | 0           |

| Innodb_buffer_pool_read_ahead         | 0           |

| Innodb_buffer_pool_read_ahead_evicted | 0           |

| Innodb_buffer_pool_read_requests      | 4029033624  |

| Innodb_buffer_pool_reads              | 91661       |

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

5 rows in set (0.00 sec)

5.1.2.2. 命中率

A．Innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数，需要从磁盘中读取。

B．Innodb_buffer_pool_read_requests：表示从内存中逻辑读取的请求数

命中率= B / ( A + B )  * 100%

如果接近1 说明查询的命中率很高，可以满足缓冲池本身的大部分请求。从磁盘完成读取的百分比非常小。因此无需增加innodb_buffer_pool_size值。

无需增大缓存，反之增加即可。

5.1.3. 什么时候减小innodb_buffer_pool_size

在专用MySQL服务器上，多余的innodb_buffer内存不会有问题，但是当使用共享服务器时，可能会有性能影响。因为空闲内存对其他程序和操作系统很有用。

5.1.3.1.缓冲池的监控

可以使用SHOW ENGINE INNODB STATUS\G命令检查内存池状态：

mysql> show engine innodb status\G

Total large memory allocated 26386366464 //为缓冲池分配的总内存（以字节为单位）

Dictionary memory allocated 23826297//为InnoDB数据字典分配的总内存（以字节为单位

Buffer pool size   1572672

Free buffers       8192//缓冲池空闲列表的页面总大小

Database pages     1553364 //缓冲池LRU列表的页面总大小

Old database pages 573246//缓冲池旧LRU子列表的页面总大小

Modified db pages  36//缓冲池旧LRU子列表的页面总大小

Pending reads      0//等待读入缓冲池的缓冲池页数

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 881819, not young 18198964

0.02 youngs/s, 0.05 non-youngs/s

Pages read 681064, created 2749237, written 3988300

0.02 reads/s, 0.12 creates/s, 11.50 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 1553364, unzip_LRU len: 0

I/O sum[5152]:cur[0], unzip sum[0]:cur[0]

 

Free buffers :表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。

InnoDB buffer pool 命中率：

InnoDB buffer pool 命中率 低于90%，则可以考虑增加innodb_buffer_pool_size

5.2．缓冲池实例 innodb_buffer_pool_instances

5.2.1.  作用

增加缓冲池的数量可以提高数据库线程访问的并发量，

1. 查看引擎实例数量：SELECT @@innodb_buffer_pool_instances;

5.2.2. 设置原则

 A．最大为64（最大值）。

B．仅当您设置innodb_buffer_pool_size为1GB或更大的大小时，实例的配置设置才会生效 。

C．指定的总大小在所有缓冲池之间分配。为了获得最佳效率，指定的组合 innodb_buffer_pool_instances 和innodb_buffer_pool_size，使得每个缓冲池实例是至少为1GB。

官网提供：

https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-buffer-pools.html

5.3. 缓冲块设置 innodb_buffer_pool_chunk_size

5.3.1.规则

 A．如果缓冲池等于(块 * 实例个数)正整数倍，则引擎不会调整各值，结果取各自设置的原值

    Buffer = N * ( 块 * 实例 )

B．如果缓冲块 大于缓冲池的大小 则 缓冲块 = 缓冲池 / 实例数量

规则：见5.1.1

 

5.4.sync_binlog：控制数据库的binlog刷到磁盘上

查看值：select @@sync_binlog;

该参数控制着二进制日志写入磁盘的过程。

该参数的有效值为0 、1、N：

0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。

1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。1是最安全的，但是也是最慢的。

N: 使执行N次写入后，与硬盘同步

5.5.innodb_flush_log_at_trx_commit

查看值：select @@innodb_flush_log_at_trx_commit;

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点。来修改事务日志同步时机：如下

 

1. innodb_flush_log_at_trx_commit=0 //每1秒钟同步一次事务日志文件
2. innodb_flush_log_at_trx_commit=1// 每个事务完成之后同步一次事务日志文件
3. innodb_flush_log_at_trx_commit=2//事务完成后，写到事务日志文件中，等到日志覆盖再同步数据

 

建议：A的性能最差，B不能完全保证数据是写到数据文件中，C

5.6. innodb_use_native_aio

InnoDB使用Linux上的异步I / O子系统（本机AIO）来执行数据文件页面的预读和写入请求。此行为由innodb_use_native_aio 配置选项控制，该选项仅适用于Linux系统，默认情况下处于 启用状态