人要有主见，表也要有主键

背景

在MySQL中,建表时一定要指定主键,并且是not null和auto increment单调递增的.那么为什么MySQL中建表一定要指定主键呢?因为在MySQL中数据的存储是根据索引进行存储的.也叫索引组织表.所以mysql要求建表时要有主键.那么如果我们在建表时没有指定主键索引怎么办呢?mysql会选择第一个unique key且定义为not null的索引作为主键索引,那么如果这个索引也没有怎么办呢?mysql会默认生成一个隐藏列占6个字节作为这个表的主键索引.索引在此也可以看到.MySQL是强制一个表必须要有主键的

没有主键的危害

那么在mysql中如果一个表没有主键会有什么影响呢?首先你基于某个你认为是主键列的查询,插入,更新,都会变慢.同时还会引起主从延迟的加大.再其次你的架构可扩展性将变小.因为无论是PXC还是MGR这些高可用架构都需要你显示的制定主键,mycat,dble或者shardingspher这些分布式架构也都需要你显示的指定主键.所以为了我们运维的安全性及架构的可扩展性.都一定要显示的制定主键.同时一定要告诉你的开发.主键一定要使用int或者bigint数据类型.不要使用uuid或者varchar这些数据类型.这些数据类型会导致索引失效和表碎片的产生.

查看没有主键的表

FROM information_schema.tables t1

WHERE (table_schema, table_name) NOT IN (

SELECT DISTINCT table_schema, table_name

FROM information_schema.columns

WHERE COLUMN_KEY = 'PRI'

)

AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema')
and t1.table_rows is not null;

批量加主键索引

CONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN inc_id bigINT UNSIGNED NOT NULL auto_increment COMMENT \'自增主键\' PRIMARY KEY FIRST;')
FROM
information_schema.tables t1
WHERE (table_schema, table_name) NOT IN (
SELECT DISTINCT table_schema, table_name
FROM information_schema.columns
WHERE COLUMN_KEY = 'PRI'
)
AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema')
and t1.table_rows is not null;

注意: 在mysq8.0中添加主键索引的话使用的是inplace方式进行添加的.会有短暂锁表.如果是在生产上进行批量添加索引的话.请在业务不繁忙的时候添加!!!