We have been fixing schema drift for around five years now. It has been such a massive endeavor that we lost track of tickets we made for the work.
For better visibility and tracking. Let's collect them.
I try to put them below this line
Wikibase: T85414: wikibase: synchronize schema on production with what is created on install
The starting point: T132416: Rampant differences in indexes on enwiki.revision across the DB cluster
T205913: Drop ct_ indexes on change_tag
T206103: recentchanges table indexes: tmp1, tmp2 and tmp3
T217397: logging.log_title_time and logging.log_title_type_time indexes are not on tables.sql but they exist on most of the wikis
T249972: Wikitech has lots of database drifts with core and rest of the databases
T249973: db1110 has 5 important database drifts that are unique to the host
T250055: Remove image.img_deleted column from production
T250056: lc_lang_key index is lingering in production
T250057: type_acton index in logging table is lingering in production
T250058: searchindex indexes are missing in production
T250060: tl_namespace index on templatelinks is unique only in s8
T250062: ipb_parent_block_id_2 index on ipblocks table on s8 only
T250063: inverse_timestamp column exists in text table, it shouldn't
T250066: text table still has old_* fields and indexes on some hosts
T250067: user_newtalk has two indexes not renamed in s4
T250071: Rename ipb_address index on ipb_address to ipb_address_unique
T252126: tl_from index on templatelinks is lingering in production
T256679: text table in db1144 drifts from core considerably
T256680: imagelinks has index mismatch on s8
T256682: page_restrictions indexes have been majestically drifting from code
T256684: pl_from index still lingers in random hosts
T256685: pl_namespace index on pagelinks is unique only in s8
T260111: All sorts of random drifts in wikis in s3
T265321: ipblocks_restrictions.ir_type is tinyint(1) in code but tinyint(4) in production
T265349: querycache qc_type and qc_title have different nullabality on s1 only
T265445: Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5
T269348: wikitech database has almost all of its varbinary fields wrong
T277116: fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production
T277118: iw_url in interwiki is varbinary(127) in production but blob in code
T277354: "chemical" major mime type was never added to production database
T278619: ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007
T278621: job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007
T298295: Fix length of columns page_restrictions.pr_level/pr_type on wmf wikis
T298316: Fix nullability of column recentchanges.rc_params on wmf wikis
T298554: Fix mismatching field type of archive.ar_timestamp on wmf wikis
T298555: Fix mismatching field type of logging.log_timestamp on wmf wikis
T298556: Fix mismatching field type of oldimage.oi_timestamp on wmf wikis
T298557: Fix mismatching field type of page.page_touched on wmf wikis
T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis
T298559: Fix mismatching field type of querycache_info.qci_timestamp on wmf wikis
T298560: Fix mismatching field type of revision.rev_timestamp on wmf wikis
T298563: Fix mismatching field type of column text.old_flags on wmf wikis
T298565: Fix mismatching field type of user table for columns user_email_authenticated, user_email_token, user_email_token_expires, user_newpass_time, user_registration, user_token, user_touched on wmf wikis
T306560: Fix nullability of img_major_mime and oi_major_mime
T307525: Fix mismatching field type of user table for columns user_newpassword, user_password, user_email on wmf wikis
T312027: Fix enwikivoyage drifts on pagelinks
T312028: Random drifts on s3 part 2