本篇介绍了MySQL的大致内容，包括性能瓶颈、事务、存储引擎、文件系统、Liinux参数优化和MySQL参数优化等;

后面会介绍关于MySQl的表结构优化、索引优化、SQL查询优化、分库分表和数据库监控等。

性能瓶颈网络IO

磁盘IORAID(磁盘冗余队列):可以把多个容量较小的硬盘组成一组容量更大的硬盘，并提供数据冗余来保证数据的完整性的技术。RAID0：只需要2块以上硬盘即可，但没有提供冗余或错误修复能力，读写都快

RAID1(磁盘镜像):原理是把一个磁盘的数据镜像到另一个磁盘上，也就是说写入一块磁盘的同时，在另一块磁盘上进行备份，保证了数据 的可靠性和可修复性；读取快、写入慢

RAID5(分布式奇偶校验磁盘队列):通过分布式奇偶校验块把数据分散到多个磁盘上，可以保证可靠性；读取快，写入最慢，一般用于从服务器

RAID10(分片的镜像):先对磁盘做RAID1后，在对两组RAID1的磁盘做RAID0,所以读写都有很快，比RAID5更快、重建更简单；

CPU :Mysql不支持多个CPU对同一sql的并发处理，所以单条SQL的运行，CPU的主频比核心数重要；

但如果并发量高，需要同时处理多条sql，那么核心数多也是有必要的；(在web应用中，核心数一般比主频重要些)

大表：所有DDL(数据定义语言)操作都是现在主库完成，然后在从库进行相同操作建立索引需要很长时间MySQL版本 < 5.5 建立索引会锁表

MySQL版本 >= 5.5 建立索引不会锁表但会引起主从延迟

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

对大表的处理分库分表 难点:分表主键的选择，分表后跨分区数据的查询和统计

历史数据归档：减少对前后端业务的影响(归档后可以提供对归档数据的查询接口)

大事务：运行时间长，操作数据比较多的事务锁定数据过多，造成阻塞和锁超时

回滚所需时间长

执行时间长，造成主从延迟

对大事务的处理避免一次处理太多数据

移除不必要在事务中的select操作

事务：要执行的一系列操作的整合；

四种特性(ACID):原子性(Atomicity) 同一事务中的一系列操作，要么全部成功，要么全部失败； 例如一个事务中包含了1.减少A记录金额100、2.增加B记录的金额200； 那么一个事务执行后，如果成功，则两个操作都成功；如果失败，则两个操作都失败；

一致性(Consistency) 任何情况下，整个系统的状态是一致的； 例如转账，A转账给B500元；那么事务一旦执行成功后，A-500，B+500，但无论如何，A和B的资金总额都不会发生改变,都是一致的;

隔离性(isolation) 某个事务在执行时，会修改、读取某个表的某些记录；那么此时，这些记录将针对其他事务隔离；当然这种隔离是有级别的，以下将针对Spring中的事务隔离级别进行讨论;

持久性(Durability) 一旦事务成功后，将会持久的保存在数据库中，即使出现系统故障，也会保存成功；

并发情况下事务的隔离级别较低可能会引发的几种问题：脏读(dirty read)：读出了无效数据 如果A事务读取了B事务未提交的数据，而后B事务回滚了；那么A事务读取到的数据将是错误的，即为脏读; 例如A事务中，a转账500元给b，那么将分为两个步骤(1.a余额-500，2.b余额+500)； 如果进行了第一个步骤(a-500)后，B事务读取了a的余额，然后，A事务回滚了； 那么B事务读取到的金额将是(a-500)元，那么在B事务的后续处理中，将会发生一些错误;

不可重复读(unrepeatable read)：一个事务范围内两个相同的查询却返回了不同数据 例如A事务先读取了a账户的余额，为1000元； 此时B事务中，扣除了a账户的余额500元； 而A事务又进行了一系列操作后，再次读取了a账户的余额，却和第一次读取不一致了；

幻读(phantom read)：一个事务范围内针对同一张表的查询返回了不同行数的记录 幻读和不可重复读的区别就在于：不可重复读是另一个事务修改了某条记录的值，导致两次读取值不一致；幻读则是另一个事务增加某条记录，导致两次读取记录数不一致(当然，记录数只是一个笼统的说明，更常见的情况就是统计一个表的数据时，没有发生不可重复读，发生了幻读，也就是增加了一条数据，那么两次统计的结果将不一致)。

Spring事务的隔离级别：DEFAULT (默认)

