文章目录

  • 1. 索引匹配原则
  • 2. 一般建议
  • 3. 问题1 `=和in可以乱序`
  • 4. 问题2 复合索引的选取


1. 索引匹配原则

  • 全值匹配我最爱
  • .最佳左前缀法则
  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换，会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
  • mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
  • is not null 也无法使用索引,但是is null是可以使用索引的
  • like以通配符开头(‘%abc…’) mysql索引失效会变成全表扫描的操作, "xxx%"有可能使用使用，也不一定百分百使用索引
  • 字符串不加单引号索引失效
  • 少用or,用它来连接时会索引失效

2. 一般建议

  • 对于单键索引，尽量择针对当前query过滤性更好的索引
  • 在择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)，在择组合索引的时候，尽量择可以能够包含当前query中的where字句中更多字段的索引,尽可能通过分析统计信息和调整query的写法来达到择合适索引的目的

3. 问题1 =和in可以乱序

如果 建立索引a_b_c 那么 select * from b=2and a=1会走索引吗? 会

建索引的几大原则

  • 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
    比如a = 1 and b = 2 and c > 3 and d = 4
  • 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，
  • 如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序 比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4. 问题2 复合索引的选取

假设有这样一个表，里面有500w数据，假设创建了两个索引，一个是idx_de_no(deleted, task_no)，另外一个是idx_no（task_no），当查询条件是 where delete=0 and task_no=‘xxx’ 时，会走哪个索引？

-- auto-generated definition
create table test_index
(
    id          int unsigned auto_increment comment '主键'
        primary key,
    task_no     varchar(256)                         not null comment '任务编号',
    task_name   varchar(256)                         null comment '质检任务名称',
    remark      varchar(1000)                        null comment '说明',
    creator     varchar(30)                          null comment '创建人',
    updater     varchar(30)                          null comment '更新人',
    create_time datetime   default CURRENT_TIMESTAMP not null comment '创建时间',
    update_time datetime   default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
    deleted     tinyint(1) default 0                 not null comment '0：正常 1：删除'
)
    comment '测试';

create index idx_de_no
    on test_index (deleted, task_no);

create index idx_no
    on test_index (task_no);

答案是：会走 idx_no（task_no） 索引，而不是走idx_de_no(deleted, task_no),原因是索引查询优化器还会根据统计信息、表的大小和复杂度等因素来决定最终使用的索引，delete字段只有0和1两种可能，复杂度太低，优化器最终会选择idx_no索引进行查询，所以我们在选取字段做复合索引时，会优先使用复杂度高的(零散度高的)字段；