目录
5 性能调优
5.1 表设计
5.2 字段类型的选取
5.2.1 针对数值类型
5.2.2 字符类型
5.2.3 时间类型
5.2.4 小技巧：快速修改表结构
5.2.5 pt-online-schema-change在线更改表结构
5.2.6 MySQL5.6在线DDL更改表测试
5.3 采用合适的锁机制
5.3.1 表锁
5.3.2 行锁
5.3.3 InnoDB引擎与MyISAM引擎的性能对比
5.4 选择合适的事务隔离级别
5 性能调优
5.1 表设计
5.2 字段类型的选取
- 选择的原则：保小不保大，能用占用字节少的字段就不用大字段。
- 依据：更小的字段类型占用的内存就更少，占用的磁盘空间和磁盘I/O也会更少，而且还会占用更少的带宽。
5.2.1 针对数值类型
- 数值类型
最小值和最大值代表的是宽度
2. 录入手机号可用BIGINT
一般程序的字符集是gbk或utf8, gbk占用2字节，utf8占用3字节，那么11×3就是33字节，而bigint(20)宽度为20，只占用8字节，从性能上考虑，应该设置为bigin
3. 年龄、用数字表示的状态等均可采用TINYINT
采用tinyint完全可以满足需要，int占用的是4字节，而tinyint才占用1个字节。
5.2.2 字符类型
- char(N)用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。
- varchar(N)用于保存可变长度的字符串，长度最大为65535，只存储字符串实际需要的长度（它会增加一个额外字节来存储字符串本身的长度）, varchar使用额外的1～2字节来存储值的长度，如果列的最大长度小于或等于255，则使用1字节，否则就是用2字节。
- char和varchar跟字符编码也有密切联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。
5.2.3 时间类型
- 日常建表时应优先选择timestamp类型
- 关闭自动更新：更改默认值为NULL
- 在MySQL5.6中，year(2)类型会自动转换为year(4)，如12-- >2012
5.2.4 小技巧：快速修改表结构
- 修改varchar类型
创建新表--->锁表--- >复制表；Slave 也要执行一遍。
5.2.5 pt-online-schema-change在线更改表结构
存在风险
5.2.6 MySQL5.6在线DDL更改表测试
MySQL5.6之后执行Alter table的规则：
- 执行alter table表时，对该表的增、删、改、查均不会锁表。
- 如果在这之前，该表有被访问，那需要等其执行完毕后，才可以执行alter table，否则会存在锁表现象。
- 在凌晨上线时，一定要观察一下，此时此刻是否有某个慢SQL在对该表进行操作，以免改表时出现锁等待现象。
5.3 采用合适的锁机制
MySQL的锁形式:
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。MyISAM引擎属于这种类型。
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB引擎属于这种类型。
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。NDB属于这种类型。
5.3.1 表锁
MyISAM存储引擎只支持表锁：
- 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
- 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
5.3.2 行锁
行锁：
- InnoDB存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁。
- 在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重的性能问题，甚至拖垮数据库，这时需要通过设置合适的锁等待超时阀值参数innodb_lock_wait_timeout来解决，一般设置为100秒即可。
行锁转表锁：
- 只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁。
死锁：
- 两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
- 发生死锁后，InnoDB一般都能自动检测到，它会让一个事务释放锁并回退，另一个事务则获得锁，继续完成事务。死锁是无法避免的，我们可以通过调整业务的逻辑来尽量减少死锁出现的概率。
5.3.3 InnoDB引擎与MyISAM引擎的性能对比
- 建议尽量不要混合使用多种存储引擎，这样容易带来更复杂的问题。
- 从测试的结果来看，InnoDB每秒处理的数据为1549个，而MyISAM仅仅为154个。
- MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。试想一下，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL该如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般要比读请求重要。这也正是MyISAM表不太适合有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
- InnoDB用于事务处理应用程序，具有众多特性，包括支持ACID事务、行锁等。如果应用中需要执行大量的读写操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。对于MyISAM引擎，在MySQL5.5版本里Oracle公司支持的已经很少了，以后内存数据库是一种趋势，所以建议优先选择InnoDB引擎。
5.4 选择合适的事务隔离级别
- 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
- ORACLE/SQL SERVER的默认隔离级别是Read Committed（读提交），MySQL的默认隔离级别是Repeatable Read（可重复读）
TODO
参考：
- 《MySQL管理之道：性能调优、高可用和监控》第二版