MySQL性能优化

MySQL数据库优化可以从以下几个方面考虑：

  • SQL语句优化
  • 数据库参数配置
  • 数据库架构
  • 硬件升级

一、SQL语句优化

1、开启MySQL数据库慢查询

修改MySQL配置文件my.cnf，增加以下内容：

修改完成后重启MySQL，执行以下语句查看修改是否生效

2、使用mysqldumpslow命令缩小范围

  • 访问次数最多的5个SQL语句
  • 　　mysqldumpslow -s c -t 5 /usr/local/mysql/data/slow-query.log
  • 返回记录集最多的5个SQL语句
  • 　　mysqldumpslow -s r -t 5 /usr/local/mysql/data/slow-query.log
  • 按照时间返回前5条里含有左连接的SQL语句
  • 　　mysqldumpslow -t 5 -s t -g “left join” /usr/local/mysql/data/slow-query.log

说明：

  • -s，表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar表示相应的倒序
  • -t，是top n的意思，即返回前面多少条数据
  • -g，后面可以写一个正则匹配模式，大小写不敏感

3、如何分析SQL查询语句

（1）先用mysqldumpslow命令找出需要优化的SQL语句，如下：

（2）然后把SQL语句中的N替换成具体的字段值，再用EXPLAIN来分析，如下：

各列含义：

  • table：表名
  • type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见的类型如下，由好到最差
  •     -NULL：最好
  •     -const：主键、索引
  •     -eq_reg：主键、索引的范围查找
  •     -ref：连接的查找（join）
  •     -range：索引的范围查找
  •     -index：索引的扫描
  •     -All：全表扫描
  • possible_keys：可能用到的索引
  • key：显示MySQL在查询中实际使用的索引，若没有使用索引，则显示为NULL
  • key_len：索引的长度，越短越好
  • ref：索引的哪一列被使用了，常数较好
  • rows：表示MySQL根据表统计信息以及索引选用情况，估算找到所需记录需要读取的行数
  • Extra：using filesort、using temporary（常出现在使用order by时）时需要优化
  •     -Only index：意味着信息只用索引数中的信息检索出来的，比扫描整个表都快
  •     -Using where：使用上了where限制
  •     -Impossible where：表示用不着where，一般是没查出来啥
  •     -Using filesort：额外排序
  •     -Using temporary：使用了临时表
  •          -Using filesort和Using temporary意味着查询很吃力，需要优化。where和order by的索引经常无法兼顾，如果按照where来确定索引，order by必然会引起using filesort，这就要看是先过滤再排序划算还是先排序再过滤划算

（3）从EXPLAIN的结果可以看到，type的值为ALL，即为全表扫描。Extra的值为Using where 和Using filesort，说明查询很吃力，这句SQL语句是要优化的。

优化方案：对category_id、comments、views三个字段增加索引，再次用EXPLAIN来分析，如下图：

type变成了“range”，比之前有了进步，可以接受。rows也由3变为了1，但是Extra使用的using filesort仍然不能接受。这里的comments条件是查询一个范围，会导致MySQL无法利用索引对后面的views进行检索，即range类型查询字段后面的索引无效，所以我们删除comments索引再看看效果，如下图：

这时，我们发现type又提升到了ref，Extra中的Using filesort消失了，说明性能有了比较大的提升。虽然rows由1变为了2，但是去掉了Using filesort带来的性能提升更大。

二、参数配置

1、Max_connections

MySQL的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，MySQL会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

查看当前最大连接数，如下图：

查看MySQL以往实际达到过的最大连接数，如下图：

比较理想的设置是：max_used_connections/ max_connections * 100%≈85%，如果max_used_connections跟max_connections相等，那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

如何设置max_connections：修改MySQL配置文件，加入以下内容，然后重启MySQL，如下图：

2、back_log

MySQL能暂存的连接数量，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log。如果等待连接的数量超过back_log，将不被授予连接资源，将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程超时。当主要MySQL线程在一个很短时间内得到非常多的连接请求，back_log就起作用。 

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog。对于Linux系统推荐设置为小于512的整数。

当我们在观察主机进程列表，发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值。

可以通过以下命令查看back_log的设置，如下图：

通过在my.cnf文件中配置back_log的数量，如下图：

3、wait_timeout和interactive_timeout