这是一个PlatfromTransactionManager(所有事务管理器的接口)默认的隔离级别，使用数据库默认的事务隔离级别.

READ_UNCOMMITTED (读未提交)

这是事务最低的隔离级别，它允许另外一个事务可以看到这个事务未提交的数据。这种隔离级别会产生脏读，不可重复读和幻像读。

READ_COMMITTED (读已提交) 保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。这种事务隔离级别可以避免脏读出现，但是可能会出现不可重复读和幻像读。

REPEATABLE_READ (可重复读)

这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻像读。它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了不可重复读 。

SERIALIZABLE(串行化) 这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻像读。

Spring事务的传播性：多个事务方法相互调用时，事务如何在这些方法间传播PROPAGATION_REQUIRED:支持当前事务，没有事务就新建一个，最常用;

PROPAGATION_SUPPORTS:支持当前事务，如果没有事务，以非事务方式处理

PROPAGATION_MANDATORY:支持当前事务，没有事务就抛异常

PROPAGATION_REQUIRES_NEW:新建事务，如果当前存在事务，把当前事务挂起

PROPAGATION_NOT_SUPPORTED:以非事务方式执行操作，有事务则挂起

PROPAGATION_NEVER:以非事务方式处理，有事务则挂起

PROPAGATTION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则进行与PROPAGATION_REQUIRED类似的操作;

嵌套是子事务嵌套在父事务中执行，子事务是父事务的一部分，在进入子事务之前，父事务建立一个回滚点，叫save point，然后执行子事务，这个子事务的执行也算是父事务的一部分，然后子事务执行结束，父事务继续执行。重点就在于那个save point。看几个问题就明了了： (1) 如果子事务回滚，会发生什么？

父事务会回滚到进入子事务前建立的save point，然后尝试其他的事务或者其他的业务逻辑，父事务之前的操作不会受到影响，更不会自动回滚。

(2) 如果父事务回滚，会发生什么？

父事务回滚，子事务也会跟着回滚！为什么呢，因为父事务结束之前，子事务是不会提交的，我们说子事务是父事务的一部分，正是这个道理。

(3) 事务的提交，是什么情况？

是父事务先提交，然后子事务提交，还是子事务先提交，父事务再提交？答案是第二种情况，还是那句话，子事务是父事务的一部分，由父事务统一提交。

CentOS系统参数优化

内核相关参数(/etc/sysctl.conf)

net.core.somaxconn TCP连接中的监听队列的大小

net.core.netdev_max_backlog 每个网络接口接收数据包的速度与内核处理相比过快的时候，允许被存储在队列中的请求数

net.ipv4.tcp_max_syn_backlog 未连接请求的最大数目

//防止连接数被占满的一些参数

net.ipv4.tcp_fin_timeout 连接等待超时时间

net.ipv4.tcp_tw_reuse

net.ipv4.tcp_tw recycle

//TCP接收和发送缓冲区的默认值和最大值

net.core.wmem_default

net.core.wmem_max

net.core.rmem_default

net.core.rmem_max

//加快资源利用率的参数 小一些要

net.ipv4.tcp_keepalive_time tcp连接保持时间 s

net.ipv4.tcp_keepalive_intvl 检测到消息未获得响应时，重发消息的间隔 s

net.ipv4.tcp_keepalive_probes 认定TCP连接失效前，最多重发几次消息

kernel.shmmax 单个共享内存段的最大值，要足够大，以便在一个内存段容下整个InnoDB缓冲池的大小

对于64位的linux系统，可以取的最大值是内存大小-1byte，建议大小为内存的一半；可以取最大值

vm.swappiness = 0

linux有一个分区，叫系统交换分区，使用free -m中swap就是交换分区；

当操作系统没有足够内存时，就将虚拟内存写到该区中，就会发生内存交换；

当此参数为0，表示，除非内存满了，否则就不使用交换分区，因为虚拟内存会使mysql性能下降

//增加资源限制(/etc/security/limit.conf) 需要重启

下面两个参数是打开文件数量的限制，将其加到该文件末尾即可

* 表示对所有用户有效；soft 表示当前系统生效的设置：hard 表示系统中所能设置的最大值

nofile 表示所限制的资源是打开文件的最大数目；65535 就是限制的数量

* soft nofile 65535

* hard nofile 65535

//磁盘调度策略(/sys/block/devname/queue/scheduler)

cat /sys/block/devname/queue/scheduler 查看目前的调度策略

