解开发者之痛:中国移动MySQL数据库优化最佳实践

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
  20160812094109227.jpg 章颖

数据研发工程师


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

 

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


本次分享大纲:

  • 一个例子

  • 数据类型优化

  • 索引优化

  • SQL优化


一、一个例子

20160812094226186.png


20160812094237356.png


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


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


20160812094249633.png


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


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


20160812094301694.png


通过加索引,数据库需要处理的行数下降了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文件中保存“数字-字符串”映射关系的“查找表”。枚举字段是按照内部存储的整数而不是定义的字符串进行排序。


20160812094326315.jpg


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


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


  1. 按照需要的顺序来定义枚举列; 

  2. 在查询中使用FIELD()函数显示地指定排序顺序,但这会导致MySQL无法利用索引消除排序。 


20160812094339136.png


与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


20160812094352597.png


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


20160812094423965.png


0和空字符串都不是NULL:


20160812094433703.png


NULL与空字符串的区别 


20160812094444864.png


上图中分别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


20160812094459370.jpg


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


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


20160812094508828.jpg


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


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


20160812094519951.jpg


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


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


冗余索引:

  • Case1: 如创建了索引(A,B),再创建索引(A),则产生了冗余索引,因为索引(A)只是索引(A,B)的前缀索引。

  • Case2: 索引(A),再创建索引(A,ID),其中ID是主键,对于InnoDB来说主键列已经包含在二级索引中了,所以这也是冗余。


什么时候需要冗余索引?


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


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


索引列顺序:

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


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


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


20160812094530512.jpg


发现customer_id的选择性更高。


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


覆盖索引 


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


优势:

  • 只需要读取索引,就可以访问到数据 

  • 索引按照列值顺序存储,顺序查询比随机io要快。 


案例:


20160812094543142.png


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


20160812094553422.png


不能使用索引的场景


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

  • 使用LIKE或者REGEXP时,以%开头,即“%***”时; 

  • 在字段使用函数时; 

  • 在join时条件字段类型不一致时; 

  • 在组合索引里使用非第一个索引时; 

  • 使用!=以及<>不等于时;

  • 索引列不独立时。


四、SQL优化


Where子句中使用独立的列:


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


20160812094605612.jpg


关联查询优化: 

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

  • 关联字段类型保持一致。 


LIKE匹配优化:

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


select * from customer where last_name like 'MA%';


20160812094619262.png


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


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


select * from customer where last_name like '%MA%';


20160812094630335.png


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


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


select * from charge_record where phone=13990055761;


20160812094642400.jpg


select * from charge_record where phone=‘13990055761’;


20160812094653481.jpg


Select指定列来代替select *:

  • 在某些情况下 select *  要比select 指定列 需要浪费更多的资源 

  • 如果某些列中含有text等类型,select 指定列可以减少网络传输缓冲区的使用 

  • 如果SQL中含有order by ,并且排序不能利用上已用的索引那么,额外的字段会占用更多的sort_buffer_size . 

  • Select指定列可以方便使用覆盖索引。 


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


20160812094706595.jpg

子查询优化:


  • 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;


20160812094717333.jpg


优化后:

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

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


20160812094727679.jpg


因为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等子句冗余的写一份到各个子查询中。


案例:


20160812094737361.jpg


20160812095135438.png


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


20160812095123519.png


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


参考文献《高性能MySQL》


Q&A 

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

20160812094748476.jpg
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,因为要保证数据完整性。


本文来自云栖社区合作伙伴"DBAplus",原文发布时间:2016-08-12

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2天前
|
存储 缓存 监控
如何优化数据库子查询?
【7月更文挑战第22天】如何优化数据库子查询?
32 15
|
2天前
|
存储 缓存 监控
优化数据库查询的关键
【7月更文挑战第22天】优化数据库查询的关键
17 7
|
2天前
|
缓存 关系型数据库 MySQL
MySQL优化
【7月更文挑战第21天】MySQL优化
10 1
|
4天前
|
存储 关系型数据库 数据库
优化数据库性能的关键技术与实践
数据库作为现代应用架构的核心组成部分,其性能优化直接关系到系统整体的稳定性和效率。本文探讨了提升数据库性能的关键技术和实际应用,涵盖了索引优化、查询优化、存储引擎选择以及硬件优化等方面,旨在帮助开发者有效提升应用程序的响应速度和吞吐量。
|
4天前
|
存储 运维 NoSQL
现代化企业管理中的数据库选择与优化策略
在当今信息化时代,企业管理越来越依赖于高效的数据库系统来支撑业务运作。本文探讨了在选择和优化数据库时需要考虑的关键因素,包括数据类型、访问模式以及性能需求。通过分析不同数据库系统的特性和优劣势,帮助企业在面对日益复杂的业务需求时,选择合适的数据库解决方案,提升管理效率和业务运行质量。
|
4天前
|
存储 负载均衡 定位技术
现代数据库系统中的数据分片策略与优化
数据分片在现代数据库系统中扮演着关键角色,特别是在面对海量数据和高并发访问的情况下。本文探讨了数据分片的基本概念、常见的分片策略(如水平分片与垂直分片)、以及如何通过优化和选择合适的分片策略来提升数据库系统的性能和可扩展性。
|
6天前
|
关系型数据库 MySQL 调度
MySQL高级功能与优化策略深度探索
MySQL高级功能与优化策略深度探索
|
11天前
|
关系型数据库 MySQL API
MySQL上亿数据查询优化:实践与技巧
MySQL亿级数据查询优化涉及索引设计、分区表、查询语句优化和数据库架构调整。例如,通过为常用查询列创建索引、使用EXPLAIN分析查询计划、避免全表扫描和SELECT *,以及采用垂直拆分、水平拆分和读写分离来提升性能。分区表能减少查询数据量,API接口测试可验证优化效果。
20 0
|
11天前
|
网络协议 关系型数据库 MySQL
【最佳实践】MySQL数据库迁移到PXC集群
借本次数据库迁移实践,再次总结一下MySQL数据库迁移到PXC的最佳操作路径。
21 0
|
13天前
|
SQL 监控 Java
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
24 0

推荐镜像

更多