多读书多实践,勤思考善领悟

MySQL性能优化

本文于392天之前发表,文中内容可能已经过时。

一、背景

在数据爆炸的年代,单表数据达到千万级别,甚至过亿的量,都是很常见的情景。这时候再对数据库进行操作就是非常吃力的事情了,select个半天都出不来数据,这时候业务已经难以维系。技术在进步虽然有了NoSQL、NewSQL数据库,但是它们都有各自的应用场景,在不适合的场景下还只能老老实实的使用关系型数据库。

1.1 数据库的不同类型

1.1.1 常用的关系型数据库

  • Oracle:功能强大,主要缺点就是贵。

  • MySQL:互联网行业中最流行的数据库,这不仅仅是因为MySQL的免费。可以说关系数据库场景中你需要的功能,MySQL都能很好的满足,后面详解部分会详细介绍MySQL的一些知识点。

  • MariaDB:是MySQL的分支,由开源社区维护,MariaDB虽然被看作MySQL的替代品,但它在扩展功能、存储引擎上都有非常好的改进。

  • PostgreSQL:也叫PGSQL,PGSQL类似于Oracle的多进程框架,可以支持高并发的应用场景,PG几乎支持所有的SQL标准,支持类型相当丰富。PG更加适合严格的企业应用场景,而MySQL更适合业务逻辑相对简单、数据可靠性要求较低的互联网场景。

1.1.2 NoSQL数据库(非关系型数据库)

  • Redis:提供了持久化能力,支持多种数据类型。Redis适用于数据变化快且数据大小可预测的场景。

  • MongoDB:一个基于分布式文件存储的数据库,将数据存储为一个文档,数据结构由键值对组成。MongoDB比较适合表结构不明确,且数据结构可能不断变化的场景,不适合有事务和复杂查询的场景。

  • HBase:建立在HDFS,也就是Hadoop文件系统之上的分布式面向列的数据库。类似于谷歌的大表设计,HBase可以提供快速随机访问海量结构化数据。在表中它由行排序,一个表有多个列族以及每一个列族可以有任意数量的列。HBase依赖HDFS可以实现海量数据的可靠存储,适用于数据量大,写多读少,不需要复杂查询的场景。

  • Cassandra:一个高可靠的大规模分布式存储系统。支持分布式的结构化Key-value存储,以高可用性为主要目标。适合写多的场景,适合做一些简单查询,不适合用来做数据分析统计。

  • Pika:一个可持久化的大容量类Redis存储服务, 兼容五种主要数据结构的大部分命令。Pika使用磁盘存储,主要解决Redis大容量存储的成本问题。

1.1.3 NewSQL数据库(新一代关系型数据库)

  • TiDB:开源的分布式关系数据库,几乎完全兼容MySQL,能够支持水平弹性扩展、ACID事务、标准SQL、MySQL语法和MySQL协议,具有数据强一致的高可用特性。既适合在线事务处理,也适合在线分析处理。

  • OceanBase:OceanBase是蚂蚁金服的数据库,OB是可以满足金融级的可靠性和数据一致性要求的数据库系统。当你需要使用事务,并且数据量比较大,就比较适合使用OB。不过目前OB已经商业化,不再开源。

二、结构设计优化

2.1 表场设计

  • 数据库主键设计,推荐带时间属性自增长数字ID(分布式自增长ID生成算法)如:雪花算法、百度分布式ID算法、美团分布式ID算法。
  • 表字段不为空,因为空值很难查询和优化,占用额外的索引空间,建议默认数字0。
  • 对于数据状态类型字段,比如status、type等,尽量不要定义负数,比如-1。因为可以加上unseigned,所以数值容量会翻倍。
  • 如果可能,使用 tinyint,smallint 代替 int,并且尽量不要使用 bigint,因为它占用的空间更少。
  • 字符串类型的字段会比数字类型的字段占用更多空间,所以尽量使用整数而不是字符串。在许多情况下,编码逻辑可以使用整数来代替。
  • 字符串类型的长度不能随意设置,在满足业务需求的前提下尽量小。
  • 使用整数来存储 IP。
  • 一个表中不要有太多字段。建议20以内。
  • 提前为可预测的字段预留,因为数据量越大,修改数据结构越耗时。

2.2 分割

  • 分区是一种水平分区,它按照一定的规则将一个表分成几个更小、更容易管理的部分。对应用程序完全透明,不影响应用程序的业务逻辑,即无需修改代码。因此,它可以存储更多的数据,查询和删除,还支持分区操作,从而达到优化的目的。如果考虑分区,可以提前做好准备,避免以下限制:
  • 一张表最多只能有1024个分区 mysql 5.6以后支持8192个分区)。但实际操作时,最好不要一次打开超过100个分区,因为打开分区也有时间损失。
  • 如果分区字段中有主键或唯一索引列,则必须包括所有主键列和唯一索引列。如果表中有主键或唯一索引,则分区键必须是主键或唯一索引。
  • 外键约束不能在分区表中使用。
  • 空值会使分区过滤失效,所以会放入默认分区。请不要让分区字段显示为空。
  • 所有分区必须使用相同的存储引擎。

