一、MySQL架构1.1 MySQL逻辑架构┌──────────────────────────────────────┐ │ 客户端层连接处理 │ └─────────────────┬────────────────────┘ │ ┌─────────────────┴────────────────────┐ │ Server层SQL处理 │ │ ┌─────────┐ ┌─────────┐ ┌────────┐ │ │ │ 连接器 │ │ 解析器 │ │ 优化器 │ │ │ └─────────┘ └─────────┘ └────────┘ │ └─────────────────┬────────────────────┘ │ ┌─────────────────┴────────────────────┐ │ 存储引擎层InnoDB/MyISAM │ └──────────────────────────────────────┘1.2 InnoDB vs MyISAM特性InnoDBMyISAM事务支持不支持外键支持不支持锁粒度行锁表锁全文索引5.6支持支持崩溃恢复支持不支持二、索引详解2.1 索引数据结构B Tree索引[15] ← 索引页 / \ [5,10] [20,25] ← 索引页 / | \ / | \ 叶 叶 叶 叶 叶 叶 ← 数据页 ↓ ↓ ↓ ↓ ↓ ↓ 实际数据顺序链表连接B Tree vs B Tree对比B TreeB Tree数据存储所有节点仅叶子节点查询稳定性不稳定所有查询复杂度相同范围查询需要回旋叶子节点链表支持2.2 索引分类类型说明示例主键索引主键自动建PRIMARY KEY(id)唯一索引唯一不重复UNIQUE(name)普通索引普通加速查找INDEX(name)联合索引多列组合INDEX(a,b,c)全文索引文本搜索MATCH(content) AGAINST(‘关键词’)三、Explain执行计划3.1 explain使用EXPLAINSELECT*FROMuserWHEREname张三;输出字段字段含义id查询序号select_type查询类型table表名type访问类型possible_keys可用索引key实际使用索引key_len索引长度ref索引引用rows扫描行数估算Extra额外信息3.2 type详解从好到差type值说明案例system表只有一行系统表const最多一行PRIMARY KEYeq_ref唯一扫描PRIMARY KEY/UNIQUEref非唯一扫描普通索引range范围扫描BETWEEN/IN/LIKEindex全索引扫描INDEXALL全表扫描无索引3.3 Extra详解值含义Using filesort需要额外排序不好Using temporary需要临时表不好Using index覆盖索引好Using index condition索引下推好Using where回表过滤四、索引失效场景4.1 索引失效的11种情况-- 1. 索引列参与计算EXPLAINSELECT*FROMuserWHEREage130;-- ✖ 失效-- 2. 索引列使用函数EXPLAINSELECT*FROMuserWHERESUBSTRING(name,1,3)张三;-- ✖ 失效-- 3. 类型转换EXPLAINSELECT*FROMuserWHEREage30;-- ✓ 有效MySQL会自动转换EXPLAINSELECT*FROMuserWHEREname123;-- ✖ 失效字符串字段用数字查-- 4. LIKE以%开头EXPLAINSELECT*FROMuserWHEREnameLIKE%三;-- ✖ 失效EXPLAINSELECT*FROMuserWHEREnameLIKE张%;-- ✓ 有效-- 5. OR前后不都是索引列EXPLAINSELECT*FROMuserWHEREname张三ORage30;-- ✖ 失效age不是索引-- 6. NOT IN / NOT EXISTSEXPLAINSELECT*FROMuserWHEREageNOTIN(20,30);-- ✖ 可能失效-- 7. ! / EXPLAINSELECT*FROMuserWHEREage!30;-- ✖ 可能失效-- 8. 联合索引违反最左前缀原则CREATEINDEXidx_name_ageONuser(name,age);EXPLAINSELECT*FROMuserWHEREname张三;-- ✓ 有效EXPLAINSELECT*FROMuserWHEREage30;-- ✖ 失效EXPLAINSELECT*FROMuserWHEREname张三ANDage30;-- ✓ 有效-- 9. 排序时索引失效CREATEINDEXidx_name_ageONuser(name,age);EXPLAINSELECT*FROMuserORDERBYname;-- ✓ 有效EXPLAINSELECT*FROMuserORDERBYage;-- ✖ 失效EXPLAINSELECT*FROMuserORDERBYname,age;-- ✓ 有效五、慢查询优化5.1 开启慢查询日志-- 查看慢查询开关SHOWVARIABLESLIKEslow_query%;SHOWVARIABLESLIKElong_query_time%;-- 开启慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 查看慢查询日志SHOWGLOBALSTATUSLIKESlow_queries%;5.2 my.cnf配置[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 15.3 慢查询分析工具# 使用mysqldumpslow分析mysqldumpslow-t10/var/log/mysql/slow.log5.4 慢查询优化案例优化前SELECT*FROMorders o,users u,products pWHEREo.user_idu.idANDo.product_idp.idANDo.create_time2024-01-01ORDERBYo.create_timeDESCLIMIT100;优化步骤Step1添加必要索引ALTERTABLEordersADDINDEXidx_create_time(create_time);ALTERTABLEordersADDINDEXidx_user_id(user_id);ALTERTABLEordersADDINDEXidx_product_id(product_id);Step2只查必要字段SELECTo.id,o.amount,o.create_time,u.nameasuser_name,p.nameasproduct_nameFROMorders oINNERJOINusers uONo.user_idu.idINNERJOINproducts pONo.product_idp.idWHEREo.create_time2024-01-01ORDERBYo.create_timeDESCLIMIT100;Step3创建覆盖索引CREATEINDEXidx_coverONorders(create_time,user_id,product_id,id,amount);六、分页优化6.1 普通分页大数据量会慢SELECT*FROMordersLIMIT1000000,10;-- 偏移量越大越慢6.2 优化分页使用ID-- 第一页SELECT*FROMordersORDERBYidLIMIT10;-- 下一页记住上一页最后一条IDSELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;-- 利用主键索引极快七、SQL优化经验总结7.1 常用优化规则规则说明SELECT只查需要的字段减少网络传输使用覆盖索引避免SELECT *无法使用覆盖索引批量插入替代循环单条减少网络开销合理使用LIMIT分页查询要带上上一页ID索引列不能参与运算会导致索引失效避免隐式类型转换字符串字段不要用数字比较7.2 常用优化案例JOIN优化-- 优化前小表驱动大表没做好SELECT*FROMbig_table t1LEFTJOINsmall_table t2ONt1.idt2.t1_id;-- 优化后让小表驱动大表SELECT*FROMsmall_table t2LEFTJOINbig_table t1ONt1.idt2.t1_id;IN优化-- 优化前IN中数据量大SELECT*FROMuserWHEREidIN(1,2,3,...,10000);-- 可能变成嵌套查询-- 优化后先查出来再JOINSELECTt1.*FROMusert1INNERJOIN(SELECTidFROMuserLIMIT10000)t2ONt1.idt2.id;总结知识点说明执行计划type/Extra/Key是核心索引失效最左前缀/函数/OR/LIKE%开头慢查询开启慢日志explain分析优化方向索引SQL分页JOIN顺序核心口诀EXPLAIN先行索引失效要记清SQL优化三原则 LIMIT分页要记牢。