中国移动MySQL数据库优化经验

开源数据库MySQL比较容易碰到性能瓶颈,为此经常需要对MySQL数据库进行优 化,而MySQL数据库优化需要运维DBA与相关开发共同参与,其中MySQL参数及服务器配置优化主要由运维DBA完成,开发则需要从数据类型优化,索引优化,SQL优化三个角度考虑MySQL数据库优化问题。

本次分享将从开发角度,看如何实现MySQL数据库优化。

本次分享大纲:

  • 一个例子
  • 数据类型优化
  • 索引优化
  • SQL优化

一、一个例子

数据库需要处理的行数: 189444*1877*13482~~~479亿

如果在关联字段上加上合适的索引:

数据库需要处理的行数:368006*1*3*1~~~110万

MySQL通常是一个请求对应一个线程,其thread_handling是one-thread-per-connection,因此一条sql请求只能利用一个CPU

通过加索引,数据库需要处理的行数下降了4个数量级,第一种情况下等待半小时不一定能跑出结果,但第二种情况可以在秒级范围内拿到需要的结果。从该例子可以看出,MySQL数据库优化非常重要,一条不合理的SQL就可能导致服务异常。

开发需要掌握查看MySQL执行计划及profile工具:

  1. EXPLAIN  SELECT ……
  2. EXPLAIN EXTENDED SELECT ……
  3. profile工具

SET profiling = 1;

show profiles;

— 显示最近发送的mysql服务的sql语句

show profile;

— 显示最近的单个SQL语句的详细过程信息

show profile all for query 61;

— 显示所有相关信息

二、数据类型优化

选择数据类型的步骤:

  • Step1:确定合适的大类型,如数字、字符串、时间等;
  • Step2:选择具体类型,相同大类型的不同子类型数据的存储长度,范围,允许的精度不同,有时候也有一些特殊的行为和属性。

普遍适用的原则:

  • 使用小而简单的合适的数据类型;
  • 对于可变长字符串VARCHAR,只分配真正需要的空间;
  • 小心使用ENUM;
  • 尽量使用整型定义标识列;
  • 使用相同数据类型存储相似或者相关的值,尤其是关联条件中使用的列。

核心原则:具体问题具体分析。一些特定的业务场景并不适合套用普遍使用的原则。

>>>>

使用小而简单的合适的数据类型:

  • Case1:如果只需要存0-200,tinyint unsigned更好。因为更小的数据类型所需的磁盘,内存和CPU缓存更少,处理时需要的CPU周期也更少。
  • Case2:用INT代替varchar(15)来存储IP地址。因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。
  • Case3:使用MySQL内建的类型(date, time, datetime等)而不是字符串来存储日期和时间。
  • Case4:用char存储密码的MD5值,因为密码的MD5是一个定长的值。
>>>>

对于可变长字符串VARCHAR,只分配真正需要的空间:

使用VARCHAR(4)和VARCHAR(200)存储‘ZYHY’的空间开销是一样的,但使用更短的列VARCHAR(4)有如下优势:

因为MySQL通常会分配固定大小的内存块来保存内部值,所以更长的列会消耗更多的内存,在使用内存临时表进行排序或者操作时会特别糟糕,利用磁盘临时表进行排序时也同样糟糕。

所以,建议只分配真正需要的空间。

>>>>

小心使用ENUM

MySQL 在存储ENUM枚举时非常紧凑,会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数,并且在表的.frm 文件中保存“数字-字符串”映射关系的“查找表”。枚举字段是按照内部存储的整数而不是定义的字符串进行排序。

从上图中的select e + 0 from enum_test;的结果可以看出,MySQL在内部会将每个值在列表中的位置保存为整数,可以与整数进行算术运算。

从上图中的select e from enum_test order by e;的结果可以看出,排序结果与建表时的顺序一致,如果需要按字符创的字母顺序排序,则需要通过额外的方法来处理,比如:

  1. 按照需要的顺序来定义枚举列;
  2. 在查询中使用FIELD()函数显示地指定排序顺序,但这会导致MySQL无法利用索引消除排序。

与VARCHAR相比,ENUM优势与劣势:

  1. 优势:数据紧凑,存储的是整数,占用空间小,作为关联字段时,效率比varchar类型高很多;
  2. 劣 势:字符串列表是固定的,添加或者删除字符串必须使用ALTER TABLE,如果添加的字符串不在列表末尾,则需要重建整个表完成修改。由于ENUM保存为整数,必须进行查找才能转换为字符串,在需要转换为字符串时有 一些开销。在一些特定情况下,把varchar列和枚举列进行关联可能比varchar自关联更慢。
>>>>

