<<高性能mysql>>笔记1

转载请注明： TheViper http://www.cnblogs.com/TheViper  

 <<高性能mysql>>这本书写的真的很好，只可惜本屌不才，大部分都看不懂，暂且记下与mysql优化有关，对自己有用的东西。

测试指标

• 吞吐量

吞吐量指的是单位时间内的事务处理数，单位tps(transaction per second).这一直是经典的数据库应用测试的指标。

• 响应时间或延迟

这个指标用于测试任务所需的整体时间

• 并发性

注意，web服务器并发性不等同于数据库的并发性。服务器的高并发一般也会导致数据库的高并发，但服务器所用的语言，框架，工具集对此都会有影响。一个设计良好的应用，同时可以打开成千上百个数据库服务器连接，但可能同时只有少数连接在执行查询。因此，所需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或连接数。

• 可扩展性

可扩展性指的是，给系统增加一倍的工作，理想情况下，会获得两倍的吞吐量，这时，看实际增加的吞吐量是多少。

 性能优化的目标--响应时间

很多人认为性能优化就是降低cpu利用率。但这是个陷阱，资源就是用来消耗并用来工作的。所以，有时候消耗更多的资源能够加快查询速度。很多时候，升级到mysql新版本后，cpu利用率会上升的很厉害。这不代表性能出了问题。相反，说明新版本对资源的利用率上升了。

另外，如果把性能优化仅仅看出是提升每秒查询量，这其实只是吞吐量优化。吞吐量的提升可以减少响应时间的副产品（倒数关系）。

 优化数据类型

• 更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。但是确保没有低估需要存储的值得范围。

• 简单就好

简单数据类型的操作通常需要更少的cpu周期。例如，整型比字符操作代价更低，具体的。

1.应该使用mysql内建类型而不是字符串存储日期和时间

2.应该用整型存储ip地址。

• 尽量避免null

通常情况下，最好指定列为not null,除非真的需要存储null值。如果查询中包含可为null的列，对mysql来说会更难优化，因为可为null的列使得索引，索引计算和值比较更复杂。另外，可为null的列会使用更多的存储空间。

下面具体说下数据类型

1. 整数类型

有两种类型的数字，整数和实数。如果存储整数，可以使用这几种整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT，对应8,16,24,32,64位存储空间。

 整数类型有UNSINGED属性，表示不允许负数，这大致可以是整数的最大值上限提高一倍。

 mysql可以为整数类型指定宽度，如INT(7),但对大多数应用这时没意义的。它不会限制值的范围。对于存储来说，INT(1)和INT(10)是一样的。

　　2.实数类型

实数是带有小数部分的数字。但是，它们不仅可以用来储存小数部分，还可以使用DECIMAL储存比BIGINT还大的整数。

浮点类型在储存同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节储存，DOUBLE使用8个字节储存。mysql使用DOUBLE作为内部浮点计算的类型。

注意，在数据量比较大时，例如储存财务数据，可以考虑使用BIGINT替代DECIMAL，将需要存储的货币单位，根据小数的位数乘以相应的倍数即可。这样可以同时避免浮点存储不精确和DECIMAL精确计算代价高的问题。

 　　3.字符串类型

VARCHAR：用于存储可变长字符串。它比定长类型更节省空间，因为它仅使用必要的空间。

VARCHAR需要使用1或2个额外字节记录字符串的长度。如果列的最大长度<=255字节，则使用1个字节表示，否则使用2个字节。

由于行是变长的，在update时可能会使行变得比原来长。下面情况下使用VARCHAR比较合适。

1.字符串列的最大长度比平均长度大很多

2.列的更新很少，不会出现碎片问题。

3.使用了想utf-8这样复杂的字符集。

4.每个字符都使用不同的字节数进行存储。

 CHAR：定长，mysql总是根据定义的字符串长度分配足够的空间。

在存储时，mysql会删除所有的末尾空格。CHAR适合存储很短的字符串，或所有值都接近同一长度。

