如何构建高性能MySQL?

2020-07-10 777 0 编辑:深圳网站建设 来源:互联网

mysql事务ACID:

1、原子性:automicity 要么全部执行成功,要么全部执行失败,这就是事务的原子性

2、一致性:consistency 从一个一致性的状态转换到另外一个一致性的状态

3、隔离性:isolation 事务在提交之前,对其他事务是不可见的

4、持久性:durability 一旦提交,所做的数据修改就会永远保存在数据库中


隔离级别: set session transaction isolation level read COMMITTED;

1、READ UNCOMMITTED 未提交读

在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的,事务可以读取未提交的数据,这也被称为"脏读"(dirty read)

2、READ COMMITTED 提交读

大多数的数据库系统的默认隔离级别都是READ COMMITTED。一个事务从开始直到提交之前,所做的任何操作修改对其他事务是不可见的。也叫做不可重复读

3、REPEATABLE READ 可重复读

可重复读解决了脏读,但是会存在幻读的现象。当某个事务在读取范围内的记录时,另外一个事务在该范围内插入新的数据。

4、SERIALIZABLE 可串行化

通过强制事务串行执行,是最高的隔离级别


死锁:

死锁是指两个或两个以上的事务在同一个资源上相互占用,并请求锁定对方占用的资源,从而产生恶性循环的现象。

INNODB目前将持有最少行级排它锁的事务进行回滚


事务日志:

事务日志可以提高事务的效率。使用事务日志,存储引擎在修改表数据时,只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中。而不是每次修改数据本身持久到硬盘上。

事务日志采用的是追加的方式,因此写日志的操作是硬盘上一小块区域内的顺序I/O,而不是随机I/O。事务日志持久以后,内存被修改的数据在后台慢慢刷回到磁盘。修改数据需要写两次磁盘。

如果在事务日志中持久化,没有落盘,系统崩溃,数据库会自动恢复。


INNODB存储引擎:

innodb表是基于聚簇索引建立的,聚簇索引对主键查询有很高的性能,不过它的二级索引里必须包含主键列,所以主键列很大的话,其他索引都会很大。


表修改存储引擎:

alter table table_name engine=innodb; 按行将数据从原表复制到新表,因此执行时间会很长

性能测试前900s预热,避免预热时的IO影响测试结果 


===MySQL基本测试===

sysbench:

1、CPU

2、IO

3、内存

4、线程

5、OLTP


绘图工具:gnuplot 或者 R

===服务器性能剖析===

日志轮转工具:log rotation

mysqlslowlog tmpdump pt-query-digest mysql-proxy

pt-query-digest --explian 和 V/M 值 更容易识别出性能低下的查询

官方mysql和percona server对比慢查询日志缺少了很多附加信息

show profile

使用 SHOW GLOBAL STATUS 捕获数据

mysqladmin ext -i1 | awk '

/Queries/{q=$4-qp;qp=$4/}

/Threads_connected/{tc=$4}

/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'


使用 SHOW PROCESSLIST

innotop工具

每个时间段吞吐量

awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' mysql-slowlog.log

pt-stalt pt-pmp pt-collect工具

gdb 工具对mysql的分析

iostat vmstat new relic工具

===Schema和数据类型优化===

1、选择优化的数据类型

更小的数据类型通常更快,因为它们占用更少的磁盘,内存,CPU缓存,并且处理时需要的CPU周期也更少

2、简单就好

3、尽量避免NULL

因为可为NULL的列使得索引,索引统计和值比较更复杂化,可为NULL的列占用更多的存储空间。可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至可能导致固定大小的索引变为可变大小的索引

1、时间类型:

DATETIME 和 TIMESAMP 列都可以存储相同类型的数据,时间和日期,精确到表。但是 TIMESAMP只使用 DATEIME 一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。但是,TIMESAMP允许的时间范围要小的很多

2、整数类型: 整数 和 实数

整数:tinyint 8

samllint 16

mediumint 24

int 32

bigint 64

整数类型有可选的 unsigned ,表示不允许为负值

例如:tinyint unsigned 0 ~ 255

tinyint 128 ~ 127

MySQL可以为整数类型指定宽度,例如int(11),对大多数应用是没有意义的。它不会限制值的合法范围,只是规定了mysql交互工具用来显示的字符的个数。对于存储和计算来说,int(1)和int(20)是相同的。

3、实数类型: 实数是带有小数部分的数字。

尽量在对小数进行精确计算的时候使用DECIMAL-例如存储财务数据。 或者可以使用BIGINT,根据小数的位数乘以相应的倍数后存储在BIGINT里,避免DECIMAL精确计算代价高的问题。

4、字符串类型:

VARCHAR:

a.VARCHAR类型用于存储可变长字符串,它比定长类型更节省空间,因为它仅使用必要的空间。 如果ROW_FORMAT=FIXED创建的话,每一行都是定长存储,很浪费空间。

