ActionTech的博客

SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句，虽然数据库本身对 SQL 语句的优化一直在持续改进、提升，但是我们不能完全依赖数据库，应该在给到数据库之前就替它做好各种准备工作，这样才能让数据库来有精力做它自己擅长的事情。就拿 MySQL 来讲，一条 SQL 语句从客户端发出到数据库端返回结果一般会经历几个阶段：词法解析、语法解析、语义解析、逻辑优化、物理优化、最终执行并返回结果。那么这几个阶段，我们 DBA 能参与的也就是两个阶段：逻辑优化以及少许物理优化。

大佬的专栏更新啦

分区键为关联条件。如果分区键为非关联条件，那过滤条件必须得是分区键。两分区表的分区方法，分区数目必须一致。

之前的篇章我们讨论的都是基于单列的分区表，那有无必要建立基于多列的分区表？这种分区表数据分布是否均匀？有无特殊的应用场景？有无特殊的优化策略？本篇基于这些问题来进行重点解读。MySQL 不仅支持基于单列分区，也支持基于多列分区。比如基于字段（f1,f2,f3)来建立分区表，使用方法和使用场景都有些类似于联合索引。比如下面查询语句，同时对列(f1,f2,f3) 进行过滤。多列分区表的前提是参与分区的列检索频率均等，如果不均等，就没有必要使用多列分区。我们还是以具体实例来验证下多列分区的优缺点以及适用场景，这

提到分区表，一般按照范围（range）来对数据拆分居多，以哈希来对数据拆分的场景相来说有一定局限性，不具备标准化。接下来我用几个示例来讲讲 MySQL 哈希分区表的使用场景以及相关改造点。对于哈希分区表，最通俗的方法就是 hash 单个字段，比如下面表 hash_t1（存有500W行记录），按照自增 ID 来做 HASH ，分区数目为 1024 ：mysql:ytt_new> show create table hash_t1\G*************************** 1. r.

基于时间类分区我之前写过实现篇、细节篇。今天来继续分享一下时间类分区的真实案例： 某家互联网公司数据库系统内的分区表调优过程。问题与背景：单张表数据量太大，每天会产生 10W 条记录，一年就是 3650W 条记录，对这张表的查询 95% 都是在某一天或者几天内，过滤区间最大不超过一个月。比如在2019年3月1日、2019年4月20日或者是2019年5月1日和2019年5月5日这个时间段内。偶尔会涉及到跨月、跨年查询，但是频率很低。记录保留10年。也就是单表3.6亿条记录，单表太大，不便于管理，后.

上篇《MySQL 时间类分区具体实现》介绍了时间类分区的实现方法，本篇是对上篇的一个延伸，介绍基于此类分区的相关 SQL 编写注意事项。对于分区表的检索无非有两种，一种是带分区键，另一种则不带分区键。一般来讲检索条件带分区键则执行速度快，不带分区键则执行速度变慢。这种结论适应于大多数场景，但不能以偏概全，要针对不同的分区表定义来写最合适的 SQL 语句。用分区表的目的是为了减少 SQL 语句检索时的记录数，如果没有达到预期效果，则分区表只能带来副作用。 接下来我列举几个经典的 SQL 语句：细心的读者.

适用分区或者说分表最多的场景依然是针对时间字段做拆分， 这节我们详细讲讲如何更好的基于时间字段来拆分。分别按照年、月、日几个维度的实现方法以及一些细节注意事项。第一，以年为维度做拆分日期字段拆分粒度的选择跟业务检索请求密切相关。比如保留10年数据，每次查询基于某个具体年份做为过滤条件，那按照年拆分肯定最好。例如下面SQL：select * from ytt_pt1 where log_date >='2018-01-01' and log_date < '2019-01-01';那我.

MySQL 数据库现在主要用的引擎是 InnoDB ，InnoDB 没有类似于 MERGE 引擎这样的原生拆表方案，不过有原生分区表，以水平方式拆分记录集，对应用端透明。分区表的存在为超大表的检索请求、日常管理提供了一种额外的选择途径。分区表使用得当，对数据库性能会有大幅提升。分区表主要有以下几种优势：大幅提升某些查询的性能。简化日常数据运维工作量、提升运维效率。并行查询、均衡写 IO 。对应用透明，不需要在应用层部署路由或者中间层。接下来我们用实际例子来让前两种优势体现更新清晰。.

