21 Essential SQL Habits to Boost Performance and Avoid Mistakes

SQL Regret Medicine

Practical safeguards to avoid costly mistakes when modifying data.

Add LIMIT to DELETE or UPDATE statements. Limits the number of rows affected, reduces the risk of accidental full‑table deletions, allows the engine to stop scanning early, and prevents long‑running transactions. DELETE FROM euser WHERE age > 30 LIMIT 200; Test changes in a non‑production environment and document a rollback plan before production review.

Back up data before modifying or deleting important rows.

Verify the WHERE clause with a SELECT before executing DELETE or UPDATE .

Wrap data‑modifying statements in explicit transactions. Example of a correct transaction:

BEGIN;
UPDATE account SET balance = 1000000 WHERE name = '捡田螺的小男孩';
COMMIT;

Missing transaction (dangerous):

UPDATE account SET balance = 1000000 WHERE name = '捡田螺的小男孩';

SQL Performance Optimization

Techniques to ensure queries run efficiently.

Run EXPLAIN after writing a query. Inspect the execution plan to confirm index usage.

EXPLAIN SELECT userid, name, age FROM user WHERE userid = 10086 OR age = 18;

Index columns used in WHERE , ORDER BY , GROUP BY . Prefer composite indexes when multiple columns are filtered or sorted.

ALTER TABLE user ADD INDEX idx_address_age (address, age);

Query without an appropriate index:

SELECT * FROM user WHERE address = '深圳' ORDER BY age;

Avoid implicit type conversion in WHERE clauses. Compare values using the column’s native type.

SELECT * FROM user WHERE userid = '123';   -- correct (userid is VARCHAR)

Incorrect conversion that disables the index:

SELECT * FROM user WHERE userid = 123;   -- numeric literal forces conversion

Return only required columns instead of SELECT * . Reduces I/O, network traffic and can enable covering indexes.

SELECT id, name FROM employee;   -- correct

SELECT * FROM employee;   -- avoid

Prefer VARCHAR over CHAR for variable‑length data. Saves storage space.

`deptName` VARCHAR(100) DEFAULT NULL COMMENT '部门名称';

`deptName` CHAR(100) DEFAULT NULL COMMENT '部门名称';   -- not recommended

Do not apply functions or expressions to indexed columns in WHERE . Functions on indexed columns invalidate the index.

EXPLAIN SELECT userId, loginTime FROM loginuser WHERE loginTime >= DATE_ADD(NOW(), INTERVAL -7 DAY);

Incorrect usage:

SELECT userId, loginTime FROM loginuser WHERE DATE_ADD(loginTime, INTERVAL 7 DAY) >= NOW();

Batch large data modifications. Split massive deletes/updates into smaller chunks to avoid long transactions, master‑slave lag and CPU saturation.

-- Example batch (repeat 200 times)
DELETE FROM account LIMIT 500;
-- Single large delete (risky)
DELETE FROM account LIMIT 100000;

Elegant SQL Standards

Guidelines for readable, maintainable, and safe schema design.

Document tables and columns with comments. Example:

CREATE TABLE `account` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` VARCHAR(255) DEFAULT NULL COMMENT '账户名',
  `balance` INT DEFAULT NULL COMMENT '余额',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='账户表';

Consistent keyword case and indentation. Example of well‑formatted SQL:

SELECT stu.name, SUM(stu.score)
FROM Student stu
WHERE stu.classNo = '1班'
GROUP BY stu.name;

Contrast with a poorly formatted version.

Always list column names in INSERT statements. Prevents errors when schema changes.

INSERT INTO Student(student_id, name, score) VALUES ('666', '捡田螺的小男孩', '100');

Include primary key, create_time , and update_time in every table. Enables auditing and change tracking.

Define columns as NOT NULL whenever possible. Saves one byte per row and avoids null‑pointer issues in calculations.

Use the InnoDB storage engine for all tables. Provides transactions, row‑level locking and better crash recovery.

Standardize character set to UTF‑8 (or UTF8MB4 for emoji). Prevents garbled text and avoids charset conversion that can invalidate indexes.

Update column comments promptly when their meaning changes. Keeps documentation accurate for future developers.

Adopt a naming convention for indexes. Prefixes: pk_ for primary keys, uk_ for unique indexes, idx_ for regular indexes. Example: pk_id, uk_email, idx_status.

References

https://blog.csdn.net/qq_39390545/article/details/107519747

Alibaba Development Manual