2.3 子表

子表包括水平子表和垂直子表。

横向分表拆分成数据结构相同的小表,如table1、table2……以缓解数据库读写压力
垂直分表是指一些字段被分离出来形成一个新的表。每张表的数据结构不同,可以优化高低锁表的情况。

可以想象,在分表的情况下需要修改程序的逻辑。因此,在项目前期预见到大量数据时,一般会考虑分表。后期不建议分表,所以成本很高。

2.4 子库

分库一般是主从模式。将数据库服务器的主节点复制到一个或多个从节点和多个数据库。主库负责写操作,从库负责读操作,从而达到主从分离、高可用和数据备份的优化目的。

当然,主从模式也有一些缺陷,比如主从同步的延迟,binlog文件过大带来的问题等等。

2.4 其他

热表和冷表是隔离的。对于历史数据,当查询和使用的人数较少时,可以将其移至另一个冷库,仅用于查询,以缓解热量表数据量大的情况。

2.5 索引设计

  • 索引,空间换时间的优化策略,基本根据业务需要设计好的索引,足以应付百万数据,养成使用explain的习惯,关于explain也可以访问:explain让你的SQL写得更多脚踏实地,了解更多。
  • 一个常识:索引越多越好。索引会降低数据写入的性能。
  • 索引字段的长度尽可能的短,这样可以节省大量的索引空间;
  • 取消外键可以通过程序进行约束,具有更好的性能。
  • 复合索引匹配的最左列规则,索引顺序和查询条件一致,尽可能去掉不必要的单列索引。
  • 值分布较少(不重复较少)的字段不适合索引。例如,当只有性别等两个或三个值时,对字段进行索引是没有意义的。
  • 建议对需要排序的字段添加索引,因为索引会进行排序,可以提高查询性能。
  • 前缀索引用于字符串字段而不是全字段索引,可以大大减少索引空间。

三、模糊查询优化

我们经常在数据库中使用 LIKE 操作符来完成对数据的模糊搜索,LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

如果需要查找客户表中所有姓氏是“马”的数据,可以使用下面的 SQL 语句:

SELECT * FROM Customer WHERE Name LIKE ‘马%’

如果需要查找客户表中所有手机尾号是“阳”的数据,可以使用下面的 SQL 语句:

SELECT * FROM Customer WHERE Phone LIKE ‘%阳’

如果需要查找客户表中所有名字中包含“东”的数据,可以使用下面的 SQL 语句:

SELECT * FROM Customer WHERE Name LIKE ‘%东%’

以上三种分别对应了:左前缀匹配、右后缀匹配和模糊查询,并且对应了不同的查询优化方式。

左前缀匹配查询优化:索引就可以

我们可以采用“以空间换时间”的方式来解决右后缀匹配查询时效率低下的问题。

简单来说,我们可以将字符串倒过来,让右后缀匹配变成左前缀匹配。以“防着古海回来再抓孙悟空”为例,将其倒置之后的字符串是“空悟孙抓再来回海古着防”。当需要查找结尾为“孙悟空”的数据时,去查找以“空悟孙”开头的数据即可。

具体做法是:在该表中增加“txt_back”列,将“txt”列的值倒置后,填入“txt_back”列中,最后为 “txt_back”列增加索引。

模糊查询,根据用户量和接口qps决定使用哪种优化方式,如果用户量级小,是面向B端的系统,可以采用mysql的feature进行查询优化,如果用户量级大,我们需要的就是一个全文检索引擎。

3.1 MySQL是如何优化模糊匹配like的SQL

3.1.1 全文索引

语法

1
2
3
alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);
MySQL 中的全文索引,有两个变量,最小搜索长度和最大搜索长度,对于长度小于最小搜索长度和大于最大搜索长度的词语,都不会被索引。
从MySQL 5.7开始内置了ngram全文检索插件,用来支持中文分词,并且对MyISAM和InnoDB引擎有效。

mysql 官方文档对match策略的解释:https://dev.mysql.com/doc/internals/en/full-text-search.html

3.1.2 生成列

MySQL 5.7开始支持生成列,生成列是由表达式的值计算而来,有两种模式:VIRTUAL和STORED,如果不指定默认是VIRTUAL,创建语法如下

1
col_name data_type [GENERATED ALWAYS] AS (expr) [**VIRTUAL** | **STORED**] [NOT NULL | NULL]

可以解决右后缀匹配的问题,但是不能解决模糊查询的问题。也是将字符串倒过来。

3.1.3 索引条件下推ICP

MySQL 5.6开始支持ICP(Index Condition Pushdown),不支持ICP之前,当进行索引查询时,首先根据索引来查找数据,然后再根据where条件来过滤,扫描了大量不必要的数据,增加了数据库IO操作。在支持ICP后,MySQL在取出索引数据的同时,判断是否可以进行where条件过滤,将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据,减少了不必要数据被扫描带来的IO开销。在某些查询下,可以减少Server层对存储引擎层数据的读取,从而提供数据库的整体性能。

