mysql-数据库优化篇

1.数据库优化
系统的吞吐量瓶颈往往出现在数据库的访问速度上，随着应用程序的运行，数据库中的数据会越来越多，处理时间会相应变慢，数据是存放在磁盘上的，读写速度无法跟内存相比。
优化原则：减少系统瓶颈，减少资源占用，增加系统反应速度 。
**2.数

• 据库结构优化**
  一个好的数据库设计方案对于

数据库 的性能往往会起到事半功倍的作用，需要考虑数据冗余，查询和更新的速度。字段的数据类型是否合理等多方面的内容。
将字段很多的表分解成多个表：对于字段较多的表，如果有些字段的使用频率低的字段存在而变慢。
对于需要经常联合查询的表，可以建立中间表以提高查询效率，通过中间表将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
增加冗余字段：设计数据表时应尽量遵循三大范式理论的规约，尽可能的 减少冗余字段，让数据库设计看起来精致优雅。但是，合理的加入冗余字段可以提高查询速度。
表的规范程度越高，表表之间的关系越多，需要连接查询 的情况也就越多，性能也就越差。
冗余字段的值在一个表中修改了。就想办法在其他表中更新，否则就会导致数据的不一致。
3.MYsql数据库cpu飙升处理方式
当cpu飙升到500%时，先用操作系统命令top命令观察是不是MYSQL占用导致的，如果不是就找出占用进程并进行相关处理。
如果是mysql造成的，show processlist，看看里面跑的session情况，是不是消耗资源的sql在运行，找出高消耗的sql,看看执行计划是否准确，index是否缺失，或者实在是数据量大造成的。
一般来说，肯定要kill掉这些进程（同时观察cpu使用率是否下降），等进行相应的调整（加索引，改sql，改内存参数），再重新跑sql。
也有可能是每一个sql消耗资源并不多，但是突然之间，有大量的session连进来导致cpu飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整(限制连接数等)。
4.大表的优化
当mysql单表数据量过大时，数据库的CRUD性能会明显下降，一些常见的优化措施：
1. 限定数据的范围：务必禁止不带任何限制数据范围条件查询语句
2. 读写分离：经典的数据库拆分方案，主库负责写，从表负责读。两种方式（1）通过代码实现逻辑，对读写请求进行解析，分发到不同数据库.spring注入数据源，包括Master-Slave,手写Mybis拦截器，判断一个sql语句是读还是写，选择对应的数据源进行操作（2）基于类似MYCat中间件来帮助实现读写分离的效果，只需要配置就可以实现读写分离的效果提升。
3. 缓存使用mysql的缓存，另外对重量级，更新少的数据可以考虑使用应用级别的缓存。
4. 垂直分区：根据数据库里面数据表的相关性进行拆分，数据表列的拆分，把一张列较多的表拆分为多张表。用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。优点：可以使行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。缺点：主键会出现冗余，需要管理冗余列并会引起Join操作，可以通过在应用层进行join操作。让垂直分区会让事务变得更加复杂。把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。适用场景：表中某些列常用，某些列不常用，可以使数据行变小，一个数据页能存储更多数据，查询时I/O减少次数
5. 水平分区：保证数据库结构不变，通过某种策略存储数据分片，这样每一片数据分散到不同的表或者库中，到达分布式的目的，水平拆分可以支撑非常大的数据量。拆分的是数据表的行，行数超过200万时，就会变慢，这时可以把一张表拆分成多张表来存放。拆分表可以支持非常大的数据量，需要注意的是分表仅仅解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升mysql并发能力没有什么意义。水平拆分最好还是分库。水平拆分能支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，夸界点的join性能差，逻辑复杂，尽量不要对数据进行分片，因为带来逻辑，部署，运维的各种辅助，一般的数据表在优化得当的情况下支持千万以下的数据量没有大问题。如果实在要分片，尽量选择客户端分片架构，可以减少一次和中间件的网络IO.适用场景：表中数据本身就有独立性，需要把数据存放在多个介质上。缺点：给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需要UNION操作，在许多数据库应用中,这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数。
6. 数据库分片的两种常见方案：（1）客户端代理：分片的逻辑在应用端，封装在jar包，通过修改或者封装JDBC层来实现，当当网sharding-JDBC,阿里的TDDL是两种常用的实现。（2）中间件代理：在应用和数据中间加一个代理层。分片逻辑统一维护在中间件服务中MYCAT,360的atlas,网易的DDB等等都是这种架构。
5.分库分表后的问题
1.事务支持：分库分表后，就成看分布式事务，如果依赖数据库本身的分布式事务管理功能去执行事务，将付出较大的性能代价，如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程的负担。
2.跨库join：只要进行切分，跨节点join的问题是不可避免的，但是良好的设计和切分却可以减少此类情况的发生，解决这一问题的普遍做法是分两次查询，第一次查询的结果集中找出关联的id，根据这些id发起第二次请求得到关联数据。
3.跨节点count,order by ,group by以及聚合函数问题。因为他们都是基于全部数据集合进行计算，多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。
4.数据迁移，容量规划，扩容等问题，利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。
5.id问题:一但数据库被切分到多个物理节点上，我们将不能再依赖数据库自身的主键生成机制，一方面某个分区数据库自生成的id无法保证全局的唯一，另一方面应用程序在插入数据之前需要先获取ID,以便进行sql路由。一些常见的UUID使用做主键是最简单的方法，缺点也明显，由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，建立索引和基于索引进行的查询都存在性能问题。Twitter的分布式自增ID算法Snowflake 在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。
6.跨分片的排序分页：分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序。
6.mysql的复制原来以及流程
1.主从复制：将主数据库的DDL和DML操作进行二进制日志传输到从数据库上，然后将这些日志重新执行，从而使得从数据库的数据与主数据库保持一致。
2.主从复制的作用：主数据库出现问题，可以切换到从数据库，可以进行数据库层面的读写分离，可以在从数据库上进行日常备份。
3.mysql主从复制解决的问题：数据分布：随意开始或者停止复制，并在不同地理位置分布数据备份，负载均衡：降低单个服务器的压力，高可用和故障切换：帮助应用程序避免单点失败，升级测试：可以用更高版本的mysql作为从表。
4.mysql主从复制工作原理：在主库上把数据更高记录到二进制日志，从库将主库的日志复制到自己的中继日志，从库读取中继日志的事件将其重放到从库数据中
5.基本原理流程，3个线程之间的联系：主binlog:记录所有改变了数据库的语句，放进master的binlog中，从io:在使用start slave之后，负责master上拉取内容，放进自己的relay log中，从sql：执行relay log中的语句。复制过程：Binary log：主数据库的二进制日志。Relay log：从服务器的中继日志
第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。
7.读写分离的解决方案
读写分离依赖于主从复制，而主从复制又是为读写分离服务，因为主从复制要求slave不能写只能读（如果对slave执行写操作那么show slave status将会呈现Slace_sql_Running=NO，此时你需要按照前面提到的手动同步slave）
1.使用mysql-proxy代理
优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的账号，mysql官方不建议实际生产中使用。缺点：降低性能，不支持事务。
2.使用AbstractRoutingDataSource+aop+annotation在到层决定数据源。如果采用了mybatis，可以将读写分离放在orm层，比如mybatis可以通过mybatisplugin拦截SQL语句，所有的insert，update,delete都访问master库，所有的select都访问salve库，这样对于dao层都是透明，plugin实现时可以通过注解或者分析语句是读写方法选定主从库，不过这样依然有一个问题，也就是不支持事务，所以我们还是需要重写一下DataSourceTransactionManager,将read-only的事务扔进读库。其余的有读有写的放进写库。
3.使用AbstracRoutingDataSourse+aop+annotation在service层决定数据源，可以支持事务，缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需要特殊处理。
8.备份计划，mysqldump自带的逻辑备份工具，xtranbackup基于InnoDB的在线热备工具
1.备份计划，视库的大小来定，一般来说100G内的库，可以考虑使用mysqldump来做。因为mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行全量备份|（mysqldump备份出来的文件比较小，压缩之后更小）。100G以上的库，可以考虑xtranbackup来做，备份速度明显要比mysqlump要快，一般选择一周一个全备份，其余每天做增量备份，备份时间为业务低峰期。
2.备份时间：物理备份恢复快，逻辑备份恢复慢，这里跟机器，尤其是硬盘的速率有关系，20G的2分钟（mysqldump），80G的30分钟（mysqldump）,111G的30分钟（mysqldump）,288G的3小时（xtranbackup），3T的4小时（xtranbackup）,逻辑导入时间一般是备份时间5倍以上。
3.备份恢复失败如何处理，首先在恢复之前就应该做好准备工作，避免恢复时出错，比如说备份之后的有效性检查，权限检查，空间检查等，如果万一报错，根据错误提示来调整。
4.mysqldump和xtrabackup实现原理。
mysqldump属于逻辑备份，加入-single-transaction选项可以进行一致性备份。后台进程回先设置session的事务隔离级别RR(set,session transaction isolation levelrepeatable read),之后显示开启一个事务（start transaction/!40100 with consistentsnapshot/），这样就保证了该事务里读到的事务都是事务时候的快照。之后再把表数据读取出来。如果加上-master-data=1的话，刚开始的时候还会加一个数据库的读锁（flush tables with read lock),等开启事务后，再记录下数据库此时binlog的位置（shiwmaster status）,马上解锁，再读取表的数据，等所有的数据都已经导完就可以结束事务。
xtrabackup属于物理备份，直接拷贝空间文件，同时不断扫描产生的redo日志并保存下来最后完成innodb的备份后，会做一个fiush engine logs 的操作确保所有的redo log都已经落盘（涉及到事务的两阶段提交概念，因为xtrbackup并不拷贝binlog,所以必须保证所有的redo log都落盘，否则可能会丢失最后一组提交事务的数据）。这个时间点就是innodb完成备份的时间点，数据文件虽然不是一致性，但是有这段时间的redo就可以让数据文件达到一致性（恢复的时做的事情）。然后需要flush tables with read lock ，把myisam等其他引擎的表给备份出来，备份完后解锁，这样就做到了完美热备份。
9.数据表损坏的修复方式
使用myisamchk修复（1）修复前将mysql服务停止（2）打开命令行方式，然后进入到mysql的/bin目录。（3）执行myisamchk-recover数据库所在路径/*.MYI使用repair table或者OPTIMIZE table命令来修复，REPAIR TABLE table_name修复表OPTIMIZE TABLE table_name 优化表REPAIR TABLE用于修复被破坏的表。OPTIMIZE TABLE用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE 命令后这些空间被回收，并且对磁盘上的数据行进行重排