COMMIT waits only for the redo log to reach disk. Changed pages wait in the buffer pool and reach disk later.SELECT reads a snapshot, rebuilt from the undo log, and takes no locks. That's multi-version concurrency control (MVCC).REPEATABLE READ the gaps between them too.Asked in interviews
AskedWhat is MySQL and what are its key features?ENGINE=InnoDBUse the default engine, for nearly every tableENGINE=MyISAMKeep read-mostly legacy tablesENGINE=MEMORYKeep rows in RAM, lost at shutdownTipSHOW ENGINES lists every engine the server has, such as Archive and CSV.
TrapAfter a crash, you must check and repair MyISAM tables.
innodb_buffer_pool_size = 12GThe default is 128MB. Dedicated servers often give it up to 80% of RAM.SET GLOBAL innodb_buffer_pool_size = 8589934592;TipNew pages enter the old sublist, three eighths of the pool, so a table scan rarely pushes out hot pages.
TrapA pool that's too large can make the server swap.
Asked in interviews
AskedWhat is the InnoDB buffer pool and why does its size matter?country CHAR(2)MySQL pads it with spaces and strips them on read.name VARCHAR(100)It stores only the characters used, plus a length prefix.price DECIMAL(10,2)FLOAT and DOUBLE are approximate.qty SMALLINT UNSIGNED1, 2, 3, 4 or 8 bytes, from TINYINT to BIGINT.TrapVARCHAR(n) counts characters, up to 4 bytes each in utf8mb4. A row's columns share 65,535 bytes.
id BIGINT AUTO_INCREMENT PRIMARY KEYOne per table, and it must be indexed.SELECT LAST_INSERT_ID(); Per connection, so other sessions can't change it.ON DUPLICATE KEY UPDATE n = new.nFrom 8.0.19, AS new after VALUES names the incoming row.TipInnoDB keeps the counter across restarts. Inserts fail once it reaches the type's maximum.
TrapGaps are normal. A rolled-back insert or an updating upsert can use up a number, and InnoDB never reuses it.
CHARACTER SET utf8mb4The default, with the collation utf8mb4_0900_ai_ci .SET NAMES utf8mb4;VARCHAR(191) in old schemasOld COMPACT tables cap an index at 767 bytes, or 191 utf8mb4 characters.TipIn a collation name, _ci ignores case and _ai ignores accents. _cs, _as and _bin are stricter.
TrapMySQL's utf8 is utf8mb3, 3 bytes at most. Emoji inserts fail in strict mode, and turn into ? without it.
Asked in interviews
AskedWhat are character sets and collations in MySQL, and why use utf8mb4?attrs->'$.brand' attrs->>'$.brand' ->> also removes the quotes.JSON_SET(attrs, '$.price', 34.99)MySQL rewrites only that part when the new value is no larger.brand VARCHAR(50) AS (attrs->>'$.brand') Index this generated column. Arrays need a multi-valued index (8.0.17+).TipMySQL rejects invalid JSON with an error, and stores documents in a binary format.
Asked in interviews
AskedWhat are MySQL's JSON data type capabilities?FOREIGN KEY (user_id) REFERENCES users(id)MySQL indexes user_id if needed.ON DELETE CASCADESET NULL keeps them and clears the key.SET foreign_key_checks = 0;MySQL never checks the rows loaded meanwhile.TipNO ACTION, the default, acts like RESTRICT in InnoDB. InnoDB rejects SET DEFAULT.
TrapOnly InnoDB and NDB enforce foreign keys. MyISAM accepts the clause and ignores it.
ADD INDEX (status, total)A covering index. EXPLAIN shows Using index.AUTO_INCREMENT. Random keys can leave pages half full.UUID_TO_BIN(UUID(), 1)The 1 reorders the time parts so new values sort together.TipEvery secondary index stores the primary key, so a long primary key makes every index bigger.
TrapWith no primary key or UNIQUE NOT NULL index, InnoDB clusters on a hidden row ID.
CREATE INDEX ix_email ON users (email);ADD INDEX (country, city)It serves country alone, but not city alone.ADD FULLTEXT (body)Query it with MATCH ... AGAINST.ADD INDEX ((LOWER(email)))ALTER INDEX ix_email INVISIBLEThe optimizer ignores it until you set VISIBLE.TipMEMORY tables support hash indexes. InnoDB builds a B-tree even if you ask for USING HASH.
TrapEvery index slows writes and takes space. Drop the ones sys. lists.
EXPLAINEXPLAIN ANALYZEtype column's values, best to worstconst, eq_ref, ref, range, index, then ALL, a full table scan.ExtraLook for Using filesort and Using temporary. Using index means the index covers the query.YEAR(col) = 2023 hides it from the index.IN and EXISTSEither works. Since 8.0.16, both get the same semijoin rewrite.Traprows is an estimate, and InnoDB's can be off. EXPLAIN ANALYZE shows the real count.
NULLCOUNT(*)COUNT(bonus)GROUP_CONCAT(name ORDER BY name)MySQL cuts it at group_concat_max_len , 1,024 bytes by default.WHERE salary > 50000HAVING COUNT(*) > 5MySQL also lets HAVING use a select-list alias.LIMIT 10 OFFSET 20LIMIT 20, 10The comma form puts the offset first.WHERE id > 1020 ORDER BY id LIMIT 10Keyset pagination. A large offset still reads every skipped row.TipOrder pages by a unique key. Tied rows come back in any order, so pages can repeat or skip rows.
TrapONLY_ is on by default. It rejects a selected column that isn't grouped, aggregated or determined by the grouped columns.
LEFT JOIN and a RIGHT JOIN with UNION.JOIN from CROSS JOINIn MySQL they're the same. With ON you get matching rows, and without it every pair.FROM emp e LEFT JOIN emp m ON e.manager_id = m.idA self-join. LEFT JOIN keeps employees with no manager.WITH RECURSIVEIt stops at 1,000 levels, the cte_max_recursion_depth default.FROMThat's a derived table, and MySQL requires an alias for it.TipMySQL rewrites many IN and EXISTS subqueries as semijoins, which return each outer row once.
START TRANSACTION;COMMIT;ROLLBACK;SELECT @@autocommit;It's on by default, so each statement commits by itself.TrapDDL such as ALTER TABLE commits the open transaction, so a later ROLLBACK can't undo it.
Asked in interviews
AskedExplain transactions and ACID properties.READ UNCOMMITTEDRead other sessions' uncommitted changesREAD COMMITTEDTake a new snapshot for every readREPEATABLE READKeep the first read's snapshot, the defaultSERIALIZABLEWith autocommit off.Make plain reads take shared locksTipSet it with SET SESSION TRANSACTION ISOLATION LEVEL, and read @@transaction_.
TrapInnoDB's REPEATABLE READ also stops phantom rows, using next-key locks. READ COMMITTED locks fewer gaps, so it deadlocks less.
Asked in interviews
AskedWhat are isolation levels in MySQL?Both readers see v1 before the COMMIT, and neither waits. After it, REPEATABLE READ (RR) keeps its first snapshot, and READ COMMITTED (RC) sees v2.
SELECT * FROM accounts WHERE id = 1;TrapA long-running transaction, even a read-only one, holds back purge, and the undo tablespace grows.
Asked in interviews
AskedHow does MVCC work in InnoDB?FOR UPDATEFOR SHAREAUTO_INCREMENT insertsinnodb_autoinc_lock_mode = 2The default. Mode 1 takes the lock for bulk inserts, and mode 0 for every insert.SHOW ENGINE INNODB STATUS;innodb_print_all_deadlocks logs every one.FOR UPDATE NOWAITOr lower innodb_lock_wait_timeout , 50 seconds by default.TipWithout an index on the WHERE column, an UPDATE scans the whole table and locks every row.
TrapInnoDB rolls back one transaction, the victim, with a deadlock error. Your code must retry the whole transaction.
innodb_redo_log_capacity = 2GIt replaced innodb_log_file_size .TipA checkpoint marks how far changed pages are safely on disk. InnoDB then truncates the redo log before it.
Asked in interviews
AskedWhat are the redo log and undo log in InnoDB?mysqldump --single-transaction shop > shop.sqlIt reads one consistent snapshot.mysql shop < shop.sqlThe dump drops and recreates each table first.mysqlbinlog binlog.000042 | mysqlRestore the backup, then replay the binary logs.TipTake backups on a replica to keep the load off the source.
TrapCopying a running InnoDB server's files doesn't give a working backup. Use a hot backup tool.
Asked in interviews
AskedHow do you backup and restore MySQL databases?CHANGE REPLICATION SOURCE TO SOURCE_HOST='db1'; START REPLICA;Also set the login, SOURCE_SSL=1 and binlog position, or use global transaction IDs (GTIDs).SHOW BINARY LOG STATUS;On 8.0, run SHOW MASTER STATUS.TipReplication is asynchronous by default, so a replica may miss the last commits when the source crashes.
TrapMySQL 8.4 removed CHANGE MASTER TO, START SLAVE and SHOW MASTER STATUS. Old scripts that use them fail.
Asked in interviews
AskedExplain MySQL replication.innodb_flush_log_at_trx_commit = 1The default. With 2, a crash can lose the last second of commits.sync_binlog = 1max_connections = 500The default is 151.wait_timeout.TipKeep the pool small. A small pool with waiting threads is faster than a huge one.
TrapSET GLOBAL lasts until a restart. Use SET PERSIST, or put it in my.cnf.
SET GLOBAL slow_query_log = 'ON';SET GLOBAL long_query_time = 2;The default is 10 seconds.SELECT * FROM sys.statement_analysis LIMIT 10;It reads the Performance Schema, on by default.SHOW FULL PROCESSLIST;TipTune in layers. Queries and indexes first, then memory and logging, then hardware.
DELETE FROM users WHERE age < 18;It fires delete triggers, and a transaction can roll it back.TRUNCATE TABLE users;It resets AUTO_INCREMENT and fires no triggers.DROP TABLE users;TipTRUNCATE fails on an InnoDB table that another table's foreign key points to.
TrapTRUNCATE and DROP commit at once, so ROLLBACK can't bring the data back.
Asked in interviews
AskedExplain the difference between DELETE, DROP, and TRUNCATE.DELETE FROM logs WHERE id < 90000 LIMIT 1000;Commit and repeat. An UPDATE batch must skip rows already done.ADD COLUMN phone VARCHAR(20), ALGORITHM=INSTANT MySQL picks INSTANT by default when it can.ALGORITHM=INPLACE, LOCK=NONEIf MySQL can't, it fails at once.pt-online-schema-change or gh-ost. Both copy rows in chunks.TrapOnline DDL still takes a brief exclusive metadata lock. It waits for open transactions, and new queries queue behind it.
Asked in interviews
AskedHow do you handle large table operations efficiently?PARTITION BY RANGE (YEAR(sale_date)) PARTITION BY LIST (store_id)PARTITION BY HASH (id) PARTITIONS 4ALTER TABLE sales DROP PARTITION p2020;TipPartition pruning skips partitions the WHERE clause rules out. So partition on the column your queries filter by.
TrapEvery unique key, the primary key too, must include the partitioning columns. Partitioned tables can't have foreign keys.
Asked in interviews
AskedWhat is partitioning and when would you use it?CREATE VIEW active_users AS SELECT id, name FROM users WHERE active;A view stores no data. Grant it instead of the table to hide columns.TEMPTABLE view can't use them.TrapA view with GROUP BY or DISTINCT uses TEMPTABLE, so you can't update through it.
Asked in interviews
AskedWhat are views and their benefits?mysql clientDELIMITER //The client then ends statements at //, not at each ; in the body.CALL replaces many round trips. MySQL doesn't precompile them, though.EXECUTE only. The procedure runs with its DEFINER account's privileges.SET NEW.email = LOWER(NEW.email); In a BEFORE INSERT or BEFORE UPDATE trigger.AFTER UPDATE trigger. OLD holds the row before, and NEW after.TipIf a trigger fails, its statement fails too, and InnoDB rolls both back.
TrapCascaded foreign key actions don't fire triggers.
Nothing matches. Try a shorter word, or clear the search.
With Pro, you can:
Already have Pro? Sign in