本文为个人总结如有错误请评论区指出。文章目录索引什么是索引B树索引的分类按物理存储结构分类聚集索引二级索引也叫辅助索引按逻辑功能分类主键索引primary唯一索引(unique)普通索引(index)联合索引(Composite Index)前缀索引(Prefix Index)全文索引(fulltext)什么是倒排索引索引的语法性能分析查看执行频次慢查询日志show profiles开启profile操作使用profileexplain查看执行计划explain执行计划各字段的含义1. id(查询执行的顺序)2. select_type(查询的类型)3. table 当前行查询的表名4. partitions匹配的分区5. type 访问类型6. possible_keys可能会用到的索引7. key实际真正用到的索引8. key_len实际使用的索引长度9. ref索引匹配的关联条件10. rows预估扫描的行数11. filtered过滤后的行占比12. Extra额外执行信息、优化提示索引的使用规则最左前缀法则索引失效情况SQL提示SQL优化插入数据优化主键优化order by优化group by优化limit优化count优化update优化索引什么是索引索引是 MySQL 对表中一个 / 多个字段构建的有序数据结构核心作用加速 SELECT 查询同时辅助唯一索引 / 主键索引保证字段唯一性核心特点空间换时间 —— 索引会占用额外的磁盘存储空间以此换取极致的查询速度两面性只提速查询SELECT会减慢增删改INSERT/UPDATE/DELETE 速度因为增删改时需要同步维护索引结构。B树B 树是 MySQL 索引的核心底层数据结构是有序多路平衡搜索树B树的结构分为非叶子节点和叶子节点两层B树的核心规则非叶子节点只存索引值和指针不存真实数据叶子节点只存真实有效数据按照顺序串联成双向链表非叶子节点的索引值都能在叶子节点中找到对应的完整数据例如InnoDB用B树为索引结构的原因相比二叉树B树的层级更少搜索效率更高相比B树B树只在叶子节点存储数据而B树无论是叶子节点还是非叶子节点都会保存数据这样就使一页中存储的键值减少指针跟着减少要保存和B树一样多的数据就不得不增加树的高度导致性能下降。B 树也叫 B - 树是平衡多路搜索树核心特点是非叶子节点和叶子节点都存真实数据是 “多路 平衡” 结构层级少减少磁盘 IO不支持叶子节点串联范围查询效率低。B树是 B 树的前身缺点是节点存数据导致体积大、范围查询差所以 MySQL 不用 B 树而用优化后的 B 树。索引的分类按物理存储结构分类聚集索引本质将索引和数据物理存储在一起索引的叶子节点就是数据表的真实数据行。核心特点InnoDB中主键默认是聚集索引。若表无主键会选非空唯一列即会选唯一索引作为聚集索引若无此类列MySQL会隐式生成6字节自增的rowid为聚集索引一张表只能有一个聚集索引因为数据的物理存储顺序只能有一种查询效率极高通过聚集索引查询时找到索引节点就能直接拿到完整数据无需回表查询什么是回表查询下面会讲二级索引也叫辅助索引本质索引和数据物理分离叶子节点仅存储索引字段值聚集索引的主键值 需要通过回表查询获取完整的行数据核心特点范围定义除了聚集索引索引之外所有手动创建的所有普通索引、唯一索引、联合索引、全文索引等都属于二级索引一张表可以有多个二级索引查询逻辑第一步在二级索引树中找到目标数据的主键值第二步用主键值到聚集索引树中查询完整的行数据这个过程就叫回表查询优化方案若查询的字段全部包含在二级索引中即覆盖索引可以不进行回表查询直接从二级索引返回数据。按逻辑功能分类主键索引primary定义基于主键字段创建的索引用于唯一标识数据表的每一行记录核心特点字段必须满足 NOT NULL UNIQUEInnoDB 默认作为聚簇索引一张表仅能有 1 个优点查询无需回表效率最高数据物理有序范围查询速度极快数据唯一缺点插入 / 更新时需维护数据物理排序写入性能略差易产生数据碎片需定期优化唯一索引(unique)定义基于唯一字段创建的索引保证索引字段的值不重复核心特点字段值唯一允许存储多个 NULL一张表可创建多个可用于防止业务字段重复优点兼具唯一性约束 查询加速双重作用比主键索引灵活支持多字段创建缺点写入时需校验唯一性性能略低于普通索引普通索引(index)定义基于普通字段创建的索引无任何业务约束纯提升查询效率核心特点无约束字段值可重复、可 NULL一张表可创建多个开发中使用频率最高优点创建 / 删除开销最小对写入性能影响最低针对性优化高频查询字段缺点无业务约束能力无法防止数据重复联合索引(Composite Index)定义基于多个字段组合创建的索引也称复合索引核心特点遵循最左前缀匹配原则后面会讲什么是最左前缀原则可设为联合唯一索引 / 联合普通索引一个索引覆盖多字段查询场景优点替代多个单列索引减少索引数量和磁盘占用优化多字段组合查询效率缺点字段顺序直接影响索引有效性顺序错误会导致索引失效前缀索引(Prefix Index)定义对字符串字段的前 N 个字符创建的索引是普通索引的特殊形式核心特点仅适用于CHAR/VARCHAR/TEXT类型需指定前缀长度 N本质是普通索引的优化版优点大幅减小索引文件体积提升索引查询 / 写入效率解决长字符串字段索引效率低的问题缺点前缀长度过短会降低索引区分度导致查询效率下降无法用于排序和分组前缀重复时全文索引(fulltext)定义专为长文本字段设计的索引支持分词检索核心特点仅支持CHAR/VARCHAR/TEXT类型底层是倒排索引非 B 树替代like %关键词%全模糊查询优点支持分词检索、权重排序全模糊查询效率远超 like适用于文章、评论等长文本场景缺点不支持短词检索默认最小 4 字符维护成本高写入性能影响较大什么是倒排索引定义倒排索引也叫反向索引它不按记录行存储索引而是先对文本内容做分词处理再建立关键词 → 包含该关键词的记录 ID 列表的映射关系。结构倒排索引主要由词典和倒排列表组成词典存储文本中所有去重后的关键词倒排列表存储每个关键词对应的记录 ID 集合工作原理假如有三条文章记录记录id文章内容1无籽西瓜2MySQL3有籽西瓜构建倒排索引分词处理对每篇文章内容拆分关键词得到无籽、西瓜、MySQL、有籽建立映射关键词倒排列表记录ID西瓜1,3无籽1有籽3MySQL22.执行查询过程例如在名为水果的字段中检索包含西瓜这个关键词的所有记录第一步去词典中找到关键词西瓜第二步获取对应的倒排列表1,3第三步直接返回记录ID 1 和 3 的完整数据无需全表扫描match(水果)against(西瓜);match(字段名)指定要检索的字段必须是已经创建了全文索引的字段against(‘搜索词’)指定要搜索的关键词 / 语句整体作用在指定的全文索引字段中检索包含目标关键词的记录返回匹配结果。索引的语法创建索引create[unique|...索引类型]index索引名称ontable表名(字段名...)查看索引showindexfrom表名删除索引dropindex索引名on表名性能分析查看执行频次通过如下指令可以查看当前数据库的insert、update、delete、select的访问频次showglobalstatuslikeCom_%慢查询日志慢查询日志记录了所有执行时间超过指定参数long_query_time单位:秒默认10秒的sql语句的日志MySQL的慢查询日志默认没有开启需要在MySQL的配置文件(/etc/my.cnf)中配置下面的信息#开启MySQL慢查询日志开关 slow_query_log 1 #默认是0关闭1为开启 #设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢日志 long_query_time 2show profilesshow profiles 可以在做sql优化时了解到时间在哪耗费了。开启profile操作通过have_profiling参数能够看到当前MySQL是否支持profile操作selecthave_profiling;profiling默认是关闭的,可以通过set语句在session/global级别开启先通过如下语句查看profiling操作是否开启selectprofiling如图为0表示未开启再通过如下语句开启setprofiling1;设置后再查询可以看到已经为1开启使用profile查看每一条sql耗时的基本情况showprofiles;查看指定query_id的sql语句各个阶段的耗时情况showprofileforquery query_id;查看指定query_id的sql语句CPU的使用情况showprofile cpuforquery query_id;explain查看执行计划explain或者desc命令可以获取MySQL如何执行select语句的信息包括在select语句执行过程中表如何连接和连接到顺序。#直接在语句前面加上explain/descexplainselect字段列表from表名where条件;explain执行计划各字段的含义1. id(查询执行的顺序)是sql执行的编号MySQL会依照id的规则决定执行顺序执行规则id不同时id的数值大的先执行id相同时执行顺序是从上而下2. select_type(查询的类型)表示这一行的查询时什么类型的查询语句用来区分普通查询、子查询、联表查询、聚合查询、union查询等常见取值simple最简单的查询不包含子查询、union、联表primary查询中最外层的主查询只要sql包含子查询/union外层查询就会标记为primarysubqueryselect/where中独立的子查询执行一次即可derived表示派生表查询即from后面的子查询会生成临时表3. table 当前行查询的表名表示当前这一行的执行计划正在访问那一张表。取值说明直接显示表名比如 user、order_info表示查询这张表显示derived数字比如 derived2表示这是 id2 的查询生成的「派生临时表」显示union数字1,数字2比如 union1,3表示这是 id1 和 id3 的查询通过 UNION 生成的临时表显示subquery数字表示这是 id 数字的子查询生成的临时表。4. partitions匹配的分区表示当前查询命中了表的哪个分区仅对分区表生效。取值说明显示具体的分区名表示查询命中了这个分区显示 NULL如果表不是分区表这个字段永远是 NULL → 99% 的业务场景都是 NULL非核心字段。5. type 访问类型表示 MySQL 在表中 找到所需行的扫描方」这个字段直接决定了这条 SQL 的查询性能好坏type的取值是判断 SQL 是否高效的核心标准取值含义system极致最优表里只有 1 条数据且是系统表业务中几乎遇不到const通过 主键 / 唯一索引 做等值查询只匹配 1 条结果比如 where id100id 是主键查询速度极快一次命中eq_ref多表联查时被联表通过 主键 / 唯一索引 做等值匹配联表查询的最优级别比如 a left join b on b.ida.b_idb.id 是主键每次联表只匹配 1 条ref通过 普通非唯一索引 做等值查询可能匹配多条结果比如 where name‘张三’name 是普通索引这是单表查询最常见的优秀级别绝大多数业务的最优目标就是这个range索引的范围查询只扫描索引的某一段比如 where id between 100 and 200、where id100、where id in (1,2,3)、like ‘张%’前缀匹配索引生效性能很好。index全索引扫描会扫描整个索引树但不会扫描物理表数据比 ALL 好但依然是全量扫描比如查询的字段都是索引字段覆盖索引但无查询条件ALL全表扫描MySQL 会遍历整张表的所有数据行找符合条件的记录表数据量越大查询越慢这是慢查询的头号元凶看到typeALL第一件事就是加索引优化。优化准则至少要达到 range 级别最好能达到 ref/eq_ref/const 级别杜绝 index/ALL 级别6. possible_keys可能会用到的索引MySQL 的查询优化器预判当前查询有可能匹配上、可以使用的索引列表。核心特点这个字段是候选名单只是 MySQL 觉得这些索引有机会生效不代表一定会用取值为NULL表示当前查询「没有任何可用的索引」大概率会走全表扫描typeALL这个字段的数量不影响性能只是罗列候选没有参考优先级。7. key实际真正用到的索引表示 MySQL 在执行这条 SQL 时实际最终选择并使用的索引名称这是索引是否真正生效的核心判断依据核心特点key 是 possible_keys 的子集实际用的索引一定在候选索引列表里possible_keys有值但keyNULL表示索引存在但是 MySQL 优化器判定用索引不如全表扫描快放弃使用索引keyNULL表示完全没有使用任何索引这是性能差的核心原因显示索引名比如idx_user_name表示这条 SQL成功用上了该索引索引生效8. key_len实际使用的索引长度表示 MySQL 在执行查询时实际用到的索引字段的字节长度单位是字节核心特点key_len 越长代表用到的索引字段越多、字段越长key_len 是索引生效的精准判断依据例如创建联合索引 idx_name_age_sex(name,age,sex)如果key_len只包含name的长度 → 只有第一个字段生效索引失效一半如果key_len包含nameagesex的长度 → 联合索引全字段生效。key_len 是预估的最大长度是精确值越小越好9. ref索引匹配的关联条件表示 MySQL 在使用索引查询时与索引字段进行等值匹配的内容是什么常见取值const常量匹配比如 where id100用常量 100 匹配主键索引具体字段名比如 a join b on a.id b.user_id则 b 表的 ref 字段显示test.a.id表示用 a 表的 id 字段匹配 b 表的索引NULL非等值匹配比如范围查询 between、全索引扫描、全表扫描时该字段为 NULL。10. rows预估扫描的行数MySQL 查询优化器预估为了找到符合条件的记录需要扫描的表 / 索引的行数核心特点这个值是预估数值不是精确值但误差极小具备绝对的参考价值rows的数值越小越好代表 MySQL 只需要扫描少量行就能找到结果性能越好如果rows数值接近表的总数据量 → 说明走了全表扫描性能极差。11. filtered过滤后的行占比表示经过查询条件过滤后符合条件的记录数占扫描行数 (rows) 的百分比取值范围0 ~ 100。核心规则filtered 的值 越大越好百分比越高代表「过滤效果越好」扫描的行中大部分都是符合条件的无用扫描少filtered 100表示扫描的所有行都符合条件无过滤filtered 10表示扫描的行中只有极少部分符合条件过滤效果极差大概率是查询条件写的不合理比如没有索引、用了函数导致索引失效12. Extra额外执行信息、优化提示这是explain的补充字段包含了无法用其他字段表达的、极其重要的执行细节记录了 MySQL 执行查询时的特殊逻辑、优化策略、性能隐患很多时候看 Extra 字段就能直接判定 SQL 是否需要优化以及如何优化常见取值Using index 覆盖索引查询的所有字段select 后的字段 where 条件字段都在同一个索引中MySQL 只需要扫描索引就能返回结果不需要回表查询物理数据这是单表查询的极致优化说明这条 SQL 的索引设计完美性能拉满Using where临时表查询有WHERE条件MySQL 通过索引找到数据后再做条件过滤索引生效 条件过滤正常场景无需优化Using temporary文件排序MySQL 需要创建临时表来存储中间结果临时表是内存 / 磁盘表数据量大时会严重拖慢性能看到这个值必须优化优化方向给分组 / 去重的字段加索引。Using filesort MySQL 无法利用索引完成排序需要把数据加载到内存后手动排序排序的数据量越大性能越差慢查询高频元凶优化方向给排序字段创建索引让排序通过索引完成默认是升序asc索引的使用规则最左前缀法则对联合索引idx(字段1,字段2,字段3)只有查询条件从索引的最左侧字段开始、连续匹配时索引才会生效。例如有效匹配字段1、字段1字段2、字段1字段2字段3从左到右连续使用索引字段无效匹配字段2、字段3、字段2字段3跳过左侧字段索引完全失效。即联合索引必须从第一个字段开始用中间不能断索引失效情况1. 违反最左前缀法则联合索引idx(a,b,c)查询条件用b/b,c/c跳过左侧字段a索引失效。2. 字段类型不匹配例如where phone 13800138000phone是字符串类型传入数字触发隐式转换索引失效。也就是条件是字符串必须用引号’ ’3. 模糊查询使用%xxx或%xxx%like%关键词或like %关键词%索引失效仅like 关键词%能命中前缀索引。4. 条件中使用函数操作或运算对索引字段做函数操作where left(name,2) 张或运算where id 1 10索引失效。5. or条件中存在无索引字段where 索引字段1 or 无索引字段2or会导致所有条件的索引失效。6. 范围查询后字段失效联合索引idx(a,b)where a10 and b2a的范围查询后b的索引失效。7. 数据区分度过低例如性别字段只有 2 个值建索引MySQL 会直接选择全表扫描索引失效。8. 查询结果占表数据比例过高若查询结果超过表数据的 20%MySQL 认为全表扫描比走索引更快主动放弃索引。SQL提示SQL提示是优化数据库的一个重要手段就是在SQL语句中加入一些人为的提示来达到优化操作的目的use index :建议 MySQL 优先使用指定的索引给优化器一个优先级建议如果这个索引完全无法匹配条件优化器会再选其他方案例如-- 建议MySQL使用联合索引 idx_bookname_author查询书名selectbook_namefrombookuseindex(index_bookname_author);ignore index:强制MySQL忽略指定的索引告诉优化器这个索引绝对不能用-- 忽略掉低效的单列索引 idx_name让MySQL自己选其他更优的索引select*fromuserignoreindex(idx_name)wherename张三andage20;force index:强制MySQL 使用指定的索引不管优化器认为走索引成本高 / 区分度低 / 查询占比高必须走这个索引-- 性别字段有索引 idx_genderMySQL默认不走索引强制让它走索引select*fromuserforceindex(idx_gender)wheregender男;SQL优化插入数据优化核心是提升批量插入效率常用手段用insert into 表 values(...)批量插入少用单条循环插入例如insertintouser(name,age)values(张三,20),(李四,21),(王五,22);关闭自动提交事务set autocommit0批量插入后统一提交例如setautocommit0;-- 关闭自动提交insertintouser(name,age)values(张三,20),(李四,21),(王五,22);-- 批量插入N条数据commit;-- 统一提交事务setautocommit1;-- 恢复默认自动提交主键优化用自增主键避免 UUID 等无序主键导致的页分裂主键字段尽量小如int代替bigint减少索引占用空间避免主键更新会导致聚集索引重建性能损耗大。order by优化目标是让排序走索引避免using filesort把排序字段加入联合索引遵循最左前缀例如联合索引idx_name_age(name,age)查询按 age 排序索引失效写法触发Using filesort跳过左侧字段直接排序explainselectname,agefromuserorderbyage;-- typeALLExtraUsing filesort索引生效写法explainselectname,agefromuserwherename张三orderbyage;-- typeref无Using filesort避免select *只查索引包含的字段触发覆盖索引还是上面那个例子低效排序查所有字段即使排序字段有索引也会回表后再排序explainselect*fromuserwherename张三orderbyage;-- ExtraUsing filesort高效排序只查索引内字段触发覆盖索引直接用索引有序性返回结果explainselectname,agefromuserwherename张三orderbyage;-- ExtraUsing indexgroup by优化核心是减少临时表与文件排序把分组字段加入索引让分组基于有序索引完成例按 age 分组统计无索引先全表扫描再排序分组explainselectage,count(*)fromusergroupbyage;-- ExtraUsing temporary; Using filesort创建索引idx_age再分组有索引索引本身有序直接按索引分组无需额外排序explainselectage,count(*)fromuserforceindex(idx_age)groupbyage;-- ExtraUsing index先过滤再分组用where提前缩小数据范围还是上面那个例子低效写法先全表分组再过滤结果扫描数据量大selectage,count(*)fromusergroupbyagehavingage20;高效写法先 where 过滤再分组扫描数据量大幅减少selectage,count(*)fromuserwhereage20groupbyage;limit优化解决大偏移量分页慢用主键 / 唯一索引定位低效写法limit 偏移量,条数偏移量越大越慢explainselect*fromuserlimit100000,10;-- rows100010扫描10万行只返回10行高效写法通过主键过滤偏移量只扫描 10 行explainselect*fromuserwhereid100000limit10;-- rows10精准扫描目标数据count优化不同count写法性能差异大count(字段) 最慢需取值 判空无 not null 约束时尽量不用count(主键id) 较慢需遍历取出主键值累加主键非空无需判空count(1) 很快遍历表不取任何值每行赋值常量 1 直接累加count(*) 最快最优遍历表不取任何值仅统计存在行语义最贴合统计总行数。效率排序count(无约束字段) count(有约束字段) count(主键id) count(1) ≈ count(*)update优化InnoDB 行锁基于索引若update条件未命中索引会升级为表锁确保update的where条件命中索引错误写法无索引条件表锁并发时所有 update 排队等待-- age无索引锁整张表updateusersetname张三whereage20;正确写法createindexidx_ageonuser(age);-- 建索引updateusersetname张三whereage20;-- 命中索引行锁生效避免更新主键会重建聚集索引更新主键会导致聚集索引全量重建锁表 性能损耗极大