mysql性能优化

2019-07-18 本文已影响0人 huxt

1.影响数据库的因素

1.）sq查询速度

2.）服务器硬件

3.）网卡流量

4.）磁盘IO

2.超高的QPS和TPs

QPS:每秒钟处理的査询量

3.还有什么会影响数据库性能

1.）大表给我们带来的问题

1.1）如何处理数据库中的大表

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

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

2.）大事务给我们带来的影响（https://www.cnblogs.com/AndyAo/p/8177872.html）

定义:运行时间比较长,操作的数据比较多的事务

风险:锁定太多的数据,造成大量的阻塞和锁超时回滚时所需时间比较长执行时间长,容易造成主从延迟

1.）事务的原子性( ATOMICITY）

定义:个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作

2.）事务的一致性( CONSISTENCY)

定义：致性是指事务将数据库从一种一致性状态转换到另外—种一致性状态,在事务开始之前和事务结束后数据库中数据的完整性没有被破坏

3.）事务的隔离性( ISOLATION)

定义：隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其它事务是不可见的

第1级别：Read Uncommitted(读取未提交内容)

(1)所有事务都可以看到其他未提交事务的执行结果

(2)本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少

(3)该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据

#首先，修改隔离级别

set tx_isolation='READ-UNCOMMITTED';

select @@tx_isolation;

第2级别：Read Committed(读取提交内容)

(1)这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）

(2)它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变

(3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

|——>导致这种情况的原因可能有：(1)有一个交叉的事务有新的commit，导致了数据的改变;(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit

#首先修改隔离级别

set tx_isolation='read-committed';

select @@tx_isolation;

第3级别：Repeatable Read(可重读)

(1)这是MySQL的默认事务隔离级别

(2)它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行

(3)此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行

(4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题

第4级别：Serializable(可串行化)

(1)这是最高的隔离级别

(2)它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。

(3)在这个级别，可能导致大量的超时现象和锁竞争

4.）事务的持久性( DURABILITY)

定义:一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,已经提交的修改数据也不会丢失

4.MySql存储引擎

1.）MyISAM

2.) Innodb

5.系统参数优化

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

net.core.somaxconn=65535

net.core.netdev_max_ backlog=65535

net.ipv4.tcp_maxsyn_backlog=65535

net.ipv4.tcp_fin_timeout 10

net.ipv4.tcp_tw_reuse =1

net.ipv4.tcp_tw_recycle =1

net.core.wmem max =16777216

net.core.rmem_default =87380

net.core.rmem_max =16777216

net.ipv4.tcp_keepalive_time= 120

net.ipv4.tcp_keepalive_intl 30

net.ipv4.tcp_keepalive_probes =3

kernel.shmmax= 4294967295

注意:1这个参数应该设置的足够大,以便能在一个共享内存段下容纳下整个的 Innodb缓冲池的大小。一般去大于Innodb内存即可

wappiness=0的时候表示最大限度使用物理内存，然后才是 swap空间，swappiness＝100的时候表示积极的使用swap分区，并且把内存上的数据及时的搬运到swap空间里面

注意：需要重启系统才能生效

6.磁盘调度策略优化

磁盘I/O，Linux提供了cfq, deadline和noop三种调度策略

MySQL数据库环境调整磁盘IO调度算法

最后期限算法(Deadline)除了维护了一个拥有合并和排序功能的请求队列外，额外维护了两个队列，分别是读请求队列和写请求队列，他们都是带有超时的请求队列，当新来一个IO请求时，会被同时插入普通队列和读写队列，然后I/O调度器正常处理普通队列中的请求。当调度器发现读写请求队列中的请求超时的时候，会优先处理这些请求，保证尽可能不产生饥饿请求。对于MYSQL来说，建议设置为Deadline，对MYSQL来说是很好的调度算法。

查看当前系统支持的磁盘IO调度算法

[root@localhost~]#dmesg | grep -i scheduler

io scheduler noop registered

io scheduler anticipatory registered

io scheduler deadline registered

io scheduler cfq registered (default)

default代表当前设备使用的缺省的IO调度算法

也可以用以下命令查看：

[root@localhost ~]# more/sys/block/sda/queue/scheduler

noop anticipatory deadline [cfq]

备注:括号里括起来的即为当前调度算法值

修改当前块设备使用的io调度算法为deadline:

[root@localhost ~]# echo"deadline" > /sys/block/sda/queue/scheduler

备注:修改立即生效

如果已经部署了MySQL数据库环境，需要重新启动MySQL。