wait_timeout：指的是MySQL连接再闲置超过一定的时间后会被强制关闭，在关闭一个非交互的连接之前所要等待的秒数。MySQL默认的wait-timeout 值为8个小时。wait_timeout如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用，如果设置太大，容易造成连接打开时间过长。

设置这个值是非常有意义的，比如你的网站有大量的MySQL链接请求（每个MySQL连接都是要内存资源开销的 ），由于你的程序的原因有大量的连接请求空闲啥事也不干，白白占用内存资源，或者导致MySQL超过最大连接数但无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态（可用show processlist)，如果经常发现MYSQL中有大量的Sleep进程，则需要 修改wait-timeout值了。

interactive_timeout：指的是MySQL在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行MySQL管理，使用的即是交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。

什么是交互式连接，什么是非交互式连接？说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。

查看wait_timeout和interactive_timeout的设置

通过在my.cnf文件中配置wait_timeout和interactive_timeout的超时时间，如下图：

从上面两个图可以看出，wait_timeout的值好像和配置的时间不一致，这是因为：

  • 对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout
  • 对于交互式连接，类似于mysql客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout

因为我使用的是SecureCRT连接Linux服务器在进入MySQL的，所以是交互式连接，所以wait_timeout的值是继承interactive_timeout的值，都是3600秒。

对应用来说，如果用的是连接池或者是长连接，并且没有F5（F5可能会设置一个超时，把连接时间过长的session kill掉），那么两个timeout的时间尽量设长一点，避免应用连接池不断的重新连接数据库。如果是短连接，那么就需要把两个timeout的时间设短一些，因为数据库的最大连接数是有限制的，很多连接一直不释放，浪费数据库资源，同时可能会有“MySQL：ERROR 1040：Too many connections”的错误。

4、key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。key_buffer_size只对MyISAM表起作用，即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理，如下图：

一共有6个索引读取请求，有三个请求在内存中没有找到直接从硬盘中读取索引

key_cache_miss_rate（索引未命中缓存的概率）= key_reads/key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好，如果在0.01%以下的话，则key_bufferl_size分配过多，可以适当减少。

查看key_buffer_size设置，如下图：

查看key_buffer_size参数设置大小，单位为B
key_buffer_size是对MyISAM表性能影响最大的一个参数

通过在my.cnf文件中配置key_buffer_size大小，如下图：

5、innodb_buffer_pool_size

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓存数据和索引。innodb_buffer_pool_size的大小直接关系到 InnoDB存储引擎的性能，所以如果我们有足够的内存，尽可能将该参数设置到足够大。简单来说，当我们操作一个 InnoDB 表的时候，返回的所有数据或者取数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

innodb_buffer_pool_size 这个值越大越好，能保证在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB（8GB内存），20-25GB（32GB内存），100-120GB（128GB内存）

我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化，如下图：

命中率=（1945-315）/1945*100%=83.8%，显然，我们要加大innodb_buffer_pool_size，如下图：

6、query_cache_size

MySQL中Select语句执行过程：

  • 先做语法、权限检查
  • 然后去数据库缓存中查询看有没有这条SQL语句的执行计划，如果有直接把结果返回给应用
  • 如果没有则生成这条SQL语句的执行计划
  • 将执行计划存到数据库缓存里面
  • 数据库按照执行计划去数据库中去执行
  • 到硬盘/磁盘去找到这条数据，然后放到缓存中再返回给应用

使用查询缓存，MySQL将查询结果存放在缓存区中，今后对于同样的SELECT语句（区分大小写）,将直接从缓冲区中读取结果，从而省略了后面所有的步骤，极大的提高性能。一个SQL查询如果以SELECT开头，那么MySQL服务器将尝试对其使用查询缓存。两个SQL语句，只要相差哪怕是一个字符（列如大小写不一样、多一个空格等）,那么这两个SQL将使用不同的cache。

当然，Query Cache也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的Select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。

