Why Index Operations Can Crash Your Database: MySQL & TiDB Experiments and Lessons

Background and Fault Report

The author’s core computation system stalled because an index‑optimisation operation on a huge MySQL table caused the database to become unresponsive. Deleting and recreating indexes triggered slow queries, connection‑pool exhaustion, and the error Communications link failure, leading to a two‑hour service outage.

MySQL Data Snapshot

A test copy of the >100 million‑row table was created in a test database. The SHOW INDEX FROM table_name; command revealed the existing indexes. Sample screenshots (omitted) showed index creation time, query execution times with and without index coverage, and the negligible cost of dropping an index.

Lock and Transaction Inspection

Running processes:

SELECT * FROM information_schema.PROCESSLIST WHERE LENGTH(info) > 0;

Open tables: SHOW OPEN TABLES WHERE In_use > 0; InnoDB locks: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; Lock waits: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; Active transactions: SELECT * FROM information_schema.INNODB_TRX; Kill a transaction: KILL (trx_mysql_thread_id); The log excerpt showed a transaction TRANSACTION 93275186, ACTIVE 155 sec fetching rows with over 770 k lock structs, 102 M row locks, and a large undo log, updating od_no_order_product_storage_plan_detail_copy2_copy1 where type='通讯'.

Index vs. Non‑Index Updates

Experiments demonstrated that when an indexed UPDATE runs before a non‑indexed UPDATE, the latter can be blocked because the two update sets intersect. Two non‑indexed UPDATEs on the same table caused a lock timeout on the second statement.

Implicit Type Conversion Causing Index Failure

When a VARCHAR(200) column was indexed, MySQL performed an implicit conversion during a query, causing the index to be ignored and a full‑table scan to occur. Screenshots illustrated three cases: index ignored, zero rows returned but cost estimated, and normal index usage with a filtered percentage.

DDL Impact on DML and Indexes

Create Index → Update → Select : No impact; all three statements ran in parallel.

Update → Drop Index → Select : The DROP INDEX statement was blocked by a subsequent SELECT, even though it started earlier.

Delete → Drop Index → Select : Delete did not block the SELECT, but the DROP INDEX still got blocked by the later SELECT.

These patterns highlighted that dropping an index locks the table and can block queries that would use the index.

TiDB vs. MySQL Comparison

TiDB imported the same data (12.2 GB) via DataX. Despite larger size, TiDB’s distributed architecture gave different performance characteristics. MySQL ran on a 16 GB machine with an 8 GB buffer pool; TiDB’s configuration was more aggressive, making direct performance comparison unfair.

TiDB Index Operations

TiDB allows tuning of DDL reorganization workers and batch size via SET @@global.tidb_ddl_reorg_worker_cnt and SET @@global.tidb_ddl_reorg_batch_size. Adjusting these parameters can speed up index creation when the indexed column is heavily written, but may increase write conflicts.

TiDB Experiment Findings

Running UPDATE → DROP INDEX → SELECT on TiDB produced different results from MySQL: after dropping the index, the SELECT performed a full‑table scan without being blocked. The TiDB dashboard showed that the UPDATE executed first, the DROP INDEX completed without locking, and the SELECT finished quickly.

Conclusions

DML statements should target indexed columns to limit the updated data set and avoid full‑table scans or lock contention.

Adding an index does not lock the table, but dropping an index does and can block concurrent queries that rely on that index.

Altering table structure locks the whole table, affecting DML but not read‑only queries.

Post‑mortem Recommendations

Avoid deleting indexes unless absolutely necessary; adding indexes is generally safe.

Never modify table or column definitions in production without a maintenance window.

Ensure DML statements use appropriate indexes; overlapping update sets cause lock contention.

These lessons were derived from the real‑world incident and the systematic experiments described above.