SELECT*FROM orders o JOIN users u ON o.user_id = u.id WHERE u.name='林渊'
字符集差异诊断:
解法:
ALTER TABLE users CONVERT TO CHARACTER SET utf8;
陷阱五:最左匹配
复合索引idx_time_status(create_time,status)失效案例:
SELECT*FROM logs WHERE status ='SUCCESS'
陷阱六:索引选择器
优化器的致命误判:
SELECT*FROM products WHERE category_id =3AND is_hot =1ORDERBY price DESC
索引选择矩阵:
强制干预方案:
SELECT*FROM products FORCE INDEX(idx_category) WHERE category_id =3AND is_hot =1ORDERBY price DESC
索引检验工具包
# 索引有效性核验套件mysql>SHOW INDEX FROM products WHERE Seq_in_index=1;# 字符集冲突检测mysql>SELECT TABLE_NAME,COLUMN_NAME,COLLATION_NAME FROM information_schema.COLUMNS WHERE COLLATION_NAME NOTLIKE'utf8%';# 隐式转换检测mysql> EXPLAIN EXTENDED SELECT ...;mysql>SHOW WARNINGS; # 查看转换痕迹