业务字段变更引发cpu 100%？

呆呆的私房菜 2025-04-03

126

Whoami：6年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天轮、公众号（呆呆的私房菜）

最近接到的一个case，开发凌晨做表结构变更，第二天早上cpu直接告警100%。排查了sql order by top cpu后，定位到是昨晚开发同事变更的业务表，执行了删字段操作。而这张表上原有的复合索引也自动删除了，导致今天业务瘫痪的结果。

是的，删除字段是会自动删除其依赖索引的。下面，基于PostgreSQL和Oracle数据做一下测试。

PostgreSQL测试

1. 创建测试表：

test=# create table t1(id int, name varchar(100));
CREATE TABLE


test=# insert into t1 select generate_series(1,100000), md5(random()::varchar);
INSERT 0 100000


test=# create index idx_id_name on t1 (id, name);
CREATE INDEX

2. 收集统计信息，并查看业务sql执行计划：

test=# analyze t1;
ANALYZE
test=# explain(analyze, buffers) select id,name from t1 where id = 30000;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_id_name on t1  (cost=0.42..8.44 rows=1 width=37) (actual time=0.026..0.027 rows=1 loops=1)
   Index Cond: (id = 30000)
   Heap Fetches: 1
   Buffers: shared hit=1 read=3
 Planning Time: 0.199 ms
 Execution Time: 0.062 ms
(6 行记录)

执行删除字段操作：

test=# alter table t1 drop column name;
ALTER TABLE

查看sql执行计划

test=# explain(analyze, buffers) select id from t1 where id = 30000;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..2084.00 rows=1 width=4) (actual time=1.300..4.077 rows=1 loops=1)
   Filter: (id = 30000)
   Rows Removed by Filter: 99999
   Buffers: shared hit=834
 Planning Time: 0.093 ms
 Execution Time: 4.092 ms
(6 行记录)

结论：执行变化走成了全表扫描。

我们查看一下此时表上的索引情况，发现此时复合索引已经被自动删除了。

test=# SELECT
test-#     t.relname AS table_name,
test-#     i.relname AS index_name,
test-#     array_to_string(array_agg(a.attname), ', ') AS column_names
test-# FROM
test-#     pg_class t,
test-#     pg_class i,
test-#     pg_index ix,
test-#     pg_attribute a
test-# WHERE
test-#     t.oid = ix.indrelid
test-#     AND i.oid = ix.indexrelid
test-#     AND a.attrelid = t.oid
test-#     AND a.attnum = ANY(ix.indkey)
test-#     AND t.relkind = 'r'
test-#     AND t.relname = 't1'  -- 替换为你的表名
test-# GROUP BY
test-#     t.relname,
test-#     i.relname
test-# ORDER BY
test-#     t.relname,
test-#     i.relname;
 table_name | index_name | column_names 
------------+------------+--------------
(0 行记录)
## 结论：删除表上的某个字段时，无论该字段有单列索引还是复合索引，都会被自动删除掉。如果业务做完变更之后，出现某个模块慢的问题，一定要关注该类情况。
## 预防措施：删除某张表字段之前，应该关注查看表上的字段索引情况

因此，在删除表字段的时候，一定要关注表上的索引、统计信息以及约束情况。

## 可以用如下SQL生成索引创建语句
test=# SELECT
test-#     n.nspname AS schema_name,
test-#     t.relname AS table_name,
test-#     i.relname AS index_name,
test-#     array_to_string(array_agg(a.attname), ', ') AS column_names,
test-#     pg_get_indexdef(i.oid) AS index_definition
test-# FROM
test-#     pg_class t,
test-#     pg_class i,
test-#     pg_namespace n,
test-#     pg_index ix,
test-#     pg_attribute a
test-# WHERE
test-#     t.oid = ix.indrelid
test-#     AND i.oid = ix.indexrelid
test-#     AND n.oid = t.relnamespace
test-#     AND a.attrelid = t.oid
test-#     AND a.attnum = ANY(ix.indkey)
test-#     AND t.relkind = 'r'
test-#     AND i.relkind = 'i'
test-#     AND t.relname = 't2'  -- 替换为你的表名
test-# GROUP BY
test-#     i.relname,
test-#     n.nspname,
test-#     t.relname,
test-#     pg_get_indexdef(i.oid)
test-# ORDER BY
test-#     i.relname;
 schema_name | table_name | index_name  | column_names |                       index_definition                       