引言上一章节我们探讨过数据垂直拆分，今天我们来继续讨论数据拆分：水平拆分！水平拆分和垂直拆分有些不一样，垂直拆分最小单元是字段，与业务有很强的关联性，具体业务对应具体的拆分数据；而水平拆分最小单元是数据行，与具体业务关系不大，业务关联可以是拆分后的单张表数据，也可以是拆分前的全局数据。简单来说，水平拆分对应用透明，应用逻辑在数据拆分后不需要大动。正文一般在关系数据库中，水平拆分具体对应两个方面：第一是水平拆表。水平拆表是基于一张表的某个字段，以一定的拆分方法拆分为多张表的数据拆分，拆分完后需要.

引言一般来说讲，提到数据拆分，可以归结为两个层面：一是垂直拆分，二是水平拆分。这里我们来讨论下垂直拆分。垂直拆分是以数据库、表、列等为单位进行拆分的方法。正文MySQL里垂直拆分可以细分为：垂直拆库（实例级别）、垂直拆模（表级别）、垂直拆表（列级别）。1、垂直拆库：也即在业务层按照业务逻辑由大拆小，各个子业务之间无关联查询，仅查询单个子业务即可，类似微服务治理的思想。在 MySQL 里表现为将全部表按照业务关联紧密程度拆分后存储在不同的数据库，每个数据库为一台 MySQL 实例，查询仅查对应的.

引言：上一篇我介绍了 MySQL 范式标准化表设计，范式设计具有以下优点：把如何消除数据冗余做到极致，从而减少关系表对磁盘的额外占用。各个表之间的关系表现非常清晰，可读性非常强。正文：但是范式设计同样也有缺点：表范式标准化，等级越高，表数量就越多。比如 2NF 比 1NF 可能要多几张表，3NF 比 2NF 可能又要多几张表等等。表数量越多，查询时可能需要关联的表就越多。 我们知道，检索多表关联的开销比检索单表的开销要大的多。综上，我们需要结合范式设计的优点，并且想办法去解决范.

关系表设计合理与否是影响关系型数据库性能的核心要素之一。谈到关系型数据库表设计问题，首先想到的是范式理论。也就是说一张表的设计首先要满足一定的范式，完了后再根据一定的需求来反范式设计，也即冗余备用设计。数据库范式一般包含6个，分别为1NF、2NF、3NF、BCNF、4NF、5NF。 这6个范式级别分别从数据是否允许一定范围的冗余、数据是否更加精细化的管理这两个关键点出发，越往后，数据越规范，冗余越小，可读性越强。一般来说，达到3NF或者BCNF即可，或者更进一步，达到4NF即可，5NF更加偏向学术。.

通过前面一些关于索引设计的相关介绍与示例，相信大家已经对索引设计这块有了一些零碎的认识，那本篇来做下总结，给出一个索引设计的详细规范。索引命名规范：单值索引，建议以 idx_ 为开头，字母全部小写。例如：alter table t1 add key idx_r1(r1)；组合索引，建议以 dx_multi_ 开头，字母全部小写。例如：alter table t1 add key idx_multi_1(r1,r2,r3) ;唯一索引，建议以 udx_ 为开头，字母全部小写；如果是多.

一般提到索引，大家都只关注索引的优点，一个优秀的索引的确会让查询请求的效率大幅提升、亦或是大幅提升带有索引键进行过滤的写入请求（update、delete)效率。 不可否认，索引在这样的场景下带来巨大的性能提升，但是对于整张表的写请求来讲，则刚好相反，索引的存在会在一定程度上降低写入请求的效率。为了维护索引数据的准实时性而让优化器基于索引做出更优化的执行计划，数据库会自动更新索引数据分布，比如带来索引页的拆分与合并等。那索引的存在对写入请求影响到底有多大？ 也就是说，索引的数量多少与写请求效率的降低有没.

本篇是全文索引终篇，来细聊下 MySQL 全文索引对中文如何处理。在了解 MySQL 全文索引如何处理中文之前，先来看看什么是分词。MySQL 全文索引默认是基于单字节流处理的，也就是按照单词与停止词（默认空格或者标点符号）来划分各个关键词，并且把关键词的文档ID和位置保存到辅助表用于后期检索。这种对英文，数字类的单字节字符处理很好， 比如“I am a boy!”， 每个单词很明确的用空格分割，后期查询只需要按照以空格为分隔符的单词检索就行，这些我前面三篇文章已经详细讲过。但是这种分割方法对多字节字符.

