mysql 海量数据架构,MySQL海量数据优化（理论+实战） 吊打面试官

前言

朋友们，又见面了，上篇文章咱们讲到MySQL分库分表的方法，这篇文章咱们就针对上一篇文章模拟在MySQL中海量数据的优化方法，文章干货较多，建议你点赞、评论、收藏、关注起来慢慢看

提示：以下是本篇文章正文内容，案例仅供参考

一、准备表数据

咱们建一张用户表，表中的字段有用户ID、用户名、地址、记录创建时间，如图所示

OK，接下来准备写一个存储过程插入一百万条数据

插入完后咱们看看数据条数

二、优化方式

1.分页查询优化

OK，咱们看下分页limit到一定值时的耗时是多少

limit 1000时

limit 10000时

limit 100000时

limit 1000000时

可以看到limit值越大，耗时越长，这还只是一百万数据，要是千万级、亿级呢？

OK不废话，咱们马上进行分页优化

子查询优化

可以看到比起之前 limit 1000000时的0.218s 效率提高了很多

使用JOIN分页

可以看到比起之前 limit 1000000时的0.218s 效率也同样提高了很多

使用前一次查询的最大ID

可以看到这种方法效率最高，但依赖于需要知道最大ID，这种适合点击下一页查询(类似于滚动加载数据)的场景

通过伪列对ID进行分页

然后可以开启多个线程去进行最高效率查询语句的批量查询操作

0~10000，10001-20000....

这样子的话可以快速把全量数据查询出来同步至缓存中。

分页优化总结：使用前一次查询的最大ID进行查询优化是效率最高的方法，但这种方法只适用于下一页点击的这种操作，对于同步全量数据来说建议的方式使用伪列对ID进行分页，然后开启多个线程同时查询，把全量数据加载到缓存，以后面试官问你如何快速获取海量数据并加载到缓存你该知道怎么回答了吧。

2.普通索引优化

先来看没索引优化的情况下的查询效率

可以看到这时没用索引的情况，用了0.305S接下来看看加了索引后的结果

普通索引优化

只需要0.024S，我们可以EXPLAIN看下

可以看到使用了普通索引后查询效率明显增加

3.复合索引优化

复合索引什么时候用？为什么要用？

围绕着这两问题，咱们先来说说复合索引什么时候用

单表中查询、条件语句中具有较多个字段

使用索引会影响写的效率，需要研究建立最优秀的索引

我们这里建议一个复合索引

MySQL建立复合索引时实际建立了(user_name)、(user_name,address)、(user_name,address,create_time)三个索引,我们都知道每多一个索引，都会增加写操作的开销和磁盘空间的开销，对于海量数据的表，这可是不小的开销，所以你会发现我们在这里使用复合索引一个顶三个，又能减少写操作的开销和磁盘空间的开销

当我们select user_name,address,create_time from t_user where user_name=xx and address = xxx时，MySQL可以直接通过遍历索引取得数据，无需回表，这减少了很多的随机IO操作。所以，在真正的实际应用中，这就是覆盖索引，是复合索引中主要的提升性能的优化手段之一。

4.SQL查询优化

避免使用OR，看看例子

可以看到这条语句没有使用到索引，是因为当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

不要使用like '%xx'  %在左边时索引失效

3. 使用复合索引时没有遵循最左匹配原则

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如，利用最左边前缀)时发生。没有值说明没有利用最左前缀原则

再来看个使用了最左前缀的例子

4. 不要让数据类型出现隐式转化

可以看以下两个例子

5. 不要在索引字段上使用not，<>，!=，一样会导致索引失效

6. 分解关联查询例如这条语句

可以分解成

7.小表驱动大表

即小的数据集驱动大的数据集。如：以t_user，t_order两表为例，两表通过 t_user的id字段进行关联。

5.事务优化

首先了解下事务的隔离级别，数据库共定义了四种隔离级别：

Serializable：可避免脏读、不可重复读、虚读情况的发生。(串行化)

Repeatable read：可避免脏读、不可重复读情况的发生。(可重复读)

Read committed：可避免脏读情况发生(读已提交)。

Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

可以通过 set transaction isolation level 设置事务隔离级别来提高性能

6.数据库性能优化

开启查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表(不要理解成table，可以认为是类似于HashMap的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同(例如：空格、注释)，都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表(数据或结构)发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存 　　如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗复制代码

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

. 批量插入代替循环单条插入　 . 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适　 . 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存　　最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。　　当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

7.系统内核参数优化

这些参数可按照自己的实际服务器以及数据库的大小进行适当调整，主要起参考作用

8.表字段优化

很多系统一开始并没有考虑表字段拆分的问题，因为拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下，而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

下面直接看下如何去优化字段

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED

单表不要有太多字段，建议在15以内

尽量使用TIMESTAMP而非DATETIME

使用枚举或整数代替字符串类型

VARCHAR的长度只分配真正需要的空间

避免使用NULL字段，很难查询优化且占用额外索引空间

用整型来存IP

9.分布式场景下常用优化手段

升级硬件

Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使用SSD，都能显著提升MySQL性能

读写分离

也是目前常用的优化，从库读主库写，一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离

使用缓存

缓存可以发生在这些层次：

MySQL内部：在系统内核参数优化介绍了相关设置

数据访问层：比如MyBatis针对SQL语句做缓存，而Hibernate可以精确到单个记录，这里缓存的对象主要是持久化对象Persistence Object

应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象Data Transfer Object

Web层：针对web页面做缓存

浏览器客户端：用户端的缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现，目前主要有两种方式：

直写式(Write Through)：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单，同步好，但效率一般。

回写式(Write Back)：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。

水平拆分：我的上篇文章有讲到，这里不再赘述。