大表对查询的影响
慢查询:很难在一定的时间内过滤出所需要的数据。
如果大表原本跟业务无关,此时没有太多的关系,但如果一旦大表加入了业务,就会对业务产生严重的性能影响。
比方说我们要在订单中显示日志表中订单来源的字段(是来源于自己的web系统还是来源于京东,阿里等等),而日志表中的数据已经上亿了,如果直接关联日志表查询就会产生慢查询,甚至崩溃。
大表对DDL操作的影响
建立索引需要很长的时间。
风险:
修改表结构需要长时间锁表,数据库连接数被占满,导致500错误
风险:
分库分表把一张大表分成多个小表
难点:
大表历史数据归档 减少对前后端业务的影响
难点:
事务的四大特性 可以参考Spring事务说明与自实现
我们来看一下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';
最后一个隔离级别又叫可串行化,会在读取的每一行数上都加锁,所以可能会导致大量的锁超时和锁征用的问题。
运行时间比较长,操作的数据比较多的事务。
风险:
锁定太多的数据,造成大量的阻塞和锁超时。
回滚时所需要的时间比较长。
执行时间长,容易造成主从延迟。
如何处理大事务
服务器硬件当然是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内核参数中最重要的参数之一,用于定义单个共享内存段的最大值。
注意:
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
select语句:如何从文件中获得我们所要查询的数据,这个具体的实现的方式则是由下一层存储引擎层来实现。存储引擎有很多种
注意:
存储引擎是针对于表的而不是针对于库的(一个库中不同表可以使用不同的存储引擎)
常用存储引擎——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之后,独立表空间已经成为了默认的配置。
表转移的步骤
把原来存在于系统表空间的中的表转移到独立表空间中的方法
步骤:
Innodb存储引擎的特性
show variables like 'innodb_log_buffer_size'; 查看Redo Log缓冲区的大小。
show variables like 'innodb_log_files_in_group'; 查看log_file文件的数量
也就是这两个文件
什么是锁
锁的类型
首先我们在一个表中插入两行数据。
启动一个事务,并修改其中的一行,但并不提交。
我们进入另外一个连接,并查询id=2的数据。
此时我们可以看到我们是可以查出数据来的,为什么没有被独占锁所阻塞呢?
这是因为Innodb使用的Undo Log中的记录,所以这里查看的数据实际上是Undo Log中的版本。
锁的粒度
我们将连接1中的事务回滚rollback
lock table tablename write;
这样就在表中执行了一个表级独占锁,我们在另外一个连接中进行查询
这时,这个select操作是没有返回数据的。
直到我们在第一个连接中执行解锁操作
unlock tables;
此时第二个连接上的查询才会返回数据
可以最大程度支持并发处理
阻塞和死锁
在有些时刻,一个事务中的锁需要等待另一个事务中的锁匙放它所占用的资源,这就形成了阻塞。阻塞是为了事务的并发可以正常运行,但是当一个系统中存在着大量的阻塞,往往就是系统中存在着问题,也许是在一个频繁更新的表中出现了慢查询,又或者alter table对一个表产生了排他锁。而过多的阻塞会使数据库连接产生大量的堆积,从而占用大量的系统资源,使得系统的性能整体下降。
死锁是指两个或两个以上的事务在执行的过程中相互占用对方等待的资源而产生一种异常。死锁对于数据库系统都是会自动发现,并且在多个死锁的事务中选择出一个资源占用最少的事务来进行回滚操作,这样就可以使其他事务正常运行了,所以死锁是可以由系统自动处理的。如果只是有少量的死锁并不会对系统有什么样的影响。如果一个系统频繁的出现大量的死锁,就是有问题的了。
Innodb状态检查
show engine innodb status
pager more 对输出的信息进行分页
红色部分表示显示的是最近33秒的信息
红色部分为事务信息,下面为文件IO的信息
适用场景
可以运用到大多数在线处理的场景,适用于大多数OLTP应用。如全文索引,空间函数。