MySQL性能优化(一) 顶

• 大表
• 记录行数巨大，单表超过千万行 。但如果是记录日志的表，只有insert和select操作，没有update的，一般超过千万行，对业务也没有影响。
• 表数据文件巨大，表数据文件超过10G

大表对查询的影响

慢查询:很难在一定的时间内过滤出所需要的数据。

如果大表原本跟业务无关，此时没有太多的关系，但如果一旦大表加入了业务，就会对业务产生严重的性能影响。

比方说我们要在订单中显示日志表中订单来源的字段(是来源于自己的web系统还是来源于京东，阿里等等)，而日志表中的数据已经上亿了，如果直接关联日志表查询就会产生慢查询，甚至崩溃。

大表对DDL操作的影响

建立索引需要很长的时间。

风险：

1. MySQL版本<5.5 建立索引会锁表
2. MySQL版本>5.5 虽然不会锁表但会引起主从延迟。(搭建主从库可以参考Docker安装mysql8主从结构 )

修改表结构需要长时间锁表，数据库连接数被占满，导致500错误

风险：

1. 会造成长时间的主从延迟。
2. 影响正常的数据操作。

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

难点：

1. 分表主键的选择
2. 分表后跨分区数据的查询和统计

大表历史数据归档 减少对前后端业务的影响

难点：

1. 归档时间点的选择 业务数据可以选择前年的，而对于全日志数据，前一周的就可以了
2. 如何进行归档操作 我们要对已归档的数据在现有的表中进行删除。对大表的增删改查都要小心细致，不然容易造成阻塞，严重影响业务。
3. 事务
4. 事务是数据库系统区别于其他一切文件系统的重要特性之一。
5. 事务是一组具有原子性的SQL语句，或是一个独立的工作单元。

事务的四大特性 可以参考Spring事务说明与自实现

1. 原子性
2. 一致性
3. 隔离性
4. 持久性
5. READ-UNCOMMITTED 允许存在脏读（事务A读取了事务B未提交的数据，并在这个基础上又做了其他操作）
6. READ-COMMITTED 不允许脏读，允许不可重复读（事务A读取了事务B已提交的更改数据）
7. REPEATABLE-READ 不允许脏读，不可重复读，允许幻读（事务A读取了事务B已提交的新增数据）
8. SERIALIZABLE 全部不允许，做到完全隔离

我们来看一下READ_COMMITTED，REPEATABLE_READ，SERIALIZABLE在实际操作中是什么样子的。(第一种，我们肯定是不考虑的)

首先我们查看一下当前数据库的隔离级别

show variables like '%iso%'

'tx_isolation', 'REPEATABLE-READ'

所以我们知道数据库默认的隔离级别为REPEATABLE-READ

假设我们有一个表t，里面有一个字段id,有以下这些数字

id

1

3

5

7

9

10

我们依次执行以下命令

begin;

select * from t where id < 7;

id

1

3

5

此时我们再起一个连接，依次执行以下命令

begin;

insert into t values(2);

commit;

我们再回到第一个连接，再次执行

select * from t where id < 7;

id

1

3

5

此时我们可以看到，数据并没有发生变化，可见在REPEATABLE-READ的事务隔离级别下，虽然其他事务提交了插入2，但并没有影响第一个事务的查询。

此时我们将第一个事务提交

commit;

再来查询

select * from t where id < 7;

id

1

2

3

5

这个时候我们才能看到2这个数据。

现在我们来修改数据库的隔离级别为READ-COMMITTED

set session tx_isolation='read-committed';

我们再依次执行

begin;

select * from t where id < 7;

id

1

2

3

5

然后在另外一个连接中依次执行

begin;

insert into t values(4);

commit;

此时我们返回第一个连接，执行

select * from t where id < 7;

id

1

2

3

4

5

此时我们可以看到，虽然第一个连接的事务并没有提交，但它依然可以查询到第二个连接插入的4.

此时我们可以知道READ-COMMITTED是可以读取其他事物提交的数据的。