接着讲 MySQL 全文索引，这篇主要探讨 MySQL 全文索引的监测。MySQL 有很完整的元数据表来监测全文索引表的插入，更新，删除；甚至全文索引表以及辅助表的数据追踪。这里分为三个部分：第一部分，介绍监测相关参数；第二部分，介绍监测相关元数据表；第三部分，实例演示如何进行监测。第一部分， 全文索引监测相关参数：innodb_ft_aux_table： 动态设置被监测的全文索引表名。 这个参数必须显式设置，才能对全文索引正常监测。 值一般为：数据库名/表名，比如 ytt/ft_sampl.

上一篇介绍了全文索引的基本原理，这篇来讲讲如何更好的使用全文索引。全文索引的检索和普通检索的语法不同，普通检索一般类似下面SQL：select * from tb1 where id in (1,2);select * from tb1 where id < 10;过滤条件在WHERE子句后面，以一定的方式来拼接SQL，全文索引的使用有特定的语法：MATCH (col1,col2,...) AGAINST (expr [search_modifier])search_mo..

前面介绍了 B 树索引、哈希索引，接下来看看 MySQL 全文索引。在讲全文索引之前，可以看看如下很常见的一类 SQL 语句： select count(*) from fx where s1 like '%cluster%'这条语句从表 fx 中检索字段 s1，过滤条件为 ‘%cluster%’，这样的模糊查找语句性能很差，即使在字段 s1 上有索引也因无法找到切入点从而对表 fx 进行全表扫描，特别是对于一张大表，这类 SQL 的性能无疑致命。全文索引则很好地解决了这类低效 SQL 的性能问.

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是 MySQL 5.6 发布后针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。MySQL ICP 里涉及到的知识点如下：1.MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存.

这篇主要介绍 MySQL 索引的 Cardinality 值（基数）以及索引的可选择性。索引基数值索引基数的含义：由索引中唯一值计算的一个预估值。索引基数值的准确程度直接影响到 MySQL 优化器基于此索引的查询计划是否准确高效。与索引基数值最为密切的典型场景就是：一条 SQL 在某一时刻执行比较慢，其中较为可能的原因就是当前表记录更新频繁，这条 SQL 执行计划走的索引基数值没及时更新，优化器选择走备用索引或者走全表扫描，从而非最优执行计划，最终执行结果没有达到预期，总体查询时间较慢，这时可能得.

说到这儿，可能有的人会有些疑问： 多值索引不就是联合索引吗，还需要单独开一篇来讲？多值索引和基于多个字段的联合索引完全不同，联合索引是基于多个一维字段，比如字段 r1 int, r2 int,r3 int，这三个字段的组合是联合索引。一般用于三个字段的联合查找，比如 r1 = 1 and r2 = 2 and r3 = 2 等等。多值索引则不同，它是基于单个字段的，不同的是多值索引基于多维字段，比如数组：[1,2,3,4,5,6] ，基于这样的一个数组来建立索引，可以用来检索数组内任何一个元素值。比如.

上篇文章已经详细介绍 MySQL 组合索引的概念以及其适用场景，这篇主要介绍 MySQL 组合索引的不适用场景以及改造方案。回顾下组合索引的语法和必备条件【组合索引的语法】（只讨论默认升序排列）alter table t1 add idx_multi(f1, f2, f3);【必备条件】列 f1 必须存在于 SQL 语句过滤条件中！也就是说组合索引的第一个列（最左列）在过滤条件中必须存在，而且最好是等值过滤。看看下面这些 SQL，没有一款适合组合索引。# SQL 1select * from

