数据库优化

以Mysql为例，主要从表设计，架构设计，SQL优化三个方面整理的一些数据库优化思路。

表设计

设计思路

a.进行需求分析，梳理业务流程，识别业务实体，明确数据库表的功能和目标。
b.确定各个实体的属性，建立各实体之间的关系，包括一对一，一对多，多对多等等。
c.尽量遵循数据库三范式（列不可分割，属性完全依赖主键，属性之间不相互依赖）进行具体的设计。适当时候可以反范式设计，比如通过个别冗余的字段来减少联表查询，以空间换时间。

设计准则

1.命名规范

数据库表名、字段名、索引名需要命名规范，一般来说，采用和业务相关的小写英文，中间用下划线分割，让别人一看就知道想表达什么意思，可读性高。

2.选择合适的字段类型

a.尽可能选择存储空间小的字段，比如整数类型，从tinyint，smallint，int，bigint从小到大选择。
b.涉及到金额价格的字段，用decimal。
c.字符串如果是定长的，使用char。
d.如果存储的字符串很大，考虑使用text，单独使用一张表存储。或者使用其他存储手段。

3.添加一些通用的字段

a.一般来说创建人，创建时间，修改人，修改时间这些字段是每个表必备的。
b.设置逻辑删除的字段，尽量考虑逻辑删除而非物理删除，方便回滚数据恢复。

4.主键要与业务无关，一般使用自增的字段

5.禁止使用外键，外键存在性能低，并发死锁的问题。外键的概念应该在代码层面解决

6.索引的设置

a.评估表数据量，如果一张表数据量只有几十一百行，就没必要设置索引。
b.频繁作为查询，排序，与其他表关联的字段应该设置索引。
c.区分度不高的字段尽量不要加索引。
d.频繁更新的字段不要加索引。
e.设置索引的字段要not null，实在要为空，设置默认值
f.设置组合索引的时，遵循最左匹配原则，使用最频繁的字段越靠前面。如果能利用到覆盖索引最好。组合索引的列不能太多，一般不超过4个。

架构设计

当数据量越来越大的时候，索引文件也会越来越大，当数据库服务器无法缓存所有索引文件只能从磁盘读取的时候，我们查询性能也必然降低。这种因为数据量变大产生的性能瓶颈可以使用分库分表，读写分离的办法来解决。

分库分表

1.垂直拆分

垂直拆分通常是指通过业务维度拆分，把不同业务的数据拆分到不同的库中去，以此来达到数据拆分的目的。

2.水平拆分

水平拆分通常是指根据单表数据维度，指定一个拆分规则，把特定规则下的数据拆分到不同的数据库和不同的表中去。例如对userId，createTime指定。

3.做主从读写分离

https://segmentfault.com/a/1190000038791015

SQL优化

索引失效

默认我们已经在表设计阶段设置好了索引的情况下，未能编写正确的SQL造成了索引失效，主要有以下几种情况：

1.查询条件包含or可能会造成索引失效

如果or的条件列都加了索引，考虑拆分成两条SQL，分别去查询。

2.like查询以通配符%开头

3.在索引列上进行一些函数操作

把函数的逻辑移到右边去

4.对索引列使用了运算符号

去代码层面解决

5.索引列使用了!=

6.查询条件没有按照最左匹配原则

顺序不一致
最左字段缺失
范围查询
!=条件

join或子查询过多

join的执行流程是，先把驱动表读到线程内存join_buffer中，然后扫描被驱动表，把被驱动表中的数据和join_buffer中的数据对比，满足条件作为结果集返回。
a.使用小表作为驱动表，大表作为被驱动表，小表驱动大表，减少匹配次数。具体哪个是驱动哪个是被驱动可以看下SQL执行计划。被驱动表关联字段要加上索引。
b.join关联尽量控制在3个以内，如果需要关联更多表实现的业务逻辑在代码层处理。
c.子查询尽量优化成join或者创建临时表，禁止多层嵌套子查询。

in后面元素过多

in后面元素过多的情况下，建议分为500个一组去执行。禁止使用in+子查询，子查询的结果数量不可控。