下面是四种策略决

noop anticipatory deadline(数据库的最好选择) cfq

下面是修改调度策略为 deadline

echo deadline > /sys/block/devname/queue/scheduler

文件系统WindowsNTFS 如果使用Windows只有这一种文件系统的选择

LinuxETX3

ETX4 如果选择以上两种文件系统，需要注意的参数：挂载参数(/etc/fstab)data=writeback|ordered|journalwriteback:源数据写入日志，源数据和数据写入不同步，最快

ordered:只会记录源数据，但提供了一致性保证，在写入源数据前会先写数据，更加安全

journal:原子日志，数据写入前会写入日志，没必要，最慢

noatime，nodiratime 禁用记录文件目录的访问时间和读取时间，增加系统性能

XFS 性能比上面两种高，推荐

锁：主要作用是管理共享资源的并发访问，实现事务的隔离性共享锁(读锁) 多个线程可以同时读取同一资源，而不相互干扰

独占锁(写锁) 同一时间只有一个线程可以写入，并防止其它线程读取正在写入的资源

表级锁：开销小，并发低

行级锁：开销大，并发大

阻塞和死锁阻塞：一个事务中的锁等待另一个事务中的锁释放

死锁：两个或以上的事务相互等待

临时表系统查询等操作时生成的临时表未超出限制使用Memory表

超出限制使用Myisam临时表

create temporary table 建立的临时表

插件式存储引擎

存储引擎只要符合其规定，就可以使用；

存储引擎是针对于表的，而不是针对于库的

MySQL的所有引擎创建的表都有FRM文件，该文件用于记录表结构MyISAM(MySQL5.5.8之前默认)：将表存储在MYD(数据文件)和MYI(索引文件)

不支持事务，表级锁(读取写入互斥)

可以将表损坏修复(不能修复事务，因为它本身就不支持事务)： check table tableName 检查

repair table tableName 修复

支持全文索引，支持压缩操作(存储后不再修改的表，读取单行数据时，无需解压整个表):myisampack命令

适用场景：非事务型应用(一些非财务的数据统计可以不使用事务)

只读类应用

空间类应用：使用空间函数

InnoDB(MySQL5.5.8之后默认):支持事务，适合大量小事务(提交多，回滚少)：Redo Log:存储已提交事务日志，实现事务的持久性；由系统中的缓冲区和日志文件组成；顺序写入，无需读取操作

Undo Log:存储未提交事务日志，来让事务回滚；需要随机读写;最好将该日志存储到好的IO设备上

支持行级锁：可以最大程度支持并发；该锁是在存储引擎层实现的

状态检查: show engine innodb status

使用表空间存储，具体哪个表空间，根据如下参数决定:innodb_file_per_tableON:独立表空间：tablename.ibd 推荐可以通过optimize table命令收缩系统文件

可以同时向多个文件刷新数据

OFF:系统共享表空间：ibdataX(X是一个数字) 可以将系统表空间的表转移到独立表空间无法简单的收缩文件大小

同时向多个文件刷新数据，可能会产生IO瓶颈

5.7之后，InnoDB也支持全文索引和空间函数

一些热点数据，也会被缓存到内存中

CSV：将普通的csv文件作为表来处理数据以文本方式存储在文件中，可以直接查看、编辑 (CSV格式，每一列用 ","分割，并用双引号表示字符串，所有列不为null)

.CSV存储表内容;.CSM文件存储表的元数据，如表状态和数据量；.frm存储表结构

不支持索引，所以不适合大表，不适合在线处理(WEB应用)

可以作为数据交换的中间表，例如将数据(表格)导出为CSV文件，然后放入Mysql目录，即可从MySQL中读取该数据

Archive:用zlib对表数据要锁，磁盘IO少

数据存储在.ARZ文件中

只支持insert和select操作

支持行级锁，可以高并发插入

只它允许在自增ID列上加索引

适合日志和数据采集类应用

Memory:也称为HEAP存储引擎，所有数据保存在内存中支持HASH索引(默认)和BTree索引，HASH等值查询很快，但范围查询不行

所有字段长度固定，也就是就是varchar(10)等价char(10)

不支持BLOG和TEXT等大字段

使用表级锁，所以性能也不高

表的最大大小由参数 max_heap_table_size参数决定

可以等值查找或者映射表，例如邮编和地区的对应表

或保存数据分析的中间结果表

Federated：默认禁止，没软用提供了访问远程MySQL服务器上表的功能

