MySQL table lookup without primary key

Foreword:

In MySQL, a primary key is generally required when creating a table. If the requirements are not standardized, it is inevitable that there will be several tables without a primary key. In this article, let us find the table without a primary key.

1. The harm of no primary key table

Taking InnoDB tables as an example, we all know that in InnoDB, tables are stored in the form of indexes according to the order of the primary key. The tables in this storage method are called index-organized tables. An InnoDB table must have a clustered index. When there is a primary key, the primary key will be used as the clustered index; if the primary key is not explicitly defined, InnoDB will choose a unique non-empty index instead. If there is no such index, MySQL automatically generates a hidden field for the InnoDB table as the primary key.

In other words, it is best that we can explicitly define the primary key, so what harm may be caused by a table without a primary key? First of all, the absence of a primary key means that the primary key index cannot be used, which may affect query efficiency. Secondly, it is not friendly to maintenance. For example, when you want to upgrade to an MGR cluster or use some open source tools, you will require the table to have a primary key. Another point is that batch updates or deletions of tables without a primary key can easily cause a long master-slave delay.

Here also, by the way, when the master database updates or deletes a large number of tables without a primary key (especially a table with neither a primary key nor an index), the slave database will have a great master-slave delay, and it will even be stuck in execution. Don't ask me how I knew it, I encountered it some time ago. The phenomenon that this happens is that the delay of the slave library is increasing, and the binlog pos position of the main library being executed has not changed. At this time, it is necessary to go to the main library to analyze the binlog pos position of the slave library and find that it is correct If you want to catch up with the database as soon as possible, you can ignore the synchronization of the table under manual setting. The processing SQL is as follows:

# 假设检查发现是 testtb 表导致了主从延迟 可以再从库忽略该表的同步
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db.testtb);
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

After ignoring the synchronization of this table, the slave library will soon catch up with the master library. You can add a primary key to the table later, and then manually synchronize and cancel the ignore.

2. Find a table without a primary key

Closer to home, when there are many tables in our database instance, how should we find out whether there are tables without primary keys? You can't find them one by one. You may be smart. You can search from the system tables that come with MySQL, because all our table building information is stored in the system library information_schema. The following SQL can find a table without a primary key:

# 查找某个库中无主键的表（有唯一键无主键的表也会被查出）
SELECT
    t1.table_schema,
    t1.table_name
FROM
    information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
    t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA = 'testdb';

# 查找整个实例中无主键的表
SELECT
    t1.table_schema,
    t1.table_name
FROM
    information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
    t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
);

If you find a table without a primary key, the next step is to add a primary key to the table. Whether you use auto-increment id, uuid, or primary key fields generated by other algorithms, it is recommended to add a primary key to the table. Taking self-increasing id as an example, we can add a primary key for a table without a primary key like this:

# 为表 tb1 新增自增ID字段作为主键
ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT '自增主键' PRIMARY KEY FIRST;

# 查找到的无主键表 拼接出新增主键的SQL
SELECT
CONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT \'自增主键\' PRIMARY KEY FIRST;')
FROM
    information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
    t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
) ;

summary:

This article mainly introduces the possible harm of a table without a primary key and how to find out whether there is a table without a primary key. Some of the SQL in the article is searched based on the system tables, you can save it to your own environment to try it out. The tables in MySQL still require primary keys to be mandatory. People must have their own opinions, and tables must also have primary keys!