提交后，我们可以把事务隔离级别改回REPEATABLE-READ

commit;

set session tx_isolation='repeatable-read';

最后一个隔离级别又叫可串行化，会在读取的每一行数上都加锁，所以可能会导致大量的锁超时和锁征用的问题。

• 大事务

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

风险：

锁定太多的数据，造成大量的阻塞和锁超时。

回滚时所需要的时间比较长。

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

如何处理大事务

1. 避免一次处理太多的数据
2. 移除不必要在事务中的select操作。
3. 影响性能的几个方面
4. 服务器硬件
5. 服务器系统
6. 数据库存储引擎的选择
7. 数据库参数配置
8. 数据库结构设计和SQL语句

服务器硬件当然是CPU核数越多，频率越快越好，服务器内存当然是越大越好。

服务器系统来说，一般使用的是CentOS

CentOS系统参数优化

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

net.core.somaxconn=65535 决定了每个端口最大的监听队列的长度，一般默认较小，对于负载比较大的系统肯定是不够的，一般修改成2048或者更大的值。

net.core.netdev_max_backlog=65535 决定了在每个网络接口接收数据包的速率比内核处理机接收的速率快的时候，允许被发送到队列中的数据包最大的数目

net.ipv4.tcp_max_syn_backlog=65535 决定了还未获得连接的请求，保存在队列中的最大数目，对于超过这个大小的连接会被抛弃

net.ipv4.tcp_fin_timeout=10 用于控制TCP连接用于处理等待状态的时间，对于连接比较频繁的系统，通常会有大量的连接会处于等待状态的，该参数用于减少timeout(超时)的时间，加快TCP连接的回收速度

net.ipv4.tcp_tw_reuse=1

net.ipv4.tcp_tw_recycle=1

以上三个参数用于加快TCP连接的回收，如果有大量的数据库连接请求，而TCP的连接又被占满的情况下，就会出现TCP无法连接的错误。

net.core.wmem_default=87380

net.core.wmem_max=16777216

net.core.rmem_default=87380

net.core.rmem_max=16777216

以上四个参数决定了TCP连接接收和发送缓冲区大小的默认值和最大值

net.ipv4.tcp_keepalive_time=120 用于探测时间的间隔，单位为秒，用于确认TCP连接是否有效

net.ipv4.tcp_keepalive_intvl=30 用于当探测的消息未获得响应时，重发该消息的时间间隔，单位为秒

net.ipv4.tcp_keepalive_probes=3 用于当TCP连接确认失效前发送多少个探测消息

以上三个参数用于减少失效连接所占用的TCP资源的数量，加快资源回收的效率。

kernel.shmmax=4294967295 Linux内核参数中最重要的参数之一，用于定义单个共享内存段的最大值。

注意：

1. 这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池的大小。
2. 这个值的大小对于64位linux系统，可取的最大值内存值为物理内存值-1byte,建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可，可以取物理内存-1byte.

vm.swappiness=0 当内存不足时会对性能产生比较明显的影响，告诉Linux内核除非虚拟内存完全满了，否则不要使用交换区。

增加资源限制(/etc/security/limit.conf)

这个文件实际上是Linux PAM，也就是插入式认证模块的配置文件。打开文件数的限制。

* soft nofile 65535

* hard nofile 65535

* 表示对所有用户有效

soft 指的是当前系统生效的设置

hard 表明系统中所能设定的最大值 soft不能超过hard

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

65535 就是限制的数量

结论：

把可打开的文件数量增加到了65535个以保证可以打开足够多的文件句柄。

注意：

这个文件的修改需要重启操作系统才可以生效。

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

cat /sys/block/sda/queue/scheduler sda可能是vda

noop anticipatory deadline cfq

noop(电梯式调度策略) NOOP实现了一个FIFO队列，它像电梯的工作方法一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。NOOP倾向饿死读而利于写，因此NOOP对于闪存设备，RAM及嵌入式系统是最好的选择。

deadline(截止时间调度策略) Deadline确保了在一个截止时间内服务请求，这个截止时间是可调整的，而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象，Deadline对数据库类应用是最好的选择。

