MySQL性能优化.docx
《MySQL性能优化.docx》由会员分享，可在线阅读，更多相关《MySQL性能优化.docx（38页珍藏版）》请在冰豆网上搜索。
MySQL性能优化
Schema与数据类型优化
1.1选择优化的数据类型
●更小的通常更好：
小的数据类型占用更少的磁盘、内存和CPU缓存，需要的CPU时钟周期也少。
例如：
datetime和timestamp都可以存储时间数据并精确到秒，但timestamp只有datetime一半的存储空间，并且会更具时区变化。
不过timestamp允许的时间范围要小的多。
●简单就好：
简单的数据类型通常需要的CPU时钟周期就少。
例如：
字符串就比整形复杂；在使用日期时我们可以使用mysql的内建类型（date、time、datetime）而不要使用字符串存储日期。
在存储IP地址时使用整数存储而不是字符串存储。
●尽量避免NULL
因为可为NULL的列使的索引、索引统计、值比较都比较复杂，可为NULL的列也会使用更多的存储空间。
通常把可为NULL的列改为NOTNULL带来的性能提升较小。
但是如果计划在列上建立索引，就应该尽量避免设计成可为NULL的列。
1.1.1整数类型
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8、16、24、32、64位的存储空间，可以存储的值的范围为-2（N-1）到2（N-1）-1。
MySQL可以为整数类型指定宽度，例如INT（11），对于大多数应用来说这是没有意义的：
它不会限制值的合法范围，只是用来显示字符的个数，对于存储和计算来说INT
（1）和INT（20）是一样的。
1.1.2实数类型
实数是带有小数部分的数字，有DECIMAL、FLOAT和DOUBLE类型。
和整数类型一样，我建议只选择数据类型而不指定精度（整数是指定宽度），因为MySQL内部使用DOUBLE作为内部浮点计算的类型（其他类型会被转换为DOUBLE类型）。
FLOAT使用4个字节、DOUBLE使用8个字节，相比FLOAT有更高的精度和更大的范围，都比DECIMAL使用更少的空间。
只有在需要对小数进行精确计算时才使用DECIMAL—例如财务数据，但此时也可以用BIGINT代替DECIMAL类型。
1.1.3字符串类型
1.1.3.1VARCHAR和CHAR类型
下面这些情况使用VARCHAR是合适的：
1.字符串列的最大长度比平均长度大很多；
2.列的更新很少，所以碎片不是问题（由于行是变长的，在UPDATE时可能会使行变得比原来更长，这就会导致碎片产生）
3.使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR是定长的，它适合存储很短的字符集，或者所有值都接近同一个长度，例如MD5密码值。
定长的CHAR不容易产生碎片，这点要比VARCHAR强。
VARCHAR存储最少需要两个字节，因为有一个字节是记录长度的额外字节，而CHAR就不需要这个字节。
虽然VARCHAR（5）和VARCHAR（200）在存储“hello”时的空间开销是一样的，但是更长的列还是会消耗更多的内存，此外对排序也有影响，所以最好的策略是只分配真正需要的空间。
1.1.3.2BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。
MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。
对字符串多的列尽量少使用orderby
1.1.3.3使用枚举代替字符串类型
我们使用枚举列代替常用的字符串类型，枚举列会将每个字符串值保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
从下面的两个查询可以看出，第一个查询查询枚举列的内容，我们看到的是字符串：
但实际这三行数据存储为整数，而不是字符串，可以通过数字上下文环境检索看到这个双重属性：
另外一个注意的地方是枚举字段是按照内部存储的整数而不是字符串进行排序的。
所以在定义枚举列时我们应该按照字母的顺序来做。
当两个表关联查询时，把CHAR/VARCHAR列与枚举列进行关联查询可能会比直接关联CHAR/VARCHAR列更慢，但是两个枚举列进行关联查询肯定会比两个CHAR/VARCHAR列要快。
1.1.4日期和时间类型
DATETIME类型，这个类型能保存1001年到9999年，精度为秒，与时区无关，使用8个字节的存储空间。
TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数，它使用4个字节的存储空间，因此只能表示从1970年到2038年。
TIMESTAMP的显示值依赖时区，而DATETIME则保留文本表示的日期和时间。
1.1.5选择标识符
要确保在不同的关联表中使用相同的类型，类型之间需要精确匹配，混用不同的数据类型可能导致性能问题，也可能在比较操作时隐式类型转换也可能导致很难发现的错误。
尽量用整数类型作为标识符，因为他们很快并且可以使用AUTO_INCREMENT.
如果可能，尽量避免使用字符串类型作为标识符，因为他们很消耗空间，并且比数字类型要慢。
如果存储的是UUID值，则应该移除“-”符号；或者更好的做法是用UNHEX函数转换UUID值为16字节的数字，存储在一个BINARY（16）列中。
检索时可以通过HEX函数来格式化为16进制格式。
2创建高性能的索引
2.1索引基础
索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。
2.1.1索引的类型
2.1.1.1B-Tree索引
B-Tree结构（从技术上来说是B+Tree）索引
B-Tree索引是从索引的根节点（图中并未画出）开始进行搜索。
根节点中的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。
通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，最终存储引擎要么就能找到对应的值，要么该记录不存在。
上图仅画出了一个节点及其对应的叶子节点，叶子节点比较特别，他们的指针指向是被索引的数据。
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
假设有如下的数据表：
对于表中的数据，索引中包含了last_name,first_name和birthday列的值，下图显示了该索引如何组织数据的存储的。
B-Tree索引对如下类型的查询有效：
●全值匹配
全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为WangLong、出生于2010-10的人
●匹配最左前缀
前面提到的索引可用于查找所有姓为Wang的人，即只使用索引的第一列
●匹配列前缀
也可以只匹配某一列的值的开头部分。
例如前面提到的索引可用于查找所有以W开头的姓的人，这里也只使用了索引的第一列。
●匹配范围值
前面提到的索引可用于查找姓在Allen和Wang之间的人，这里也只使用了索引的第一列
●精确匹配某一列并范围匹配另外一列
前面提到的索引也可用于查找所有姓为Wang，并且名字是字母L开头（比如Long、Liu）的人，即第一列last_name全匹配，第二列first_name范围匹配
●只访问索引的查询
B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行，这就是所说的覆盖索引。
下面是一些关于B-Tree索引的限制：
●如果不是按照索引的最左列开始查找，则无法使用索引。
例如上面例子中的索引无法查找名字为Long的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。
类似地，也无法查找姓氏以某个字母结尾的人。
●不能跳过索引中的列，也就是说，前面的索引不能用于查找姓为Wang并且在某个特定日期出生的人，如果不指定名（first_name），则MySQL只能使用索引的第一列。
●如果查询中有某个列的范围查找，则其右边的所有列都无法使用索引优化查找。
例如：
这个查询就只能使用索引的前两列，因为like是一个范围条件。
优化的话如果范围包含的内容不多，可以考虑用等号代替like查询。
由此可见，索引的顺序是多么的重要，因此在优化性能的时候，就可能需要使用相同的列但顺序不同的索引来满足不同类型的查询要求。
2.1.1.2哈希索引
哈希索引是基于哈希表实现，只有精确匹配索引所有列的查询才有效。
对于每一行数据，存储引擎会对所有的索引列计算一个哈希值。
哈希索引将所有的哈希值存储在索引中，并同时在哈希表中保存指向每个数据行的指针。
在MySQL中只有Memory引擎支持哈希索引，但我们可以在InnoDB中创建自定义哈希索引。
此索引其实还是B-Tree索引，它需要新增一个被索引的列，然后用哈希值而不是键本身进行索引查找。
例如：
如果我们使用B-Tree索引来存储url列的索引值，那么存储空间就会比较大，而此时如果我们新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的查询方式：
这样的话，查询性能会非常的高。
因为有可能会出现哈希冲突，所以必须在where条件中带入哈希值和对应列值。
2.2高性能索引策略
2.2.1独立的列
“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
如：
我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧
下面这也是一个常见的错误：
2.2.2前缀索引和索引选择性
索引选择性是指：
不重复的索引值与技术总数之间的比值，范围从0到1之间，索引的选择性越高表示查询效率越高。
对于BLOB、TEXT或者很长的Varchar列则必须使用前缀索引。
要想获得合适的前缀索引的长度，有两种方法一种是count排序法，一种是count除数法。
count排序法：
可以看到最常见的城市出现的次数，下面我们就可以利用增加前缀长度的方式，找到最接近完整列时前缀的长度究竟时多少。
从图上可以看出，当前缀长度为7时是比较合适的。
count除数法：
从图中可以看出city列的选择性为0.0312，此时我们可以利用不同的前缀长度实验在前缀长度到达何值时最接近0.0312.
从图中可以看出，当前缀长度等于7时应该是最接近0.0312了。
所以此时前缀长度可以定义为7.
缺点：
无法使用前缀索引做ORDERBY或者GroupBY，也无法使用前缀索引做覆盖扫描。
2.2.3多列索引
“把Where条件里面的列都加上索引”这个建议其实是错误的，在多个列上建立独立的单列索引大部分并不能提高MySQL的查询性能。
MySQL5.0之后针对查询条件中存在的多列索引有一种策略叫“索引合并”。
例如我们在actor_id和film_id列上分别建立了索引，下面的查询就使用了两个索引扫描的联合。
当出现索引合并时意味着索引建立的不好。
●当出现对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的索引，而不是多个独立的单列索引。
●当出现对多个索引做联合操作时（通常有多个OR条件，上面的例子就是）会导致消耗大量的CPU与内存。
2.2.4选择合适的索引列顺序
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，对于如何选择索引的列顺序有一个经验法则：
就是将选择性最高的列放到索引最前列。
但根据所处的场景不同，这个法则也不是一个放之四海而皆准的法则，也要根据不同情况做不同判断。
不过当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。
我们可以根据运行频率最高的查询来调整索引列的顺序。
从这个sql语句中可以看出customer_id列的选择性较高，所以建立索引时应将customer_id作为索引列的第一列。
2.2.5聚簇索引
以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚簇索引。
如果您的表上定义有主键，该主键索引是聚簇索引（MySQL默认通过主键来聚集数据）。
如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOTNULL）作为主键，InnoDB使用它作为聚簇索引。
如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。
聚簇索引不是一个独立的索引类型，而是一种数据存储的方式。
InnoDB使用B-Tree来实现聚簇索引，并把索引和数据存放在同一结构中。
在有聚簇索引的表中，索引和行是存放在同一个叶子节点（B-Tree）中的。
"聚簇"表示索引值相邻的行的存储位置一般也是相邻的——一般来说是这样，但在某些情况下不是。
一张表只能有一个聚簇索引，因为同一份数据不可能出现两个地方。
图中展示了一个聚簇索引的示例，需要注意的是：
只有叶子节点才包括了数据行的值，非叶子节点只存了索引字段。
2.2.5.1聚簇索引优点
1.可以把相关数据保存在一起，在设计一个邮件系统时，按照（用户id，邮件id）来做聚簇，同一个用户的邮件会被存在相邻的磁盘区域，这样获取用户所有邮件的时候，磁盘IO开销就很小。
2.聚簇索引把索引和数据放在同一个B树中，在以聚簇索引获取整行数据会很快
3.只要是以聚簇索引为条件来查找数据，都能利用到”覆盖索引“这个特性，效率高。
2.2.5.2聚簇索引缺点
1.聚簇只是数据在磁盘上的组织形式，所以只能带来磁盘IO上的收益，如果表的数据能全部载入内存，那么聚簇索引也就失去了存在的意义。
2.大量数据按照聚簇索引书序插入会很快，如果乱序插入，就会比较慢，因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
3.更新或者插入主键（聚簇）的代价很高，因为会导致被更新的行移动，这将会导致页分裂。
因此对于InnoDB表，我们一般定义主键不可更新且自增。
4.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间
2.2.5.3聚簇和非聚簇对比
从上面的图中可以看出InnoDB的聚簇表中，行数据是和主键一起存储的，主键索引的叶子节点存储行数据，二级索引的叶子节点存储的不是“行指针”而是主键值，并以此作为指向行的“指针”。
而MyISAM的非聚簇表中的索引和数据是分开存储的。
主键索引与二级索引也没有什么区别。
2.2.5.4在InnoDB中按照主键顺序插入行
使用InnoDB时应该尽可能地按照主键顺序插入数据，并且尽可能使用单调增加的聚簇键的值来插入新行。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，使用UUID作为聚簇索引则会非常糟糕。
在MySQL5.1.22之前，innodb使用一个表锁解决自增字段的一致性问题（内部是用一个计数器维护，每次自增时要加表锁），如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。
在5.1.22之后，innodb使用新的方式解决自增字段一致性问题，对于可以预判行数的insert语句，innodb使用一个轻量级的互斥量。
如：
某一insert语句1执行前，表的AUTO_INCREMENT=1，语句1的插入行数已知为3，innodb在语句1的实际插入操作执行前就预分配给该语句三个自增值，当有一个新的insert语句2要执行时，读取的AUTO_INCREMENT=4，这样虽然语句1可能还没有执行完，语句2就可直接执行无需等待语句2。
这种新的自增互斥方式就是通过配置选项：
innodb_autoinc_lock_mode来实现的，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：
innodb_autoinc_lock_mode=0（“traditional”lockmode：
全部使用表锁）
innodb_autoinc_lock_mode=1（默认）（“consecutive”lockmode：
可预判行数时使用新方式，不可时使用表锁）
innodb_autoinc_lock_mode=2（“interleaved”lockmode：
全部使用新方式，不安全，不适合replication）
2.2.6覆盖索引
MySQL可以使用索引来直接获取列的数据，那么这样就不用读取数据行了，因为索引的叶子节点中已经包含要查询的数据了，就没有必要再回表查询了。
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。
覆盖索引得名于它满足了查询中给定表用到的所有的列。
想要创建一个覆盖索引，这个索引必须包含指定表上包括WHERE语句、ORDERBY语句、GROUPBY语句（如果有的话）以及SELECT语句中的所有列。
覆盖索引的优点：
1.索引条目通常远小于数据行大小，所以覆盖索引会极大的减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询覆盖索引会比随机从磁盘读取每一行数据的IO要少的多。
3.在InnoDB中，覆盖索引可以避免在二级索引中再对主键索引进行二次查询。
2.2.7使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果：
通过排序操作，或者按索引顺序扫描。
如果索引不能覆盖查询所需的全部列，那就不能不每扫描一条索引记录就都回表查询一次对应的行。
这基本上都是随机I/O。
因此按索引顺序读取数据的熟读通常要比顺序的全表扫描慢。
只有当索引列的顺序和OrderBY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。
如果查询需要关联多个表，则只有当OrderBy子句所引用的字段全部为第一个表时，才能使用索引做排序。
有一种情况下OrderBy子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。
例如：
其中rental_date、inventory_id、customer_id为索引列。
2.2.8冗余和重复索引
重复索引是指在相同的列上按照相同的顺序创建的相同类型（如果索引类型不同，并不算是重复索引）的索引，应该避免这样创建重复索引，发现以后应该立即移除。
冗余索引和重复索引有一些不同，如果创建了索引（A,B），再创建索引（A）就是冗余索引，但索引（B,A）、（B）就不是冗余索引。
但如果我们创建其他类型的索引（哈希索引、全文索引）则也不是索引（A,B）的冗余索引。
我们应该尽量扩展已有的索引而不是创建新索引，但也有时候处于性能的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
所以我们在扩建索引的时候也要尽量做好测试。
2.3索引案例学习
用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色等等。
必须支持上面的这些特征的各种组合来搜索用户。
第一件事是需要考虑使用索引来排序还是先检索数据在排序。
如果MySQL使用某个索引进行范围查询，也就没法在使用另一个索引（或者该索引的后续字段）进行排序了。
2.3.1支持多种过滤条件
country、sex列在每个查询中几乎都会用到，所以尽管这两列数据的选择性不高，我们也将他们作为索引的前缀。
对于各种复杂的查询，我们应该尽可能的重用索引，而不是新建索引。
可以使用IN（）的技巧来避免同时需要（sex、country、age）和（sex、country、region、age）两个索引，可以去掉（sex、country、age）索引。
大家看到我们将age放在最后面，这就是尽可能的将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。
但也不可滥用IN（）语句。
例如
优化器会将此语句转化为3*4*2=24种组合。
2.3.2避免多个范围条件
假设我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户：
这个查询有一个问题：
它有两个范围条件，last_online列和age列，MySQL可以使用last_online或者age的索引，但无法同时使用它们。
解决办法就是尽量使用一个范围条件，例如last_online表示上次登录时间，我们可以在新增一列active，表示7天内登录过的用户（active等于1），这样就可以不使用last_online列，而是可以在查询条件中加上active=1。
这样就可以使用最后包含age的索引了。
还有一个办法就是where条件中age改为用in（）方法。
2.3.3优化排序
这个查询无论索引如何创建都会很慢，因为MySQL需要花费大量的时间来扫描需要丢弃的数据。
有两个方法解决这个问题：
1.限制用户能够翻页的数量，因为实际上用户很少会翻到10000页。
2.使用延迟关联，先用覆盖索引找到需要返回的主键，然后在根据这些主键在原表中找到需要的行。
3查询性能优化
3.1扫描行数和访问类型
MySQL可能需要扫描多行才能返回一行结果，也有些访问方式无需扫描就能返回结果。
在EXPLAIN语句中的type列反映了访问类型，访问类型有很多中，比如全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。
这些访问类型的速度是从慢到快的。
如果发现查询需要扫描大量的数据而只返回少数的行，那么通常可以尝试下面的技巧去优化他。
1.使用索引覆盖查询，把所以需要的列都放到索引中，这样存储引擎就无需回表去获取对应行就可以返回结果了。
2.改变库表结果，例如使用汇总表
3.重写复杂的查询，例如可以将一个多表关联的sql语句换成多个单表查询的sql语句。
3.2重构查询的方式
3.2.1一个复杂查询还是多个简单查询
在某些版本的MySQL上，即使在一个通用服务器上，也能够运行每秒超过10万次的查询，即使一个千兆网卡也能轻松满足每秒超过2000次的查询，所以运行多个查询已经不是问题。
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢多了。
当然，如果一个查询能解决问题时还故意写成多个查询是不明智的。
3.2.2切分查询
有时候对于一个大查询我们可以“分而治之”，将大查询切分成多个小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
删除旧的数据就是一个很好的例子，如果数据表中数据足够大的话，那么我们直接删除全部数据就会导致服务器宕机，这个问题的解决方法是每次只删除一万条数据，分多次进行。
3.2.3分解关联查询
将关联查询分解为多次单表查询在很多高性能应用中都会用到。
例如：
可以分解为：
这样做有什么好处呢？
●缓存的效率更高，因为我们拆分为多个查询语句后，就可以将前面两个查询语句的结果放入缓存中。
●对于MySQL的查询缓存来说，如果关联的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
●将查询分解后，可以减少锁的竞争。
●在应用层做关联，可以更容易的对数据库进行拆分，更容易的做到高性能和可扩展。
●可以减少冗余记录的查询，在应用层做关联查询，记录只需要查询一次即可。
而在数据库中做关联则可能需要重复的访问一部分数据，这样来看，这可能会减少网络和内存的消耗。
●这样做就相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。
3.3查询执行的基础
查询执行路径如下：
1.服务器接收客户端的查询信息
2.服务器先在查询缓存中查找，如能找到则返回结果给客户端，如
升级会员 