例如，CHAR适合存储密码的md5值，因为它是定长的。对于经常变更的数据，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

 　　4.blob和text类型

两者都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

 　　5.日期，时间类型

 mysql能存储的最小时间粒度为秒。

DATETIME:这个类型能保存大范围的值，从1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。

TIMESTAMP:这个类型保存了从1970年1月1日0时以来的秒数。它和unix时间戳相同。它使用4个字节储存，因此范围比DATETIME小很多，只能表示1970年到2038年。TIMESTAMP显示的值与时区有关。

 创建高性能的索引

 索引可以包含一个或多个列的值。如果索引包含多个列，那列的顺序十分重要，因为mysql只能高效的使用索引的最左前缀列。

 最常见的B-Tree索引，按照顺序存储数据，所以mysql可以做order by和group by操作。因为数据是有序的，所以B-Tree也会将相关的列值都存储在一起。最后，因为索引中存储了实际的值，所以某些查询只使用索引，就能够完成全部查询。

 策略：

1.独立的列：索引列不能是表达式的一部分，也不能是函数的参数。比如，

select id from a where id+1=5

select .... where TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10

 应该养成简化where条件的习惯，始终将索引列单独放在比较符号的一侧。

 2.前缀索引：索引开始的部分字符。

对于像BLOB,TEXT这种很长的列，必须使用前缀索引。因为mysql不允许索引这些列的完整长度。

因此建立前缀索引的关键是，选择足够长的前缀，以保证较高的选择性，同时又不能太长。

前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。为了找出这个足够长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。例如

可以看到每个值都在45到65之间，区分度不好。下面取3个前缀字符

这次区分度就要好点了，下面继续增加前缀长度，最后发现前缀长度为7时比较合适

计算合适前缀长度的另一个方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性，具体的

 3.多列索引

一个常见的错误是，为每个列创建一个单独的索引，或按照错误的顺序创建多列索引。

 关于索引列的顺序，正确的顺序依赖于使用该索引的查询，同时还要考虑是否满足排序和分组的需要。

一个经验：将选择性最高的列放在索引的最前列。这个经验在不需要考虑排序和分组的时候效果很好。这时候索引的作用只是在优化where条件的查找。

事实上，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值分布有关，这和前面说的选择最佳前缀长度需要考虑的地方一样。换句话说，可能需要根据那些运行频率最高的查询来调整索引列的顺序。

使用索引扫描排序

mysql有两种方式生成有序结果，通过排序操作或按索引顺序扫描。如果explain出来的type列的值为index,说明使用了索引扫描做排序。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就回表查询一次对应的行。这基本上都是随机的io.因此按索引顺序读取数据的速度通常比顺序的全表扫描慢，尤其是在io密集型的工作负载中。

mysql可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能的满足这两种任务最好。

 只有当索引的顺序和order by子句的顺序完全一致，并且所有列的排列方向都一样时，mysql才能使用索引对结果排序。

如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。

order by子句和查找性查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行排序操作。

 有一种情况可以不满足索引的最左前缀要求，依然可以使用索引排序。那就是当前导量为常数时。例如

在一个表上，建立索引(a,b,c)。

select ... where a=“2014-12-21” order by b,c.

这时索引的第一列被指定为常数，可以使用索引。下面的也可以使用索引

select ... where a>"2014-12-21" order by b

select ... where a>"2014-12-21" order by a,b

这两个刚好用了索引的前缀，所以也可以。

 下面是一些不能使用索引进行排序的查询

select ... where a="2014-12-21" order by b DESC,c ESC

查询使用了两种不同的排序方向，但是索引列都是正序排序的。

select ... where a="2014-12-21" order by b，d

引用了一个不在索引中的列

select ... where a="2014-12-21" order by c

where和order by中的列无法组成索引的最左前缀，因为跳过了b这个列

select ... where a>"2014-12-21" order by b，c

第一列是范围查询

select ... where a>"2014-12-21" and b in(1,2) order by c

b列上有多个等于条件