anticipatory(预料I/O调度策略) 本质上与Deadline一样，但在最后一次读操作后，要等待6ms，才能继续进行对其他I/O请求进行调度。它会在每个6ms中插入新的I/O操作，而会将一些小写入流合并成一个大写入流，用写入延时换取最大的写入吞吐量。AS适合于写入较多的环境，比如文件服务器，AS对数据库环境表现很差。

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

文件系统对性能的影响

Windows FAT NTFS

Linux EXT3 EXT4 XFS

EXT3/4系统的挂载参数(/etc/fstab)

data=writeback | ordered | journal

这三个值代表了不同的日志策略，writeback意味着只有元数据写入到日志，元数据写入和数据写入不是同步的，这是最快的一种配置，对Innodb是最好的选择。ordered只会记录元数据，但提供了一些一致性的保证，在写元数据之前会先写数据，使他们保持一致，比writeback稍微慢一些，但是如果出现崩溃则更加安全。journal选项提供了原子日志的一种行为，在数据写入到最终位置之前，将记录到日志中，该选项对Innodb不必要，也是最慢的一种。

noatime , nodiratime 禁止记录文件的访问时间和目录的时间，禁用了之后可以减少一些写的操作，系统在读取文件和目录不必记录写操作来记录以上两个时间。

完整配置：/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

• MySQL体系结构

select语句：如何从文件中获得我们所要查询的数据，这个具体的实现的方式则是由下一层存储引擎层来实现。存储引擎有很多种

1. innodb
2. myisam
3. XtraDB
4. CSV
5. Memory
6. MRG_MYISAM
7. archive
8. federated
9. tokudb等

注意：

存储引擎是针对于表的而不是针对于库的(一个库中不同表可以使用不同的存储引擎)

常用存储引擎——MyISAM

mysql 5.5之前版本默认存储引擎

这里的临时表是指在排序、分组等操作中，当数量超过一定的大小之后，由查询优化器建立的临时表。

MyISAM存储引擎表由MYD和MYI组成

这里.frm是所有存储引擎记录数据库表结构的文件，.myd存储数据信息,.myi是索引信息。

特性

1、并发性与锁级别

MyISAM使用的是表级锁而不是行级锁，意味着对表进行修改时，需要对整个表进行加锁，而在对表中的数据进行读取时也需要对所有的表加共享锁。所以MyISAM的读取和写入这两种操作是互斥的。在一些情况下，当我们对表中的数据进行读取时，也可以在表的末尾插入数据。读写混合的并发性不是太好。如果是只读操作的话，并发性可以接受，因为共享锁并不会阻塞共享锁。

2、表损坏修复

对于任意意外关闭而损坏的MyISAM表，进行修复操作。但这里所说的修复并不是事务恢复，因为MyISAM并不是一种事务性的存储引擎，所以它也不可能有进行事务恢复所需要的相关日志。所以对MyISAM表进行修复，可能会造成数据的丢失。

check table tablename 检查表

repair table tablename 恢复表

3、MyISAM表支持的索引类型

原生支持全文索引的官方的存储引擎

4、MyISAM表支持数据压缩

对于一些OLAP是一个不错的选择

命令行：myisampack

myisampack -b -f myisam.MYI

注意：

被压缩的表不能进行写操作，只能进行读操作。

5、限制

版本 < MySQL5.0时默认表大小为4G

如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH

版本 > MySQL5.0时默认支持为256T

6、适用场景

非事务型应用，可以压缩，只读应用 比如对应一些OLAP,数据仓库，报表

空间类应用 如GPS数据

常用存储引擎——Innodb

MySQL5.5及以后版本默认存储引擎,是一种事务型的存储引擎。Innodb更适合处理大量的小事务。

Innodb适用表空间进行数据存储。

innodb_file_per_table

ON:独立表空间:tablename.ibd

OFF:系统表空间:ibdataX

show variables like 'innodb_file_per_table';

当我们建立一个innodb的表时，就可以看到

