共50道题 | 难度分布:★★★★☆平均 | 20K+岗位数据库底层必考
难度:★★★★☆
答案要点:
难度:★★★★★
答案要点:
innodb_old_blocks_time(默认1秒)才移入innodb_old_blocks_pct控制old区比例,innodb_old_blocks_time控制晋升时间难度:★★★★☆
答案要点:
innodb_flush_method(O_DIRECT绕过OS缓存)innodb_io_capacity:SSD建议10000+,HDD建议200,控制每秒刷脏页数量innodb_max_dirty_pages_pct:脏页比例阈值(默认75%),超过则加速刷盘难度:★★★★★
答案要点:
innodb_doublewrite=0(不建议,除非有ZFS等保证原子写的文件系统)难度:★★★★☆
答案要点:
innodb_change_buffering:控制缓存操作类型(all/inserts/deletes/purges等)innodb_change_buffer_max_size:Change Buffer占Buffer Pool最大比例(默认25%)难度:★★★★★
答案要点:
难度:★★★★★
答案要点:
难度:★★★★☆
答案要点:
ALTER TABLE ... PAGE_MERGE_THRESHOLD=N设置难度:★★★★★
答案要点:
难度:★★★★★
答案要点:
SELECT name FROM user WHERE age=20→索引(age, name)可覆盖,无需回表难度:★★★★★
答案要点:
innodb_flush_log_at_trx_commit:0(每秒刷)/1(每次提交刷,最安全)/2(提交写OS缓存)难度:★★★★★
答案要点:
innodb_undo_tablespaces:独立Undo表空间(MySQL 8.0默认)难度:★★★★★
答案要点:
难度:★★★★☆
答案要点:
难度:★★★★★
答案要点:
SELECT * FROM performance_schema.data_locks(MySQL 8.0)innodb_deadlock_detect(默认开启),innodb_lock_wait_timeout(默认50秒)难度:★★★★☆
答案要点:
innodb_file_per_table=ON,MySQL 8.0默认)CREATE TABLESPACE创建,多表共享难度:★★★★☆
答案要点:
难度:★★★★☆
答案要点:
ROW_FORMAT=DYNAMIC是大表推荐的行格式难度:★★★★☆
答案要点:
SHOW ENGINE INNODB STATUS中的Log sequence number/Log flushed up to难度:★★★★☆
答案要点:
innodb_flush_method=O_DIRECT:绕过OS Page Cache,避免Double Bufferinginnodb_io_capacity/innodb_io_capacity_max:SSD建议10000/20000innodb_read_io_threads/innodb_write_io_threads:I/O线程数(默认4)innodb_flush_neighbors=0(关闭相邻页刷盘)难度:★★★★★
答案要点:
innodb_force_recovery:恢复模式(0正常,1-6逐渐放宽,6最宽松)难度:★★★★★
答案要点:
难度:★★★★☆
答案要点:
Seconds_Behind_Master、pt-heartbeat工具难度:★★★★★
答案要点:
innodb_lock_wait_timeout:锁等待超时时间(默认50秒)innodb_deadlock_detect=ON(默认),通过等待图(Wait-for Graph)检测环SHOW ENGINE INNODB STATUS的LATEST DETECTED DEADLOCK难度:★★★★☆
答案要点:
innodb_adaptive_hash_index:默认开启,高并发写场景可考虑关闭SHOW ENGINE INNODB STATUS的SEMAPHORES部分难度:★★★★★
答案要点:
EXPLAIN查看优化器的执行计划难度:★★★★★
答案要点:
SHOW INDEX FROM table的Cardinality(基数)列ANALYZE TABLE更新统计信息FORCE INDEX/USE INDEX:强制/建议使用指定索引optimizer_switch:控制优化器行为(如ICP、MRR等)难度:★★★★☆
答案要点:
WHERE a=1 AND b LIKE '%x'→a走索引,b在存储层过滤EXPLAIN中Using index condition表示使用了ICPoptimizer_switch='index_condition_pushdown=off'可关闭难度:★★★★☆
答案要点:
read_rnd_buffer_size:MRR排序缓冲区大小optimizer_switch='mrr=on,mrr_cost_based=on'难度:★★★★☆
答案要点:
join_buffer_sizeEXPLAIN中Using join buffer表示使用了BNL难度:★★★★☆
答案要点:
难度:★★★☆☆
答案要点:
innodb_stats_persistent=ON(默认)mysql.innodb_table_stats和mysql.innodb_index_statsANALYZE TABLE ②表数据变化超过1/16 ③新打开表innodb_stats_auto_recalc:自动重新计算(默认开启)innodb_stats_persistent_sample_pages:采样页数(默认20)ALTER TABLE ... STATS_SAMPLE_PAGES=100(提高统计精度)ANALYZE TABLE修复难度:★★★☆☆
答案要点:
innodb_buffer_pool_dump_at_shutdown=ON关闭时导出热点页innodb_buffer_pool_load_at_startup=ON启动时加载热点页SET GLOBAL innodb_buffer_pool_dump_now=ON/innodb_buffer_pool_load_now=ONib_buffer_pool,记录表空间ID+页号innodb_io_capacity调整innodb_buffer_pool_instances(默认8或1)难度:★★★☆☆
答案要点:
CREATE TABLE ... COMPRESSION="zstd"innodb_compression_level:压缩级别(1-9,越高压缩率越高但越慢)难度:★★★☆☆
答案要点:
internal_tmp_mem_storage_engine=TempTable:内存临时表引擎temptable_max_ram:TempTable最大内存(默认1GB)temptable_max_ram减少磁盘溢出CREATE TEMPORARY TABLE→存储在会话临时表空间难度:★★★★☆
答案要点:
EXPLAIN中Using window functions表示使用窗口函数难度:★★★★☆
答案要点:
WITH RECURSIVE cte AS (seed UNION ALL recursive_part)cte_max_recursion_depth(默认1000)难度:★★★☆☆
答案要点:
ALTER TABLE t ALTER INDEX idx INVISIBLE/VISIBLEuse_invisible_indexes=on:调试时可以强制使用隐藏索引难度:★★★☆☆
答案要点:
CREATE INDEX idx ON t(a ASC, b DESC):真正的降序索引ORDER BY a ASC, b DESC可以完全利用索引,不再需要filesortEXPLAIN中不会出现Using filesort(如果索引完全匹配排序)难度:★★★★☆
答案要点:
SELECT * FROM t WHERE b=10不满足最左前缀→无法用索引WHERE b=10改写为WHERE a=1 AND b=10 UNION ALL a=2 AND b=10...EXPLAIN中Using index for skip scanoptimizer_switch='skip_scan=on/off'控制难度:★★★★★
答案要点:
EXPLAIN:查看执行计划(type/key/rows/Extra)EXPLAIN ANALYZE(MySQL 8.0):实际执行并显示每步耗时performance_schema.events_statements_history:查看SQL执行详情SHOW PROFILE(已弃用)→performance_schema替代long_query_time+slow_query_logsys.schema_unused_indexes:查找未使用的索引难度:★★★★☆
答案要点:
难度:★★★★☆
答案要点:
难度:★★★★★
答案要点:
难度:★★★★☆
答案要点:
innodb_buffer_pool_size:可在线修改(MySQL 5.7+)Buffer pool size:总页数Free buffers:空闲页数Database pages:已使用页数Old database pages:old区页数Modified db pages:脏页数Read hits:缓存命中率(应>99%)SELECT * FROM sys.memory_global_by_current_bytes查看内存使用innodb_buffer_pool_instances(大Buffer Pool时设为8)难度:★★★★★
答案要点:
难度:★★★★☆
答案要点:
难度:★★★★☆
答案要点:
EXPLAIN中的Using where:Server层过滤EXPLAIN中的Using index condition:存储引擎层过滤(ICP)难度:★★★★★
答案要点:
information_schema.innodb_trx查看长事务innodb_undo_log_truncate=ON:自动截断Undo表空间innodb_lock_wait_timeout避免无限等待难度:★★★★★
答案要点:
lock_sys中注册,直到COMMIT/ROLLBACK才释放