With and without autoincremented id performance
Let's categorize things into 3 buckets:

Case 1: No natural PK.  Then do have an AI.  Soner or later, you will find that having it makes maintenance easier.  Example: Deleting or modifying a specific row due to a data error.  That is, do not depend on the hidden PK that InnoDB will generate.

Case 2: There is a natural PK and you need an index by that column(s).  Bingo!  A PK is an index, so you can get the performance benefit of the index inherent in a PK.  Plus you avoid the space, clutter, etc, of the AI.

Case 3: Natural PK possible, but indexing it is not needed.  Now we are in a gray area.  Flip a coin.  Or, since a VARCHAR is slightly slower for indexing, pick the INT AI.

Case 4: (Yeah, I started with 3 buckets.)  If the table is PARTITIONed, a lot of additional considerations come into play.  I won't go into them in this Q&amp;A.

Let me provide an example of Case 2 -- a many-to-many mapping table.

A lot of people build essentially

CREATE TABLE A_to_B_mapping
    id INT AUTO_INCREMENT,       -- not used
    id_a,  -- link to table A
    id_b,  -- link to table B
    PRIMARY KEY(id),
    INDEX(id_a),  -- for joining from `A` to `B`
    INDEX(id_b)   -- if going the other way
)


But this would be much better:

CREATE TABLE A_to_B_mapping
    -- no AUTO_INCREMENT,      -- saving space
    id_a,  -- link to table A
    id_b,  -- link to table B
    PRIMARY KEY(id_a, id_b), -- PK, covering, unique, and for joining from `A` to `B`
    INDEX(id_b, id_a)   -- if going the other way, now 'covering'
) ENGINE=InnoDB;        -- so PK is 'clustered'


A "Covering" index is an INDEX that has all the columns needed anywhere in the SELECT.  A non-covering index must reach into the data BTree (after reaching into the index BTree) to get the rest of the columns.  That is, "covering" is more efficient.

Note:  Even if the ids are VARCHAR, the benefit of 'covering' outweighs the string-vs-int argument.

More on many-to-many:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Another Case 2 example:  wp_postmeta is inefficient .  That applies to other EAV schemas, as well.

I find that 2/3 of the time, a 'natural' PK can be made from a column (or combination of columns).