msyql优化篇

---

前言

最近整理了一下关于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入侵，扩展性好，性能较差，成本高

总结

加上我画的图：