Mysql innodb表针对主键选择的性能问题分析

针对mysql innodb 表

主键选择 （建议使用auto_increment 字段）

1、Auto_increment 字段（并发插入出现表锁争用）

 

2、趋势增量递增字段（性能最高）

 

3、在innodb表中，并不是每个行记录都存有row ID，只有表定义没有主键时，才会使用rowid并作为主键。rowid的分配是全局的，所有的表都共享这个ID。（容易出现全局锁）

 

4、随机UUID（磁盘随机写，性能差）

 

无主键性能问题

1、InnoDB维护一个全局的dict_sys.row_id值，所有无主键的InnoDB表，每插入一行数据，都将当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值+1；

 

2、InnoDB中很多地方的代码，包括刚才提到的ROW_ID的生成都是通过dict_sys->mutex保证线程安全问题，因此，我可以说任何用ROW_ID作为隐式聚簇索引键的表，都可能随机性的碰到插入停顿问题。多张这种表并行插入就会遇到性能限制，因为共享计数器的共享互斥锁和缓存争用是串行的。此外，每生成256个ID就会需要日志写入和刷新，这些都会引起性能毛刺问题。

 

 

如果没有主键，并且也没有一个趋势递增的唯一键，那么所有这些表都会依赖一个全局序列计数器生成的ROW_ID来构造一个隐式聚簇索引，这就会导致竞争从而引起性能问题。

 

如果随机主键，那么所有页都会被频繁写入，从而导致无法高效的缓存页。并且频繁的裂变还会导致页填充率不理想，从而额外占用很多的磁盘空间。

 

所以innodb最好要有一个递增的主键

mysql官方技术说明

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order. 

 

排查无主键表

select table_schema, table_name

from information_schema.tables

where table_name not in (select distinct table_name

from information_schema.columns

where column_key = "PRI")

AND table_schema not in

('mysql', 'information_schema', 'sys', 'performance_schema');