从七个方面对mysql数据库调优

在网上找的数据库调优知识，有点凌乱，而且数据库调优是面试必问的，因此这里总结一下。话不多说，直接开始。

1. 存储引擎的选择

mysql 数据库的存储引擎这里举例两种，分别是MyISAM和InnoDB，来对比一下：
存储空间：
MyISAM:存储空间小，可被压缩。
InnoDB:需要更多的内存和存储，它会在内存中建立缓冲池用于高速缓冲数据和索引（因为内存的读写速度过慢，所以需要缓冲池缓冲）。
事务支持：
MyISAM：强调的是性能，每次查询都具院子性，执行速度比InnoDB块，但是不提供事务支持。
InnoDB：提供事务支持，外键等功能。具有事务，回滚，崩溃修复能力。
锁差异：
MyISAM只支持表锁，用户在操作mysiam表时，select,update,delete,insert语句都会给表自动加锁，这样导致并发性极低。
InnoDB:支持事务和行锁，行锁是指行锁大幅度提高了用户的并发操作。
表主键：
MyISAM:允许没有主键和索引的表存在，索引都是保存行的地址。
InnoDB:如果没有设定主键，就会自动生成一个主键（用户不可见）。
表的具体行数：
MyISAM：保存了表的总行数，执行select count() from table就会直接取出该值。
InnoDB:没有保存表的行数，执行select count() from table会遍历整个表，非常耗资源，但是两个引擎使用where条件后，效率是差不多的，都需要扫描整个表计算某个字段的次数。
综上所诉：
1)MyISAM管理非事务表，它提供高速检索，如果应用需要执行大量select语句，应该选择MyISAM.
2)InnoDB用于事务处理应用程序，提供事务支持。如果应用中需要大量的INSERT和UPDATE操作，应该选择InnoDB,这样并发操作性能。
2. 缓存
Mysql缓存机制就是缓存sql文本及缓存结果，用Key-Value形式保存在服务器内存中，如果运行形同的sql，服务器直接从缓存中去获取结果，不需要再去解析，优化，执行sql。这样就很大程度上缓解访问数据库的压力。
如果这个表修改了，那么这个表的所有缓存不再有效。显然，缓存不适用于那些频繁更新（UPDATE，INSERT）的表，对于一些不变的数据且有大量相同sql查询的表，查询缓存会节省很大的性能（比如查询某件商品A的信息，一件商品的信息基本不会改变，并且查询改商品的sql语句也不会变，这是我们就可以把这件商品的信息以及查询该商品的sql语句放到缓存）。
缓存的工作流程：

• 服务器接收SQL，以SQL作为key查询缓存表。

• 如果找到了缓存，则直接返回缓存。

• 如果没有找到，则执行SQL查询，包括SQL解析，优化等。

• 执行完SQL查询结果后，将SQL查询结果存进缓存表。

3. sql执行计划，sql运行时间明细
sql执行计划， 使用mysql相应的功能和语法来对在进行数据库查询时预先估计查询要涉及多少行，使用哪些索引，运行时间等。
执行计划，简单来说就是SQL在数据库执行时表现情况。通过在SQL语句前加上explain可以查看SQL的执行计划（把一一条SQL分解，列出每一步需要干什么，按照步骤依次执行，这样我们就能看出在哪个步骤耽误了时间）。
举个例子，执行以下语句：

每个字段的含义：
id:：
表示查询中select操作表的顺序，id值相同的从上到下顺序执行，id值不同时大的先执行。
select_type：
显示对应行是简单的还是复杂的select。simple值以为着不包含子查询和UNION。
查询中有任何复杂的子部分，最最晚层标记为PRIMARY。

table：
输出数据行所在的表的名称。
type：
表示访问类型，最常见的有以下几种：
ALL（扫描全表），index（索引扫描），range（范围扫描，比如使用between and,大于，小于等），ref（非唯一索引扫描），const（常数索引）。访问速度一次从慢到快。

possible_key：
指出mysql能使用哪些索引来优化查询。查询所涉及的列上的索引都会被列出，但不一定会被使用，只是起个提示作用。
key：
显示mysql实际使用的索引。
key_len：
表示索引使用的字节数，这个长度是由字段定义时计算而来的，并非实际长度。

上图是各种类型的长度。
ref：
连接匹配条件，如果走主键索引的话，值为const;全表扫描的话，值为null。
rows（关键）：
扫描行数，意思是说需要扫描多少行才能获取目标行数。所以对于优化来说，当然是想需要扫描最少的行数就得到目标行数。大部分SQL优化都是在减小这个值。
Extra：
这个属性非常重要，这个属性包括SQL执行时的真是情况个信息，如提示“using where”就是表示使用了where关键字来筛选得到结果。常用的有：

