mysql 的服务指南

MySQL（02） 存储引擎的 InnoDB 与 MyISAM 之争

作为 MySQL 数据库的两种主要的存储引擎，InnoDB 与 MyISAM 各有长处。

在 MySQL 5.1 及之前的版本中，MyISAM 是默认的存储引擎，而在 MySQL 5.5 版本以后，默认使用 InnoDB 存储引擎。

MyISAM 不支持行级锁，换句话说，MyISAM 会对整张表加锁，而不是针对行。同时，MyISAM 不支持事务和外键。MyISAM 可被压缩，存储空间较小，而且 MyISAM 在筛选大量数据时非常快。

InnoDB 是事务型引擎，当事务异常提交时，会被回滚。同时，InnoDB 支持行锁。此外，InnoDB 需要更多存储空间，会在内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 支持自动奔溃恢复特性。

InnoDB 与 MyISAM 的主要区别

方面	MyISAM	InnoDB
事务	不支持	支持
外键	不支持	支持
行级锁	不支持	支持
自动奔溃恢复	不支持	支持

对于如何选择 InnoDB 与 MyISAM 存储引擎，我的建议：一般情况下，应该优先选择 InnoDB 存储引擎，并且尽量不要将 InnoDB 与 MyISAM 混合使用。

MySQL（03） 如何设计索引

改善性能最好的方式，就是通过数据库中合理地使用索引，换句话说，索引是提高 MySQL 数据库查询性能的主要手段。在下面的章节中，介绍了索引类型、强制索引、全文索引。

基本索引类型

MySQL 索引可以分为单列索引、复合索引、唯一索引、主键索引等。这里，将为读者介绍这几种索引的特点。

单列索引

单列索引：单列索引是最基本的索引，它没有任何限制。

创建一个单列索引，例如：

1. create index index_name on tbl_name(index_col_name);

同时，也可以通过修改表结构的方式添加索引，例如：

1. alter table tbl_name add index index_name on (index_col_name);

复合索引

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

创建一个复合索引，例如：

1. create index index_name on tbl_name(index_col_name,...);

同时，也可以通过修改表结构的方式添加索引，例如：

1. alter table tbl_name add index index_name on (index_col_name,...);

唯一索引

唯一索引：唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一。

创建一个复合索引，例如：

1. create unique index index_name on tbl_name(index_col_name,...);

同时，也可以通过修改表结构的方式添加索引，例如：

1. alter table tbl_name add unique index index_name on (index_col_name,...);

主键索引

主键索引：主键索引是一种特殊的唯一索引，不允许有空值。此外， CREATE INDEX 不能创建主键索引，需要使用 ALTER TABLE 代替，例如：

1. alter table tbl_name add primary key(index_col_name);

强制索引

有时，因为使用 MySQL 的优化器机制，原本应该使用索引的优化器，反而选择执行全表扫描或者执行的不是预期的索引。此时，可以通过强制索引的方式引导优化器采取正确的执行计划。

使用强制索引，SQL 语句只使用建立在 index_col_name 上的索引，而不使用其它的索引。

1. select * from tbl_name force index (index_col_name) …

切记，不要滥用强制索引，因为 MySQL 的优化器会同时评估 I/O 和 CPU 的成本，一般情况下，可以自动分析选择最合适的索引。

如果优化器成本评估错误，因而没有选择最佳方案，最好的方法应该是将合适的索引修改得更好。

如果某个 SQL 语句使用强制索引，需要在系统迭代开发过程中时时维护强制索引，一方面，需要保证使用的强制索引最优，另外一面，需要保证所使用的强制索引不能被误删，不然将导致 SQL 报错。

因此，如果某个 SQL 语句必须要使用强制索引，建议在团队内部开展严格地评审后才可以使用。

全文索引

在一般情况下，模糊查询都是通过 like 的方式进行查询。但是，对于海量数据，这并不是一个好办法，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情,所以 like 进行模糊匹配性能很差。

这种情况下，需要考虑使用全文搜索的方式进行优化。全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。 FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表。

假设，有一张应用全文索引表。

