MySQL优化点记录(一)

摘抄于 极客时间 <MySQL实战45讲> 专栏，内容非常实用，有兴趣的同学可以看看：
https://time.geekbang.org/column/intro/100020801

1. 使用长连接，因为创建连接是比较耗时和耗费资源的
2. 长连接可能导致MySQL内存占用飙升，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里的，这些资源会在连接断开的时候才会释放，所以如果长连接累积下来，可能导致内存占用太大被系统强行杀掉（OOM），表现为MySQL异常重启了，解决方案：一、定期断开长连接；二、如果是MySQL5.7或更新版本，可以在每次执行一个较大的操作后通过执行mysql_reset_connection来重新初始化连接资源
3. 禁用查询缓存，因为查询缓存失效非常频繁，导致命中率极低，是因为只要对一个表有更新，这个表上所有的缓存都会被清空。所以查询缓存只适用于读多写少的场景。
4. 关于日志记录的参数设置： 一、innodb_flush_log_at_trx_commit参数设置成1，表示每次事务的redolog都持久化到磁盘，保证MySQL异常重启之后数据不丢失。二、sync_binlog这个参数也设置为1，表示每次事务的binlog都持久化到磁盘，保证MySQL重启之后binlog不会丢失
5. 尽量避免长事务：以可重复读隔离级别为例，基于MVCC（多版本并发控制）的实现原理，每条记录的每一次更新都会记录一个undolog，而每次事务启动的时候，会创建一个逻辑上的视图，不同时刻创建的事务会有不同的read_view，所以同一记录在系统中可能同时存在多个版本的值，对于一个尚未提交的事务来说，其通过undolog可以计算得到其启动时的值。所以如果有事务需要使用到undolog(即事务的创建时间早于undolog生成时间)，undolog就不能被删除，使用长事务会导致系统中存在很老的事务视图，在这个事务提交之前，所有的回滚记录都必须保留，会导致大量的存储空间占用。在MySQL5.5之前，undolog跟数据字典一起存放在ibdata文件中的，即使长事务最终被提交，回滚段被清理，文件也不会变小。如果要清理undolog，只能重建整个库。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库
6. 承接上一条，set autocommit=1，开启事务自动提交，如果考虑到显示开启事务多一次begin的网络开销，可以使用commit work and chain提交事务并开启下一个事务。
7. 承接第5条，检查是否存在不必要的只读事务，有些框架习惯上来就使用begin等语句，而有些业务可能只有几条查询语句，是没有必要开启事务的，所以这种情况是可以去掉事务的。
8. 承接第5条，根据业务预估业务的最长执行时间，使用max_execution_time限制语句执行的最长时间，避免单个语句意外执行太长时间
9. 承接第5条，通过事务表（innodb_trx）监控执行时间较长的事务，进行报警或kill
10. 承接第5条，把innodb_undo_tablespaces设置成2（或更大的值），开启undolog独立的存储，如果真的出现大事务导致回滚段过大，后续清理比较方便
11. 尽量基于主键查询，因为基于主键能避免回表操作，减少开销
12. 从索引维护的角度来看：为了维护索引的有序性，在插入新值的时候需要做必要的维护，如果主键是有序增加的，不存在中间插入的情况，也就不涉及挪动其他记录，那么可以减少因为中间插值造成的页分裂的情况（也分裂会造成空间利用率降低大约50%），所以通常建议使用自增主键；同时应尽量使用逻辑删除（标记删除），减少页合并的情况。
13. 主键的长度不宜过大，因为二级索引的叶子节点存储的是主键的值，主键的长度越小，二级索引占用的空间就越小
14. 适合使用业务字段做主键的场景，一、只有一个索引；二、该索引必须是唯一索引；综合以上两者来看，就是典型的kv场景
15. 使用覆盖索引，减少回表操作，提升效率
16. 建立联合索引时：一、优先考虑最左原则，这样可以达到索引的重复利用，有效减少需要维护的索引数量；二、考虑索引空间的占用，比如一个字符串（长度大于int型）和一个int型的字段间联合索引，最好将字符串放在前面，然后再单独给age创建索引
17. 主键索引需要注意，不论是删除主键还是创建主键，都会将整个表重建，会影响聚簇索引和其他依托聚簇索引的所有二级索引。

-- 错误的方式
alter table T drop primary key;
alter table T add primary key(id);
-- 建议使用如下方式：
alter table T engine=InnoDB;