文章目录
前言
最近整理了一下关于mysql相关知识，本篇会从多个维度来列举关于mysql优化知识点。
一、开发规范
由于单纯mysql的优化有很大的局限性，我们更多希望是制定相关开发的规范来尽可能多的规避各种问题。
1.基础规范
表存储引擎必须使用InnoDB
InnoDB支持事务、外键、行锁等优点，强制要求使用InnoDB。
索引都选择B+TREE
B+TREE支持排序和范围检索，为了统一则尽可能使用B+TREE，强制规范。
表字符集选择
表字符集默认使用utf8，必要时候用utf8mb4，强制规范!
utf8通用，无乱码，汉字3字节，英文1字节；
utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它；
禁止使用存储过程，视图，触发器等
对数据库性能影响较大，高并发下容易成为数据库瓶颈；
调试，排错，运维，迁移都比较困难，扩展性差，数据垂直和水平分片对其影响较大。
建议规范！
表关联查询不能超过3个表
阿里巴巴开发规范要求是不能超过3个表；
复杂sql在数据量大时性能是瓶颈，mysql优化器也会选择错误索引。
建议规范！
2 命名规范
库名，表名，列名必须小写，采用下划线分割
强制规范!
主键索引 pk_ 唯一索引 uk_ 普通索引 idx_
强制规范!
备份表后缀必须带上日期
强制规范!
3 表设计规范
表必要字段
表必须要有 主键 创建时间 修改时间这3个字段
强制规范!
禁止使用外键
数据完整性给服务层实现,外键会使表之间耦合，会影响update/delete操作，有死锁问题，高并发下容易成为数据库瓶颈
强制规范!
冷热数据分离
建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据（垂直拆分）
强制规范!
4 列设计规范
整数型类型选择
根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节
强制规范!
字符类型大小选择
字段大小按照实际大小定义，如手机号定义为20位等，要考虑存储空间问题
强制规范!
字段定义为NOT NULL 尽量设默认值
NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化；
NULL需要更多的存储空间 NULL只能采用IS NULL或者IS NOT NULL。
强制规范!
5 列设计规范
控制索引数量
单张表索引数量建议控制在5个以内，组合索引字段数不建议超过5个；
太多索引会影响写性能，在高并发下会成为瓶颈；
异常复杂的查询需求，可以选择ES等更为适合的方式存储。
强制规范!
索引创建原则
不建议在频繁更新的字段上建立索引；
值分布很少的字段不适合建立索引.如性别；
强制规范!
连表查询原则
进行JOIN查询，被JOIN的字段必须类型相同，并建立索引；
JOIN字段类型不一致，即便创建了索引也会导致全表扫描；
强制规范!
遵循最左匹配
联合索引 (key1,key2,key3)，相当于创建了（key1）、(key1,key2)(key1,key2,key3)三个索引，这就是最左匹配原则。
强制规范!
6 SQL规范
禁止使用select *，只获取必要字段
select *会增加cpu/io/内存/带宽的消耗；
指定字段能有效利用索引覆盖，减少回表问题；
指定字段查询，在表结构变更时，能保证对应用程序无影响 。
强制规范!
insert必须指定字段
禁止使用insert into T values()；
强制规范!
禁止在where条件列使用函数或者表达式
会导致不能命中索引，全表扫描。
强制规范!
禁止全模糊查询
查询以及%开头的模糊查询。
强制规范!
同一个字段上的OR必须改写问IN
IN的值尽量少于200个
强制规范!
应用程序必须捕获SQL异常
强制规范!
程序的SQL需要在mysql上用执行计划检验是否有走索引
强制规范!
二、性能优化
1 字段优化
整数型数据要选择合适类型，如果非负则加上UNSIGNED；
VARCHAR 的长度只分配需要的空间；
避免使用null字段,很难查询优化且占用额外索引空间。
2 表结构优化
单表不要有太多字段,建议在20以内；
多列表垂直拆分；
合理的增加冗余字段；
新建字段一定要有默认值。
3 索引优化
索引建立原则
一般建在where和order by，基数要大，区分度要高，不要过度索引，外键建索引。
值分布很少的字段不适合建立索引
索引创建不能超过5个
杜绝索引重复问题
4 SQL优化
EXPLAIN来查看是否用了索引还是全表扫描；
查询行数时使用 count(*)；
in的值不要超过200个；
使用union all替代union；
连表查询的表不能超过3个；
使用联合索引时注意顺序即最左匹配原则；
利用索引覆盖,5.6支持索引下推；
关注索引失效场景
不满足最左匹配原则；
频繁回表；
索引列上有计算；
索引列上有函数；
字段类型不同；
列对比；
隐式转换；
在索引字段上使用not，<>，！= ,or,like ‘%x’
在写多读少的场景下，可以选择普通索引而不要唯一索引；
不用外键,用程序保证约束；
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况；
可以使用force index()防止优化器选错索引；
join表时on上条件列推荐加索引且字段一致；
避免隐式转换，如字符串类型必须要带单引号；
选择合理驱动表即小表驱动大表；
5 升级硬件
加内存换固态硬盘等。
6 缓存
对于读多写少场景下推荐使用缓存，尽可能减少DB压力提高吞吐量。
7 复杂检索
对于复杂检索等业务场景，推荐使用ES等。
8 分表分库
待补充。。
9 读写分离
做主从复制来实现读写分离，主写从读，尽可能提高DB吞吐量，具体请参考主从复制；
相关中间件
客户端模式：性能好，成本低，对代码有入侵，对于复杂业务扩展性差
代理模式：对代码0入侵，扩展性好，性能较差，成本高
总结
加上我画的图：
