数据库的那些事（全是干货）

谈到数据库，大家第一想法就是怎么去优化，怎么让查询操作更快。我认为最好的方式就是从开始数据库设计的时候就要尽量考虑周全。如果不幸是个老项目，就得从优化入手了。接下就从设计和优化谈一下我的一些认识和经验。

1：数据库的设计

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。数据库的设计包含数据库架构和业务表的设计。

1）数据库架构

根据不同的数据量和访问量，来设计不同的架构。适合自己的才是最好的。

• 单实例：数据读取和写入都是一个数据库实例。（备份实例不算在内）。这个适用于小型的企业内部系统。缺点是只适合数据量少的场景，优点是能达到数据的强一致性。

  

• 垂直拆分，多实例。不同的业务走不同的实例。同样也是适用于单个业务，数据量不大，并且每个业务相对独立，不产生关联。

• 读写分离，主从架构。通过主从结构，主库抗写压力，通过从库来分担读压力。适用于写少读多，数据一致性的实时性要求不高的应用。

• 主从，集群结构。适用于写多，读也多的应用。实现是最为复杂的一种，需要考虑数据如何路由，后期扩容也相对麻烦。需要设计初期在理解业务的基础上，选择好合适的路由策略。例如range，hash等

• 云数据库：阿里云rds等。适合没有专业数据库运维团队的公司，使用非常方便和简单。

2）业务表的设计

常见业务表分类：

（1）配置表

这种表通常存放业务一些基础的配置信息或者字典信息。表的数据量一般都比较小，修改变化的操作不太频繁，通常都是Select查询操作。

（2）状态表

这种表通常存放在业务系统中实体读象的状态信息，常见的有用户信息表，订单信息表等。这种表的数据量与实体读象的规模有直接关系，比如一个APP有多少注册用户，通常这个APP的用户表都会有多少条记录。状态表的变化通常比较频繁，而且Insert、Update、Select操作都会有，Delete操作是否有，通常会根据业务情况的规定决定。

（3）日志表

这种表通常用来记录业务系统中某种实体的状态信息，常见的有用户登录表、充值信息记录表等。这种表的数据规模通常比较大，而且如果业务状态变化频繁，记录的变化信息比较多，这种表的数据量和插入性能都要求比较高。日志表的操作，通常会以Insert操作为主，个别业务会对日志表进行查询。MySQL五种特殊需求架构中的高性能写入架构，主要就是应用这种表的需求。

（4）归档表

这种表，是将上面三种OLTP业务表的数据进行归档或者冷热分离的表。对线上业务三类表进行数据归档、冷热分离，一方面可以控制线上业务表的数据规模，保证业务表性能；另一方面进行归档后，可用于对归档历史数据进行更好的查询反映和支持。归档表的数据量大小与对应的线上表大小、归档周期有关。归档表的操作，除了归档过程的数据加载外，主要就是Select查询操作了，归档后就算是只读表。

（5）统计数据表

统计数据表，是指业务有离线统计分析需求时，需要将各种线上表和归档表的数据，通过ETL过程流转到线上OLAP统计分析系统中的原始数据表。这类表通常数据量会非常大，一个OLAP统计分析平台会汇总多个线上业务系统的数据进行统计分析。统计数据表的操作，除了数据流转动作外，主要就是各种统计分析程序的访问计算。

（6）统计结果表

统计结果表是在业务有离线统计分析需求时，各种统计分析过程访问统计数据表中的数据，按照一定的逻辑进行统计分析后的结果数据。这种统计结果数据，通常数据量会比较小。统计结果表的操作，处理结果流转动作外，主要就是供访问接口进行Select查询。

对业务表类型的梳理，可以对所有的业务系统进行一个大体的划分，做到心中有数。

下面是设计表的一些经验：

• 将字段很多的表分解成多个表：对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来，因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

• 增加中间表：对于需要经常联合查询的表，可以建立中间表以提高查询效率。

• 增加冗余字段：合理的加入冗余字段可以提高查询速度。（冗余字段会导致一些问题，比如，冗余字段的值在一个表中被修改了，就要同步关联的表，否则会导致数据不一致。这要根据实际情况，平衡数据库性能，进行冗余字段的设计。）