建立在多个列上的索引即组合索引（联合索引），适用在多个列必须一起使用或者是从左到右方向部分连续列一起使用的业务场景。组合索引和单值索引类似，索引上的每个键值按照一定的大小排序。比如针对三个字段的组合索引有以下组合：(f1, f2, f3)(f1, f2, f3 desc)(f1, f2 desc, f3)(f1 desc, f2, f3)(f1 desc, f2 desc, f3 desc)…今天讨论的组合索引只基于默认排序方式，也就是 (f1,f2,f3)，等价于 (f1 asc, .

本篇主要介绍 MySQL 的函数索引（也叫表达式索引）。通常来讲，索引都是基于字段本身或者字段前缀（第 20 篇），而函数索引是基于字段本身加上函数、操作符、表达式等计算而来。如果将表达式或者操作符也看做函数的话，简单来说，这样的索引就可以统称函数索引。MySQL 的函数索引内部是基于虚拟列（generated columns）实现，不同于直接定义虚拟列，函数索引自动创建的虚拟列本身实时计算结果，并不存储数据，只把函数索引本身存在磁盘上。MySQL 8.0.13 之前不支持函数索引，所以老版本包括现.

这里主要介绍 MySQL 的前缀索引。从名字上来看，前缀索引就是指索引的前缀，当然这个索引的存储结构不能是 HASH，HASH 不支持前缀索引。先看下面这两行示例数据：你是中国人吗？是的是的是的是的是的是的是的是的是的是的确定是中国人？是的是的是的是的是的是的是的是的是的是的这两行数据有一个共同的特点就是前面几个字符不同，后面的所有字符内容都一样。那面对这样的数据，我们该如何建立索引呢？大致有以下 3 种方法：拿整个串的数据来做索引这种方法来的最简单直观，但是会造成索引空间极大的浪费.

这里讲述 MySQL 哈希索引的实现方式以及使用场景。哈希表在 MySQL 里有如下应用：各种存储引擎的哈希索引存储。MySQL 的 Memory，NDB 都有哈希索引的具体实现；MySQL 内部生成的哈希表。比如：数据在 innodb buffer pool 里的快速查找；子查询的物化自动加哈希索引；JOIN KEY 无 INDEX 下的 HASH JOIN 等。一、哈希数据分布哈希索引显式应用主要存在于内存表，也就是 Memory 引擎，或者是 MySQL 8.0 的 Temp.

MySQL 的默认索引结构是 B+ 树，也可以指定索引结构为 HASH 或者 R 树等其他结构来适应不同的检索需求。这里我们来介绍 MySQL 哈希索引。MySQL 哈希索引又基于哈希表（散列表）来实现，所以了解什么是哈希表对 MySQL 哈希索引的理解至关重要。接下来，我们来一步一部介绍哈希表。1. 数组数组是最常用的数据结构，是一种线性表的顺序存储方式，由下标（也叫索引）和对应的值构成。数组在各个开发语言以及数据库中都有类似的结构，类似下图1：图 1 展示了一个一维整数数组，数组的长度为 1.

表的主键指的针对一张表中的一列或者多列，其结果必须能标识表中每行记录的唯一性。InnoDB 表是索引组织表，主键既是数据也是索引。主键的设计原则对空间占用要小上一篇我们介绍过 InnoDB 主键的存储方式，主键占用空间越小，每个索引页里存放的键值越多，这样一次性放入内存的数据也就越多。最好是有一定的排序属性如 INT32 类型来做主键，数值有严格的排序，那新记录的插入只要往原先数据页后面添加新记录或者在数据页后新增空页来填充记录即可，这样有严格排序的主键写入速度也会非常快。数据类型为整形数.

上一章讲了数据库基本上都用 B+ 树来存储索引的原因：适合磁盘存储，能够充分利用多叉平衡树的特性，磁盘预读，并且很好的支持等值，范围，顺序扫描等。这篇主要介绍 MySQL 两种常用引擎，MyISAM 和 InnoDB 的索引组织方式，了解这些存储方式，对数据库优化很有帮助。MySQL 的索引按照存储方式分为两类：**聚集索引：**也称 Clustered Index。是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放，一张表最多也只能存在一个聚集索引。与非聚集索引相比，聚.

谈到索引，大家并不陌生。索引本身是一种数据结构，存在的目的主要是为了缩短数据检索的时间，最大程度减少磁盘 IO。任何有数据的场景几乎都有索引，比如手机通讯录、文件系统（ext4\xfs\ntfs)、数据库系统（MySQL\Oracle）。数据库系统和文件系统一般都采用 B+ 树来存储索引信息，B+ 树兼顾写和读的性能，最极端时检索复杂度为 O(logN)，其中 N 指的是节点数量，logN 表示对磁盘 IO 扫描的总次数。**MySQL 支持的索引结构有四种：**B+ 树，R 树，HASH，FULLT.

MySQL InnoDB 表数据页或者二级索引页（简称数据页或者索引页）的合并与分裂对 InnoDB 表整体性能影响很大；数据页的这类操作越多，对 InnoDB 表数据写入的影响越大。MySQL 提供了一个数据页合并临界值（MERGE_THRESHOLD），在某些场景下，可以人为介入，减少数据页的合并与分裂。在 InnoDB 表里，每个数据页默认16K 大小，默认 MERGE_THRESHOLD 值为 50，取值范围从 1 到 50，默认值即是最大值。也就是当页面记录数占比小于 50% 时，MySQL .

