数据库访问量很大时，如何做优化

你好！如果有大量的访问用到调取到数据库时，往往查询速度会变得很慢，所以我们需要进行优化处理。

优化从三个方面考虑：

SQL语句优化、

主从复制，读写分离，负载均衡、

数据库分库分表。

一、SQL查询语句优化

1、使用索引

建立索引可以使查询速度得到提升，我们首先应该考虑在where及orderby，groupby涉及的列上建立索引。

2、借助explain（查询优化神器）选择更好的索引和优化查询语句

SQL的Explain通过图形化或基于文本的方式详细说明了SQL语句的每个部分是如何执行以及何时执行的，以及执行效果。通过对选择更好的索引列，或者对耗时久的SQL语句进行优化达到对查询速度的优化。

3、任何地方都不要使用SELECTFROM语句。

4、不要在索引列做运算或者使用函数

5、查询尽可能使用limit来减少返回的行数

6、使用查询缓存，并将尽量多的内存分配给MYSQL做缓存

二、主从复制，读写分离，负载均衡

目前大多数的主流关系型数据库都提供了主从复制的功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库这一功能，实现数据库的读写分离，从而改善数据库的负载压力。一个系统的读 *** 作远远多于写 *** 作，因此写 *** 作发向master，读 *** 作发向slaves进行 *** 作（简单的轮询算法来决定使用哪个slave）。

利用数据库的读写分离，Web服务器在写数据的时候，访问主数据库（master），主数据库通过主从复制将数据更新同步到从数据库（slave），这样当Web服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读 *** 作的Web应用可以轻松地读取数据，而主数据库也只会承受少量的写入 *** 作，还可以实现数据热备份，可谓是一举两得。

三、数据库分表、分区、分库

1、分表

通过分表可以提高表的访问效率。有两种拆分方法：

垂直拆分

在主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中。如果一个表中某些列常用，而另外一些不常用，则可以采用垂直拆分。

水平拆分

根据一列或者多列数据的值把数据行放到两个独立的表中。

2、分区

分区就是把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但是数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能。实现比较简单，包括水平分区和垂直分区。

3、分库

分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。

分库解决的是数据库端并发量的问题。分库和分表并不一定两个都要上，比如数据量很大，但是访问的用户很少，我们就可以只使用分表不使用分库。如果数据量只有1万，而访问用户有一千，那就只使用分库。

注意：分库分表最难解决的问题是统计，还有跨表的连接（比如这个表的订单在另外一张表），解决这个的方法就是使用中间件，比如大名鼎鼎的MyCat，用它来做路由，管理整个分库分表，乃至跨库跨表的连接

随着业务的不断发展，数据库中的数据会越来越多，相应地，单表的数据量也会越到越大，大到一个临界值，单表的查询性能就会下降。

这个临界值，并不能一概而论，它与硬件能力、具体业务有关。

虽然在很多 MySQL 运维规范里，都建议单表不超过 500w、1000w。

但实际上，我在生产环境，也见过大小超过 2T，记录数过亿的表，同时，业务不受影响。

单表过大时，业务通常会考虑两种拆分方案：水平切分和垂直切分。

水平切分，拆分的维度是行，一般会根据某种规则或算法将表中的记录拆分到多张表中。

拆分后的表既可在一个实例，也可在多个不同实例中。如果是后者，又会涉及到分布式事务。

垂直切分，拆分的维度是列，一般是将列拆分到多个业务模块中。这种拆分更多的是上层业务的拆分。

从改造的复杂程度来说，前者小于后者。

所以，在单表数据量过大时，业界用得较多的还是水平拆分。

常见的水平拆分方案有：分库分表、分区表。

虽然分库分表是一个比较彻底的水平拆分方案，但一方面，它的改造需要一定的时间；另一方面，它对开发的能力也有一定的要求。相对来说，分区表就比较简单，也无需业务改造。

很多人可能会认为 MySQL 的优势在于 OLTP 应用，对于 OLAP 应用就不太适合，所以，也不太推荐分区表这种偏 OLAP 的特性。

但实际上，对于某些业务类型，还是比较适合使用分区表的，尤其是那些有明显冷热数据之分，且数据的冷热与时间相关的业务。

下面，我们看看分区表的优点：

遗憾的是，MySQL 分区表不支持并行查询。理论上，当一个查询涉及到多个分区时，分区与分区之间应进行并行查询，这样才能充分利用多核 CPU 资源。

但 MySQL 并不支持，包括早期的官方文档，也提到了这个问题，也将这个功能的实现放到了优先级列表中。

在 MySQL 57 中，对于分区表，有个很重大的更新，即 InnoDB 存储引擎原生支持了分区，无需再通过 ha_partition 接口来实现。

所以，在 MySQL 57 中，如果要创建基于 MyISAM 存储引擎的分区表，会提示 warning 。

而在 MySQL 80 中，则更为彻底，server 层移除了 ha_partition 接口代码。

如果要使用分区表，只能使用支持原生分区的存储引擎。在 MySQL 80 中，就只有 InnoDB。

这就意味着，在 MySQL 80 中，如果要创建 MyISAM 分区表，基本上就不可能了。

这也从另外一个角度说明了为什么生产上不建议使用 MyISAM 表。

在使用分区表时，大家常常会碰到下面这个报错。

即分区键必须是主键的一部分。

上面的 opr 是一张 *** 作流水表。其中，opr_no 是 *** 作流水号，一般都会被设置为主键，opr_date 是 *** 作时间。基于 *** 作时间来进行分区，是一个常见的分区场景。

为了突破这个限制，可将 opr_date 作为主键的一部分。

但是这么创建，又会带来一个新的问题，即对于同一个 opr_no ，可插入到不同分区中。如下所示：

这实际上违背了业务对于 opr_no 的唯一性要求。

既然这样，有的童鞋会建议给 opr_no 添加个唯一索引，But，现实是残酷的。

即便是添加唯一索引，分区键也必须包含在唯一索引中。

总而言之，对于 MySQL 分区表，无法从数据库层面保证非分区列在表级别的唯一性，只能确保其在分区内的唯一性。

这也是 MySQL 分区表所为人诟病的地方之一。

但实际上，这个锅让 MySQL 背并不合适，对于 Oracle 索引组织表（ InnoDB 即是索引组织表），同样也有这个限制。

Oracle 官方文档（ >

将所有数据都迁移到mycat中，一共有4个数据库，blog01，blog02，blog_article01，blog_article02。

article，article_tags分别在blog_article01，blog_article02，按照uid进行水平拆分。

user_info表在blog01，link，category，tag在blog02数据库中。

以上就是关于数据库访问量很大时，如何做优化全部的内容，包括:数据库访问量很大时，如何做优化、MySQL 分区表，为什么分区键必须是主键的一部分、mysql里的大表用mycat做水平拆分，是不是要先手动分好，再配置mycat等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！