|
写在前面，最近比较忙没时间去写，这不,清明放假了，也没有办法回老家，才得以写点东西
| 类型 | myisam | memory | innodb |
|---|
| 用途 | 快读 | 内存数据 | 完整的事务与支持 | | 锁 | 全表锁 | 全表锁 | 多种隔离级别的行锁 | | 持久性 | 基于表恢复 | 磁盘I/O,无持久性 | 基于日志恢复 | | 事务特征 | 不支持 | 不支持 | 支持 | | 支持索引 | B-tree/FullText/R-tree | Hash/B-tree | Hash/B-tree |
MySQL在高并发下的性能瓶颈很明显，主要原因就是锁机制导致的阻塞，而 innodb 采用的是行级别的锁，行级锁消耗要远大于表级锁。但是系统遇到高并发的时候 innodb 的性能要高得多，同时 innodb不仅缓存索引本身还会缓存数据，所以 innodb 需要更大的内存
1-选择合适的存储引擎是优化 mysql 的第一步
ps:读写比：R/W, 执行 show global status; 可得到系统当前的状态，得到一个粗糙的读写比，理想的读写比一般在 100:1 左右，当这个值约为 10:1 的时候就可以认为系统是以写为主了。
选择存储引擎的原则：
| 场景 | 采用 myisam引擎 | 采用 myisam引擎 | 采用 myisam引擎 |
|---|
| 读写比 | R/W>100:1,且update相对较少 | R/W较小且频繁更新 | 无限制 | | 并发 | 并发量低不需要事务 | 高并发、一致性要求高 | 一致性要求不高，但实时性高 | | 数据量 | 数据量较小100W以内 | 数据超千万 | 无确切数，需要定期归档的数据 | | 硬件资源 | 资源有限 | 硬件资源相对充足 | 硬件资源相对充足 |
2-MySQL服务器调优
(1)关闭不必要的二进制日志和慢查询日志，仅在内存足够或者开发测试阶段打开
使用 show variables like ‘%slow%’; 查看慢查询是否打开
使用 show global status like ‘%slow%’; 查看慢查询条数
由于慢查询会带来cpu损耗，建议间断性打开慢查询日志来定位瓶颈
(2)适度使用 Query Cache
(3)增加MySQL允许的最大连接数：show variables like ‘%max-connections%’
(4)对MyIsam 适当增加 key-buffer-size ,需根据 key-cache 的命中率进行计算
(5)在表中删除大量的行后，使用optimize table tablename 整理碎片
当然，数据层面的优化通常就是加索引优化查询
3-MySQL瓶颈以及应对
首先明确一点，mysql 肯定是存在瓶颈的，当单表数据达到1000万级别后，无论如何调优，执行一次简单的查询，mysql 的性能都会显著降低。
我们可以考虑从以下几方面优化,优先级降序：
(1) 增加 mysql 配置中的 buffer 和 cache 的值，增加服务器 cpu 和内存，这样很大程度上应对 mysql 性能瓶颈。性能调优中效果最显著成本最低的就是硬件和服务器优化，一般有限考虑这一点，当然前提是系统中的 sql 没有索引问题。
(2) 使用第三方引擎和第三方版本：
如 Percona 在功能和性能上比 MySQL 有显著提升
MariaDB 在 innodb 引擎性能上比 MySQL 更加优秀
TokuDB 性能是 MySQL 的 10 倍以上！
以上3款替代品都是针对 innodb 存储引擎而言的。
PS: innodb 每次提交事务，为保证数据持久化到磁盘，需要调用一次 fsync 告知文件系统，将可能存在缓存中的数据刷新到磁盘上，而 fsync I/O 操作会占用大量资源，响应较慢，若每次事务都 fsync 那么这个又是个瓶颈，于是就有了 group commit 从名字我们可以看出批量提交。但是 MySQL5.0后处于分布式架构考虑为了保证 innodb 和 mysql 日志顺序一致性，innodb 被迫放弃了 group commit
(3) 对数据库进行分区分表操作，减少单表体积
(4) 使用 nosql 等辅助解决方案如 redis mongodb memcache等
(5) 使用数据库连接池
(6) 使用中间件做数据拆分和分布式部署
|