开启ICP特性后,由于like条件可以通过索引筛选,存储引擎层通过索引与where条件的比较来去除不符合条件的记录,这个过程不需要读取记录,同时只返回给Server层筛选后的记录,减少不必要的IO开销。

3.2 全文检索引擎

3.2.1 Sphinx

如果数据源存放在MySQL,可是使用:Sphinx

其实咱们KM早期就是使用Sphinx实现全文检索查询的,Sphinx可以非常容易的与SQL数据库和脚本语言集成。当前系统内置MySQL和PostgreSQL 数据库数据源的支持,也支持从标准输入读取特定格式 的XML数据。通过修改源代码,用户可以自行增加新的数据源。现在KM使用的是搜搜的底层实现搜索;

Sphinx:原生支持基于MySQL的表建索引

ElasticSearch官方文档上,数据都是使用RESTful接口一条一条插入的,也就是增量更新。在数据量非常大的时候,遍历全表重建一次索引会非常消耗时间。而elasticsearch-rivel-mysql这个项目并不是很靠谱,开发者甚至曾经在git上标明deprecated(现在没了)。反正我是自己另外写了一套。

在导入MySQL数据生成索引时,从易用性、可靠性、速度上来看,Sphinx优于ElasticSearch。

资源占用,Sphinx优于ElasticSearch。不得不说,java在这方面比不上C++。CPU还好,差距不大,内存占用方面真心天差地别。

3.2.2 Elasticsearch

如果你是使用MongoDB,中文分词同样是个坑!业界通用方案是使用Elasticsearch 实现中文检索。 大致的路数是:使用 mongo-connector 将数据同步到Elasticsearch中;

增量更新支持,ElasticSearch优于Sphinx。ElasticSearch把增量更新作为首选CURD方式;而Sphinx使用辅助表的方案不但不优雅,还会让你的其他系统变得复杂起来,在你频繁更改单条数据的时候很容易出错。

搜索算法支持,ElasthcSearch的搜索底层功能基于Lucene,Sphinx也该有的都有。然而ElasticSearch的Query DSL支持更复杂的查询逻辑,这一点是超越Sphinx的。

在自定义Ranker方面,ElasticSearch的Function Score Query比Sphinx的expression-ranker强大许多。

横向扩展与高可用,ElasticSearch是天生为了集群化而设计的。索引如果没有Replica就会显示黄灯,有才会亮绿灯。每个节点分为Client Node、Data Node、Master Node三种角色,在合理的配置之下,任意一台(甚至多台)机器炸了,整个集群都能正常运行。ElasticSearch还支持动态加机器等等功能,暂不赘述。

Sphinx也有master searchd和slave searchd的概念,可以分布式,但想实现高可用就相当复杂了。

ElasticSearch优于Sphinx。Sphinx的劣势不在于做不到,而在于不好用。

3.2.3 RediSearch

最后还有一个好东西RediSearch是一款基于redis的搜索组件。基于redis,性能高效,实时更新索引,支持Suggest前缀、拼音查找(AutoComplete 功能) ,支持单个或多个分词搜索 ,可根据字段进行结果排序。RediSearch的吞吐量高、延迟低,速度相比ElasticSearch 和 Solr要快120%到500%,欠缺中文的模糊搜索支持的不是很好。

主要特性

RediSearch 是在Redis基础上从0开始开发的一个全文搜索索引,使用新的Redis Modules API来扩展Redis新命令和能力,它的主要特性包括:

  • 简单,快速索引和搜索
  • 数据存储在内存中,使用内存-有效的自定义数据结构
  • 支持多种使用UTF-8编码的语言
  • 文档和字段评分
  • 结果的数值过滤
  • 通过词干扩展查询
  • 精确的短语搜索
  • 按特定属性过滤结果(例如仅在标题中搜索“foo”)
  • 强大的自动提示引擎
  • 增量索引(不需要对索引进行优化和压缩)
  • 支持用作存储在另一数据库中的文档的搜索索引
  • 支持已经在Redis中存在的HASH对象作为文件的索引
  • 扩展到多个Redis实例

四、普通SQL查询优化

  • 尝试使用短查询而不是复杂的内联查询。
  • 查询不使用select *,尽量查询带索引的字段,避免返回表。
  • 尝试使用limit来限制查询的数量。
  • 查询字段应尽可能在索引上,尤其是复合索引。我们应该更加注意最左边的前缀匹配。
  • 拆分大的删除/插入操作会锁定表,影响其他业务操作。另一方面,MySQL 对 SQL 的长度也有限制。
  • 不建议使用MySQL函数、计算等,可以先由程序处理。从上面提到的一些点,我们会发现,程序可以处理的,尽量不要把压力转给数据库。因为大部分服务器的性能瓶颈都在数据库中。
  • 查询计数,性能:count(1)=count(*)>count(主键)>count(其他字段)。
  • 如果可以使用between,则不需要in。如果可以使用in,则不需要or。
  • 避免使用=或<>、is null或is not null、in、not in等,因为这些查询不能使用索引。
  • SQL 应该尽可能简单,并使用更少的连接。不建议使用两个以上的连接。