NULL与NOT NULL约束对SQLite性能的影响探究

Great question! I’ve run into similar performance gaps when working with large datasets in SQLite, and the difference boils down to how SQLite handles storage, indexing, and query planning based on these constraints. Let’s break it down step by step:

1. Storage Overhead Savings with NOT NULL Constraints #

When you don’t specify NULL/NOT NULL for a column, SQLite defaults to allowing NULL values. For every column that can be NULL, SQLite needs to store an extra "NULL flag" (a single bit, packed into bytes across columns) for each row. While that sounds tiny, with millions (or more) of rows, this adds up to significant extra disk space and I/O operations during import.

When you mark a column as NOT NULL, SQLite knows it never has to store that NULL flag for that column. This reduces the total size of each row, cutting down on the amount of data that needs to be written to disk during your bulk insert. Less disk I/O means faster imports—exactly what you saw when dropping from 2000 seconds to 600 seconds.

2. Index Efficiency and Query Planning Improvements #

First, let’s note that your second table also adds id TEXT PRIMARY KEY—this creates an implicit unique index on the id column, which helps with both import (since SQLite can optimize index inserts when it knows the key is unique) and query performance. But the NOT NULL constraints play their own critical role:

• For queries like SELECT * FROM comments WHERE author = 'blabla', when author is marked NOT NULL, SQLite’s query optimizer knows it doesn’t have to account for NULL values in that column. This means it can skip checking for IS NULL conditions during index scans or table scans, streamlining the execution plan. Without the constraint, SQLite has to consider that some rows might have a NULL author, which adds extra checks and slows down the query.
• Additionally, indexes on NOT NULL columns are more compact. Since SQLite doesn’t have to handle NULL entries in index structures, the index itself is smaller and faster to scan—this directly boosts query speed for filtered or sorted operations.

3. Data Validation Overhead is Negligible #

You might wonder: doesn’t SQLite have to validate that author isn’t NULL during import? While that’s true, the overhead of this check is tiny compared to the massive savings from reduced disk I/O. For bulk inserts wrapped in a transaction, SQLite can batch these checks efficiently, so they don’t offset the performance gains from the storage optimizations.

To sum it up: NOT NULL constraints reduce storage overhead, let SQLite optimize query plans by eliminating NULL-related checks, and (when combined with a primary key) create more efficient indexes—all of which add up to the dramatic speed improvements you observed.

内容的提问来源于stack exchange，提问作者Jesper