现在我们将innodb_file_per_table参数设为off.

set global innodb_file_per_table=off;

这里我们可以看到只有一个.frm的文件，也就是记录表格式的文件

系统表空间和独立表空间要如何选择

比较：

在MySQL 5.6之前的版本中，Innodb的innodb_file_per_table默认值参数是OFF,表的数据都是存储在系统表空间中。在一个繁忙的系统中，我们会发现系统表空间会不断的增长，一旦磁盘空间不足，我们不得不删除大量无效的文件。系统表空间无法简单的收缩文件大小。

独立表空间可以通过optimize table命令收缩系统文件。

系统表空间会产生IO瓶颈

独立表空间可以同时向多个文件刷新数据

建议：

对Innodb适用独立表空间，在MySQL 5.6之后，独立表空间已经成为了默认的配置。

表转移的步骤

把原来存在于系统表空间的中的表转移到独立表空间中的方法

步骤：

1. 使用mysqldump导出所有数据库表数据
2. 停止MySQL服务，修改innodb_file_per_table参数，并删除Innodb相关文件。
3. 重启MySQL服务，重建Innodb系统表空间
4. 重新导入数据

Innodb存储引擎的特性

1. Innodb是一种事务性存储引擎
2. 完全支持事务的ACID特性
3. Redo Log实现事务的持久性，存储的是已提交的事务，Undo Log实现未提交事务进行回滚。存储的是未提交的事务。

show variables like 'innodb_log_buffer_size'; 查看Redo Log缓冲区的大小。

show variables like 'innodb_log_files_in_group'; 查看log_file文件的数量

也就是这两个文件

1. Innodb支持行级锁
2. 行级锁可以最大程度的支持并发
3. 行级锁是由存储引擎层实现的。

什么是锁

1. 锁的主要作用是管理共享资源的并发访问
2. 锁用于实现事务的隔离性

锁的类型

1. 共享锁(也称读锁)
2. 独占锁(也称写锁)

首先我们在一个表中插入两行数据。

启动一个事务，并修改其中的一行，但并不提交。

我们进入另外一个连接，并查询id=2的数据。

此时我们可以看到我们是可以查出数据来的，为什么没有被独占锁所阻塞呢？

这是因为Innodb使用的Undo Log中的记录，所以这里查看的数据实际上是Undo Log中的版本。

锁的粒度

1. 表级锁

我们将连接1中的事务回滚rollback

lock table tablename write;

这样就在表中执行了一个表级独占锁，我们在另外一个连接中进行查询

这时，这个select操作是没有返回数据的。

直到我们在第一个连接中执行解锁操作

unlock tables;

此时第二个连接上的查询才会返回数据

1. 行级锁

可以最大程度支持并发处理

阻塞和死锁

1. 什么是阻塞

在有些时刻，一个事务中的锁需要等待另一个事务中的锁匙放它所占用的资源，这就形成了阻塞。阻塞是为了事务的并发可以正常运行，但是当一个系统中存在着大量的阻塞，往往就是系统中存在着问题，也许是在一个频繁更新的表中出现了慢查询，又或者alter table对一个表产生了排他锁。而过多的阻塞会使数据库连接产生大量的堆积，从而占用大量的系统资源，使得系统的性能整体下降。

1. 什么是死锁

死锁是指两个或两个以上的事务在执行的过程中相互占用对方等待的资源而产生一种异常。死锁对于数据库系统都是会自动发现，并且在多个死锁的事务中选择出一个资源占用最少的事务来进行回滚操作，这样就可以使其他事务正常运行了，所以死锁是可以由系统自动处理的。如果只是有少量的死锁并不会对系统有什么样的影响。如果一个系统频繁的出现大量的死锁，就是有问题的了。

Innodb状态检查

show engine innodb status

pager more 对输出的信息进行分页

红色部分表示显示的是最近33秒的信息

红色部分为事务信息，下面为文件IO的信息

适用场景

可以运用到大多数在线处理的场景，适用于大多数OLTP应用。如全文索引，空间函数。