MySQL优化（一）——哪些因素影响了数据库性能
所谓知己知彼，百战不殆。既然要优化数据库，我们就首先要知道，优化的是什么，或者说：什么因素影响了数据库的性能。
影响数据库因素主要因素总结如下：
sql查询速度
网卡流量
服务器硬件
磁盘IO
以上因素并不是时时刻刻都会影响我们的数据库性能，而就像木桶效应一样：如果其中一个因素严重影响性能，那么整个数据库性能就会严重受阻。另外，这些影响因素都是相对的，例如：当数据量并没有达到百万千万这样的级别，那么sql查询速度也许就不是个重要因素，换句话说，你的sql语句效率适当低下可能并不影响整个效率多少，反之，这种情况，无论如何怎么优化sql语句，可能都没有太明显的效果。
因此，知道哪些影响因素会直接导致哪些现象产生，是至关重要的经验，就像福尔摩斯一样，通过现象看本质。接下来我们对不同的现象与影响因素做一一对应的总结。
什么是QPS：每秒钟查询量。如果每秒钟能处理100条查询sql语句，那么QPS就约等于100
什么是TPS：每秒钟事务处理的数量。
在大促的情况下，访问量暴增。这种情况下，sql语句的优化显得最直接最有效。由于现在的mysql不支持多cpu并发运算，即每条sql只能由一条cpu执行。这也就意味着，如果我们想提高单挑sql的执行速度，无法通过增加cpu的方式达到效果。
大量的并发：数据库连接数被占满
对于数据库而言，所能建立的连接数是有限的，mysql中max_connections参数默认值是100。
超高的CPU使用率：因CPU资源耗尽而出现宕机
使用更好的磁盘设备解决。
调整计划任务
如何避免无法连接数据库的情况：
减少从服务器的数量。从服务器都要从主服务器上复制日志，所以，从服务器越多，网络流量越大。
进行分级缓存。前方大量缓存突然失效会对数据库造成严重的冲击。
避免使用“select * ”进行查询
分离业务网络和服务器网络
什么样的表可以称为大表
记录行数巨大，单表超过千万行
表数据文件巨大，表数据文件超过10G
很难在一定的时间内过滤出所需要的数据
建立索引需要很长的时间
风险：MySQL版本<5.5建立索引会锁表，>=5.5虽然不会锁表但会引起主从延迟。
修改表结构需要很长时间锁表
风险：会造成长时间的主从延迟；影响正常的数据操作
难点：
分表主键的选择。如订单号，订单地区等，好的分表主键选择，会对后期的扩展更有利
分表后跨分区数据的查询和统计。
分库分表需要消耗大量的人力物力，而且要冒着影响业务的风险，所以要慎重。
大表的历史数据归档，可以减少对前后端业务的影响。
难点：
归档时间点的选择。例如订单表，可以对一年前的数据进行归档；日志类的数据，对一个月前的数据归档即可。
如何进行归档操作。
事务是数据库系统区别于其他一切文件系统的重要特性之一
事务是一组具有原子性的SQL语句
事务特性：原子性，一致性，隔离性，持久性
一个事务必须被视为一个不可分割的最小工作单元。整个事务要么全部提交成功，要么全部失败。
例如：银行转账，我向你汇钱，要么成功，我的账户减少1000元，你的账户增加1000元。要么失败，我不减，你也没有增加。不能出现：我的账户减少1000，这时候断电了，你没收到。
事务将数据库从一种一致性状态转换到另一种一致性状态，在事务开始之前和事务结束之后数据库中的数据的完整性没有被破坏
例如：银行转账，转来转去，总和应该保持不变。在我看开，一致性其实就是宏观上强调了一下原子性。只要原子性原则没有被破坏，应该就总是一致的。
一个事务对数据库中的数据进行修改，在未提交完成前对其他事务是否可见的。隔离性有四种级别：
未提交读（READ UNCOMMITED）
已提交读（READ COMMITED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）
例如：银行转账。比如你答应给你女朋友转1000块钱给她买粉。
未提交读：你开启一个事务，然后转账1000给你女朋友，注意不要提交事务。然后让你女朋友开启一个事务查账户，她会发现账户确实多了1000，然后屁颠屁颠出门了。这时候你回滚事务，等于转账失败。这时候你的女朋已经选好准备刷卡了，可是钱没了。你就可以教训她了。为什么女朋友尴尬了，就是因为她读到了你没有提交事务的数据，这样的数据是脏数据，是不算数的。
已提交读：后来你女朋友改了数据库的隔离级别。这次你还想这么玩，可是当你不提交事务时，你女朋友就永远看不到自己的账户到账1000，你只能老老实实的提交事务了，事务已提交，钱就回不来了。
这种方式有效地防止了程序员的女盆友上当受骗
可重复读：女朋友觉得这样还不够，把数据库隔离级别又改了。这时候你由于上次的教训，只能老老实实汇钱了。但是你发现无论你汇了多少钱过去，而且提交了，女朋友的账户就是不见加钱。你开始怀疑了，原来女朋友在要钱的时候开启了一个查询的事务，这个事务在你汇钱之前，可重复读的隔离级别这是后根本看不到此时的账户变化，查询结果也一直是她事务开启前的状态。等你汇了够多了，她提交查询事务，第二次查询账户，整整多了2000块呢。
串行化：互联网行业很少用，不介绍了。
一旦事务提交，则其所做的修改就会永久保存到数据库中。
运行时间比较长，操作数据比较多的事务
锁定太多数据，造成大量的阻塞和锁超时。
回滚时需要的时间比较长。
执行时间长，容易造成主从延迟。
避免一次处理太多的数据
移出不必要在事务中的select操作
MySQL优化（二）——什么影响了MySQL性能
影响性能的几个方面：
服务器硬件
服务器系统
数据库存储引擎的选择
数据库参数配置
数据库结构设计和SQL语句
对于cpu密集型的应用，我们需要加快sql语句的处理速度。由于mysql的sql语句处理是单线程的，因此我们需要更好的cpu，而不是更多的cpu。
一个cpu同时只能处理一条sql语句。所以，高并发量的情况下，就需要更多的cpu而不是更快的cpu。
64位已经是默认配置了。
内存的io要远高于磁盘，即便是SSD或者Fusion_IO。所以把数据缓存到内存中读取，可以大大提高性能。
常用的mysql引擎中，MyISAM把索引缓存到内存，数据不缓存。而InnoDB同时缓存数据和索引。
缓存不仅对读取有益，对写入也是可以优化的，我们可以通过缓存，将多次写入合并成一次写入操作。
怎么选择内存：主频尽量大，型号尽量相同，单条内存容量尽量打。
传统机械磁盘
RAID增强传统机械磁盘
固态存储SSD和PCIe卡
网络存储NAS和SAN
存储容量
传输速度
访问时间
主轴转速
物理尺寸：越小性能越高，存储空间越小
什么是RAID：把多个容量小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。
RAID 0，多磁盘串联。成本最低，数据容易丢失
RAID 1，镜像。磁盘利用率降低一半。
RAID5，分布式奇偶校验磁盘阵列
RAID10，分片镜像
等级特点是否冗余盘数读写
RAID0便宜，快速，危险否N快快
RAID1高速读，简单，安全有2快慢
RAID5安全，成本这种有N+1快取决于最慢的盘
RAID10贵，高速，安全有2N快快
推荐RAID10
相比机械磁盘，固态磁盘有更好的随机读写性能。
相比机械磁盘，固态磁盘能更好的支持并发。
相比机械磁盘，固态磁盘 更容易损坏
SSD：
1.使用SATA接口。可以替换传统磁盘而不需任何改变
2.SATA接口的SSD同样支持RAID技术。注意SSD的RAID控制器与传统不同。
PCI-E
1.无法使用SATA接口，需要独特的驱动和配置
2.价格相对于SSD要贵，但是性能比SSD更好
固态存储的使用场景：
1.适用于存在大量的随机I/O的场景。
2.适用于解决单线程负载的I/O瓶颈。
SAN：通过光钎链接服务器，服务器可以将其当做硬盘使用。适合大量顺序读写
NAS：使用网络连接，通过基于文件的协议如NFS或SMB来访问。
网络存储适合的场景：
数据库备份
延迟、带宽（吞吐）
网络带宽的影响，不必多说。可能很多人认为数据库服务器与Web服务器的通信是在内网下的，带宽影响不大。其实在大促的情况下，我们有50台服务器，同时向数据库请求2M的数据，那么就需要100M的带宽了。
建议：
采用高性能和高带宽的网络接口设备和交换机。
对多个网卡进行绑定，增强可用性和带宽。
尽可能的进行网络隔离。
cpu
1、64位的cpu一定要工作在64位的系统下。
2、对于并发比较高的场景，cpu的数量比频率重要
3、对于cpu密集型场景和复杂SQL则频率越高越好。
内存
1、选择主板所能使用的最高频率的内存
2、内存的大小对性能很重要，所以尽可能的大
I/O子系统
PCIe–>SSD–>Raid10–>磁盘–>SAN
MySql适合的操作系统
Windows
FreeBSD
Solaris
Linux
下面的内容以CentOs系统为例
内核相关参数（/etc/sysctl.conf）
网络相关
- net.core.somaxconn=65535
对于一个TCP连接来说，服务器端和客户端需要进行三次握手来建立网络的连接。当三次握手成功之后，我们可以用netstat命令查看端口的状态由监听转变成了连接，接着该连接就可以传输数据了。对于一个监听状态的端口，都会有自己的监听队列，而该参数就决定了监听队列的最大长度。
1
2
- net.core.netdev_max_backlog=65535
- net.ipv4.tcp_max_syn_backlog=65535
加快tcp连接的回收
net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
tcp连接接收和发送缓冲区大小的默认值和最大值
net.core.wmem_default = 87380
net.core.wmem_=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216
检测占用的tcp连接是否已经失效
net.ipv4.tcp_keepalive_time=120 发送时间
net.ipv4.tcp_keepalive_intvl=30 没有返回再次发送的间隔
net.ipv4.tcp_keepalive_probes=3 发送几次
内存相关参数
kernel.shmmax = 4294967295
注意：
1、这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池大小
2、这个值的大小对于64位linux系统，可取的最大值为物理内存值-1byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池大小即可。
vm.swappiness=0
这个参数当内存不足时会对性能产生比较明显的影响。
拓展：
Linux系统内存交换区：在linux系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区。如果我们使用free-m在系统中查看可以看到类似下面内容，其中swap就是交换分区。
1
2
3
当操作系统因为没有足够的内存时就会将一些虚拟内存写到磁盘的交换区中，这样就会发生内存交换
在MySQL服务器上是否要使用交换分区有一些争议：
-主张 完全禁用 的风险：
1、降低操作系统的性能
2、容易造成内存溢出，崩溃，或都被操作系统kill掉
结论：在MySQL服务器上保留交换分区，但是要控制何时使用。vm.swappiness=0就是告诉Linux内核除非虚拟内存完全满了，否则不要使用交换区。
增加资源限制（/etc/security/limit.conf）
这个文件实际上是linux PAM也就是插入式认证模块的配置文件。
其中重要的配置是：打开文件数的限制。
* soft nofile 65535
* hard nofile 65535
其中：*表示对所有用户有效，soft指当前系统生效的设置，hard表明系统中所能设定的最大值，nofile表示所限制的资源是打开文件的最大数目，65535就是限制的数量。
结论：把可打开的文件数量增加到65535，以保证可以打开足够多的文件句柄。
注意：这个文件的修改需要重启系统才可以生效。
磁盘调度策略（/sys/block/devname/queue/scheduler）
noop anticipatory deadline [cfq]
查看：cat /sys/block/sda/queue/scheduler
noop（电梯式调度策略）
noop实现了一个FIFO队列，它像电梯的工作方法一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。
deadline（截止时间调度策略）
对数据库最好。
修改磁盘调度策略
echo deadline > /sys/block/sda/queue/scheduler
windows：
- FAT
- NTFS
linux：
EXT3
EXT4
XFS（性能更高）
注意：存储引擎是针对于表的而不是针对于库的（一个库中的不同表可以使用不同的存储引擎）
MySql5.5之前版本默认存储引擎。
MyISAM存储引擎表有MYD和MYI组成。
特性：
并发性与锁级别：对读写混合的并发性并不会太好
表损坏修复
支持的索引类型：全文索引
支持压缩：压缩表只支持读操作，不支持写操作
适用场景：
非事务型应用
只读类应用
空间类应用
系统表空间 和 独立表空间的选择
比较：
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table命令收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
建议：对Innodb使用独立表空间
如何把原来存在于系统表空间中的表转移到独立表空间中？
1、使用mysqldump导出所有数据库表数据
2、停止MySql服务，修改参数，并删除Innodb相关文件
3、重启MySql服务，重建Innodb系统表空间
4、重新导入数据
Innodb存储引擎的特性
事务性存储引擎
完全支持事务的ACID特性
Redo Log 和 Undo Log
Innodb支持行级锁（MyISAM支持表级锁），行级锁可以最大程度的支持并发，行级锁是由存储引擎层实现的
Innodb适用场景
基本上只要不使用到Innodb不包含的功能，基本上都使用Innodb作为默认存储引擎（例如MyISAM的空间应用，全文索引。但实际上MySql5.7之后Innodb已经支持了前面两点）。
网友评论