本篇介绍 MySQL 表如何计算统计信息。表统计信息是数据库基于成本的优化器最重要的参考信息；统计信息不准确，优化器可能给出不够优化的执行计划或者是错误的执行计划。对统计信息的计算分为 非持久化统计信息（实时计算）与 持久化统计信息。非持久化统计信息统计信息没有保存在磁盘上，而是频繁的实时计算统计信息；每次对表的访问都会重新计算其统计信息；假设针对一张大表的频繁查询，那么每次都要重新计算统计信息，很耗费资源。持久化统计信息把一张表在某一时刻的统计信息值保存在磁盘上；避免每次查询时重新.

上一篇已经解了压缩表的相关概念、索引页的影响以及简单使用。这篇主要来介绍如何观测压缩表。一、压缩表的使用场景分类1. SELECT 业务这类操作不需要对压缩页进行解压，所以非常适合使用压缩表。2. INSERT 业务这类操作需要重新对二级索引数据页解压和以及重新压缩，不过 MySQL 对这部分操作放入 change buffer，所以频率相对来说不是很高。3. DELETE 业务由于 MySQL 对删除的操作是直接写标记位，然后等待定期的 PURGE 线程清理，这块也适合用压缩表。4. U.

一、概念压缩表从名字上来看，简单理解为压缩后的表，也就是把原始表根据一定的压缩算法按照一定的压缩比率压缩后生成的表。1.1 压缩能力强的产品表压缩后从磁盘占用上看要比原始表要小很多。如果你熟悉列式数据库，那对这个概念一定不陌生。比如，基于 PostgreSQL 的列式数据库 Greenplum；早期基于 MySQL 的列式数据库 inforbright；或者 Percona 的产品 tokudb 等，都是有压缩能力非常强的数据库产品。1.2 为什么要用压缩表？情景一：磁盘大小为 1T，不算其他的.

表空间的选择，可以说是对表的日常管理以及访问性能有非常紧密的联系。表空间是用来管理 MySQL 关系表的一种形式，有自己的磁盘文件。MySQL 表空间可分为共享表空间和单表空间；其中共享表空间又可分为系统表空间和通用表空间。下面我来逐一看下每种表空间的相关特性。一、系统表空间在 MySQL 数据目录下有一个名为 ibdata1 的文件，可以保存一张或者多张表。923275 12M -rw-r----- 1 mysql mysql 12M 3月 18 10:42 ibdata1这个文件.

相信大家通过前几篇文章，已经了解了 MySQL 字符集使用相关注意事项。那么数据乱码问题在这儿显得就非常简单了，或许说可能不会出现这样的问题。数据之所以会乱码，在 MySQL 里无非有以下几类情况：一、转码失败在数据写入到表的过程中转码失败，数据库端也没有进行恰当的处理，导致存放在表里的数据乱码。针对这种情况，前几篇文章介绍过客户端发送请求到服务端。其中任意一个编码不一致，都会导致表里的数据存入不正确的编码而产生乱码。比如下面简单一条语句：set @a = "文本字符串";insert i.

本文关键字：字符集、建库建表一、数据库和字符集1、建库时指定​创建数据库时，显式指定字符集和排序规则，同时，当切换到当前数据库后，参数 character_set_database，collation_database 分别被覆盖为当前显式指定的字符集和排序规则。举个简单例子，创建数据库 ytt_new2，显式指定字符集为 latin1，同时排序规则为 latin1_bin。之后在切换到数据库 ytt_new2 后，对应的系统参数也被修改。mysql> create database y..

本篇为理清字符集的续篇（上一篇：第06期：梳理 MySQL 字符集的相关概念），重点讲述字符集涉及到的 sql 语句用法。一、character introducer翻译过来就是字符引导。也就是针对字符串，显式的给定一个字符编码和排序规则，不受系统参数的影响。语法很简单：[_charset_name]'string' [COLLATE collation_name] 示例：字符串 “北京加油❤!”-- 字符集 utf8mb4，排序规则 utf8mb4_binselect _utf8m..

此篇介绍 MySQL 字符集、排序规则、相关的元数据、参数等设置以及使用情况。概念字符集的内容包含：字符集（character set）和排序规则（collation rule）每种字符集可对应一到多个排序规则，每种排序规则对应一种字符集字符集是一套字符与一套编码的映射集合，像这样：字符编码A0B1……排序规则是字符集内用来比较每个字符的一套规则，也就是字符的排序方式比如要比较字符 A 和 B 的大小，最简单直观的方法就是对比..