b.VARCHAR需要使用1或者2个字节存储字符串的长度,如果列的最大长度<=255,则使用1个字节,否则使用2字节。 VARCHAR(10) 需要11个字节,VARCHAR(1000) 需要1002个字节。

VARCHAR节省了存储空间。但是由于行是变长的,在UPDATE时可能使行变得更长,导致额外的工作。MyISAM 会将行拆分成不同的片段存储。 INNODB则需要分裂页来使行放进页内。

c.慷慨不是明智的:

VARCHAR(5) 和 VARCAHR(200) 存储'yoon'的空间开销是一样的。因此短的有什么优势? 更长的列会消耗更多的内存,因为mysql通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表排序或者操作时会特别

糟糕。在利用磁盘临时表排序时也同样糟糕。 因此分配真正需要的空间。


CHAR:

CHAR的类型是定长的。

BLOB 和 TEXT:

a.BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。 BLOB类型存储的是二进制,没有排序规则或字符串, 而TEXT类型有字符串和排序规则。

b.如果非要使用BLOB和TEXT,可以将BLOB字段的地方使用SUBSTRING(column,length)将列值转换为字符串,但是要确保截取的够短,否则临时表的大小会超过max_heap_table_size tmp_table_size

,超过以后mysql会将内存临时表转换为磁盘临时表。

日期和时间类型:

DATETIME: 从1001 ~ 9999,精度为秒。它把日期和时间封装为 YYYYMMDDHHMMSS 整数中,与时区无关。使用8个字节的存储空间。 默认情况下,mysql以一种可排序的,无歧视的格式显示DATETIME值。

TIMESAMP:从1970 ~ 2038,占用4个字节的存储空间。 

a.TIMESAMP 显示的值也依赖时区。 mysql服务器,操作系统,以及客户端都有时区设置。

b.如果在多个时区存储或者访问数据,TIMESAMP和DATETIME的行为很不一样,TIMESAMP和时区有关,DATETIME则保留文本表示的日期和时间。

c.存储比秒粒度更小的日期和时间,用mariadb替换mysql

范式的有点和缺点:

1、范式化的更新操作要比反范式化快

2、当数据较好的范式化时,就有较少的或者没有重复的数据,所以只需要修改更少的行

3、范式化的表通常都很小,可以更好的在内存里执行

4、很少有多余的数据意味着要检索列表数据时更少需要DISTINCT 或者 GOURP BY语句。 在非范式化的结构中要 DISTINCT 和 GROUP BY 才能获得唯一部门的数据

5、范式化设计的缺点通常需要关联。

反范式的优点和缺点:

1、数据都在一个表中,因此可以避免关联

2、如果不需要关联表,对大部分查询最差的情况---即使没有使用索引--是全表扫描。当数据比内存大时,这可能比关联要快的多,避免了随机IO 。(全表扫描基本上是顺序IO)

范式:俗称就是将数据拆分细化,查询时需要关联多张表进行查询想要的数据

反范式:俗称就是将数据混合存放在一起,查询时只需要查询一张表即可,不需要关联

3、混用范式和反范式化

总结:

1、尽量避免过度设计表

2、使用小而简单的数据类型,避免使用NULL值

3、尽量使用相同的或相似的数据类型存储相关的值,尤其要在关联的表中使用的列

4、尽量使用整型定义标识列

===创建高性能的索引===

在mysql中,索引是在存储引擎层而不是服务器层实现,索引没有统一的索引标准。不同存储引擎的索引的工作方式不同,也不是所有的存储引擎都支持相同类型的索引。即使存储引擎支持相同类型的索引,底层实现的也可能不同。

B+Tree索引:

没有特别指明,多半说的都是B-Tree索引,使用B-Tree数据结构来存储数据,即每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的遍历搜索。

存储引擎以不同的方式使用B-Tree索引,性能也不相同,各有优劣。 myisam使用前缀压缩技术使得索引更小,innodb则按照原数据格式进行存储。 myisam索引通过数据的物理位置引用被索引的行,innodb则通过主键引用被索引的行。

B-Tree通常意味着值都是按顺序存储的,每一个叶子页到根的距离相同。 根节点存放了指向"叶子节点的指针",叶子节点"指针指向的是被索引的数据"。

索引对多个值进行排序,是根据 CREATE TABLE 语句定义的索引列的顺序,例如: IDX_INDEX(A,B,C) 如果A,B值都一样,则根据C排序。

如果查询中有某个列的范围查询,则其右边的列无法使用索引优化查找,例如:IDX_INDEX(A,B,C)

WHERE A = 'YOON' AND B LIKE 'K%' AND C = '1987-7-7'; 这个查询只能使用所用的前两个列,因为这里的 LIKE 是一个范围条件。

网站设计专家.jpg

本站文章均为深正网站建设摘自权威资料,书籍,或网络原创文章,如有版权纠纷或者违规问题,请即刻联系我们删除,我们欢迎您分享,引用和转载,但谢绝直接搬砖和抄袭!感谢...
关注深正互联
多一份免费策划方案,总有益处。

请直接添加技术总监微信联系咨询

2
5