从运维角度分析MySQL数据库优化的一些要点逐步完善。1、数据库表设计项目建立后，开发部根据产品部的需求进行项目开发。开发工程师的一部分工作就是设计表结构。对于数据库来说，这是非常重要的。如果设计不当，将直接影响访问速度和用户体验。影响它的因素有很多，比如查询速度慢，查询语句效率低下，没有适当的索引，数据库拥塞（死锁）等等。当然，有测试工程师团队会做压力测试，找bug。对于没有测试工程师的团队，大多数开发工程师前期不会过多考虑数据库设计是否合理，而是尽快完成功能实现和交付。项目有一定的访问量后，隐藏的问题就会暴露出来。这时候要修改就没那么容易了。2、数据库部署运维工程师在现场，项目初期访问量不会很大，所以单次部署足以应对1500左右的QPS（每秒查询率）。考虑高可用，MySQL主从复制+Keepalived可以作为双击热备。常见的集群软件有Keepalived和Heartbeat。3、数据库性能优化如果MySQL部署在普通的X86服务器上，不做任何优化，MySQL的理论值正常可以处理2000QPS左右。优化后可能会提升到2500QPS左右。否则，访问并发连接数达到1500左右时，数据库处理性能会变慢，硬件资源还是比较充裕的。这个时候就该考虑软件问题了。那么如何最大限度地发挥数据库的性能呢？一方面，可以在一台服务器上运行多个MySQL实例，最大限度地发挥服务器的性能；另一方面，优化了数据库。保守会对数据库的性能有一定的限制。可以适当调整这些配置以处理尽可能多的连接。具体优化有以下三个层次：3.1数据库配置优化MySQL中常用的存储引擎有两种，一种是MyISAM，不支持事务处理，不支持快速读性能处理，不支持表级锁。另一个是InnoDB，它支持事务处理（ACID）。设计目标是最大化处理大容量数据的性能和行级锁。表锁：开销小，加锁粒度大，死锁概率高，并发度相对较低。行锁：开销大，加锁粒度小，死锁概率低，相对并发度高。为什么会出现表锁和行锁？主要是保证数据的完整性。比如一个用户正在操作一张表，其他用户也想操作这张表，那么就需要等待第一个用户操作完成后，其他用户才能操作，表锁和行锁就是为了这个目的。否则多个用户同时操作一张表，肯定会出现数据冲突或异常。综上所述，使用InnoDB存储引擎是最好的选择，也是MySQL5.5以后版本默认的存储引擎。每个存储引擎关联的参数很多，下面列出主要影响数据库性能的参数（限于篇幅，这里只使用innodb分析）。InnoDB参数默认值：innodb_buffer_pool_size=128M#索引和数据缓冲区大小，一般设置物理内存的60%-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#默认是共享表空间，共享表空间中的idbdata文件不断增长，影响一定的I/O性能。建议开启独立表空间模式。每个表的索引和数据都存储在自己独立的表空间中，可以实现单表在不同数据库中的移动。innodb_log_buffer_size=8M#日志缓冲区的大小。由于日志最多每秒刷新一次，一般不需要超过16M3.2系统内核优化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_WAITsockets的最大数量。如果超过这个数量，系统会随机清除一些TIME_WAIT并打印警告信息net.ipv4.tcp_max_syn_backlog=4096#EnterSYN队列的最大长度，增加队列长度可以容纳更多的等待连接在Linux系统中，如果进程打开的文件句柄数超过系统默认值1024，会提示“打开的文件太多”，需要调整打开的文件句柄数。*softnofile65535*hardnofile65535#ulimit-SHn65535#立即3.3硬件配置增加物理内存，提高文件系统性能。linux内核会从内存中分配缓冲区（系统缓存和数据缓存）存放热数据，在满足条件时（比如缓冲区大小达到一定比例）使用文件系统延时写入机制进行同步或执行同步命令）。到磁盘。也就是说，物理内存越大，分配的缓冲区就越大，缓存的数据也就越多。当然，服务器故障会丢失一些缓存数据。SSD硬盘替换SAS硬盘，RAID级别调整为RAID1+0。与RAID1和RAID5相比，具有更好的读写性能（IOPS）。毕竟数据库的压力主要来自于磁盘I/O。4.数据库架构扩展这里的主要思路是分解单个数据库的负载，突破磁盘I/O的性能，将热点数据存放在缓存中，降低磁盘I/O的访问频率。4.1主从复制和读写分离因为在生产环境中，大部分数据库都是读操作，所以部署主多从架构。运行上，主流的负载均衡器包括LVS、HAProxy、Nginx。如何实现读写分离？大多数企业都是在代码层面实现读写分离，效率相对较高。另一种方式是通过代理程序实现读写分离，这种方式在企业中很少使用。常见的代理程序包括MySQLProxy和Amoeba。在这样的数据库集群架构中，大大提高了数据库的高并发能力，解决了单台服务器的性能瓶颈问题。如果一个从库可以处理2000个QPS，那么5个从库可以处理1w个QPS（理论上），数据库的横向扩展也很容易。有时，面对大量写入操作的应用，单台服务器的写入性能无法满足业务需求。如果做双主，会遇到数据库数据不一致的情况。这样做的原因是，不同应用的用户可能操作两个数据库，同时进行更新操作会导致两个数据库的数据库数据发生冲突或不一致。在单个数据库中，MySQL使用存储引擎机制表锁和行锁来保证数据的完整性。在多个主库中如何解决这个问题？有一套基于perl语言的主从复制管理工具，叫做MySQL-MMM（Master-MasterreplicationmanagerforMysql，Mysql主从复制管理器），这个工具最大的优点就是只提供了一个数据库写操作同时进行，有效保证数据的一致性。4.2添加缓存在数据库中添加缓存系统，将热点数据缓存在内存中。如果缓存中有要请求的数据，则不会将结果返回给数据库，以提高读取性能。缓存实现包括本地缓存和分布式缓存。本地缓存将数据缓存在本地服务器内存或文件中。分布式缓存可以缓存海量数据，具有良好的可扩展性。主流的分布式缓存系统有memcached和redis。Memcached性能稳定，数据缓存在内存中，速度非常快。QPS可以达到8w左右。如果要数据持久化，就选redis，性能不比memcached低。工作过程：4.3分库分库是将相关的表根据不同的业务划分到不同的数据库中，如web、bbs、blog等库。如果业务量大，分库还可以做主从架构，进一步避免单个库压力过大。4.4分表数据量越来越大。数据库中的某个表有几百万条数据，查询和插入非常耗时。如何解决单表压力？你应该考虑是否将这张表拆分成多个小表来减轻单表的压力，提高处理效率。这种方法称为分表。分表技术比较繁琐。修改程序代码中的SQL语句，需要手动创建其他表。也可以使用合并存储引擎来实现分表，比较简单。分表后，程序是对一张总表进行操作。这张总表不存储数据，只存储一些子表关系和更新数据的方式。总表会根据不同的查询，把压力分成不同的小表，从而提高并发性和磁盘I/O性能。分表分为垂直分表和水平分表：垂直分表：将字段较多的原表拆分为多张表，解决表宽问题。可以将不常用的字段放在一个表中，可以将大的字段独立放在一个表中，也可以将关系密切的字段放在一个表中。水平拆分：将原表拆分为多张表，每张表结构相同，解决单表数据量大的问题。4.5分区分区就是将一个表的数据按照表结构中的字段（如range、list、hash等）划分成多个块。这些块可以在一个磁盘上，也可以在不同的磁盘上。分区后表面上还是一张表，但是数据散列在多个位置。这样多个硬盘可以同时处理不同的请求，从而提高磁盘I/O读写性能，实现起来也比较简单。注：添加缓存、分库、分表、分区主要由程序员实现。5.数据库监控数据库监控维护是运维工程师或DBA的主要工作，包括性能监控、性能分析、性能调优、数据库备份恢复等。5.1性能状态关键指标QPS，QueriesPerSecond：numberofqueriespersecond，一个数据库每秒可以处理的查询数TPS，TransactionsPerSecond：每秒的事务数通过showstatus查看运行状态，会有300多条状态信息记录，其中有有几个值可以帮助我们计算QPS和TPS根据Questions计算QPS：mysql>showglobalstatuslike'Questions';mysql>showglobalstatuslike'Uptime';根据Com_commit和Com_rollback计算TPS：mysql>showglobalstatuslike'Com_commit';mysql>showglobalstatuslike'Com_rollback';mysql>showglobalstatuslike'Uptime';5.2打开慢查询日志MySQL打开慢查询日志，分析哪条SQL语句慢，使用set设置变量，重启服务失败，可以在my.cnf中添加参数***生效。mysql>setglobalslow-query-log=on#开启慢查询功能mysql>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#查看最慢查询前三5.3数据库备份备份数据库是最基本的工作，也是最重要的，否则后果很严重，你懂的！但是由于数据库比较大，几百G，备份起来往往比较费时，所以要选择高效的备份策略。对于数据量大的数据库，一般采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比较适合小型数据库。因为是逻辑备份，备份和恢复的时间比较长。mysqlhotcopy和xtrabackup是物理备份，备份恢复速度快，热拷贝不影响数据库服务。推荐使用xtrabackup，支持增量备份。5.4数据库修复有时MySQL服务器突然断电或非正常关机，会损坏表，导致无法读取表数据。这时候可以使用mysql自带的两个工具myisamchk和mysqlcheck进行修复。myisamchk:只能修复myisam表，需要停止数据库分析表-o--safe-recover旧的恢复模式，如果-r不能修复，可以用这个参数试试-F--fast只检查未正常关闭的表mysqlcheck：myisam和innodb表都可以使用，不需要stop数据库，如修复单表，可以在数据库后面加上表名，常用参数用空格隔开：-a--all-databases查所有库-r--repair修表-c--check查表，默认选项-a--analyze分析表-o--optimize优化表-q--quik最快查表或修表-F--fastonlychecktablesthatarenotclosednationally将数据放在关系型数据库中，在海量数据查询和分析方面已经无法获得更好的性能。因此，NoSQL开始流行起来。非关系型数据库数据量大，性能高。同时，它们也弥补了关系数据库的一些不足。渐渐地，大部分公司都将一些业务数据库存储在NoSQL中，比如MongoDB、HBase等。数据存储采用分布式文件系统，如HDFS、GFS等，采用Hadoop、Spark、Storm等进行海量数据计算分析。
