数据库使用七宗罪

前言

关于数据库的使用规范和各种教程、原理网上的资料都十分丰富。同时，各位同学在工作中肯定也积累了十分丰富的经验，那为什么还要做这次分享？

一方面，是希望能够将有关数据库方面的一些经常遇到的问题做个归纳和总结，帮助开发同学在遇到问题时能够快速的定位问题并及时解决。

另一方面，也希望各位开发同学能够积极补充和分享自己对于数据库知识的理解，做到1+1>2的效果。

因此，本次分享着重在一些数据库的基础原理原理和操作上，希望大家可以批评指正。

索引

1.1 索引分类

索引的类型有很多，按照功能上主要可以分为普通索引、唯一索引、主键索引和组合索引。

普通索引：最基本的索引，它没有任何限制。主要应用在查询条件（where xxxx）、排序条件（order by xxxx）、聚合条件（group by xxxx）、连接条件（join xxxx）中。

**唯一索引：**与普通索引类似，但普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一（允许有空值）。如果是组合索引，则列值的组合必须唯一。

**主键索引：**它是一种特殊的唯一索引，不允许有空值，一个表只能有一个主键索引。

**组合索引：**将多个字段组合成一个索引，在使用索引时需要满足‘最左前缀’原则。例如：index (a,b,c)。 可以支持a、a,b、 a,b,c3种组合进行查询，但不支持 b,c进行查询。

全文索引：要能在MyISAM引擎上才能使用，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。但是由于版本、性能、存储引擎的限制，一般不怎么用全文索引，而是通过其他解决方案如Lucene、ES解决。www.cnblogs.com/wxzhe/p/995…

空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。具体可参考这篇文章 blog.csdn.net/MoFengLian/…

1.2 如何建合适的索引

标准1 Key的区分度高--**索引的选择选择性 = 基数 / 总行数

索引的选择性，指的是不重复的索引值（基数）和表记录数的比值。选择性是索引筛选能力的一个指标。索引的取值范围是 0—1 ，当选择性越大，索引价值也就越大。

这里很好理解，比如一个有100w条数据的user表，每个user记录中的sex字段为男/女，很显然，在sex字段上增加索引的意义不大，因为该索引后仍然还有50w条数据需要遍历，而此时的索引选择为2/100w，太小了。

标准2 Key尽量小--前缀索引

前缀索引是用列的一部分字符去建立索引，更加节省空间（因为只使用一部分字符，索引建立时长度会降低），选择合适的话，效率也会更高。

举个例子：

比如这里我需要对change_log这张表的content字段加索引，content字段保存的是用户对发起的sql记录。由于content字段是text类型的，mysql对文本类型增加索引时，必须指定其长度，所以这里我们根据上面提到的索引的选择选择性来确定加索引的长度。

索引长度 = 50

索引长度 = 150

索引长度 = 249

最终我们可以看到，索引的选择选择性最终稳定在0.86这样，再增加长度上升空间也不够大，所以此处确定长度就为249。

当然我们在创建索引时，mysql本身也给我们做了限制，当我们为太多的字段创建索引时，可能会出现以下报错：

此时我们是不是该反思下自己呢？

标准3 最左前缀原则

前面提到了mysql在建立组合索引后，查询的条件需要满足最左前缀原则，才能充分的使用该组合索引。

举个例子：

建一整jidao表，包含abcd四个字段，且索引idx_abcd（a,b,c,d）

此时我们知道，idx_abcd会命中（a),(a,b),(a,b,c),(a,b,c,d)这样查询条件。对以下sql执行explain。

发现两个sql都走了索引，效率相同。

这个key_len计算过程：3068 = (255 *( character set：utf8=3,gbk=2,latin1=1)+2) * 4(变长字段)

这个结果看似是违背了mysq的最左前缀原则，实际上只是mysql的优化器帮我们优化成索引可以识别的形式。对于最左前缀原则，=和in是可以乱序的。

再对以下sql执行explain查看一下

