MYSQL数据库优化-白红宇

MYSQL数据库优化

阅读量：3665 次

发布时间：2019-05-21

本文共 9837 字，大约阅读时间需要 32 分钟。

1. 优化sql语句和索引

1) 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2) 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3) 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

4) 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20。可以这样查询：select id from t where num=10union allselect id from t where num=20

5) 下面的查询也将导致全表扫描：select id from t where name like '%abc%'，若要提高效率，可以考虑全文检索。

6) in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：selectid from t where num between 1 and 3

7) 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：selectid from t where num/2=100。应改为:select id from t wherenum=100*2

8) 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

9) 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10)在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11)很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

12)并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

13)索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

14)应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

15)尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

16)尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

17)任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

18)避免频繁创建和删除临时表，以减少系统表资源的消耗。

19)如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

20)尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

21)尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

22)尽量避免大事务操作，提高系统并发能力。

2. 缓存，memcached,redis，一致性哈希算法

1) 这2种都是基于内存的数据存储系统。Memcached：Memcached是一个高性能的分布式内存对象缓存系统，用于动态Web应用以减轻数据库负载。它通过在内存中缓存数据和对象来减少读取数据库的次数，从而提高动态、数据库驱动网站的速度。Memcached基于一个存储键/值对的hashmap。

2) Redis：redis是一个key-value存储系统。和Memcached类似，它支持存储的value类型相对更多，包括string(字符串)、list(链表)、set(集合)和hash（哈希类型）。与memcached一样，为了保证效率，数据都是缓存在内存中。区别的是redis会周期性的把更新的数据写入磁盘或者把修改操作写入追加的记录文件，并且在此基础上实现了master-slave(主从)同步。

Redis 是一个高性能的key-value数据库。 redis的出现，很大程度补偿了memcached这类key/value存储的不足，在部分场合可以对关系数据库起到很好的补充作用。

Redis支持主从同步（memcached不支持）。主从同步的流程大致如下:

a) 主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。

b) 从服务器(slave)的IO线程从主服务器(binlogdump线程)获取二进制日志，并在本地保存一份自己的二进制日志(relaylog)

c) 从服务器的SQL线程读取本地日志(relay log)，并重演变更事件。

主从同步的作用：

d) 可以作为一种备份机制（因为redis都是用内存存储，重启数据会丢失，所以要备份）。

e) 可以用来做读写分离，均衡数据库负载(主写从读)

f) 当主服务器出现问题时，可以切换到从服务器。

3) 内存使用效率对比：使用简单的key-value存储的话，Memcached的内存利用率更高，而如果Redis采用hash结构来做key-value存储，由于其组合式的压缩，其内存利用率会高于Memcached。

4) 性能对比：由于Redis只使用单核，而Memcached可以使用多核，所以平均每一个核上Redis在存储小数据时比Memcached性能更高。而在100k以上的数据中，Memcached性能要高于Redis，虽然Redis最近也在存储大数据的性能上进行优化，但是比起Memcached，还是稍有逊色。

5) 集群管理的不同：Memcached本身并不支持分布式，因此只能在客户端通过像一致性哈希这样的分布式算法来实现Memcached的分布式存储。下图给出了Memcached的分布式存储实现架构。当客户端向Memcached集群发送数据

之前，首先会通过内置的分布式算法计算出该条数据的目标节点，然后数据会直接发送到该节点上存储。但客户端查询数据时，同样要计算出查询数据所在的节点，然后直接向该节点发送查询请求以获取数据。相较于Memcached只能采用客户端实现分布式存储，Redis更偏向于在服务器端构建分布式存储。Redis支持主从模式，在Redis Cluster中，每个Master节点都会有对应的两个用于冗余的Slave节点。这样在整个集群中，任意两个节点的宕机都不会导致数据的不可用。当Master节点退出后，集群会自动选择一个Slave节点成为新的Master节点。

3. 一致性哈希算法

一致性哈希算法是用来实现分布式缓存存储的，用来解决服务器的负载均衡问题。在分布式数据库中，设计数据分布算法通常需要考虑到几点。

1) 平衡性：平衡性是指哈希的结果能够尽可能分布到所有的分片节点中去，这样可以使得所有的分片节点都得到利用。很多哈希算法都能够满足这一条件。

2) 单调性：是指例如原来有4个分片节点，如果加一个分片节点，则在进行数据重分布时，已有节点的数据只可能继续在已有当前节点待着或移动到新加的节点，绝对不会移动到已有的其他节点。

