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 是一个范围条件。