首先，我们通过如下命令来获得关于查询缓存的相关的信息，如下图：

  • Qcache_free_blocks：查询缓存中相邻内存块的个数，这个值越大说明缓存的碎片越多，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
  • Qcache_free_memory ：查询缓存的剩余内存的大小，通过这个参数我们可以较为准确的观察出当前系统中的查询缓存的内存大小是否足够，是需要增加还是过多了。
  • Qcache_hits：缓存的命中次数，们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
  • Qcache_inserts：缓存未命中的次数，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果插入到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
  • Qcache_lowmem_prunes：多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看，如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的Qcache_free_blocks和Qcache_free_memory可以告诉您属于哪种情况）
  • Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
  • Qcache_queries_in_cache：当前缓存的数据量
  • Qcache_total_blocks：缓存块的数量，如果这个值一直在增加，说明缓存是起作用的。
  • 对于写操作来说，开启qcache没有任何作用
  • 如果读操作比较多，开启qcache操作会提高性能 

我们再来查询一下关于query_cache的相关配置，如下图：

  • query_cache_limit：超过此大小的查询将不缓存
  • query_cache_min_res_unit：缓存块的最小大小 ，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
  • query_cache_size：查询缓存大小 。
  • query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：
  •     -设置为0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
  •     -设置为1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache。
  •     -设置为2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache。

Query Cache的使用需要多个参数配合，其中最为关键的是query_cache_size和query_cache_type，前者设置用于缓存 ResultSet的内存大小，后者设置在何场景下使用Query Cache。在以往的经验来看，如果不是用来缓存基本不变的数据的MySQL数据库，query_cache_size一般256MB是一个比较合适的大小。

如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓存被清除出Query Cache，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整query_cache_size的值，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。

修改my.cnf文件来设置缓存大小和缓存类型，如下图：

 

7、thread_cache_size

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量。当断开连接时,如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。

试图连接到MySQL(不管是否连接成功)的连接数，如下图：

  • Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程。
  • Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
  • Threads_created：代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
  • Threads_running：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

查看开机起来数据库被连接了多少次：

  • (Connections -  Threads_created) / Connections * 100 %
  • 通过连接线程池的命中率来判断设置值是否合适，命中率超过90%以上，设定合理

修改my.cnf文件来来配置服务器缓存线程，如下图：

8、thread_concurrency

thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍。比如有一个双核的CPU, 那thread_concurrency  的应该为4; 2个双核的cpu, thread_concurrency的值应为8。

根据上面的计算方法，可知道为4个CPU,每个CPU为8核，按照上面的计算规则，这儿thread_concurrency应设为：4*8*2=64

修改my.cnf来修改thread_concurrency配置，如下图：

需要注意的是这个参数在MySQL5.6.1中已经被标记为过时，在5.7.2版本的MySQL中被移除。

三、MySQL数据库参考配置

1、InnoDB配置

#InnoDB存储数据字典、内部数据结构的缓冲池，16MB 已经足够大了。
innodb_additional_mem_pool_size = 16M

#InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典等
#如果是专用的DB服务器，且以InnoDB引擎为主的场景，通常可设置物理内存的50%-70%
#如果是非专用DB服务器，可以先尝试设置成内存的1/4，如果有问题再调整
#默认值是8M，非常不合适，这也是导致很多人觉得InnoDB不如MyISAM好用的缘故
innodb_buffer_pool_size = 4G

#InnoDB共享表空间初始化大小，默认是 10MB，非常坑X，改成 1GB，并且自动扩展
innodb_data_file_path = ibdata1:1G:autoextend

#如果不了解本选项，建议设置为1，能较好保护数据可靠性，对性能有一定影响，但可控
innodb_flush_log_at_trx_commit = 1

#InnoDB的log buffer，通常设置为 64MB 就足够了
innodb_log_buffer_size = 64M

#InnoDB redo log大小，通常设置256MB 就足够了
innodb_log_file_size = 256M

#InnoDB redo log文件组，通常设置为 2 就足够了
innodb_log_files_in_group = 2

#启用InnoDB的独立表空间模式，便于管理
innodb_file_per_table = 1

#启用InnoDB的status file，便于管理员查看以及监控等
innodb_status_file = 1

#设置事务隔离级别为 READ-COMMITED，提高事务效率，通常都满足事务一致性要求
transaction_isolation = READ-COMMITTED

2、其他配置

#设置最大并发连接数，如果前端程序是PHP，可适当加大，但不可过大
#如果前端程序采用连接池，可适当调小，避免连接数过大
max_connections = 100

#最大连接错误次数，可适当加大，防止频繁连接错误后，前端host被mysql拒绝掉
max_connect_errors = 100000

