Preface:

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

1. Harm of no primary key table

Take InnoDB table as an example. As we all know, in InnoDB, tables are stored in the form of indexes according to the order of primary keys. This storage mode of tables is called indexed organized table. An InnoDB table must have a clustered index. If there is a primary key, the primary key is used as the clustered index. If no primary key is explicitly defined, InnoDB selects a unique non-empty index instead. If there is no such index, MySQL automatically generates an implied field for the InnoDB table as the primary key.

That is, it would be better if we could explicitly define primary keys, so what harm might a table without primary keys do? First of all, the absence of a primary key means that the primary key index cannot be used, which may affect query efficiency. The second is that it is not maintension-friendly, such as requiring tables to have primary keys when upgrading to an MGR cluster or using some open source tools. Also, batch updates or deletes of tables without primary keys can easily cause long master/slave delays.

As a side note, when the primary database updates or deletes a large number of tables with no primary key (especially tables with no primary key and no index), the slave database will experience a significant master/slave delay, and even become stuck. In this case, it is necessary to go to the master library to parse the binlog POS point stuck to the slave library, and find that it is an operation on a table with no primary key. In this case, if you want to catch up with the slave library as soon as possible, you can manually set the synchronization of the table to ignore. Process SQL as follows:

# suppose the check finds that the testTB table is causing master-slave delay and the secondary library can ignore the synchronization of the table 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)
Copy the code

Once the synchronization of the table is ignored, the slave will soon catch up with the master. You can add a primary key to the table and manually synchronize the table to remove the neglect.

2. Select table with no primary key

How do we find tables with or without primary keys when we have many tables in our database instance? The MySQL database contains all of our database information stored in the system database information_schema. Select * from table where no primary key exists;

Select * from table where no primary key exists; select * from table where no primary key existsSELECT
	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 * from table where no primary key existsSELECT
	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'
);
Copy the code

If you find a table without a primary key, the next step is to create a new primary key for the table. Whether you use an autoincrement ID, uUID, or some other algorithm-based primary key field, you are advised to create a new primary key for the table. For example, we can add a primary key to a table that has no primary key:

Alter table tb1 alter table Tb1 alter table Tb1 alter table Tb1ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT 'Increment primary key' PRIMARY KEY FIRST; Select * from table where no primary key is foundSQL
SELECT
CONCAT('ALTER TABLE ',t1.table_schema,'. ',t1.table_name,' ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT \'Increment primary key \' 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');Copy the code

Conclusion:

This article focuses on the possible hazards of tables without primary keys and how to find if there are tables without primary keys. Some SQL in the article are based on the system table to find, you can save to their own environment to try oh. MySQL > select * from table where primary key = ‘key’;