【MySQL11】进阶篇 | 索引_#3使用规则

目录验证索引执行效率联合索引最左前缀法则全部索引失效常见原因or、null、数据分布引发的索引失效SQL 索引提示use /ignore/force index覆盖索引、回表原理前缀索引使用教程单列索引 vs 联合索引对比索引创建全套设计原则一、验证索引效率无索引状态sqlselect *from tb_sku where sn 1000000003124没有索引会进行全表扫描查询速度很慢。创建 B 树单列索引sqlcreate index idx_sku_sn on tb_sku(sn);建立索引之后查询会走 B 树索引检索大幅提速。MySQL 普通索引底层结构为 B‑Tree二、联合索引核心最左前缀法则多字段联合索引查询必须匹配索引最左侧前列不能跳过索引字段跳过后面字段跳过位置之后索引失效示例联合索引顺序(profession,age,status)正常命中全部索引sqlexplain select *from tb_user where profession 软件工程 and age 31 and status 0;命中前两列索引sqlexplain select *from tb_user where profession 软件工程 and age 31;跳过最左首字段 → 索引直接失效typeALL 全表扫描sqlexplain select *from tb_user where age 31 and status 0;跳过中间 age 字段 → age 之后 status 索引失效仅 profession 生效sqlexplain select *from tb_user where profession 软件工程 and status 0;where 条件字段顺序不影响MySQL 优化器会自动调整顺序满足最左前缀即可生效sqlexplain select *from tb_user where age 31 and status 0 and profession 软件工程范围查询陷阱联合索引遇到 范围条件范围右侧所有字段索引直接失效sql-- age30 是范围查询status索引失效 select *from tb_user where profession 软件工程 and age 30 and status 0优化方案 优先使用 等值边界代替大于小于范围尽量把范围字段放在联合索引最后一位。三、高频索引失效场景1. 在索引列上做运算、函数、隐式类型转换sql-- phone为字符串索引字段不加单引号发生隐式转换索引失效 explain select *from tb_user where phone 17799999015;索引字段必须保持原生类型不要运算、不要调用函数、不要类型转换2. like 模糊查询like xxx%尾部通配索引生效like %xxx、like %xxx%头部带通配符索引失效全表扫描3. or 连接条件一侧无索引则整条 SQL 索引失效sql-- id带索引、age没有索引or导致索引失效 explain select *from tb_user where id 10 or age 23;解决办法给 or 后面 age 字段新建索引。4. 字段 is null /is not nullsql-- 有概率命中索引 select *from tb_user where profession is null -- 大概率索引失效全表扫描 typeall select *from tb_user where profession is not null5. MySQL 优化器判断全表扫描更快主动放弃索引当查询条件匹配表中绝大多数的数据索引查找成本高于全表扫描MySQL 自动放弃索引。sql-- 绝大多数phone满足该条件触发全表扫描 explain select *from tb_user where phone 1973892485四、SQL 提示手动控制 MySQL 选用索引适合一张表存在多条索引MySQL 优化器选错索引时手动干预sql-- 创建单列索引 create index idx_user_pro on tb_user(profession); -- 1.use index建议MySQL使用指定索引 select *from tb_user use index(idx_user_pro) where profession 软件工程; -- 2.ignore index忽略指定索引 select *from tb_user ignore index(idx_user_pro) where profession 软件工程; -- 3.force index强制使用该索引 select *from tb_user force index(idx_user_pro) where profession 软件工程;五、覆盖索引 回表查询基础原理聚集索引主键索引B 树叶子节点存放该行全部字段数据辅助索引普通索引叶子节点只存放主键 id回表通过普通索引拿到主键 id 之后再去主键索引读取整行数据多出一次 IO 查询。覆盖索引定义查询所需要的全部字段都存在于联合索引当中不需要回表读取主键索引执行计划 Extra 字段出现Using index 命中覆盖索引性能最高Using index condition 使用索引检索但是需要回表查询数据案例联合索引(profession,age,status)sql-- select * 需要回表 explain select *from tb_user where profession 软件工程 and age 31 and status 0 -- 查询字段全部包含在索引内触发覆盖索引Using‑index explain select id, profession from tb_user where profession 软件工程 and age 31 and status 0避坑准则禁止习惯性写select *极易触发回表拖慢查询速度SQL 优化例题现有字段id, username, password, statussqlselect id, username, password from tb_user where username itcast;优化方案建立联合索引idx_username_up(username,password)触发覆盖索引免去回表。六、前缀索引适用场景varchar、text 等超长字符串字段完整建立索引占用磁盘 IO 大、索引体积臃肿。 只存储字段前面一部分字符当作索引节省索引存储空间。创建语法sql-- 取该字段前n位作为索引 create index idx_xxxx on table_name(column(n));索引选择性核心参数选择性 不重复数值 ÷ 表总记录数数值越接近 1索引效果越好唯一索引选择性 1sql-- 完整字段选择性 select count(distinct email) / count(*) from tb_user; -- 截取前10位前缀选择性 select count(distinct substring(email, 1, 10))/count(*) from tb_user;调试不同截取长度在存储空间与选择性之间取平衡点。七、单列索引 vs 联合索引单列索引一个索引只包含单个字段sql-- phone单列索引查询phonename条件命中phone索引之后必须回表查找name select *from tb_user where phone 1779999991110 and name 韩信联合索引sqlcreate index idx_user_phone_name on tb_user(phone, name)查询条件phone name索引已经携带两个字段能够触发覆盖索引、跳过回表查询。开发规范优先建立合适顺序的联合索引减少大量单列索引遵循最左前缀规划索引字段顺序等值字段放前面、范围查询字段放在索引末尾。八、完整索引创建设计原则数据表数据量大的时候才建立索引小表没必要经常用于 where 查询、join 连接、order by、group by 的字段建立索引区分度高的字段建立索引优先创建唯一索引长字符串 varchar/text 字段采用前缀索引节约磁盘优先设计联合索引利用覆盖索引避免回表 IO一张表索引数量需要管控索引过多会拖慢 insert/update/delete 写入速度索引字段设置NOT NULLNULL 会造成索引失效、查询优化困难。