# 开启慢查询功能
slow-query-log = on
# 慢查询日志存放路径与文件名
slow_query_log_file = /usr/local/mysql/data/slow-query.log
# 设置慢查询阀值，建议设置最小的 1 秒
long_query_time = 1
# 列出没有使用索引的查询语句
log-queries-not-using-indexes = on

#设置临时表最大值，这是每次连接都会分配，不宜设置过大 max_heap_table_size 和 tmp_table_size 要设置一样大
max_heap_table_size = 96M
tmp_table_size = 96M

#每个连接都会分配的一些排序、连接等缓冲，一般设置为 2MB 就足够了
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

#开启查询缓存
query_cache_size = 256M
query_cache_type=1

#如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的 key_buffer_size 可以设置较小，8MB 已足够
#如果是以MyISAM引擎为主，可设置较大，但不能超过4G
#在这里，强烈建议不使用MyISAM引擎，默认都是用InnoDB引擎
key_buffer_size = 8M

#设置连接超时阀值，如果前端程序采用短连接，建议缩短这2个值
#如果前端程序采用长连接，可直接注释掉这两个选项，是用默认配置(8小时)
interactive_timeout = 120
wait_timeout = 120

四、架构优化

随着数据量的增加、压力增加、流量增加，简单的架构不能保证数据库的性能，我们需要尝试主从复制、读写分离、分库分表这些技术来提高数据库性能。

1、主从复制

  • Master将改变记录到二进制日志（binary log）中
  • Slave将master的binary log日志拷贝到它的中继日志（Relay log）中
  • 　　Slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
  • Slave重做中继日志中的事件
  • 　　SQL线程从中继日志中读取事件，并重放其中的事件而更新slave中的数据，使其与master中的数据一致。只要改线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

2、读写分离

看了上面主从复制的介绍，我们就有如下疑问：

问题1：master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？

  • 假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。实际上，这里已经揭示了读写分离的概念。

问题2：主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要他们干嘛？

  • 数据备份：类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。
  • 异地容灾：比如master在北京，地震挂了，那么在上海的slave还可以继续。主要用于实现scale out。
  • 分担负载：可以将读的任务分散到slaves上。很可能的情况是，一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves进行操作。

问题3：主从复制中有master,slave1,slave2,...等等这么多MYSQL数据库，那比如一个JAVA WEB应用到底应该连接哪个数据库?

  • 当然，我们在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection(for master)进行操作，select用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select，例如简单的轮循算法。
  • 这样的话，相当于应用程序完成了SQL语句的路由，而且与MYSQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应用程序就要修改了。能不能让应用程序与MYSQL的主从复制架构没有什么太多关系呢？可以看下面的图：
  • 找一个组件，application program只需要与它打交道，用它来完成MYSQL的代理，实现SQL语句的路由。mysql proxy并不负责，怎么从众多的slaves挑一个？可以交给另一个组件(比如haproxy)来完成。这就是所谓的MYSQL READ WRITE SPLITE，MYSQL的读写分离。

3、分库分表

分表策略：

我们知道每台机器无论配置多么好它都有自身的物理上限，所以当我们应用已经能触及或远远超出单台机器的某个上限的时候，我们惟有寻找别的机器的帮助或者继续升级的我们的硬件，但常见的方案还是通过添加更多的机器来共同承担压力。同时我们还得考虑业务逻辑不断增长，我们的机器能不能通过线性增长就能满足需求。因此，使用数据库的分库分表，能够立竿见影的提升系统的性能。

对于大部分数据库的设计和业务的操作基本都与用户的ID相关，因此使用用户ID是最常用的分库的路由策略。用户的ID可以作为贯穿整个系统用的重要字段。因此，使用用户的ID我们不仅可以方便我们的查询，还可以将数据平均的分配到不同的数据库中。（当然，还可以根据类别等进行分表操作，分表的路由策略还有很多方式）

当数据比较大的时候，对数据进行分表操作，首先要确定需要将数据平均分配到多少张表中，也就是：表容量。这里假设有100张表进行存储，则我们在进行存储数据的时候，首先对用户ID进行取模操作，根据 user_id%100 获取对应的表进行存储查询操作，示意图如下：

分表之前的查询语句：Select * from order where user_id = xxx