3) 分散性：在分布式环境中，终端有可能看不到所有的分片数据，而是只能看到其中的一部分。当终端希望通过哈希过程将内容映射到分片节点上时，由于不同终端所见的分片节点范围有可能不同，从而导致哈希的结果不一致，最终的结果是相同的内容被不同的终端映射到不同的分片节点中。这种情况显然是应该避免的，因为它导致相同内容被存储到不同分片节点中去，降低了系统存储的效率。好的哈希算法应能够尽量避免不一致的情况发生，也就是尽量降低分散性。

4) 负载：既然不同的终端可能将相同的内容映射到不同的分片节点中，那么对于一个特定的分片节点而言，也可能被不同的用户映射为不同的内容。与分散性一样，这种情况也是应当避免的，因此好的哈希算法应能够尽量降低分片节点的负荷。

1. 基本场景：比如有N个缓存服务器，哈希算法通过该公式：

hash(object)%N,根据得到的结果将对象均匀的映射到N台服务器上。当一切都正常运行再考虑如下两种情况。

1) 一台服务器M down掉了，这样所有映射到服务器M的对象都会失效。需要把服务器M移除，这时服务器就是N-1台，映射公式就变成了hash(object)%（N-1）。

2) 由于访问加重，需要添加服务器，这时候服务器总数是N+1台，映射公式变为了hash(object)%（N+1）。

上述这2种情况意味着突然之间几乎所有的服务器都失效了。对于服务器端而言，这是一场灾难，高并发情况下可能导致服务器崩溃。因此需要用到一致性哈希算法。

2. 一致性哈希算法原理。

一致性哈希算法也是一种哈希算法，简单的来说，就是在移除，添加一台服务器的时候。她能够尽可能小的的改变已经存在的key的映射关系，尽可能满足单调性的要求。

2.1 环形哈希空间。

按照常用的hash算法来将对应的key哈希到一个具有2^32次方个桶的空间中，即0~(2^32)-1的数字空间中。

现在我们可以将这些数字头尾相连，想象成一个闭合的环形。如下图：

2.2 把对象映射到哈希空间

现在我们将object1、object2、object3、object4四个对象通过特定的Hash函数计算出对应的key值，然后散列到Hash环上。如下图：

2.3 将机器的唯一标示通过hash算法映射到环上

在采用一致性哈希算法的分布式集群中将新的机器加入，其原理是通过使用与对象存储一样的Hash算法将机器也映射到环中（一般情况下对机器的hash计算是采用机器的IP或者机器唯一的别名作为输入值），然后以顺时针的方向计算，将所有对象存储到离自己最近的机器中。

假设现在有NODE1，NODE2，NODE3三台机器，通过Hash算法得到对应的KEY值，映射到环中，其示意图如下：

通过上图可以看出对象与机器处于同一哈希空间中，这样按顺时针转动object1存储到了NODE1中，object3存储到了NODE2中，object2、object4存储到了NODE3中。

在这样的部署环境中，hash环是不会变更的，因此，通过算出对象的hash值就能快速的定位到对应的机器中，这样就能找到对象真正的存储位置了。

2.4 机器的删除与添加

机器的删除：以上面的分布为例，如果NODE2出现故障被删除了，那么按照顺时针迁移的方法，object3将会被迁移到NODE3中，这样仅仅是object3的映射位置发生了变化，其它的对象没有任何的改动。如下图：

机器的添加：如果往集群中添加一个新的节点NODE4，通过对应的哈希算法得到KEY4，并映射到环中，如下图：

通过按顺时针迁移的规则，那么object2被迁移到了NODE4中，其它对象还保持这原有的存储位置。

通过对节点的添加和删除的分析，一致性哈希算法在保持了单调性的同时，还是数据的迁移达到了最小，这样的算法对分布式集群来说是非常合适的，避免了大量数据迁移，减小了服务器的的压力。

3. 平衡性

根据上面的图解分析，一致性哈希算法满足了单调性和负载均衡的特性以及一般hash算法的分散性，但这还并不能当做其被广泛应用的原由，因为还缺少了平衡性。

下面将分析一致性哈希算法是如何满足平衡性的。

hash算法是不保证平衡的，如上面只部署了NODE1和NODE3的情况（NODE2被删除的图），object1存储到了NODE1中，而object2、object3、object4都存储到了NODE3中，这样就照成了非常不平衡的状态。

在一致性哈希算法中，为了尽可能的满足平衡性，其引入了虚拟节点。