本地不存储数据，数据全部在远程服务器上

本地需要保存表结构和远程服务器的连接信息

服务器参数MySQL获取配置信息路径命令行参数

配置文件：不同系统可能不同查询配置文件路径及优先级: mysqld --help --verbose | grep -A 1 'Default options'

配置参数的作用域全局参数： set global 参数名=参数值

会话参数： set [session] 参数名=参数值

内存配置相关参数最大内存

每个连接(线程)使用的最大内存(例如100M，那么10个连接就是1G)sort_buffer_size 排序时的缓存大小

join_buffer_size 每个线程连接缓冲区的大小(例如1M，那么查询中关联2张表就用2M)

read_buffer_size 查询缓冲区大小

read_rnd_buffer_size 索引缓冲区大小

引擎Innodb_buffer_pool_size InnoDB引擎使用的缓冲区大小(很重要) 计算方式：总内存-(每个线程所需的内存*连接数)-系统保留内存

key_buffer_size MyISAM引擎的索引缓冲区大小 (就算所有表都使用InnoDB,也要分配，因为MySQL的系统表使用到了该引擎)

IO相关配置参数InnoDB引擎相关Innodb_log_file_size:事务日志大小

Innodb_log_files_in_group:事务文件个数(可以不用管该参数)

Innodb_log_buffer_size:日志缓冲区大小(1s内的事务就够了,128M)

Innodb_flush_log_at_trx_commit:0:每秒一次将日志写入缓冲区，并flush log到磁盘，MySQL崩溃可能会丢失1s的事务数据;

1:(默认)每次事务提交都将日志写入缓冲区，并flush log到磁盘;安全性最高，性能最差;

2:(推荐)每次事务提交都将日志写入缓冲区，每秒执行一次flush log到磁盘；性能好，数据库崩溃也不会丢失事务；除非服务器宕机

Innodb_flush_method:将缓冲区的数据写入操作系统的方式O_DIRECT:推荐，会通知操作系统不缓存数据，也不预读，避免了InnoDB和操作系统对数据的双重缓存

Innodb_file_per_table:如何使用表空间：独立表空间或系统表空间

Innodb_doublewrite:是否使用双写缓存(防止数据写入时损坏)，建议启用

MyISAM引擎相关delay_key_write:OFF:每次写操作后刷新 键缓冲 中的脏块到磁盘，最安全

ON：只对在建表时指定了delay_key_write选项的表使用延迟刷新

ALL:对所有MyISAM表都是用延迟键写入，崩溃时可能导致表损坏，但可以修复

安全相关配置expire_logs_days:自动清理binlog(二进制日志)的天数；

max_allowed_packet:MySQL可以接收的包的大小;(32M左右,主从服务器最好保持一致)

skip_name_resolve:(推荐启用)禁用DNS查找;

sysdate_is_now:(推荐启用)确保sysdate()返回确定性日期(默认情况下多次调用该函数可能返回不同结果,可能导致主从复制数据不一致)

read_only:禁止非super权限用户的写权限(从库中启用)

skip_slave_start:禁用Slave(从库)自动恢复

sql_mode:设置MySQL使用的SQL模式：strict_trans_tables:如果事务中数据无法插入事务存储引擎中，则中断当前操作；对非事务无影响

no_engine_subtitution:建表时如果指定的存储引擎如果不可用，会建表失败，而不是使用默认存储引擎

no_zero_date:不能写入0年0月0日这样的日期

no_zero_in_date:也不能接收部分日期为0的日期

only_full_group_by:(5.7默认启用)拒绝语义不明确的列

其他常用配置参数sync_binlog:控制MySQL如何向磁盘刷新binlog默认为0，不主动刷新，由操作系统控制

如果大于0，则表示多少次事务刷新一次(建议为1)

tmp_table_size和max_heap_table_size:内存临时表的大小

max_connections:最大连接数(默认100)，通常为2000或更大

基准测试：针对系统设置的一种压力测试，不关心业务逻辑对整个系统进行基准测试，包括Web服务器缓存、数据库等

单独对MySQL进行基准测试

常见指标：单位时间处理的事务数(TPS)

单位时间处理的查询数(QPS)

响应时间

并发量：同时处理的查询请求的数量

mysqlslap:MySQL服务器自带的基准测试工具，随MySQL一起安装可以模拟服务器负载，并输出相关统计信息

可以指定自动生成查询语句

sysbench测试工具；比上面的好，具体看如下链接