分表之后的查询语句：Select * from order_(user_id%100) where user_id = xxx

根据所要查询的user_id的取模结果来确定去哪张表查询

分库策略：

数据库分表能够解决单表数据量很大的时候数据查询的效率问题，但是无法给数据库的并发操作带来效率上的提高，因为分表的实质还是在一个数据库上进行的操作，很容易受数据库IO性能的限制。因此，如何将数据库IO性能的问题平均分配出来，很显然将数据进行分库操作可以很好地解决单台数据库的性能问题。

分库策略与分表策略的实现很相似，最简单的都是可以通过取模的方式进行路由。还是上例，将用户ID进行取模操作，这样的话获取到具体的某一个数据库，如下图：

我们将user_id%100=0(0,100,200)的所有用户相关的order信息放入第一个DB的表中，user_id%100=1(1,101,201)的所有用户相关的order信息放入第二个DB的表中，以此类推，user_id%100=99(99,199,299)的所用用户相关的order信息放入第一百个DB的表中，这样就完成了分库的操作

分库分表策略：

上述的介绍中，数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题。但有时候，我们需要同时考虑这两个问题，因此，我们既需要对单表进行分表操作，还需要进行分库操作，以便同时扩展系统的并发处理能力和提升单表的查询性能，就是我们使用到的分库分表。

分库分表的策略相对于前边两种复杂一些，一种常见的路由策略如下：

中间变量　＝ user_id%（库数量*每个库的表数量） # 使用mod()函数
库序号　＝　取整（中间变量／每个库的表数量）    # 使用floor()函数
表序号　＝　中间变量％每个库的表数量           # 使用mod()函数

假如数据库有256 个，每一个库中有1024个数据表，用户的user_id＝100，按照上述的路由策略，可得：

中间变量　＝ 100%（256*1024）= 100
库序号　＝　取整（100／1024）= 0
表序号　＝　100％1024 = 100

这样的话，对于user_id＝100，将被路由到第0个数据库的第100个表中，如下图：

上述的分库和分表操作，查询性能和并发能力都得到了提高，但是还有一些需要注意的就是，例如：原本跨表的事物变成了分布式事物；由于记录被切分到不同的数据库和不同的数据表中，难以进行多表关联查询，并且不能不指定路由字段对数据进行查询。分库分表之后，如果我们需要对系统进行进一步的扩阵容（路由策略变更），将变得非常不方便，需要我们重新进行数据迁移。

测试策略：

在性能测试中，我们不可能申请这么多机器来进行测试。那么，对于数据库架构使用分库分表模式的系统，我们怎么来测试呢？我们只需要申请一台数据库服务器，这台服务器中的数据根据分表策略进行导入（与线上环境的某一台数据库要一致）。然后，用来跑业务的user_id，只使用该数据库会出现的user_id，并发数可以根据线上的最大并发数进行换算，这样测试出来的结果是可靠的，这套环境测试通过，线上也没问题。

数据库迁移注意事项：

加入我们要把这种分库分表架构的数据库从一种数据库迁移到另外一种数据库中，比如从Oracle迁移到MySQL当中，我们应该要怎么验证呢？可以从以下几个方面考虑：

  • 拆库规则验证：每个库的数据分配是否正确
  • 是否有丢失数据，数据总量是否一致
  • 分库的数据是否对
  • 数据是否错行
  • 字符集是否正确，中文是否会显示成乱码

五、硬件优化

1、磁盘

MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量在100万PV以上的系统，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案： 使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。

  • 常用RAID级别简介：
  • 　　RAID0：也称为条带，就是把多个磁盘链接成一个硬盘使用，这个级别IO最好。
  • 　　RAID1：也称为镜像，要求至少有两个磁盘，每组磁盘存储的数据相同。
  • 　　RAID5：也是把多个（最少3个）硬盘合并成1个逻辑盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。
  • 　　RAID-0+1：就是RAID0和RAID1的结合。同时具备两个级别的优缺点。一般建议数据库使用这个级别。

2、CPU

对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用两颗Intel Xeon 3.6GHz的CPU，现在我较推荐用4U及以上的服务器来专门做数据库服务器，不仅仅是针对于MySQL。

3、内存

物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都在16G以上。

posted on 2018-09-01 17:35  破解孤独  阅读(573)  评论(0)  编辑  收藏  举报

导航