• 所有字段均定义为NOT NULL，除非你真的想存储null。
  

• 提前做好数据量的预估，进行分表设计。不要等需要拆分时再拆，一般把表的数据量控制在千万级别。当单表数据量达到一定程度时（MySQL5.x时代的性能拐点则为1KW - 2KW行级别，具体需根据实际情况测试），为了提升性能，最为常用的方法就是分表。分表的策略可以是垂直拆分（比如：不同订单状态的订单拆分到不同的表），也可以是水平拆分（比如：按月将订单拆分到不同表）。如果在业务层分表，会将逻辑变得复杂，而且分散。可以引入分表的中间件屏蔽分表后的细节，让业务层像查询单表一样查询分表后的数据。比如Mycat。（访问量不大，但是表数据很多的表，我们可以采取分区表，实现起来也比较简单）

• 选择统一的字符集。MySQL采用“阶梯”式的方式来设定字符集默认值，每个数据库，每张表都有自己的默认值，它们逐层继承，最终最靠底层的默认设置将影响你创建的对象。不同字符集和校对规则之间的转换可能会带来额外的系统开销，影响数据库的性能。

• 合理的设置主键和索引。

  主键分自增主键和业务主键。

  自增主键：写入、查询效率和磁盘利用率都高，但每次查询都需要两级索引，因为线上业务不会有直接使用主键列的查询。

  业务主键：写入、查询效率和磁盘利用率都低，但可以使用一级索引，依赖覆盖索引的特性，某些情况下在非主键索引上也可以实现1次索引完成查询

  一般情况下都是采用业务主键。

  索引大概分为三类。

  主键索引：InnoDB会自动在表的主键上创建索引，数据结构使用B+Tree。

  非主键索引：非主键列上的索引为二级索引(因为一次查询需要查找两个索引树)

  联合索引：联合索引也叫多列索引，索引结构的key包含多个字段，排序时先第一列比较，如果相同再按第二列比较，以此类推。
  

2：数据库的优化（mysql）

说起mysql优化，一定要了解一下mysql原理，这样才能深入的理解那些sql规则。下图展示了MySQL的逻辑架构图。

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

1）sql的优化。通过explain和show profiles进行执行计划分析，找出问题，进行针对性的优化。其中创建高效索引是最有效的一个手段。

• 多列索引和索引顺序：

  出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。

  在选择性高的字段上建立索引，可以让MySQL在查询时过滤掉更多的行。对于多列索引，哪个索引字段在前面，取决于索引的选择性的高低。选择性高的索引排在前面，有利于提高查询效率。例如联合索引（user_group_id，trade_amount）用户的群组肯定比订单的交易金额的选择性高。

• 覆盖索引：如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

• 优化关联查询：以小表驱动大表。

• 子查询尽量换成join。这是因为join，MySQL不需要在内存中创建临时表来完成这个逻辑上的需求。
  

• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

• 优化LIMIT分页：一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。考虑下面的查询：

  修改前：SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

  修改后：

  SELECT film.film_id,film.description

  FROM film INNER JOIN (

  SELECT film_id FROM film ORDER BY title LIMIT 50,5

  ) AS tmp USING(film_id);

• 优化UNION：除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。

• 避免导致索引失效的写法

  （1）负向条件查询不能使用索引（not in/not exists都不是好习惯）

  （2）前导模糊查询不能使用索引（like'XX%'）

  （3）数据区分度不大的字段不宜使用索引

  （4）在属性上进行计算不能命中索引

  （5）复合索引最左前缀不满足

• 强制类型转换会全表扫描

• 如果明确知道只有一条结果返回，limit 1能够提高效率
  

2）合理设置mysql的部分参数，达到最高效。

• thread_pool_size：如果主引擎(primary storage engine)为InnoDB,thread_pool_size最佳设置可能在16和36之间，最常见的优化值倾向于24到36。

• thread_pool_stall_limit：用处理被阻塞和长时间运行的语句，确保服务器不完全被阻塞。设置过长会导致线程被阻塞，引起性能问题。

• tmp_table_size:通过设置tmp_table_size选项来增加一张临时表的大小，例如做order by ,GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。