MySQL大数据时数据库性能优化简谈

1 概要

随着MySQL中数据量越来越大，我们不得不面临大数量中如何进行数据库优化的问题，下面本文将对“MySQL性能优化”进行简述，提供一定解决检索慢、写入慢、难以维护问题。

2 选择存储引擎

2.1 MySQL中的存储引擎

MySQL对数据库有多种存储引擎，目前我们推荐的两种引擎为：InnoDB与MyISAM。InnoDB和MyISAM有着不同的应用场景。

在MySQL5.5版本开始InnoDB已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。

2.2 引擎区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以InnoDB检索速度并不如MyISAM快，如果使用InnoDB，最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

（以上InnoDB和MyISAM区别内容转自知乎某位答者，对内容稍微做了修改）

那么我们又该如何选择存储引擎呢？主要看以下几方面：

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM。

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写并且频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难。

3 增加索引（解决检索慢问题）

当单表数据量达到百万级别，我们的检索速度就已经变得慢起来了，比如一个User表中数据量有800万，我们根据“phone_number(手机号)”进行检索，大概检索速度是2min+左右或者更高，这对于用户来说是一件无法忍受的事情（这至少就意味着登录时间大概需要两分钟）。那这个时候比较合适的方案就是增加索引（INDEX）了。

索引的主要功能是：增加读取速度，但索引会一定程度的减慢写入速度。

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

比如我们为User表的“phone_number”增加索引之后，检索速度将会由2min+ 缩减到0.8s左右，速度提升可见一斑。

那么索引是越多越好吗，如果可以提升速度是不是可以所有字段都加上索引？显然不是的：首先索引会占用物理内容，其次索引过多会大大减慢写入速度，所以我们要尽量避免不被检索的字段增加索引。

索引的优点：

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组(group by)和排序(order by)子句进行数据检索时，可以显著减少查询中分组和排序的时间。

索引的缺点：

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

MySQL中索引的类型也有多种，例如：FULLTEXT（全文索引）、HASH、BTREE。这些索引的应用范围稍有不同，在此只是简要说明，不做过多介绍，之后会单独分享。

索引的分类也有“单一列索引”和“联合索引”，单一列的索引如上述对“phone_number”一列的索引，下面简单介绍一下联合索引：

联合索引是对多个字段增加一个索引，这种情况一般所用于：比如频繁多条件检索时，可以增加联合索引，比如我们业务需要多次对“订单表”中："user_name"、"order_type"进行查询(select * from order where user_name = 'zhangsan' and order_type = 'Weixin') ，我们可以对"user_name"和"order_type"字段增加联合索引，但是如果我们查询语句没有出现其中一个条件，或多出一个其他字段的检索条件时将不会用到联合索引，如：

• select * from order where user_name = 'zhangsan'（不会用到联合索引）

• select * from order where user_name = 'zhangsan' and order_type = 'Weixin' and order_id='906235468'（不会用到联合索引）

4 数据库架构（集群模式、主从模式）

4.1 集群模式（增加性能、可靠性）

遇到性能问题，我们首先想到的是同时使用多台物理机架构数据库集群(cluster)，这样不但增加了性能，也可以防止数据库挂掉，同时增加了物理空间，但这样往往面临着物理机、硬件的的资源局限问题。

4.2 主从模式（读写分离、降低读写压力）

可以将数据库配置为主从模式（master/slave），一主多从，master(主)数据库做写入功能，slave(从)数据库做读取功能，达到读写分离的效果。

为什么使用读写分离策略就能降低读写压力呢？这涉及到增加索引的问题，读写分离能解决增加多个索引会影响写入速度的问题。

在解决数据同步问题之后，我们可以为slave(从)数据库增加多个索引，由于master(主)数据库主要职责为写入数据，而slave(从)做读取功能，所以可以为slave(从)增加多个索引增加读取速度，而不影响master(主)的写入速度。

在Java中我们也可以使用Spring的@Qualifier注解进行区别注入，达到指定写入数据时用master(主)数据库，读取数据时用slave(从)数据库的功能。

5 分库分表（解决访问量大情况）

分表，最直白的意思，就是将一个表结构分为多个表，然后可以再同一个库里，也可以放到不同的库。
当然，首先要知道什么情况下，才需要分表。个人觉得单表记录条数达到百万到千万级别时就要使用分表了。

5.1 纵向分表

将本来可以在同一个表的内容，人为划分为多个表。（所谓的本来，是指按照关系型数据库的第三范式要求，是应该在同一个表的。）

分表依据：根据数据的活跃度进行分离，（因为不同活跃的数据，处理方式是不同的）

对于一个商品表：商品名称，商家id，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好实时性的数据，我们把它叫做冷数据。而商品的浏览量，购买量、评论数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。

这样纵向分表后，首先存储引擎的使用不同，冷数据使用MyIsam可以有更好的查询数据。活跃数据可以使用Innodb ,可以有更好的更新速度。
其次，对冷数据进行更多的从库配置，因为更多的操作时查询，这样来加快查询速度。对热数据，可以相对有更多的主库的横向分表处理。
其实，对于一些特殊的活跃数据，也可以考虑使用memcache ,redis之类的缓存，等累计到一定量再去更新数据库。或者mongodb 一类的nosql数据库，这里只是举例，就先不说这个。

5.2 横向分表

横向分表，可以按字面意思理解，将一张表分为多个结构一样、数据不同的表，存储到一个或多个数据库中，表的内容可以按照不同规则来分，这样所有的物理上来说是在不同的表中，可是在查询、修改时逻辑上是在一个表里的。

分表理由：根据数据量的规模来划分，保证单表的容量不会太大，从而来保证单表的查询等处理能力。

同上面的例子，商品的数量达到很大时候，就应该采取横向分割来降低每个单表的压力，来提升性能。例如商品的冷数据表，当同时有100万个用户在浏览时，如果是单表的话，会进行100万次请求，而如果分为100个表后，就可能是每个表进行1万个数据的请求，这样压力就降低了很多很多。

在实际中，20亿数据并不算是特别大的数据，我们依然可以采取分库分表的形式，利用主从模式优化数据库。

6 冗余策略（防止单点宕机）

比如我们将一张1w条数据的表进行分库分表，如何防止某一个数据库突然宕机时，不影响查询结果？

解决方案可以为：

第一台数据库表内存储第0~2000条数据；

第二台数据库表内存储第1000~3000条数据；

第三台数据库表内存储第2000~4000条数据；

第四台数据库表内存储第3000~5000条数据；

比如第二台数据库宕机了，那么第二台数据库中的数据在第一台和第三台中存在，就不会影响到查询结果了。

以上就是简谈大数据时MySQL数据库性能优化策略，如内容如果有所纰漏敬请指出。

其实优化的方案还有很多，而且每个策略细分还有很多细节，这次主要偏简介的方式介绍这些策略，后续会单独分出多个分享介绍，大家敬请期待！