MySQL 性能优化

前言

一开始标题命名的sql优化，后来改成了性能优化，之所以这样，是因为我们需要优化sql，是为了提高数据库系统性能，而如果仅仅着眼于sql，不去看表结构，业务逻辑，不评估数据量，不控制增删改查的复杂度与频繁程度，恐怕性能优化只能是隔靴搔痒

表结构设计

我们拿到一个需求，需要存储数据时，就会涉及到表结构设计
提到表结构设计，首先都会考虑，怎样以尽量少的表，尽量少的字段实现业务需求，同时也想保证一定的可扩展性，控制表的数据量级
然后就是具体的字段设计 索引设计 多表关联关系等

字段设计

基本字段

字段设计，首先会有几个几乎每个表都有的字段 主键ID 创建时间 更新时间等
主键ID没啥好说的 考虑到扩展性，一般用bigint(20) 然后自增长 非负数 设为主键 不为空
CREATE TABLE aaa(id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘表注释’;
然后是创建时间、更新时间
如果没有特殊需求，这两个时间一般都是取系统时间
入库时直接sql中设为now()即可
如果有特殊需求，比如希望数据按唯一索引覆盖更新而又不想覆盖创建时间
想要数据更新时自动更新更新时间等 可参考时间字段智能更新.

业务字段设计

1、允许为空，允许为空特别是在后期增加字段时，避免因改表导致以前的系统插入有问题
2、设置默认值，设置默认值是因为为空的字段，索引会失效，所以一般都设置一个默认值或者空字符串
3、注意字段值大小 在保证业务可能最大值的情况下，能小则小 比如人名 varchar(20)戳戳有余了，能用tinyint不用int 能用int不用bigint varchar类型要合理限制长度，日期类型尽量使用timestamp，只记录到日的日期用date类型不要用字符串
4、不同表相同字段含义的尽量同名，反之不同含义的字段尽量不要同名，便于理解

索引设计

1、B-tree与Hash
一般都会选择B-tree类型，虽然hash的索引结构可以一次定位，检索效率高，但限制很多，由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，不能进行范围查询，只能用"=",不支持> < IN ；检索是不能避免排序操作；组合索引不能利用部分索引字段查询；Hash索引任何时候都不能避免表扫描；Hash索引在大量数据值的hash值相同时，检索效率也会降低。
2、索引应选择辨识度高的字段，可以通过该字段屏蔽掉大量无需计算的数据
3、组合索引使用时需注意索引顺序
4、可以合理利用唯一索引解决业务数据重复问题
5、设置索引的字段尽量不要存在null值
6、索引设置后，多进行查询速度检测

表拆分

复杂的业务逻辑 数据通常需要进行拆分，将公共属性抽离出来，减少数据冗余
1、拆分时的关联关系，建议使用字段逻辑关联，不要用外键强制关联。虽然外键有阻止执行及级联执行的特性，可以保证数据的完整性与关联性，杜绝数据冗余；但在实际业务处理中，主表的调整会波及一大片从表，稍有不慎就会让业务处于瘫痪状态，且数据维护难度增大，不利于业务拓展。且各种数据库的DML操作都需要先检查数据库的外键约束，导致性能下降。
2、数据量增多引起的单库多表或多库多表数据拆分