一个成熟的数据库架构，并不是从一开始就设计高可用、高扩展等特性。随着用户数量的增加逐渐完善。本文主要讲MySQL数据库在开发周期中面临的问题及优化方案。暂且不谈前端应用，大致可以分为以下五个阶段：第一阶段：数据库表设计项目建立后，开发部需要开发项目。开发工程师会在开发项目之初设计表结构。对于数据库来说，表结构的设计非常重要。如果设计不当，会直接影响用户访问网站的速度，用户体验不好！影响这种情况的具体因素有很多，比如查询慢（查询语句效率低下）、索引不当、数据库拥塞（锁）等。当然，测试部门有一个团队会做产品测试，找bug。由于开发工程师的侧重点不同，前期不会过多考虑数据库设计是否合理，而是尽快完成功能实现和交付。项目上线有一定的访问量后，隐藏的问题就会暴露出来，这时候想修改就没那么容易了！Phase2：数据库部署是运维工程师出来，项目上线的时候。项目初期访问次数一般为***。现阶段Web+数据库单次部署足以应对1000左右的QPS（每秒查询率）。考虑到单点故障，要做到高可用，可以采用MySQL主从复制+Keepalived实现双机热备。主流的HA软件包括：Keepalived（推荐）、Heartbeat。Phase3：数据库性能优化如果MySQL部署在普通的X86服务器上，不做任何优化，MySQL的理论值正常可以处理1500QPS左右，优化后可能会提升到2000QPS左右。否则，当访问量达到1500左右并发连接数时，数据库处理性能可能响应变慢，硬件资源相对充裕。这时候就要考虑性能优化了。那么如何让数据库发挥其最佳性能呢？主要从硬件配置、数据库配置、架构入手，分为以下几点：3.1硬件配置如果条件允许，一定要使用SSD固态硬盘，而不是SAS机械硬盘，RAID级别调整为RAID1+0，优于RAID1和RAID5。读写性能，毕竟数据库的压力主要来自于磁盘I/O。Linux内核有一个特点，就是在物理内存中划分出缓存区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制。当条件满足时（如缓存区大小达到一定百分比或执行同步命令）将同步到磁盘。也就是说，物理内存越大，分配的缓冲区就越大，缓存的数据也就越多。当然，服务器故障会丢失一些缓存数据。建议物理内存至少增加50%。3.2数据库配置优化MySQL使用最广泛的存储引擎有两种：一种是MyISAM，不支持事务处理，读性能快，有表级锁。另一个是InnoDB，支持事务处理（ACID属性），专为大数据处理而设计，行级锁。表锁：开销小，加锁粒度大，死锁概率高，并发度相对较低。行锁：开销大，加锁粒度小，死锁概率低，相对并发度高。为什么会出现表锁和行锁？主要是保证数据的完整性。比如一个用户正在操作一个表，其他用户也想操作这个表，那么其他用户就必须等待第一个用户操作完成。表锁和行锁就是为了这个目的。否则多个用户同时操作一张表，肯定会出现数据冲突或异常。从这些方面来看，使用InnoDB存储引擎是最好的选择，也是MySQL5.5+默认的存储引擎。与每个存储引擎相关的运行参数很多，下面列出可能影响数据库性能的参数。public参数默认值：max_connections=151#同时处理最大连接数，建议设置最大连接数为连接数上限的80%左右sort_buffer_size=2M#查询大小排序时buffer的大小，只针对orderby和groupby建议增加到16Mopen_files_limit=1024#限制打开文件数。如果showglobalstatuslike'open_files'查看的值等于或大于open_files_limit的值，程序将无法连接数据库或卡住MyISAM参数默认值：key_buffer_size=16M#Indexbuffersize，一般设置30-40物理内存的%read_buffer_size=128K#读取缓冲区大小，建议设置16M或32Mquery_cache_type=ON#开启查询缓存功能query_cache_limit=1M#查询缓存限制，只缓存1M以下的查询结果，以防结果数据较大，缓存池被覆盖query_cache_size=16M#查看缓冲区大小，用于缓存SELECT查询结果。下次同一个SELECT查询直接从缓存池中返回结果时，这个值可以适当增加一倍。InnoDB参数默认值：innodb_buffer_pool_size=128M#索引和数据缓冲区大小，建议设置为物理内存的70%左右innodb_buffer_pool_instances=1#缓冲池实例数，建议设置4或8innodb_flush_log_at_trx_commit=1#关键参数，0表示大约每秒写入如果数据库出现故障，事务数据会丢失大约1秒。1、每条SQL执行完后，写入日志，同步到磁盘。I/O开销很高。执行SQL后还要等待日志的读写，效率低下。2表示日志只写入系统缓存，然后每秒同步一次到磁盘，效率很高。如果服务器出现故障，交易数据将丢失。推荐设置2，对数据安全性要求不高，性能高，修改后效果明显。innodb_file_per_table=OFF#是否共享表空间，5.7+版本默认开启，共享表空间的idbdata文件不断增加，影响一定的I/O性能。建议开启独立表空间模式。每个表的索引和数据都存储在自己独立的表空间中，可以实现单表在不同数据库中的移动。innodb_log_buffer_size=8M#日志缓冲区的大小，由于日志最多每秒刷新一次，一般不需要超过16M3.3系统内核参数优化MySQL大多部署在Linux系统上，所以一些参数的操作系统也会影响MySQL的性能，下面适当优化Linux内核参数net.ipv4.tcp_fin_timeout=30#TIME_WAIT超时时间，默认是60snet.ipv4.tcp_tw_reuse=1#1表示开启多路复用，允许TIME_WAITsocket要重新用于新的TCP连接，0表示关闭net.ipv4.tcp_tw_recycle=1#1表示开启TIME_WAITsocket快速回收，0表示关闭net.ipv4.tcp_max_tw_buckets=4096#系统保持最大TIME_WAITsocket数量,如果超过这个数,系统会随机清除一些TIME_WAIT并打印警告信息net.ipv4.tcp_max_syn_backlog=4096#EnterthemaximumlengthoftheSYNqueue,增加qu的长度eue可以容纳更多的等待连接在Linux系统中，如果进程打开的文件句柄数超过系统默认值1024，会提示“打开的文件太多”信息，所以调整打开的文件句柄限制。重启***生效：#vi/etc/security/limits.conf*softnofile65535*hardnofile65535当前用户立即生效：#ulimit-SHn65535第四阶段：数据库架构扩展数据库服务器已经减少如果不能满足业务需求，就要考虑增加服务器扩展架构了。主要思路是分解单个数据库的负载，突破磁盘I/O的性能，将热点数据存放在缓存中，降低磁盘I/O访问频率。4.1添加缓存在数据库中添加缓存系统，将热点数据缓存在内存中。如果缓存中有请求的数据，则不再请求MySQL，减少数据库负载。缓存实现包括本地缓存和分布式缓存。本地缓存将数据缓存在本地服务器内存或文件中。分布式缓存可以缓存海量数据，具有良好的可扩展性。主流的分布式缓存系统：memcached、redis、memcached性能稳定，数据缓存在内存中速度快。QPS理论可以达到8w左右。如果要数据持久化，就选redis，性能不比memcached低。工作过程：4.2主从复制和读写分离在生产环境中，业务系统通常读多写少，可以部署主多从架构。负载均衡，负责读操作。主流负载均衡器：LVS、HAProxy、Nginx。如何实现读写分离？大多数企业都是在代码层面实现读写分离，效率很高。另一种方式是通过代理程序实现读写分离。企业中的应用较少，会增加中间件的消耗。主流的中间件代理系统有MyCat、Atlas等。在这种MySQL主从复制拓扑中，分散了单台服务器的负载，大大提高了数据库的并发能力。如果一台从服务器可以处理1500QPS，那么3台从服务器可以处理4500QPS，并且很容易横向扩展。有时，面对大量写入操作的应用，单台服务器的写入性能无法满足业务需求。可以做双向复制（dualmaster），但是有一个问题需要注意：如果两个master服务器对外提供读写操作，可能会出现数据不一致的情况。这样做的原因是程序有机会同时操作两个数据库。同时更新操作会导致两个数据库之间的数据冲突或不一致。您可以将每个表ID字段设置为唯一：auto_increment_increment和auto_increment_offset，或者您可以编写算法来生成随机唯一性。也可以考虑近两年正式推出的MGR（multi-masterreplication）集群。4.3分库分库是将数据库中的相关表按照业务分离到不同的数据库中，如web、bbs、blog等库。如果业务量较大，还可以采用分离的数据库作为主从复制架构，进一步避免单一数据库压力过大。4.4分表数据量越来越大。数据库中的某个表有几百万条数据，查询和插入的时间太长。如何解决单表压力？应该考虑将这张表拆分成多个小表，以减轻单表的压力，提高处理效率。这种方法称为表拆分。分表技术比较繁琐。修改程序代码中的SQL语句，需要手动创建其他表。也可以使用合并存储引擎来实现分表，比较简单。分表后，程序是对一张总表进行操作。这张总表不存储数据，只存储一些子表关系和更新数据的方式。总表会根据不同的查询，把压力分成不同的小表，从而提高并发性和磁盘I/O性能。表格拆分分为垂直拆分和水平拆分：垂直拆分：将字段较多的原始表格拆分为多个表格，解决表格宽度问题。可以将不常用的字段放在一个表中，可以将大的字段独立放在一个表中，也可以将关系密切的字段放在一个表中。水平拆分：将原表拆分为多张表，每张表结构相同，解决单表数据量大的问题。4.5分区分区就是将一个表的数据按照表结构中的字段（如range、list、hash等）划分成多个块。这些块可以在一个磁盘上，也可以在不同的磁盘上。分区后表面上还是一张表，但是数据散列在多个位置。这样多个硬盘同时处理不同的请求，从而提高磁盘I/O读写性能。注：添加缓存、分库、分表、分区主要由程序员或DBA实现。第五阶段：数据库维护数据库维护是数据库工程师或运维工程师的工作，包括系统监控、性能分析、性能调优、数据库备份与恢复等主要工作。5.1性能状态关键指标技术术语：QPS（QueriesPerSecond，查询书每秒）和TPS（TransactionsPerSecond）通过showstatus查看运行状态，会有300多条状态信息记录，其中有几条可以帮助我们计算QPS和TPS，如下：Uptime：服务器实际运行的时间，单位秒Questions：发送到数据库的查询次数Com_select：查询次数，实际运行的数据库插入次数com_delete：删除次数com_update：更新次数com_commit：事务次数com_rollback：回滚次数那么，计算方法就到这里了，根据Questions来计算QPSmysql>showglobalstatuslike'Questions';;mysql>showglobalstatuslike'Com_rollback';mysql>showglobalstatuslike'Uptime';TPS=(Com_commit+Com_rollback)/Uptime另一种计算方式：根据Com_select,Com_insert,Com_delete,Com_update计算QPS：mysql>showglobalstatuswhereVariable_namein('com_select','com_insert','com_delete','com_update');执行前等待1秒，得到间隔差，第二次每个变量值减去第一次对应的变量值就是QPS。TPS计算方法：mysql>showglobalstatuswhereVariable_namein('com_insert','com_delete','com_update');计算TPS不算查询操作，只是计算insert、delete、update这四个值。根据网友对这两种计算方式的测试，当数据库中的myisam表较多时，使用Questions计算更为准确。当数据库中innodb表较多时，用com_*计算更准确。5.2开启慢查询日志MySQL开启慢查询日志，分析哪个SQL语句比较慢，支持动态开启：mysql>setglobalslow-query-log=on#openslowquerylogmysql>setglobalslow_query_log_file='/var/log/mysql/mysql-slow.log';#指定慢查询日志文件的位置mysql>setgloballog_queries_not_using_indexes=on;#记录不使用索引的查询mysql>setgloballong_query_time=1;#只记录耗时超过1s的慢查询来分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具可以比较简单的分析日志。mysqldumpslow-t3/var/log/mysql/mysql-slow.log#查看最慢的前三查询，也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可以分析慢日志、binlog、通用日志。分析慢查询日志：pt-query-digest/var/log/mysql/mysql-slow.log分析binlog日志：mysqlbinlogmysql-bin.000001>mysql-bin.000001.sqlpt-query-digest--type=binlogmysql-bin.000001.sql普通日志分析：pt-query-digest--type=genloglocalhost.log5.3数据库备份备份数据库是最基础的工作，也是最重要的，否则后果会很严重，你懂的！对于高频备份策略，选择一款稳定快速的工具非常重要。如果数据库大小在2G以内，推荐使用官方提供的逻辑备份工具mysqldump。2G以上，建议使用percona的物理备份工具xtrabackup，不然会慢如蜗牛。两款工具都支持InnoDB存储引擎下的热备份，不影响业务读写操作。5.4数据库修复有时MySQL服务器突然断电或非正常关机，会损坏表，导致无法读取表数据。这时候可以使用mysql自带的两个工具myisamchk和mysqlcheck进行修复。前者只能修复MyISAM表并停止数据库，而后者可以同时对MyISAM和InnoDB进行在线修复。注意：修复前最好先备份数据库。myisamchk常用参数：-f--force强制修复，覆盖旧的临时文件，一般不用-r--recover恢复模式-q--quik快速恢复-a--analyze分析表-o--safe-recoveroldrecoverymode，如果-r不能修复，可以用这个参数试试-F--fast只检查没有正常关闭的表例如：myisamchk-r-q*.MYImysqlcheck常用参数：-a--all-databases检查所有库-r--repair修复表-c--check检查表，默认选项-a--analyze分析表-o--optimize优化表-q--quik最快的检查或修复表-F--fastonly检查没有正常关闭的表。例如：mysqlcheck-r-q-uroot-p123456weibo5.5MySQL服务器性能分析重点：id：CPU使用率，平均不到60%，正常，但已经很忙了。wa：CPU等待磁盘IO响应的时间，一般大于5表示磁盘读写较多。KB_read/s,KB_wrtn/s每秒读写数据量，主要根据磁盘每秒的最大读写速度。r/s、w/s：每秒读写请求数，可以理解为IOPS（每秒输入输出），是衡量磁盘性能的主要指标之一。await：每秒IO平均响应时间，一般大于5表示磁盘响应慢，超出自身性能。util：磁盘使用率，平均不到60%，正常，但已经很忙了。总结由于关系型数据库原有设计的局限性，在处理大数据时会显得力不从心。因此，NoSQL（非关系数据库）开始流行起来。它天生励志，具有分布式、高性能、高可靠等特点，弥补了关系型数据库的一些先天不足。非常适合存储非结构化数据。主流的NoSQL数据库包括：MongoDB、HBase、Cassandra等。纯数据库层面的优化效果提升不是很明显。主要是根据业务场景选择合适的数据库！
