MySQL优化（一）——哪些因素影响了数据库性能

所谓知己知彼，百战不殆。既然要优化数据库，我们就首先要知道，优化的是什么，或者说：什么因素影响了数据库的性能。

影响数据库因素主要因素总结如下：

• sql查询速度
• 网卡流量
• 服务器硬件
• 磁盘IO

以上因素并不是时时刻刻都会影响我们的数据库性能，而就像木桶效应一样：如果其中一个因素严重影响性能，那么整个数据库性能就会严重受阻。另外，这些影响因素都是相对的，例如：当数据量并没有达到百万千万这样的级别，那么sql查询速度也许就不是个重要因素，换句话说，你的sql语句效率适当低下可能并不影响整个效率多少，反之，这种情况，无论如何怎么优化sql语句，可能都没有太明显的效果。
因此，知道哪些影响因素会直接导致哪些现象产生，是至关重要的经验，就像福尔摩斯一样，通过现象看本质。接下来我们对不同的现象与影响因素做一一对应的总结。

超高的QPS和TPS

风险：效率低下的SQL

什么是QPS：每秒钟查询量。如果每秒钟能处理100条查询sql语句，那么QPS就约等于100
什么是TPS：每秒钟事务处理的数量。

在大促的情况下，访问量暴增。这种情况下，sql语句的优化显得最直接最有效。由于现在的mysql不支持多cpu并发运算，即每条sql只能由一条cpu执行。这也就意味着，如果我们想提高单挑sql的执行速度，无法通过增加cpu的方式达到效果。

大量的并发和超高的CPU使用率

风险：

大量的并发：数据库连接数被占满

对于数据库而言，所能建立的连接数是有限的，mysql中max_connections参数默认值是100。

超高的CPU使用率：因CPU资源耗尽而出现宕机

磁盘IO

风险：磁盘IO性能突然下降。

使用更好的磁盘设备解决。

其他大量消耗磁盘性能的计划任务

调整计划任务

网卡流量

风险：网卡IO被占满（100Mb/8=100MB）

如何避免无法连接数据库的情况：

1. 减少从服务器的数量。从服务器都要从主服务器上复制日志，所以，从服务器越多，网络流量越大。
2. 进行分级缓存。前方大量缓存突然失效会对数据库造成严重的冲击。
3. 避免使用“select * ”进行查询
4. 分离业务网络和服务器网络

大表

什么样的表可以称为大表

• 记录行数巨大，单表超过千万行
• 表数据文件巨大，表数据文件超过10G

影响

慢查询

很难在一定的时间内过滤出所需要的数据

对DDL操作

建立索引需要很长的时间

风险：MySQL版本<5.5建立索引会锁表，>=5.5虽然不会锁表但会引起主从延迟。

修改表结构需要很长时间锁表

风险：会造成长时间的主从延迟；影响正常的数据操作

如何处理数据库中的大表

分库分表：把一张大表分成多个小表

难点：

• 分表主键的选择。如订单号，订单地区等，好的分表主键选择，会对后期的扩展更有利
• 分表后跨分区数据的查询和统计。

分库分表需要消耗大量的人力物力，而且要冒着影响业务的风险，所以要慎重。

历史数据归档

大表的历史数据归档，可以减少对前后端业务的影响。
难点：

• 归档时间点的选择。例如订单表，可以对一年前的数据进行归档；日志类的数据，对一个月前的数据归档即可。
• 如何进行归档操作。

大事务

• 事务是数据库系统区别于其他一切文件系统的重要特性之一

• 事务是一组具有原子性的SQL语句

  事务特性：原子性，一致性，隔离性，持久性
  

原子性：

一个事务必须被视为一个不可分割的最小工作单元。整个事务要么全部提交成功，要么全部失败。

例如：银行转账，我向你汇钱，要么成功，我的账户减少1000元，你的账户增加1000元。要么失败，我不减，你也没有增加。不能出现：我的账户减少1000，这时候断电了，你没收到。

一致性：

事务将数据库从一种一致性状态转换到另一种一致性状态，在事务开始之前和事务结束之后数据库中的数据的完整性没有被破坏

例如：银行转账，转来转去，总和应该保持不变。在我看开，一致性其实就是宏观上强调了一下原子性。只要原子性原则没有被破坏，应该就总是一致的。

隔离性：

一个事务对数据库中的数据进行修改，在未提交完成前对其他事务是否可见的。隔离性有四种级别：
未提交读（READ UNCOMMITED）
已提交读（READ COMMITED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

例如：银行转账。比如你答应给你女朋友转1000块钱给她买粉。

• 未提交读：你开启一个事务，然后转账1000给你女朋友，注意不要提交事务。然后让你女朋友开启一个事务查账户，她会发现账户确实多了1000，然后屁颠屁颠出门了。这时候你回滚事务，等于转账失败。这时候你的女朋已经选好准备刷卡了，可是钱没了。你就可以教训她了。为什么女朋友尴尬了，就是因为她读到了你没有提交事务的数据，这样的数据是脏数据，是不算数的。
• 已提交读：后来你女朋友改了数据库的隔离级别。这次你还想这么玩，可是当你不提交事务时，你女朋友就永远看不到自己的账户到账1000，你只能老老实实的提交事务了，事务已提交，钱就回不来了。
  这种方式有效地防止了程序员的女盆友上当受骗
• 可重复读：女朋友觉得这样还不够，把数据库隔离级别又改了。这时候你由于上次的教训，只能老老实实汇钱了。但是你发现无论你汇了多少钱过去，而且提交了，女朋友的账户就是不见加钱。你开始怀疑了，原来女朋友在要钱的时候开启了一个查询的事务，这个事务在你汇钱之前，可重复读的隔离级别这是后根本看不到此时的账户变化，查询结果也一直是她事务开启前的状态。等你汇了够多了，她提交查询事务，第二次查询账户，整整多了2000块呢。
• 串行化：互联网行业很少用，不介绍了。

持久性

一旦事务提交，则其所做的修改就会永久保存到数据库中。

什么是大事务

运行时间比较长，操作数据比较多的事务

风险：

• 锁定太多数据，造成大量的阻塞和锁超时。
• 回滚时需要的时间比较长。
• 执行时间长，容易造成主从延迟。

如何处理大事务

• 避免一次处理太多的数据
• 移出不必要在事务中的select操作