“虚拟节点”（ virtual node ）是实际节点（机器）在 hash 空间的复制品（ replica ），一实际个节点（机器）对应了若干个“虚拟节点”，这个对应个数也成为“复制个数”，“虚拟节点”在 hash 空间中以hash值排列。

以上面只部署了NODE1和NODE3的情况（NODE2被删除的图）为例，之前的对象在机器上的分布很不均衡，现在我们以2个副本（复制个数）为例，这样整个hash环中就存在了4个虚拟节点，最后对象映射的关系图如下：

根据上图可知对象的映射关系：object1->NODE1-1，object2->NODE1-2，object3->NODE3-2，object4->NODE3-1。

通过虚拟节点的引入，对象的分布就比较均衡了。那么在实际操作中，正真的对象查询是如何工作的呢？对象从hash到虚拟节点到实际节点的转换如下图：

虚拟节点与实际节点的个数是倍数关系，例如有4个实际的物理节点，你可以有4*n个虚拟节点，例如96个虚拟节点。

即每个物理节点负责24个虚拟节点。

虚拟节点的目的就是让数更加分散，从而减少数据倾斜的出现。

“虚拟节点”的hash计算可以采用对应节点的IP地址加数字后缀的方式。

例如假设NODE1的IP地址为192.168.1.100。

引入“虚拟节点”前，计算 cache A 的 hash 值：

Hash(“192.168.1.100”);

引入“虚拟节点”后，计算“虚拟节”点NODE1-1和NODE1-2的hash值：

Hash(“192.168.1.100#1”); // NODE1-1

Hash(“192.168.1.100#2”); // NODE1-2

4.主从复制，读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。

读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

5. 分布式数据库方案

1) 分布式DB水平切分中用到的主要关键技术：分库，分表，M-S,集群，负载均衡

2) 需求分析：一个大型互联网应用每天几十亿的PV对DB造成了相当高的负载，对系统的稳定性的扩展性带来极大挑战。

3) 现有解决方式：通过数据切分提高网站性能，横向扩展数据层

水平切分DB，有效降低了单台机器的负载，也减小了宕机的可能性。

集群方案：解决DB宕机带来的单点DB不能访问问题。

读写分离策略：极大限度提高了应用中Read数据的速度和并发量。

典型例子：Taobao,Alibaba,Tencent，它们大都实现了自己的分布式数据访问层（DDAL）。Taobao的基于ibatis和Spring的的分布式数据访问层，已有多年的应用，运行效率和生产实效性得到了开发人员和用户的肯定。

水平切分需要考虑的后续问题：分库后路由规则的选择和制定，以及后期扩展。如，如何以最少的数据迁移达到最大容量的扩展。因些路由表分规则以及负载均衡的考虑很重要。

4）对于DB切分，实质上就是数据切分。下面从What, Why, How三个方面来讲述。

What？什么是数据切分？

具体将有什么样的切分方式呢和路由方式呢？举个简单的例子：我们针对一个Blog应用中的日志来说明，比如日志文章（article）表有如下字段：

article_id(int),title(varchar(128)),content(varchar(1024)),user_id(int)

面对这样的一个表，我们怎样切分呢？怎样将这样的数据分布到不同的数据库中的表中去呢？其实分析blog的应用，我们不难得出这样的结论：blog的应用中，用户分为两种：浏览者和blog的主人。浏览者浏览某个blog，实际上是在一个特定的用户的blog下进行浏览的，而blog的主人管理自己的blog，也同样是在特定的用户blog下进行操作的（在自己的空间下）。所谓的特定的用户，用数据库的字段表示就是“user_id”。就是这个“user_id”，它就是我们需要的分库的依据和规则的基础。我们可以这样做，将user_id为1～10000的所有的文章信息放入DB1中的article表中，将user_id为10001～20000的所有文章信息放入DB2中的article表中，以此类推，一直到DBn。这样一来，文章数据就很自然的被分到了各个数据库中，达到了数据切分的目的。接下来要解决的问题就是怎样找到具体的数据库呢？其实问题也是简单明显的，既然分库的时候我们用到了区分字段user_id，那么很自然，数据库路由的过程当然还是少不了user_id的。考虑一下我们刚才呈现的blog应用，不管是访问别人的blog还是管理自己的blog，总之我都要知道这个blog的用户是谁吧，也就是我们知道了这个blog的user_id，就利用这个user_id，利用分库时候的规则，反过来定位具体的数据库，比如user_id是234，利用该才的规则，就应该定位到DB1，假如user_id是12343，利用该才的规则，就应该定位到DB2。以此类推，利用分库的规则，反向的路由到具体的DB，这个过程我们称之为“DB路由”。

