MYSQL 查询select、更新update导致CPU占用高

 

线上的某个服务保存操作很慢，备份服务器连备份数据库很快，但备份服务器连接该数据库也很慢，可以确定是数据库的问题。

通过监控发现，一执行 SQL 语句 CPU 占用就飙升至 100%，再看慢查询日志，都是很简单 SQL，也很慢。

 

原因是：查询的表没有设置主键和索引，而表中又有 blob 字段。该字段随着服务的使用存储了越来越多的数据，导致查询慢。

 

为什么 blob 字段在没有主键以及索引的情况下会慢呢？

 

实际上，根本原因是因为 MySQL 是行数据库，而主键、索引只是一种优化。

举个栗子，现在有一个 10W 行的 table，有3个字段：path(varchar)、content(blob)、lastmodified(timestamp)，3个都不是主键、索引，也都不符合非空唯一的条件[1]。

执行 select * from table where path = 'a/b/c/d'; MySQL必须把10W行的 path 都取出来（path不唯一的话）一一判断。

由于是行数据库，每一行的 content 和 lastmodified 也会被取出来，即使没有用到。

索引的存在可以优化这个问题， B+ 树的叶子节点存储真实数据，非叶子节点存储索引数据。当 SQL 语句用到了索引时，从 B+ 树的根结点（实际上也可从叶节点开始）进行遍历，满足索引条件后，再去拿出真实数据判断非索引列的条件，最终得出结果。这也是为什么在 WHERE 后要先写索引列再写非索引列的原因。

 

解决办法：

按照上面的说法，

根本解决办法是：新建一个表存储 blob 字段，通过一个唯一字段与原表关联。

优化的办法是：建立主键或索引，并在查询时尽量使用到。

 

[1]对于没有设置主键的表，MySQL会寻找第一个非空唯一列默认作为主键，如果没有符合条件的列（我就是这种情况），会自动增加一列6字节的隐藏列作为自增主键。参考：

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.