五. 优化参数

参数	说明
`innodb_file_per_table`	OFF: 表的数据放在系统共享表空间, 也就是跟数据字典放在一起； ON: 表示的是, 每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中. mysql 5.6.6后,该值默认为ON. ON: (建议设置)一个表单独存储为一个文件更容易管理, 而且在你不需要这个表的时候, 通过 drop table 命令, 系统就会直接删除这个文件.而如果是放在共享表空间中, 即使表删掉了, 空间也是不会回收的. 解决方法: 重建表 `alter table A engine=InnoDB` 推荐github开源项目gh-ost来做`online DDL`.
`wait_timeout`	客户端连接超时时间.
`innodb_flush_log_at_trx_commit`	0: 表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ; 1: 表示每次事务的 redo log 都直接持久化到磁盘.(推荐 ) 2: 表示每次事务提交时都只是把 redo log 写到 page cache.
`sync_binlog`	0: 每次提交事务都只 write, 不 fsync； 1: 表示每次事务的 binlog 都持久化到磁盘(推荐) N: 表示每次提交事务都 write, 但累积 N 个事务后才 fsync. 建议100~1000
`transaction-isolation`	隔离级别
`autocommit`	ON 显示的启动事务,提交和回滚,
`innodb_change_buffer_max_size`	这个参数设置为 50 的时候, 表示 change buffer 的大小最多只能占用 buffer pool 的 50%.
`long_query_time`	慢查询阀值
`innodb_stats_persistent`	存储索引统计的方式: 设置为 on 的时候, 表示统计信息会持久化存储. 这时, 默认的 N 是 20, M 是 10. 设置为 off 的时候, 表示统计信息只存储在内存中. 这时, 默认的 N 是 8, M 是 16.
`innodb_io_capacity`	告诉 InnoDB 你的磁盘能力,建议你设置成磁盘的 IOPS. 可用此命令查询: `fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest`
`innodb_max_dirty_pages_pct`	脏页比例上限, 默认值是 75%,通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到.
`innodb_flush_neighbors`	1: 表示有上述的“连坐”机制, 0: 表示不找邻居, 自己刷自己的.
`sort_buffer_size`	MySQL 为排序开辟的内存（sort_buffer）的大小
`sql_log_bin`	off: 不写binlog on: 不写binlog
`binlog_group_commit_sync_delay`	表示延迟多少微秒后才调用 fsync;
`binlog_group_commit_sync_no_delay_count`	表示累积多少次以后才调用 fsync.

在 InnoDB 中, 每个数据页的大小默认是16KB.
如果内存够, 就要多利用内存, 尽量减少磁盘访问.
唯一索引的更新就不能使用 change buffer, 实际上也只有普通索引可以使用
- change buffer 用的是 buffer pool 里的内存, 因此不能无限增大.
- change buffer 的大小, 可以通过参数 innodb_change_buffer_max_size 来动态设置. (这个参数设置为 50 的时候, 表示change buffer 的大小最多只能占用 buffer pool的 50%. )
优化器逻辑
- 扫描行数是影响执行代价的因素之一. 扫描的行数越少, 意味着访问磁盘数据的次数越少, 消耗的 CPU 资源越少.
- 扫描行数并不是唯一的判断标准, 优化器还会结合是否使用临时表、是否排序等因素进行综合判断.
MySQL 在真正开始执行语句之前, 并不能精确地知道满足这个条件的记录有多少条, 而只能根据统计信息来估算记录数.

这个统计信息就是索引的“区分度”. 显然, 一个索引上不同的值越多, 这个索引的区分度就越好. 而一个索引上不同的值的个数, 我们称之为**“基数”（cardinality）**. 也就是说, 这个基数越大, 索引的区分度越好.

在 MySQL 中, 有两种存储索引统计的方式, 可以通过设置参数 innodb_stats_persistent的值来选择：
- 设置为 on的时候, 表示统计信息会持久化存储. 这时, 默认的 N 是 20, M 是 10.
- 设置为 off的时候, 表示统计信息只存储在内存中. 这时, 默认的 N 是 8, M 是 16.
给字符串加索引
- 完整索引: 这样可能比较占用空间；
  
  alter table SUser add index index1(email);
- 前缀索引: 节省空间, 但会增加查询扫描次数, 并且不能使用覆盖索引, 因为必须回表查询确认一次；
  前缀索引, 定义好长度, 就可以做到既节省空间, 又不用额外增加太多的查询成本.
  
  alter table SUser add index index2(email(6));
```
select   count(distinct left(email,4)）as L4,  
            count(distinct left(email,5)）as L5,  
            count(distinct left(email,6)）as L6,  
            count(distinct left(email,7)）as L7,
from SUser;
```
- 倒序存储: 再创建前缀索引, 用于绕过字符串本身前缀的区分度不够的问题；但是不支持范围查询.
- hash字段: 查询性能稳定, 有额外的存储和计算消耗, 跟第三种方式一样, 都不支持范围扫描.
  
  可以在表上再创建一个整数字段, 来保存身份证的校验码, 同时在这个字段上创建索引.
刷脏页逻辑, mysql抖动原因.
- innodb_io_capacity这个参数了, 它会告诉 InnoDB 你的磁盘能力. 这个值我建议你设置成磁盘的 IOPS. 通过fio工具获取磁盘IOPS能力fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
- innodb_max_dirty_pages_pct 是脏页比例上限, 默认值是 75%. InnoDB 会根据当前的脏页比例（假设为 M）, 算出一个范围在 0 到 100 之间的数字. 脏页比例获取通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total具体使用select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';select @a/@b;
- innodb_flush_neighbors 用来控制刷脏页时是否刷邻居脏页.
如何删除数据并缩小表文件.
- 可以使用以下命令要重建表
```
alter table A engine=InnoDB -- 与下一句相同
alter table t engine=innodb,ALGORITHM=inplace;
alter table t engine=innodb,ALGORITHM=copy;
```
- 建议使用 gh-ost 来做.
optimize table、analyze table 和 alter table 这三种方式重建表的区别.
- 从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）；
- analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
- optimize table t 等于 recreate+analyze.
对索引字段做函数操作, 可能会破坏索引值的有序性, 因此优化器就决定放弃走树搜索功能. 比如隐式转换(字符转数字),字符集不一致(utf-8与uft8mb4)等都有可能产生函数操作,引起优化器放弃树搜索.

Keyboard shortcuts

knowledge

五. 优化参数