当然考虑到数据切分的DB设计必然是非常规，不正统的DB设计。那么什么样的DB设计是正统的DB设计呢？

Why?为什么要切分数据？

1) 像Oracle这样成熟稳定的DB可以支撑海量数据的存储和查询，但是价格不是所有人都承受得起。

2) 负载高点时，Master-Slaver模式中存在瓶颈。现有技术中，在负载高点时使用相关的Replication机制来实现相关的读写的吞吐性能。这种机制存在两个瓶颈：一是有效性依赖于读操作的比例，这里Master往往会成为瓶颈所在，写操作时需要一个顺序队列来执行，过载时Master会承受不住，Slaver的数据同步延迟也会很大，同时还会消耗CPU的计算能力，为write操作在Master上执行以后还是需要在每台slave机器上都跑一次。而Sharding可以轻松的将计算，存储，I/O并行分发到多台机器上，这样可以充分利用多台机器各种处理能力，同时可以避免单点失败，提供系统的可用性，进行很好的错误隔离。

3) 用免费的MySQL和廉价的Server甚至是PC做集群，达到小型机+大型商业DB的效果，减少大量的资金投入，降低运营成本，何乐而不为呢？

How？如何切分数据？

数据切分可以是物理上的，对数据通过一系列的切分规则将数据分布到不同的DB服务器上，通过路由规则路由访问特定的数据库，这样一来每次访问面对的就不是单台服务器了，而是N台服务器，这样就可以降低单台机器的负载压力。

数据切分也可以是数据库内的，对数据通过一系列的切分规则，将数据分布到一个数据库的不同表中，比如将article分为article_001,article_002等子表，若干个子表水平拼合有组成了逻辑上一个完整的article表，这样做的目的其实也是很简单的。举个例子说明，比如article表中现在有5000w条数据，此时我们需要在这个表中增加（insert）一条新的数据，insert完毕后，数据库会针对这张表重新建立索引，5000w行数据建立索引的系统开销还是不容忽视的。但是反过来，假如我们将这个表分成100个table呢，从article_001一直到article_100，5000w行数据平均下来，每个子表里边就只有50万行数据，这时候我们向一张只有50w行数据的table中insert数据后建立索引的时间就会呈数量级的下降，极大了提高了DB的运行时效率，提高了DB的并发量。当然分表的好处还不知这些，还有诸如写操作的锁操作等，都会带来很多显然的好处。

综上，分库降低了单点机器的负载；分表，提高了数据操作的效率，尤其是Write操作的效率。

上文中提到，要想做到数据的水平切分，在每一个表中都要有相冗余字符作为切分依据和标记字段，通常的应用中我们选用user_id作为区分字段，基于此就有如下三种分库的方式和规则：（当然还可以有其他的方式）

按号段分：

(1) user_id为区分，1～1000的对应DB1，1001～2000的对应DB2，以此类推；

优点：可部分迁移

缺点：数据分布不均

(2) hash取模分：

对user_id进行hash（或者如果user_id是数值型的话直接使用user_id的值也可），然后用一个特定的数字，比如应用中需要将一个数据库切分成4个数据库的话，我们就用4这个数字对user_id的hash值进行取模运算，也就是user_id%4,这样的话每次运算就有四种可能：结果为1的时候对应DB1；结果为2的时候对应DB2；结果为3的时候对应DB3；结果为0的时候对应DB4，这样一来就非常均匀的将数据分配到4个DB中。

优点：数据分布均匀

缺点：数据迁移的时候麻烦，不能按照机器性能分摊数据

(3) 在认证库中保存数据库配置

就是建立一个DB，这个DB单独保存user_id到DB的映射关系，每次访问数据库的时候都要先查询一次这个数据库，以得到具体的DB信息，然后才能进行我们需要的查询操作。

优点：灵活性强，一对一关系

缺点：每次查询之前都要多一次查询，性能大打折扣

以上就是通常的开发中我们选择的三种方式，有些复杂的项目中可能会混合使用这三种

方式。

4) 接下来对分布式数据库解决海量数据的存访问题做进一步介绍

分布式数据方案提供功能如下：

（1）提供分库规则和路由规则（RouteRule简称RR），将上面的说明中提到的三中切分规则直接内嵌入本系统，具体的嵌入方式在接下来的内容中进行详细的说明和论述；

（2）引入集群（Group）的概念，解决容错性的问题，保证数据的高可用性；