发现该sql仍然走了idx_abcd索引，但key_len却比上面的3068小，key_len不同这就说明该组合索引并没有完全利用上，本例子中只使用了idx_abcd中的a、b两个字段。

这个key_len计算过程：1534 = (255 *( character set：utf8=3,gbk=2,latin1=1)+2) * 2(变长字段)

关于key_len的计算，大家可以参考这篇博客写的比较清楚了www.cnblogs.com/gomysql/p/4…。

对于最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

这里提一句，当我们执行以下sql时，则会走完全idx_abcd完全索引。这是因为对于like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

标准4 索引不是越多越好

过多的索引可能会带来以下问题：

比如对于行运的DB，执行以下sql获取当前表占用的空间：

SELECT
    table_name AS `Table`,
    round((data_length / 1024 / 1024), 2) `data Size(MB)`,
    round((index_length / 1024 / 1024), 2) `index Size(MB)`
FROM
    information_schema.tables
WHERE
    table_schema = 'industry_open_extension'
ORDER BY
    (data_length + index_length) DESC;

当前表的数据只用81M,但是索引文件的大小已经达到70M，和数据文件本身的大小几乎相当，可删除一些冗余的索引。information_schema.tables的值是mysql 针对innodb存储引擎的采样值，不可尽信，当然也不可不信。

SQL优化

2.1 隐式转换：

在MySQL中，当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。则会发生转换隐式

举个例子,对于如下的表结构

在执行一个简单的select后，我们会发现并没有走索引，

这是因为tb1表中domain_id的定义是varchar（11）而不是int.

更改完成后，成功使用索引。隐式转换的出现常常也出现在字符集或者字符的默认排序规则设置有误的情况下。但是有些情况下，却是可以的：比如索引建在int上，查询条件使用where条件，也是可以走索引的。比如：

匹配到了user_id这个索引。以下是 MySQL 中的一些隐式转换规则：

总之，在 MySQL 中，隐式转换可能会导致意想不到的结果，因此应该尽可能使用显式转换来确保正确处理数据类型。

2.2 建表时尽量创建符合第三范式的表

第三范式（Third Normal Form，3NF）是关系型数据库设计中的一种规范化形式，它要求一个数据库表中的每个字段都只与主键或候选键有直接关系，而不与其他字段有冗余关系。这样可以避免数据重复和不一致，提高数据的完整性和查询效率。

举一个实际的例子，假设有一个商品订单表，其中包括订单信息、客户信息、商品信息等，如下所示：

-- 订单详情表，冗余了用户、商品、订单信息
CREATE TABLE order_detail (
  order_detail_id INT PRIMARY KEY,
  user_name VARCHAR(50),
  user_email VARCHAR(50),
  product_name VARCHAR(50),
  product_price DECIMAL(10,2),
  quantity INT,
  price DECIMAL(10,2)
);

很显然，这张表包含了中冗余了太多信息，按照第三范式可以优化成如下3张表

-- 用户表，存储用户信息
CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 商品表，存储商品信息
CREATE TABLE `product` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 订单表，存储订单信息
CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `order_date` date NOT NULL,
  `status` varchar(255) NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 订单明细表，存储订单中每个商品的详细信息