我们可以看到上面例子执行的SQL语句是没有使用索引（possible_key和key都为null）的，而且是通过扫描全表（tpye为ALL,rows=5）才得到的结果。这样是很消耗数据库的性能的（在数据量大的情况下）。我们可以通过索引优化查询速度。

再来执行这个SQL语句：

我们可以看到，这次使用的是索引（使用的是非唯一索引，type=ref），通过rows列可知值查询了1行就得到结果。这就是通过查看sql执行计划，找到可进行优化的点，进行优化。
4. 分库分表
对于单库来说：

• 数据量超过100w性能就会下降。

• 在并发环境下，短时间大量的访问会对磁盘的IO造成非常的影响。

• 数据库连接是非常稀少的资源，如果一个库里有用户，商品，订单想过的数据，当海量用户同时操作时，数据库连接就成为很大的瓶颈。
  所以分库分表很有必要，有两种方式，分别是垂直拆分以及水平拆分。
  垂直拆分：

垂直分库：
垂直分库是对一个系统中不同的业务进行拆分，可以客户一个库，存款一个库，贷款一个库，支付一个库，拆分后库放到不同的服务器上。

垂直分表：
垂直分表是基于表的“列”进行，某个表字段较多，可以把不经常用到或字段长度较大的字段拆分到扩展表中。通过“大表拆小表”的方式，便于开发与维护，也能避免跨页的问题。因为MYSQL底层是通过数据页存储的，一条记录占用太大空间会导致跨页，造成额外的性能开销。
同时，数据库以行的单位将数据加载进内存，如果表中字段较短且访问频率高，内存能加载更多数据，命中率更高，较少了磁盘IO，提高数据库性能。

垂直切分的有点：

• 使系统解耦，业务逻辑清晰。
• 在高并发环境下，提升了数据库连接数，硬件资源（磁盘等）的瓶颈。

当然也有缺点：

• 部分表无法连接（join），只能通过接口聚合方式解决，提升了开发难度。
• 分布式事务处理复杂。
• 依然存在单表数据量大的问题。

水平拆分：
垂直划分不能解决单表数据量的问题，水平划分就派上了用场。
水平拆分是指不改变表的逻辑（字段，数据间的逻辑），只是将一个表的数据分成若干部分存在若干个表（字段跟原来的表一样）里，然后分散到多个服务器上。

水平切分后同一张表会出现在多个数据库/表中，每个库/表的内容不同。
水平拆分的优点：
不存在单库数据库量过大，高并发的瓶颈，提高了系统稳定及负载能力。

分库分表后带来的问题：

做了垂直分库或水平分库后，必然会涉及到跨库执行SQL的问题，这样就引发了分布式事务的问题。当更新内容分布在不同库时，不可避免带来跨库事务问题，没有简单的方案，一般使用“XA协议”和“两阶段提交”处理。
XA协议：规定事务管理器和资源管理器接口，采用二阶段提交协议。
第一阶段：
应用程序调用事务管理器的提交方法，第一阶段分为两个步骤：

• 事务管理器通知参与该事务的资源管理器，通知它们准备事务。
• 资源管理器接收消息开始准备阶段，写好事务日志并执行事务，但是不提交。然后将准备就绪的消息返回给事务管理器。
  第二阶段：
  
• 事务管理器接收各个资源管理器的消息，开始逐一分析，如果有任意其一失败，则发送回滚命令，否则发送提交事务命令。
• 资源管理器收到提交事务命令后，执行命令，并将提交消息返回给事务管理器。
  事务管理器接收消息后，事务结束应用程序继续执行。
  分两步的原因是一让事务管理器有统一管理的机会，二是尽可能晚地提交事务，让事务在提交前尽可能完成所有工作。
  同时，二阶段提交协议是为了保证事务一致性，事务管理器和资源管理器的每一步操作都会有日志记录，为出现故障后的恢复准备。
  5. 读写分离，主从复制
  读写分离是指将读操作（select）和写操作（insert,update,delete）分开，
  读操作在从数据库里进行，写操作在住数据库里进行。
  为什么要读写分离？
  因为写操作效率比读操作效率低非常多，如果不分离的话读操作的效率会被大大降低。
  读写分离就是在主服务上修改（写），数据会同步到从服务器上，从服务器上只能读数据，不能写入，实现备份的同时也实现了数据库性能的优化。
  主从同步原理：
  
  主服务器master记录数据库操作日志到Binary log，从服务器开启i/o线程将二进制日志记录的操作同步到relay log（存在从服务器的缓存中），另外sql线程将relay log日志记录的操作在从服务器执行。
  6. 碎片整理
  每当从表中删除一个数据时，都会让这段空间空出来，即使插入数据使，mysql会尽可能使用这些碎片空间，但还是无法全部被重新利用。所以碎片整理是很有必要的。
  可以通过这两条语句进行碎片整理
  MyISAM引擎：Optimize table 表名;
  InnoDB：alter table 表名 ENGINE=‘InnoDB’；
  7. 备份