尽量使用整型定义标识列

  • 因为整形数据的执行计算和比较都很快;
  • 不建议使用UUID等随机字符串作为标识列,因为随机字符串会任意分布在很大的空间,导致INSERT和SELECT语句变得很慢。
>>>>

使用相同数据类型存储相似或者相关的值,尤其是关联条件中使用的列

  • 因为混用不同的数据类型可能导致性能问题,在关联条件中会有数据类型转换的资源消耗;
  • 在比较操作时隐形类型转换可能导致很难发现的错误。
>>>>

关于整数类型指定宽度的一个解释

MySQL可以为整数类型指定宽度,如INT(11),但对大多数应用来说,这并没有什么意义:它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1) 和INT(20)是相同的。

>>>>

关于实数类型

  1. MySQL既支持精确类型(decimal, numeric),也支持不精确类型(float, double)。
  2. 可以使用DECIMAL存储比BIGINT还大的整数。
  3. CPU不支持对DECIMAL的直接计算,而是MySQL服务器自身对DECIMAL进行高精度计算。而CPU直接支持原生浮点运算,所以,浮点运算明显更快。
  4. 可以考虑使用BIGINT代替DECIMAL,将需要存储的值根据小数的位数乘以相应的倍数即可,如精确到0.01,则把所有值乘以100存储到BIGINT中,这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
>>>>

关于NULL的定义:

a missing unknown value, means “not having a value.”

与NULL的任何数学运算的结果还是NULL

判断值是否等于NULL,不能简单用=,而要用IS NULL/ IS NOT NULL

0和空字符串都不是NULL:

NULL与空字符串的区别

上图中分别insert了一个NULL和一个空字符创,其表达的意义不一样:

  • INSERT a NULL:不知道这个人有没有电话号码;
  • INSERT a ‘’: 确定这个人没有电话号码;
  •  COUNT(table.column), MIN(), and SUM() 会忽略NULL ,count(*)会计算包含NULL的所有行

三、索引优化

>>>>

索引类型

按数据存储方式分类:

  • 聚簇索引:数据行实际上存放在索引的叶子(leaf page)页中。即数据行和相邻的键值紧凑地存储在一起。
  • 二级索引(非聚簇索引):二级索引的叶子节点包含了引用行的主键列(它不指向行的物理位置,而是行的主键值)。二级索引需要两次索引查找,而不是一次。(对于InnoDb,自适应哈希索引能够减少这样的重复工作)

按索引的数据结构分类:

  • B-TREE索引
  • 哈希索引
  • 空间数据索引(R-TREE)
  • 全文索引

InnoDB主键索引结构:

在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

InnoDB非主键索引:

InnoDB的辅助索引data域存储相应的记录值及该记录对应的主键的值而不是地址。

>>>>

索引策略

  • 经常与其他表进行关联的表,在关联字段上应该建立索引;
  • 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
  • 频繁进行数据操作的表,不要建立太多的索引,数据的插入,更新和删除会对索引产生影响,太多的索引会导致插入更新删除操作缓慢;
  • 索引应该建在选择性高的字段上Cardinality/rows尽可能等于1。Show index命令查看Cardinality(索引列去重后的行数)。
  • 索引应该建在小字段上,整数字段尤其适合,对于大的文本字段甚至超长字段,不要建索引,或者建立前缀索引, 如create index 索引名 on 表名(列名1 (指定长度),……)
  • 删除无用的索引,如重复索引,不必要的冗余索引;
  • 针对组合索引,设计合理的索引列顺序

下面介绍一些与索引相关的概念。

前缀索引:索引开始的部分字符,以节约索引空间,提高索引效率。

风险:会降低索引的选择性。

对于BLOB,text或者很长的varchar类型的列,必须使用前缀索引。

否则会报错:

[Err] 1170 – BLOB/TEXT column ‘blobtext’ used in key specification without a key length

前缀索引的长度有一个权衡点:选择足够长的前缀以保证较高 的选择性,同时又不能太长。

那么如何计算不同前缀长度的选择性:

查询显示当前缀长度到达7的时候,再增加前缀长度,选择性提升的幅度已经很小。

重复索引:指在相同列上按照相同顺序创建相同类型的索引。 (SQL摘抄自《高性能MySQL》)

相当于建了三个重复索引。

MySQL需要单独维护重复索引,优化器在优化查询的时候也需要逐个进行考虑,因此 重复索引会影响性能。

冗余索引:

  • Case1: 如创建了索引(A,B),再创建索引(A),则产生了冗余索引,因为索引(A)只是索引(A,B)的前缀索引。
  • Case2: 索引(A),再创建索引(A,ID),其中ID是主键,对于InnoDB来说主键列已经包含在二级索引中了,所以这也是冗余。

什么时候需要冗余索引?