-------------+------------+-------------+--------------+--------------------------------------------------------------
 public      | t2         | idx_id_name | id, name     | CREATE INDEX idx_id_name ON public.t2 USING btree (id, name)
(1 行记录)


## pg_indexes视图也可以获取索引的定义语句
test=# select * from pg_indexes where tablename = 't2' --and indexdef like '%name%';
  indexname  |                           indexdef                           
-------------+--------------------------------------------------------------
 idx_id_name | CREATE INDEX idx_id_name ON public.t1 USING btree (id, name)
(1 行记录)

Oracle测试

1. 创建测试表：

SQL> CREATE TABLE t1 (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100)
);  2    3    4  


Table created.


SQL> INSERT INTO t1 (id, name)
SELECT LEVEL, DBMS_RANDOM.STRING('U', 32)
FROM dual
CONNECT BY LEVEL <= 100000;   2    3    4  
select index_name from user_indexes;


100000 rows created.


SQL> create index idx_id_name on t1 (id, name);


Index created.


SQL> col owner for a10;
SQL> col table_name for a20
SQL> col index_name for a20;
SQL> select owner, table_name, index_name, blevel, status from dba_indexes where owner = 'CHEN' and table_name = 'T1';
OWNER	   TABLE_NAME		INDEX_NAME		 BLEVEL STATUS
---------- -------------------- -------------------- ---------- ------------------------
CHEN	   T1			SYS_C008967			VALID
CHEN	   T1			IDX_NAME12		      2 VALID

2. 收集统计信息，并查看业务sql执行计划：

SQL> exec dbms_stats.gather_table_stats('CHEN','T1',estimate_percent=>10,method_opt=>'for columns size 1',degree=>16,cascade=>true,force=>true);


PL/SQL procedure successfully completed.


SQL> select name1, name2 from t1 where name2 = 'NQMNIGOQDCGUDAHFQEWPTIDPRKCSZUOD';




Execution Plan
----------------------------------------------------------
Plan hash value: 4281484941


-----------------------------------------------------------------------------------
| Id  | Operation	     | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		  |    12 |  1248 |   302   (1)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_NAME12 |    12 |  1248 |   302   (1)| 00:00:01 |
-----------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("NAME2"='NQMNIGOQDCGUDAHFQEWPTIDPRKCSZUOD')


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




Statistics
----------------------------------------------------------
	  7  recursive calls
	  0  db block gets
       1177  consistent gets
       1078  physical reads
	  0  redo size
	669  bytes sent via SQL*Net to client
	608  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

执行删除字段操作：

SQL> alter table t1 drop column name;


Table altered.

查看sql执行计划

SQL> select name2 from t1 where name2 = 'NQMNIGOQDCGUDAHFQEWPTIDPRKCSZUOD';




Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |    12 |   624 |   308   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1	 |    12 |   624 |   308   (1)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("NAME2"='NQMNIGOQDCGUDAHFQEWPTIDPRKCSZUOD')


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




Statistics
----------------------------------------------------------
	 68  recursive calls
	  0  db block gets
       1216  consistent gets
	  0  physical reads
	  0  redo size
	569  bytes sent via SQL*Net to client
	608  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	 10  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

结论：执行变化走成了全表扫描。

我们查看一下此时表上的索引情况，发现此时复合索引已经被自动删除了。

SQL> col owner for a10;
SQL> col table_name for a20
SQL> col index_name for a20;
SQL> select owner, table_name, index_name, blevel, status from dba_indexes where owner = 'CHEN' and table_name = 'T1';SQL> SQL> SQL> 


OWNER	   TABLE_NAME		INDEX_NAME		 BLEVEL STATUS
---------- -------------------- -------------------- ---------- ------------------------
CHEN	   T1			SYS_C008965			VALID

本文内容就到这啦！希望本篇内容能给你带来帮助。我们下篇再见！

点击上方公众号，关注我吧！

索引 test oracle索引 mysql创建索引

文章转载自呆呆的私房菜，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

业务字段变更引发cpu 100%？

评论