CREATE TABLE `order_detail` (
  `order_detail_id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `product_id` int NOT NULL,
  `quantity` int NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`order_detail_id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  KEY `idx_price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=1948 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

但是为啥上面会说尽量呢，因为当表拆分之后，我们每次查询需要在多张表之间进行链接，这带来程序的复杂度、可维护性、以及性能的问题。

Anyway！！！在工程中平衡数据库第三范式带来的开发和维护成本，需要根据实际情况进行综合考虑，并采取适当的措施来优化开发流程和降低数据库的维护成本。

2.3 合理使用Join

关于几个join的几个区别，这就不想赘述了，大家应该都能背一遍了，这里就把上面那个一个例子拿出来，一起探讨下，

如果我想直接搜索某个价格范围内的订单详情，使用以下sql，三表join一下，一切看起来都很美好：

但是此时我的要求变更了，需要查询某个用户的某个价格范围内的订单详情，还是使用上面三表的sql，但是where条件中增加一个筛选条件，那么事情就变得复杂了：

这里发现虽然对od表有单独建price索引，但是mysql 优化器在选择时，会首先使用idx_phone对user表的phone进行查询，再将结果集中的user_id作为链接条件查询order表，获取order表中的订单id，再根据订单id对od表查询，因此，od表只会走order_id索引。

这个索引使用情况不一定，为啥呢？因为mysql优化器会根据当前数据量的使用情况确认使用哪条索引的查询效率最高。因此，当我们严格使用第三范式建表后，对于join的使用需要尽量慎重，因为当前sql执行没问题，随着生产环境数据量的增大，很有可能变成慢sql，影响业务。

（我这里盲猜一波，mysql优化器在选择索引时，会参考information_schema.tables表中的数据量，而这个数据量在前文中提到，只是一个采样值，会因为数据空洞，单条记录大小差距很大造成采样值和实际值不一样，因此会出现迭代器出现负优化的情况。这时候可能有些卧龙会让你用force index，看似聪明，但是你must say no。force index可能会出现非常多的问题，且之后的维护也会很困难，生产环境不要使用。）

举这个例子其实还是想说，join用的时候要慎重，一是join会有临时表的操作，对数据库的内存、cpu、io都是有一定的挑战，二是join会因为线上数据量的变化，而造成数据库索引选择的不确定，会有一些难以预想的问题。

CPU、IOPS、Conn、Mem 100%问题的出现及应对

3.1 确认因果

这个问题很大，原因也很多。虽然看似是不同指标升高，但实际上会发现这些基本上都是伴随着一起出现，cpu上去的同时你的链接使用率和iops很难独善其身。

但是遇到问题不同慌，如果我们真的遇到这些报警了，第一步要确认什么是因什么是果。

举例说明，cpu一旦100%，后面对数据库的所有请求都会被pending住，吞吐量急剧下降，这时候的sql执行时间都会被严重拉长，我们在DB上执行‘show processlist’后会发现一堆Query、sleep的请求。但是这些sql都是正常的，正常情况下并不慢。此时cpu 100%是因，慢sql是果。

反之，一个慢sql也可以把cpu占满，这时候慢sql是因，而cpu是果。

3.2 排查思路

因此遇到这些100%问题，我们先不用着急去看代码找慢sql，大体上可按照以下流程去排查：

3.2.1 查看当前数据库负载

这个有可能是造成生产负载最直接原因。基本上各个云厂商的DMS控制台、OpenAPI都会暴露当前DB的出入流量情况，QPS、TPS作为直接的负载指标。

3.2.2 判断当前有无慢sql

如果幸运的话

慢sql的量不大且执行时间也不算太长的话，影响的sql并不会太多，你可以在DB控制台找到对应时间，进行慢sql分析，这个分析会给你展示慢sql的时间、finger、扫描行数、执行时间等数据，一般情况下，我们看扫描行数进一步判断慢日志的Finger是哪一个。

如果有点不幸的话

你的慢sql太慢了，把所有的sql都拖慢了，那么下面这个列表里，你会看到密密麻麻的一片。这时候，就要根据最大耗时且综合业务一起判断下你的sql哪一个比较慢了。实际上，ddl（加索引、加字段、改字段类型）、多表join、大表查询、sql套娃这些是常见的几个罪魁祸首，可重点关注。这里可以注意下面这种sql finger，表面看起来挺老实，一肚子鬼水。

select * from xx where xx in (?)

因为业务上的数据千奇百怪，当in后面的数据非常非常非常大，数量级在K级时，mysql很有可能自作聪明的给你来一波优化--直接全表扫，这种情况，你代码上线前explain都发现不了。

如果更不幸

你的数据库是自建 or 私有化部署的，没有监控。。。。不幸中的万幸，mysql给你自带了。

-- 是否启用慢查询日志
slow_query_log = 1

-- 慢查询日志文件的路径
slow_query_log_file = /var/log/mysql/slow.log

-- 超过多少秒的查询会被记录到慢查询日志中
long_query_time = 1

举个例子（核心数据已经脱敏，仅做示例）：

这个示例中包含了三条慢查询记录，每条记录都以 # 开头，后面跟着查询的相关信息和统计数据。其中，比较重要的信息包括：

Time：查询执行的时间戳。
User@Host：发起查询的用户和客户端 IP 地址。
Query_time：查询的执行时间，以秒为单位。
Lock_time：查询等待锁定的时间，以秒为单位。
Rows_sent：查询返回的行数。
Rows_examined：查询扫描的行数。

在上面的示例中，第一条记录是一个查询 orders 表的语句，执行时间为 3 秒，扫描了 100000 行数据，但没有返回任何结果；第二条记录是一个查询 customers 表的语句，执行时间为 1.5 秒，返回了 10 行数据，并扫描了 10000 行数据；第三条记录是一个更新 products 表的语句，执行时间为 10.2 秒，修改了 1000000 行数据

史诗级不幸

mysql自带的慢日志你也没开，你也没启用。先别慌，用root账号登陆sql执行‘show processlist’，会展示所有在执行中的sql，人工撸一遍。如果比这还不幸，当前sql太多了，无法快速找到有问题的sql，去业务上看看业务日志吧。数据库层面除了binlog应该真的找不到了。

3.2.3 判断有无锁竞争

如果数据库里读写竞争很激烈，有可能出现死锁情况，也会把数据库cpu干翻，可以去日志中排查是否有‘Lock wait timeout exceeded; try restarting transaction’报错。

举个例子：

执行sessionA的update时，会等待对方的锁释放，但是SessionB还没有commit，他还有在后面更新id = 1的行，从而互相等待，永远无法commit。造成死锁。这种情况一旦很激烈，数据库的cpu肯定会上升。

当然如果你的数据库配置了慢日志kill，那你是搜不到这样的日志的，因为死锁一旦发生，要么被mysql自己的sql超时给中断，要不就是被数据库的killer主动中断，查看是否有killer记录，可以去日志中看是否有‘Lost connection to server during query’ 或者‘xxxxx was interrupt’日志。

3.2.4 是否机器配置不够

如果以上情况都不是，则有可能是机器本身的问题。比如你的机器是cpu、磁盘、内存的，虚拟化时，物理资源仍然会有部分是共用，别人的锅你背；也有可能是业务发展很快，流量慢慢变大扛不住了；或者物理机本身问题。总之这一部分需要联系运维协助解决了。

3.4 应对

• 如果是qps的问题：及时的升配、扩容

• 如果是慢sql的问题：

1.配置慢sql对应的killer，超时时间一般设置1秒

2.对于自建库，可先执行‘show processlist’获取当前正在执行的sql，然后执行‘kill + id’手动中断（需要root权限）.

• 机器问题：联系运维 or 云厂商

Disk 100%

4.1 数据空洞

单独把磁盘拿出来讲，是一般情况下生产环境，运行时的状态并不会对磁盘有太大的影响。出现这个问题基本上就是业务迭代造成的数据量越来越大了。但是这里想要单独讲一下‘数据空洞’问题。

所谓数据空洞，是指在表的数据文件中，已经被删除的行所占据的磁盘空间没有被及时回收，而是留下了一些空白区域。这些空白区域虽然不包含实际数据，但仍然占据了硬盘空间，并且可能导致查询性能下降和存储空间浪费。

为了解决数据空洞问题，MySQL 提供了一种叫做 OPTIMIZE TABLE 的命令来进行表的优化。该命令会执行以下操作：

以下很重要！

以下很重要！

以下很重要！

4.2 应对方案

4.2.1 止血方案

如果你真的收到过这个报警，那说明你这家公司的运维太不给力了，磁盘100%才通知你，去联系运维或者云服务商扩容下吧。

这时候千万不要想着我去删一些不要的数据，一方面是时间紧急很可能删错出现更多的问题，另一方面是可能是此时的DB已经被挂起，无法执行任何sql，更有可能你认为的物理删除，根本不会清理磁盘的数据。

4.2.2 长期方案

• 分库分表，针对单库进行横向拆分。将磁盘压力分摊到多个DB上。

• 关注报警，及时扩容

• 联系运维，业务低峰期，执行 OPTIMIZE TABLE。当然需要先对数据库HA，依次对从库进行优化。总功能需要HA两次，且HA期间，数据库会触发一次所有请求的中断。

4.3 mysql的物理删除

我们常见的物理删除sql，实际上也不一定真的是立即物理删除了，可参考下面的表：

如何进行大表变更

大表变更很难，线上一个10亿+的订单表，直接‘ALTER TABLE xxx ADD COLUMN xxxx’很容易就会造成锁表直接gg，而且这种ddl语句的执行时间一般都巨长，很难搞，一般的。有以下的几个方案可参考：

配置预留字段

在建表时，使用预留字段，并将该字段类型配置成varchar，这样后面数据量上来后，只需要修改表名就相当于增加了一个字段。而修改表名只会修改mysql的infomationschma这个系统表，对元数据的修改非常快。不会影响线上业务。注意：这里不要修改字段类型，否则还是会有影响。

优点：不会影响线上数据库运行。

缺点：代码中要兼容sql的字段变更。或者在变更前，保证原先的字段不存在任何的sql中，或者没有关联任何orm生成的DO对象。尤其使用hibernate、gorm感觉基本上这套行不通。

5.2 在线DDL

pt-online-schema-change --alter "ADD COLUMN xxx VARCHAR(50)" D=orders,t=orders_copy

其中 orders_copy 是一个与 orders 表结构相同的临时表，该工具会在后台自动创建并维护这个临时表，同时使用 triggers 和 locks 来保证数据的一致性和安全性。

有兴趣的同学可以前往pt-online-schema-change官网，私底下联系我一起探讨也可～

5.3 云厂商支持

现在阿里云RDS、腾讯云CDS应该都已经支持了，火山云、AWS应该也有类似的解决方案。

数据库Killer

6.1 Killer是什么

killer本身不是mysql自带的服务，而是依赖mysql系统命令独立部署的一个外部服务，killer依赖定时任务按照一定要求对业务数据库进行session级别的熔断。killer本身作为守护线上数据库的独立服务，往往可以在关键时刻保证线上数据库的稳定运行，避免慢sql在线上泛滥。

但是在某些情况下，killer也不是万能的。比如killer的检测行为往往是定时任务触发的，定时任务的检测时间间隔（一般为0.5秒-2秒），并不能100%保证线上不存在慢sql，甚至相反，killer在数据库整体hang住的情况下，甚至会加重这种情况，而且kill掉线上服务的会话，本身也是非常危险的，需要慎重对待。

6.2 killer依赖的系统命令

SHOW PROCESSLIST 是一个用于查询 MySQL 实例中所有进程列表的 SQL 命令，它可以展示每个进程的 ID、用户、状态、运行时间等信息。返回的结果集包含以下列：

连接数：指 MySQL 数据库服务器当前打开的总连接数，包括活跃连接和非活跃连接（已经建立但处于空闲状态的连接）。通常使用 max_connections 参数来限制最大连接数。当达到最大连接数时，新连接请求将被拒绝。
活跃连接数：指当前正在使用的连接数，也就是正在执行 SQL 查询或事务处理的连接数量。对于高并发负载下的 MySQL 服务器，该值往往是需要关注的一个重要性能指标。可以使用 Threads_connected 状态变量查询当前活跃连接数量。

6.3 主要流程

参数优化

mysql的参数有很多，至少在200+。我这边整理了一些个人觉得比较重要的，我们在没问题的情况下尽可能不要去动这些，但是知道总归是好的。

尾声

关于数据库的内容还有很多，本文限于篇幅和时间，只归纳了我认为比较重要的几个点，未来还会继续补充本文。计划补充以下三个话题。（这下真的可以凑足十宗罪了，水文也算水的完整了）