原创 2016-08-12 章颖 DBAplus社群

 

本文根据DBAplus社群第69期线上分享整理而成,文末还有书送哦~

 

讲师介绍章颖

数据研发工程师

 

  • 现任中国移动杭州研发中心数据研发工程师,擅长MySQL故障诊断,性能调优,MySQL高可用技术,曾任中国电信综合平台开发运营中心DBA

 

开源数据库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》

 

Q&A

Q1:这个是乘积吗?那不是笛卡尔积了吗?

 


A1:这个是乘积,但不是笛卡尔积。笛卡尔积是表的总行数的乘积,这个乘积是嵌套乘积。

 

Q2:在索引以优化的前提下,MySQL 单表超过多大就要考虑分表了或者说达到其性能瓶颈了?

A2:MySQL单表过亿差不多就达到性能瓶颈了,还可以借助NoSQL的查询高效,把热点数据放在NoSQL里,减轻MySQL压力。

 

Q3:线上库上有几条select ,执行时间达到上千甚至上万秒,但我连接数据库执行只有1秒多,show processlist显示为 waut to net   max_net_package我已经设置为1个G  服务器端网络没问题,请问这个问题该怎么排查?

A3:1、检查max_allowed_packet 这个参数是否足够大且生效;2、线上是否有其他请求会堵塞那几条select;3、监控mysql服务的cpu io memorybandwidth等。

 

Q4:MySQL中flush table 的运行机制是怎么样的?(加锁还是?)之前因为MySQLdump的备份在线上出现了一个问题导致数据库宕机: 线上有条执行很长的SQL 这是我在MySQLldump脚本备份导致后来的SQL一直处于wait to flush table 导致大量的等待 追加一个问题~ 除了备份时有flush table隐士命令,还有什么操作会有隐式的flushtable,再有就是好想知道 fluh table的实现原理,这个我查了很多资料都没找到。

A4:flush会加共享锁,备份一般都有flush table,因为要保证数据完整性。