最左前缀原则

最左前缀原则：

最左前缀原则”是数据库索引优化中的一个重要概念，尤其在使用复合索引时。复合索引是指在多个列上创建的索引，而最左前缀原则规定：查询时，如果想利用复合索引，查询条件必须从索引中的最左边的列开始匹配。

简单来说，复合索引可以视为多个单列索引的组合，而数据库会按照索引中列的顺序来处理查询。因此，只有从第一个（最左边的）列开始匹配时，索引才会生效。

例如，有一个复合索引 (A, B, C)：

1. 查询条件是 A = ? 时，复合索引可以正常使用。
2. 查询条件是 A = ? AND B = ? 时，复合索引也可以使用。
3. 查询条件是 B = ? 或 C = ? 时，复合索引不会生效，因为它没有从最左列 A 开始匹配。

这个原则可以帮助我们优化查询语句，确保索引的有效利用，提升查询性能。

/*
 最左前缀原则
 */
 use test2;
 drop table if exists t_customer;
 create table t_customer(
    id int primary key auto_increment,
    name varchar(255),
    age int,
    gender char(1),
    email varchar(255)
);

insert into t_customer values(null, 'zhangsan', 20, 'M', 'zhangsan@123.com');
insert into t_customer values(null, 'lisi', 22, 'M', 'lisi@123.com');
insert into t_customer values(null, 'wangwu', 18, 'F', 'wangwu@123.com');
insert into t_customer values(null, 'zhaoliu', 22, 'F', 'zhaoliu@123.com');
insert into t_customer values(null, 'jack', 30, 'M', 'jack@123.com');
# 添加联合索引
create index idx_name_age_gender on t_customer(name, age, gender);
show index from t_customer;

/*
最左前缀原则:要想让索引生效，必须遵循最左前缀原则。
最左前缀原则是什么:
如果给t_customer表的name age gender添加了联合系引。
并且在创建索引的时候，顺序是:(name,age,gender)
那么在编写select语句的时候，where当中有很多条件，如果这些条件中
没有name字段的参与，则复合索引会失效。必须有name字段这个最左边的
字段参与条件，这样复合素引才会生效。生效后:可能是完全使用索引，也可能是部分使用案引。
 */

 explain select * from t_customer where name='zhangsan' and age=20 and gender='M'; # 完全使用索引 索引长度1033个字节
 explain select * from t_customer where name='zhangsan' and age=20; # 部分使用索引 索引长度1028个字节 说明gender在索引当中占用5个字节的长度
 explain select * from t_customer where name='zhangsan'; # 部分使用索引 索引长度1023个字节 说明age在索引当中占用10个字节的长度
 explain select * from t_customer where age=20 and gender='M' and name='zhangsan'; # 完全使用索引
 explain select * from t_customer where age=20 and gender='M'; # 不使用索引 原因：没有name条件的出现
 explain select * from t_customer where name = 'zhangsan' and gender='M'; #部分使用索引、只有name字段使用索引，因为中间断开了，导致gender没有使用索
 explain select * from t_customer where name = 'zhangsan' and gender='M' and age = 20; #全部使用索引
 explain select *from t_customer where name= 'zhangsan'and age>20 and gender='M'; /*当使用范围查找的时候，范围条件右侧的列不条使用索引*/
 explain select *from t_customer where name= 'zhangsan' and age>=20 and gender='M';# 使用>=即可

• 索引列参与了运算，索引失效：

  • 当查询条件中使用索引列进行运算时，索引失效。

    explain select * from t_emp where sal > 5000;
    explain select * from t_emp where sal * 10 > 50000;  -- 索引失效
    

• 模糊查询以 % 开头，索引失效：

  • 在进行模糊查询时，如果通配符 % 位于字符串的开头，索引将无法使用。

  explain select * from t_emp where name like '张%';
  explain select * from t_emp where name like '%飞';   -- 索引失效
  explain select * from t_emp where name like '%飞%';  -- 索引失效
  

• 字符串类型索引列，查询时省略单引号，索引失效：

  • 如果索引列是字符串类型，而查询时未使用单引号，可能导致索引失效。

• OR 查询条件中，未添加索引的字段导致索引失效：

  • 当查询条件中包含 OR，且其中一个字段未添加索引，索引失效。

  explain select * from t_emp where name = '张三' or sal = 5000;
  alter table t_emp drop index idx_t_emp_sal;
  explain select * from t_emp where name = '张三' or sal = 5000;  -- 索引失效
  

• 查询符合条件的记录在表中占比较大，索引失效：

  • 当查询的结果集中，符合条件的记录占表中较大比例时，索引可能失效。数据库可能选择进行全表扫描。

• IS NULL 和 IS NOT NULL 导致索引失效：

  • 当查询条件为 IS NULL 或 IS NOT NULL 时，索引通常会失效，特别是在 NULL 或 非 NULL 值占比较大的情况下。

  SELECT * FROM t_emp WHERE sal IS NULL;    -- 索引失效
  SELECT * FROM t_emp WHERE sal IS NOT NULL;  -- 索引失效
  

  /*
  索引失效的情况
  1.索引列参加了运算，索引列会失效
   */
  create table t_emp(
      id int primary key auto_increment,
      name varchar(255),
      sal int,
      age char(2)
  );
  
  insert into t_emp values(null, '张三', 5000, '20');
  insert into t_emp values(null, '宋飞', 4000, '30');
  insert into t_emp values(null, '李飞', 6000, '40');
  
  create index idx_t_emp_name on t_emp(name);
  create index idx_t_emp_sal on t_emp(sal);
  create index idx_t_emp_age on t_emp(age);
  explain select * from t_emp where sal > 5000;
  explain select * from t_emp where sal * 10 > 50000; # 索引失效
  
  /*
  索引列进行模糊查询时以 %开始的，索引失效
   */
  explain select * from t_emp where name like '张%';
  explain select * from t_emp where name like '%飞'; #失效
  explain select * from t_emp where name like '%飞%'; #失效
  
  /*
   索引列是字符串类型，但查询时省略了单引号，索引失效
   */
  
  /*
  查询条件中有or，只要有未添加索引的字段，索引失效
   */
  show index from t_emp;
  explain select * from t_emp where name = '张三' or sal = 5000;
  alter table t_emp  drop index idx_t_emp_sal;
  explain select * from t_emp where name = '张三' or sal = 5000; #失效
  
  
  /*
  当查询的符合条件的记录在表中占比较大，索引失效
   */
  
  /*
   关于is nu1l和is not null的索引失效问题
   */
  SELECT * FROM t_emp WHERE sal IS NULL;
  #假设表 t_emp 中有许多 NULL 值，
  # 由于 NULL 值不参与索引的存储，数据库无法利用索引直接定位 NULL 值，需要扫描整个表，索引在此情况下失效。
  SELECT * FROM t_emp WHERE sal IS NOT NULL;
  #当 NULL 值占比较大时，使用 IS NOT NULL 的查询会导致数据库扫描大多数的记录，
  # 这时即使有索引，数据库认为使用索引的性能优势不足，也可能放弃索引，改为全表扫描。