如何构建高性能MySQL？

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