1. CREATE TABLE IF NOT EXISTS `app_full_text` (
2. `app_id` bigint(20) NOT NULL,
3. `app_name_full_text` text NOT NULL,
4. `introduce_full_text` text NOT NULL
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在需要对应用的名称创建全文索引，可以这么设计。

1. alter table `app_full_text` add fulltext key `app_name_intro` (`app_name_full_text`);

默认 MySQL 不支持中文全文检索，对此，网上的方案很多，例如添加 MySQL 扩展，或者将内容转换成拼音的方式存储在索引表，或者使用 IKAnalyzer 分词库等，其效果都不是非常的理想。使用拼音分词，虽然可以查询到内容，但是如果拼音相同的情况，是非常致命的，而且分词的粒度也是个很可怕的问题。使用 IKAnalyzer 分词库，效果也不是很好。因为业务的需要，命中率也是非常重要的，有的关键字没有进行分词导致查询不到的问题。

我之前的临时解决方案。如下：

• 为中文内容表提供一个全文索引表，存储全文索引分词信息，两张表根据中文内容表的 ID 进行关联。
• 将内容进行分词后，用 base64 编码，保存在全文索引表中。
• 关键的一步，如何分词，分词的命中率问题。很简单，自定义分词库，写一个分词算法将所有的组合进行分词，在内容不多的情况下非常有用。举个例子，“梁桂钊”，可以进行自定义分词：[梁、桂、钊、梁桂、桂钊、梁桂钊]。

事实上，MySQL 全文搜索只是一个临时方案，对于全文搜索场景，更专业的做法是使用全文搜索引擎，例如 ElasticSearch 或 Solr。

MySQL（04） 索引使用的注意事项

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。

不要在列上使用函数和进行运算

不要在列上使用函数，这将导致索引失效而进行全表扫描。

1. select * from news where year(publish_time) < 2017

为了使用索引，防止执行全表扫描，可以进行改造。

1. select * from news where publish_time < '2017-01-01'

还有一个建议，不要在列上进行运算，这也将导致索引失效而进行全表扫描。

1. select * from news where id / 100 = 1

为了使用索引，防止执行全表扫描，可以进行改造。

1. select * from news where id = 1 * 100

尽量避免使用 != 或 not in或 <> 等否定操作符

应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因为这几个操作符都会导致索引失效而进行全表扫描。

尽量避免使用 or 来连接条件

应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描。

1. select * from news where id = 1 or id = 2

多个单列索引并不是最佳选择

MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。

假设，有两个单列索引，分别为 news_year_idx(news_year) 和 news_month_idx(news_month)。现在，有一个场景需要针对资讯的年份和月份进行查询，那么，SQL 语句可以写成：

1. select * from news where news_year = 2017 and news_month = 1

事实上，MySQL 只能使用一个单列索引。为了提高性能，可以使用复合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。

复合索引的最左前缀原则

复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

假设，有一个场景只需要针对资讯的月份进行查询，那么，SQL 语句可以写成：

1. select * from news where news_month = 1

此时，无法使用 news_year_month_idx(news_year, news_month) 索引，因为遵守“最左前缀”原则，在查询条件中没有使用复合索引的第一个字段，索引是不会被使用的。

覆盖索引的好处

如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

范围查询对多列查询的影响

查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。

举个例子，假设有一个场景需要查询本周发布的资讯文章，其中的条件是必须是启用状态，且发布时间在这周内。那么，SQL 语句可以写成：

1. select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

这种情况下，因为范围查询对多列查询的影响，将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。换句话说，news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 。

对于这种情况，我的建议：对于范围查询，务必要注意它带来的副作用，并且尽量少用范围查询，可以通过曲线救国的方式满足业务场景。

例如，上面案例的需求是查询本周发布的资讯文章，因此可以创建一个news_weekth 字段用来存储资讯文章的周信息，使得范围查询变成普通的查询，SQL 可以改写成：

1. select * from news where news_weekth = 1 and enable = 1

然而，并不是所有的范围查询都可以进行改造，对于必须使用范围查询但无法改造的情况，我的建议：不必试图用 SQL 来解决所有问题，可以使用其他数据存储技术控制时间轴，例如 Redis 的 SortedSet 有序集合保存时间，或者通过缓存方式缓存查询结果从而提高性能。

索引不会包含有NULL值的列

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。

因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。

隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。

1. select * from news where date_str = 201701

因此，要谨记隐式转换的危害，时刻注意通过同类型进行比较。

like 语句的索引失效问题

like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情。所以，根据业务需求，考虑使用 ElasticSearch 或 Solr 是个不错的方案。

MySQL（05） 索引的排序优化方案

MySQL可以利用索引来快速地执行 ORDER BY 和 GROUP BY 语句的排序和分组操作。因此，在数据库设计时，设计索引应该尽可能地同时满足查询和排序，使用同一个索引既用于查询，又用于排序。

ORDER BY 的索引优化

对于只存在 ORDER BY 的 SQL 语句，可以在 ORDER BY 的字段上创建索引。

举个例子，可以在 prior 上创建 news_prior_idx(prior) 索引，就可以实现利用索引进行 ORDER BY 优化。

1. select * from news order by prior

WHERE 与 ORDER BY 的索引优化

对于同时存在 WHERE 和 ORDER BY 的 SQL 语句，可以建立一个复合索引进行性能优化。

举个例子，可以创建 news_year_idx(year, prior) 索引，就可以实现利用索引进行 ORDER BY 优化。

1. select * from news where news_year = 2017 order by prior

MySQL（07） 范式与反模式

数据库范式是为解决关系数据库中数据冗余、更新异常、插入异常、删除异常问题而引入的。简单的理解，数据库范式可以避免数据冗余，减少数据库的空间，并且减轻维护数据完整性的麻烦。

第一范式（1NF）

第一范式，强调属性的原子性约束，要求属性具有原子性，不可再分解。

举个例子，活动表（活动编码，活动名称，活动地址），假设这个场景中，活动地址可以细分为国家、省份、城市、市区、位置，那么就没有达到第一范式。

第二范式（2NF）

第二范式，强调记录的唯一性约束，表必须有一个主键，并且没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子，版本表（版本编码，版本名称，产品编码，产品名称），其中主键是（版本编码，产品编码），这个场景中，数据库设计并不符合第二范式，因为产品名称只依赖于产品编码。存在部分依赖。所以，为了使其满足第二范式，可以改造成两个表：版本表（版本编码，产品编码）和产品表（产品编码，产品名称）。

第三范式（3NF）

第三范式，强调属性冗余性的约束，即非主键列必须直接依赖于主键。

举个例子，订单表（订单编码，顾客编码，顾客名称），其中主键是（订单编码），这个场景中，顾客编码、顾客名称都完全依赖于主键，因此符合第二范式，但是顾客名称依赖于顾客编码，从而间接依赖于主键，所以不能满足第三范式。为了使其满足第三范式，可以拆分两个表：订单表（订单编码，顾客编码）和顾客表（顾客编码，顾客名称），拆分后的数据库设计，就可以完全满足第三范式的要求了。

值得注意的是，第二范式的侧重点是非主键列是否完全依赖于主键，还是依赖于主键的一部分。第三范式的侧重点是非主键列是直接依赖于主键，还是直接依赖于非主键列。

反模式

范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。

然而，通过数据库范式化设计，将导致数据库业务涉及的表变多，并且可能需要将涉及的业务表进行多表连接查询，这样将导致性能变差，且不利于分库分表。因此，出于性能优先的考量，可能在数据库的结构中需要使用反模式的设计，即空间换取时间，采取数据冗余的方式避免表之间的关联查询。至于数据一致性问题，因为难以满足数据强一致性，一般情况下，使存储数据尽可能达到用户一致，保证系统经过一段较短的时间的自我恢复和修正，数据最终达到一致。

需要谨慎使用反模式设计数据库。一般情况下，尽可能使用范式化的数据库设计，因为范式化的数据库设计能让产品更加灵活，并且能在数据库层保持数据完整性。

有的时候，提升性能最好的方法是在同一表中保存冗余数据，如果能容许少量的脏数据，创建一张完全独立的汇总表或缓存表是非常好的方法。举个例子，设计一张“下载次数表”来缓存下载次数信息，可使在海量数据的情况下，提高查询总数信息的速度。

另外一个比较典型的场景，出于扩展性考虑，可能会使用 BLOB 和 TEXT 类型的列存储 JSON 结构的数据，这样的好处在于可以在任何时候，将新的属性添加到这个字段中，而不需要更改表结构。但是，这个设计的缺点也比较明显，就是需要获取整个字段内容进行解码来获取指定的属性，并且无法进行索引、排序、聚合等操作。因此，如果需要考虑更加复杂的使用场景，更加建议使用 MongoDB 这样的文档型数据库。

 

MySQL（08） 分库与分表设计

面对海量数据，例如，上千万甚至上亿的数据，查询一次所花费的时间会变长，甚至会造成数据库的单点压力。因此，分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。

分表概述

随着用户数的不断增加，以及数据量的不断增加，会使得单表压力越来越大，面对上千万甚至上亿的数据，查询一次所花费的时间会变长，如果有联合查询的情况下，甚至可能会成为很大的瓶颈。此外，MySQL 存在表锁和行锁，因此更新表数据可能会引起表锁或者行锁，这样也会导致其他操作等待，甚至死锁问题。

通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用，此外，可以很大的缓解表锁的问题。

分表策略可以归纳为垂直拆分和水平拆分。

垂直拆分，把表的字段进行拆分，即一张字段比较多的表拆分为多张表，这样使得行数据变小。一方面，可以减少客户端程序和数据库之间的网络传输的字节数，因为生产环境共享同一个网络带宽，随着并发查询的增多，有可能造成带宽瓶颈从而造成阻塞。另一方面，一个数据块能存放更多的数据，在查询时就会减少 I/O 次数。举个例子，假设用户表中有一个字段是家庭地址，这个字段是可选字段，在数据库操作的时候除了个人信息外，并不需要经常读取或是更改这个字段的值。在这种情况下，更建议把它拆分到另外一个表，从而提高性能。

如何设计好垂直拆分，我的建议：

• 将不常用的字段单独拆分到另外一张扩展表，例如前面讲解到的用户家庭地址，这个字段是可选字段，在数据库操作的时候除了个人信息外，并不需要经常读取或是更改这个字段的值。
• 将大文本的字段单独拆分到另外一张扩展表，例如 BLOB 和 TEXT 字符串类型的字段，以及 TINYBLOB、 MEDIUMBLOB、 LONGBLOB、 TINYTEXT、 MEDIUMTEXT、 LONGTEXT字符串类型。这样可以减少客户端程序和数据库之间的网络传输的字节数。
• 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中。举个例子，假设用户表的设计中，还存在“最后登录时间”字段，每次用户登录时会被更新。这张用户表会存在频繁的更新操作，此外，每次更新时会导致该表的查询缓存被清空。所以，可以把这个字段放到另一个表中，这样查询缓存会增加很多性能。
• 对于需要经常关联查询的字段，建议放在同一张表中。不然在联合查询的情况下，会带来数据库额外压力。

水平拆分，把表的行进行拆分。因为表的行数超过几百万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平拆分，有许多策略，例如，取模分表，时间维度分表，以及自定义 Hash 分表，例如用户 ID 维度分表等。在不同策略分表情况下，根据各自的策略写入与读取。

实际上，垂直拆分后的表依然存在单表数据量过大的问题，需要进行水平拆分。因此，实际情况中，水平拆分往往会和垂直拆分结合使用。假设，随着用户数的不断增加，用户表单表存在上千万的数据，这时可以把一张用户表的数据拆成多张用户表来存放。

常见的水平分表策略归纳起来，可以总结为随机分表和连续分表两种情况。例如，取模分表就属于随机分表，而时间维度分表则属于连续分表。

连续分表可以快速定位到表进行高效查询，大多数情况下，可以有效避免跨表查询。如果想扩展，只需要添加额外的分表就可以了，无需对其他分表的数据进行数据迁移。但是，连续分表有可能存在数据热点的问题，有些表可能会被频繁地查询从而造成较大压力，热数据的表就成为了整个库的瓶颈，而有些表可能存的是历史数据，很少需要被查询到。

随机分表是遵循规则策略进行写入与读取，而不是真正意义上的随机。通常，采用取模分表或者自定义 Hash 分表的方式进行水平拆分。随机分表的数据相对比较均匀，不容易出现热点和并发访问的瓶颈。但是，分表扩展需要迁移旧的数据。此外，随机分表比较容易面临跨表查询的复杂问题。

对于日志场景，可以考虑根据时间维度分表，例如年份维度分表或者月份维度分表，在日志记录表的名字中包含年份和月份的信息，例如 log_2017_01，这样可以在已经没有新增操作的历史表上做频繁地查询操作，而不会影响时间维度分表上新增操作。

对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

对于租户场景，可以考虑租户维度分表，不同的租户数据独立，而不应该在每张表中添加租户 ID，这是一个不错的选择。

分库概述

库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

分库策略也可以归纳为垂直拆分和水平拆分。

垂直拆分，按照业务和功能划分，把数据分别放到不同的数据库中。举个例子，可以划分资讯库、百科库等。

水平拆分，把一张表的数据划分到不同的数据库，两个数据库的表结构一样。实际上，水平分库与水平分表类似，水平拆分有许多策略，例如，取模分库，自定义 Hash 分库等，在不同策略分库情况下，根据各自的策略写入与读取。举个例子，随着业务的增长，资讯库的单表数据过大，此时采取水平拆分策略，根据取模分库。

 MySQL（09） 分库与分表带来的分布式困境与应对之策

随着用户数的不断增加，以及数据量的不断增加，通过分库与分表的方式提高查询性能的同时，带来了一系列分布式困境。

数据迁移与扩容问题

前面介绍到水平分表策略归纳总结为随机分表和连续分表两种情况。连续分表有可能存在数据热点的问题，有些表可能会被频繁地查询从而造成较大压力，热数据的表就成为了整个库的瓶颈，而有些表可能存的是历史数据，很少需要被查询到。连续分表的另外一个好处在于比较容易，不需要考虑迁移旧的数据，只需要添加分表就可以自动扩容。随机分表的数据相对比较均匀，不容易出现热点和并发访问的瓶颈。但是，分表扩展需要迁移旧的数据。
针对于水平分表的设计至关重要，需要评估中短期内业务的增长速度，对当前的数据量进行容量规划，综合成本因素，推算出大概需要多少分片。对于数据迁移的问题，一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。

表关联问题

在单库单表的情况下，联合查询是非常容易的。但是，随着分库与分表的演变，联合查询就遇到跨库关联和跨表关系问题。在设计之初就应该尽量避免联合查询，可以通过程序中进行拼装，或者通过反范式化设计进行规避。

分页与排序问题

一般情况下，列表分页时需要按照指定字段进行排序。在单库单表的情况下，分页和排序也是非常容易的。但是，随着分库与分表的演变，也会遇到跨库排序和跨表排序问题。为了最终结果的准确性，需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户。

分布式事务问题

随着分库与分表的演变，一定会遇到分布式事务问题，那么如何保证数据的一致性就成为一个必须面对的问题。目前，分布式事务并没有很好的解决方案，难以满足数据强一致性，一般情况下，使存储数据尽可能达到用户一致，保证系统经过一段较短的时间的自我恢复和修正，数据最终达到一致。

分布式全局唯一ID

在单库单表的情况下，直接使用数据库自增特性来生成主键ID，这样确实比较简单。在分库分表的环境中，数据分布在不同的分表上，不能再借助数据库自增长特性。需要使用全局唯一 ID，例如 UUID、GUID等。关于如何选择合适的全局唯一 ID，我会在后面的章节中进行介绍。

总结

分库与分表主要用于应对当前互联网常见的两个场景：海量数据和高并发。然而，分库与分表是一把双刃剑，虽然很好的应对海量数据和高并发对数据库的冲击和压力，但是却提高的系统的复杂度和维护成本。

因此，我的建议：需要结合实际需求，不宜过度设计，在项目一开始不采用分库与分表设计，而是随着业务的增长，在无法继续优化的情况下，再考虑分库与分表提高系统的性能。