mysql优化总结

---

一、MyISAM引擎

1、引擎特点

1、不支持事务(事务是指逻辑上的一组 SQL 操作，组成这组操作的各个单元，要么全成功要么全失败) 
2、表级锁定，数据更新时锁定了整个表，其锁定机制是表级锁定，这虽然可以让锁定的实 现成本很小但是也同时大大降低了其并发性能 
3、读写互相阻塞，不仅会在写入的时候阻塞读取，myisaw 还会在读取的时候阻塞写入，但 读本身并不会阻塞另外的读
4、只会缓存索引，myisaw 可以通过 key_buffer_size 缓存索引，以大大提高访问性能减少磁 盘 IO，但是这个缓存区只会缓存索引，而不会缓存数据 
5、读取速度较快，占用资源相对少
6、不支持外键约束，但支持全文索引

2、MyISAM引擎调优

1、设置合适的索引(缓存机制) 
2、调整读写优先级，根据实际需求确保重要操作更优先执行 
3、启用延迟插入改善大批量写入性能(降低写入频繁，尽可能多条数据一次性写入) 
4、尽量顺序操作让 insert 数据都写入到尾部，减少阻塞时间
5、分解大的时间长的 SQL 操作，降低单个操作的阻塞时间
6、降低并发数(减少对 mysql 访问)，某些高并发场景通过应用进行排队队列机制 Q 队列 
7、对于相对静态(更改不频繁)的数据库数据，充分利用 query cache 或 memcached/redis 缓 存服务可以极大的提高访问效率，网站动态内容静态化，减少对数据库的访问 
8、MyISAW 的 count 只有在全表扫描的时候特别高效，带有其他条件的 count 都需要进行 实际的数据访问
9、可以把主从同步的主库使用 innodb，从库使用 MyISAW 引擎(不推荐)

二、innoDb引擎

1、引擎特点

1、支持事务:支持 4 个事务隔离级别，支持多版本读
2、行级锁定(更新时一般是锁定当前行):通过索引实现，全表扫描仍然会表锁，注意间隙锁的影响
3、读写阻塞与事务隔离级别相关
4、具有非常高效的缓存特性:能缓存索引，也能缓存数据
5、整个表和主键以 Cluster 方式存储，组成一颗平衡树
6、所有 secondary index 都会保存主键信息
7、支持分区、表空间
8、支持外键约束，5.5 以前不支持全文索引，以后就支持了
9、和 MyISAW 引擎比，innodb 对硬件资源要求比较高

2、适合的业务场景

1、需要事务支持的业务(具有较好的事务特性) 
2、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成 
3、数据读写及更新都比较频繁的场景 
4、数据一致性要求比较高的业务，例如:充值转账，银行卡转账 
5、硬件设备内存较大，可以利用 innodb 较好的缓存能力来提高内存利用率，尽可能减少磁 盘IO

3、常用的一些参数设置

// 缓存大小，理论上是越大越好，实际设置的时候一般是系统内存的70%左右
innodb_buffer_pool_size = 32M
// 修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。
innodb_file_per_table = 0
innodb_data_file_path = ibdata1:128M:autoextend 
// 读线程数量
innodb_read_io_threads = 4
// 写线程数量
innodb_write_io_threads = 4
// 并发操作系统的线程数,当为0时，并发数没有限制
innodb_thread_concurrency = 8 
// 内存数据刷新策略
// 0：log buffer 会 每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
// 1：每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
// 2：每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。
innodb_flush_log_at_trx_commit = 2 
// 该参数确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。
innodb_log_buffer_size = 2M
// 该参数决定着mysql事务日志文件（ib_logfile0）的大小；
// 太小会触发数据库的checkpoint,并且频繁切换日志文件，导致性能下降
// 太大会大大提升性能，单一旦数据库宕机，恢复数据会花费很长时间
// 一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
innodb_log_file_size = 4M 
// 该参数控制日志文件数。默认值为2。mysql 事务日志文件是循环覆写的。
innodb_log_files_in_group = 3 
// innodb_max_dirty_pages_pct 是 MySQL InnoDB 存储引擎非常重要的一个参数，用来控制buffer pool中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB会启动刷脏页的操作。
innodb_max_dirty_pages_pct = 90 
// InnoDB事务在放弃前等待行锁的时间（秒）
innodb_lock_wait_timeout = 120

4、常用优化点

1、主键尽可能小，避免给 secondary index 带来过大的空间负担 
2、建立有效索引避免全表扫描，因为会使用表锁 
3、尽可能缓存所有的索引和数据，提高响应速度，减少磁盘 IO 消耗 
4、在大批量小插入的时候，尽量自己控制事务而不要使用 autocommit 自动提交，有开关可 以控制提交方式
5、合理设置 innodb_flush_log_at_trx_commit 参数值，不要过度追求安全性
6、避免主键更新，因为这会带来大量的数据移动