当扩展已有的索引会导致其变得太大,从而影响其他使用该索引的查询性能。

比如,在整数列上有一个索引,现在需要增多一个VARCHAR列来扩展该索引,此时,如果使用整数列与varchar列的组合索引比单独使用整数列的索引的效率要慢很多,因此,此时可以考虑冗余索引,以满足不同场景下的query需求。

索引列顺序:

在多列B-tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,…

建议将选择性最高的列放在索引最左列。

如何确定选择性更高的字段: (SQL摘抄自《高性能MySQL》)

发现customer_id的选择性更高。

索引列顺序建议为(customer_id, staff_id)。

覆盖索引 

索引包含(或者说覆盖)所有需要查询字段的值。

优势:

  • 只需要读取索引,就可以访问到数据
  • 索引按照列值顺序存储,顺序查询比随机io要快。

案例:

当发起一个被索引覆盖的查询时,在explain的extra列可以看到“Using index”的信息。

不能使用索引的场景

在一些场景下,索引不能生效,比如:

  • 使用LIKE或者REGEXP时,以%开头,即“%***”时;
  • 在字段使用函数时;
  • 在join时条件字段类型不一致时;
  • 在组合索引里使用非第一个索引时;
  • 使用!=以及<>不等于时;
  • 索引列不独立时。

四、SQL优化

Where子句中使用独立的列:

查询中列如果不是独立的,则不会使用索引。

关联查询优化: 

  • 确保ON或者USING子句的列上有索引。一般只需要在关联顺序中的第二个表的相应列上创建索引。
  • 关联字段类型保持一致。

LIKE匹配优化:

如果 LIKE 的参数是非通配字符开始的固定字符串,MySQL在做LIKE比较时也可能用到索引。

select * from customer where last_name like ‘MA%’;

Extra信息中显示使用了索引。

like后面使用通配符开始的字符串则不会使用索引

select * from customer where last_name like ‘%MA%’;

rows列显示599行,也就是customer表的总行数,因此没利用到索引。

避免SQL中出现不必要的类型转换:

select * from charge_record where phone=13990055761;

select * from charge_record where phone=‘13990055761’;

Select指定列来代替select *:

  • 在某些情况下 select *  要比select 指定列 需要浪费更多的资源
  • 如果某些列中含有text等类型,select 指定列可以减少网络传输缓冲区的使用
  • 如果SQL中含有order by ,并且排序不能利用上已用的索引那么,额外的字段会占用更多的sort_buffer_size .
  • Select指定列可以方便使用覆盖索引。

比如下面这个例子,使用到了覆盖索引。

子查询优化:

  • MySQL5.6前,子查询大多时候会先遍历outer table,对于其返回的每一条记录都执行一次subquery,而且子查询没有任何索引,导致子查询相较于关联查询要慢很多(解决方案:表连接代替子查询);
  • MySQL5.6 后,对子查询进行了大幅度的优化,将子查询结果存入临时表,使得子查询只执行一次,而且优化器还会给子查询产生的派生表添加索引,使得子查询性能得到了强劲的优化。

曾经的“绝对真理”:子查询比关联查询慢很多。——不再成立。

通过子查询优化可以减少多个查询多次对数据进行访问。

但也有时候,子查询可能比关联查询还要快。

>>>>

GROUP BY优化:

表的标识列分组比其他列分组的效率高。

SELECT actor.first_name, actor.last_name, count(*) FROM film_actor INNER JOIN actor USING (actor_id) GROUP BY actor.first_name, actor.last_name;

优化后:

SELECT actor.first_name, actor.last_name,count(*) FROM film_actor

INNER JOIN actor USING (actor_id) GROUP BY actor.actor_id ;

因为actor.actor_id是主键,分组效率会提升。

使用GROUP BY子句时,结果集会自动按照分组的字段进行排序,GROUP BY子句中可以直接使用DESC或者ASC关键字,使得分组的结果集按需要的方向排序。

So:如果没有排序需求,可以加ORDER BY NULL,让MySQL不再进行文件排序,从而提高查询效率。

>>>>

UNION优化:

除非需要消除重复的行,否则一定要使用union all,因为没有ALL关键字,MySQL会给临时表加上DISTINCT选项,使得对整个临时表做代价很高的唯一性检查。

由于union产生的临时表无法使用优化器的优化策略,所以可以直接将WHERE, ORDER BY, LIMIT等子句冗余的写一份到各个子查询中。

案例:

如果把ORDER BY, LIMIT等子句冗余写一份到各个子查询中。

则排序的基数会有效的得到降低,从而提高效率。

参考文献《高性能MySQL》

本文文字及图片出自 微信公众号

余下全文(1/3)
分享这篇文章:

请关注我们:

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注